Copy table data into text file

0
votes

I'm trying to insert data into text file from select query using SQL Server 2012. But I'm not able to to this please help me.

Code

EXEC xp_cmdshell 'bcp "Select RecodLine from WorkDataLoad" queryout "D:\Test.txt" -T -SSubhash-Pc -c -t,'

Error

Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'WorkDataLoad'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations

BCP copy out failed

2
sqlsql-serversql-server-2008tsqlsql-server-2012
Fully qualify? dbname.dbo.WorkDataLoad - Alex K.
It gives invalid object name error. So before you run this script, have you set database like this - Set USE [YourDB] or just append dbname in your query - Krishnraj Rana
@AlexK. Same error after try this "EXEC xp_cmdshell 'bcp "Select RecodLine from [dbo].[WorkDataLoad]" queryout "D:\Test.txt" -T -SSubhash-Pc -c -t,'" - S. S. Rawat
@Subhash replace <dbname> with your database name - mxix
@Subhash - Did you write the name of your database where it says "dbname" ? Like "myOwnLittleDatabase.dbo.WorkDataLoad" ? - Sasse

2 Answers

0
votes

Try this way :)

SET NOCOUNT ON
DECLARE @TestTable TABLE (ID INT IDENTITY(1,1), Data NVARCHAR(1000))
DECLARE 
    @AllData NVARCHAR(MAX),
    @FileDest NVARCHAR(100)

--Replace only the path here for example ' >>C:\myfile.txt'',no_output'
SET @FileDest = ' >>E:\test.txt'',no_output'
SET @AllData = ''

INSERT INTO @TestTable VALUES
    ('This is my first row'),
    ('hello from second row')

SELECT @AllData += 
    'EXEC xp_cmdshell ''@ECHO '+Data+@FileDest+CHAR(10)
FROM @TestTable

EXEC(@AllData)
0
votes

I think the DataBase mane is missing in the SELECT statement, use query like this:

Select RecodLine from DatabaseName.SchemaName.WorkDataLoad

Or, you have to provide an argument to specify the database name like:

EXEC xp_cmdshell 'bcp "Select RecodLine from WorkDataLoad" queryout "D:\Test.txt" -d "DatabaseName" -T -SSubhash-Pc -c -t,'