I installed the SAS ODBC-driver 9.1 and can choose the SAS tables (datasets)
but then I get this message:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server."
Linked server?
What do I do? Is it possible to link SAS datasets to an Access Project or to
SQL Server 2000?
By definition, Access Projects are a presentation layer that store all
their data in a SQL Server backend. You should simply point SAS to the
real SQL backend database
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
Showing posts with label ole. Show all posts
Showing posts with label ole. Show all posts
Friday, March 30, 2012
Linking SAS-datasets to an Access Project 2003
I installed the SAS ODBC-driver 9.1 and can choose the SAS tables (datasets)
but then I get this message:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server."
Linked server?
What do I do? Is it possible to link SAS datasets to an Access Project or to
SQL Server 2000?By definition, Access Projects are a presentation layer that store all
their data in a SQL Server backend. You should simply point SAS to the
real SQL backend database
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql
but then I get this message:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server."
Linked server?
What do I do? Is it possible to link SAS datasets to an Access Project or to
SQL Server 2000?By definition, Access Projects are a presentation layer that store all
their data in a SQL Server backend. You should simply point SAS to the
real SQL backend database
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.droptable.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via droptable.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.droptable.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.droptable.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via droptable.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.droptable.com
>
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.sqlmonster.com
Look at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.sqlmonster.com
Look at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
--
Message posted via http://www.sqlmonster.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
--
Message posted via http://www.sqlmonster.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
Wednesday, March 28, 2012
Linking Access(urgent)
Dear all
I've a problem when I make a command button in Access's Form which open form or report the message that "error encountered with OLE ActiveX?"
Can any body help me to fix this ?
PLZ Help me urgentCould you give the exact error message, # etc. That would help.
I've a problem when I make a command button in Access's Form which open form or report the message that "error encountered with OLE ActiveX?"
Can any body help me to fix this ?
PLZ Help me urgentCould you give the exact error message, # etc. That would help.
LinkeServer Problems...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.That's an initialization failure...a pretty generic OLEDB error. Sounds lik
e you have yet to configure the Security mappings correctly. If you are att
empting to use Windows Authentication Delegation, you have a LOT of work ahe
ad of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make
sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
--
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0
EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initial
ize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initializ
e
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.That's an initialization failure...a pretty generic OLEDB error. Sounds lik
e you have yet to configure the Security mappings correctly. If you are att
empting to use Windows Authentication Delegation, you have a LOT of work ahe
ad of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make
sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
--
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0
EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initial
ize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initializ
e
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>
LinkeServer Problems...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
That's an initialization failure...a pretty generic OLEDB error. Sounds like you have yet to configure the Security mappings correctly. If you are attempting to use Windows Authentication Delegation, you have a LOT of work ahead of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>
|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>
|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
That's an initialization failure...a pretty generic OLEDB error. Sounds like you have yet to configure the Security mappings correctly. If you are attempting to use Windows Authentication Delegation, you have a LOT of work ahead of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>
|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>
|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
Wednesday, March 21, 2012
servers in SQL 2005.
The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/Brian
May check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, this
> dont work either)
> /Brian
>
|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/Brian
May check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, this
> dont work either)
> /Brian
>
|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
servers in SQL 2005.
The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
Monday, March 19, 2012
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers 2000/2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
The error message is :
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.
Thank you.
If not, in order for Distributed queries in SQL Server 2005 to work against SQL Server 2000, you need to run the instcat.sql script that is supplied as part of SP4 on your SQL Server 2000 instance.
Thanks,
- Balaji|||I'll try to apply SP4
Thank you|||I'll mark Balaji's answer as the correct one. If SP4 doesn't fix the issue, let us know. You or I can unmark the message at that time.
Thanks
Laurentiu|||Emil,
I am also getting the following error when i try to create a linked server from a 64bit SQL 2005 to a SQL Server 200o (SP4) instance. Did you find a solution to your problem?
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "eppinf001" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "eppinf001". The provider supports the interface, but returns a failure code when it is used.
Cheers,
Priyanga
|||I came across this KB article which explains the issue.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Cheers,
Priyanga|||No. We didn't find a solution.
Using the x64 version of SQL Server was not a requirement so we used x86 version instead.|||
Hi,
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@.table_name sysname,
@.table_schema sysname = null,
@.table_type nvarchar(255) = null
as
declare @.Result int set @.Result = 0
exec @.Result = sp_tables_info_rowset @.table_name, @.table_schema, @.table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk
|||I am having the same issue but we ARE already running sp4. Can I assume that instcat.sql was run? How do I tell?|||I had the same problem, and found a workaround.
You'll probably find that you are able to create a linked server to the 2000 database. This can be referenced though an OPENQUERY statement:
CREATE view [dbo].[vw1_Sql_L_ElogiaSFProd_OrderEntry_AppletAttribute] as
Select * From OPENQuery(ELOGIA_IPG_SFOPROD, 'Select * from IPG_SFOPROD.dbo.applet_attributes')
where:
ELOGIA_IPG_SFOPROD is the name of the linked server, with its "Catalogue" pointed to the 2000 database name.
dbo is the object owner
applet_attributes is the table name
Cheers,
Mark
|||Marek,
Thanks to your useful wrapper SP. This worked for me since it would be whole lot more pain to modify all selects to openquery methods. Instead this wrapper worked perfect.
servers
Hi,
I'm trying to create a linked server of DB2. I've tried using the providers
"IBM OLE DB Provider for DB2" and "Microsoft OLE DB Provider for ODBC
Drivers" but I just don't know what to put in the fileds to create a valid
connection. Can someone give me an example that would work for me.
SQL Server and DB2 are installed on the same server.
TIA,
EricLook up sp_addlinkedserver in Books Online. There's an example there on how
to add a linked server to DB2.
ML
http://milambda.blogspot.com/
I'm trying to create a linked server of DB2. I've tried using the providers
"IBM OLE DB Provider for DB2" and "Microsoft OLE DB Provider for ODBC
Drivers" but I just don't know what to put in the fileds to create a valid
connection. Can someone give me an example that would work for me.
SQL Server and DB2 are installed on the same server.
TIA,
EricLook up sp_addlinkedserver in Books Online. There's an example there on how
to add a linked server to DB2.
ML
http://milambda.blogspot.com/
servers
I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.
Anyone have any ideas wht's up?Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?
-PatP|||When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:
select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||What happens in Query Analyzer?
I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.
-PatP|||The SQL and error message posted are from Query Analyzer.
I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.
Any other ideas? I appreciate the help.|||I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.
Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?
-PatP|||Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.|||Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.
-PatP|||Originally posted by peterlemonjello
Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.
What's the linked server login?
Did you grant right to it on the new tables?|||Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.|||I'm stuck...did you stop and restart EM?
Can you query them in QA?
Hey Pat, it's past 3:30...EST
Is there a way to see the linked server catalogs?|||Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.|||OK, let's get stupid (since I'm already there)
Can you create a new linked server with the same code?
Did you do it with code or through EM?|||I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.|||Good Luck...
Time for a 'rita...
Later...|||Originally posted by Brett Kaiser
Hey Pat, it's past 3:30...EST
Is there a way to see the linked server catalogs? Not hardly, it was just past 15:30 EST when you posted! I really meant 03:30!
-PatP|||all righty then...
Hey Peter, did bouncing the box help?|||OK, I found the problem. There's a bug in Sybase's OLEDB provider. Luckily there's a patch out for the bug.
If you've setup more than one OLEDB profile in the Configuration Manager the first profile that's setup will apply to all additional profiles. No matter what properties are specified in the additional profiles settings.
For example create Profile1 that connects to 10.5.1.4 port 7682.
Cretae Profile2 and specify server 10.5.1.5 port 7680. Eventhough the properties are set correctly on Profile2 it will always connect to 10.5.1.4 port 7682. Also, any addiotional profiles will connect the profile that was created first in the Configuration Manager.
I couldn't see the new tables (in my test db) because my OLEDB connection was logging into a different server (production). Which happen to have the same schema except for the new tables.|||Wow! That's a pretty good one.
-PatP
Anyone have any ideas wht's up?Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?
-PatP|||When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:
select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||What happens in Query Analyzer?
I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.
-PatP|||The SQL and error message posted are from Query Analyzer.
I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.
Any other ideas? I appreciate the help.|||I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.
Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?
-PatP|||Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.|||Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.
-PatP|||Originally posted by peterlemonjello
Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.
What's the linked server login?
Did you grant right to it on the new tables?|||Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.|||I'm stuck...did you stop and restart EM?
Can you query them in QA?
Hey Pat, it's past 3:30...EST
Is there a way to see the linked server catalogs?|||Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.|||OK, let's get stupid (since I'm already there)
Can you create a new linked server with the same code?
Did you do it with code or through EM?|||I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.|||Good Luck...
Time for a 'rita...
Later...|||Originally posted by Brett Kaiser
Hey Pat, it's past 3:30...EST
Is there a way to see the linked server catalogs? Not hardly, it was just past 15:30 EST when you posted! I really meant 03:30!
-PatP|||all righty then...
Hey Peter, did bouncing the box help?|||OK, I found the problem. There's a bug in Sybase's OLEDB provider. Luckily there's a patch out for the bug.
If you've setup more than one OLEDB profile in the Configuration Manager the first profile that's setup will apply to all additional profiles. No matter what properties are specified in the additional profiles settings.
For example create Profile1 that connects to 10.5.1.4 port 7682.
Cretae Profile2 and specify server 10.5.1.5 port 7680. Eventhough the properties are set correctly on Profile2 it will always connect to 10.5.1.4 port 7682. Also, any addiotional profiles will connect the profile that was created first in the Configuration Manager.
I couldn't see the new tables (in my test db) because my OLEDB connection was logging into a different server (production). Which happen to have the same schema except for the new tables.|||Wow! That's a pretty good one.
-PatP
Friday, March 9, 2012
server/SQL Server OLE DB performance problem with parameters?
Hi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.
I have set the dynamic paramters option on, and use collation compatible.Linked servers in general are a performance nightmare.
Get me that bucket of data over there...ALL of it.
Careful...carry it over here now. Be careful. Oh crap...I dropped some. You need to start over.
Great...it's finally here....ALL OF IT. Can you throw away everything that doesn't match this one value?
That's great. Thanks.|||Replace a straight DML/SELECT with a call to a stored procedure on the remote side and you'll be home free!|||SQL Attempts to retrieve statistics information from remote tables to determine which server should be the driving server - or where to filter rows first before joins. It runs some system stored procedures (I forget the names at the moment) If the account connecting to the remote server does not have permisisons on the procedures SQL will assume the worset and generate a query plan with the statistics available on the local server. I'm not recommending you grant the remote user dbo rights (not that I would do that ...) but if you did do it temporarily and saw a performance increase you could research exactly which permissions were really needed. Actually you can run profiler on the remote server and you will see the statistics gathering queries and identify the objects involved.|||In testing I found that the query performed faster with the parameters declared 'inline'. Does this make sense? Or am I suffering from inconsistent testing conditions? Our production databases are on the same servers as our test dbs.
The distributed queries are in stored procedures. And they (usually) don't join to the local server. However, the stored procs need to query the local db and store results there. It would be possible to put stored procs on the remote db. But we have already taken the functions into production so code changes are undesired.|||Try using the openquery() method. When this method is used the calling SQL server instance does not attempt to retrieve statistics info and the linked server processes the SQL and simply returns the results.
This especially makes a huge difference when the linked server is Sybase ASE.
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.
I have set the dynamic paramters option on, and use collation compatible.Linked servers in general are a performance nightmare.
Get me that bucket of data over there...ALL of it.
Careful...carry it over here now. Be careful. Oh crap...I dropped some. You need to start over.
Great...it's finally here....ALL OF IT. Can you throw away everything that doesn't match this one value?
That's great. Thanks.|||Replace a straight DML/SELECT with a call to a stored procedure on the remote side and you'll be home free!|||SQL Attempts to retrieve statistics information from remote tables to determine which server should be the driving server - or where to filter rows first before joins. It runs some system stored procedures (I forget the names at the moment) If the account connecting to the remote server does not have permisisons on the procedures SQL will assume the worset and generate a query plan with the statistics available on the local server. I'm not recommending you grant the remote user dbo rights (not that I would do that ...) but if you did do it temporarily and saw a performance increase you could research exactly which permissions were really needed. Actually you can run profiler on the remote server and you will see the statistics gathering queries and identify the objects involved.|||In testing I found that the query performed faster with the parameters declared 'inline'. Does this make sense? Or am I suffering from inconsistent testing conditions? Our production databases are on the same servers as our test dbs.
The distributed queries are in stored procedures. And they (usually) don't join to the local server. However, the stored procs need to query the local db and store results there. It would be possible to put stored procs on the remote db. But we have already taken the functions into production so code changes are undesired.|||Try using the openquery() method. When this method is used the calling SQL server instance does not attempt to retrieve statistics info and the linked server processes the SQL and simply returns the results.
This especially makes a huge difference when the linked server is Sybase ASE.
server/OLE DB Performance problem with parameters?
Hey,
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:
> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:
> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
Subscribe to:
Posts (Atom)