help with a query

Hello..I need help
Surporse I have a table TBRelation with 4 columns A1,B1,A2,B2 all are int.
this table has two relation with a table called TB with 2 colums A, B they
are composite primary key.
How can I to create a query that insert in tbRelation with the result of 2
queries?
wrong query today:...
insert into tbrelation
A1, B1, A2, B2)
select a,b from tb where culture = 'es-au'
select a,b from tb where culture = 'pt-br'
Result that I should want:
A1 - B1 - A2 - B2
1 1 2 2
someone can help me?Try,
insert into tbrelation (A1, B1, A2, B2)
select a.a, a.b, b.a, b.b
from
(
select top 1 a, b from tb where culture = 'es-au'
) as a
cross join
(
select top 1 a, b from tb where culture = 'pt-br'
) as b
go
AMB
"EdwinSlyfingster" wrote:

> Hello..I need help
> Surporse I have a table TBRelation with 4 columns A1,B1,A2,B2 all are int.
> this table has two relation with a table called TB with 2 colums A, B they
> are composite primary key.
> How can I to create a query that insert in tbRelation with the result of 2
> queries?
> wrong query today:...
> insert into tbrelation
> A1, B1, A2, B2)
> select a,b from tb where culture = 'es-au'
> select a,b from tb where culture = 'pt-br'
>
> Result that I should want:
> A1 - B1 - A2 - B2
> 1 1 2 2
> someone can help me?
>|||On Wed, 16 Nov 2005 04:30:02 -0800, EdwinSlyfingster wrote:

>Hello..I need help
>Surporse I have a table TBRelation with 4 columns A1,B1,A2,B2 all are int.
>this table has two relation with a table called TB with 2 colums A, B they
>are composite primary key.
>How can I to create a query that insert in tbRelation with the result of 2
>queries?
>wrong query today:...
>insert into tbrelation
>A1, B1, A2, B2)
>select a,b from tb where culture = 'es-au'
>select a,b from tb where culture = 'pt-br'
>
>Result that I should want:
>A1 - B1 - A2 - B2
>1 1 2 2
>someone can help me?
Hi EdwinSlyfingster,
INSERT INTO Relation (A1, B1, A2, B2)
SELECT t1.A, t1.B, t2.A, t2.B
FROM tb AS t1
CROSS JOIN tb AS t2
WHERE t1.culture = 'es-au'
AND t2.culture = 'pt-br'
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help says ODBC is Available. Where?

In BOL it clearly says that you can "use the Microsoft OLE DB Provider for ODBC Drivers provided." in relation to Analysis Services.

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/uas9/html/9fab8298-10dc-45a9-9a91-0c8e6d947468.htm

Unfortunately, this provider is not in my list. Here is the full text section from help.

"Other relational systems support the older Open Database Connectivity (ODBC) specification but do not yet support the OLE DB specification. For these systems, use the Microsoft OLE DB Provider for ODBC Drivers provider. Although ODBC providers can be used by referencing them with the Microsoft OLE DB Provider for ODBC Drivers provider, you should use a native OLE DB provider whenever possible for performance reasons."

HELP PLEASE!

Hi Hugh

From what I've seen there is no ODBC functionality in SQL 2005 analysis services. There is in the integration services not that this helps much as you have to dump all of the data from your source database into SQL. I have successfully created a linked server and then used native SQL source to attach to the data. I have run into problems with data limits and the system running out of memory and there is very little documentation, so am stil playing around with this. It seems that Microsoft has missed the mark here as SQL 2000 does allow this functionality so strange to take away functionality with new release.

|||

Thanks for the input kiwi. I have also used the .Net ODBC driver in Integration Services, but find it oddly missing from Analysis Services. Even stranger is that the documentation says it has an OLE ODBC driver which I can't find anywhere in any part of the Business Intelligence tools. I'll try using the linked server route to see if that works for me. Maybe they will resove this in a future patch.

Thanks again.

|||Kiwi, how did you connect to your linked server through the native SQL source? I can't figure out how to do that in the Data Source setup.|||

Never mind. I found the other post that talks about it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78023&SiteId=1

I knew I could use saved views pointing to a linked server, but I was hoping to be able to directly address the linked server in the data source. If they really aren't planning to support ODBC out of the box in Analysis Services, that makes it a severly limited product. For all of us who have legacy systems that are going to be around for many years to come, we're stuck doing five times the work.

(I'm done whining now :). It's still a great product.)

|||

Hey Hugh

I didn't do all of that I simply used the managment studio to create the linked server and then in Business Intelligence development I used Named queries instead of tables and used Openquery. Worked fine apart from I ran into memory issues with returns over 100k records.

Kiwi

|||Thanks Kiwi. Don't know why I didn't think of that. Definitely much easier that way.

Help relation Between Xml and SQL2000

HI,
I have a xml file and would like to Make Relation Between
it and table Or View in SQl2000
can some one say how?
Thanks
You should look into using either OpenXML or the annotated schema. Check out
the SQL Server Books Online section on XML or
http://msdn.microsoft.com/sqlxml
Best regards
Michael
"Abdallah Salah" <a_elsabbahi2000@.yahoo.com> wrote in message
news:a8cb01c487d8$c1801960$a401280a@.phx.gbl...
> HI,
> I have a xml file and would like to Make Relation Between
> it and table Or View in SQl2000
> can some one say how?
>
> Thanks
>