SQL 2005 Export View To Text with ID and Time Parsed In File Name

Hi,
I am using BCP to export a SQL view to a text file.  It works fine except the file has a static name.  I am trying to get a dynamic file name.

I have a field in my view (vwWorkOrderAdjust) named wo_id

Instead of exporting the file named forMas90.txt  I am trying to name the file with the wo_id and Hour_Minute_Second       So if it is 5:30:33 PM and I am exporting work order 18898 I want the file to be saved as 18898_17_30_33.txt.

Is this possible?
ALTER PROCEDURE [dbo].[spPostUpload] AS
BEGIN
DECLARE @sql varchar(8000)
SELECT @sql= 'bcp iris..vwWorkOrderAdjust out C:\Inetpub\wwwroot\Iris\htdocs\textfiles\forMas90.txt -c -t, -T -S'
EXEC master..xp_cmdshell @sql
end

Open in new window

LVL 1
ScamquistAsked:
Who is Participating?
 
Som TripathiDatabase AdministratorCommented:
Please find the code below -
ALTER PROCEDURE [dbo].[spPostUpload] AS
BEGIN
DECLARE @file_name varchar(20) 
DECLARE @sql varchar(8000)

SET @file_name=replace(convert(varchar(20) , getdate()  , 108) , ':' , '_' ) 
SET @file_name= '18898_' + @file_name + '.txt'

SELECT @sql= 'bcp iris..vwWorkOrderAdjust out C:\Inetpub\wwwroot\Iris\htdocs\textfiles\' + @file_name + ' -c -t, -T -S'
EXEC master..xp_cmdshell @sql
END

Open in new window

0
 
ScamquistAuthor Commented:
This works, but the 18898 is dynamic.

I tried changing the code as below but get an error at Select.

The table PendingPost only will only have one row, so selecting the top 1 will insure that.

Is what I am trying to do possible, or is my syntax wrong?

Thanks
create PROCEDURE [dbo].[spPostUpload2] AS
BEGIN
DECLARE @wo_nbr int
DECLARE @file_name varchar(20) 
DECLARE @sql varchar(8000)


set @wo_nbr = SELECT TOP (1) WorkOrderId FROM PendingPost
SET @file_name=replace(convert(varchar(20) , getdate()  , 108) , ':' , '_' ) 
SET @file_name=  @wo_nbr + '_' + @file_name + '.txt'

SELECT @sql= 'bcp iris..vwWorkOrderAdjust out C:\Inetpub\wwwroot\Iris\htdocs\textfiles\' + @file_name + ' -c -t, -T -S'
EXEC master..xp_cmdshell @sql
END

Open in new window

0
 
Som TripathiDatabase AdministratorCommented:
For each entry in WorkOrderId, you want to run BCP.
Am I right?
0
 
ScamquistAuthor Commented:
Yes, but there will never be more one record in the table PendingPosting.

I have an intranet webpage that populates the WorkOrderID, calls the stored procedure to generate the txt file and delete all records in the table.

I am just not sure of the syntax to call out the value of WorkOrderID.

0
 
ScamquistAuthor Commented:
You answered the question I posted.  Thank you very much.  I will repost with the next step.  It is only fair.

I appreciate the assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.