Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 4
  • +1
20 Comments
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 23

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 52

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 52

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 1000 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 52

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 52

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

597 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