Output an SQL Query to a text file

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt

AlexOpen query analyzer. Go to Query and select Result to file... and
during execution, it will prompt a file name...|||Thanks but I cannot do that.

This must query must be invisible to the end user.

For example, if a user performs some function on my system, I wish to
execute a query on what they have selected and output the results to a
file. I do not want my user to be selecting Save As etc..|||First your question is not clear. I am still not sure what you are
trying to accomplish.

First if you have a query that you dont want your user to know, wrap it
up in a stored proc and schedule it in a job and let the user execute
the job.

Another easy way is to create a dts and let the user run the dts that
should run the query and output the file.

Hope this helps..!|||Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.|||Alex,
You might try adding this to your connection string: -o '<path
name>\results.txt.

such as: isql -SMyserver -Uusera -Puserapassword -i mysqlquery.sql -o
results.tx

hth

"Alex" <alex_seymour@.lineone.net> wrote in message
news:1108412990.821013.237700@.g14g2000cwa.googlegr oups.com...
> Hi,
> How would I go about writing the results of an SQL query to a text
> file?
> I cannot find any info in the Online help files.
> For example, I would like the results of:
> SELECT * FROM TableA
> To be written to the file result.txt
>
> Alex|||On 2/14/05 4:24 PM, in article
1108416279.303119.278950@.z14g2000cwz.googlegroups. com, "Alex"
<alex_seymour@.lineone.net> wrote:

> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

There is no OUTPUT TO FILE argument in a SELECT clause. You can SELECT INTO
a temporary table but this doesn't help you. The simplest thing to do (path
of least resistance) is to use a program in the middle to handle the query
and writing the recordset into a file. For example, if you wanted to create
an .ASP web form you could simply ask for the customer's email address, run
a query and send them the results. You could write a VB app or VB script to
run locally and generate a file from your query.

-Greg|||There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.|||Alex (alex_seymour@.lineone.net) writes:
> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

No, you don't want to do that. Yes, you can invoke xp_cmdshell and from
there do all sorts of things. But then your users would have to have
permission to use xp_cmdshell, and that could be a security issue.

It's not clear why you want to write to a file, but if you want to
log some stuff for debug reasons - write to a table instead. You are
in a database, not in a file system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok this is great, the tables I need data outputting from are permanent.

Basically it is for creating a log file. The database is huge, but the
log only needs to contain the contents of a few tables. It has to go to
a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.|||Alex wrote:
> Ok this is great, the tables I need data outputting from are
permanent.
> Basically it is for creating a log file. The database is huge, but
the
> log only needs to contain the contents of a few tables. It has to go
to
> a file because I want to be able to save the results of the query to
> disk or print it out.
> Can I BCP out from several tables into the same file?
> i.e. I need SELECT * from TableA, TableB, TableC to result.txt
> Security is not an issue.

It's a little involved but you can write a select statement joining
several tables. Each time BCP executes, I believe it overwrites the
text file.

Check out this article:
http://www.sqlteam.com/item.asp?ItemID=4722

output 2 table data to a text file

hi, how to output 2 table data to a text file?Not specifing what you want...
You can use bcp, osql with the -o switch or if you just wanna put out the
result of a query in QA you can send the result to a fiel rather than to the
result pane.
Is it that what you need ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
> hi, how to output 2 table data to a text file?
>|||can I do it in a query: select * from tb1 output my.txt?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
> Not specifing what you want...
> You can use bcp, osql with the -o switch or if you just wanna put out the
> result of a query in QA you can send the result to a fiel rather than to
> the result pane.
> Is it that what you need ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
>|||Yes of course, here is an example:
osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
will produuce the following output:
OrderID CustomerID EmployeeID OrderDate
RequiredDate ShippedDate ShipVia
Freight ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
-- -- -- --
-- -- --
-- ---
----
-- -- -- --
10248 VINET 5 1996-07-04 00:00:00.000
1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
32.3800 Vins et als Chevalier
59 rue de l'Abbaye
Reims NULL 51100 France
(1 row affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> can I do it in a query: select * from tb1 output my.txt?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
>|||Thanks Jens...|||how about this:
osql -E -Q"backup Northwind to disk='c:\output1.txt " -o "C:\Output2.txt" -S
myServer
'c:\output1.txt : Server Path
'C:\Output2.txt : Local Path
can I run it in another worksation? and want to 'c:\output1.txt' located in
the workdation?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
> Yes of course, here is an example:
> osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
> will produuce the following output:
> OrderID CustomerID EmployeeID OrderDate
> RequiredDate ShippedDate ShipVia
> Freight ShipName
> ShipAddress
> ShipCity ShipRegion ShipPostalCode ShipCountry
> -- -- -- --
> -- -- --
> -- ---
> ----
> -- -- -- --
> 10248 VINET 5 1996-07-04 00:00:00.000
> 1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
> 32.3800 Vins et als Chevalier
> 59 rue de l'Abbaye
> Reims NULL 51100 France
> (1 row affected)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Output will be redirected to the workstation due to the output of osql,
backup will be made to the server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
> how about this:
> osql -E -Q"backup Northwind to disk='c:\output1.txt " -o
> "C:\Output2.txt" -S myServer
> 'c:\output1.txt : Server Path
> 'C:\Output2.txt : Local Path
> can I run it in another worksation? and want to 'c:\output1.txt' located
> in the workdation?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||can backup to disk=unc format?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
> Output will be redirected to the workstation due to the output of osql,
> backup will be made to the server.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||YEah that works,
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:OdHsAJQTFHA.2812@.TK2MSFTNGP09.phx.gbl...
> can backup to disk=unc format?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
>

out put to text file issue

DECLARE @.cdr_date datetime
SET @.cdr_date = getdate()-1
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)

I want to run this query with output file like g_YYMMDD ( where YYMMDD will come from @.cdr_date

How can I do thatFor this to work reliably you need to create a couple of procedures:

create proc dbo.sp_test
@.cdr_date datetime = current_timestamp
as
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)
return (0)
go

Then create another procedure that will create the output file:

create proc dbo.sp_bcp_queryout as
@.cdr_date datetime = current_timestamp
as
declare @.cmd varchar(8000)
--assuming the db name is MYDB, server name - MYSERVER
set @.cmd = 'bcp "exec MYDB.dbo.sp_test ' + char(39) +
convert(char(10), @.cdr_date, 101) + char(39) + '" queryout D:\g_' +
convert(char(6), @.cdr_date, 12) + ' -S MYSERVER -T -c'

exec master.dbo.xp_cmdshell @.cmd
return (0)
go|||Thank you very much|||now I get following error

SQLState = 37000, NativeError = 8146
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure sp_call_get_cdr has no parameters and arguments were supplied.
NULL|||At what point are you getting the error? I also recommend to use "-e Error-ExceptionFile.ERR" on BCP so that you can check what record(-s) caused the error.|||Thanks for the quick reply

I got it. it works now. the only quesitons remains is that if I want to create csv file can I do that with column name as headers.

please advice.

thanks for your help in advance|||Yes you can, but you'll need to switch from BCP to OSQL.|||so can you please be specific. which command I need to put|||You should be able to create a CSV with BCP by using Column terminators:
8.0
4
1 SQLSMALLINT 0 2 "" 1 smallintField ","
2 SQLNCHAR 2 100 "'" 2 charField1 "',"
3 SQLNCHAR 2 100 "'" 3 charField2 "',"
4 SQLDATETIME 0 8 "'" 4 datetimeField "',"

OSQL- How to know the length of each column in the output file?

Hi,

I would like to use OSQL command to spool the output of a query to a text file. However, the length of the column in the text file is not same as the table column length declared. As I need to process the output file again , I would like to know exactly the start and the end position in the output file for each column. Delimiter doesn't help as the the logic will break if the value of one of the columns is having the delimiter.

My questions are:
1) How does the sql server calculate the length of each column when it spooled to a text file using OSQL?
2) How can I enclose the value with ""? For e.g., "record1"|"record2"|"record3"
3) If I use trial an error to get the length of each column in the output file, will the length of each column changes according to the value each time it runs?

drop table test
create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

osql -S servername -d dbname -E -Q "select * from test" -o "c:\test.log" -h-1 -n -w 8000 -s "|"

Thanks.FIRST!

EXCELLENT POST!

Having sample code makes it sooooooooooooo much easier

Second, I didn't test the bcp, but I did test the execution of the SQL...

Try this

USE Northwind
GO

create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

DECLARE @.cmd varchar(4000) DECLARE @.SQL varchar(4000)

SELECT @.SQL = 'SELECT ''"''+RTRIM(col1)+''"|"''+RTRIM(col2)+''"|"''+RTRIM(col3)+''"'' FROM test'
SELECT @.SQL
EXEC(@.SQL)

SELECT @.cmd = 'osql -S servername -d dbname -E -Q "'+ @.SQL + '" -o "c:\test.log" -h-1 -n -w 8000 -s "|"'
EXEC(@.cmd)
GO
DROP TABLE test
GO

Let us know how it works out...|||BTW, I would use the SQL with bcp with queryout...|||Hi,

First of all, thanks for your reply.

I can't use bcp as i need to call stored procedure (which reside externally in another database and I can't make any code change).

The logic behind is after calling the stored procedure and get the records, I need to spool all records into a text file and process the data -> load it to Oracle database. I can't use middle tier to write to a text file per records due to performance issue. As a result, I need to use OSQL to get the output.

Right now, I am having problem to identify the columns in the text file. Can you help with me quesitons above?

Any idea on that?

Thanks.|||Originally posted by Brett Kaiser
BTW, I would use the SQL with bcp with queryout...

and a format file along with it

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it into
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for this is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the text gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
Joachim
The object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim
|||Dan Guzman wrote:

> Consider using sp_helptext instead of using syscomments directly. This

>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim
|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produktiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim
|||Dan Guzman wrote:

> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken again.
Can I stop sclcmd from *breaking lines*?
Joachim
|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim
|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produktiv) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim
|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it in
to
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for t
his is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the te
xt gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
JoachimThe object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim|||Dan Guzman wrote:

> Consider using sp_helptext instead of using syscomments directly. This

>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produ
ktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too
.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>
>
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produk
tiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim|||Dan Guzman wrote:

> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken agai
n.
Can I stop sclcmd from *breaking lines*?
Joachim|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produkti
v) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENB
ANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENB
ANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it into
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for this is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the text gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
JoachimThe object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim|||Dan Guzman wrote:
> Consider using sp_helptext instead of using syscomments directly. This
>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>> Consider using sp_helptext instead of using syscomments directly. This
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produktiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim|||Dan Guzman wrote:
> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken again.
Can I stop sclcmd from *breaking lines*?
Joachim|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
>> 2005, provides a -W option that suppresses addition of padding and CRLF.
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produktiv) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
--
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim

osql and unicode

Hi,
Is anybody aware of any problems with inserting unicode chars via osql. I've
got some unicode text and when it gets inserted it inserts not the character
I am expecting. It works fine from query analyser etc. My collation is set t
o
the generic collation no problems there. Thanks.
Panos.Just some extra information.
The string I have in the input file is:
WIENER B?RSE
and it becomes
WIENER BíRSE
When the same script is run from query analyser then it insert WIENER B?RSE
which is correct. Thanks.
"Panos Stavroulis." wrote:

> Hi,
> Is anybody aware of any problems with inserting unicode chars via osql. I'
ve
> got some unicode text and when it gets inserted it inserts not the charact
er
> I am expecting. It works fine from query analyser etc. My collation is set
to
> the generic collation no problems there. Thanks.
> Panos.

OSQL - appending output to a file

I want to run a job every hour which calls a stored proc using osql and apppends the results to a text file.
But it always overwrites the previous results. Is there a way of getting osql to append the results instead?Originally posted by mwilkes
I want to run a job every hour which calls a stored proc using osql and apppends the results to a text file.

But it always overwrites the previous results. Is there a way of getting osql to append the results instead?

Good old DOS, you should use batch file to append OSQL results into existing text file, named differently than the one used in OSQL output, something like:

osql ... > Osql.Txt

Type Osql.Txt >> FinalResult.Txt

Where FinalResult.Txt contains all, appended results.

Cheers, Marin