Executing on a different database.

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?

Although I always suggest not using dynamic SQL its the appropiate way to do some adhoc things:

DECLARE @.SQLText VARCHAR(MAX)
DECLARE @.DatabaseName VARCHAR(MAX)

SET @.DatabaseName = 'Mydatabase'
SET @.SQLTEXT = 'USE ' + @.DatabaseName

SELECT @.SQLTEXT = @.SQLTEXT + ' GO ' + ROUTINE_DEFINTION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE <YourFiltercriteriaHere>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Execute proceduers from another Proceduer with error handling

I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

Regard JoelCheck the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

<joel.sjoo@.gmail.comwrote in message
news:1166184223.625115.296200@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?
>
I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
>
>
Regard Joel
>

|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

Even better is this check:

IF @.Returcode <0 OR @.@.error <0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(joel.sjoo@.gmail.com) writes:

Quote:

Originally Posted by

I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

I don't relly know what this is supposed to be, but note that the first
GO marks the end of USP_RUNJOB, so the call to SP_TEST2 is not part of
that procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx