CREATE proc spImportRegData

AS

CREATE TABLE #T
(o varchar(8000))
insert into #T
EXEC MASTER..xp_cmdshell 'dir D:\Site\log\*.rpt'
delete from #T WHERE o not like '%batch_registration%'
delete from #T WHERE o IS NULL
select right(o,45) from #T

DECLARE @filename varchar(100)
DECLARE @sql varchar(5000)

DECLARE fileCursor Cursor for
SELECT right(o,45) AS o FROM #T
OPEN fileCursor

FETCH NEXT FROM fileCursor INTO @filename
while @@fetch_status=0
Begin
SET @sql = 'BULK INSERT adp2sql.dbo.[RegisteredSubscribers] FROM ''D:\Site\log\' + @filename + ''' WITH (FIELDTERMINATOR =''|'',ROWTERMINATOR =''\n'')'
print @sql
exec @sql
FETCH NEXT FROM fileCursor INTO @filename
END
Close fileCursor
Deallocate fileCursor
-----------------------------------------------------

The error i get is;

(9 row(s) affected)


(5 row(s) affected)


(3 row(s) affected)


(1 row(s) affected)

BULK INSERT adp2sql.dbo.[RegisteredSubscribers] FROM 'D:\Site\log\batch_registration_update_20040521_20 5002.rpt' WITH (FIELDTERMINATOR ='|',ROWTERMINATOR ='\n')
Server: Msg 203, Level 16, State 2, Procedure spImportRegData, Line 25
The name 'BULK INSERT adp2sql.dbo.[RegisteredSubscribers] FROM 'D:\Site\log\batch_registration_update_20040521_20 5002.rpt' WITH (FIELDTERMINATOR ='|',ROWTERMINATOR ='\n')' is not a valid identifier.


Can anyone help? i have been battling with this and i cant seem to see a way round it...

thanks

try replacing

exec @sql

with

exec sp_sqlexec @sql

when you execute a TSQL string you should wrap that @sql variable with brackets () like...

Exec (@sql)

This is what sp_sqlexec does.

But this is not the same with executing a stored proc.

Cheers!

_________________________
-Vijay G
Strive for Perfection