Solved

ANSI_DEFAULTS Error???!?!?!

Posted on 2001-06-13
7
577 Views
Last Modified: 2010-05-18
Hi all,
      I have setup a merge replication between two sqlservers and wrote a
custom conflict resolver
function to resolve conflicts in a table. The code of this stored procedure
is as follows.

CREATE PROCEDURE sp_ResolveProductConflict
(@tableowner  SYSNAME,
@tablename   SYSNAME,
@rowguid   UNIQUEIDENTIFIER,
@subscriber   SYSNAME,
@subscriber_db  SYSNAME,
@log_conflict INT OUTPUT,
@conflict_message  nvarchar(512) OUTPUT
)
AS
DECLARE @FirstPassYieldPublisher  BIT
DECLARE @DateRevisedPublisher DATETIME
DECLARE @FirstPassYieldSubscriber BIT
DECLARE @DateRevisedSubcriber DATETIME
DECLARE @pub_qualified_name NVARCHAR(392)
DECLARE @sub_qualified_name NVARCHAR(392)
DECLARE @rowguidvar uniqueidentifier
DECLARE @select_string NVARCHAR(2000)

SET ANSI_DEFAULTS ON

SELECT
@pub_qualified_name=QUOTENAME(db_name())+'.'+QUOTENAME(@tableowner)+'.'+QUOT
ENAME(@tablename)
SELECT
@sub_qualified_name=QUOTENAME(@subscriber_db)+'.'+QUOTENAME('dbo')+'.'+QUOTE
NAME(@tablename)
SELECT @select_string= 'SELECT * FROM OPENQUERY('+QUOTENAME(@subscriber)+','
+ char(39) + 'SELECT DaveRevised,FirstPassYield,ROWGUIDCOL
 FROM  '  + @sub_qualified_name  + char(39) +  ') '
EXECUTE (@select_string)

SELECT @select_string= 'SELECT * FROM OPENQUERY('+QUOTENAME(@subscriber)+','
+ char(39) + 'SELECT DaveRevised,FirstPassYield,ROWGUIDCOL
 FROM sitemaster.dbo.product where ROWGUIDCOL='  +  char(39) +
convert(varchar(36),@rowguid) +  char(39)
+  ') '


IF (CURSOR_STATUS('global','get_subscribervalues') <> -3)
     DEALLOCATE get_subscribervalues               -- old one still around -
get rid of it.

SELECT @select_string='DECLARE get_subscribervalues CURSOR GLOBAL
FAST_FORWARD FOR ' + @select_string
-- Go ahead and execute the declare cursor now, then we can fetch values
into variables.
-- There will never be more than one row per guid - hence no FETCH loop
necessary


EXECUTE (@select_string)
     if (@@error <> 0)
          BEGIN
          RAISERROR('Error Declaring Cursor.',18,-1)
          RETURN(1)
          END
RAISERROR("Computation of Resolved  Failed.",18,-1)

OPEN my_curs
     if (@@error <> 0)
          BEGIN
          RAISERROR('Error Opening Cursor.',18,-1)
          RETURN(1)
          END

FETCH NEXT FROM my_curs INTO
@DateRevisedSubcriber,@FirstPassYieldSubscriber,@rowguidvar
     if (@@fetch_status <> 0)  -- We always expect first fetch to succeed.
          BEGIN
          RAISERROR('Error Fetching Subscriber row from Cursor.',18,-1)
          RETURN(1)
          END

FETCH NEXT FROM my_curs INTO
@DateRevisedSubcriber,@FirstPassYieldSubscriber,@rowguidvar -- Since this
FETCH will fail - variables not reassigned.
     if (@@fetch_status <> -1)  -- We always expect the second fetch to fail
since we expect exactly one
row.
          BEGIN
          RAISERROR('Error: Unexpected cursor result.  Multiple rows may
have been retrieved from Subscriber.',18,-1)
          RETURN(1)
          END
CLOSE my_curs
DEALLOCATE my_curs


SELECT @DateRevisedPublisher = DATEREVISED , @FirstPassYieldPublisher =
FIRSTPASSYIELD
FROM PRODUCTS WHERE ROWGUIDCOL=@rowguid

if (@@error <> 0 OR @@rowcount <> 1)
     BEGIN
     RAISERROR("Computation of Resolved  Failed.",18,-1)
     RETURN(1)
     END

IF @FirstPassYieldPublisher = 0  return 0
IF @FirstPassYieldSubscriber = 0  return 1



IF @DateRevisedPublisher >  @DateRevisedSubcriber
     return 0
ELSE
     return 1
GO


This stored procedure compiles fine. But when a conflict occurs in a row of
the table I get the error

The Stored Procedure Resolver encountered the following error executing the
stored  procedure 'sp_ResolveProductConflict'.
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection.
This ensures consistent query sem.

I have changed the default connection settings in both of the servers but it
didnt help. I have also
tried to put SET ANSI_DEFAULTS ON in the stored procedure.





0
Comment
Question by:cenkcivici
7 Comments
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Did you try:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

Beyind that, I'd comment out pieces of code until the error went away.  That could provide a clue.

-- Dan
0
 
LVL 1

Author Comment

by:cenkcivici
Comment Utility
Yes I tried it and commented the code leaving only the beginning which was executing the code execute(@select_string) statement. I can execute distributed queries from Query Analyzer but it doesnt work in a conflict resolver stored procedure. It also works fine in a normal stored procedure.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
I just read that SET ANSI_Whatever won't have any effect in a stored procedure.  Perhaps you need to set the two databases so that they have the same settings.

>>leaving only the beginning which was executing the code execute(@select_string)

Have you tried simplifying the code that gets used there?  I have had success debugging by breaking long statements into more easily-digested chunks and plugging in literal values as hardcoded test.  For instance,

DECLARE @sSubQ varchar(255)

SET @sSubQ =
  ' SELECT DaveRevised,FirstPassYield, ROWGUIDCOL '
+ ' FROM sitemaster.dbo.product '
+ ' WHERE ROWGUIDCOL='
+char(39) +convert(varchar(36),@rowguid)+ char(39)

SET @select_string=
  'SELECT * '
+ 'FROM OPENQUERY ("DansServer", '
+ char(39) +@sSubQ+ char(39) + ')'

EXECUTE (@select_string)
   
(I did not test this code)
-- Dan
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:cenkcivici
Comment Utility
Nope,
  i have tried that before and didnt work. I get this error in the very first Execute statement. I have replaced the sql statement with a statement which was only retrieving all the rows without the usage of a Cursor but got the same error. I have written a com component that does the samething and it worked.
0
 

Expert Comment

by:CleanupPing
Comment Utility
cenkcivici:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Recommendation: Save as 0-pt PAQ
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed, with points refunded (300)

modulo
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now