1

I have a SQL script that works exactly as I want it to when I step through it line by line. At first, this was a viable option, but the script has gotten larger over time and stepping through it now sucks.

I've tried to run the script as part of an SSIS package in Visual Studio, but that gave me weird results.

I have a feeling this is because I have mixed language in the script, (i.e multiple UPDATE, ALTER, SELECT INTO, statements) ... Is there a way to automate this script so it runs line by line as if I were stepping through it?

be kind. and thank you!

hansolo
  • 903
  • 4
  • 12
  • 28
  • 1
    You need to commit DDL changes before moving on to the next block. I think SQL Server uses GO. https://stackoverflow.com/questions/1432757/sql-server-script-alter-procedure-executing-multiple-alter-procedure-into-one may help. GO executes all statements from the last "GO" thus if you're making changes to DDL, then the updates can be made before the next series of transactions allowing the changes to be in effect. – xQbert May 25 '17 at 14:32
  • Is this for debugging purposes? Why not split the script in multiple packages / multiple stored procedures? – Alexei - check Codidact May 25 '17 at 14:32
  • @Alexei ideally i'd like to deploy this to run automatically. I attempted to split up the script into multiple packages when I transferred to visual studio but because of the linear way I wrote the script, it didn't give me the desired outcome. – hansolo May 25 '17 at 14:35
  • @xQbert thank you for the reference – hansolo May 25 '17 at 14:35
  • 1
    Sure More on GO: here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go Just be careful because if you execute go twice with no script changes between, I believe it will run TSQL statement twice and if you're adding 1 to some value you may end up adding 2! – xQbert May 25 '17 at 14:36
  • @xQbert great advice, thank you. just to make sure I understand, would the syntax below be correct: GO ALTER TABLE x ADD COLUMN X int GO UPDATE dbo.x Set X = 'X1' GO ALTER TABLE y ADD COLUMN Y int GO UPDATE dbo.y Set X = 'Y1' – hansolo May 25 '17 at 14:42
  • 1
    I don't believe you need the first Go; and I generally try do do the DDL first then go, then do the data updates so i have fewer go's. – xQbert May 25 '17 at 14:47
  • @xQbert thanks again dude. i am going to try this out. i really appreciate you taking a second to explain – hansolo May 25 '17 at 14:49
  • 1
    I'm not so sure that GO is the only solution. I think you also are going to need to terminate each individual query with a semi-colon. – Tab Alleman May 25 '17 at 18:38
  • @TabAlleman started testing with just GO and it's been running for hours, so I am sure something else isn't quite right. I will also consider the semi-colon. thanks for adding. – hansolo May 25 '17 at 19:03
  • @TabAlleman turns out a bunch of code was not executed after 2 hours and 33 minutes of run time. I am hoping the semi-colon solves ... – hansolo May 25 '17 at 19:49

1 Answers1

2

Generally speaking. All you need is a semi-colon after each statement, this is regardless of statement types. "Go" is only needed if you want to separate it into batches (although it might not work if you are passing the code through to something else, ie. ADO.NET command object). This is helpful if you want the code to continue running regardless of the success or failure of the code before the "go". If you want the continuation of the code to depend upon the previous code just make sure to end each statement with a ";"

Austin Wagner
  • 81
  • 5
  • thank you for that. great example of how bad habits become an issue at scale. Any idea why it is taking such a longer time? It didn't take me 2 hours to step through the script manually ... – hansolo May 25 '17 at 20:03
  • still no luck. parts of the script run successfully but a number of the UPDATE statements never happen resulting in columns with no data. – hansolo May 25 '17 at 23:32
  • Have you tried setting it up as an SSIS job with a SQL script task for each statement? Or you could set it up in a Data Flow Task? Both of these might allow a little bit more visibility into where you are running into issues. After you try running it look at the output and look for any error or warning messages. – Austin Wagner May 26 '17 at 11:54
  • figured it out Austin. The semicolon actually solved the issue, I just had such a large script that I missed a couple spots the first time. Thanks again for your help. The next step for me is figuring out how to schedule the SSIS job that pulls that data and this transformation script automatically so this runs every night while I am sleeping. – hansolo May 26 '17 at 14:57
  • 1
    Excellent! I do this all the time. You can either set this kind of stuff up in Data Flow Tasks or just place the whole script in a SQL script task. I prefer using Data Flow Tasks because you can track the rows between each transformation. – Austin Wagner May 26 '17 at 15:50