March 15, 2005 at 4:27 pm
I have the following query;
SELECT *
FROM OPENQUERY(PROGEN, 'SELECT * FROM sysadm.creditors')
This works fine and returns the results.
If I use the four part naming convention like this;
SELECT * FROM Progen.Cromwell.sysadm.creditors
I get an error;
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Progen' does not contain table '"Cromwell"."sysadm"."creditors"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Progen', TableName='"Cromwell"."sysadm"."creditors"'].
What the????????
Can anyone tell me whats going on here?
March 16, 2005 at 2:40 am
Must confess I don't use openquery that much, but from what it looks like, openquery doesn't support four part naming..?
-- snip BOL --
OPENQUERY ( linked_server , 'query' )
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
-- end BOL --
Looks to me the expected format is like the working example you have on top - linked server and the query referencing 'the table' as is...
/Kenneth
March 16, 2005 at 6:47 am
Is PROGEN a sql server?
I get this error when connecting to a third party database using their odbc driver. OPENQUERY works fine but the system will not work with four part naming.
Don't know is helps
Far away is close at hand in the images of elsewhere.
Anon.
March 16, 2005 at 4:10 pm
PROGEN is Gupta SQL Base Server.
If four part naming doesn't work, then how are you suppose to interact between SQL Base & SQL Server? A number of the queries I need to write will require joins from a Database on a SQL Server to a Database on the SQL Base Server!
What would be a solution to this?
March 16, 2005 at 4:41 pm
>>A number of the queries I need to write will require joins from a Database on a SQL Server to a Database on the SQL Base Server!
Cross platform joins are typically slow, and SqlServer ends up pulling all the data across to join to anyway ... so you could 'stage' the Gupta tables into a Sql database using a DTS package, and do all your joins locally between 2 Sql Server DB's.
Depends on your latency requirements, i.e. do you need to join to up to the minute Gupta data, or is it acceptible to refresh the data once a day and join Sql data to Gupta data that may be up to 24 hours stale.
March 16, 2005 at 4:56 pm
The data from Gupta can wait for 24 hours. So, your suggestion for a DTS Package overnight will work well.
I haven't asked the owners yet but I'm pretty sure that they will be happy for 1 day late data as this database is for reporting purposes only. A psuedo Datawarehouse if you like!
However, just in case they tell me that the data has to be real time, what would you suggest for that? I guess replication would have to be the ultimate solution but that would require a third party tool because I won't be able to setup replication from the SQL Server as it won't see Gupta SQL Base Server!
March 16, 2005 at 5:45 pm
if the Gupta sql base table is small, then view in sql server using openquery may help for the real time join.
March 16, 2005 at 6:03 pm
WZ700,
It appears that OPENQUERY deos not support four-part names. Whether it's a view or four-part names or OPENQUERY, I have to join tables from 1 platform to the other which will require four-part names!
In addition, it also appears that GUPTA SQL Base deos not support four-part naming as well.
Back to square 1 with views!
March 16, 2005 at 7:50 pm
create view guptaview as
SELECT *
FROM OPENQUERY(PROGEN, 'SELECT * FROM sysadm.creditors') a
March 16, 2005 at 7:56 pm
March 16, 2005 at 8:05 pm
A normal join from table and view. something like
select a.*
from creditors a
inner join guptaview b on a.credid = b.credid
March 16, 2005 at 8:17 pm
Ok. This could be an option. Thanks for that.
I will have to do some testing as PW has mentioned cross platform queries are typically slow. I'll give both options a try and see what speed differences are.
I get the feeling it would be more optimal to copy the appropriate tables to the SQL Server Database overnight.
March 16, 2005 at 8:21 pm
>>I get the feeling it would be more optimal to copy the appropriate tables to the SQL Server Database overnight.
Definitely compare/benchmark both options, but in our data warehousing environment where we had to integrate Oracle data, trying to use a linked server and cross-database join was several orders of magnitude slower than just sucking all the tables across, re-creating necessary 100% fillfactor indexes on the now static Oracle table copies, and joining on the SQL copies.
March 16, 2005 at 9:17 pm
I have just tested both options. Below are the stats.
Option Duration
View 10 seconds
Copy table to SQL Server 300 milliseconds
In light of all this, I'm amazed at some of the CRAP platforms there are on the market! Before I started this contract, I had never heard of Gupta SQL Base. Let me tell you, that it is a horrible platform in all aspects that a relational Database could ever offer!
The tools they provide....well! what tools? They don't even do the task properley so you end up having to tap into it via Microsoft Access to see the data.
I'm so glad that I have been exposed to this horrible (Gupta SQL Base) platform as It makes me realise that Platforms such as Microsoft SQL Server, Oracle and IBM DB2 have it right. Atleast they have proper tools to do the job like Develop and Design a Database and Administer a Database and the Server. In addition, they also have descent documentation like BOL for SQL Server. Gupta SQL Base is ancient and not worth spitting on.
Can you sense that I hate Gupta SQL Base with a passion?
March 16, 2005 at 10:40 pm
It lands you a job.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply