Queries in DTS 1

[Catadmin]

This may be a silly question, but I'm going to ask it anyway.

Can you put multiple T-SQL queries separated by GO in a DTS job step or do you have to put each query in a separate step?

My example is to do the following:

Use DB1
GO
Truncate Table TableA
GO
Insert TableA (Various column names)
(Select * from TableB)
GO

Please let me know if I need to include more info on this example.

Thanks in advance!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

You can use GO to put multiple steps in one job step - I have used this before a number of times

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Viele Danke!

Thanks Muchly!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gradley]

dbomrrsm is correct that you can put multiple TSQL statements in a DTS package step, but you can't use the GO command in DTS since this is specific to Query Analyzer and is not recognized as a TSQL command. As for the USE DB1, I don't believe you can do that in a DTS Execute SQL task either. You won't need to, since the connection for the task will already point to the correct database.

 

[Catadmin]

If I can't use GO (and that would explain the error I got yesterday trying to execute my package), how do I separate the T-SQL commands in a DTS package?

Thanks!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gradley]

You just separate them logically or by using separate Execute SQL tasks. The following should work fine:

Truncate Table TableA

Insert TableA (Various column names)
(Select * from TableB)

 

[Catadmin]

Okay. Thank you for verifying that for me.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

I put go inbetween statements in a number of SQL Tasks and they run fine.

I guess that gradley may be right but you can use them although they get ignored by DTS - however - the point is that the SQL Task with them in runs !!!

what error message did you get when you ran the dts ? I think again gradley may be right here that it was the USE statement that failed the package.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Honest, I didn't get any error messages. The task sat and thought for about 3 minutes before it failed out and ended. It told me it failed on step 1, but I couldn't find further details. I believe I tried it with and without the USE DB statements, but I'll try that again and see if it works or not.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gradley]

I stand corrected!

If you read about GO in BOL, it states it can be used with isql, osql and Query Analyzer but is not a valid TSQL command.

I just looked up the DTS "Execute SQL Task" in BOL and it states that you CAN use GO commands to separate batches. I seem to think there were issues with GO in the past but appearantly that is not the case.

Thanks dbomrrsm for enlightening me!!

 

[dbomrrsm]

I remember having probelms running multiple statements in an SQL Task but couldnt remember if it was because of GO or a lack of GO - I had to check an existing package to see one that either used GO or didnt - found ones that did so assume the problem I had was without them.

As it happens I think - if I remember right - without the GO it only executed the first statement - but dont quote me on that.

Hope its sorted now !!!

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Pattycake245]

I have a question on this topic. I have created a execute SQL task to truncate and do an insert but when I try to connect this task to my sql connection and do a "on completion" workflow thing it gives me the error:

"Defining precedences between the selected items is not valid"

What does this mean?

thanks, Tim

 

[Pattycake245]

Oh found another post with this message, thanks anyway