July 23, 2011 at 9:04 am
Hello All,
I am not sure this is right section, but thought I am DBA and using Bulk load thought I can ask other DBAs.
I have a situation to load tons of documents(all types) into varbinary(max) field. I am using using following code in Cursor to load thousands of documents. It works perfect until it hits with filename with characters like & ' etc. I have no option to change file names since they are in tens of thousands. I must force SQL to accept those names. How can I achieve this. I tried to load through SSIS, you have to use expression in SQLExecuteTask. It also faces same issue.
SELECT @SQLstr = ''
SELECT @SQLstr = 'INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT ''' + @OrignalFileName + ''',
''' + @FileName + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''',
*
FROM OPENROWSET(BULK ''C:\DOCS\'+ @FileName + ''', SINGLE_BLOB) AS imagesource'
EXEC (@SQLstr)
This is the way final SQL looks
INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT '07/14/2011 11:57 AM 99,840 sley_Lisa_sley'sCustomerService&Admin_1.doc',
'Woosley_Lisa_LisaWoosley'sResumeCustomerService&Admin_1.doc',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK 'C:\DOCS\sley_Lisa_sley'sCustomerService&Admin_1.doc', SINGLE_BLOB) AS imagesource
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '&'.
July 23, 2011 at 9:17 am
This is a duplicate post. Please post replies to:
http://www.sqlservercentral.com/Forums/Topic1147081-392-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply