?
Solved

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

Posted on 2011-10-19
5
Medium Priority
?
194 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:Scamquist
  • 3
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
Som Tripathi earned 2000 total points
ID: 36998010
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
 
LVL 1

Author Comment

by:Scamquist
ID: 37000614
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
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 37002496
For each entry in WorkOrderId, you want to run BCP.
Am I right?
0
 
LVL 1

Author Comment

by:Scamquist
ID: 37003021
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
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 37005944
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question