Linked Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server

Author

Topic

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 02:52:47

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 !!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 11:48:12

Try it without the * in the Openquery Select...maybe just 1 column as a test..

Can you get


Select Top 10 *
FROM [LinkedServerName].[Database].dbo.[CUS]

I am not versed in Openquery...so I am not sure. I have read that parameters with Openquery can sometimes be an issue, not sure if the select * inside the openquery command does or not.

Poor planning on your part does not constitute an emergency on my part.

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 13:45:51

Thanks for your reply.Without start * its not going to work.

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'

It could be a hiccup with the Driver.I am not sure.Atleast it should have given me some error Instead of running forever

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 14:01:25

The linked server is an ODBC_DSN....

Is it another actual server or just an ODBC Connection to a file of some kind?

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-08 : 14:57:47

Did you set permission on linked server?

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 18:49:27

ODBC_DSN is the SystemDSN name for a Database called 'QuickFill'.

rmiao,
I tried all the options in the security.
Be Made without using a security Context
Be Made with using the login's current security Context
Be Made using this security Context.
But the same result.

Irony is that I could successfully use SystemDSN and get into the Database using either CrystalReports or Microsoft Visual studio 2005.

But my requirement is to build some SQL queries from SQL Server Management Studio.

Thanks all for your replies so far.Would appreciate if somebody can look into this

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 19:36:52

Is it a SQL server? or what kind of database is it? if it is an access db for example you wuold use Microsoft.Jet.OLEDB.4.0
instead of the MSDASQL

just thinking you may have the wrong provider?

Poor planning on your part does not constitute an emergency on my part.

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 20:29:06

It is not a SQL Server.Its a QUICKFill(Not so popular) Database.I am using a ODBC connection for it as there is no provider for it in LinkedServer.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 20:48:15

Hmmmm..that may be part of the problem?

Poor planning on your part does not constitute an emergency on my part.

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 21:30:29

No.Ideally that should'nt be.I could successfully access that DB from VisualStudio and CrystalReports using the same DSN.If at all there is a problem ManagementStudio should straight away throw an error.I dont understand why its executing forever.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 22:17:04

Okay...the next question is, why do these queries have to be written in SQL SMSS?

why the requirement to build the queries in the management studio?

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-08 : 22:20:28

Tried trace it in profiler?

durgapras
Starting Member

15 Posts

Posted - 2008-03-08 : 22:40:00

Because,QuickFill doesnt support scheduling jobs.I would first develop some queries to pull the data from Quickfill and later schedule it in MS SMS.

I never tried profiler.Is it part of MS SMS?Excuse me for my Ignorance.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-09 : 00:01:39

You can start it in ssms from tools.

durgapras
Starting Member

15 Posts

Posted - 2008-03-09 : 01:32:23

I started SQL Profiler.Its showing as,

SQL:BacthStarting 'EXEC sp_tables_ex @table_server = 'ODBC_SRV'

Its going on from two hours without anything happening.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 14:12:25

http://www.sqlservercentral.com/Forums/Topic466253-146-1.aspx?Update=1

cross post.

Please post links to the other posts when asking for help from multiple forums.

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-09 : 18:10:30

Looks like sql can't reach target data source.

durgapras
Starting Member

15 Posts

Posted - 2008-03-09 : 19:56:21

rmiao,
Is there anyway we can debug that?

Dataguru,
I was checking my luck in multiple sites.I feel posting the same topic in multiple sites is better than just providing the crosslinks to other site.That way I feel it makes things easier for experts like you.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 20:07:39

quote:
I feel posting the same topic in multiple sites is better than just providing the crosslinks to other site.That way I feel it makes things easier for experts like you.

Just the opposite. You have a couple people engaged here, and we don't know you may have a few people engaged over there and elsewhere.

Also, suggestions offered in one, may be different than another..and if the cross link is posted, those replying here can check if their thoughts were already addressed at the other site and vice versa.

In general,cross-posting, and not providing the other links can be seen bad forum etiquette with the help forums. At other sites I moderate and/or participate, once undisclosed cross-posts are found..it usually ends the help offered as well. The thinking is, "why am I wasting my time, when those other people can answer this one" followed by "I am going to the next unanswered topic".

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-09 : 21:04:07

>> Is there anyway we can debug?

Try check netrwork traffic with network monitor.

durgapras
Starting Member

15 Posts

Posted - 2008-03-09 : 21:42:34

quote:
Just the opposite. You have a couple people engaged here, and we don't know you may have a few people engaged over there and elsewhere.

Also, suggestions offered in one, may be different than another..and if the cross link is posted, those replying here can check if their thoughts were already addressed at the other site and vice versa.

In general,cross-posting, and not providing the other links can be seen bad forum etiquette with the help forums. At other sites I moderate and/or participate, once undisclosed cross-posts are found..it usually ends the help offered as well. The thinking is, "why am I wasting my time, when those other people can answer this one" followed by "I am going to the next unanswered topic".

That makes sense

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources