SQL Server Performance

Convert Sql Data to any file format

Discussion in 'Contribute Your SQL Server Scripts' started by rajeev_id, May 15, 2004.

  1. rajeev_id New Member

    --This sp convert SQL Server data into any file format.eg xsl,txt,doc etc.
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO
    --exec SP_GEN_FILE 'select * from <DATABASENAME>.<OWNER>.<TABLENAME>','<DIR>','<FILENAME>'

    CREATE PROC SP_GEN_FILE
    (
    @QUERY VARCHAR(4000),
    @PATH VARCHAR(250),
    @FILENAME VARCHAR(50),
    @EXTENSION VARCHAR(50)=NULL,
    @USERNAME VARCHAR(250)=NULL,
    @PWD VARCHAR(250)=NULL
    )
    AS
    --THIS IS WRITTEN BY RAJEEV SRIVASTAVA: TO EXPORT DATA INTO ANY FILE FORMAT
    DECLARE @DBNAME VARCHAR(50)
    DECLARE @DIR_PATH VARCHAR(250)
    DECLARE @SQL_QUERY VARCHAR(4000)
    DECLARE @CNT INT
    DECLARE @FOUND INT

    SET @FOUND=0
    SET @CNT=0

    WHILE @CNT<=LEN(@QUERY)
    BEGIN
    SET @CNT=@CNT+1
    --PRINT SUBSTRING(@QUERY,@CNT-1,1)
    if SUBSTRING(@QUERY,@CNT-1,1)='.'
    begin
    set @FOUND=@FOUND+1
    IF @FOUND=2
    SET @CNT=LEN(@QUERY)+1
    end

    END

    IF @FOUND < 2
    BEGIN
    PRINT 'TABLE NAME SHOULD BE START WITH <DATABASENAME>.<OWNER>.<TABLENAME>'
    RETURN
    END

    SET @DBNAME=db_name()
    --SELECT @DBNAME
    IF @USERNAME IS NULL
    SELECT @USERNAME=SYSTEM_USER

    IF @PWD IS NULL
    SET @PWD=''

    IF RIGHT(@PATH,1)<>('')
    SET @PATH=@PATH + ''


    IF @EXTENSION IS NULL
    SET @EXTENSION='.XLS'

    IF LEFT(@EXTENSION,1)<>('.')
    SET @EXTENSION='.'+@EXTENSION


    IF RIGHT(@FILENAME,4)<>@EXTENSION
    SET @FILENAME=@FILENAME+@EXTENSION


    SET @DIR_PATH=LEFT(@PATH,LEN(@PATH))



    IF (SELECT COUNT(*) FROM TEMPDB.DBO.SYSOBJECTS WHERE [NAME]='##SP_GEN_EXCEL_FILE_TABLE')= 1
    BEGIN
    DROP TABLE ##SP_GEN_EXCEL_FILE_TABLE
    END

    CREATE TABLE ##SP_GEN_EXCEL_FILE_TABLE
    (COL1 VARCHAR(4000))


    INSERT INTO ##SP_GEN_EXCEL_FILE_TABLE EXEC('exec master..xp_cmdshell ''MD '+@DIR_PATH+'''')
    IF (SELECT COUNT(COL1) FROM ##SP_GEN_EXCEL_FILE_TABLE WHERE COL1 IS NOT NULL )=0
    PRINT 'NEW FOLDER CREATED'


    IF (SELECT COUNT(*) FROM TEMPDB.DBO.SYSOBJECTS WHERE [NAME]='##SP_GEN_EXCEL_FILE_TABLE')= 1
    BEGIN
    DROP TABLE ##SP_GEN_EXCEL_FILE_TABLE
    END

    SET @SQL_QUERY='''BCP "'+@QUERY+'" QUERYOUT '+@PATH+@FILENAME+' /c -t -S '+ @@SERVERNAME +' -U '+@USERNAME+' -P '+@PWD+''''

    --SELECT @SQL_QUERY

    EXEC('master..xp_cmdshell '+@SQL_QUERY)



    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO



    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--

Share This Page