Bulk insert in dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk insert in dynamic sql

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 03:46:33

When I try and put a BULK INSERT statement (that otherwise works fine) in a string variable and execute it (because I want to build the source path up dynamically), I get this error:
Msg 7202, Level 11, State 2, Procedure MySproc, Line 51
Could not find server 'BULK INSERT MyDatabase' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Here's the line of code:
set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '+@targetPath+'source.txt WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'

This problem is referred to here under the "Linked Server Tables" heading, but I don't know what a suitable workaround is:
http://www.sommarskog.se/strict_checks.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 03:54:37

which server does MyDatabase exist? is it configured as linked server to one where you're executing above code?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 04:03:47

You should replace MyDatabase with the actual database name you use

Madhivanan

Failing to plan is Planning to fail

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 04:13:19

the database is on my local server and it is called "MyDatabase". I don't configure linked servers ever. i hate them

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 04:16:15

Are you running the query on a different server?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 04:25:44

Then add it as linked server to one where you're executing this query.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 04:37:06

quote:
Originally posted by madhivanan

Are you running the query on a different server?

Madhivanan

Failing to plan is Planning to fail

no, it's all on the same server. I dont clearly understand why it loses the server context just because you are changing it to dynamic sql.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 04:37:46

quote:
Originally posted by visakh16

Then add it as linked server to one where you're executing this query.

have you tried it yourself?
Linked servers are slow and generally bad practice. you know that right? surely there must be another way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 05:03:20

quote:
Originally posted by coolerbob

quote:
Originally posted by visakh16

Then add it as linked server to one where you're executing this query.

have you tried it yourself?
Linked servers are slow and generally bad practice. you know that right? surely there must be another way?

its indeed slow. but for BULK INSERT a file which exists in other server you need to add it as linked server using sp_addlinkedserver or use distributed queries like OPENROWSET()

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 09:08:51

it's not on another server, i've already said that. And it works fine when I don't use dynamic sql. We're going round in circles here!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 09:49:53

quote:
Originally posted by coolerbob

it's not on another server, i've already said that. And it works fine when I don't use dynamic sql. We're going round in circles here!

will your path be dynamic? also does that path represent a local path in your machine?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-10-21 : 12:45:03

the path is dynamic. Which is why i had to put the bulk insert statement in a string variable and execute it. yes the path is also on the local server

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 13:34:48

not sure what you guys are discussing.
try this,

declare @sql varchar(1000)
declare @targetPath varchar(1000)
set @targetPath='d:\booking\'
set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '+@targetPath+'source.txt WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'
exec @sql

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 13:35:47

Sorry, I meant

set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '''+@targetPath+'source.txt'' WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'

Think you are just missing the quotes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 03:10:59

quote:
Originally posted by sakets_2000

Sorry, I meant

set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '''+@targetPath+'source.txt'' WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'

Think you are just missing the quotes

That makes sense

Madhivanan

Failing to plan is Planning to fail

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2009-01-23 : 11:10:11

quote:
Originally posted by sakets_2000

Sorry, I meant

set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '''+@targetPath+'source.txt'' WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'

Think you are just missing the quotes

Thanks, I made that necessary change. But I still get the same error after that. The problem was how I executed that variable. This didn't work:
exec @sql
but this did:
exec dbo.sp_executesql @sql

I'll add "BCP in" to my list of operations that can't be done calling exec on it's own. I should be using sp_executesql as standard anyway. My own lazy fault!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:45:54

quote:
Originally posted by coolerbob

quote:
Originally posted by sakets_2000

Sorry, I meant

set @sql='BULK INSERT MyDatabase.dbo.MyTable FROM '''+@targetPath+'source.txt'' WITH (FIELDTERMINATOR = ''¦¦\t'',ROWTERMINATOR = ''¦¦\n'',KEEPNULLS,maxerrors=1,TABLOCK)'

Think you are just missing the quotes

Thanks, I made that necessary change. But I still get the same error after that. The problem was how I executed that variable. This didn't work:
exec @sql
but this did:
exec dbo.sp_executesql @sql

I'll add "BCP in" to my list of operations that can't be done calling exec on it's own. I should be using sp_executesql as standard anyway. My own lazy fault!

did you try
exec @sql
or
exec (@sql)

it should be latter

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-23 : 13:10:13

quote:
Originally posted by coolerbob
I'll add "BCP in" to my list of operations that can't be done calling exec on it's own. I should be using sp_executesql as standard anyway. My own lazy fault!

This is not true. Visakh's post explains how to use exec. You should be fine once you have that in your code.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2009-01-26 : 03:47:00

quote:
Originally posted by sakets_2000

quote:
Originally posted by coolerbob
I'll add "BCP in" to my list of operations that can't be done calling exec on it's own. I should be using sp_executesql as standard anyway. My own lazy fault!

This is not true. Visakh's post explains how to use exec. You should be fine once you have that in your code.

It's still a better way of doing it though. Ken Henderson: "The moral of the story is this: sp_executesql is generally a more efficient (and therefore faster) method of executing dynamic SQL than EXEC()."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources