Bulk inserts between two different DBMS

Hi folks,
I have a table located in DB2 nd I need to have a mirror image of this table on a SQL2000 database to avoid some server downtime problems.
Right now I have a solution using ADO.NET with Windows Services.

This windows service invokes itself everyday morning and pulls all the records from this table in DB2 to a dataset. Then I loop through the dataset and insert every record into SQL 2000 Table. This method is working fine ( It take approximately 2 minutes to insert 5000 records). I am just wondering whether there is any way to acheive bulk insertion in this case. Considering future growth of table I am not thinking the existing solution is neither elegant nor efficient.

Please let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

Thanks,
SaiPlease let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

You probably want to set up a DTS package in SQL Server that migrates the data. This does support using another DBMS as a source.

The alternative is you dump from DB2 to a flat file and then BULK INSERT that flat file into SQL Server.

Both approaches will be much faster than a procedural row by row transfer on large data sets.sql

Bulk insert from a .csv file

Hi folks,

I have a small problem - I am unable to load data from a .csv file into a table in SQL Server. Here is the command I am running:
BULK INSERT CCSProgramParticipation FROM 'c:\test.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Data in test.csv is the following format: (date fields can be blank)
NY580232,0,6/30/2006,3567,396,7/1/2005,9/9/2005
NY580232,0,6/30/2006,14850,462,12/12/2005,
...
...

What I see is the data does get loaded; however, data from the following row is getting inserted in the last field of a particular row (previous row) - it seems like the rowterminator is being ignored.

Has anyone encountered this issue? Please let me know your thoughts on this.

Thanks so much!

-ParulTry inserting ROWTERMINATOR ='\r\n'|||I have the following command now:

BULK INSERT CCSTest FROM 'c:\test.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR ='\r\n'
)

But this is erroring out with the following message:
Bulk Insert fails. Column is too long in the data file for row 1, column 7. Make sure the field terminator and row terminator are specified correctly.

I am not sure why...

Thanks!|||because exactly what the message says...your column is too small for the data...|||I understand, but I am not sure why it is erroring out in the first place - the fields are large enough for the data that they are supposed to hold.|||How can you be so sure?

Wht not DTS the file to a staging table and see what it builds, or import it into access

In any event, trying to troubleshoot this without more details is difficult.

Read the hint sticky at the top of the forum and post what it asks for

You may need to attach a sample load file as well|||Here is the file attached...as you try to load this file you will see that some rows are getting clubbed into a single field in the row preceding them...
I really think the file has invalid characters in the last column (date).

Bulk insert bypasses trigger

Hey Folks,
A 3rd party product uses some kind of bulk insert to update a table
several times during the day. Sometimes the data is overwritten, which
effectively loses history. A trigger on the table is bypassed by the bulk
process. I'm hoping there is another way to get notified when the table is
updated so I can quickly copy it to a safer location for reporting purposes.
Maybe an event? Alert?
Thanks!> A 3rd party product uses some kind of bulk insert to update a table
> several times during the day. Sometimes the data is overwritten, which
> effectively loses history. A trigger on the table is bypassed by the bulk
> process. I'm hoping there is another way to get notified when the table is
> updated so I can quickly copy it to a safer location for reporting
> purposes.
Bulk Insert to a staging table, then perform a regular insert ... select ...
from to ensure the trigger is fired.|||BULK INSERT? See the FIRE_TRIGGERS argument for the BULK INSERT statment in
BOL.
HTH
Jerry
"MnFisher" <mnfisher@.community.nospam> wrote in message
news:utwybjoyFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Hey Folks,
> A 3rd party product uses some kind of bulk insert to update a table
> several times during the day. Sometimes the data is overwritten, which
> effectively loses history. A trigger on the table is bypassed by the bulk
> process. I'm hoping there is another way to get notified when the table is
> updated so I can quickly copy it to a safer location for reporting
> purposes.
> Maybe an event? Alert?
> Thanks!
>
>|||Jerry,
Thanks for the response. I don't have access to the command itself. Is there
any other way to catch a bulk insert?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uJbnaooyFHA.1960@.TK2MSFTNGP10.phx.gbl...
> BULK INSERT? See the FIRE_TRIGGERS argument for the BULK INSERT statment
> in BOL.
> HTH
> Jerry
> "MnFisher" <mnfisher@.community.nospam> wrote in message
> news:utwybjoyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>|||To "catch"? view? Yes. Profiler.
HTH
Jerry
"MnFisher" <mnfisher@.community.nospam> wrote in message
news:OptkiQpyFHA.2812@.TK2MSFTNGP14.phx.gbl...
> Jerry,
> Thanks for the response. I don't have access to the command itself. Is
> there any other way to catch a bulk insert?
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uJbnaooyFHA.1960@.TK2MSFTNGP10.phx.gbl...
>

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
string,[vbcol=seagreen]
> is
> it
SP[vbcol=seagreen]
> The
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:[vbcol=seagreen]
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
David G.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
string,
> is
> > there still an advantage to using stored procedures (over just executing
> it
> > directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let the
SP
> > build the Sql string. Either way though, the Select statement will be a
> > variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of case?
> The
> > result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> >
> >
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
> > string, is there still an advantage to using stored procedures (over
> > just executing it directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let
> > the SP build the Sql string. Either way though, the Select statement
> > will be a variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of
> > case? The result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> John Smith wrote:
>> Hey folks,
>> I've got a select statement that gets built dynamically in code.
>> If what I pass to a stored procedure is nothing but a string that
>> is a select statement, and then have the stored procedure execute
>> that string, is there still an advantage to using stored procedures
>> (over just executing it directly from code).
>> The other possibility is to pass other parameters to the SP and let
>> the SP build the Sql string. Either way though, the Select
>> statement will be a variable string that gets executed.
>> Is there still a case for using stored procedures in this type of
>> case? The result set size will range from 1 record to 50,000
>> records.
>> Thanks!
>> Having the SP build the SQL is fine. Just use sp_executesql to
>> execute the call, and if the query has parameters, then use the
>> parameter feature of sp_executesql to define them and pass them to
>> the SQL statement. That way, if similar statements get executed, the
>> plan will already be in cache.
>> Performance will be fine... assuming your queries are tuned properly.
>>
>> --
>> David G.
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
--
David G.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!
John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>
|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.
|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,[vbcol=seagreen]
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
string,[vbcol=seagreen]
> is
> it
SP
> The
>
|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>
|||John Smith wrote:[vbcol=seagreen]
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
David G.

Building a mailing list

Hi folks,

I'm building a DTS package which needs to mail a list of users nightly

The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.

I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.

The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3...etc).

I'm a bit unsure on how to go about doing / writing this.

Can anyone help?

Thanks,

Davethis would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.|||Thought about that but my Stored Proc skills are nothing to write home about. If you have anything I could tweak to suit my needs I'd be grateful.

Thanks,

Dave|||Try running this sql through Query Analyzer:declare @.MyEmailString varchar(8000)

select @.MyEmailString = coalesce(@.MyEmailString + ',', '') + coalesce(email, '')
from employees
where role = 'MGR'

select @.MyEmailString|||Thanks Blindman.

Still trying to get my head around the syntax and use of Coalesce but it works.

Thanks again,

Dave|||Hi Blindman,

I need to run the code below against a MySql database.
It works fine when I modify it for an MS SQL db however I'm getting syntax errors when running it against a MySQL (5.1.9) db.

Any ideas?

Thanks,

Dave|||I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.|||Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html) function in MySQL.

<Afterthought>
After posting this, I realized that Rudy would probably pitch a fit, mostly because he enjoy's pitching fits. Note that I do NOT consider MySQL to be a toy, Rudy just likes to play with it because MySQL offers functions that violate the basic rules of relational algebra (such as the GROUP_CONCAT() function). This violation of hte rules doesn't make MySQL a bad product in any way, although I see it as removing MySQL from the category of Relational databases since it doesn't follow the basic rules required for a relational database.

-PatP

Building a calculation string

Hiya folks,
I'm trying to build up a calculation string the values and operators will (eventually) be held in a SQL table. But for now i'm just testing it using the query analyzer. Please see example code below:

Declare @.MyFirstNo varchar(50)
Declare @.MySecondNo varchar(50)
Declare @.MyStringPart varchar(50)
Declare @.MyCalcString varchar(50)
Declare @.MyResult varchar(50)

SET @.MyCalcString = ''
SET @.MyFirstNo = 10
SET @.MySecondNo = 3
SET @.MyStringPart = '/'

SET @.MyCalcString = @.MyCalcString + @.MyFirstNo
SET @.MyCalcString = @.MyCalcString + @.MyStringPart
SET @.MyCalcString = @.MyCalcString + @.MySecondNo

create table #tmp1(result decimal(18,5))
insert #tmp1
exec('select '+@.Mycalcstring)
select @.MyResult = result from #tmp1
drop table #tmp1

Print @.MyResult

So the calculation is : 10/3. Which results in an answer of 3.00000. Which is obviously wrong. If I declare the variables as 'decimal' then I can't build the calcation string, with an error of 'Error converting varchar to numeric'.

Any offers?

Love and peaceDeclare @.MyFirstNo varchar(50)
Declare @.MySecondNo varchar(50)
Declare @.MyStringPart varchar(50)
Declare @.MyCalcString varchar(50)
Declare @.MyResult varchar(50)

SET @.MyCalcString = ''
SET @.MyFirstNo = 10.0
SET @.MySecondNo = 3.0
SET @.MyStringPart = '/'

SET @.MyCalcString = @.MyCalcString + @.MyFirstNo
SET @.MyCalcString = @.MyCalcString + @.MyStringPart
SET @.MyCalcString = @.MyCalcString + @.MySecondNo

create table #tmp1(result decimal(18,5))
insert #tmp1
exec('select '+@.Mycalcstring)
select @.MyResult = result from #tmp1
drop table #tmp1

Print @.MyResult|||Thanks for taking the time to reply, (although it took me a while to spot what you'd changed!!!). Seems to have done the trick, now comes the extensive testing! Any problems and I'll let you know.

Love and peace

Building / Modifying an SQL Database remotely

Hi Folks,

I should begin by saying I'm fairly new to SQL Server. I'm an application
developer who has built front ends atop Server, but have never done much in
the way of design / development.

My question is in two parts;

1). What is the best way to remotely "create" an SQL Server database? I've
been told that this can be done with SQL Statements, but thought that there
may be a way using XML.

2). Can I use this same method to propogate updates to the remote database
without affecting the data?

To give you a better idea of what I'm trying to do, I have a client that
lives quite a ways up north. I'm building an application that will be
installed up there. I'd like a method to be able to make changes to this
database on my end (the development copy), create an XML file of the
"database structure" and have the other database or application import this
XML file, and add tables, fields and relationships if they don't already
exist.

Any help would be appreciated.

Thanks!
RickRWC (rcollens_SPAMONATOR_@.hemmingway.com) writes:
> I should begin by saying I'm fairly new to SQL Server. I'm an
> application developer who has built front ends atop Server, but have
> never done much in the way of design / development.
> My question is in two parts;
> 1). What is the best way to remotely "create" an SQL Server database?
> I've been told that this can be done with SQL Statements, but thought
> that there may be a way using XML.

I don't know whether you can create databases and change metadata from
SQLXML. It does sound a bit akward to me, though. The normal way is
to use CREATE DATABASE, CREATE TABLE and that sort of SQL Statements.

> 2). Can I use this same method to propogate updates to the remote database
> without affecting the data?

You mean changes to the metadata? Normally, the point with updates is
to affect the data? :-)

Composing an update script is a task that requires care. For simple
changes it may be as easy as a couple of ALTER TABLE statements. For
complex changes, it may be a task that requires quite some development.
Whatever you do, careful testing is needed before you do it in the
live databaase.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp