Hi Marc
I have the same error and what answer I got from Mircosoft
**********************
It turns out that something called a SPN (ServicePrincipalName) must be established for the SQL Server and the account under which the SQL Server service runs. This happens automatically when SQL Server is run under the Local System account, but must be done manually when running SQL Server under another domain account.
There is a tool called SetSPN which allows viewing or setting SPN settings. It is available at
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.aspHere is what is required to make the SPN work correctly:
1. Set SQL Server to use TCP/IP. Network must be Windows 2000 or better and use ActiveDirectory
2. Set the SPN to the domain account the SQL server account runs under, like this:
setspn -A "SQL Server Service Name"/"fully qualified domain name for SQL Server machine":"tcp port number" "unqualified domain account name service runs under"
**********************
In my case it does not work because we don' have ActiveDirectory service,
but you may try.
Mariam
>Hi mariam
>
>All is already checked in the provider options (except the last one)
>
>
>>Hi Marc,
>>
>>Try to check AllowInProcess Option
>>
>>1. Start Enterprise Manager
>>2. Drill down into server.
>>3. Drill down into Security/Linked Servers.
>>4. Rightclick on Linked Servers and select New linked server
>>5. For Provider name, select MS OLE DB Provider for SQL SERVER.
>>6. Click Provider Options
>>7. Check Allow InProcess checkbox. This will apply to any SQL linked
>>servers or distributed queries.
>>8. Click OK
>>9. Click Cancel, since you don't need to create a new linked server.
>>
>>Mariam
>>
>>>Sergey,
>>>
>>>The two SQL server are version 2000 SP3a, one is runing on Win2k3 build 3790 and the other (linked server) is runing on Win2k build 2195 SP4
>>>
>>>The main SQL Server is named OBELIX2004 the distant one is named PANORAMIX2004...
>>>
>>>The linked server has been created on OBELIX2004, in the Server Enterprise Manager, using Microsoft OLE DB Provider for SQL Server, the Datasource is the second SQL Server (PANORAMIX2004) and the catalog is G2MIR (the database on PANORAMIX2004.
>>>
>>>In the security tab, we chose the las radiobutton, with a remote connection (user and password) defined for the G2MIR database on PANORAMIX2004.
>>>
>>>In the server option tab, all is checked
>>>
>>>The security on PANORAMIX2004 SQL Server is defined as SQL Server and Windows authentication
>>>
>>>>Marc,
>>>>
>>>>What are the versions/Service packs of the local and remote SQL Servers? How did you create linked server and what options did you specify? What kind of security did you set for linked server?
>>>>
>>>>>
>>>>>I should give you the error returned by >>
select * from echange.g2mir.dbo.test
>>>>>
>>>>>The result message is
>>>>>Serveur : Msg 7399, Niveau 16, État 1, Ligne 1
>>>>>OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
>>>>>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].
>>>>
>>>>>
>>>>>I hope it will help you
>>>>>
>>>>>
