Proper syntax for string concatenation / EXEC()

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

Transact-SQL (2000)

Proper syntax for string concatenation / EXEC()

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2005-10-05 : 12:43:13

I have a need to pull data from multiple Excel books with multiple spreadsheets. I'm using a cursor to walk through each one, but I can't seem to get my syntax straight for the actual SELECT inside. The following code, where "@thestate" is my variable (AK, AL, AZ, etc.) does this:


INSERT INTO dbo.bck_county (county, state_id)
SELECT dbo.fn_cap_words(county) 'county', state
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\SQL\1.xls;Extended Properties=Excel 8.0')...@thestate
WHERE county IS NOT NULL
GROUP BY state, county

Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near '@thestate'.

So I thought I'd throw everything into a string and just EXEC the string. Again, trouble with my syntax. (code below wrapped for aesthetics/page width. SET @tempstring is all on a single line)


SET @tempstring = N'INSERT INTO dbo.bck_county (county, state_id)
SELECT dbo.fn_cap_words(county) ''county'', state
FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source=C:\SQL\1.xls;Extended Properties=Excel 8.0'')
...[' + @thestate + '$]
WHERE county IS NOT NULL GROUP BY state, county'
EXEC (@tempstring)

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'Microsoft.Jet'.

Any help with my syntax would be appreciated.
-Brian

"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914)

Kristen
Test

22859 Posts

Posted - 2005-10-05 : 13:45:30

I tend to do these multi-quin-quadruplicate

embedded quoted strings along the following lines - dunno if that's the problem, or fi this will help, but maybe it will give you an idea:


SELECT @tempstring = 'FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',
''Data Source=C:\SQL\1.xls;Extended Properties=Excel 8.0'')
...[' + @thestate + '$]'

SELECT @tempstring = N'INSERT INTO dbo.bck_county (county, state_id)
SELECT dbo.fn_cap_words(county) ''county'', state '
+ REPLACE(@tempstring, '''', '''''') -- Double up any embedded single quotes
+ ' WHERE county IS NOT NULL GROUP BY state, county'

Kristen

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2005-10-05 : 14:55:18

Thanks Kristen. I appreciate the time you took to go over my code. I ended up just putting all the data into a single sheet and importing it all into a temp table before populating my other [real] tables.

-Brian

"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources