Linking Server to MySql

Hello All,
I am stuck with this really frustrating problem...

I am using a linked server to run a qeury to mysql with sp_addlinked server etc...

The problem i am having is I am using this in a trigger with sql server 2000 and it always errors there for the row doesnt get inserted.

the error is
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into mail_relay(ip_addr,timestamp) values('myip',1039087122)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

With mysql I cant use 4 part names cause I get this error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

So I have to run
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122)')

and mysql also doesnt support multiple queries in the one statement so i cant do this to stop the error:
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122);select null')

so my question is,
I want to be able to stop the error message being reported in my trigger so the row still gets inserted

I am sure it is possible.. I really hope so... any help would be really greatly appreciated

thanks guysCan you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.|||Originally posted by zmichailov
Can you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.

Outside of the trigger the error still occurs...
Here is a the way i set the connection up...

EXEC sp_addlinkedserver
'MailServer',
'',
'MSDASQL',
NULL,
NULL,
'DSN=MailServer;UID=myuser;pwd=mypass;'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname='MailServer',
@.useself=false,
@.locallogin=null,
@.rmtuser='myuser',
@.rmtpassword='mypass'
Exec sp_serveroption 'MailServer', 'data access', 'true'|||Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay|||Originally posted by zmichailov
Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay

Hey, Sorry for delay in reply,
in answer to these questions

1. The default database on the DSN is vpopmail, I am using the latest MyODBC driver for this...
In mySql you can have as many dbs as your want to on any server.

2. i get the following error message
Server: Msg 7313, Level 16, State 1, Line 17
Invalid schema or catalog specified for provider 'MSDASQL'.

i also found MyOLEDB ole db provider but It would create an instanmce of it it just errors saying

Server: Msg 7302, Level 16, State 1, Line 17
Could not create an instance of OLE DB provider 'MySQLProv'.

Well any help would be great, thanks again|||i have exactly same problem, do you have some solution?|||This may help.

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.htmlsql

Linking Server to a different Domain

Hi:
I am trying to create a Linked Server to SQL Server but residing in a differ
ent domain. It is giving me a error message that It does not recognize this
SQL Server. Please let me know how should create this. Any help is greatl
y appreciated.
ThanksHi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison|||Yes. I acn connect with query analyzer.|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a diffe
rent domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated
.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C|||I was able to Create Linked Server by creating an ODBC on the server and the
n using that ODBC to create Linked Server as Data Source but It would not al
low me to create Linked Server by specifying the SQL Server Name. It someho
w does not recognize the SQ
L Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but
if you were able to create by just specifying the sql server name, please l
et me know.
Thanks
Sal

Linking Server to a different Domain

Hi:
I am trying to create a Linked Server to SQL Server but residing in a different domain. It is giving me a error message that It does not recognize this SQL Server. Please let me know how should create this. Any help is greatly appreciated.
Thanks
Hi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>
|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison
|||Yes. I acn connect with query analyzer.
|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a different domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C
|||I was able to Create Linked Server by creating an ODBC on the server and then using that ODBC to create Linked Server as Data Source but It would not allow me to create Linked Server by specifying the SQL Server Name. It somehow does not recognize the SQ
L Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but if you were able to create by just specifying the sql server name, please let me know.
Thanks
Sal

Linking Server to a different Domain

Hi
I am trying to create a Linked Server to SQL Server but residing in a different domain. It is giving me a error message that It does not recognize this SQL Server. Please let me know how should create this. Any help is greatly appreciated
ThankHi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison|||Yes. I acn connect with query analyzer.|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:
>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a different domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C|||I was able to Create Linked Server by creating an ODBC on the server and then using that ODBC to create Linked Server as Data Source but It would not allow me to create Linked Server by specifying the SQL Server Name. It somehow does not recognize the SQL Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but if you were able to create by just specifying the sql server name, please let me know.
Thank
Sal

Linking Server problem

Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
Rodrigo
Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo

Linking Server problem

Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||This is a multi-part message in MIME format.
--060600060209060500070603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
--060600060209060500070603
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, this is wrong (please verify your guesses before providing
them).<br>
<br>
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.<br>
<br>
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the
same values for the ANSI_NULLS & ANSI_WARNINGS settings. Both
servers have to have them enabled (turned on).<br>
<br>
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc
and used when the proc is executed. So, when you create or alter the
proc (with the linked server reference) make sure that "SET ANSI_NULLS
ON" is there before the ALTER PROC or CREATE PROC statement (just to be
sure).<br>
<br>
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA
session it used ANSI_NULLS on but when you executed the proc from that
same session it used the ANSI_NULLS value that was saved with the proc
(ie. ANSI_NULLS off). That's my guess.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
doller wrote:
<blockquote
cite="mid1123641009.412382.267360@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
</pre>
</blockquote>
</body>
</html>
--060600060209060500070603--|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigosql

Linking Server problem

Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query
."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo