[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ANSI_DEFAULTS Error???!?!?!

Posted on 2001-06-13
7
Medium Priority
?
601 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

649 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