Getting stuck on running script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting stuck on running script

Author

Topic

MTater
Starting Member

2 Posts

Posted - 2010-09-14 : 20:19:20

Hi- I get stuck at SELECT @StartTime = getdate() when I try and run this script. Get error Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@StartTime".

How do I run this? It should work. . . I must not be breaking it up right or ???

DECLARE @Counter int
DECLARE
@RowsProcessed int,
@MaxCount int

SET @Counter = 0 -- Start of real records
SET NOCOUNT ON

SELECT @MaxCount = MAX(PrimaryID) FROM dbo.OEM_RtoS_Returns_Working

StartOfLoop:

SELECT @StartTime = getdate()

-- Increment the control counter so 1,000 rows are operated on at a time.
SET @Counter = @Counter + 1000

INSERT OEM_RtoS_Result
SELECT DISTINCT r.BeginningCOAID AS ReturnCOAID
, s.BeginningCOAID AS ShipBegCOAID
, s.EndingCOAID AS ShipEndCOAID
FROM dbo.OEM_RtoS_Returns_Working r
LEFT JOIN OEM_RtoS_Shipments s
ON r.First11Chars = s.First11Chars
AND r.Last6BegCOAID >= s.Last6BegCOAID
AND r.Last6BegCOAID <= s.Last6EndCOAID
WHERE r.PrimaryID <= @Counter

SET @RowsProcessed = @@ROWCOUNT

DELETE FROM OEM_RtoS_Returns_Working
WHERE PrimaryID <= @Counter

SELECT @LoopTime = getdate()

SELECT @MSG = 'Batch counter = (' + CONVERT(varchar, @Counter) + ') ' +
CONVERT(varchar, @RowsProcessed) + ' Records processed in (' +
CONVERT(varchar,(DATEDIFF(Minute, @StartTime, @LoopTime))) + ') Minutes'
RAISERROR(@MSG,1,1) WITH NOWAIT

--
IF (@Counter >= @MaxCount) BEGIN GOTO EndOfLoop END
GOTO StartOfLoop

EndOfLoop:

END

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-15 : 02:29:01

Hello,

quote:

Hi- I get stuck at SELECT @StartTime = getdate() when I try and run this script. Get error Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@StartTime".

You must declare the scalar variable "@StartTime".

quote:

DECLARE @Counter int
DECLARE
@RowsProcessed int,
@MaxCount int
DECLARE @StartTime datetime
..........
..........

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-15 : 03:08:09

You can use
DECLARE @Counter int
DECLARE
@RowsProcessed int,
@MaxCount int,
@StartTime datetime

MTater
Starting Member

2 Posts

Posted - 2010-09-15 : 15:40:36

Ok cool thank you very much. So that does jive with the very beginning piece of the overall script although that one doesn't run. I get this error:

Msg 1018, Level 15, State 1, Procedure OEMFY10_Q4_Part1_MatchReturnsToShipments, Line 20
Incorrect syntax near 'NOWAIT'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

when running:

IF EXISTS
(SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[OEMFY10_Q4_Part1_MatchReturnsToShipments]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [OEMFY10_Q4_Part1_MatchReturnsToShipments]
END
GO

-- OEMFY10_Q4_Part1_MatchReturnsToShipments

CREATE PROCEDURE dbo.OEMFY10_Q4_Part1_MatchReturnsToShipments
AS
BEGIN

DECLARE
@StartTime datetime,
@LoopTime datetime,
@MSG varchar(255),
@SQLString nvarchar(4000),
@ErrorDescr varchar(255)

SELECT @ErrorDescr = ''
SELECT @StartTime = getdate()

SELECT @MSG = 'Start of OEMFY10_Q4_Part1_MatchReturnsToShipments Process(' +
CONVERT(varchar,@startTime) + ')'
RAISERROR(@MSG,1,1) WITH NOWAIT

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:20:34

Do you know what books online is?

BOL:

quote:

C. Using a local variable to supply the message text
The following code example shows how to use a local variable to supply the message text for a RAISERROR statement.

Copy Code
DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'<<%7.3s>>';

RAISERROR (@StringVariable, -- Message text.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO

I don't think you need NOWAIT

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:21:43

and

CONVERT(varchar,@s...

Will give you 1 byte

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources