Query output into a file

Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram GIt depends on which database engine (DB2, Microsoft, Oracle, etc) you are using, and what language was used to write the stored procedure.

-PatP|||Hi,
The database is SQLSERVER2000 and it uses T-SQL.
Bharathram|||Hi,

In a stored procedure how do I output the result of a query to a text file?

Regards,

Bharathram G
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:

From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"

From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'

Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.

BCP and Data Transformation Services (DTS) let you export table data to text files.

I hope this will clear your doubts.
Rudra

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
--
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

query output format

Hey all, another question with the same SQL line:

AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||

If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:

***
Smith, H
Johnson, P
***
Amid, D

Is it possible to strip the comma from the Supervisor name records so that it will return:

***
Smith H
Johnson P
***
Amid D

??

Any help would be appreciated!
DeeOriginally posted by psdcc
Hey all, another question with the same SQL line:

AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||

If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:

***
Smith, H
Johnson, P
***
Amid, D

Is it possible to strip the comma from the Supervisor name records so that it will return:

***
Smith H
Johnson P
***
Amid D

??

Any help would be appreciated!
Dee

Hi dee,

Use oracle replace function for the final output string

ex : select replace(outputtext, ',',' ')

regards

Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

query output as XML

Hi

Can I have my query output returned as XML (FOR XML AUTO) in SQL Mobile ?

thanks
Bruce

no, not directly from SQL Mobile. you can fill a DataSet with your query results using the adapter or data reader and then convert the DataSet into XML....

-Darren

Query output as text rather than a temp table..

Hi Guys,

I'm trying to figure out how to output a query as text instead of a tempory table...

I thought perhaps i could use this:

SELECT PRINT CustomerID FROM ORDER_TABLE

But that doesn't work

PRINT CustomerID FROM ORDER_TABLE

Doesn't work either...

I need this so i can print a customised invoice in SQL. That is, unless their is a better way of tackling this problem?

thx for reading :)

--PhilkillsWhere does "SELECT CustomerID FROM ORDER_TABLE" write its output to?|||That generates a tempory table with a heading of 'CustomerID' and row numbers at the left hand side....

Im talking about just outputing the data in the customerID column... and nothing else :p

The same way you would do:

PRINT 'BLAH'

etc...|||That generates a tempory table with a heading of 'CustomerID' and row numbers at the left hand side....you can be assuered that it isn't a temporary table and it doesn't have line numbers -- your front end app is doing that

what database is this? (notice you have posted in the standard sql forum)|||Its for MSSQL

and i found 1 potential way...

storing the output in a buffer:

DECLARE @.ordNo INT
DECLARE @.outBuff VARCHAR(1000)
DECLARE @.newLine CHAR(1)
SET @.ordNo = 1
SET @.outBuff = ''
SET @.newLine = '
'

SELECT @.outBuff = @.outBuff + 'PRICE: ' + CAST(ItemPrice AS VARCHAR) + @.newLine
FROM ORDER_ITEM, ITEM
WHERE ORDER_ITEM.ItemID = ITEM.ItemID

PRINT @.outBuff

But is there a better way?|||moved to SQL Server forum