Must Declare Scaler Variable Error 2

[Aidy680]

Hi,

The code below works fine:

USE [database]
GO

DECLARE @Environment AS VARCHAR(40)
DECLARE @Error AS VARCHAR (40)
DECLARE @FileName AS VARCHAR(300)
DECLARE @SQLSTRING AS VARCHAR (max)
SET @Error = 'ERROR : Server instance not recognised'

SET @Environment = CASE @@SERVERNAME
WHEN 'MG1VDBAZ01\SQLDEV01'
THEN 'mg1vdbaZ01'
WHEN 'MG1VDBAZ01\SYSTEST'
THEN 'mg1vdbaZ01'
WHEN 'MG1VDBAU02'
THEN 'mg1vdbaU02'
WHEN 'MG1VDBAP04'
THEN 'mg1vdbaP04'
ELSE
@Error
END
IF @Environment = @Error
BEGIN
PRINT @Error
RETURN
END
PRINT @Environment

-- Insert tblTaxDomicile Data
SET @FileName = '\\'+ @Environment +'\SQLDump\tblTaxDomicile.txt'
PRINT @FileName

SET @SQLSTRING =
'
BULK INSERT tblTaxDomicile
FROM '''+ @FileName +'''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n'',
KEEPIDENTITY,
DATAFILETYPE=''char''
)
'

PRINT @SQLSTRING
EXEC(@SQLSTRING)
GO

PRINT 'tblTaxDomicile Updated'

If however I try to repeat this code, immediately underneath, using a different table and file names, I get the error below:

"Must declare the scalar variable "@Environment".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@FileName".

It's almost as though I need to refresh the variable.

Any pointers please?

 

[gmmastros]

If however I try to repeat this code, immediately underneath, using a different table and file names, I get the error below:

I would suggest that you remove the GO near the bottom of your code block. You see... GO is a batch separator. This means that variables are deallocated/destroyed.

For example, copy/paste this in to a query window.

Declare @Blah VarChar(10)

Set @Blah = 'Blah'

Print 'Before the GO: ' + @Blah
Go
Print 'After the GO: ' + @Blah

When you run this code, you will see:

[tt][blue]
Before the GO: Blah
[red]Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Blah".[/red]
[/blue][/tt]

Notice a couple things. First, notice that the "Before" message printed just fine. Also notice the error message, in particular the line number for the error message. It says line 1 even though the actual error is in the last line. Seems weird until you consider that line numbering is also reset with GO. So the line that is causing the error is actually the first line of the batch.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Aidy680]

Thanks gmmastros! Cant believe it was something as simple as that.

This forum continually proves to be a blinding resource!