CODEWITHSUNDEEP
sql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

TSQL - Export into multiple text files

So i need to export the ID numbers from a table into multiple text files (300 in length), i am trying to use BCP to achieve this and have been trying to use the following:

DECLARE  @First             int
        ,@Last              int
        ,@Range             INT
        ,@docName           NVARCHAR(20)
        ,@SQLCommand        NVARCHAR(MAX)
SELECT @First   = MIN(CandidateID)  FROM tblCandidate
SELECT @Last    = MAX(CandidateID)  FROM tblCandidate
SELECT @DocName = 1
SELECT @Range   = 300
WHILE @First <= @Last
BEGIN
    SELECT @docName = 1
    SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM db1.dbo.tblCandidate WHERE [CandidateID] = ''''' 
                    + 'BETEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                    + '''''" queryout "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test\' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,''' 
    PRINT @SQLCommand
    EXEC (@SQLCommand)
    SET @First = @First + 300
    SET @Range = @Range + 300
    SET @DocName = @DocName + 1
END

However i keep getting the following error:

Starting copy...
SQLState = 22005, NativeError = 245
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'BETEEN 1 AND 300' to data type int.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed

Any ideas what i have done wrong here?

---- EDIT: ---------------------------------------------------------------------

Changed the SQLCommand to:

SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM TRIS_Aspen.dbo.tblCandidate WHERE [CandidateID] ''''' 
                + 'BETWEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                + '''''" queryout "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test\"' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,'''

And am now getting the following error:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
NULL

Upvotes: 1

Views: 123

Answers (2)

pacreely
pacreely

Reputation: 1931

The between command just needs to look like 

[CandidateID] BETWEEN 1 AND 300

Your code is producing

[CandidateID] ''' BETWEEN 1 AND 300

Loose the unnecessary quotes

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Two typos:

  • [CandidateID] = - lose the equals here as you are using BETWEEN on this column
  • BETEEN - Change to BETWEEN

Try this:

SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM TRIS_Aspen.dbo.tblCandidate WHERE [CandidateID] ''''' 
                    + 'BETWEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                    + '''''" queryout "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test\' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,'''

Upvotes: 2

Related Questions