what component should I use ?

Question

Post reply

what component should I use ?

mw_sql_developer

SSCoach

Points: 19445
More actions
September 26, 2011 at 8:00 am

#245412

I have a long stored proc that needs to be deployed in 5 servers.
I tried to use "SQL task" to do this but it accepts only upto a certain number of characters ?
So then what component should I use to deploy a very long stored procs ?
My script goers like this...
----------------------------------------
Use DataExch;
Go
Create procedure abc
as
Begin
/*
-- For now please assume that the body of the stored proc has many lines and it is very long...
*/
End
GO

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

put the stored proc into a procedure and then use the variable in the task

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Not clear what you are saying ?

Are you telling me a variable can handle more lines of code ?

1.)So then put the stored proc inside the variable ?

2.)Asign the variable in the "SQL task"

steveb. SSC-Forever Points: 46733 More actions · Answer 3

mw112009 (9/26/2011)
Not clear what you are saying ?
Are you telling me a variable can handle more lines of code ?
1.)So then put the stored proc inside the variable ?
2.)Asign the variable in the "SQL task"

it can handle a lot, but as you never stated how many line your proc is i can not tell you for sure if its going to work.

Those steps seem fine.

kramaswamy SSCoach Points: 18181 More actions · Answer 4

Are you asking for a method to have SSIS create a stored procedure for you, and then run it?

Because from your original question, its not clear to me why you don't just create the stored proc on your server, and then use the Execute SQL Task to execute the stored procedure.

If you're trying to take a stored procedure which exists on one server and then move it into another server, you can find the code of the stored proc in the table sys.procedures. The query to find the code of a stored proc is:

SELECT OBJECT_DEFINITION(OBJECT_ID)

So for example what you could do is:

SELECT OBJECT_DEFINITION(OBJECT_ID)

FROM sys.procedures

WHERE [name] = 'proc_name_here'

Then to deploy that code to another server, you would probably be able to just run an EXEC command against it.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Sir

We have 6 servers and I have to deploy many stored procs in each of these servers. Doing them manually is a pain. Besides I run into situations where I have to change the stored proc(s) now and then and make sure it gets deployed in all six servers.

So having to deal with 10-15 stored procs and making sure they get to all servers is a tedious task to do manually.

That is why I need an automated solution.

I have a ssis package that loops through all the servers and then all that I do is put the stored proc code in a SQl task and it gets created.

The problem occurs when the stored proc is large ?

Hope I explained.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I would recommend trying either SQL Compare by Redgate or the free tool DBDiff from codeplex.

http://dbdiff.codeplex.com/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mw_sql_developer SSCoach Points: 19445 More actions · Answer 7

I kind of expected that someone would help me how to use the

"Trasnfer SQl Server Objects Task" component.

This might be the best way to get around the issue.

The only thing that I don' like about this component is it does not recognize existing connections. Why is that ?

Anyone familiar with this component ?

steveb. SSC-Forever Points: 46733 More actions · Answer 8

mw112009 (9/26/2011)
I kind of expected that someone would help me how to use the
"Trasnfer SQl Server Objects Task" component.
This might be the best way to get around the issue.
The only thing that I don' like about this component is it does not recognize existing connections. Why is that ?
Anyone familiar with this component ?

Your existing connections are probably OLEDB connections, the transger object task requires SMOServer connections.

you can set these up by Right-Clicking on Connection Managers and then New Connection.

Or simply click the (...) on the task editor and selet new connections.

Sorry, I would have mentioned using this task, but as it requires that the procedure exist in a db already and it didn't appear this was the case in your question.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

What is a SMOServer connection and how different is it from a OLEDB Connection ?

steveb. SSC-Forever Points: 46733 More actions · Answer 10

its a connection to the sql server object library, you can't select data from it like you would an OLEDB connection, you can only select objects.

Underneath i think it is simply a limited .NET connection and the transfer object task simply constructs the code that you could write in .NET to move objects.

drew.allen SSC Guru Points: 76985 More actions · Answer 11

mw112009 (9/26/2011)
Sir
We have 6 servers and I have to deploy many stored procs in each of these servers. Doing them manually is a pain. Besides I run into situations where I have to change the stored proc(s) now and then and make sure it gets deployed in all six servers.

You should probably be using the "sqlcommand" command line utility to run your scripts instead of putting them in SQL Tasks in SSIS. We've put ours into a batch file that takes the server name as a parameter and that parameter is then used to specify the server for the sqlcommand.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mw_sql_developer SSCoach Points: 19445 More actions · Answer 12

Interesting

I wonder whether if you could throw some sample code on how the batch file should look like.

Also how do you plan to place the stored proc on 6 servers ? Inside the batch file is there a looping mechanism that logs into each server and places the stored proc ?

drew.allen SSC Guru Points: 76985 More actions · Answer 13

Here is a sample of the batch file. You have to specify the server and database separately, so the IF statement is setting up different environment variables for each.

if "%1"=="dev" (

set server=DEV01

set AW_db=Adventure_Works_Dev

) else if "%1"=="test" (

set server=QA1

set AW_db=Adventure_Works_QA

) else if "%1"=="prod" (

set server=PRD01

set AW_db=Adventure_Works_PROD

) else (

echo "Usage: install dev|test|prod"

goto :EOF

)

set AW_args=-S %server% -d %AW_db% -E -i

sqlcmd %AW_args% Script1.sql

sqlcmd %AW_args% Script2.sql

sqlcmd %AW_args% Script3.sql

You could have another batch file call this with the separate servers.

install dev

install test

install prod

That should be enough to get you started.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mw_sql_developer SSCoach Points: 19445 More actions · Answer 14

So all that I have to do then is run this at the c:> prompt ?

C:> runsql "Dev"

The actual file is runsql.bat sitting in the C:\ root drive.