March 8, 2008 at 12:53 am
I have successfully added a LinkedServer(MSDASQL) in SqlServerManagementServer.But after that when I run 'SELECT' statement its running for ever without returning anything
SELECT * FROM OPENQUERY(QFODBC_Test, 'SELECT top 10 * FROM CUS')
On MSSMS I see "Executing Query" for ever.
Please help !!!
March 8, 2008 at 8:57 am
The Top 10 is limiting the rows being returned across the pipe, but is still causing the Source server to do a table scan or index scan as you are not doing any restrictions. Also what do you want the top 10 of, you have no order by clause so you are going to usually get the top 10 in order of the clustered index. If you have no clustered index, you will not be guaranteed to get the same 10 without an order by. So I would start by adding a where clause if you can. Secondly, do you need all the columns in the table returned? If not, just return the columns you need.
Have you run the query on the server you are linking to, so you know how long the query should take to run on the server?
One thing I found with linked servers is that many times creating an SP on the linked server that returns the data I need is much faster than directly querying across the link. I would call the SP using the 4 part naming syntax:
linked_server_name.database.schema.sp_name
I have seen a 20 second query reduced to under 1 second doing this as I put all the processing on the linked server and return only the data I need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 8, 2008 at 10:03 am
Thanks for your reply Jack.
I posted 'SELECT TOP 10 ...' as just an example.It is happening for all the queries.
This is how I added new LinkedServer through my MSSMS
EXEC sp_addlinkedserver
@server = 'MY_ODBC_SRV',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = ''ODBC_DSN'
GO
After that if I run anything it takes for ever.Immediately I ran the following SP to test it.But its the same result
EXEC sp_tables_ex @table_server = 'MY_ODBC_SRV'
March 9, 2008 at 11:04 am
If you are linking to another SQL Server why are you using ODBC? You can directly link by name or use SQLOLEDB which both remove the overhead of ODBC. I don't have access to SQL right now, but BOL will explain using either of those options.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2008 at 11:27 am
I agree with Jack. This is not a situation where an odbc driver is optimal. You should use oledb for its native support and performance benifits.
March 9, 2008 at 12:00 pm
The other DB which I am trying to link is not another SQL Server.Its not so popular 'QuickFill' DB.Its driver is not present in the provider list of the LinkedServer.
March 9, 2008 at 12:12 pm
March 10, 2008 at 10:11 am
Do you need to hit the data live? If not, it sounds like you should use SSIS to copy the data to SQL Server on a scheduled basis.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2008 at 6:48 pm
Finally,I could figureout this problem
EXEC sp_addlinkedserver
@server = 'QFODBCSRV',
@srvproduct = '',
@provider = 'MSDASQL.1',
@datasrc = 'QFODBC'
GO
Replaced provider 'MSDASQL' with 'MSDASQL.1'.
I have no logical answer why this worked.Even 'MSDASQL.1' is not part of the providers in SQL Management Studio
March 19, 2008 at 10:05 pm
Now that I see the answer I can remember a time with 2000 that I had a similar problem. I think it was due to a an MDAC update.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply