Openquery QUESTION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Openquery QUESTION

Author

Topic

ytsun
Starting Member

9 Posts

Posted - 2007-11-14 : 00:57:55

I executed OPENQUERY method in SQLSERVER2005 to change isolation level of mysql database.

SELECT * FROM OPENQUERY(YOURDBLINK,
'SET Transaction Isolation Level READ UNCOMMITTED')

promption:
Msg 7357, Level 16, State 2, Line 2
Cannot process the object "SET Transaction Isolation Level READ UNCOMMITTED". The OLE DB provider "MSDASQL" for linked server "YOURDBLINK" indicates that either the object has no columns or the current user does not have permissions on that object.

And executed
SELECT * FROM OPENQUERY(YOURDBLINK,
'SELECT @@tx_isolation;')

result:
@@tx_isolation
----------------
REPEATABLE-READ

As far as permissions were concerned,I logined MySql with the same username/passwod
and excuted 'SET Transaction Isolation Level READ UNCOMMITTED' directly,it worked.

BTW, we don't wanna use SSIS.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 17:16:24

SELECT * FROM OPENQUERY(YOURDBLINK, 'SELECT @@tx_isolation AS f;')

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 17:18:44

This SQL does not have any columns:
'SET Transaction Isolation Level READ UNCOMMITTED'

CODO ERGO SUM

ytsun
Starting Member

9 Posts

Posted - 2007-11-14 : 17:48:49

Thanks Peso,
SELECT * FROM OPENQUERY(YOURDBLINK, 'SELECT @@tx_isolation AS f;')
It does work,returns current isolation level, but i need to change it.

Thanks Michael Valentine Jones,
I just wanna change Isolation Level of mysql,
and this need to be implemented in a stored procedure of SQLSERVER2005

ytsun
Starting Member

9 Posts

Posted - 2007-11-15 : 00:55:35

In brief, I need to change database isolation level of mysql by using SQLSERVER2005 stored procedure.

ytsun
Starting Member

9 Posts

Posted - 2007-11-15 : 08:29:23

...

ytsun
Starting Member

9 Posts

Posted - 2007-11-15 : 18:16:31

I have to use SSIS? No any other method?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-15 : 22:40:12

Tried this?

exec ('MySql command') at linked_server_name

ytsun
Starting Member

9 Posts

Posted - 2007-11-16 : 01:04:56

Thanks rmiao,
after running
EXEC ('SET Transaction Isolation Level READ UNCOMMITTED;') AT MYDBLINK
prompt:
(0 row(s) affected)

but the islolation level of mysql doesn't changed

SELECT * FROM OPENQUERY(MYDBLINK, 'SELECT @@TX_ISOLATION;')
result:
REPEATABLE-READ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources