Declare values error. - SQL Server Forums

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

Declare values error.

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-27 : 03:26:46

Below is a script that I am using to sot save date to a text file and zip it up. I want to delate the zip folder name and the txt file name. I am using the date. It runs with out an error with one variable but it does not use the variable vaule, it just puts in @MyFileName as the name of the file

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'set'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'exec'.

Glad of any help!

DECLARE @MyFileNamezip varchar(50),

@MyFileNametxt varchar(50)

set @MyFileNamezip = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(DD, GETDATE()) +'.zip'
set @MyFileNametxt = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+ CAST(DATEPART(DD, GETDATE()) +'.txt'

exec master..xp_cmdshell 'bcp "SELECT [RecordType],[CodeAssortment],[Description],[ManualMaintenance],[Product],[Quantity],[CatalogCode],[ProductLong],[Supplier],[CustomerCodeAlfa],[DeleteFlag] ,[CodeAssortment_Long],[ERPCustomerGroup],[FreeForUse]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASS]" queryout "D:\WebFiles\Web Update File Out\ASS Update Files\RASS_ASS_ALL.txt" -c -Uusername -Ppassword -SAppserver'
EXEC UDEF_KD_CompressFile 'D:\WebFiles\Web Update File Out\ASS Update Files\@MyFileNamezip', 'D:\WebFiles\Web Update File Out\ASS Update Files\@MyFileNametxt'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-27 : 08:04:04

The syntax errors are due to missing parts in your CAST expressions.

DECLARE @MyFileNamezip varchar(50),

@MyFileNametxt varchar(50)

set @MyFileNamezip = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +'.zip'
set	@MyFileNametxt = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +'.txt'

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-27 : 14:36:40

Thanks for that. the proc runs fine.

I issue I am having now is that it is saving the file name as @MyFileNametxt. I guess I need to put something around it???

SET NOCOUNT OFF

DECLARE @MyFileNamezip varchar(50),

@MyFileNametxt varchar(50)

set @MyFileNamezip = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +'.zip'
set @MyFileNametxt = 'RASS_ASS_ALL_' + CAST(DATEPART(YYYY, GETDATE()) AS varCHAR(4)) +'_'+ CAST(DATEPART(MM, GETDATE()) AS varCHAR(2)) +'_'+
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +'.txt'

exec master..xp_cmdshell 'bcp "SELECT [RecordType],[CodeAssortment],[Description],[ManualMaintenance],[Product],[Quantity],[CatalogCode],[ProductLong],[Supplier],[CustomerCodeAlfa],[DeleteFlag] ,[CodeAssortment_Long],[ERPCustomerGroup],[FreeForUse]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASS]" queryout "D:\WebFiles\Web Update File Out\ASS Update Files\@MyFileNametxt" -c -Uusername -Ppassword -SAppserver'
EXEC UDEF_KD_CompressFile 'D:\WebFiles\Web Update File Out\ASS Update Files\@MyFileNamezip', 'D:\WebFiles\Web Update File Out\ASS Update Files\@MyFileNametxt'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources