October 14, 2011 at 10:36 am
HI,
I am unable to resolve few loopholes in the Proc
1. Unable to Handle error inside the Try...Catch
2. Unable to Execute queries in 2 Databases
Request your suggestions.
IF EXISTS (SELECT 1 FROM SYSObjects WHERE Name = 'pExecuteQueries_MaintainLogs' AND Type = 'P' and uid=user_id('dbo'))
BEGIN
DROP PROCEDURE [dbo].[pExecuteQueries_MaintainLogs]
END
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pExecuteQueries_MaintainLogs]
(@source int)
AS
BEGIN
SET NOCOUNT ON
If (@source is null) --== Handle code in case execution for all the sources
BEGIN
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @SOURCEID INT
DECLARE SOURCEID CURSOR FOR SELECT SOURCEID FROM RULE_DB.DBO.DEFSOURCE; -- Capture sources from Defsource
OPEN SOURCEID;
FETCH NEXT FROM SOURCEID INTO @SOURCEID;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
TRUNCATE TABLE MIGRATION_DB.DBO.QUERIES
EXECUTE sp_executesql N'USE Rule_DB' --- Execute in Rule_DB database
EXEC [DPPEngine] 'MIGRATION_DB','Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1','v4.0', @SOURCEID
END TRY
BEGIN CATCH
EXEC [pERROR_LOG_PROC]
END CATCH
PRINT 'THE C-I SCRIPT FOR SOURCEID = ' + CAST(@SOURCEID AS VARCHAR) + ' IS BEING UPLOADED'
--===== Declare cursor to fetch queries from Queries table and execute Step-By-Step
DECLARE SUPERB CURSOR FOR SELECT QUERY FROM MIGRATION_DB.DBO.QUERIES WHERE QUERY NOT LIKE 'GO' ORDER BY ID
OPEN SUPERB
FETCH NEXT FROM SUPERB INTO @QUERY
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT @QUERY
BEGIN TRY
EXECUTE sp_executesql N'USE Migration_DB' ---- Need to execute this in Migration_DB DB
EXECUTE SP_EXECUTESQL @QUERY
END TRY
BEGIN CATCH
EXEC [pERROR_LOG_PROC]
END CATCH
FETCH NEXT FROM SUPERB INTO @QUERY
END
DEALLOCATE SUPERB
CLOSE SUPERB
END
END
--============== Handle Code when SourceID is provided in The Procedure
Else
BEGIN
TRUNCATE TABLE MIGRATION_DB.DBO.QUERIES
BEGIN TRY
EXECUTE sp_executesql N'USE Rule_DB'
EXEC [DPPEngine] 'MIGRATION_DB','Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1','v4.0', @source
END TRY
BEGIN CATCH
END CATCH
PRINT 'THE C-I SCRIPT FOR SOURCEID = ' + CAST(@SOURCE AS VARCHAR) + ' IS BEING UPLOADED'
DECLARE SUPERB CURSOR FOR SELECT QUERY FROM MIGRATION_DB.DBO.QUERIES WHERE QUERY NOT LIKE 'GO' ORDER BY ID
OPEN SUPERB
FETCH NEXT FROM SUPERB INTO @QUERY
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT @QUERY
BEGIN TRY
EXECUTE sp_executesql N'USE Migration_DB'
EXECUTE SP_EXECUTESQL @QUERY
END TRY
BEGIN CATCH
EXEC [pERROR_LOG_PROC]
END CATCH
FETCH NEXT FROM SUPERB INTO @QUERY
END
DEALLOCATE SUPERB
CLOSE SUPERB
END
END
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 14, 2011 at 12:03 pm
Am I missing some information or is it Friday that is keeping people busy ?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 14, 2011 at 12:11 pm
Your post wasn't showing up on the active threads.
You can't quite do that the way your are trying. Dynamic sql runs in its own batch so when you execute your 'use dbname' the next line of code will be in the context of the database where you started this. To accomplish this the way you are trying you will have to execute the 'use statement' AND the stored proc call via dynamic sql.
Similar to this;
EXECUTE sp_executesql N'USE Rule_DB; EXEC [DPPEngine] ''MIGRATION_DB'',''Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1'',''v4.0'', @SOURCEID'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 12:11 pm
If you are getting errors, it would help to know the errors for one.
October 15, 2011 at 12:36 am
Sean Lange (10/14/2011)
Your post wasn't showing up on the active threads.
EXECUTE sp_executesql N'USE Rule_DB; EXEC [DPPEngine] ''MIGRATION_DB'',''Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1'',''v4.0'', @SOURCEID'
Sean, Thanks for your help.
The ";" after Rule_DB is making @Sourceid [Inaccessible]
DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY = N'SELECT 1'
EXECUTE sp_executesql N'USE Migration_DB; EXEC @QUERY'
Gives Error @Query is not declared'
Please suggest.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 15, 2011 at 12:41 am
Lynn Pettis (10/14/2011)
If you are getting errors, it would help to know the errors for one.
Lynn,
1.the DPPEngine Proc is creating queries and storing into Queries table.
2.Superb cursor is executing these generated queries step-by-step.
3.I want to catch Errors while executing DPPEngine
4. I also want to catch run time error for queries within that Cursor.
Can you please eloborate a bit what you are suggesting.
Thanks
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 15, 2011 at 7:44 am
SQL_By_Chance (10/15/2011)
Sean Lange (10/14/2011)
Your post wasn't showing up on the active threads.
EXECUTE sp_executesql N'USE Rule_DB; EXEC [DPPEngine] ''MIGRATION_DB'',''Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1'',''v4.0'', @SOURCEID'
Sean, Thanks for your help.
The ";" after Rule_DB is making @Sourceid [Inaccessible]
DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY = N'SELECT 1'
EXECUTE sp_executesql N'USE Migration_DB; EXEC @QUERY'
Gives Error @Query is not declared'
Please suggest.
Your variable is outside the string.
DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY = N'SELECT 1'
EXECUTE sp_executesql N'USE Migration_DB; EXEC ' + @QUERY
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2011 at 8:40 am
Sean Its not working for me ?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 15, 2011 at 8:13 pm
Are you getting an error message? "Not working for me" doesn't give me much to go on. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2011 at 11:47 pm
Sean Lange (10/15/2011)
Are you getting an error message? "Not working for me" doesn't give me much to go on. 😀
If I execute
DECLARE @QUERY NVARCHAR(MAX)SET @QUERY = N'SELECT 1'EXECUTE sp_executesql N'USE Migration_DB; EXEC ' + @QUERY
In SSMS it says : "Error near + @Query"
Thanks
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 16, 2011 at 12:43 am
You can't put expressions as a parameter to a procedure. It has to be a literal or a variable only. So the call to sp_executesql must be just @Query. Hence do all the build up before calling sp_executesql
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply