Getting error while inserting the rows using linkedserver

[hafeez]

Hello All,
I created two database instances in the same MS SQL SERVER 2005 named hafeez and local. I created a linked server from hafeez database to local named HTOL

I created a dummy table in local database named samplocal.

Now i am able to list the records using the linked server, for this i used the following query.

SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL');

Now the problem is, i am not able to insert the rows into the samplocal table using linkedserver. I am using the following query to insert the rows and getting the following errro.

INSERT INTO OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL') VALUES('tertertetttwertw');

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HTOL" supplied inconsistent metadata for a column. The column "nameandval" (compile-time ordinal 1) of object "SELECT nameandval FROM SAMPLOCAL" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Is the above query correct? or shall i miss anything?

Please guide me.

Thanks in Advance
Hafeez Shaik.

[skhanal]

You do not need OPENQUERY

INSERT INTO HTOL.local.dbo.SAMPLOCAL (nameandval) VALUES('tertertetttwertw');

[rmiao]

You didn't specify db name in openquery.

[hafeez]

Hello skhanal,
I tried the following query but still getting the some other error.

INSERT INTO HTOL.local.dbo.SAMPLOCAL(nameandval) VALUES('tertertetttwertw');

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HTOL" 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 "HTOL". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Shall i need to modify any environment variables?
Thanks and Regards,
Hafeez.

[hafeez]

Hello rmiao,
How do i specify db name in openquery? Can you please write a sample query?

Thanks and Regards,
Hafeez Shaik.

[skhanal]

Is Samplocal table in dbo schema or other schema?

[rmiao]

SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM local.schema.SAMPLOCAL');

Replace schema with real schema name. By the way, how did you create linked server? How did you map sql logins on linked server?

[hafeez]

Hello All,
Recently i got the new error msg i.e., 3910 for the above problem.

I searched in the google abt this and i found some terminology like "loopback server" and somebody says that this is a bug in MS SQL SERVER 2005. Is it true?

Can any one plz tell me what is this loopback server and work around for this?


Thanks and Regards,
Hafeez Shaik.

[rmiao]

What's error message? Is msdtc running on both server? Enabled network access in msdtc? How did you create linked server by the way?

[hafeez]

Hello rmiao,
Please note that there is only one server which have two data bases namely hafeez and local

I created a linkedserver from hafeez database to local database and trying to insert the rows into local database.

I started the Distribution Transaction Coordinator service.

The process which i followed to create linked server is:


(i) Open Microsoft SQL Server Management Studio. server login.
(ii) Right click on the Server Objects->Linked Servers and select the New Linked Server…option.

(iii)I gave the provider as Microsoft OLE DB provider for SQL Server.
(iv) In security dialogue box i selected "Be made using the login's correct security context" radio button.
(v) In server options dialouge box I made Rpc Out and Use Remote Collation as True.

Please correct me if u need further info.

Thanks and Regards,
Hafeez.

[rmiao]

Why use linked server in this case then?

[hafeez]

Hello rmiao,
Sorry for the late reply,

In my case the 2nd database is not always resided in the same server, some times the database resided in another server so i am using linked server.

I wrote the code accordingly. I am checking for second database, if it is available i am using linked server whether it is residing in the same server or not.

But now i am getting the following mentioned error.

Can you please tell/confirm whether linked server is not working if the second database is residing in the same server, so that i will change the logic in my code?

Thanks and Regards,
Hafeez.

[rmiao]

You have to create linked server first.

[hafeez]

Hello rmiao,

I created the linkedserver but failed to insert. Please see the my first posted problem.

If the databases are resided on different servers then linkedserver concept is working properly. But when the databases are resided in the same server linkedserver is not working.

Hope u got my problem

Thanks,
Hafeez.

[rmiao]

Tried with four part name instead of openquery?