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 problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).
Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan
|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegro ups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegroups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

Bulk Insert in 2005

After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegroups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>

Bulk insert

Hi,
i'm trying to store some bitmap or jpeg files located on my harddrive into a
table of my datable which have an image type field.
while looking at different help files, it seems to be possible with bulk
insert ...
does anybody got a simple way to to such a job ?
best regards.Franck,
In version 2000, you will find a utility named textcopy.exe, installed on
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn". See if this helps.
Copy Text or Image into or out of SQL Server
http://www.databasejournal.com/feat...cle.php/1443521
AMB
"Franck Fouache" wrote:

> Hi,
> i'm trying to store some bitmap or jpeg files located on my harddrive into
a
> table of my datable which have an image type field.
> while looking at different help files, it seems to be possible with bulk
> insert ...
> does anybody got a simple way to to such a job ?
> best regards.
>
>|||Yes it helps ...
as i didn't know textcopy exists i thought bulk insert was the only way ...
thanks for your help Alejandro.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> a crit dans le
message de news: F8380DA0-0EF8-454E-B0FB-234CA7661816@.microsoft.com...[vbcol=seagreen]
> Franck,
> In version 2000, you will find a utility named textcopy.exe, installed on
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn". See if this helps.
> Copy Text or Image into or out of SQL Server
> http://www.databasejournal.com/feat...cle.php/1443521
>
> AMB
> "Franck Fouache" wrote:
>

Bulk insert

Hi,
i'm trying to store some bitmap or jpeg files located on my harddrive into a
table of my datable which have an image type field.
while looking at different help files, it seems to be possible with bulk
insert ...
does anybody got a simple way to to such a job ?
best regards.Franck,
In version 2000, you will find a utility named textcopy.exe, installed on
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn". See if this helps.
Copy Text or Image into or out of SQL Server
http://www.databasejournal.com/features/mssql/article.php/1443521
AMB
"Franck Fouache" wrote:
> Hi,
> i'm trying to store some bitmap or jpeg files located on my harddrive into a
> table of my datable which have an image type field.
> while looking at different help files, it seems to be possible with bulk
> insert ...
> does anybody got a simple way to to such a job ?
> best regards.
>
>|||Yes it helps ...
as i didn't know textcopy exists i thought bulk insert was the only way ...
thanks for your help Alejandro.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> a écrit dans le
message de news: F8380DA0-0EF8-454E-B0FB-234CA7661816@.microsoft.com...
> Franck,
> In version 2000, you will find a utility named textcopy.exe, installed on
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn". See if this helps.
> Copy Text or Image into or out of SQL Server
> http://www.databasejournal.com/features/mssql/article.php/1443521
>
> AMB
> "Franck Fouache" wrote:
>> Hi,
>> i'm trying to store some bitmap or jpeg files located on my harddrive
>> into a
>> table of my datable which have an image type field.
>> while looking at different help files, it seems to be possible with bulk
>> insert ...
>> does anybody got a simple way to to such a job ?
>> best regards.
>>