Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to change a message string Expand / Collapse
Author
Message
Posted Monday, November 26, 2007 1:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:32 PM
Points: 114, Visits: 1,070
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
Posted Monday, November 26, 2007 1:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Monday, November 26, 2007 1:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:32 PM
Points: 114, Visits: 1,070
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
Posted Monday, November 26, 2007 1:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Monday, November 26, 2007 1:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Monday, November 26, 2007 2:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:32 PM
Points: 114, Visits: 1,070
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 »

Add to briefcase

Permissions Expand / Collapse