automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
qin_23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1835350.html
quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1835350.html
>
sql

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
--
qin_23
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1835350.htmlquin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual data fr
om the old db to the new one, but i would like to take the table structure.
in DB2, i could run through a ddl file that contained CREATE DATABASE, CREAT
E TABLE, etc. kinds of commands to automatically create tables, databases, a
nd the like. how exactly do i do this using MS SQL server? im thinking along
the lines of writing my own file, and then using something in SQL server to
basically read the file and execute the SQL commands. any ideas? thanks.
qin_23quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>|||alright. thanks it worked.
qin_23

Automatic sync between SQLce and Access

Hi,

I am converting an old eVB application to CF.NET 2. It's a very simple setup.
1) An Access database sits on the host and has occasional edits applied to it.
2) An application on the PDA with a mirror copy of the Access database (filtered data) and also has occasional edits applied
3) When the user puts the PDA in the cradle the databases get automatically synchronized with each other.

Probably couldn't get much simpler than this. This used to be very easy to do with PocketAccess and ActiveSync. Unfortunately this is no longer supported. I decided to use the new SQLce database instead when porting to CF.NET. It's working like a charm except that I haven't found an easy way to synchronize with the desktop Access application.

Merge replication seems to be exactly what I need but it doesn't work with Access or SQL Server Express. I don't need (or want) a full version of SQL Server. The dataset is very small.

ADS seems to point in the right direction except it doesn't support automatic synchronization. It also doesn't support Vista in combination with PPC2003SE.

Sync Services seems to have the same problems as ADS.

I have seen some third party tools around but they all require the user to manually kick off synchronization which I'm desperatly trying to avoid.

I've been searching for days now and I'm not getting any closer to a solution. Any help would be greatly appreciated!

Cheers,
Thomas

Hi Thomas,

You can kick of a process on the PC (from the desktop PC) (your own program), whenever ActiveSync is connected, and then use RAPI to launch a process on the device. You can even add command line parameters to the PDA process.

The activesync registry key is:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnConnect]

and opennetcf.org has a .NET RAPI wrapper: http://www.opennetcf.com/library/communication/OpenNETCF.Desktop.Communication.RAPI.CreateProcess.html

|||

Thanks for the reply. This is indeed a step in the right direction.

I guess the idea then would be to use ADS on top of it. So cradling the PDA would create 2 processes, one to start the ADS service and one to start some custom synchronization application on the PDA which in turn would connect to the ADS desktop service using RDA.

Sounds very clunky to me, but it could work. I guess I was hoping there would be an easy solution. I mean, i can't imagine a much simpler scenario and it's not supported out of the box (anymore). Why does Microsoft not support this?

|||

ADS is the replacement technology for CEDB/ADOCE - MS Access sync - as described here: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/sync-with-access.aspx

It looks like ADS does do automatic sync on desktop connection:

Synchronization happens when there is an active ActiveSync connection between the device and the desktop.

(from http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/microsoft-sql-server-2005-compact-edition-access-database-synchronizer-ads-rtw.aspx)

|||

That's what I thought too. Unfortunately I haven't found any evidence that it actually does so. ADS documentation is skimpy at the moment, but from what I gather you have to create a PDA application that uses RDA to sync yourself. And the only thing it does automatic is start the desktop "listen" service. There is no "client agent". This is not "simple and easy" to do since you have to push and pull data manually (using RDA) and provide your own conflict handling! ADS as it is now is definitely NOT a replacement.

|||

*bump*

Don't tell me that's it?

|||

This MS blog post addresses your question:
http://blogs.msdn.com/sqlservercompact/archive/2007/02/22/ads-automatically-starting-the-synchronization-between-device-and-desktop.aspx

sql

automatic rename in FROM clause of dependant objects ?

I have a problem.
I should rename some tables which are referenced in numerous stored
procedures.
Is there any way to automatically replace old table name with new one in all
dependant stored procedures,
instead of manual replacement in every stored procedure ehich would be
time-consuming?

Thank you in advance.Hi

No. You need to do it by hand for all Functions, Triggers and Stored
Procedures.
People will really get cross is SQL server would go and change scripts.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d1ieos$nno$1@.ls219.htnet.hr...
> I have a problem.
> I should rename some tables which are referenced in numerous stored
> procedures.
> Is there any way to automatically replace old table name with new one in
all
> dependant stored procedures,
> instead of manual replacement in every stored procedure ehich would be
> time-consuming?
> Thank you in advance.|||On Sun, 20 Mar 2005 01:07:22 +0100, "Zlatko Mati" <zlatko.matic1@.sb.t-com.hr>
wrote:

>I have a problem.
>I should rename some tables which are referenced in numerous stored
>procedures.
>Is there any way to automatically replace old table name with new one in all
>dependant stored procedures,
> instead of manual replacement in every stored procedure ehich would be
>time-consuming?
>Thank you in advance.

2 words - Speed Ferret.

http://www.moshannon.com/|||Zlatko Mati wrote:
> I have a problem.
> I should rename some tables which are referenced in numerous stored
> procedures.
> Is there any way to automatically replace old table name with new one
in all
> dependant stored procedures,
> instead of manual replacement in every stored procedure ehich would
be
> time-consuming?
> Thank you in advance.

One word. View.

--
David Rowland
http://dbmonitor.tripod.com|||Hi

Scripting the procedure to a file(s) and globally replacing should make all
the necessary changes. Having your code in a source code control system
would help you identify what needs to be changed.

John

"Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d1ieos$nno$1@.ls219.htnet.hr...
>I have a problem.
> I should rename some tables which are referenced in numerous stored
> procedures.
> Is there any way to automatically replace old table name with new one in
> all dependant stored procedures,
> instead of manual replacement in every stored procedure ehich would be
> time-consuming?
> Thank you in advance.

Automatic Log expansion

SQL Server 2000 SP4
On an underpowered machine (mostly an old RAID 5 controller/drives), hosting
Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt with).
When the .LDF file filled, SQL Server did a 10% expansion and the entire
server locked up for several minutes.
Is it normal for the system to refuse connections when expanding the .LDF
file? My guess is yes, because it has nowhere to put log entries (though I
would have hoped it would do the expansion before it filled, thus allowing
continued use).
How about when the .MDF/.NDF is expanded?
Thanks,
JayA 10 gig TLog? Wow :)
Is not it too big mate? You have an issue which is more important than
"expanding" thing... You better take a look at the following documentation:
Optimizing Transaction Log Performance
http://msdn2.microsoft.com/en-us/library/aa178420(SQL.80).aspx
Transaction Logs
http://msdn2.microsoft.com/en-us/library/aa933065(SQL.80).aspx
Managing the Transaction Log (Applies to SQL Server 2005, however, it's
gonna give you precious ideas I believe)
http://msdn2.microsoft.com/en-us/library/ms345382.aspx
--
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Sounds like you have Full recovery mode enabled and you are not backing up
your tlog. Common mistake.
You should size tlog appropriately for your workload, data size and backup
plan. It should NEVER grow automatically - you should control this if
needed during periods of minimal activity. Autogrowth should only fire if
something unexpected happens.
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Jay,
I wouldn't expect SQL Server to to refuse connections. But perhaps this was perceived so, because
the app tries to do some modification when it connects. Even the smallest modifications would
require a log records and if the log is full, well...
No, database files are not expended in advance, that is your job. ;-) This is one of the reasons why
we only use autogrow as a sort of backup plan (don't page me while I'm sleeping).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives), hosting Visnetic MailFlow we
> had a .LDF file that was 10 GB (already dealt with). When the .LDF file filled, SQL Server did a
> 10% expansion and the entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF file? My guess is yes,
> because it has nowhere to put log entries (though I would have hoped it would do the expansion
> before it filled, thus allowing continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||That makes sense Tibor, thanks.
To everyone else, thanks for your replies, but I already dealt with the
super-sized file before the post, hence the statement "(already dealt
with)". The question was to verify why the server locked up during the
expansion. As to how it happened, I'm being handed production database
servers one-by-one and would have dealt with something as basic as a huge
logfie withing a day, if not hours, of getting a server.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0E2F49FB-37F3-4497-9CB3-F8886A294B98@.microsoft.com...
> Jay,
> I wouldn't expect SQL Server to to refuse connections. But perhaps this
> was perceived so, because the app tries to do some modification when it
> connects. Even the smallest modifications would require a log records and
> if the log is full, well...
> No, database files are not expended in advance, that is your job. ;-) This
> is one of the reasons why we only use autogrow as a sort of backup plan
> (don't page me while I'm sleeping).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 SP4
>> On an underpowered machine (mostly an old RAID 5 controller/drives),
>> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already
>> dealt with). When the .LDF file filled, SQL Server did a 10% expansion
>> and the entire server locked up for several minutes.
>> Is it normal for the system to refuse connections when expanding the .LDF
>> file? My guess is yes, because it has nowhere to put log entries (though
>> I would have hoped it would do the expansion before it filled, thus
>> allowing continued use).
>> How about when the .MDF/.NDF is expanded?
>> Thanks,
>> Jay
>>
>

automate creation of staging database

any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>> RESTORE DATABASE TargetDB
>> FROM DISK = 'C:\Backups\SourceDB.bak'
>> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
>> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
>> See the Books Online for BACKUP/RESTORE details.
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.