Export using bcp problem

Question

Export using bcp problem

Oracle765

SSCrazy

Points: 2862
More actions
June 30, 2013 at 9:48 pm

#274623

Hi All
I have a stored procedure which extracts data and creates a file in the cleansed directory.
All works fine except obne thing
when the filename is longer than 16 characters the procedure works bu the file will not be created in the folder.
any ideas why
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 07/01/2013 13:42:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[exporttocsv]
@importedquery nvarchar(1000), /* The query to be executed */
@importedcsvname nvarchar(150)/* To name the exported file back to the original name */
as
BEGIN
DECLARE @path varchar(50)
DECLARE @filename varchar(30)
DECLARE @dbname varchar(30)
DECLARE @sql varchar(2000)
DECLARE @bcpcommand varchar(1000)
SELECT @path = 'C:\inetpub\wwwroot\cleansed\'
SELECT @filename = @importedcsvname + ' -c -t, -d '
SELECT @dbname = 'TestData -U sa -P sqldba'
SELECT @bcpcommand = 'bcp "' + @importedquery + '" queryout '
SELECT @sql = @bcpcommand + @path + @filename + @dbname
--print @sql
EXEC master..xp_cmdshell @sql
end

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

I don't know if this will be the solution, but I suggest to increase the size of variable @filename to NVARCHAR(165). Because this variable is build upon (adding text to) variable @importedcsvname which is designed as nvarchar(150), the variable @filename should be longer then @importedcsvname.

Can you post the complete string of a generated BCP command with a failing filename?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I'll also suggest that you really shouldn't hard-code user name and password. Use a "trusted connection" instead. Use the -T parameter instead of -U and -P.

Also, you should really check the two import parameters for "DOS Injection".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)