Creating a linked server to DB2 from SQl server 2000 64bit

Hi,
I am trying to create a linked server to DB2 using IBM
64 bit OLEDB provider, as Microsoft doesn't have a DB2
OLEDB driver for 64bit.
When I test the connection to DB2 using the driver, it
works, but when I try to query DB2 using a OPENQUERY it
gives me this message.
"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
Authentication Failed".
The userID I am using in the linked server properties, has
access to DB2 tables.
Is there anyway I could trace, to see where the connection
is failing.
I would really appreciate if someone could help me with
this.
Please let me know if I am posting on the wrong Newsgroup.
Thanks
Geetha
You can turn on a trace flag to try to get additional
information on the 7399 error. Execute
dbcc traceon(7300,3604)
and then run the query. You could also use profiler and
capture the OLEDB Errors event.
-Sue
On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
<gdabbara@.brownshoe.com> wrote:

>Hi,
> I am trying to create a linked server to DB2 using IBM
>64 bit OLEDB provider, as Microsoft doesn't have a DB2
>OLEDB driver for 64bit.
>When I test the connection to DB2 using the driver, it
>works, but when I try to query DB2 using a OPENQUERY it
>gives me this message.
>"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
>Authentication Failed".
>The userID I am using in the linked server properties, has
>access to DB2 tables.
>Is there anyway I could trace, to see where the connection
>is failing.
>I would really appreciate if someone could help me with
>this.
>Please let me know if I am posting on the wrong Newsgroup.
>Thanks
>Geetha
|||When I set the trace. I see this error message.
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB
provider 'IBMDADB2.1'.
OLE DB error trace [Non-interface error: Provider not
registered.].
I can see 'IBMDADB2.1' in the registry. How can I register
the provider? Thanks for your help.
-Geetha.
[vbcol=seagreen]
>--Original Message--
>You can turn on a trace flag to try to get additional
>information on the 7399 error. Execute
>dbcc traceon(7300,3604)
>and then run the query. You could also use profiler and
>capture the OLEDB Errors event.
>-Sue
>On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
><gdabbara@.brownshoe.com> wrote:
IBM[vbcol=seagreen]
has[vbcol=seagreen]
connection[vbcol=seagreen]
Newsgroup.
>.
>
|||Try reinstalling the DB2 client. You could try just
unregistering and reregistering the dll for the provider
using regsvr32.exe but it might be safer to just reinstall
the client in case other files didn't get registered or
installed correctly.
-Sue
On Thu, 8 Jul 2004 08:47:37 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When I set the trace. I see this error message.
>Server: Msg 7403, Level 16, State 1, Line 1
>Could not locate registry entry for OLE DB
>provider 'IBMDADB2.1'.
>OLE DB error trace [Non-interface error: Provider not
>registered.].
>I can see 'IBMDADB2.1' in the registry. How can I register
>the provider? Thanks for your help.
>-Geetha.
>IBM
>has
>connection
>Newsgroup.

Creating a linked server to DB2 from SQl server 2000 64bit

Hi,
I am trying to create a linked server to DB2 using IBM
64 bit OLEDB provider, as Microsoft doesn't have a DB2
OLEDB driver for 64bit.
When I test the connection to DB2 using the driver, it
works, but when I try to query DB2 using a OPENQUERY it
gives me this message.
"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
Authentication Failed".
The userID I am using in the linked server properties, has
access to DB2 tables.
Is there anyway I could trace, to see where the connection
is failing.
I would really appreciate if someone could help me with
this.
Please let me know if I am posting on the wrong Newsgroup.
Thanks
GeethaYou can turn on a trace flag to try to get additional
information on the 7399 error. Execute
dbcc traceon(7300,3604)
and then run the query. You could also use profiler and
capture the OLEDB Errors event.
-Sue
On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
<gdabbara@.brownshoe.com> wrote:

>Hi,
> I am trying to create a linked server to DB2 using IBM
>64 bit OLEDB provider, as Microsoft doesn't have a DB2
>OLEDB driver for 64bit.
>When I test the connection to DB2 using the driver, it
>works, but when I try to query DB2 using a OPENQUERY it
>gives me this message.
>"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
>Authentication Failed".
>The userID I am using in the linked server properties, has
>access to DB2 tables.
>Is there anyway I could trace, to see where the connection
>is failing.
>I would really appreciate if someone could help me with
>this.
>Please let me know if I am posting on the wrong Newsgroup.
>Thanks
>Geetha|||When I set the trace. I see this error message.
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB
provider 'IBMDADB2.1'.
OLE DB error trace [Non-interface error: Provider not
registered.].
I can see 'IBMDADB2.1' in the registry. How can I register
the provider? Thanks for your help.
-Geetha.

>--Original Message--
>You can turn on a trace flag to try to get additional
>information on the 7399 error. Execute
>dbcc traceon(7300,3604)
>and then run the query. You could also use profiler and
>capture the OLEDB Errors event.
>-Sue
>On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
><gdabbara@.brownshoe.com> wrote:
>
IBM[vbcol=seagreen]
has[vbcol=seagreen]
connection[vbcol=seagreen]
Newsgroup.[vbcol=seagreen]
>.
>|||Try reinstalling the DB2 client. You could try just
unregistering and reregistering the dll for the provider
using regsvr32.exe but it might be safer to just reinstall
the client in case other files didn't get registered or
installed correctly.
-Sue
On Thu, 8 Jul 2004 08:47:37 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When I set the trace. I see this error message.
>Server: Msg 7403, Level 16, State 1, Line 1
>Could not locate registry entry for OLE DB
>provider 'IBMDADB2.1'.
>OLE DB error trace [Non-interface error: Provider not
>registered.].
>I can see 'IBMDADB2.1' in the registry. How can I register
>the provider? Thanks for your help.
>-Geetha.
>
>IBM
>has
>connection
>Newsgroup.|||hi,
even i am getting the error like
OLE DB error trace[Non-interface error:Provider not registered.].
Could not locate registry entry for OLE DB provider
'provider=Microsoft.Jet.OLEDB.4.0'.
Deferred prepare could not be completed.
As sue said try to reinstall the DB2 client. is it possible to
reinstall the oledb driver. how can i do it? where can i view the
registry files ? please help me its very urgent?
thans shybi
shybi
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message836063.html|||The provider is installed with MDAC. You can download MDAC
versions for reinstall and also download component checker
to check the MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
Using Component Checker is a good way to check your MDAC
installation - easier than going through the registry.
Component Checker will check for mismatches.
-Sue
On Tue, 1 Aug 2006 01:06:46 -0500, shybi
<shybi.2buky9@.mail.mcse.ms> wrote:

>hi,
>even i am getting the error like
>OLE DB error trace[Non-interface error:Provider not registered.].
>Could not locate registry entry for OLE DB provider
>'provider=Microsoft.Jet.OLEDB.4.0'.
>Deferred prepare could not be completed.
>As sue said try to reinstall the DB2 client. is it possible to
>reinstall the oledb driver. how can i do it? where can i view the
>registry files ? please help me its very urgent?
>thans shybi

creating a database link between DB2 and SQL server

Hi all,

My requirement is to migrate all the data from DB2 database to SQL Server database. Is it possible to create a database link between these two databases.

regards,
Naren

Quote:

Originally Posted by ntiruhar

Hi all,

My requirement is to migrate all the data from DB2 database to SQL Server database. Is it possible to create a database link between these two databases.

regards,
Naren

Question is being moved to the SQL Server forum.

ADMIN

creating a connection to DB2 from sql server

Can We Create a SQL Server connection to DB2 using linked servers?If yes, how can we do that.ANd one more thing is that my DB2 is on unix and sqlserver is on windows.
Thanks.This MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_1gvm.asp link will explain the scenario.

Creating a common table expression--temporary table--using TSQL??

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.|||You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.

Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2

You can just about anything with a temp table that you can with a
normal table, including indexes.

HTH
Paul|||Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

On 28 Dec 2004 07:19:13 -0800, "Paul" <stpaul_71@.yahoo.com> wrote:

>You did not say what version of MSSQL you are on so I will assume 2000.
>This is straight from the TSQL books.
>Temporary Tables
>SQL Server supports temporary tables. These tables have names that
>start with a number sign (#). If a temporary table is not dropped when
>a user disconnects, SQL Server automatically drops the temporary table.
>Temporary tables are not stored in the current database; they are
>stored in the tempdb system database.
>There are two types of temporary tables:
>Local temporary tables
>The names of these tables begin with one number sign (#). These tables
>are visible only to the connection that created them.
>Global temporary tables
>The names of these tables begin with two number signs (##). These
>tables are visible to all connections. If the tables are not dropped
>explicitly before the connection that created them disconnects, they
>are dropped as soon as all other tasks stop referencing them. No new
>tasks can reference a global temporary table after the connection that
>created it disconnects. The association between a task and a table is
>always dropped when the current statement completes executing;
>therefore, global temporary tables are usually dropped soon after the
>connection that created them disconnects.
>Many traditional uses of temporary tables can now be replaced with
>variables that have the table data type.
>
>
>Example
>create table #TempTable (col1 varchar(10), col2 bit)
>insert into #TempTable values('asdf', 1)
>select * from #TempTable
>select * into #TempTable2 from #TempTable
>select * from #TempTable2
>drop table #TempTable
>drop table #TempTable2
>
>You can just about anything with a temp table that you can with a
>normal table, including indexes.
>
>HTH
>Paul|||On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >Using SQL against a DB2 table the 'with' key word is used to
> >dynamically create a temporary table with an SQL statement that is
> >retained for the duration of that SQL statement.
> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >one, what is the TSQL solution to creating a temporary table that is
> >associated with an SQL statement? Examples would be appreciated.
> >Thank you!!
> Hi Randi,
> I don't know if it's exactly the same as the DB2 version (probably not),
> but SQL Server supports derived table expressions. Example (from BOL):
> USE pubs
> GO
> SELECT ST.stor_id, ST.stor_name
> FROM stores AS ST,
> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> FROM sales
> GROUP BY stor_id
> ) AS SA
> WHERE ST.stor_id = SA.stor_id
> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability, and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.

--
JAG|||Hi John,

Thanks for your explanation. I had heard that WITH would be introduced in
SQL Server 2005; unfortunately, I'll have to wait a little longer before
I'll get a chance to actually play with it. (I don't have a spare system
lying around that I can use to safely toy with beta software).

It does look promising, though. I'm sure I'll really get to like this
feature once I have it available!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> Hi Randi,
>>
>> I don't know if it's exactly the same as the DB2 version (probably not),
>> but SQL Server supports derived table expressions. Example (from BOL):
>>
>> USE pubs
>> GO
>> SELECT ST.stor_id, ST.stor_name
>> FROM stores AS ST,
>> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> FROM sales
>> GROUP BY stor_id
>> ) AS SA
>> WHERE ST.stor_id = SA.stor_id
>> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>>
>> Best, Hugo
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>Hi Hugo, a common table expression, provided by the WITH
>clause, is defined in Standard SQL (beginning with SQL:1999)
>and is implemented in SQL Server 2005. Semantically, the WITH
>clause is similar to defining one or more views whose scope and
>extent is the enclosed query. Factoring out and naming these
>common subexpressions in a query is meant to aid readability,
>conciseness, maintainability, and even efficiency. There are cases
>when a derived table is a perfectly good alternative, however,
>when that derived table is used multiple times in the query a
>common table expression becomes handy.
>Taking the BOL example from above, imagine you wanted to
>rank stores in decreasing order by number of distinct titles. Using
>WITH, one could write (admittedly, in this case a view is a reasonable
>choice too):
>WITH DistinctTitles (stor_id, title_count) AS
> (SELECT stor_id, COUNT(DISTINCT title_id)
> FROM sales
> GROUP BY stor_id)
>SELECT T1.stor_id, T1.title_count,
> COUNT(DISTINCT T2.title_count) AS stor_rank
>FROM DistinctTitles AS T1
> INNER JOIN
> DistinctTitles AS T2
> ON T2.title_count >= T1.title_count
>GROUP BY T1.stor_id, T1.title_count;
>It's also through the WITH clause that we can define recursive queries. This
>is where WITH truly shines.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >>
> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >dynamically create a temporary table with an SQL statement that is
> >> >retained for the duration of that SQL statement.
> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >associated with an SQL statement? Examples would be appreciated.
> >> >Thank you!!
> >>
> >> Hi Randi,
> >>
> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> but SQL Server supports derived table expressions. Example (from BOL):
> >>
> >> USE pubs
> >> GO
> >> SELECT ST.stor_id, ST.stor_name
> >> FROM stores AS ST,
> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> FROM sales
> >> GROUP BY stor_id
> >> ) AS SA
> >> WHERE ST.stor_id = SA.stor_id
> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >>
> >> Best, Hugo
> >> --
> >>
> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >Hi Hugo, a common table expression, provided by the WITH
> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >and is implemented in SQL Server 2005. Semantically, the WITH
> >clause is similar to defining one or more views whose scope and
> >extent is the enclosed query. Factoring out and naming these
> >common subexpressions in a query is meant to aid readability,
> >conciseness, maintainability, and even efficiency. There are cases
> >when a derived table is a perfectly good alternative, however,
> >when that derived table is used multiple times in the query a
> >common table expression becomes handy.
> >Taking the BOL example from above, imagine you wanted to
> >rank stores in decreasing order by number of distinct titles. Using
> >WITH, one could write (admittedly, in this case a view is a reasonable
> >choice too):
> >WITH DistinctTitles (stor_id, title_count) AS
> > (SELECT stor_id, COUNT(DISTINCT title_id)
> > FROM sales
> > GROUP BY stor_id)
> >SELECT T1.stor_id, T1.title_count,
> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >FROM DistinctTitles AS T1
> > INNER JOIN
> > DistinctTitles AS T2
> > ON T2.title_count >= T1.title_count
> >GROUP BY T1.stor_id, T1.title_count;
> >It's also through the WITH clause that we can define recursive queries. This
> >is where WITH truly shines.
> Just curious - can the scope of a WITH be more than one query? An entire
> stored procedure, for instance?

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.

--
JAG|||On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
>> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
>>
>> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>> >>
>> >> >Using SQL against a DB2 table the 'with' key word is used to
>> >> >dynamically create a temporary table with an SQL statement that is
>> >> >retained for the duration of that SQL statement.
>> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >> >one, what is the TSQL solution to creating a temporary table that is
>> >> >associated with an SQL statement? Examples would be appreciated.
>> >> >Thank you!!
>> >>
>> >> Hi Randi,
>> >>
>> >> I don't know if it's exactly the same as the DB2 version (probably not),
>> >> but SQL Server supports derived table expressions. Example (from BOL):
>> >>
>> >> USE pubs
>> >> GO
>> >> SELECT ST.stor_id, ST.stor_name
>> >> FROM stores AS ST,
>> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> >> FROM sales
>> >> GROUP BY stor_id
>> >> ) AS SA
>> >> WHERE ST.stor_id = SA.stor_id
>> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>> >>
>> >> Best, Hugo
>> >> --
>> >>
>> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>> >Hi Hugo, a common table expression, provided by the WITH
>> >clause, is defined in Standard SQL (beginning with SQL:1999)
>> >and is implemented in SQL Server 2005. Semantically, the WITH
>> >clause is similar to defining one or more views whose scope and
>> >extent is the enclosed query. Factoring out and naming these
>> >common subexpressions in a query is meant to aid readability,
>> >conciseness, maintainability, and even efficiency. There are cases
>> >when a derived table is a perfectly good alternative, however,
>> >when that derived table is used multiple times in the query a
>> >common table expression becomes handy.
>>> >Taking the BOL example from above, imagine you wanted to
>> >rank stores in decreasing order by number of distinct titles. Using
>> >WITH, one could write (admittedly, in this case a view is a reasonable
>> >choice too):
>>> >WITH DistinctTitles (stor_id, title_count) AS
>> > (SELECT stor_id, COUNT(DISTINCT title_id)
>> > FROM sales
>> > GROUP BY stor_id)
>> >SELECT T1.stor_id, T1.title_count,
>> > COUNT(DISTINCT T2.title_count) AS stor_rank
>> >FROM DistinctTitles AS T1
>> > INNER JOIN
>> > DistinctTitles AS T2
>> > ON T2.title_count >= T1.title_count
>> >GROUP BY T1.stor_id, T1.title_count;
>>> >It's also through the WITH clause that we can define recursive queries. This
>> >is where WITH truly shines.
>>
>> Just curious - can the scope of a WITH be more than one query? An entire
>> stored procedure, for instance?
>No, a WITH clause encloses a single query expression and can be used
>anywhere a query is used, e.g., in defining a view.

Darn - I thought this would finally be a tool for removing SQL code
duplication within stored procedures. Does SQL Server 2005 offer some other
new feature to do this?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:5kr4t0l92t2pcit2rbgpa7c9naiutb7853@.4ax.com...
> On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> >news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> >> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >>
> >> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >> >>
> >> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >> >dynamically create a temporary table with an SQL statement that is
> >> >> >retained for the duration of that SQL statement.
> >> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >> >associated with an SQL statement? Examples would be appreciated.
> >> >> >Thank you!!
> >> >>
> >> >> Hi Randi,
> >> >>
> >> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> >> but SQL Server supports derived table expressions. Example (from BOL):
> >> >>
> >> >> USE pubs
> >> >> GO
> >> >> SELECT ST.stor_id, ST.stor_name
> >> >> FROM stores AS ST,
> >> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> >> FROM sales
> >> >> GROUP BY stor_id
> >> >> ) AS SA
> >> >> WHERE ST.stor_id = SA.stor_id
> >> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >> >>
> >> >> Best, Hugo
> >> >> --
> >> >>
> >> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >> >> >Hi Hugo, a common table expression, provided by the WITH
> >> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >> >and is implemented in SQL Server 2005. Semantically, the WITH
> >> >clause is similar to defining one or more views whose scope and
> >> >extent is the enclosed query. Factoring out and naming these
> >> >common subexpressions in a query is meant to aid readability,
> >> >conciseness, maintainability, and even efficiency. There are cases
> >> >when a derived table is a perfectly good alternative, however,
> >> >when that derived table is used multiple times in the query a
> >> >common table expression becomes handy.
> >> >> >Taking the BOL example from above, imagine you wanted to
> >> >rank stores in decreasing order by number of distinct titles. Using
> >> >WITH, one could write (admittedly, in this case a view is a reasonable
> >> >choice too):
> >> >> >WITH DistinctTitles (stor_id, title_count) AS
> >> > (SELECT stor_id, COUNT(DISTINCT title_id)
> >> > FROM sales
> >> > GROUP BY stor_id)
> >> >SELECT T1.stor_id, T1.title_count,
> >> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >> >FROM DistinctTitles AS T1
> >> > INNER JOIN
> >> > DistinctTitles AS T2
> >> > ON T2.title_count >= T1.title_count
> >> >GROUP BY T1.stor_id, T1.title_count;
> >> >> >It's also through the WITH clause that we can define recursive queries. This
> >> >is where WITH truly shines.
> >>
> >> Just curious - can the scope of a WITH be more than one query? An entire
> >> stored procedure, for instance?
> >No, a WITH clause encloses a single query expression and can be used
> >anywhere a query is used, e.g., in defining a view.
> Darn - I thought this would finally be a tool for removing SQL code
> duplication within stored procedures. Does SQL Server 2005 offer some other
> new feature to do this?

The idea behind the common table expression in a WITH clause is that
it doesn't act like a macro but is instead evaluated to a virtual table
that is used in each place where it's referenced in the enclosed query.
So in a stored procedure one might use a temp table or table variable
to store an intermediate result in lieu of such an animal. Nothing exciting
I'm afraid.

--
JAG|||John Gilson (jag@.acm.org) writes:
> The idea behind the common table expression in a WITH clause is that it
> doesn't act like a macro but is instead evaluated to a virtual table
> that is used in each place where it's referenced in the enclosed query.
> So in a stored procedure one might use a temp table or table variable
> to store an intermediate result in lieu of such an animal. Nothing
> exciting I'm afraid.

Nah, the current implementation appears to be quite macro-like, at least
for non-recursive queries.

When I look at the query plan for the query below, the CTE is computed
many times. For this query a temp table or a table variable would be
a much better alternative.

CREATE TABLE prodreport (id int NOT NULL,
product1 int NOT NULL,
product2 int NULL,
product3 int NULL,
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT prodreport (id, product1)
SELECT PurchaseOrderID, MIN(ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
go
-- This is the query of the show.
WITH temp (id, productid, rowno) AS
(SELECT PurchaseOrderID, ProductID,
rowno = (SELECT COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p2
WHERE p1.PurchaseOrderID = p2.PurchaseOrderID
AND p1.ProductID >= p2.ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1)
UPDATE prodreport
SET product1 = t1.productid,
product2 = t2.productid,
product3 = t3.productid
FROM prodreport r
JOIN temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN temp t3 ON t3.id = r.id
AND t3.rowno = 3

SELECT * FROM prodreport
go
DROP TABLE prodreport
go

I should that Umachandar Jaychandran, a former SQL Server MVP, rewrote
the query in this way:

WITH top_3_prods(id, productid, rowno) AS
(
SELECT PurchaseOrderID, ProductID,
ROW_NUMBER() OVER(PARTITION BY PurchaseOrderId
ORDER BY ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1
) ,
pvt_top_3_prods (id, product1, product2, product3) AS
(
SELECT id, [1], [2], [3]
FROM top_3_prods
PIVOT (min(ProductId) for rowno in ( [1], [2], [3] )) as pv
)
UPDATE prodreport
SET product1 = t1.product1,
product2 = t1.product2,
product3 = t1.product3
FROM prodreport r
JOIN pvt_top_3_prods t1 ON t1.id = r.id

There's a whole fireworks of new T-SQL features in that one!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp