Outer join problem

Hi.
I mean left outer join must return all rows in the left table.
Why?
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
Returns 22 Rows. and
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
and d.grupo is null
Returns only 9 rows
I need a query that returns all rows in the prm_Exp_x_PV table and only the
rows with null grupo in the prm_paneles_x_pv.
Table a and b have a master detail relation with cero or more rows in the
detail table.
Any help?
Thanks.
Pau.Having not seen your data (and DDL for that matter) I cannot see anything
unexpected in those queries.
And if you want rows where prm_paneles_x_pv.grupo is null then use the
appropriate alias - c instead of d.
For better help post DDL and sample data.
ML|||You keep mentioning table "b". Where is it? It's not referenced by any query
.
I don't know what happened to the DDL/sample data, but I can't see it.
Please read more on this here:
http://www.aspfaq.com/etiquette.asp?id=5006
ML|||Pau, if I'm correctly guessing what you want, you need to apply the
condition in the join condition rather than the where clause ...
...
FROM
a
LEFT JOIN b ON a.x = b.x AND b.y IS NULL
...
"Pau Dom_nguez" wrote:

> Ok. here it is the DDL and data.
> The field grupo is a field of table d used to filter the matching rows in
> table b.
> I need a query that returns all rows in the prm_Exp_x_PV table and only th
e
> rows from the prm_paneles_x_pv that have null grupo in prompaneles.
> Table a and b have a master detail relation with cero or more rows in the
> detail table.
> SELECT DISTINCT a.cliente, a.Expositor
> FROM prm_Exp_x_PV a
> LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_ex
pv
> LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
> WHERE a.cliente = '4306500007'
> and d.grupo is null
> What is wrong in this select?
> Thanks ML.
> Pau.
> "ML" <ML@.discussions.microsoft.com> escribió en el mensaje
> news:54B5E0ED-DD74-4A75-B4BD-F95FE6915DF5@.microsoft.com...
>
>|||Thanks you very much KH.
Now it works fine.
Pau.
"KH" <KH@.discussions.microsoft.com> escribi en el mensaje
news:3989ADA9-5111-4927-AD1A-64192018DCC9@.microsoft.com...
> Pau, if I'm correctly guessing what you want, you need to apply the
> condition in the join condition rather than the where clause ...
> ...
> FROM
> a
> LEFT JOIN b ON a.x = b.x AND b.y IS NULL
> ...
>
>
> "Pau Domnguez" wrote:
>|||Thank you for nothing ML.
You are burned, this work is not for you.
Pau.
"ML" <ML@.discussions.microsoft.com> escribi en el mensaje
news:93A4AF26-5094-4C10-863D-AC5C7E39A597@.microsoft.com...
> You keep mentioning table "b". Where is it? It's not referenced by any
> query.
> I don't know what happened to the DDL/sample data, but I can't see it.
> Please read more on this here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML

Out of options, Need performance help.

hey guys, i need your help please.
here is the scenario:

1. I need to return a data back to client (result set varies 20-10,000)
2. I only want to show 20 records at a time
3. To get info i need to display i need to join 10 tables

When there are small #s of records it works but when i get over 8000 then it becomes a problem:

1. The first version was:
Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
Takes around 15 seconds to view 20 records.

2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
Takes around 5 seconds to view 20 records.

I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?

Any help is appreciated.
Wojo.You coud try running a few instances of the search in anticipation of the Next/Prev scenario.

While users are reading 20 in their window the application is downloading a further 20 behind the scenes asynchronously. I couldn't give an example. At least they wouldn't be aware of the 5 seconds if it took longer to read 20.

The only other example I have is for Delphi, sorry.|||Well...you need to order by something...and you need to know what page your on...

How about..

USE Northwind
GO

DECLARE @.Page int, @.sql varchar(8000)
SELECT @.Page = 2
SELECT @.sql = 'SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP '
+ CONVERT(varchar(10),@.Page * 20)
+ ' * FROM Orders ORDER by OrderId) AS A '
+ ' ORDER By OrderID Desc) AS B '
+ ' ORDER BY OrderId'
EXEC(@.sql)|||Thanks guys,
Brett, how does this work?|||To see it work...go to QA...cut and paste the code and execute it...

What it does roughly is that if you want page x, the inner query will select the TOP x * 20 rows (last 20 will be page x...

Then order by a key descending to get the last 20 rows..the reorder those by the key, so page 20 will be in the correct order to be displayed...

test it, let me know if it works for you...

Oh, and btw, this will only transmit the 20 rows across..you don't want to be moving large chunks for no reason...especially since data is fluid and may change at any time...|||Hmm,

What if i want to sort by other columns, plus my IDs aren't sequential :(|||You have to order by something...

The order of data in a database is meaningless....

And yes can can order by more than 1 thing...

And it doesn't have to be an int...

Look at a phonebook...they don't order by phone number...|||Thanks Brett,

it looks like it works preety well, i'm getting 1-2 seconds on couple of first pages!

One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?

thanks|||What's the total row count?

Did you try it for the last page yet?

And...

One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?

Why does this matter to you? Remembering the page number should be more important...no?

And cool site btw...do you sell just parts or whole systems...where are you based out of?|||The row count was ~9k
I did and it took 6-7 seconds, which is not that bad considering almost nobody looks at last pages :)

It matters to me because i'm displaying total number of records and i need this number to drive my paging logic (ex. page 1 out of 12)

Re: my site, its just my personal project, this question is for work :) and no i actually don't sell anything in there, i wrote this site to allow people to sell buy hardware for no charge...

thanks again for all your help man!|||What are you SELECTING, and what's the ORDER BY on?

Are they all indexed?

I don't like 6/7 seconds....|||i will look into indexes later, i would like to get this running first, so i need total count of records...|||Wouldn't that just be @.Pages*20?|||lol, i need to know what @.PAGES is don't i?

i don't think i made myself clear, i want to know the total number of records i'm gonna page myself though, ex:

Found 100 records, you are on page number 1 out of 5 (since there is 20 recs per page)

thanks.|||"i will look into indexes later"

If you are concerned about performance NOW, then you need to look into indexes NOW. I wondered what kind of indexes you had when I read that it started getting slow at 8000 records. 8000 records is peanuts.

Doing a select count(*) to get the rowcount should not be too expensive. Another method is to select the rowcount from the sysindexes table.|||I should have mentined before but I do this instinctivly, you may already be doing it.

When you join yuor tables dont use * and avoid large text fields. can be quite a surprise how long they take.

Try a skeleton version of the query that just selects and joins the keys. Then work up to include fields you need.

Sorry if you are allready doing that. And nice site you have there.

osql return to check backup success/failure

I am running an osql using input script which runs a database backup. Is
there a way I could get a return of 1 or 0, 0 being success and 1 being
error. I tried using -b option but it returns the entire details of failure
followed by the return number and if i use -o paramater I dont see any
result at all. Can anyone please suggest some solution to this. any help
will be greatly appreciated. Thanks
Message posted via http://www.webservertalk.comTry catching the error in the batch being executed with OSQL and use
RAISERROR to set the DOS variable ERRORLEVEL.
Example:
Create a batch file with the following instructions:
osql -S my_server -E -d master -Q "backup database northwind to blahblah if
@.@.error != 0 raiserror ('Error during backup.', 16, 127)"
echo %ERRORLEVEL%
Execute the batch file and you will see 50000 as the echo of the variable
ERRORLEVEL, because I am specifying a msg_str in RAISERROR. You can create
your own error msg using sp_addmessage.
AMB
"ishaan99 via webservertalk.com" wrote:

> I am running an osql using input script which runs a database backup. Is
> there a way I could get a return of 1 or 0, 0 being success and 1 being
> error. I tried using -b option but it returns the entire details of failur
e
> followed by the return number and if i use -o paramater I dont see any
> result at all. Can anyone please suggest some solution to this. any help
> will be greatly appreciated. Thanks
> --
> Message posted via http://www.webservertalk.com
>|||I forgot to mention that in order to set DOS variable ERRORLEVEL, using
RAISERROR, you have to specify state 127.
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
AMB
"Alejandro Mesa" wrote:
> Try catching the error in the batch being executed with OSQL and use
> RAISERROR to set the DOS variable ERRORLEVEL.
> Example:
> Create a batch file with the following instructions:
> osql -S my_server -E -d master -Q "backup database northwind to blahblah i
f
> @.@.error != 0 raiserror ('Error during backup.', 16, 127)"
> echo %ERRORLEVEL%
> Execute the batch file and you will see 50000 as the echo of the variable
> ERRORLEVEL, because I am specifying a msg_str in RAISERROR. You can create
> your own error msg using sp_addmessage.
>
> AMB
>
> "ishaan99 via webservertalk.com" wrote:
>|||Thanks so much. That helped and worked fine.
Message posted via http://www.webservertalk.com

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