SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to change a message string


Need to change a message string

Author
Message
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87143 Visits: 45267
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
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87143 Visits: 45267
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87143 Visits: 45267
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
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search