September 26, 2011 at 1:29 pm
You don't need the quotes. (In fact, I think the quotes cause problems.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 26, 2011 at 1:35 pm
There is another option. Use a package scoped string variable, and load that variable as the result from a T-SQL query that calls the definition from sysmodules.
Then, in the executeSQL task that's in your looper/connection switcher, under SQLSourceType use 'variable'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 1:42 pm
I like the idea of using a variable ?
Ahh but you are sayng to load that variable I will have to use some special sql that will output the definition of the stored procedure that I plan to distribute ?
September 26, 2011 at 2:00 pm
mw112009 (9/26/2011)
I like the idea of using a variable ?Ahh but you are sayng to load that variable I will have to use some special sql that will output the definition of the stored procedure that I plan to distribute ?
Ayup. Setup the Execute SQL object set ResultSet (first tab) to 'single row'. Use a statement similar to the following:
select [definition] AS ProcDef
from
sys.sql_modules AS sm
JOIN
sys.sysobjects AS o
ono.id = sm.object_id
WHERE
o.[name] = '<ProcNameHere>'
AND o.[type] = 'P'
On the Result Set tab, add a new entry, Set Result name as ProcDef, and set it to your string variable.
Use that string variable in another ExecuteSQL task.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 2:03 pm
Just curious, any reason you're not using sys.procedures with OBJECT_DEFINITION instead Evil Craig F?
September 26, 2011 at 2:07 pm
Sorry,
It did not work.
Reason: My stored proc is very long. So when it outputs it puts only the code to a certain length. After that I don't see the rest of the stored proc.
However, this method is good for short stored procedures.
September 26, 2011 at 3:10 pm
kramaswamy (9/26/2011)
Just curious, any reason you're not using sys.procedures with OBJECT_DEFINITION instead Evil Craig F?
nVARCHAR(4000) vs. nVARCHAR(Max) in the view definitions. It's possible to cut off the create statement prematurely with sys.procedures.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 3:28 pm
mw112009 (9/26/2011)
Sorry,It did not work.
Reason: My stored proc is very long. So when it outputs it puts only the code to a certain length. After that I don't see the rest of the stored proc.
However, this method is good for short stored procedures.
Can you tell me your exact error? Since you posted this I figured I'd go test it directly instead of giving instructions off the cuff and the error has nothing to do with length. What did you find particularly?
You are in 2k5 SSIS, also, right?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 4:28 pm
Evil Kraig F (9/26/2011)
mw112009 (9/26/2011)
Sorry,It did not work.
Reason: My stored proc is very long. So when it outputs it puts only the code to a certain length. After that I don't see the rest of the stored proc.
However, this method is good for short stored procedures.
Can you tell me your exact error? Since you posted this I figured I'd go test it directly instead of giving instructions off the cuff and the error has nothing to do with length. What did you find particularly?
You are in 2k5 SSIS, also, right?
Oh. My. God. Don't go here. This is article worthy to walk you through the steps. Try the batch file. The necessary methods for dealing with any BLOB coming up to SSIS and trying to use the data is inane.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2011 at 7:09 am
Reply to Craig:
Ok, hers is what I am saying: I ran the following SQl in the SQL server management studio:
select [definition] AS ProcDef, LEN([definition]) as 'len'
from
sys.sql_modules AS sm
JOIN
sys.sysobjects AS o
on o.id = sm.object_id
WHERE
o.[name] = 'tsp_fin_dashboard_1_get_data_for_export_to_global'
AND o.[type] = 'P'
2.) The length = 23134
3.) Can a variable in SSIS handle a stored proc that is this long ?
4.) Then I copied the result set and pasted it in the SQL management studio window. I found that only part of my stored proc was present.
So then is it worthwhile the effort of trying your method when we can't
get the entire definition of the stored proc to appear in the result set ?
September 27, 2011 at 7:14 am
mw112009 (9/27/2011)
Reply to Craig:Ok, hers is what I am saying: I ran the following SQl in the SQL server management studio:
select [definition] AS ProcDef, LEN([definition]) as 'len'
from
sys.sql_modules AS sm
JOIN
sys.sysobjects AS o
on o.id = sm.object_id
WHERE
o.[name] = 'tsp_fin_dashboard_1_get_data_for_export_to_global'
AND o.[type] = 'P'
2.) The length = 23134
3.) Can a variable in SSIS handle a stored proc that is this long ?
4.) Then I copied the result set and pasted it in the SQL management studio window. I found that only part of my stored proc was present.
So then is it worthwhile the effort of trying your method when we can't
get the entire definition of the stored proc to appear in the result set ?
A variable can handle that length, but an expression can't,
September 27, 2011 at 7:16 am
I wonder, if you had linked servers between all of your servers in question, and they all used the same naming convention, if you could just use those to implement the solution.
Ex:
Server1 has the stored proc which you want to deploy on to Server2. Server2 has a linked server called "SERVER1", pointing to Server1.
On Server2, you run the query along the lines of:
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ''
select @SQL = [definition] AS ProcDef
from
SERVER1.Database.sys.sql_modules AS sm
JOIN
SERVER1.Database.sys.sysobjects AS o
on o.id = sm.object_id
WHERE
o.[name] = 'tsp_fin_dashboard_1_get_data_for_export_to_global'
AND o.[type] = 'P'
EXEC (@SQL)
If that would work, then in SSIS all you would need to do is run an execute SQL statement for each of your servers which you want to move the stored procedure to.
September 27, 2011 at 7:29 am
Sorry, I take that back!
Please disregard the previous reply.
The query did manage to split out the entire stored procedure.
So let me give it a try and let you know the results.
I think it should work. Thanks....
September 27, 2011 at 12:53 pm
mw112009 (9/27/2011)
Sorry, I take that back!Please disregard the previous reply.
The query did manage to split out the entire stored procedure.
So let me give it a try and let you know the results.
I think it should work. Thanks....
The problem comes in when the string is longer then 8192 characters long, and you need to use an object as the target variable. Only problem is there's no way to get at the string that's hiding inside the blasted thing. Not through VB, not through code... nothing. You need to actually do a full recordset drop then loop the object to kick it out to a string.
It might be worth it, but I'm not sure how to explain it without a few dozen pictures.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2011 at 1:19 pm
Cool - just tested my approach and it seems to work:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Kiran Ramaswamy
-- Create date: Sept. 27, 2011
-- Description:Transfer a stored proc from a remote server to the local one
-- =============================================
-- EXEC [sp_TransferStoredProc] 'KIRAN_NC0275CON01', 'SFD_GestionEffectifs', 'usp_GetUserRights'
CREATE PROCEDURE [sp_TransferStoredProc]
(
@LinkedServerName NVARCHAR(MAX),
@LinkedServerDBName NVARCHAR(MAX),
@LinkedServerProcName NVARCHAR(MAX)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TotalSQL NVARCHAR(MAX)
SET @TotalSQL = ''
SET @TotalSQL = '
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '''';
SELECT @SQL = [definition]
FROM [' + @LinkedServerName + '].[' + @LinkedServerDBName + '].sys.sql_modules AS sm
JOIN [' + @LinkedServerName + '].[' + @LinkedServerDBName + '].sys.sysobjects AS o ON o.id = sm.object_id
WHEREo.[name] = ''' + @LinkedServerProcName + '''
AND o.[type] = ''P'';
EXEC (@SQL);'
EXEC (@TotalSQL)
END
GO
So all that you need to do is ensure that you have a linked server setup between the different servers. If you have several servers, you could create a table that stores the names of the linked servers, and then do a FOREACH loop across them.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply