October 15, 2008 at 5:24 pm
Hi guys,
I have the following stored procedure. It runs fine only that I cannot capture for some reason the output from EXEC sp_executesql statements.
USE Database
GO
IF EXISTS (select * from sysobjects where name = 'sp_ImportCPFData')
DROP PROCEDURE dbo.sp_ImportCPFData
GO
CREATE PROCEDURE sp_ImportCPFData
@desired_datesysname = NULL
AS
IF @desired_date=NULL or ISDATE(@desired_date)=0
BEGIN
RAISERROR(50001,16,-1,@desired_date)
RETURN(1)
END
SET NOCOUNT ON
DECLARE @clk_fund_stmtnvarchar(4000)
DECLARE @clk_fundINT
DECLARE @clk_bill_stmtnvarchar(4000)
DECLARE @clk_billINT
DECLARE @ocs_fund_stmtnvarchar(4000)
DECLARE @ocs_fundINT
DECLARE @emp_bill_stmtnvarchar(4000)
DECLARE @emp_billINT
DECLARE @emp_fund_stmtnvarchar(4000)
DECLARE @emp_fundINT
DECLARE @monthnvarchar(2)
DECLARE@yearnvarchar(4)
DECLARE @date_formattednvarchar(10)
DECLARE@date_textnvarchar(20)
DECLARE @agg_contextnvarchar(19)
DECLARE @agg_spacenvarchar(17)
DECLARE@spacedefnvarchar(16)
DECLARE @event_typenvarchar(18)
DECLARE @ParmDefinition nvarchar(100)
--Normalizing the @desired_date entry by converting it to YYYY-MM-DD format and eliminating the time part
SET @date_formatted=(convert(nvarchar(10), (SELECT DATEADD(day, 0, (DATEDIFF(day, 0, convert(smalldatetime, @desired_date,120))))),121))
-- Extracting the @month, @day and double-digit them
SET @month= CAST(DATEPART(month,@date_formatted) AS nvarchar(2))
SET @month= RIGHT('0'+ @month,2)
SET @year= CAST(DATEPART(year,@date_formatted) AS nvarchar(4))
SET @agg_context= 'agg_context_' + @year + '_' + @month;
SET @agg_space= 'agg_space_' + @year + '_' + @month
SET @spacedef= 'spacedef_' + @year + '_' + @month
SET @event_type= 'event_type_' + @year + '_' + @month
SET @clk_fund_stmt= '.......'
-- SELECT @clk_fund_stmt
SET @clk_bill_stmt= '........'
-- SELECT @clk_bill_stmt
SET @ocs_fund_stmt = '........'
--SELECT @ocs_fund_stmt
SET @emp_bill_stmt = '........'
--SELECT @emp_bill_stmt
SET @emp_fund_stmt = '........'
-- SELECT @emp_fund_stmt
SET @ParmDefinition = N'@resultOUT INT OUTPUT';
BEGIN
BEGIN TRY
EXECUTE sp_executesql
@clk_fund_stmt,
@ParmDefinition,
@resultOUT=@clk_fund OUTPUT
EXECUTE sp_executesql
@clk_bill_stmt,
@ParmDefinition,
@resultOUT=@clk_bill OUTPUT
EXECUTE sp_executesql
@ocs_fund_stmt,
@ParmDefinition,
@resultOUT=@ocs_fund OUTPUT
EXECUTE sp_executesql
@emp_bill_stmt,
@ParmDefinition,
@resultOUT=@emp_bill OUTPUT
EXECUTE sp_executesql
@emp_fund_stmt,
@ParmDefinition,
@resultOUT=@emp_fund OUTPUT
SELECT @clk_fund, @clk_bill, @ocs_fund, @emp_bill, @emp_fund
INSERT INTO
dbo.f_cpf_data (date_created, clk_fund, clk_bill, ocs_fund, emp_bill, emp_fund)
VALUES
(@date_formatted, @clk_fund, @clk_bill, @ocs_fund, @emp_bill, @emp_fund);
COMMIT TRANSACTION t1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrProcedureName nvarchar(500), @ErrServerity INT
DECLARE @Subject nvarchar(4000), @Body nvarchar(4000), @Recipients nvarchar(4000)
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrServerity = ERROR_SEVERITY(), @ErrProcedureName=ERROR_PROCEDURE()
SET @Recipients = 'dwarehouse@sellingsource.com'
SET @Body = @ErrMsg
SET @Subject= ERROR_PROCEDURE()+ ' on ' + HOST_NAME() + ' at ' + CAST(CURRENT_TIMESTAMP as char(20))
--EXEC msdb.dbo.sp_send_dbmail
--@recipients = @Recipients,
--@body = @Body,
--@subject = @Subject;
RAISERROR(@ErrMsg,@ErrServerity,1)
END CATCH
END
When the whole thing reaches the "SELECT @clk_fund, @clk_bill, @ocs_fund, @emp_bill, @emp_fund" (which is place for debugging purposes) all I get is some freakin' NULLs. The output looks this way:
1686
204548
144
1863
13
NULL NULL NULL NULL NULL
So basically I need to capture the output into those 5 variables and use them for an insert into some tables. The capture part fails. Thanks in advance for the help.
October 16, 2008 at 5:39 am
I'd suggest checking the variable that's being executed as the query, @clk_fund_stmt (and the others). Make sure that's structured correctly. If you're getting nulls, it's originating there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2008 at 11:56 am
Well, yes, that's a good idea. However, first I should start with a Visual Studio Ent. debugger, which I don't have.
October 16, 2008 at 11:59 am
You can use a PRINT command to put it out into the Messages window.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2008 at 12:43 pm
I think I start to have a glimpse of what's going on. I created a smaller SP for testing and the begin try - end try block looks like this:
BEGIN
BEGIN TRY
BEGIN TRANSACTION t1
SET @ParmDefinition = N'@resultOUT INT OUTPUT';
SET @clk_fund_stmt= 'BEGIN TRANSACTION t2; SELECT ISNULL((SELECT * FROM openquery(spreporting,''
SELECT
(SELECT SUM(total_count)
FROM clk_sp2_data.' + @agg_space + ' a
JOIN clk_sp2_data.' + @spacedef + ' b
ON a.space_id = b.space_id
JOIN clk_sp2_data.' + @event_type + ' c
ON a.event_type_id = c.event_type_id
WHERE page_id in (39121,39413,1807,17212,39417)
AND date_occurred = ''''' + @date_formatted + '''''
AND event_type_key = ''''funded'''' )
-
(SELECT SUM(total_count)
FROM clk_sp2_data.' + @agg_space + ' a
JOIN clk_sp2_data.' + @spacedef + ' b
ON a.space_id = b.space_id
JOIN clk_sp2_data.' + @event_type + ' c
ON a.event_type_id = c.event_type_id
WHERE page_id in (39121,39413,1807,17212,39417)
AND date_occurred = ''''' + @date_formatted + '''''
AND event_type_key = ''''react_funded'''') as col1 '' )), 0) AS clk_fund; COMMIT TRANSACTION t2'
EXECUTE sp_executesql
@clk_fund_stmt,
@ParmDefinition,
@resultOUT=@clk_fund OUTPUT
SELECT @clk_fund
COMMIT TRANSACTION t1
END TRY
So basically, @clk_fund_stmt is executed via a linked object which points to a MySQL instance. When it gets executed, the result comes back immediately and somehow is not collected by the OUTPUT parameter waiting on the EXECUTE clause. The weird thing is that I'm using exactly the same mechanism on another SP and works like a charm. I even checked and compare the ODBC parameters - they're completely identical.
October 16, 2008 at 3:45 pm
Finally, I got it running and capture the result. The changes are in bold characters.
BEGIN
BEGIN TRY
BEGIN TRANSACTION t1
SET @ParmDefinition = N'@resultOUT INT OUTPUT';
SET @clk_fund_stmt = 'SELECT @resultOUT=ISNULL((SELECT * FROM openquery(spreporting,''
SELECT
(SELECT SUM(total_count)
FROM clk_sp2_data.' + @agg_space + ' a
JOIN clk_sp2_data.' + @spacedef + ' b
ON a.space_id = b.space_id
JOIN clk_sp2_data.' + @event_type + ' c
ON a.event_type_id = c.event_type_id
WHERE page_id in (39121,39413,1807,17212,39417)
AND date_occurred = ''''' + @date_formatted + '''''
AND event_type_key = ''''funded'''' )
-
(SELECT SUM(total_count)
FROM clk_sp2_data.' + @agg_space + ' a
JOIN clk_sp2_data.' + @spacedef + ' b
ON a.space_id = b.space_id
JOIN clk_sp2_data.' + @event_type + ' c
ON a.event_type_id = c.event_type_id
WHERE page_id in (39121,39413,1807,17212,39417)
AND date_occurred = ''''' + @date_formatted + '''''
AND event_type_key = ''''react_funded'''') as col1 '' )), 0);'
EXECUTE sp_executesql
@clk_fund_stmt,
@ParmDefinition,
@resultOUT=@clk_fund OUTPUT
SELECT @clk_fund
COMMIT TRANSACTION t1
END TRY
So, the output parameter needs to be mapped in order to be channeled correctly. Case closed - took me 24 hours to discover this.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply