Dynamic sql for convert SQL statement to txt/csv file

  • kast218

    SSCertifiable

    Points: 6286

    Comments posted to this topic are about the item Dynamic sql for convert SQL statement to txt/csv file

  • pawel.sowka

    SSC Eights!

    Points: 851

    Why, I always get

    Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'table'.

    Table exists.

    Any table return the same error.

  • kast218

    SSCertifiable

    Points: 6286

    pawel.sowka (5/28/2015)


    Why, I always get

    Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'table'.

    Table exists.

    Any table return the same error.

    Hello, pawel.sowka.

    Sorry for troubles, you should use full table name in your sql query: DATABASENAME.SCHEMANAME.TABLENAME

    I will fix description for procedure running today.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script. I may have a use for this one. Thanks again.

  • kast218

    SSCertifiable

    Points: 6286

    Iwas Bornready (6/2/2015)


    Thanks for the script. I may have a use for this one. Thanks again.

    Thank you for your reply. I updated procedure description on github (for using full name for tables): https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_bcpUnloadSelect.sql

  • lanre_makinde

    SSC Veteran

    Points: 208

    The procedure kept failing with error - Invalid Object Table Name

    This is because the database context is not supplied in the bcp string and it defaults to the master database

    see updated script

    a. Db name variable is added.

    b. This can be passed as a parameter value

    or it can be auto supplied based on the current database

    c. The executing BCP command is updated to include the database parameter

    -- Tested and worked

    ps

    Procedure call updated to turn on/off xp_cmdshell

    -------------------------------------------------

    -------------------------------------------------

    IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS Not NULL

    Begin

    EXECUTE('Drop PROCEDURE dbo.usp_bcpUnloadSelect ');

    IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS NULL

    Print ' Dropped !!! Procedure dbo.usp_bcpUnloadSelect'

    else

    Print ' Error !!! Creating Procedure dbo.usp_bcpUnloadSelect'

    End

    else

    Print ' Procedure dbo.usp_bcpUnloadSelect will be created !!!'

    GO

    Create PROCEDURE dbo.usp_bcpUnloadSelect

    (

    @outputFilePath VARCHAR(255) -- The path can have from 1 through 255 characters, see documentation

    , @serverName SYSNAME = @@SERVERNAME

    -- Added Lanre Makinde 2016/12/09

    , @dbName Varchar(50) = Null

    -- Added Lanre Makinde 2016/12/09

    , @sqlCommand VARCHAR(MAX)

    , @fileName VARCHAR(300) = ''

    , @field_term VARCHAR( 10) = '|'

    , @fileExtension VARCHAR( 10) = 'txt'

    , @codePage VARCHAR( 10) = 'C1251'

    , @row_term VARCHAR( 10) = ''

    , @debug BIT = 1

    )

    AS

    /*--

    Official bcp documentation: http://technet.microsoft.com/en-us/library/ms162802.aspx

    In select statement use full table names: DATABASENAME.SCHEMANAME.TABLENAME

    sp_configure xp_cmdshell , 1

    reconfigure

    go

    EXECUTE [dbo].[usp_bcpUnloadSelect]

    @outputFilePath = 'd:\'

    , @serverName = ''

    , @dbName = Null

    , @sqlCommand = 'SELECT * FROM dbo.merchant_profiles'

    , @fileName = 'file_name'

    , @field_term = '|'

    , @row_term = ''

    , @fileExtension = 'txt'

    , @debug = 0; --0|1-- @debug = 1 print only bcp command without executing it.

    Go

    sp_configure xp_cmdshell , 0

    reconfigure

    Go

    --*/

    BEGIN

    -- Added Lanre Makinde 2016/12/09

    If @dbName is Null or @dbName = '' or len(@dbName) = 0

    select @dbName = db_name()

    -- Added Lanre Makinde 2016/12/09

    BEGIN TRY

    DECLARE @filePath VARCHAR(2000) = @outputFilePath +

    CASE

    WHEN @fileName = '' THEN 'bcp_export_'

    ELSE @fileName

    END

    + QUOTENAME(REPLACE(CONVERT(VARCHAR, GETDATE(), 126 ), ':', '_'))

    + '.'

    + Case

    When @fileExtension is Not Null then @fileExtension

    else ''

    End;

    DECLARE @tsqlCommand VARCHAR(8000) = '';

    DECLARE @crlf VARCHAR(10) = CHAR(13) + CHAR(10);

    IF @debug = 0

    SET NOCOUNT ON

    ELSE

    PRINT '/******* Start Debug' + @crlf;

    /* remove break lines from select statement */

    SET @sqlCommand = REPLACE(REPLACE(@sqlCommand, CHAR(13), ' '), CHAR(10), ' ');

    /* remove duplicate spaces from select statement */

    SET @sqlCommand = REPLACE(REPLACE(REPLACE(@sqlCommand,' ','<>'),'><',''),'<>',' ');

    IF @debug = 1

    PRINT ISNULL('@filePath = {' + @crlf + @filePath + @crlf + '}', '@filePath = {Null}' + @crlf)

    PRINT'@sqlCommand = {' + @crlf + @sqlCommand + @crlf + '}';

    SET @tsqlCommand = 'bcp "' + REPLACE(@sqlCommand, @crlf, ' ')

    + '" queryout "' + @filePath

    + '" -d ' + @dbName -- Added Lanre Makinde 2016/12/09

    + ' -T -S ' + @serverName

    + ' -c -' + @codePage

    + ' -t"' + @field_term + '"'

    + ' -r"' + @row_term + '"'

    + @crlf;

    IF @debug = 1

    PRINT ISNULL( '@tsqlCommand = {' + @crlf + @tsqlCommand + '}' + @crlf, '@tsqlCommand = {Null}');

    ELSE

    EXECUTE xp_cmdshell @tsqlCommand;

    IF @debug = 0

    SET NOCOUNT OFF

    ELSE

    PRINT @crlf + '--End Deubg*********/';

    END TRY

    BEGIN CATCH

    -- EXECUTE dbo.usp_LogError;

    -- EXECUTE dbo.usp_PrintError;

    Print 'Error -->' + error_message() + ', Error Number :' + str(error_number())

    END CATCH

    END

    go

    IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS Not NULL

    Print ' Success !!! creating Procedure dbo.usp_bcpUnloadSelect'

    else

    Print ' Error !!! Creating Procedure dbo.usp_bcpUnloadSelect'

    GO

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    We just had a recent need for this.

Viewing 7 posts - 1 through 7 (of 7 total)

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