My next findings on Dynamic SQL + OpenRowSet
Aaaahhhh. I took so long to figure this out. But thanks to 2 of my colleagues sharing their debugging skills. LOL.
1) When working with Dynamic SQL and OpenRowSet, be careful with double quotes when you concatenate T-SQL and your variable.
Say NO to: // 1 quote between the variable
SET
@SQLString = 'INSERT INTO myTable ( FileName, FileType, SomeDate, Document ) ' +
'SELECT ' + @str1 + ' as FileName, ' +
@str2
+ ' as FileType, ' +
cast(@test1 as varchar(100)) + ' as SomeDate, ' +
'BulkColumn FROM OPENROWSET(BULK ' +
@varPicturePath
+ ', SINGLE_BLOB) AS Document';
Say YES to: // 3 quotes between the variables
SET @SQLString = 'INSERT INTO myTable ( FileName, FileType, SomeDate, Document ) ' +
'SELECT ''' + @str1 + ''' as FileName, ''' +
@str2
+ ''' as FileType, ''' +
cast(@test1 as varchar(100)) + ''' as SomeDate, ' +
'BulkColumn FROM OPENROWSET(BULK ''' +
@varPicturePath
+ ''', SINGLE_BLOB) AS Document';
2) Remember to convert DateTime to varchar in your dynamic sql. If normal t-sql, there is no issue.
Say NO to:
SET @SQLString = 'INSERT INTO myTable ( FileName, FileType, SomeDate, Document ) ' +
'SELECT ''' + @str1 + ''' as FileName, ''' +
@str2
+ ''' as FileType, ''' +
@test1 + ''' as SomeDate, ' +
'BulkColumn FROM OPENROWSET(BULK ''' +
@varPicturePath
+ ''', SINGLE_BLOB) AS Document';
EXEC (@SQLString)
Say YES to:
SET @SQLString = 'INSERT INTO myTable ( FileName, FileType, SomeDate, Document ) ' +
'SELECT ''' + @str1 + ''' as FileName, ''' +
@str2
+ ''' as FileType, ''' +
-- cast here
cast(@test1 as varchar(100)) + ''' as SomeDate, ' +
'BulkColumn FROM OPENROWSET(BULK ''' +
@varPicturePath
+ ''', SINGLE_BLOB) AS Document';
EXEC (@SQLString)
3) Be careful with the positions of each data you want to pass in. Don't get mix up.
Example
-- Section 1
SET
@SQLString = 'INSERT INTO myTable ( FileName, FileType, SomeDate, Document ) ' +
-- Section 2
'SELECT ''' + @str1 + ''' as FileName, ''' +
@str2
+ ''' as FileType, ''' +
cast(@test1 as varchar(100)) + ''' as SomeDate, ' +
'BulkColumn FROM OPENROWSET(BULK ''' +
@varPicturePath
+ ''', SINGLE_BLOB) AS Document';
Make sure the section 1 and section 2 orders the same.
Note: You don't have to follow exactly like the Database Table column name.
4) OpenRowSet statement must always the last ... try to avoid as below:
Say NO to:
INSERT myTable ( [FileName], FileType, SomeDate, [Document] )
SELECT 'Haha' as FileName,
'Hehe' as FileType,
BulkColumn
FROM OPENROWSET(BULK 'C:\wenching.jpg', SINGLE_BLOB) AS Document, GETDATE() as SomeDate
Say YES to:
INSERT myTable ( [FileName], FileType, SomeDate, [Document] )
SELECT 'Haha' as FileName,
'Hehe' as FileType, GETDATE() as SomeDate,
BulkColumn
FROM OPENROWSET(BULK 'C:\wenching.jpg', SINGLE_BLOB) AS Document
Phew, what a tough day battling with this problem. Hope you find it useful :) Do correct me if I am wrong. I am far more willing to learn and fix it :)
Thanks.