SQL Server Performance Forum – Threads Archive

combined script issues

Hi everyone, I need your help. I have 2 sql scripts that I run manually via query analyzer. Both script does this 1. Creates a temp table and imports the data from the text file via BULK INSERT
2. Creates another table which contains a status field
3. Insert data to the 2nd table where it matches the loannumbers from the first table with a status of NO
4. Updates another production table using the table created in #1
5. Updates the status field on the 2nd table to Y if those loans where found Then when I finished the 2 scripts, I run another stored procedure which I wrote. This SP uses the 2 temp tables in Step1 above to create a text file report. It has been running without any issues. Now, I combined the 2 scripts into 1 huge script. I noticed that some lines are not being executed. The BULK INSERT doesn’t work anymore. Did some digging and I got it to work by putting GO after each query. It’s working now. However, I noticed that even if I have an error in the query, succeeding queries/transactions below the failed query will still get executed. That’s a major issue. I’m also planning to make this huge query into a stored procedure. So how do I write a proper long script? I had an impression that SQL queries are similar to Unix sh/bash scripts but I was wrong. It’s because in Unix script, when you run your .sh script, if there is an error, it goes back to prompt. Thanks!

Most important issue is whether you understand WHY the errors are occurring. It sounds like there could only be data-related errors, for instance foreign key values that do not yet exist – which in any case you must resolve before you start this merge procedure.

Hi ya, in SQL a GO determines a new batch. Batches are totally independent of each other and so an error in one will not prevent the next one from running. Statements within a batch run dependent on each other and so an error in one will abort the batch After which statements did you need to put a GO, and what were the errors that you were getting before you added the go? Cheers
Twan

Yeah, I found out that the data am importing had more characters. I fixed it. So are you saying that the process I used to combine my 2 scripts is ok? I mean, the way my scripts are laid out now is ok since it worked? If so, then can I put the combined script in a single STORED PROCEDURE even if there are many GOs?

You can’t use GO in a stored procedure. GO can be used only in scripts to separate batches, or object definitions. When you insert GO in the script for a new procedure, and you execute the script, then the procedure definition ends before the GO line. And if that truncated procedure definition would contain syntax errors, then the object creation would fail.

Oh ok. So how should I put this script inside the SP if the process is what I have right now at the bottom? 1. BULK INSERT Vendor A file 2. BULK INSERT Vendor B file 3. INSERT to temp table A 4. INSERT to temp table B 5. UPDATE PROD TABLE using data from Vendor A temp table 6. UPDATE PROD TABLE using data from Vendor B temp table 7. Run previously created stored procedure and use Vendor A and B to generate report Or should I retain my combined script, remove all the GO and put it inside the stored procedure? Or what about this: separate the 2 scripts again, then make those scripts a stored procedure. That way we’ll have 2 stored procedures. Then create the 3rd procedure that will call the 2 stored procedures. Create stored procdure as Exec sp_ParseVendorA Exec sp_ParseVendorB …… Please let me know. Thanks again! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Between those 7 statements, I don’t think you need any GO statement whatsoever. You could even put all 7 instructions on a single line and it would probably still execute without a problem. The first keyword of each statement tells SQL Server that it’s the start of a new statement. You only have to put a GO before a statement if it must be the first statement in a batch – that’s the exact error message when you do need a GO in a script. If you comment out a section of the script with /* and */ and there’s a GO line within that section, then you will see weird error messages, so comment out GO lines within that section as –GO. BOL also tells me that QA interprets GO differently from the command line osql and isql utilities, so if you’re using osql or isql then start reading BOL …

]]>