Solved

How can I structure a script so that when it runs it creates multiple stored procedures at once?

Posted on 2015-02-23
20
90 Views
Last Modified: 2015-03-03
I've built a scrip which is essentially this:
USE database1
GO
CREATE PROCEDURE proc1 
.
.
USE database2
GO
CREATE PROCEDURE proc1 
.
.
USE database3
GO
CREATE PROCEDURE proc1 
.
.

Open in new window

First off, when I run the above code, it doesn't like the "GO" after the "USE database" statement.  If I remove that particular "GO" I get a bit further, but then the following error occurs:
a USE database statement is not allowed in a procedure, function or trigger. Any ideas?
0
Comment
Question by:David L. Hansen
  • 10
  • 4
  • 4
  • +1
20 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 40627008
Place a GO after each CREATE PROCEDURE statement.

What error are you getting when you put GO after USE database1 ?  That is the correct syntax.

Documentation on GO and what it does is here (for reference purposes): https://msdn.microsoft.com/en-us/library/ms188037.aspx

Examples in the docs put a ";" after "USE Database1".

As of SQL Server 2012 (I think) - I believe that the ; is required now.

Try "USE database1;" as well

EDIT:  Syntax conventions are documented here: https://msdn.microsoft.com/en-us/library/ms177563.aspx - semi-colon's aren't required yet but will be in future versions.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40627017
Do like this

USE database2
GO
CREATE PROCEDURE proc1
.
.
GO ------------------------ here
USE database3
GO
CREATE PROCEDURE proc1
.....
GO
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40627030
Doing that seems to successfully create the procedures (thank you) however the Incorrect syntax near 'GO' error still pops up (for every CREATE PROCEDURE in the script).
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40627048
if you can put the sql script here, we should be able to help; otherwise we have guess
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40627059
Try it with ANY stored procedure and see what you get. Just a simple stored_proc with a GO statement separating each (all having a different USE database line)
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40627066
Here is one (even with a simple SELECT it gives the error):
USE [Jan2015]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO


USE [Building]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO


USE [OurTemp]
GO
select name from sys.procedures where name = 'p_ScripToAllDBs'
GO

Open in new window

0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40627079
I guess it didn't create the procs after all.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40627086
I don't see any error. Are you running this on sql server management studio ? also post the exact error
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40627094
I am using sql server management studio 2008 R2.  The error I posted above is the exact error (I just didn't put in the Msg, Level, Line).  Here is all of it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.

Have you executed a similar script to see?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40627137
Have you copied this code in from another place ?  Like a Unix / Max compliant edit and maybe there are some non printable characters there that can't be seen ?

(Long shot but let's rule it out)
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40627970
GO is only a batch separator. Isn't T-SQL keyword. By default is what is used on SSMS but can be configured so check in Tools / Options if your SSMS is really using the 'GO' as batch separator.

GO.PNG
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40628381
Already checked the "GO" batch separator; yes it is "GO" still.

Has anyone actually tried the code I posted? Just try it and see if you don't also get the problem:

USE [yourdatabase1]
GO
select name from sys.tables where name = 'a_Table'
GO


[code]USE [yourdatabase2]
GO
select name from sys.tables where name = 'a_Table'
GO


USE [yourdatabase3]
GO
select name from sys.tables where name = 'a_Table'
GO

Open in new window

I'd really like to hear what any of you get.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40628405
Yes, I've tried that code and ran without any problem.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 40628420
Can you copy the code you pasted above and run that on a new sql window. if it runs, probably you have some non printable characters. You can check them using notepad++
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40628421
Please confirm that all your SP's has a BEGIN..END block and the line after the END has a GO separating the creation of SP's.
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40628480
Here is what I found:

When I paste the "SELECT name FROM sys.tables..." code (posted above) into a fresh new window it worked! So I thought Aneesh must be correct about the special characters.  Here is what I found in notepad++.

From Notepad++This is using "show all characters." The part above the "--------" worked (in a fresh window). The part below, did not (also, in a fresh new window). I can't see anything weird in any of the characters (just carriage return's and line feed's).
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40628521
Found something!

When I use my code-generator (p_scriptToAllDB's) the resulting text, when cut and pasted will always fail. However, if that text is first pasted into a text editor (ex. notepad++) and then cut/copied from there back into sql server (of course, having made no changes to it) it works! It works even if I put it back in the same, failed, window.

I conclude from this that some special characters are generated by sql server which are not accepted by notepad++, so they simply don't paste into the editor. That's why we see nothing but CR/LF's and why when taken out of notepad and pasted back into sql server, it works.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40628535
Talking about weird things... :o)
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 0 total points
ID: 40628665
Ok. I have the final analysis here and Line feed is the cure.

My script (p_scriptToAllDBs) uses char(13) for carriage returns -- fair enough. However, it only uses char(13). That create a carriage return but NO Line Feed. Notepad++ was interpreting char(13) as CR/LF, thus adding the needed Line Feed. That's why it worked when copied from notepad++! Bottom line, if you create sql code from sql server, make sure you use char(13) + char(10). That gives you the Carriage Return AND the required Line Feed!

Now it all works perfectly.  :)
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
ID: 40641396
Thank you all!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 45
Query Syntax 17 36
Can > be used for a Text field 6 49
Merge two rows in SQL 4 17
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question