BCP issue - 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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP issue

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2006-08-29 : 20:42:58

Hi all,
Hope someone out there can answer this hopefully simple question.
I am exporting data from a table in to a txt file and then SQL emails me the result daily. So far this works perfect, HOWEVER now i need the txt file to have a set file name based on yyymmdd but i can not get this to work correctly. The section in question is after the queryout.
Hopefully someone out there knows the answer the script is below.
Cheers
Phil

DECLARE @cmd VARCHAR(2048)
SET @cmd = ''bcp "Declare @filename varchar (20) set @filename = (select convert(varchar (10),getdate(),112)use [' + @DBName + '] Select substring(Recordtype,1,2),substring(Transdate,1,10),substring(Transref,1,15),substring(APN,1,15),DEPT,substring(Description,1,50),substring(QtySupplied,1,8),substring(Cost,1,12),substring(Retail,1,12),Promotion from M10Export Order by Recordtype DESC" queryout c:\@filename -c -E /t ''
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-29 : 21:11:20

This might help:

SET @cmd = ''bcp "Declare @filename varchar (20) set @filename = (select convert(varchar (10),getdate(),112)use [' + @DBName + '] Select substring(Recordtype,1,2),substring(Transdate,1,10),substring(Transref,1,15),substring(APN,1,15),DEPT,substring(Description,1,50),substring(QtySupplied,1,8),substring(Cost,1,12),substring(Retail,1,12),Promotion from M10Export Order by Recordtype DESC" queryout c:\' + @filename + '-c -E /t ''

HTH,

Tim

stumbling
Posting Yak Master

104 Posts

Posted - 2006-08-29 : 21:38:34

Thanks for the quick response tried the above but still get the following error message.
Server: Msg 137, Level 15, State 2, Line 74
Must declare the variable '@filename'.
Any other ideas?
cheers
Phil

stumbling
Posting Yak Master

104 Posts

Posted - 2006-08-29 : 22:18:28

Sorry my stuff up you were correct and i appreciate your very fast and correct response ur the best!
cheers
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources