• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

ANSI_DEFAULTS Error???!?!?!

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
cenkcivici
Asked:
cenkcivici
1 Solution
 
DanRollinsCommented:
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
 
cenkciviciAuthor Commented:
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
 
DanRollinsCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
cenkciviciAuthor Commented:
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
 
CleanupPingCommented:
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
 
DanRollinsCommented:
Recommendation: Save as 0-pt PAQ
0
 
moduloCommented:
PAQed, with points refunded (300)

modulo
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now