Need to change a message string

Question

Need to change a message string

rightfield

SSCrazy

Points: 2418
More actions
November 26, 2007 at 1:02 pm

#61467

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

rightfield SSCrazy Points: 2418 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

rightfield SSCrazy Points: 2418 More actions · Answer 5

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