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
 General SQL Server Forums
 New to SQL Server Programming
 Order of Actions WHAT?!?!?!

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 table
insert into table
select * 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 SCOListings
declare @code nvarchar(1200)
declare @loc varchar(max)


----MWk YESTERDAY

set @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 @code

and 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 SCOListings
declare @code nvarchar(1200)
declare @loc varchar(max)


----MWk YESTERDAY

set @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 @code

SELECT *
FROM OPENQUERY(SCO, 'SELECT * FROM [Sheet1$]')

exec sp_dropserver @server = 'SCO'


failure.....

WHY?!?!?!?!?!?!?!?!

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-25 : 13:36:02
probably has to do with Commits

In any case...you should not do this "maintenance" dynamically

MOO



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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?
Go to Top of Page

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 statement

EXECUTE sp_executesql @code;
GO
SELECT *
FROM OPENQUERY(SCO, 'SELECT * FROM [Sheet1$]') ;
GO
exec sp_dropserver @server = 'SCO';
GO
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 13:53:58
and post the error message
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-25 : 18:57:04
quote:
Originally posted by russell
Brett was right



Now...that's starting to get old...;-)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 20:36:22
yeah, yeah...lol

hey even i get em right once in while
Go to Top of Page
   

- Advertisement -