Query results to a text file in a specified path

Question

Query results to a text file in a specified path

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
June 23, 2009 at 7:08 am

#207454

Hi,
I am trying to get the results of a query to a text file in the path specified.
Is there any way other than BCP utility to achieve this?
I mean can we have a procedure which does something like
declare
@filePath -- to hold the path
@fileName -- name of txt file
--if file exists overwrite it
select * from tblName --> output to @filePath\@fileName
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 13 posts - 1 through 12 (of 12 total)

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

ps. One Orange Chip Points: 29255 More actions · Answer 1

if you're running this query from query window, you've option to save the results in 1)grid, 2)text and 3) file. There are three buttons on the top right side for this.

Pradeep Singh

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

ps (6/23/2009)
if you're running this query from query window, you've option to save the results in 1)grid, 2)text and 3) file. There are three buttons on the top right side for this.

Yes, but this is a manual operation.

I need this to be automated.

I mean, the query will be scheduled to run on 1st of every month.

And we need to see an output text file in the specified path in the query.

Is there any way to specify the PATH where the output should be stored, in the query?

Thanks,

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ps. One Orange Chip Points: 29255 More actions · Answer 3

see if this helps u

http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file%5B/url%5D

Using the commands there you can store the results to an external file.

Remember to run this from command prompt instead. You can create a windows scheduler for this...

Pradeep Singh

ps. One Orange Chip Points: 29255 More actions · Answer 4

OR run that with xp_cmdshell if u need to create a job in sql server itself....

Pradeep Singh

Pandian S SSChasing Mays Points: 625 More actions · Answer 5

Creating Output file at DB Server :

Goto Query Analyzer and Do the following

XP_CMDShell 'SQLCMD -S ServerName/IP -d DataBaseName

-U UserID -P Password -Q "SELECT Statement" -o OutputFilePath'

XP_CMDShell 'TYPE FilePath'

Creating Output file at Client Machine :

Goto Command Prompt and do the following

SQLCMD -S ServerName/IP -d DataBaseName -U UserID

-P Password -Q "SELECT Statement" -o OutputFilePath

Noel McKinney Hall of Fame Points: 3389 More actions · Answer 6

ps (6/23/2009)
see if this helps u
http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file%5B/url%5D

Note that the technique in that article uses osql, which is a deprecated utility that will be removed from SQL Server, so sqlcmd should be used instead.

http://msdn.microsoft.com/en-us/library/ms162806.aspx

ps. One Orange Chip Points: 29255 More actions · Answer 7

Thanks for correcting Noel. Appreciate it 🙂

Yes, use sqlcmd instead of osql.

Pradeep Singh

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

Thanks all for the help,

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 9

Hi,

I am using the below cmd to generate Report.

but the problem is I'm not able to trim the column "Code" which is of varchar(max)....

in the report generated i can see a huge gap between columns "Code" and [Method]

SQLCMD -S myServer -d myDB -E -Q "SELECT POL_NO AS 'Policy',Name,AGY_NO AS 'Agency',Initials,Code,Method,createdOn AS 'Received Date' FROM History_temp" -o "C:\Report.txt"

if i change "Code" like cast(Code as varchar(100)) then it'll work fine...

so i tried the below query in Editor and i'm getting error...

declare @CodeLen int,

@strSQL varchar(4000)

SELECT @CodeLen = MAX(DATALENGTH(Code)) from History_temp

--print @CodeLen

set @strSQL='SELECT POL_NO AS ''Policy''

,Name

,AGY_NO AS ''Agency''

,Initials

,cast(Code as varchar('+@CodeLen+')) AS 'Code'' --ERROR

,Method

,createdOn AS ''Received Date''

FROM History_temp'

exec(@strSQL)

In the table History_temp, column "Code" is varchar(max)

I'm getting the below error...

Msg 245, Level 16, State 1, Line 8

Conversion failed when converting the varchar value 'SELECT POL_NO AS 'Policy'

,Name

,AGY_NO AS 'Agency'

,Initials

,cast(Code as varchar(' to data type int.

Any suggestions like how to make the ERROR line work?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ramvbabu Old Hand Points: 372 More actions · Answer 10

declare @CodeLen int,

@strSQL varchar(4000)

SELECT @CodeLen = MAX(DATALENGTH(Code)) from History_temp

--print @CodeLen

set @strSQL='SELECT POL_NO [Policy]

,Name

,AGY_NO [Agency]

,Initials

,cast(Code as varchar( ' +@CodeLen + ')) [Code]

,Method

,createdOn AS [Received Date]

FROM History_temp '

exec(@strSQL)

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 11

Ramesh Babu (6/24/2009)
declare @CodeLen int,
@strSQL varchar(4000)
SELECT @CodeLen = MAX(DATALENGTH(Code)) from History_temp
--print @CodeLen
set @strSQL='SELECT POL_NO [Policy]
,Name
,AGY_NO [Agency]
,Initials
,cast(Code as varchar( ' +@CodeLen + ')) [Code]
,Method
,createdOn AS [Received Date]
FROM History_temp '
exec(@strSQL)

This did not made any sense to me...

This is same as what I posted...

cast(Code as varchar( ' +@CodeLen + ')) [Code]

how to make this line work?

-

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

robinhc SSCommitted Points: 1617 More actions · Answer 12

maybe ...

[font="Courier New"]set @strSQL='SELECT POL_NO AS ''Policy''

,Name

,AGY_NO AS ''Agency''

,Initials

,cast(Code as varchar(' + cast(@CodeLen as varchar) + ')) AS ''Code'' --ERROR

,Method

,createdOn AS ''Received Date''

FROM History_temp'[/font]