BCP error Must Declare Scalar Variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

BCP error Must Declare Scalar Variable

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-06-26 : 19:38:53

Hi everyone,

DECLARE @curDate smalldatetime;
DECLARE @sql varchar(1000);

SELECT @curDate = GETDate()

select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE], INVOICE=null, ceiling(ENTERED_QTY) as [INVOICE QUANTITY], TOT_BASE_AMT as [INVOICE PRICE] from PROD.dbo.MAINVDTL where COMPANY=400 AND DISTRIB_DATE = @curDate" queryout ' + @ReportPath + ' -T -t, -c';

** The select stmt above gives error Must declare Scalar Variable. How can I compare on the @curDate variable when using BCP ?

Thanks, John

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-26 : 20:41:49

how do you execute the statement ?

Did you use sp_executesql and pass in the parameter ?

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-26 : 22:08:08

DECLARE @curDate smalldatetime;
DECLARE @sql varchar(1000);
DECLARE @ReportPath varchar(200)

SELECT @ReportPath = 'C:\temp\bcp.csv', @curDate = GETDate()

select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE], INVOICE=null,
ceiling(ENTERED_QTY) as [INVOICE QUANTITY], TOT_BASE_AMT as [INVOICE PRICE]
from PROD.dbo.MAINVDTL where COMPANY=400 AND DISTRIB_DATE = ''' + CONVERT(varchar(50), @curDate) + '''"
queryout ' + @ReportPath + ' -T -t, -c';

--PRINT @sql

Then use xp_cmdshell to execute it. You'll likely want to add the -S switch, and I like -r\r\n too.

select @sql = 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE], INVOICE=null,
ceiling(ENTERED_QTY) as [INVOICE QUANTITY], TOT_BASE_AMT as [INVOICE PRICE]
from PROD.dbo.MAINVDTL where COMPANY=400 AND DISTRIB_DATE = ''' + CONVERT(varchar(50), @curDate) + '''"
queryout ' + @ReportPath + ' -T -t, -c -S(local) -r\r\n';

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources