July 14, 2008 at 1:41 am
I have installed sql2005 at server ‘A’ and server ‘B’. I have enabled all the remote connections and my sqlserver browser is also running. I have installed sp at both the servers.
My linked server name: ; Provider: Microsoft OLE DB provider for SQLSERVER
In the security tab:
Local login:sa
Remoteuser:sa
Remotepassword: (Not be made option has been selected)
The test connection is successful and i am able to locate the tables from the linked server catalog. But if i execute the following query
Select * from [servername].[dbname].[dbo].[tablename]
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider for linked server. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
How to solve the above problem? Need help
July 14, 2008 at 3:37 am
Hi,
I am not sure if we can use sa account for linked server. I've recently configured a linked server by using a SQL account(login). We would have to provide the password for the remote login also. In my case the name of the SQL login were same on both the servers and the passwords were different, hence I have to gice the remote password also. Since I was using the stored procedure execution in linked server context, I had to set the RPC attribute to 1. So try creating a SQL account and connecting using that.
Regards,
Prakash.P
---------------------------------------------------------------------------------
July 14, 2008 at 5:23 am
I also done thru SQL account.But i am not able to locate abt the RPC attribute. how to enable the rpc attribute?
July 14, 2008 at 9:19 am
Hi,
RPC will be there in the settings page (values will be true or false) . But I think this is not going to solve your problem as this is required only if we are running the stored procedure remotely. I have seen that you are using the provider as MS provider for SQL server. In the first page there are two options 'SQL server' and data provider. Did you try selecting the first radio button, i.e., 'SQL server' instead of the data provider and checking the query?
---------------------------------------------------------------------------------
July 14, 2008 at 11:04 pm
Hi,
I tried Sqlserver also but the error is same.
July 15, 2008 at 1:39 am
Hi,
Check what is the schema of the table you are trying to access on the remote server. Lets say if I am using one SQL account and is not a dbo and the table is created in the dbo schema then you can not access it remotely using the SQL account if that SQL account can not access dbo schema. Just try this thing...
Create a table with the same schema as the remote login. Lets say if you are using 'Login' as the SQL account then you will have to create the table in the same schema 'Login' and you should be accessing the table remotely like this...
[ServerName].[database].[Login(this is schema)].[Tablename]
Try this and please let me know.
Regards,
Prakash.P
---------------------------------------------------------------------------------
July 15, 2008 at 3:59 am
Hi,
All my objects are under the dbo 'schema'. I used the following query
select id,name from [linkedservername].[test].[dbo].[sample]
if i execute i am getting the same error.
I found the schema by the following command
sp_tables_ex
July 15, 2008 at 4:22 am
Hi
Since the table is there in the dbo schema, the login with which you are accessing the table remotely (remote login, password) should have access to dbo schema objects. Try adding them in the securables for the login in the database. Simple thing to check otherwise is to create a sample table in the database under the same schema as the login you are using and try accessing it.
Regards,
Prakash
---------------------------------------------------------------------------------
July 15, 2008 at 4:49 am
Hi,
I have tried adding the securables but the result is same error.
I m breaking my heads with this problem for the past 10 days. It is very easy in sql2000 but creating lot of pain in sql2005. I think i m missing out a simple thing in creation of linked server. But i m not able to locate the exact problem.
Is there anyway to contact u thru mail.
Pls..
July 15, 2008 at 5:33 am
Hi,
Nothing has really changed in SQL server 2005 related to the linked server. It must be a straightforward one.
Yeah you can send me a mail to this account prakash.padmanabha@gmail.com
Regards,
Prakash.P
---------------------------------------------------------------------------------
July 15, 2008 at 6:21 am
Hi,
i will clearly explain u the issue
1. I am able to ping both the server
2. I have cretaed a linked server between server A and server B
3. In the security tab i have given (locallogin,remote user,remote password, Not be made option)
4. I am able to test the linked server connection (successfully)
5. I am able to see the catalog i.e tables.
6. If i run a query like
SELECT [a] FROM [servername].[databasename].[dbo].[tablename]
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server . A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
can u able to locate the problem?
July 15, 2008 at 7:27 am
Hi,
What is the edition of the SQL server you are using?
---------------------------------------------------------------------------------
July 15, 2008 at 7:33 am
Hi Prakash,
It's a network issue and i moved the server to same LAN and tried creating it.
It works like charm!:)
Thanks
July 16, 2008 at 12:29 am
You hva to specify the linked server name that you have create on either Server "A" or on Server "B".
SELECT * FROM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply