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