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
82 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I guess it didn't create the procs after all.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Talking about weird things... :o)
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 0 total points
Comment Utility
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
Comment Utility
Thank you all!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now