Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Export using bcp problem Expand / Collapse
Author
Message
Posted Sunday, June 30, 2013 9:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 03, 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]
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


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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 2,078, Visits: 2,410
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 01, 2013 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Add to briefcase

Permissions Expand / Collapse