Data transfer from sql query to .txt file error

Question

Data transfer from sql query to .txt file error

sekhar.kandregula

SSC Rookie

Points: 28
More actions
June 13, 2014 at 12:00 am

#148654

Hi ,
Im trying to move data from sql query to .txt file im getting 'An error occurred while processing the command line.' only folder file is creaing and .txt file is not generating .
Please help on this.
Below is the script used .
declare @result int
DECLARE @Command NVARCHAR(4000)
create table #temp1(case_id int,pol_no varchar(20))
insert into #temp1 select case_id,case_app_ref_num from app_case
DECLARE @RecLoc int
declare @MaxLoc int
declare @cmd VARCHAR(8000)
SELECT @RecLoc = MIN(case_id),
@MaxLoc = MAX(case_id)
FROM #temp1
EXEC xp_cmdshell 'MKDIR e:\Files\file5'
WHILE @RecLoc < @MaxLoc
BEGIN
set @cmd='bcp "SELECT * from #temp1 where case_id='+ QUOTENAME(@RecLoc, '''') +'" out e:\Files\file5\1.txt -c -t, -T -S'+ @@servername
EXEC xp_cmdshell @cmd
SELECT @RecLoc = MIN(case_id)
FROM #temp1
WHERE case_id > @RecLoc
END
drop table #temp1

Viewing 5 posts - 1 through 4 (of 4 total)

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

sekhar.kandregula (6/13/2014)
Hi ,
set @cmd='bcp "SELECT * from #temp1 where case_id='+ QUOTENAME(@RecLoc, '''') +'" out e:\Files\file5\1.txt -c -t, -T -S'+ @@servername

Try below.

set @cmd='bcp "SELECT * from #temp1 where case_id='+ QUOTENAME(@RecLoc, '''') +'" queryout e:\Files\file5\1.txt -c -t, -T -S'+ @@servername

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

sekhar.kandregula SSC Rookie Points: 28 More actions · Answer 2

i too tried with queryout but it throws the same error,can you please help on this...

sekhar.kandregula SSC Rookie Points: 28 More actions · Answer 3

Even i tried with this below one ...i getting below error output

declare @sql varchar(8000)

SELECT @sql = ‘bcp “select * from MM_DEV_T_D2_P..app_case” queryout E:\files\files\1.txt -c -t’+’”|”‘+’ -T -S @@servername’

exec master..xp_cmdshell @sql

—–err msg output

SQLState = 08001, NativeError = 53

Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].

SQLState = 08001, NativeError = 53

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti

ons.

SQLState = S1T00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timeout expired

NULL

plz help on this.

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

SELECT @sql = ‘bcp “select * from MM_DEV_T_D2_P..app_case” queryout E:\files\files\1.txt -c -t’+’”|”‘+’ -T -S @@servername’

Try below.

SELECT @sql = 'bcp "select * from MM_DEV_T_D2_P..app_case" queryout E:\files\files\1.txt -T -c'

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂