Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to
access data on another Server running 2000. I get the following error, whe
n I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable the
se options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analy
zer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
JimReview the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure t
o access data on another Server running 2000. I get the following error, wh
en I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to b
e set for the connection. This ensures consistent query semantics. Enable th
ese options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Anal
yzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.sql

Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
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.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>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.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.

error with trigger

Hi ,
I have created a trigger as follows :
========================================
=========================
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER trigger Usr_Trig_GL53
ON Gl530106
FOR INSERT , UPDATE
NOT FOR REPLICATION
AS
IF UPDATE(GL53001) OR UPDATE(GL53003) or UPDATE(GL53004)
-- this is an update
BEGIN
UPDATE b
SET b.[GL53001] = a.[GL53001] , b.[GL53003] = a.[GL53003] , b.[GL53004] = a.
[GL53004]
FROM inserted as a INNER JOIN ALTIRISSERVER.ScalaDB_SGO_TEST.dbo.Gl530106
as b ON a.GL53001 = b.GL53001
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================
=========================
however when i made some modifications i was prompted the follow error
message
"another user has modified the contents of this table or view ; the database
row you are modifying no longer exists in the database .
Database Error : [Microsoft][ODBC Server Driver][SQL Server][OLD/DB provider
returned Message : New transaction cannot enlist in the specified transactio
n
coordinator ]
[Microsoft][ODBC Server Driver][SQL Server][ The operation cannot be
performed because the OLD DB Provider 'SQLOLEDB' was unable to begin a
distributed transaction "
Is this due to any services that not running ? --> i have started the SQL
Server Agent and the DTC from SQL EM
i am using SQL Standard Edition sp3 but this test wqas carried out from my
machine which is installed with MSDE2000
appreciate ur advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1Hi,
I guess there is no primary key on the table whereas SQL Server could
decide what row to update there, so put a primary key on the table you
want to access / update and that should be fine. If you want to change
a view on the remote server, make sure that the view is updateable,
otherwise the row cannot be changed either.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi,
I guess there is no primary key on the table whereas SQL Server could
decide what row to update there, so put a primary key on the table you
want to access / update and that should be fine. If you want to change
a view on the remote server, make sure that the view is updateable,
otherwise the row cannot be changed either.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi ,
I have added PK to both the source & destination table but i still have the
same error
appreciate ur advice
tks & rdgs
Jens wrote:
>Hi,
>I guess there is no primary key on the table whereas SQL Server could
>decide what row to update there, so put a primary key on the table you
>want to access / update and that should be fine. If you want to change
>a view on the remote server, make sure that the view is updateable,
>otherwise the row cannot be changed either.
>HTH, Jens Suessmeyer.
>--
>http://www.sqlserver2005.de
>--
Message posted via http://www.webservertalk.com

Error while creating inline function - CREATE FUNCTION failed because a column name is not speci

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

|||

You have to specify an alias for the columns in the select statement. Change the query to somthing like this:

Code Snippet

SELECT ident_current('orders') AS 'ID'

I hope this answers your question.

Best regards,

Sami Samir

|||Thats easy:

Code Snippet

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders') AS Newidentity)

GO

Jens K. Suessmeyer.

http://www.sqlserver2005.de