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

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2006-08-31 : 04:38:17

Hi all,
I have another issue on my bcp script i am trying to compile in its current state it works perfect However.
This section is part of another script that creates a SQL job and this code is inserted in to the job. Trouble is it sets the query file with the date the script was run.
The problem is i now find that i need the queryout file to represent the actual date the job runs.

DECLARE @M10Supcode varchar (10)
SET @M10Supcode = 'sup'
Declare @filename varchar (20)
set @filename = ((select convert(varchar (10),getdate(),112))+'.dta')
DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp "use mitre 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:\'+@M10Supcode+'' + @filename + ' -c -E /t '
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

The way i see it i need to somehow set the queryout with something like this command to represent the corect date
((select convert(varchar (10),getdate(),112))+'.dta')
Any one have any ideas or have i lost you all :-)

regards
Phil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 05:29:59

[code]DECLARE @M10SupCode VARCHAR(10),
@FileName VARCHAR(20),
@cmd VARCHAR(2048)

SELECT @M10SupCode = 'sup',
@FileName = CONVERT(varchar, GETDATE(), 112) + '.dta',
@cmd = 'bcp "SELECT SUBSTRING(Recordtype, 1, 2), SUBSTRING(Transdate, 1, 10), SUBSTRING(Transref, 1, 15), ',
@cmd = @cmd + 'SUBSTRING(APN, 1, 15), Dept, SUBSTRING(Description, 1, 50), SUBSTRING(QtySupplied, 1, 8), ',
@cmd = @cmd + 'SUBSTRING(Cost, 1, 12), SUBSTRING(Retail, 1, 12), Promotion FROM Mitre..M10Export ',
@cmd = @cmd + 'ORDER BY Recordtype DESC" queryout "c:\' + @M10Supcode + @filename + '" -c -E /t'

EXEC master..xp_cmdshell @cmd, NO_OUTPUT[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources