Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Export using bcp problem Expand / Collapse
Posted Sunday, June 30, 2013 9:48 PM


Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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]
/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 07/01/2013 13:42:26 ******/
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 */
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


Post #1468918
Posted Monday, July 1, 2013 1:33 AM



Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 2,807, Visits: 3,298
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’! **
Post #1468959
Posted Monday, July 1, 2013 7:11 PM



Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 39,672, Visits: 36,812
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #1469265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse