Results 1 to 15 of 15

Thread: Getting error while inserting the rows using linkedserver

  1. #1
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You do not need OPENQUERY

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

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    You didn't specify db name in openquery.

  4. #4
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  5. #5
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

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

    Thanks and Regards,
    Hafeez Shaik.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Is Samplocal table in dbo schema or other schema?

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    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?

  8. #8
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    What's error message? Is msdtc running on both server? Enabled network access in msdtc? How did you create linked server by the way?

  10. #10
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    Why use linked server in this case then?

  12. #12
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  13. #13
    Join Date
    Sep 2002
    Posts
    5,938
    You have to create linked server first.

  14. #14
    Join Date
    Sep 2006
    Posts
    21

    Getting error while inserting the rows using linkedserver

    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.

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with four part name instead of openquery?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •