Orphaned Sessions and locks: view

Showing posts with label view. Show all posts

Wednesday, March 28, 2012

Outer Join, Self Join rewrite

I am hoping to place an index on a view. Among other things that would
prevent this from happening, the view has both an outer join and a self join.
Is there any way to rewrite an outer join and self join, so that the view
becomes indexable? This is a sample of my current outer join and self join as
used on the view:
FROM dbo.CarCodes AS CC
LEFT OUTER JOIN dbo.CarCodes AS CC_CC
ON CC_CC.CarCodeID= CC.CarCodeFID
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1Hi
A re-write would require a UNION which would also preclude it from having an
index, therefore I don't think it would be possible to include the given
table in a view.
If you are checking for non-existance ie. there is also a where clause with
CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
join.
You may want to post full DDL and the underlying tables to show all the code.
John
"cbrichards via SQLMonster.com" wrote:
> I am hoping to place an index on a view. Among other things that would
> prevent this from happening, the view has both an outer join and a self join.
> Is there any way to rewrite an outer join and self join, so that the view
> becomes indexable? This is a sample of my current outer join and self join as
> used on the view:
> FROM dbo.CarCodes AS CC
> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> ON CC_CC.CarCodeID= CC.CarCodeFID
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
>|||So, to rewrite the outer join, it would go something like this?
FROM dbo.CarCodes AS CC
JOIN dbo.CarCodes AS CC_CC
ON CC_CC.CarCodeID= CC.CarCodeFID
WHERE CC_CC.CarCodeID IS NOT NULL
John Bell wrote:
>Hi
>A re-write would require a UNION which would also preclude it from having an
>index, therefore I don't think it would be possible to include the given
>table in a view.
>If you are checking for non-existance ie. there is also a where clause with
>CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
>join.
>You may want to post full DDL and the underlying tables to show all the code.
>John
>> I am hoping to place an index on a view. Among other things that would
>> prevent this from happening, the view has both an outer join and a self join.
>[quoted text clipped - 5 lines]
>> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
>> ON CC_CC.CarCodeID= CC.CarCodeFID
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||Hi
That was not what I was saying. Without seeing the full statement it is not
totally possible to rule out that it could be re-written, the example was a
showing something in which the outer join would have be incorrectly used.
Even knowing the whole statement would not rule out poor table design and
other improvements that may have removed the need for the index on the view.
John
"cbrichards via SQLMonster.com" wrote:
> So, to rewrite the outer join, it would go something like this?
> FROM dbo.CarCodes AS CC
> JOIN dbo.CarCodes AS CC_CC
> ON CC_CC.CarCodeID= CC.CarCodeFID
> WHERE CC_CC.CarCodeID IS NOT NULL
>
>
> John Bell wrote:
> >Hi
> >
> >A re-write would require a UNION which would also preclude it from having an
> >index, therefore I don't think it would be possible to include the given
> >table in a view.
> >
> >If you are checking for non-existance ie. there is also a where clause with
> >CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
> >join.
> >
> >You may want to post full DDL and the underlying tables to show all the code.
> >
> >John
> >
> >> I am hoping to place an index on a view. Among other things that would
> >> prevent this from happening, the view has both an outer join and a self join.
> >[quoted text clipped - 5 lines]
> >> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> >> ON CC_CC.CarCodeID= CC.CarCodeFID
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
>|||Hi
I have just noticed that it was a self-join, this would also preclude an
index being created.
John
"John Bell" wrote:
> Hi
> That was not what I was saying. Without seeing the full statement it is not
> totally possible to rule out that it could be re-written, the example was a
> showing something in which the outer join would have be incorrectly used.
> Even knowing the whole statement would not rule out poor table design and
> other improvements that may have removed the need for the index on the view.
> John
> "cbrichards via SQLMonster.com" wrote:
> > So, to rewrite the outer join, it would go something like this?
> >
> > FROM dbo.CarCodes AS CC
> > JOIN dbo.CarCodes AS CC_CC
> > ON CC_CC.CarCodeID= CC.CarCodeFID
> > WHERE CC_CC.CarCodeID IS NOT NULL
> >
> >
> >
> >
> > John Bell wrote:
> > >Hi
> > >
> > >A re-write would require a UNION which would also preclude it from having an
> > >index, therefore I don't think it would be possible to include the given
> > >table in a view.
> > >
> > >If you are checking for non-existance ie. there is also a where clause with
> > >CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
> > >join.
> > >
> > >You may want to post full DDL and the underlying tables to show all the code.
> > >
> > >John
> > >
> > >> I am hoping to place an index on a view. Among other things that would
> > >> prevent this from happening, the view has both an outer join and a self join.
> > >[quoted text clipped - 5 lines]
> > >> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> > >> ON CC_CC.CarCodeID= CC.CarCodeFID
> >
> > --
> > Message posted via SQLMonster.com
> > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
> >|||John,
Let's say the full View statement is the following (Please Note: I have
removed the self join):
SELECT cod.CarCode
FROM CarCode cod
LEFT JOIN CarColor col
ON cod.CarCodeID = col.CarCodeFID
WHERE col.CarCodeFID IS NULL
Is there any way to turn this Left Join into an Inner Join, or in other words,
is there any way to rewrite this sample View statement to make it indexable?
John Bell wrote:
>Hi
>That was not what I was saying. Without seeing the full statement it is not
>totally possible to rule out that it could be re-written, the example was a
>showing something in which the outer join would have be incorrectly used.
>Even knowing the whole statement would not rule out poor table design and
>other improvements that may have removed the need for the index on the view.
>John
>> So, to rewrite the outer join, it would go something like this?
>[quoted text clipped - 22 lines]
>> >> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
>> >> ON CC_CC.CarCodeID= CC.CarCodeFID
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||Hi
Your statement is testing for the non-existance of a corresponding row in
CarColor
An equivaent of this without the join would require a subquery such as
SELECT cod.CarCode
FROM dbo.CarCode cod
WHERE NOT EXISTS ( SELECT CarCodeFID FROM dbo.CarColor col WHERE
cod.CarCodeID = col.CarCodeFID )
Which is also NOT allowed for creating an indexed view. Therefore for your
view it would not be possible to add an index.
If you re-worked your database design so that there is a trigger that will
set set CarCodeID to NULL when a CarColor was deleted from CarColor then you
could use
CREATE VIEW vw_Transparent_Cars WITH SCHEMABINDING AS
SELECT cod.CarCode
FROM dbo.CarCode cod
WHERE cod.CarCodeID IS NULL
CREATE UNIQUE CLUSTERED INDEX idx_vw_Transparent_Cars ON vw_Transparent_Cars
( CarCode )
HTH
John
"cbrichards via SQLMonster.com" wrote:
> John,
> Let's say the full View statement is the following (Please Note: I have
> removed the self join):
> SELECT cod.CarCode
> FROM CarCode cod
> LEFT JOIN CarColor col
> ON cod.CarCodeID = col.CarCodeFID
> WHERE col.CarCodeFID IS NULL
> Is there any way to turn this Left Join into an Inner Join, or in other words,
> is there any way to rewrite this sample View statement to make it indexable?
> John Bell wrote:
> >Hi
> >
> >That was not what I was saying. Without seeing the full statement it is not
> >totally possible to rule out that it could be re-written, the example was a
> >showing something in which the outer join would have be incorrectly used.
> >Even knowing the whole statement would not rule out poor table design and
> >other improvements that may have removed the need for the index on the view.
> >
> >John
> >
> >> So, to rewrite the outer join, it would go something like this?
> >>
> >[quoted text clipped - 22 lines]
> >> >> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> >> >> ON CC_CC.CarCodeID= CC.CarCodeFID
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
>|||> Let's say the full View statement is the following (Please Note: I have
> removed the self join):
> SELECT cod.CarCode
> FROM CarCode cod
> LEFT JOIN CarColor col
> ON cod.CarCodeID = col.CarCodeFID
> WHERE col.CarCodeFID IS NULL
> Is there any way to turn this Left Join into an Inner Join, or in other
> words,
> is there any way to rewrite this sample View statement to make it
> indexable?
This query effectively becomes an inner join - just change "LEFT" to "INNER"
and remove the WHERE clause. However, you are most likely oversimplifying
the problem. Without knowing exactly what you are trying to accomplish and
without knowing what your tables (and therefore, your data model) are trying
to represent, it is unlikely that we will be able to adequately respond.
It looks like, per your original post, that you have a two level hierarchy
of carcodes. This can be easily to migrated to a two table design, making
your originally intended view indexable. This is effectively what you have
posted above - but you still are relying upon an outer join. To avoid the
outer join (and still return a result set that "looks" like the outer joined
one), you need to force at least one row into the child table; this is a row
composed solely of PK/FK values and NULLS (or blanks) for all other columns.
A bad design but that is the price for the technique you are trying to use.
Perhaps you should take a different approach and attempt to determine if you
will gain anything significant from indexing this view. It may also be
that this particular technique is not the one best suited to solving your
problem.
One last note - the above query **APPEARS** to assume that there is at most
a 1-to-1 relationship between carcode and carcolor. This is based on the
fact that a 1-to-many relationship would create a resultset that contains
duplicate rows. This should help illustrate why it is important to
understand the data model and what it represents before one attempts to
write queries that are consistent with the model. If we (well - you
actually) rely on this assumption, then that reliance should be captured in
the data model as an enforced constraint on the carcolor table, preventing
the insertion of multiple rows with the same carcodefid value.|||>> SELECT cod.CarCode
>> FROM CarCode cod
>> LEFT JOIN CarColor col
>> ON cod.CarCodeID = col.CarCodeFID
>> WHERE col.CarCodeFID IS NULL
Oops - could have sworn that said "is not null". That's what happens looking
at two different posts simultaneously. Therefore, ignore the comments about
the "change left to inner blah blah blah". The others still stand though.
Without destroying the model and creating a bad schema, indexing doesn't
look like a viable option.|||Hi
Just to add...
.. with SQL 2005 you will not need a trigger to set nullability as a new
cascade SET NULL option is available.
John
"John Bell" wrote:
> Hi
> Your statement is testing for the non-existance of a corresponding row in
> CarColor
> An equivaent of this without the join would require a subquery such as
> SELECT cod.CarCode
> FROM dbo.CarCode cod
> WHERE NOT EXISTS ( SELECT CarCodeFID FROM dbo.CarColor col WHERE
> cod.CarCodeID = col.CarCodeFID )
> Which is also NOT allowed for creating an indexed view. Therefore for your
> view it would not be possible to add an index.
> If you re-worked your database design so that there is a trigger that will
> set set CarCodeID to NULL when a CarColor was deleted from CarColor then you
> could use
> CREATE VIEW vw_Transparent_Cars WITH SCHEMABINDING AS
> SELECT cod.CarCode
> FROM dbo.CarCode cod
> WHERE cod.CarCodeID IS NULL
> CREATE UNIQUE CLUSTERED INDEX idx_vw_Transparent_Cars ON vw_Transparent_Cars
> ( CarCode )
> HTH
> John
> "cbrichards via SQLMonster.com" wrote:
> > John,
> > Let's say the full View statement is the following (Please Note: I have
> > removed the self join):
> >
> > SELECT cod.CarCode
> > FROM CarCode cod
> > LEFT JOIN CarColor col
> > ON cod.CarCodeID = col.CarCodeFID
> > WHERE col.CarCodeFID IS NULL
> >
> > Is there any way to turn this Left Join into an Inner Join, or in other words,
> > is there any way to rewrite this sample View statement to make it indexable?
> >
> > John Bell wrote:
> > >Hi
> > >
> > >That was not what I was saying. Without seeing the full statement it is not
> > >totally possible to rule out that it could be re-written, the example was a
> > >showing something in which the outer join would have be incorrectly used.
> > >Even knowing the whole statement would not rule out poor table design and
> > >other improvements that may have removed the need for the index on the view.
> > >
> > >John
> > >
> > >> So, to rewrite the outer join, it would go something like this?
> > >>
> > >[quoted text clipped - 22 lines]
> > >> >> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> > >> >> ON CC_CC.CarCodeID= CC.CarCodeFID
> >
> > --
> > Message posted via SQLMonster.com
> > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1
> >

Outer Join, Self Join rewrite

Wednesday, March 7, 2012

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

Wednesday, March 28, 2012

Outer Join, Self Join rewrite

Outer Join, Self Join rewrite

Wednesday, March 7, 2012

osql breaks output

osql breaks output

osql breaks output

Orphaned Sessions and locks

Blog Archive

About Me