anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.
We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
Chuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net.. .
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:

> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck
|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh
sql

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.comChuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net...
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:

> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.comChuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net...
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:
> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh

Anybody know how to solve this error when accessing a linked server that isn't a SQL data

TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=7311&LinkId=20476[/url]
BUTTONS:
OK
The links take you to the "We're sorry page," which doesn't help.
Thanks,
Tony
Tony
Could be a bug
http://connect.microsoft.com/SQLServer/feedback/Validation.aspx?FeedbackID=261012
"Tony" <tsheehan@.tji-holdings.com> wrote in message news:ekk$S$cbIHA.4172@.TK2MSFTNGP02.phx.gbl...
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=7311&LinkId=20476[/url]
BUTTONS:
OK
The links take you to the "We're sorry page," which doesn't help.
Thanks,
Tony

Any work around to pass parameters to OPENQUERY

I need to query a linked server which contains a table of million records an
d
need to fetch only the relevant records from the linked server.
Any ideas? Please helpSaji
Have you tried to use WHERE condition? Can you show us what you are trying
to do?
"Saji" <Saji@.discussions.microsoft.com> wrote in message
news:31530C04-03B9-4C24-9169-CBBD26773F3C@.microsoft.com...
>I need to query a linked server which contains a table of million records
>and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help|||This seems to work..
SELECT * FROM OPENQUERY(PS, '
SELECT
*
FROM
TESTDTA.F0401Z1
JOIN TESTDTA.F0101Z2 ON TESTDTA.F0401Z1.VOAN8 = TESTDTA.F0101Z2.SZAN8
WHERE
VOEDSP != ''C'' AND
VODRIN = ''2''
ORDER BY VOAN8, VOEDBT
')
"Saji" wrote:

> I need to query a linked server which contains a table of million records
and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry BellHi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>>I have recently completed upsizing a large Access 97 system from a Jet
>> backend to SQL Server 2000.
>> It uses linked tables and DAO exclusively.
>> It's working fine.
>> Sooner or later, the front end will need to go to A2003 or whatever,
>> which I asssume won't be too much of a problem.
>> Would there be any advantage converting to ADO, now, or when it goes to
>> A2003?
>> I can't see any justification at the moment.
>> Terry Bell
>|||Yes thanks - I posted to this newsgroup in error
Terry|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac/techinfo/default.aspx?pull=/library/en-us/dnmdac/html/data_mdacroadmap.asp
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegroups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry Bell
Hi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegr oups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>
|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegr oups.com...
>
|||Yes thanks - I posted to this newsgroup in error
Terry
|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegro ups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any need to convert DAO to ADO?

I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry BellHi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>|||Yes thanks - I posted to this newsgroup in error
Terry|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac...mdacroadmap.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegroups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>

Any ideas how to connect to an Informix DB?

I need to use the an OLE DB Provider to create a Linked Server using a ODBC-Connection (Informix). I know that I cannot see many things in the Providers drop-down list. Has anyone created a linked server like this one? I mean SQL 2005 (Standard Edition) x64 and Informix. 10x in advance.

Hi sageata2002,

I've got the same problem, and still searching the solution.

Perhaps we have to downgrade to SQL-Server 2005 x86 (32bit), where the MSDASQL-Provider (OLEDB Bridge for ODBC) is available....

|||

Hello.

I am waiting for an answer from a company named Openlink, since they told me they will provide the solution in couple of days (their driver is still in beta). I will keep anyone posted about this, and, if successful, I will provide the details (my understanding is that the same procedure will apply to Oracle, for example).

|||

Can you inform me, as soon as the Openlink-Solution is available?
That would be greatful!

Thank you very much.

Heiko Stahl
Systemadministrator
TQ-Systems GmbH

Heiko.Stahl@.tqs.de
http://www.tq-group.com

|||Of course I'll provide any useful news as soon as they will arive. I want this solved and shared with the rest of us, so we don't have to be at Microsoft's mercy; or any other giant for that matter|||

In the meantime I have contacted OpenLink, too.

I got a evaluation-version of an OLEDB-Provider for ODBC.

(download here:
http://download.openlinksw.com/uda/open60/wil6zole.msi ;; IA64
http://download.openlinksw.com/uda/open60/wal6zole.msi ;; AMD64
)

Unfortunately this Bridge works only with 64bit ODBC drivers, but our Informix SE is only 32 bit.

I must downgrade the SQL-Server to 32bit, no other way can help.

Regards,

Heiko

ANSI_WARNINGS error in Stored Procedure

Hi everyone,
I have a linked Server on my regular SQL server, and when I try to create a
stored procedure that reads from the linked server, I get the following erro
r:
ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS O
PTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES SEMANTI
CS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
When I click Check Syntax, it says that there are no Syntax Errors, but it w
ont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and "SET
ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same erro
r.
The query that is to be performed in the stored procedure is a simple SELECT
, no inner joins or anything. And the stored procedure receives only 1 (inte
ger) parameter.
Thanks in advance for your help.
Noe, from Mexico.Noe,
From the error I would understand that the linked server is not a Microsoft
SQL Server. Otherwise, how likely is it that the SQL Engine would be
concerned with "heterogeneous queries".
The error says to set these options for your _connection_ not for the query.
Since you are already connected before you try to compile the stored
procedure, it is too late for the SET commands to avoid the problem.
If you are using Query Analyzer to test this, then go to the menu item for
the connection and click these two settings on. (I believe that these are
normally set on by OLE DB / ODBC, but Query Analyzer may override these
settings.)
Russell Fields
"Noe" <noech77@.hotmail.com> wrote in message
news:D4F795B7-C67A-4F28-B95D-85B5FF7C7293@.microsoft.com...

quote:

---

> Hi everyone,
> I have a linked Server on my regular SQL server, and when I try to create

---

a stored procedure that reads from the linked server, I get the following
error:

quote:

---

> ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS

---

OPTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES
SEMANTICS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.

quote:

---

> When I click Check Syntax, it says that there are no Syntax Errors, but it

---

wont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and
"SET ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same
error.

quote:

---

> The query that is to be performed in the stored procedure is a simple

---

SELECT, no inner joins or anything. And the stored procedure receives only 1
(integer) parameter.

quote:

---

> Thanks in advance for your help.
> Noe, from Mexico.

---

|||Hi Russell, thanks a lot for replying...
you are correct, the linked server is a JDEdwards.
I was trying to create the stored procedure from the New Stored
Procedure Window, instead of the Query Analyzer. I dont know why I didnt
try the Query Analyzer before.
So there's a tip for all of you people, use the query analyzer and
forget about problems. Thanks again Russell!
Noe.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I doesn't mater which Database you use. Even in SQL server when we
link two different server, we get this problem. Once way of solving
this is create the stored procedure in SQL server in the format below
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
CREATE PROCEDURE dbo.MBUTotal
AS
BODY of THE STORED PROCEDURE
RETURN
GO
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
This will solve this problem. Atleast for me it helped to solve the
problem.
Regards,
John Solomon. J
Noe Carranza wrote:
> *Hi Russell, thanks a lot for replying...
> you are correct, the linked server is a JDEdwards.
> I was trying to create the stored procedure from the New Stored
> Procedure Window, instead of the Query Analyzer. I dont know why I
> didnt
> try the Query Analyzer before.
> So there's a tip for all of you people, use the query analyzer and
> forget about problems. Thanks again Russell!
> Noe.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it! *
john1811
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message291544.html

Ansi Nulls

I am get the following erro message when accessing the linked server table.
Any Suggetions ??

"HETROGENOUS QUERIES REQUIRE THE ANSI NULLS ON AND ASI WARNINGS TO BE SET FOR THE CONNETION. THIS ENSURES CONSISTENT QUERY SEMANTICS. ENABLE THESE OPTIONS AND THEN USE THE YOUR QUERY"

If Enable what go will go wrong because these are production servers..

Rajessentially you need to issue two statments:

set ANSI_NULLS on
set ANSI_WARNINGS on

I doubt setting either of these to on will give you any problems with the possability of NULL comparisons, see below.

as for what will go wrong, from Books Online:

ANSI_NULLS

When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULLS.

SET ANSI_NULLS also must be set to ON when you create or manipulate indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

and

ANSI_WARNINGS

When set to ON, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF.

SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.

The status of this option can be determined by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

ANSI defaults, Linked Server

Hey all,
I have a linked server on our SQL 2000 box, which points to PCCW.mdb; An
Access database that our Credit Card processing software uses. I have
written a SP to grab some data from this Linked Server, and from my local SQ
L
database. The Syntax check on the SP goes fine, but when I try to save the
SP, I get the warning:
"Error 7405: 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 the reissue your query"
In the SP I am issuing the commands for ANSI_NULLS and ANSI_WARNINGS (will
post below), and when I run the SELECT statement contained in the SP through
Query Analyzer, it works fine. Any help is always appreciated, and thanks i
n
advance.
Gerard - SP posted below
CREATE PROCEDURE spBasSunLyncInBadCredit
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT tblCreditCardControl.CID, PCCW..Trans.[Date], PCCW..Trans.[Time],
PCCW..Trans.Card, PCCW..Trans.TID, PCCW..Trans.Issuer, PCCW..Trans.Member,
PCCW..Trans.ExpDate, PCCW..Trans.Amount, PCCW..Trans.Status,
PCCW..Trans.Auth, PCCW..Trans.BatchNumber, PCCW..Trans.Ticket FROM
PCCW...Trans INNER JOIN tblCreditCardControl ON PCCW..Trans.TID =
tblCreditCardControl.MerchantNumber WHERE ((tblCreditCardControl.CID = '357'
)
AND (PCCW..Trans.Status = 'C') AND (PCCW..Trans.[Date] = '2/7/05'))
GOWhere are you creating the sp in EM?, Try using QA.
BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing
Linked Server Objects
http://support.microsoft.com/defaul...kb;en-us;296769
AMB
"Gerard" wrote:

> Hey all,
> I have a linked server on our SQL 2000 box, which points to PCCW.mdb;
An
> Access database that our Credit Card processing software uses. I have
> written a SP to grab some data from this Linked Server, and from my local
SQL
> database. The Syntax check on the SP goes fine, but when I try to save th
e
> SP, I get the warning:
> "Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warning
s
> options to be set for the connection. This ensures consistent query
> semantics. Enable these options and the reissue your query"
> In the SP I am issuing the commands for ANSI_NULLS and ANSI_WARNINGS (will
> post below), and when I run the SELECT statement contained in the SP throu
gh
> Query Analyzer, it works fine. Any help is always appreciated, and thanks
in
> advance.
> Gerard - SP posted below
> CREATE PROCEDURE spBasSunLyncInBadCredit
> AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SELECT tblCreditCardControl.CID, PCCW..Trans.[Date], PCCW..Trans.[Time],
> PCCW..Trans.Card, PCCW..Trans.TID, PCCW..Trans.Issuer, PCCW..Trans.Member,
> PCCW..Trans.ExpDate, PCCW..Trans.Amount, PCCW..Trans.Status,
> PCCW..Trans.Auth, PCCW..Trans.BatchNumber, PCCW..Trans.Ticket FROM
> PCCW...Trans INNER JOIN tblCreditCardControl ON PCCW..Trans.TID =
> tblCreditCardControl.MerchantNumber WHERE ((tblCreditCardControl.CID = '35
7')
> AND (PCCW..Trans.Status = 'C') AND (PCCW..Trans.[Date] = '2/7/05'))
> GO