Bulk insert in SQL Server 2000

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk insert in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-26 : 09:27:24

Mona Puri writes "I have a procedure that does bulk insert. The procedure (attached below) compiles fine in SQL Server 7.0. The same procedure does not compile in SQL Server 2000. See below for the procedure.

CREATE procedure LoaBase
@pathname varchar(128)

as
SET QUOTED_IDENTIFIER OFF
set nocount on

declare @sqlstmt varchar(2000)
declare @sqlstmt1 varchar(2000)
declare @pathname1 varchar(128)
set @pathname1 = @pathname

set @pathname = @pathname + '\' + 'manufact.txt'
set @sqlstmt = "BULK INSERT [manufacturers] FROM '"+@pathname+"' WITH (FIELDTERMINATOR = '|', firstrow = 1, ROWTERMINATOR = '\n' )"
exec(@sqlstmt)

set @pathname1 = @pathname1 + '\' + 'carlines.txt'
set @sqlstmt1 = "BULK INSERT carlines FROM '"+@pathname1+"' WITH (FIELDTERMINATOR = '|', firstrow = 1, ROWTERMINATOR = '\n' )"
exec(@sqlstmt1)

grant execute on LoaBase to public

The error I get:

Server: Msg 207, Level 16, State 3, Procedure LoaBase, Line 15
Invalid column name 'BULK INSERT [manufacturers] FROM ''.
Server: Msg 207, Level 16, State 1, Procedure LoaBase, Line 15
Invalid column name '' WITH (FIELDTERMINATOR = '|', firstrow = 1, ROWTERMINATOR = '\n' )'.
Server: Msg 207, Level 16, State 1, Procedure LoaBase, Line 19
Invalid column name 'BULK INSERT carlines FROM ''.
Server: Msg 207, Level 16, State 1, Procedure LoaBase, Line 19
Invalid column name '' WITH (FIELDTERMINATOR = '|', firstrow = 1, ROWTERMINATOR = '\n' )'.

Thanks,
Mona"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-26 : 09:28:07

Change your double quotes (") to single quotes (') and it should fix the problem.