BCP in stored procedure

Hi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram G

It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)

Hi ,
Try this ...

create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query

SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)

SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY

end

Joydeep

Batch database backup and restore

Does anyone know how I could setup a backup or restore executed from a .bat
file?
TonyTony (tonyng2@.spacecommand.net) writes:
> Does anyone know how I could setup a backup or restore executed from a
> .bat file?
Put the command you want to run in a file called BACKUP.SQL, and then
say:
osql -E -n -i BACKUP.LOG
for more details, please look up OSQL in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp