OK, little dissection of the code here:

select * from OPENROWSET('SQLOLEDB','SERVER=XX;UID=yy;PWD=pp;Database=OtherDatabase','SET FMTONLY OFF; If object_id(''MYVIEW'') IS NULL begin exec(N'' CREATE VIEW dbo.MYVIEW as select * from Employees'') end; SELECT NULL') qry

'SQLOLEDB','SERVER=XX;UID=yy;PWD=pp;Database=OtherDatabase'

Probably everyone...

hey Phil,

 I just posted "another" way, never though of it as "ganging on you"

Jamie,

Believe me, The OPENROWSET that I posted is very tricky, not only you are forced to use exec but all the extra stuff that is in there is necessary

Yes, it is nice to be back but probably will have to take a second break on my next two weeks in london (Job stuff!)

>>Wouldn't it be faster to just do a full outer join where either id is null??  <<

YES:

select (case when t2.id is...

I would second the idea of using sp_OA* for this activity as the method of choise. But for the fun of it I can tell you that you can use...

there are several ways to go about this.

If you don't want to hardcode 13 statements with union all and selects (which is not a big deal) then your best bet...

"BTW, for the ultimate in Microsoft apologia, see my blog post at http://therosenblog.blogspot.com/2004/10/happy-birthday-billg.html 

Best regards,

SteveR "

You do have many reasons to "praise" M$ but...

If the linked server is temporary then you are using the wrong tool for the job. You need to use either OPENROWSET or OPENDATABASE instead!

If you still get long execution time apparently there is something not very efficiently done (M$ fault?). This is arguably one of the most "gray" areas in which you would...

and if the Tables were to be different you can add a "helper" column

like:

Select Col1, Col2, 1 as helper from Table1 where xyz...

union all

Select Col1, Col2, 2  from Table2 where xyz...

union...

I think that you have syntax problems in your query (nothing after the "ON" clause)

To simplify a little you can create a view with the linked server qualification encapsulated in...

Well, you tell me if I am blind but:

Your query record source:

from

   turnover   full outer join    costs

on   turnover.client = costs.client

and  turnover.year   = costs.year

and  turnover.month  = costs.month

and David's record...

Jamie,

This limit is fairly common on large crosstabs there are some tricks to be used in the process in case the grow very big:

1. Several @str variables (estimating limits of...

If that (David's) query worked yours returned all the records!! Contrary to what you said!

The only difference was in the column population not...