Export using bcp problem

  • 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

  • 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’! **
  • 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)

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

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