?
Solved

SQL 2005 Export View To Text with value from view in file Name

Posted on 2011-10-21
4
Medium Priority
?
235 Views
Last Modified: 2012-05-12
I am using BCP to export a SQL view to a text file.  It worked fine until I tried to add the value from a view as part of the file name.  

I have a field in my view vwWorkOrderAdjust named wo_id.  The parsing of the time in the file name works, but if I try to begin the file name with the wo_id it fails.

While there will be multiple records in vvWorkOrderAdjust, the wo_id is the same for all records.

When I try to complie this stored procedure, I get the error:

Incorrect syntax near the keyword 'SELECT'.

Is what I am trying to do possible?
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 vwWorkOrderAdjust
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
Comment
Question by:Scamquist
  • 3
4 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 37006115
Hi,

Try this..

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


SELECT TOP (1) @wo_nbr =  WorkOrderId FROM vwWorkOrderAdjust
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 1

Author Comment

by:Scamquist
ID: 37006290
I receive the following error when I execute the stored procedure:

Msg 245, Level 16, State 1, Procedure spPostUpload2, Line 15
Conversion failed when converting the varchar value '_' to data type int.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:		Steve Lindquist - Brass Tacks Group, LLC
-- Create date: August 30, 2011
-- Description:	Save work order details with work order id and time in file name
-- 10 - spPostUpload.sql
-- =============================================
alter PROCEDURE [dbo].[spPostUpload2] AS
BEGIN
DECLARE @wo_nbr int
DECLARE @file_name varchar(20) 
DECLARE @sql varchar(8000)

SELECT TOP (1) @wo_nbr =  wo_id FROM vwWorkOrderAdjust
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 1

Author Comment

by:Scamquist
ID: 37006312
My bad.  The wo_nbr is not int, it is varchar(max)

Works great.

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

SELECT TOP (1) @wo_nbr =  wo_id FROM vwWorkOrderAdjust
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 1

Author Closing Comment

by:Scamquist
ID: 37006325
Thank you very much.  When I declare my variable properly, thigs work better.

I appreciate the assist.

Steve
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

839 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