cross database queries

i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:

rs.open "select ...", dbname, ..., adopendynamic, adlockoptimistic ...

thanksWrite a stored procedure. :)

CROSS APPLY!

OK, I finally figured out what all the hubbub is about with CROSS APPLY and XML!

It lets you "cross" a parent node with its children, and I guess in a relatively efficient manner.

I suppose lots of people already understood this, but my XML book hasn't arrived from B&N yet, and BOL hasn't been the best tutorial on this stuff. Anyway, right now I'm one fellow feeling smart.

A few more tricks like this, and I'll take off my XML newbie hat.

ps - as soon as I can figure out this posting interface I'll really feel smart!

The CROSS APPLY join is one of the features that was added in SQL Server 2005. Yes, I find it useful for a number of XML queries. This feature also provides additional usefulness for table valued functions -- especially inline functions.

( OT, I think )

Cross apply requires 9.0 compatibility

If SQL 2000 must access a 2005 database continually, does it require setting
the compatibility to 8.0? What are the consequences of setting the 2005
database to 9.0 under these circumstances? (Trying to get cross apply to
work with 8.0 compatibility set on SQL 2005).
Regards,
Jamie
Got it... fire off from master which will use version 9.0 irregardless of
server setting.
Regards,
Jamie
"thejamie" wrote:

> If SQL 2000 must access a 2005 database continually, does it require setting
> the compatibility to 8.0? What are the consequences of setting the 2005
> database to 9.0 under these circumstances? (Trying to get cross apply to
> work with 8.0 compatibility set on SQL 2005).
> --
> Regards,
> Jamie
|||That's a useful tidbit there!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:845339A7-B70C-4BEB-B556-2C3F061318D1@.microsoft.com...[vbcol=seagreen]
> Got it... fire off from master which will use version 9.0 irregardless of
> server setting.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:

Cross apply requires 9.0 compatibility

If SQL 2000 must access a 2005 database continually, does it require setting
the compatibility to 8.0? What are the consequences of setting the 2005
database to 9.0 under these circumstances? (Trying to get cross apply to
work with 8.0 compatibility set on SQL 2005).
--
Regards,
JamieGot it... fire off from master which will use version 9.0 irregardless of
server setting.
--
Regards,
Jamie
"thejamie" wrote:

> If SQL 2000 must access a 2005 database continually, does it require setti
ng
> the compatibility to 8.0? What are the consequences of setting the 2005
> database to 9.0 under these circumstances? (Trying to get cross apply to
> work with 8.0 compatibility set on SQL 2005).
> --
> Regards,
> Jamie|||That's a useful tidbit there!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:845339A7-B70C-4BEB-B556-2C3F061318D1@.microsoft.com...[vbcol=seagreen]
> Got it... fire off from master which will use version 9.0 irregardless of
> server setting.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>

Cross apply requires 9.0 compatibility

If SQL 2000 must access a 2005 database continually, does it require setting
the compatibility to 8.0? What are the consequences of setting the 2005
database to 9.0 under these circumstances? (Trying to get cross apply to
work with 8.0 compatibility set on SQL 2005).
--
Regards,
JamieGot it... fire off from master which will use version 9.0 irregardless of
server setting.
--
Regards,
Jamie
"thejamie" wrote:
> If SQL 2000 must access a 2005 database continually, does it require setting
> the compatibility to 8.0? What are the consequences of setting the 2005
> database to 9.0 under these circumstances? (Trying to get cross apply to
> work with 8.0 compatibility set on SQL 2005).
> --
> Regards,
> Jamie|||That's a useful tidbit there!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:845339A7-B70C-4BEB-B556-2C3F061318D1@.microsoft.com...
> Got it... fire off from master which will use version 9.0 irregardless of
> server setting.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>> If SQL 2000 must access a 2005 database continually, does it require
>> setting
>> the compatibility to 8.0? What are the consequences of setting the 2005
>> database to 9.0 under these circumstances? (Trying to get cross apply to
>> work with 8.0 compatibility set on SQL 2005).
>> --
>> Regards,
>> Jamie

Cross Apply Function

I am unsure how to select data within one table, and then select additional data from the result set.

There is an audit log table with columns:

Year, Month, Amount, AuditType, UniqueID, DateTime

AuditType: O = Original

AuditType: U = Update

Sample Data:

2007, January, 500, O, 333555, 3/10/2007 3:30:00 PM

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM

2007, February, 500, O, 333556, 3/10/2007 3:30:00 PM

2007, March, 500, O, 333557, 3/10/2007 3:30:00 PM

I am trying to find all Updates in the table, ie where AuditType = U.

Then, for every Update, I want to see what was updated, ie the last record with that same UniqueID. Im guessing I should use an INNER JOIN or UNION with a SELECT TOP 1 ORDER BY DateTime but not sure how do to this.

Data returned should be:

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM, 500

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM, 1000

Any ideas greatly appreciated!

Which version of SQL Server do you use?|||SQL 2005 SP1|||

You could use CROSS APPLY statement:

Code Snippet

create table Log(

[Year] int,

[Month] varchar(10),

Amount decimal,

AuditType char(1),

UniqueID int,

date DateTime

)

insert into Log values(2007, 'January', 500, 'O', 333555, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1000, 'U', 333555, '4/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1200, 'U', 333555, '5/10/2007 3:30:00 PM')

insert into Log values(2007, 'February', 500, 'O', 333556, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'March', 500, 'O', 333557, '3/10/2007 3:30:00 PM')

--Returns previous update

CREATE function udf_earlyUpdate(@.ID int,@.dt datetime) RETURNS TABLE

AS

RETURN SELECT TOP 1 Amount FROM Log WHERE UniqueID = @.ID and date<@.dt ORDER BY date DESC

select * from LOG CROSS APPLY udf_earlyUpdate(UniqueID,Date)

WHERE log.AuditType='U'

|||

I'm a bit confused about your goals.

You indicate that

the last record with that same UniqueID

, but your expected results show two records for the same UniqueID.

From you description, I would have expected that you wanted only one record for UniqueID 333555.

Please clarify.

|||

Every update should have one record. But a record with the same Unique ID can be updated multiple times.

The results should show every update, even if its the same Unique ID and what was updated.

|||

Here's the basic idea, I'll leave it to you to fill in the specific blanks.

select <alias>.columns

from (<any select statement that you want to specify, if calcing aggregates, alias each column) AS <alias>

Why does this work? A select statement simply returns a result set, which has exactly the same definition as a table. If you make sure each column in the select statement has a specific "column name", then it works just like a table. So, you simply wrap parens around the select statement within the FROM clause. The key to the whole thing is that you alias the select statement so that it really can be referenced in memory looking just like a table.

|||

The CROSS APPLY function works great with a simple SELECT. However, when I attempt to create a VIEW with this function combining two tables, I get an error because they contain the same column.

How can I get the result of the Function without using SELECT *, ie without selection all columns?

"Column names in each view or function must be unique. Column name 'BUDGETAMT' in view or function 'V_BUDGET_UPDATE_ALL' is specified more than once."

CROSS APPLY doesn't work in some databases

I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).

A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?

Here is the simplest testcase I can come up with:
select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) b

If this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.

As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
if run from one of the other user databases.

Any ideas on what could be wrong?

Thanks,

Vince
Sorry to reply in my own thread, but I found the problem. I took drastic measures and created a new, empty database, then deleted all objects out of one of the ones that didn't work. The new one worked, the old one didn't.

Then I started looking at every dialog in SMSS for each database. The difference (which everyone else has probably figured out already) was the compatability level. The old databases were created with 80, the new ones had 90.

My apologies for the noise.
|||Actually, it wasn't necessary to drop these databases - there is a special stored proc to change their compatibility level:

exec sp_dbcmptlevel MyOldDB, 90