February 26, 2002 at 6:59 am
We're trying to write a sproc to be used by Crystal reports to query an access database. The linked server end of things is okay. What happens is that when we try to run the sproc from Crystal we get a message something like 'General SQL Server error, check sql server for details'. Well (deep breath) imbued with a sense of confidence from my first MCP I decided to try my hand a using traces! And lo and behold I managed to find the error was Error 7405.
Being well pleased at that success I looked up that message and I trace it back to the fact that it is necessary to set ANSI_WARNINGS ON for distributed queries. I can't for the life of me figure out how to do this. I've tried to alter the procedure and set warnings on but every time I go to view the procedure the line is missing. I am sure this is something incredibly stupid but I'd appreciate your help
Edited by - deltakilo on 02/26/2002 07:02:48 AM
February 26, 2002 at 7:46 am
Try copying the Procedure out and pasting into Query Analyzer and make your changes there. Then change CREATE PROCEDURE to ALTER PROCEDURE and run, then go check.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 8:23 am
Nope, that's not it. But thanks.
DeltaKilo
February 26, 2002 at 8:38 am
What happens when you execute the stored procedure through Query Analyzer?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 26, 2002 at 8:45 am
Also what does you PROCEDURE code look like.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 9:15 am
Gifted ! This has been a long day and it was immensely stupid to fix. SET ANSI_WARNINGS is a database option... not something to set in a stored procedure and it was staring me in the face.. doh.
Does anyone know if setting it ON will affect our € applications? There was a suggestion that € is outside the ANSI character set and would cause errors for users using the € symbol..... but hey that's tomorrows problem.
ps as it happens it always worked from query analyzer ....... "ahh query analyser, my friend" a seldom heard Homer Simpson quote
February 26, 2002 at 10:00 am
Don't feel bad, none of us caught that either and I had BOL open at the time.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 23, 2002 at 8:35 pm
I think that you were correct with your original assumption. This is something that you can set in TSQL.
If you don't set the value in your stored proc it will default to the setting for the whole database.
I found that I got the same error as you when trying to access a visual foxpro linked server through a stored proc, but only when trying to use Enterprise Manager to create the proc.
CREATE PROCEDURE dbo.sp_contact AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT contact.*
FROM OPENQUERY(foxprolinkedserver, 'SELECT * FROM
contact') contact
GO
After creating the stored proc in Query Analyser I was able to run it from either Enterprise manager or Query Analyser using Exec sp_contact, although I havent tried accessing it from an external app yet.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply