Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PROBLEMS WHEN UPDATING LINKED SERVER WITH MSSQL DATABASE Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:36 AM
Points: 8, Visits: 68
I have a linked server (mysql) and I want to update some colums with the information on MSSQL information

now I get the following error

OLE DB provider "MSDASQL" for linked server "mysqlapp" returned message "Data provider or other service returned an E_FAIL status.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "mysqlapp".

where mysqlapp is the name of my linked server



update tabel3

set tabel3.price = dt.ac_prixVen
from openquery (mysqlapp, 'select * from product_special') as tabel3
inner join (select Ar_ref, Ac_prixVen, AC_categorie,product_id, price
from openquery (mysqlapp, 'select * from product') as tabel2 inner join
tractov15.dbo.f_artclient as tabel1
on tabel2.model = tabel1.ar_ref where tabel1.ac_categorie = '1'
) as dt on
tabel3.product_id = dt.product_id
where tabel3.customer_group_id = '1'
and tabel3.price<> dt.ac_prixVen
Post #1452944
Posted Wednesday, May 15, 2013 6:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 10,910, Visits: 12,553
Can you successfully run a SELECT query against the linked server?

What does sp_testlinkedserver do?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1453060
Posted Wednesday, May 15, 2013 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:36 AM
Points: 8, Visits: 68
Yes I can
And when I run the below query this works also,

update dt
set dt.quantity =a.as_qtesto-a.as_qteres
from openquery(mysqlapp,'select * from product')as dt
inner join TRACTOV15.dbo.f_artstock as a on
dt.model = a.ar_ref collate database_default
where a.de_no ='15'
and dt.quantity<>a.as_qtesto-a.as_qteres

the only difference between the 2 queries is

- first on is only between 2 tables (one in mssql and the second in mysql)
- second one is with 3 tables (one in mssql and 2 tables in mysql)

and the second difference is

- first query is an update of the stock so no digits behind the dot example : 5
- second query is an update of the price example: 15.25

the only thing that i didn't test at this moment is the mssql database works like 15,25 and i think the mysql database works with 15.25
I was still looking how to convert a 15,25 price to a 15.25 price
but I am not sure if this gives the problem
Post #1453064
Posted Wednesday, May 15, 2013 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:36 AM
Points: 8, Visits: 68
I downloaded the database of mysql from the another server and then I made a linked server off,
what I see now is that it works so my problem is that I get the error when connecting a linked server to another server with mysql

has anybody some suggestions how to solve this issue

I need this to work, it is not an option to download each time the mysql database adjust it and place it back to the other server
Post #1453244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse