Need to change a message string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Need to change a message string

Need to change a message string

Rate Topic

Display Mode

Topic Options

Author

Message

rightfield

Posted Monday, November 26, 2007 1:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Hi experts,

I am adding some logging capability to a stored procedure that is having problems so I can know what is happening. I am having trouble with changing the message, @MyErrorMessage, that will go into my error log. The first six lines below work fine. After those lines execute I attempt to change the message so I can run the z_pes_ErrorLog_Insert procedure with a new message. I am testing from the Query Analyzer. The error refers to line 225 which is the blank line before the open cursor_03. (Error: Line 225: Incorrect syntax near '@MyErrorMessage'.)

I am new to SQL programming and I cannot find any reference to tell me what is wrong.

Thank you and warm regards,

Hope

DECLARE @StoredProcedureName VARCHAR(255)
DECLARE @MyErrorMessage VARCHAR(4000)
--SET @StoredProcedureName = OBJECT_NAME(@@PROCID)
SET @StoredProcedureName = 'manual testing'
SET @MyErrorMessage = '1-START pr_inp insert only '
EXEC tempdb.dbo.z_pes_ErrorLog_Insert @StoredProcedureName, @MyErrorMessage
...
......omitted stuff
cast(Bal_No as integer) as Bal_No
from openquery(db032007,'select * from pr_input') pr_input

open cursor_03

SET @MyErrorMessage = 'cursor_03 is open'
EXEC tempdb.dbo.z_pes_ErrorLog_Insert @StoredProcedureName, @MyErrorMessage

Post #425964

GilaMonster

Posted Monday, November 26, 2007 1:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744, Visits: 30,024

h.schlais (11/26/2007)

......omitted stuff
cast(Bal_No as integer) as Bal_No
from openquery(db032007,'select * from pr_input') pr_input

What's the entire of that statement? From what you posted, it looks like you're missing a select, but that might be part of the omitted stuff.

I'm guessing that's the declare cursor statement, but I can't be sure.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #425975

rightfield

Posted Monday, November 26, 2007 1:38 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

What was left out were all the declares and the declare for the cursor. I have shown it below. I took a working procedure that works fine against the development database (in the PRIMARY filegroup) and ran it against the production database (in a secondary filegroup) One ran for 25 minutes the next ran 10 hours. I am trying to find out why so I am adding the logging.

Thank you very much for helping.

Warm regards,

Hope

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--CREATE PROC dbo.z_pes_sync_pr_input_insert AS
BEGIN

DECLARE @StoredProcedureName VARCHAR(255)
DECLARE @MyErrorMessage VARCHAR(50)
--SET @StoredProcedureName = OBJECT_NAME(@@PROCID)
SET @StoredProcedureName = 'manual testing'

SET @MyErrorMessage = '1-START pr_input insert only '
EXEC tempdb.dbo.z_pes_ErrorLog_Insert @StoredProcedureName, @MyErrorMessage

SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

declare @p_OpCo as integer
declare @p_Loc_No as integer
declare @p_Div_No as integer
declare @p_Pay_Date as datetime
declare @p_Freq as char(1)
declare @p_Pr_No as integer
declare @p_Spare as integer
declare @p_Summ_No as integer
declare @p_Emp_No as integer
declare @p_Item_No as integer
declare @p_Ref_No as integer
declare @p_Check_No as integer
declare @p_Ss_No as integer
declare @p_Wcomp_1 as decimal(8,2)
declare @p_Wcomp_2 as decimal(8,2)
declare @p_Per_Start as datetime
declare @p_Per_End as datetime
declare @p_Bal_No as integer

declare cursor_03 cursor for
select
cast(Loc_No as integer) as Loc_No ,
cast(Div_No as integer) as Div_No ,
cast(Pay_Date as datetime) as Pay_Date ,
cast(Freq as char(1)) as Freq ,
cast(Pr_No as integer) as Pr_No ,
cast(Spare as integer) as Spare ,
cast(Summ_No as integer) as Summ_No ,
cast(Emp_No as integer) as Emp_No ,
cast(Item_No as integer) as Item_No ,
cast(Ref_No as integer) as Ref_No ,
cast(Check_No as integer) as Check_No ,
cast(Ss_No as integer) as Ss_No ,
cast(Wcomp_1 as decimal(8,2)) as Wcomp_1 ,
cast(Wcomp_2 as decimal(8,2)) as Wcomp_2 ,
cast(Per_Start as datetime) as Per_Start ,
cast(Per_End as datetime) as Per_End ,
cast(Bal_No as integer) as Bal_No
from openquery(db032007,'select * from pr_input') pr_input

open cursor_03

SET @MyErrorMessage = 'cursor_03 is open'
EXEC tempdb.dbo.z_pes_ErrorLog_Insert @StoredProcedureName, @MyErrorMessage

Post #425988

GilaMonster

Posted Monday, November 26, 2007 1:56 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744, Visits: 30,024

Very strange. Also getting syntax errors when I get management studio to do a syntax check.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #426001

GilaMonster

Posted Monday, November 26, 2007 1:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744, Visits: 30,024

Ha! Found it. That had me stumped for some minutes.

You're missing an END. There's a BEGIN on line 7, but no matching end. Put the END after the last EXEC and the syntax check goes through fine

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #426006

rightfield

Posted Monday, November 26, 2007 2:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Oh my gosh, thank you. It seemed so simple. Here I was trying to debug one problem and I created a new problem for myself.

Thank you so much.

Warm regards,

Hope

Post #426010

« Prev Topic | Next Topic »

Permissions