Sql Linked Server: pointing

Showing posts with label pointing. Show all posts

Monday, March 26, 2012

Linked Server pointing to Access DB

I have added a linked server on my sql 2000 db server, pointing to MS Access 2000 database.

when I fire a select statement :

SELECT * FROM OPENQUERY(NewVerification, 'SELECT id,verifier FROM newverification...accounts')

I get the following error :

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT id,verifier FROM newverification...accounts'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,verifier FROM newverification...accounts'].

I am able to see all the tables thru EM which to means I am successfully able to connect to that DB. But then why is not letting me query that linked server ?

Any help is appreciated.

ThanksHave you tried the query without OPENQUERY? OPENQUERY passes the query to the remote server for processing, which, I believe with Access will not work, as it is not actually a server.

Try:

SELECT id, verifier FROM [newverification]...[accounts]|||I get the following thing :

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].|||Couple of questions...

Is the Access DB local to your SQL server, or on a remote system? I have sometimes had problems accessing remote Access databases, though those were usually on a different subnet, and it was Access preventing the connection because of it's "security" settings.

Is the database password protected?
Have you set up the proper security context?

If it is not protected, right-click on your Linked Server, and select Properties.
On the Security tab, select the last radio button "Be made with the security context:", and enter Admin as the remote login with no password.

Retry the query.

Here's a link for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.aspsql

Linked server performance

SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe

Linked Server on SQL2000 to DB2 - desperate...

Hi All,
I am close before throwing the machine out of the window.
I tried to establish a linked server on our SQL2000 server pointing to a DB.

EXEC sp_addlinkedserver
@.server = 'DB2',
@.srvproduct = 'IBM OLE DB Provider for DB2 Servers',
@.provider = 'IBMDADB2',
@.provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'

EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'

If I query the LS using query analyzer or the Enterprise Manager both start
to hang, never responding again.
To be honest I am quite new to this topic but I read a lot and also tried a
lot ;)

Some articles talk about SNA Servers to be installed first, others about
some client tools.
I am confused by now...

Could anybody please be so kind and tell me how to connect the DB2 as a
linked server?
Is my statement wrong, and if so how should it look like? Why does the
application start to hang? Do I really need to install additional software
to do so?

Our DB2 expert also taked about software necessary to access the DB2
mainframe. Any ideas?

Thanks a lot in advance and belive me, any hints are highly appreciated ;)

Seize the Day

Andy

Andreas Bretl
andreas.bretl@.brainlab.com"Bretl Andy" <andreas.bretl@.brainlab.com> wrote in message
news:2rfnulF19390eU1@.uni-berlin.de...
> Hi All,
> I am close before throwing the machine out of the window.
> I tried to establish a linked server on our SQL2000 server pointing to a
> DB.
> EXEC sp_addlinkedserver
> @.server = 'DB2',
> @.srvproduct = 'IBM OLE DB Provider for DB2 Servers',
> @.provider = 'IBMDADB2',
> @.provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'
> EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'
> If I query the LS using query analyzer or the Enterprise Manager both
> start
> to hang, never responding again.
> To be honest I am quite new to this topic but I read a lot and also tried
> a
> lot ;)
> Some articles talk about SNA Servers to be installed first, others about
> some client tools.
> I am confused by now...
> Could anybody please be so kind and tell me how to connect the DB2 as a
> linked server?
> Is my statement wrong, and if so how should it look like? Why does the
> application start to hang? Do I really need to install additional software
> to do so?
> Our DB2 expert also taked about software necessary to access the DB2
> mainframe. Any ideas?
>
> Thanks a lot in advance and belive me, any hints are highly appreciated ;)
> Seize the Day
> Andy
> Andreas Bretl
> andreas.bretl@.brainlab.com

This explanation might clarify the question of installing SNA/HIS client
components vs the IBM client drivers:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Have you checked that you can successfully connect from the SQL Server
server to DB2 using some kind of DB2 client tool, or even just a script? If
that's OK, then at least you know the provider is installed and working, so
the issue is likely to be the linked server definition.

You might want to check the @.provstr parameter - the syntax is specific for
each OLE DB provider, so you should make sure that it's really a valid
string for the IBM provider. There's a sample here:

http://www.connectionstrings.com

Simon|||First of all, Thanks a lot Simon for the hint,
unfortunately I still can not connect to the db2. But now I have another
errormessage never seen before:
"The provider reported an unexpected catastrophic failure."
This seems to happen during inizialisation??
I used the following registration:
EXEC sp_addlinkedserver
@.server = 'DB2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.catalog = 'JDE_PROD',
@.provider = 'IBMDADB2',
@.provstr='Provider=IBMDADB2;Database=JDE_PROD;HOST NAME=xxxx;PROTOCOL=TCPIP;P
ORT=5000;uid=xxxx;pwd=xxxx;'

If I read the article correctly it should work using the DSN pointing to the
db2 which is installed on the SQL server.
But as mentioned, the error above occures or it seems that the
EnterpriseManager hangs when accessing tabes in the linked-server.

Thanks a lot Simon,

Seize the Day

Andy

Andreas Bretl
andreas.bretl@.brainlab.com|||Bretl,

If you want to use the IBM provider then you need to have IBM's DB2 Connect
product installed on the SQL Server.

If you want to use the MS provider, then you need to have Host Integration
Server 2004 (latest version on SNA Server) installed on the SQL Server as a
"nodeless" install - this is basically a server install without choosing to
install the SNA Server service.

Do you have either of these pre-requisites installed and tested first?

(If you go the MS route it's worth posting future Q's in the Host Integration
Server newsgroup on the MS public servers - you're likely to get better help
there)

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or http://www.ntfaq.com/Articles/Index...epartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||First of all thanks to all who tried heling me with this problem.
Actually it seemes to be a problem with the current fix-pack installation on
the JDE Database-Driver (DB2-ODBC).
Maybe it was damaged or really out of date so it could not communicate with
our SQL Server 2000.
After installing the current fixpack I was able to implement linked-server
over ODBC without problem.

What a hassle when they do not keep their system installation up to date.

Thanks again and have a geat week,

Andy Bretl
andreas.bretl@.brainlab.com

Monday, March 12, 2012

Linked server delegation problem, works for a while after reboots

I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
used the workaround from the article, which seems a little more
straightforward than mine and has the same effect.
Thanks,
Martin
"Terry" wrote:
> Take a look at this
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> It references 2005>2005, and your issue is 2005>2000 but I beleive only
> the error message changed. This after 3+ months working with Microsoft.
> Terry
> Martin M wrote:
> > I have two SQL servers setup with linked servers pointing back and forth
> > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > The second is running SQL Server 2000 SP4. Both are running on Windows
> > Server 2003 Enterprise SP1.
> >
> > Both linked servers are setup to make connections using the login's current
> > security context.
> >
> > Immediately after both servers have been rebooted, both linked servers work
> > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > starts returning the following error message for users connected using
> > Windows authentication:
> >
> > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have yet to see the linked server from SQL 2000 to 2005 fail.
> >
> > I saw two similar issues to this, neither of which has been resolved. Does
> > anybody know if this issue is specific to connections from 2005 to 2000?
> > Would upgrading the second server resolve it or make the issue affect both
> > linked servers?
> >
> > Thanks,
> > Martin
>|||I just discovered that the linked server I created using the example command
from Microsoft's workaround doesn't seem to work for SQL logins; the original
workaround I found, creating the linked server on an alias that uses named
pipes, works for both, so I'll stick with it.
Martin
"Martin M" wrote:
> Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
> used the workaround from the article, which seems a little more
> straightforward than mine and has the same effect.
> Thanks,
> Martin
>
> "Terry" wrote:
> > Take a look at this
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> >
> > It references 2005>2005, and your issue is 2005>2000 but I beleive only
> > the error message changed. This after 3+ months working with Microsoft.
> >
> > Terry
> >
> > Martin M wrote:
> > > I have two SQL servers setup with linked servers pointing back and forth
> > > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > > The second is running SQL Server 2000 SP4. Both are running on Windows
> > > Server 2003 Enterprise SP1.
> > >
> > > Both linked servers are setup to make connections using the login's current
> > > security context.
> > >
> > > Immediately after both servers have been rebooted, both linked servers work
> > > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > > starts returning the following error message for users connected using
> > > Windows authentication:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I have yet to see the linked server from SQL 2000 to 2005 fail.
> > >
> > > I saw two similar issues to this, neither of which has been resolved. Does
> > > anybody know if this issue is specific to connections from 2005 to 2000?
> > > Would upgrading the second server resolve it or make the issue affect both
> > > linked servers?
> > >
> > > Thanks,
> > > Martin
> >
> >

Monday, March 26, 2012

Linked Server pointing to Access DB

Linked server performance

Linked Server on SQL2000 to DB2 - desperate...

Monday, March 12, 2012

Linked server delegation problem, works for a while after reboots

Linked server delegation problem, works for a while after reboots

Linked server delegation problem, works for a while after reboots

Sql Linked Server

Blog Archive

About Me