BULK INSERT / set quoted_identifier off

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 / set quoted_identifier off

Author

Topic

jimjamjo
Starting Member

10 Posts

Posted - 2005-07-14 : 14:49:44

My next hurdle is set quoted_identifier off!!!!

I'm trying to crib from an article in this website:
http://www.sqlteam.com/item.asp?ItemID=3207
but it won't let me put set quoted_identifier off before the create procedure. When I try it within the procedure as below, I get:
Invalid column name 'BULK INSERT #temp FROM ''.
Invalid column name '' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = ':|') '.

CREATE PROCEDURE sp_AddOrders (@filename VARCHAR(200),@Business INT)
AS
DECLARE @SQL VARCHAR(2000)
set quoted_identifier off

CREATE TABLE #Temp (Field1 CHAR(25), Field2etc CHAR(25))

SET @SQL = "BULK INSERT #temp FROM '"+@filename+"' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = ':|') "

EXEC (@SQL)
SELECT * FROM #TEMP

RETURN

Any ideas please as my fingers are too numb to keep searching the web!!!

jimjamjo
Starting Member

10 Posts

Posted - 2005-07-15 : 02:31:04

Fixed it myself with a little function .... eventually :(

CREATE FUNCTION dbo.QuoteId (@filename varchar(2000))

RETURNS VARCHAR(2000)
AS
BEGIN
declare @sql varchar(2000)
set @sql = "BULK INSERT #temp FROM '"
SET @sql = @sql + @filename
set @sql = @sql + "' WITH (FIELDTERMINATOR = '\t', FIRSTROW=2, ROWTERMINATOR = ':|') "
RETURN @SQL
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources