June 5, 2014 at 7:30 am
Hi
I have a really big stored proc that needs to be rolled out to various databases as part of db installs I run through SSIS. The Stored proc is too long to run using Execute SQL Task. Is there another way that just running the create script manually.
June 5, 2014 at 7:41 am
I wasn't aware there's a maximum length of script for the Execute SQL Task. What is it, as a matter of interest?
One alternative is to set up a central management server, register all the servers you want to create the stored procedure on, then execute the script once to create it.
John
Edit - corrected typo.
June 5, 2014 at 7:44 am
Is it an option to store the statement in a file and use that in an Execute SQL Task.
(not sure it beats the limit though)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 7:54 am
Hi
I am not sure what the exact limit it, test it by copying and pasting scripts, then it cuts off at some point. Not sure if the limit has been increase in 2014, we are still running on 2012.
Unfortunately this is not a central server as such, w copy the packages onto new client servers and then run them there. Looks like the best option for now would be to run this as a stand-alone script. The other option possibly would be to restore a database onto the server which only contains this stored proc and then transfer the stored proc
June 5, 2014 at 7:55 am
Koen
How exactly would you do that?
June 5, 2014 at 8:06 am
Andre 425568 (6/5/2014)
Unfortunately this is not a central server as such
Not sure which server you're referring to, but you can set up any old server as a CMS (has to be SQL Server 2008 or later, I think, and probably a version that's no older than the servers it's managing). Then just register all the target servers, right-click on the CMS name and choose New Query, paste your SP definition in, press F5, and bang! - all done.
John
June 5, 2014 at 8:45 am
I used to do this kind of thing using osql and bat files to deploy to multiple servers before we had Central Management servers. For example you could create a .bat file and include the same command multiple times just changing the server name.
e.g.
sqlcmd -S servername1 -E -i sqlfile.sql
sqlcmd -S servername2 -E -i sqlfile.sql
Koen has already mentioned that you can use a file in the Execute SQL Task component. To do this change the SQLSourceType to File connection. You will also need a File Connection Manager that points to the file containing the stored procedure.
June 5, 2014 at 4:05 pm
You could also try creating a script task and writing C# or VB code to connect to the DB and execute the command. Not sure if the limitation would apply there.
June 5, 2014 at 11:57 pm
Thanks to both of the options, I think this is the route I will take, will try both of them and see which one works best for me
June 6, 2014 at 12:10 am
Andre 425568 (6/5/2014)
KoenHow exactly would you do that?
You can store the statement in a .sql file and create a file connection to it in SSIS.
In the Execute SQL Task, you can choose to load the statement from the file connection.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply