Post reply

Can i perform Data manipulation operation on Linked Server

  • December 24, 2008 at 12:37 am

    #128767

    Hello all

    I register linked server and i can perform select query using this query

    SELECT * FROM OPENQUERY(webdb, 'SELECT * FROM tablename')

    will any one please tell me how can i perform DMl operation on Linked Server

    like update table and delete table

    Thanks

    mahendra singh

  • December 24, 2008 at 1:41 am

    #917264

    hi

    Insert into webdb.database_name.dbo.table_name

    seelct * from table

    this will work

    bhuvnesh

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 24, 2008 at 2:20 am

    #917273

    i'm trying to do an insert statement aswell. its going to a mysql linkserver database. the linked server is on an sql server.

    the link server is using the mySQL ODBC 5.1 driver and the provider name is the Microsoft OLE DB Proivder for ODBC.

    now i can do a select statement easily:

    SELECT *

    FROM OPENQUERY([SERVERNAME], 'SELECT * from TABLENAME')

    And

    select * from [SERVERNAME]...TABLENAME

    but when it comes to inserts i get the following error:

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: The provider did not give any information about the error.].

    i've tried the following two insert statements and both give the same error:

    insert into [servername]...tablename(col1,col2)

    values(1,2)

    insert into openquery ([servername], 'Select col1,col2 from tablename where 1 = 0')

    values(1,2)

    does anyone know why?

    thanks

  • December 24, 2008 at 2:40 am

    #917278

    Thanks It's working and it's solve my problem

  • December 24, 2008 at 3:25 am

    #917289

    hi

    i m not sure about the error you got ...but seeing your script ..i found that

    u r not using full quoted identifier query

    like

    insert into [linked_server_name].[database_name].[dbo].[table_name]

    select * from table

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 24, 2008 at 3:53 am

    #917293

    i used the "..." because mysql tables dont seem to have an 'owner'. e.g: dbo.

    so since we've only got the one database on that server then "..." seems to work nicely in the select query.

    thanks for the reply though.:)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply