How to solve this error "must declare the scalar variable" even though already declare ?

Question

1.00/5 (1 vote)

See more:

Hi
I have in error in Mssql Store procedure using BCP bulk transfer data into csv file
Error : - "Must declare the scalar variable "@EXPORT_EXCEL"

Pls advice me.

Thank you in advance

Maideen

What I have tried:

	DECLARE @Servername varchar(100)
	SET @Servername ='xxxxxxx'

   DECLARE @EXPORT_EXCEL TABLE
    (
		[BL_NO] [varchar](30) NULL,
		[VESSAL_NAME] [varchar](100) NULL,
		[CONTAINER_NO] [varchar](50) NULL,
		[CONTAINER_TYPE] [varchar](10) NULL

	)
    

		INSERT INTO @EXPORT_EXCEL (BL_NO, VESSAL_NAME,CONTAINER_NO,CONTAINER_TYPE) 
		SELECT BL_NO, VESSAL_NAME,CONTAINER_NO,CONTAINER_TYPE FROM [dbo].[ZR_tbl_Export_FILE] 
		ORDER BY VESSAL_NAME


declare @sql varchar(8000)
select @sql = 'bcp guard_security.dbo.' + @EXPORT_EXCEL + 'out c:\ExportExcel\Export_FILE.csv -c -t, -T -S' +  @servername
	   exec master..xp_cmdshell @sql

Posted 22-Jul-19 15:04pm

Maideen Abdul Kader

Updated 23-Jul-19 3:38am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-07-22T22:28:00

@EXPORT_EXCEL isn't a variable, it's a Table variable which means it is scoped to the procedure or command that is executing: as soon as the execution ends (which is will right after issuing the exec command) the table will be destroyed - you can't just stick the table into an SQL command where the system expects a table name as part of it's DB.Table specification!
And even if you could, you wouldn't pass the table content to the operating system using that code as it wouldn't exist when bcp started executing!

To output in CSV, why not just use XML PATH:

SQL

SELECT SUBSTRING(
(SELECT ',' + CONVERT(varchar, Id) FROM myTable WHERE date BETWEEN '2011-01-01' AND '2012-01-01'
FOR XML PATH('')),2,100000) AS CSV

And write that?