Solved

ANSI_DEFAULTS Error???!?!?!

Posted on 2001-06-13
7
588 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
ID: 6190108
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
ID: 6191936
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
ID: 6197159
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:cenkcivici
ID: 6216124
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
ID: 9282228
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
ID: 9283947
Recommendation: Save as 0-pt PAQ
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10246181
PAQed, with points refunded (300)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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