Author |
Topic |
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-08-25 : 13:30:43
|
Should code be working top to bottom?I've written sql like'create tableinsert into tableselect * from table'NEVER had a problem.However I am now trying to work with dynamic code and am running into a problem, I'm using the same logic, make table, select from table, but it is failing. Why?!?! Can someone explain?To be more detailed, I am working on Microsoft SQL Managment Studio 2008. I've got me open a query.If I run this query:--drop table SCOListingsdeclare @code nvarchar(1200)declare @loc varchar(max)----MWk YESTERDAYset @loc = replace((select '''C:\SKUM\Ebay Inventory\'+(select (datename(MONTH, GETDATE()-1) +' '+ substring(CAST(getdate()-1 as varchar),5,3)+'Ebay Stuff'))+'\sco listing templates.xls'''), ' ', ' 0')set @code = N'EXEC sp_addlinkedserver ''SCO'', ''Jet 4.0'', ''Microsoft.Jet.OLEDB.4.0'', '+@loc+', NULL, ''Excel 8.0''-- SELECT * INTO SCOListings--FROM OPENQUERY(SCO, ''SELECT * FROM [Sheet1$]'') 'EXECUTE sp_executesql @codeand then this query:SELECT * FROM OPENQUERY(SCO, 'SELECT * FROM [Sheet1$]') exec sp_dropserver @server = 'SCO'I have success, but if I run them like this :--drop table SCOListingsdeclare @code nvarchar(1200)declare @loc varchar(max)----MWk YESTERDAYset @loc = replace((select '''C:\SKUM\Ebay Inventory\'+(select (datename(MONTH, GETDATE()-1) +' '+ substring(CAST(getdate()-1 as varchar),5,3)+'Ebay Stuff'))+'\sco listing templates.xls'''), ' ', ' 0')set @code = N'EXEC sp_addlinkedserver ''SCO'', ''Jet 4.0'', ''Microsoft.Jet.OLEDB.4.0'', '+@loc+', NULL, ''Excel 8.0''-- SELECT * INTO SCOListings--FROM OPENQUERY(SCO, ''SELECT * FROM [Sheet1$]'') 'EXECUTE sp_executesql @codeSELECT * FROM OPENQUERY(SCO, 'SELECT * FROM [Sheet1$]') exec sp_dropserver @server = 'SCO'failure.....WHY?!?!?!?!?!?!?!?! |
|
X002548
Not Just a Number
15586 Posts |
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-08-25 : 13:37:31
|
commits, as in comments? no its not that, removing them does nothing.as an aside, why should I not do 'maintenence' dynamically? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 13:49:42
|
no, he means commits. one statement hasn't been committed b4 executing the next.add semicolons and GO after each statementEXECUTE sp_executesql @code;GOSELECT * FROM OPENQUERY(SCO, 'SELECT * FROM [Sheet1$]') ;GOexec sp_dropserver @server = 'SCO';GO |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-25 : 13:53:07
|
Can you validate that the file path/name is correct? It might well be correct, but it just seems very odd to me.'C:\SKUM\Ebay 0Inventory\August 024 0Ebay 0Stuff\sco 0listing 0templates.xls'Also, what is the error you are receiving? |
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-08-25 : 13:53:40
|
very nice! Thank you SO much!!!!!something bad with moving data around with dynamic code? I've never had a problem. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 13:53:58
|
and post the error message |
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2010-08-25 : 14:05:00
|
everything is working find now! its the semi colins and the gos! Fixed!!!I'm not sure what I was doing with that replace function as it was written for a different line of dynamic code. But anyways it works now! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 15:57:39
|
You're welcome. Glad to help.Brett was right, it was the commits |
|
|
X002548
Not Just a Number
15586 Posts |
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 20:36:22
|
yeah, yeah...lolhey even i get em right once in while |
|
|
|