Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of thi
s
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:

> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:

>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of th
is
>column will contain a very very long output string that part the output dat
a
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:

> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
>
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>

Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of this
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:
> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> > We are using SQL 2K with Sp4.
> >
> > I am doing a Select statement where it contains a column that composes of
> > a
> > concatenation of various strings and many fields. At time, the output of
> > this
> > column will contain a very very long output string that part the output
> > data
> > is being truncated. I assume it exceeds the limit of an output column in
> > the result panel would allow. Is there a setting in SQL I can change to
> > imcrease the size of a column in the result panel in query anaylzer?
> >
> > Wingman
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of this
>column will contain a very very long output string that part the output data
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:
> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
> >We are using SQL 2K with Sp4.
> >
> >I am doing a Select statement where it contains a column that composes of a
> >concatenation of various strings and many fields. At time, the output of this
> >column will contain a very very long output string that part the output data
> >is being truncated. I assume it exceeds the limit of an output column in
> >the result panel would allow. Is there a setting in SQL I can change to
> >imcrease the size of a column in the result panel in query anaylzer?
> >
> >Wingman
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>> In Query analyzer, go to the menu to Tools, then Options.
>> On the results tab, increase the Maximum characters per
>> column setting.
>> -Sue
>> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
>> <Wingman@.discussions.microsoft.com> wrote:
>> >We are using SQL 2K with Sp4.
>> >
>> >I am doing a Select statement where it contains a column that composes of a
>> >concatenation of various strings and many fields. At time, the output of this
>> >column will contain a very very long output string that part the output data
>> >is being truncated. I assume it exceeds the limit of an output column in
>> >the result panel would allow. Is there a setting in SQL I can change to
>> >imcrease the size of a column in the result panel in query anaylzer?
>> >
>> >Wingman
>>

OUTER JOIN table limit?

I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.

I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?

Thanks.

There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:

with t(i)
as (
select 1
)
select *
from t as t1
left join t as t2
left join t as t3
left join t as t4
left join t as t5
left join t as t6
left join t as t7
left join t as t8
left join t as t9
left join t as t10
on t10.i = t9.i
on t9.i = t8.i
on t8.i = t7.i
on t7.i = t6.i
on t6.i = t5.i
on t5.i = t4.i
on t4.i = t3.i
on t3.i = t2.i
on t2.i = t1.i

|||

as far as sql server is concerned there is no limitation.

it could have been a limitation from data access tier

e.g. dataset

|||

Hello:

I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.

Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "

Is there anything I miss here?

This book was published on September 10, 2002.

Thank you.

|||

That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:

http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx?mfr=true

I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.

|||

Hi Umachandar:

Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.

OUTER JOIN PROBLEM

My SQL Statement as follows:

SELECT S.SessionID,S.SessionName,T.number
FROM Sessions S LEFT OUTER JOIN (SELECT SessionID,COUNT(*) AS number
FROM EventLog
WHERE MachineID = @.machineID
GROUP BY SessionID) AS T
ON S.SessionID = T.SessionID
ORDER BY S.SessionID

The result sets T.number as NULL if there is no record related to SessionName. How to change NULL to ZERO?
Thanks a lot.ISNULL(T.number, 0)|||SELECT S.SessionID,S.SessionName,isnull(T.number,0)

Outer join difficulty

Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The table that is produced is as follows:-

Rundate Accno Sharename NotAtHome
20031128 260869 PANGBOURNE 123200
20031128 260869 PARAMOUNT 221100
20031128 260869 PRISAIB. 221100

The table I wish to join on, is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The result is as follows:-

Rundate Accno Sharename NotAtHome
20031103 260869 PANGBOURNE 123200

Note that Paramount and Prisaib are missing from the second table.

I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

I'd much appreciate any assistance with this..

thanks!(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q1
LEFT OUTER JOIN
(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q2
ON q1.AccNo = q2.AccNo;

Assuming AccNo is a key.

The default value for non-matching rows is Null, however if you do indeed want a numerical then,

I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
from
(
Above query
)|||you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome select t1.Rundate
, t1.Accno
, t1.Sharename
, case when t2.AccNo is null
then 0
else t1.NotAtHome
end as NotAtHome
from dbo.Holdings t1
left outer
join dbo.Holdings t2
on t1.AccNo = t2.AccNo
and t2.Rundate = '20031103'
and t2.Sharename like 'P%'
where t1.Accno = '260869'
and t1.Rundate = '20031128'
and t1.Sharename like 'P%'rudy
http://r937.com/

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>sql

Outer Join - Returning Customers Who Haven't ordered a product

Hi,
Is there any way to make the following LEFT OUTER JOIN statement work in a
similar fashion as the SUBQUERY statement that follows it? Customers 1 and
3
haven't ordered anything for the OrderDate '2006-01-01' and I want to exclud
e
them from the display in the LEFT OUTER JOIN query (like they are excluded i
n
the SUBQUERY statement).
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
customerid customername
-- ----
1 Brian
2 Andy
3 Steve
4 Delaney
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select c.customerid from orders o
where c.customerid = o.customerid
and o.orderdate = '2006-01-01' )
customerid customername
-- ----
2 Andy
4 Delaney
Thanks,
IanHello, Ian
You can use this query:
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
WHERE o.customerid IS NULL
But I think it's better with a subquery (and it's more clear):
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select o.customerid from orders o
where o.orderdate = '2006-01-01' )
Razvan|||Razvan

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
In this case we may want to add AND o.customerid IS NOT NULL
Just my 2 cents
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139292357.248363.83490@.g14g2000cwa.googlegroups.com...
> Hello, Ian
> You can use this query:
> select c.customerid
> , c.customername
> from customers c
> left outer join orders o
> on c.customerid = o.customerid
> and o.orderdate = '2006-01-01'
> WHERE o.customerid IS NULL
> But I think it's better with a subquery (and it's more clear):
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
> Razvan
>|||Hi, Uri
You are right. Since the OP did show the DDL, I assumed that a
"customerid" column in an "orders" table would not allow NULL-s, but I
may be wrong... :) But in this case, I still think that he should fix
the DDL, not the query. Anyway, if we are not talking about orders and
customers, your comment raises a very good point.
Razvan|||Hi Razvan,
Thanks for the very quick answer. I should have tried that myself. I
actually simplified the query because what I really need to do is this: "Lis
t
all customers who have never ordered a particular product". Again, I can do
this via a Subquery with no problem but I'm told this can also be done via
LEFT OUTER JOINS and I can't get the following to work, so I must be missing
something.
SELECT c.CustomerID, p.productname
FROM Customers c
LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
AND p.productname = 'Toshiba TV'
GROUP BY c.CustomerID,p.productname
ORDER BY c.CustomerID
The result set I get is this:
CustomerID productname
-- ----
1001 Toshiba TV
1001 NULL
1002 NULL
1003 Toshiba TV
1003 NULL
1004 Toshiba TV
1004 NULL
1005 NULL
1005 Toshiba TV
1006 NULL
1007 NULL
1007 Toshiba TV
1008 Toshiba TV
1008 NULL
1009 Toshiba TV
1009 NULL
1010 NULL
1010 Toshiba TV
What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 from
the display because they have never ordered a "Toshiba TV". Adding a WHERE
clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY claus
e
doesn't work (it results in 0 rows being returned).
Thanks,
Ian|||Hi guys,
Actually, adding an AND clause won't work here as it returns all four rows
that I mention in my original post. My original SUBQUERY without the AND
clause gives me what I want (i.e. just the two rows). Nulls are not allowed
for Customerid in the Order table.

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01'
and o.customerid IS NULL)
"Razvan Socol" wrote:

> Hi, Uri
> You are right. Since the OP did show the DDL, I assumed that a
> "customerid" column in an "orders" table would not allow NULL-s, but I
> may be wrong... :) But in this case, I still think that he should fix
> the DDL, not the query. Anyway, if we are not talking about orders and
> customers, your comment raises a very good point.
> Razvan
>|||Rats...I got it backwards. What I meant to say at the bottom of my post was
that I *only* want to report Customers 1002 and 1006 because they are the
only ones who have *not* ordered a Toshiba TV at some point in time.
"Ian Fisher" wrote:

> Hi Razvan,
> Thanks for the very quick answer. I should have tried that myself. I
> actually simplified the query because what I really need to do is this: "L
ist
> all customers who have never ordered a particular product". Again, I can
do
> this via a Subquery with no problem but I'm told this can also be done via
> LEFT OUTER JOINS and I can't get the following to work, so I must be missi
ng
> something.
> SELECT c.CustomerID, p.productname
> FROM Customers c
> LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
> LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
> LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
> AND p.productname = 'Toshiba TV'
> GROUP BY c.CustomerID,p.productname
> ORDER BY c.CustomerID
> The result set I get is this:
> CustomerID productname
> -- ----
> 1001 Toshiba TV
> 1001 NULL
> 1002 NULL
> 1003 Toshiba TV
> 1003 NULL
> 1004 Toshiba TV
> 1004 NULL
> 1005 NULL
> 1005 Toshiba TV
> 1006 NULL
> 1007 NULL
> 1007 Toshiba TV
> 1008 Toshiba TV
> 1008 NULL
> 1009 Toshiba TV
> 1009 NULL
> 1010 NULL
> 1010 Toshiba TV
> What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 fro
m
> the display because they have never ordered a "Toshiba TV". Adding a WHER
E
> clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY cla
use
> doesn't work (it results in 0 rows being returned).
> Thanks,
> Ian|||Hello, Ian
Please post DDL (as CREATE TABLE statements) and sample data (as INSERT
INTO ... VALUES ...). See: http://www.aspfaq.com/etiquette.asp?id=5006
I think it's possible to do this with LEFT JOIN-s instead of a "NOT IN"
subquery, but it will require a derived table (a subquery in the FROM
clause) or a view.
Razvan|||Hi Razvan,
I figured it out with the help of a colleague....thanks a lot for your help
.
SELECT c.CustomerID, c.CustFirstName, c.CustLastName
FROM Customers c
LEFT JOIN
(SELECT c1.customerid as cid from customers c1
join orders o on o.CustomerID = c1.customerid
JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
JOIN Products p ON od.ProductNumber = p.ProductNumber
WHERE p.productname = 'Viscount Microshell Helmet') c2
ON c2.cid = c.CustomerID
WHERE c2.cid IS NULL
Best Regards,
Ian|||> [...] what I really need to do is this: "List all customers
> who have never ordered a particular product".
> Again, I can do this via a Subquery with no problem
> but I'm told this can also be done via LEFT OUTER JOINS [...]
Since you did not provide DDL and sample data, I have used Northwind
for the following queries. After a little more thinking, I have found a
way to write this query using outer joins, even without using a derived
table.
So, instead of this simple and clear query:
SELECT CompanyName FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
WHERE p.ProductName='Chang'
)
we can write a query using LEFT JOIN-s, with a weird placement of the
ON clauses:
SELECT CompanyName FROM Customers c
LEFT JOIN Orders o
LEFT JOIN [Order details] od
LEFT JOIN Products p ON od.ProductID=p.ProductID
ON o.OrderID=od.OrderID
ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
or we can use a RIGHT JOIN:
SELECT CompanyName FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
RIGHT JOIN Customers c ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
However, if we examine the execution plans, we can see that the first
query (which is also easier to understand) has a lower cost than the
other two queries (which have the same execution plan).
For more informations, see:
http://groups.google.com/group/micr...br />
2579509b
http://groups.google.com/group/micr...br />
22a96b90
Razvan

outer join

Hello,

I am having problems with an outer join statement.
I have written a procedure that tests a table for missing and corrupt data and
to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:

select o.*,'#',k.* from repaired o right outer join copy k on
(str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) )
where
o.D<>k.D or
o.E<>k.E or
o.F<>k.F or
...

I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
Before that it looked like:

... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...

In table "copy" is a record missing that is in table "repaired".
Why is my statement not printingout that missing record?
Shouldn't be an outer join exactly what I have to use for finding missing data?

I anybody can help me, I would be very happy.

SvenIn table "copy" is a record missing that is in table "repaired".try LEFT OUTER JOIN instead ;)|||Your first join attempt: ... on (o.a=k.a and o.b=o.b and o.c = k.c) where ... joins o.b on itself. This sort of typo happens with the gratuitous use of table aliases.
Use a left outer join, as Rudy has suggested.
Try using the binary_checksum value rather than listing all your columns in the where clause:select repaired.*,
'#',
copy.*
from --repaired
(select binary_checksum(*) checkvalue,
repaired.*
from repaired) repaired
left outer join --copy
(select binary_checksum(*) checkvalue,
repaired.*
from copy) copy
on repaired.a = copy.a
and repaired.b = copy.b
and repaired.c = copy.c
where repaired.checkvalue <> copy.checkvalue

out to expand tempdb log file

I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file ?
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:

>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file ?
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
|||Thanks Mike
no this was just a sample, just to illustrate the question.
thanks for answering . regards
*** Sent via Developersdex http://www.codecomments.com ***

out to expand tempdb log file

I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksIt all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:

>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the lo
g
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>|||Thanks Mike
no this was just a sample, just to illustrate the question.
thanks for answering . regards
*** Sent via Developersdex http://www.codecomments.com ***

out to expand tempdb log file

I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksThis is a multi-part message in MIME format.
--020102060703060100090302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
--020102060703060100090302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It all depends on the amount of "temp" work your server does
(joins, temp tables, temp variables, etc.). Although a tempdb log that
starts at 5MB and can autogrow by 1MB to a max size of 10MB is the
smallest tempdb log file I've ever seen. My small-to-medium sized
production servers have tempdb log files that range from 100MB to 2GB.<br>
<br>
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for
space?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
David Tucker wrote:
<blockquote cite="midOS3S0DhdFHA.1504@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
</pre>
</blockquote>
</body>
</html>
--020102060703060100090302--

Out put of Select Statement Stored in a variable to another Variab

Hi Guys,
I need a solution in SQL Server 2000. I wanted to store the output of a
select query into a variable. The Select Statement is stored in a variable
.
the sample is given below.
declare @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec(@.Tmp1)
print @.Tmp2
Do any one have the idea.
Thanks in advance
Regds
Kag"Kag" <Kag@.discussions.microsoft.com> wrote in message
news:304B2B5C-AE55-491A-9D26-88F205861757@.microsoft.com...
> Hi Guys,
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
>
You will need to use a Temp table. The variables are only valid within the
scope in which they are defined. Temp tables are part of your session.
DECLARE @.tmp varchar(100),
@.tmp1 varchar(100)
CREATE TABLE #ReturnValue (tmp2 varchar(15) NULL)
SET @.tmp = 'columntitle FROM bworksheetdtl WHERE Worksheetcode=1 AND
serialno = 5'
SET @.tmp1 = 'INSERT #ReturnValue (tmp2) SELECT ' + @.tmp
EXECUTE (@.tmp1)
SELECT tmp2 FROM #ReturnValue
DROP TABLE #ReturnValue
Rick Sawtell
MCT, MCSD, MCDBA|||The @.Temp2 variable does not exist (is not declared) inside the context of
your EXECUTE statement.
Take a look at sp_executesql in Books Online. It accepts parameters and
really simplifies execution of dynamic SQL.
On the other hand, if you post DDL and sample data and specify what you're
actually trying to achieve, we can help you find a more efficient solution.
ML|||Hi Kag,
Would you like to try this?
DECLARE @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
SELECT @.tmp2 = columntitle FROM bworksheetdtl WHERE Worksheetcode = 1 and
serialno = 5
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Hi Kag,
This may be what you want:
Declare @.tmp varchar(15)
SELECT @.temp = columntitle FROM bworksheetdtl WHERE Worksheetcode=1 and
serialno=5
PRINT @.tmp
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100), columntitle from bworksheetdtl where Worksheetcode=1
and
> serialno=5
> @.Tmp2 Varchar(15)
> set @.Tmp = ''
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Pl try code below
declare @.Tmp nVarchar(100), -- varchar changed to nvarchar
@.Tmp1 nVarchar(100), -- varchar changed to nvarchar
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec sp_executesql @.Tmp1, N'@.Tmp2 Varchar(15) output', @.Tmp2 output -- alter
ed
print @.Tmp2
Rakesh
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Thanks Rakesh and ML. I tried the solution you have given with
sp_executesql and it was the one i was searching for. Special thanks to
Rakesh for the exact example. Thanks again guys..
Kag
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kagsql

Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks