August 22, 2008 at 12:38 pm
Hi,
I am running SQL Server 2000 Standard Edition 8.00.2039 SP4 and am running into a problem while running a stored procedure. I am trying to execute the following code:
DECLARE @RC int
DECLARE @ACCOUNT varchar(64)
-- Set parameter values
EXEC @RC = [Contributions].[dbo].[web_proc_SLXOrganization] 'adv'
But when I do so I get the following error message:
Server: Msg 8623, Level 16, State 1, Procedure web_proc_SLXOrganization, Line 18
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
The contents of the stored proc are:
ALTER PROC dbo.web_proc_SLXOrganization(
@ACCOUNTVARCHAR(64)
)
AS
SELECTACCT.[ACCOUNTID],
[Contributions].[dbo].[fnc_FormattedOrg](ACCT.ACCOUNT, CITY, STATE) AS ACCOUNT
FROM [SLXReporting].[dbo].[vw_SLX_ACCOUNT] ACCT
INNER JOIN[SLXReporting].[dbo].[vw_SLX_ADDRESS] ADDR
ONACCT.ADDRESSID = ADDR.ADDRESSID
WHEREACCOUNT LIKE '%' + @ACCOUNT + '%'
AND
ACCOUNT IS NOT NULL
ORDER BY ACCOUNT
If I pull the code within the stored procedure out and just run it in SQL query analyzer the code works without a problem. In addition, the stored proc has a WHERE LIKE clause in it, and if I remove that then the stored proc works. On top of all this, if I bring up my backup server (which is a replicate of my production server) I can run the stored proc in its correct format, without any changes and with the WHERE LIKE clause. The query is a basic SELECT statement, the only quirky thing about it is the data is being pulled from a Linked Server.
I've tried a whole bunch of stuff and can't seem to get this stored procedure to work but I don't know why. Any advice would be greatly appreciated. Thanks so much.
-NifflerX
August 23, 2008 at 10:49 am
No clue why the stored procedure gives the error and the adhoc doesn't.
Is the backupserver on the same build as the prod server? There is a cumulative hotfix 2187
August 23, 2008 at 10:55 am
Thank you so much for the reply. I'll take a look at the link you gave me, but wanted to let you know about the backup server. It is the same as the production server, same patch level, version of SQL server etc. One thing I didn't mention was this is something that just appeared one day, the stored procedure did work at one point, but then I was working with another linked server, I have a couple setup to the same SQL server, and all of a sudden it stopped working. Again, thank you for the link, I'll check it out and any more information would be greatly appreciated. Thanks again.
-NifflerX
August 24, 2008 at 10:58 pm
I think that you should do what the error message is telling you:
Contact your primary support provider for more information.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 7:47 am
Hi,
Thank you very much for the reply, and I will be trying to get a hold of Microsoft today, we'll see how that goes. One of the reasons I was hesitant to do that was that in addition to the stored procedure described above, I have another stored procedure that is effectively the same (a select query with a like in the where clause) but that stored procedure produces a different error message:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Other than the error message the situations are identical, I can run the code from the stored proc ad hoc and I can run the proc off the backup server. As I said I will give Microsoft a call and see what I can get, but if this new error message means anything to anyone, please let me know as any help would be appreciated. Thanks again.
-NifflerX
August 25, 2008 at 9:38 am
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
is usually a sign of database corruption.
August 25, 2008 at 9:43 am
Thank you for the reply. When you say database corruption do you mean on my database that is holding the Stored procedure, or is it more likely in the database that is using my linked server? I've run DBCC check on both and both reports have been clean. I have not run DBCC check on the actual database that my linked server is connecting to. Would you recommend I try that next? Thank you again.
-NifflerX
August 26, 2008 at 3:23 am
I would run it on the databases mentioned
in [Contributions]
and those mentioned in [SLXReporting].[dbo].[vw_SLX_ADDRESS],[SLXReporting].[dbo].[vw_SLX_ACCOUNT]
August 26, 2008 at 9:11 am
Hi,
I've posted the summary from CheckDB on those two databases, but neither have any errors. I can post the entire log if anyone thinks it would help.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Contributions'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SLXReporting'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What I've done as a temporary work around is to load the views that seem to be causing problems to a table variables. When I do this without any sort of where clause the stored procedure doesn't break. Then I am able to do my where clause on the table variable. I have no idea why this works but selecting with a where clause fails. If this sheads any light on the issue to anyone please let me know, or if my workaround has some major flaw that I'm missing. Thank you again for all your help.
-NifflerX
September 10, 2008 at 12:18 pm
Hi,
I realize this thread has been quiet for some time but I've finally heard back from some of my providers. They had me try to run the stored procedure on the computer that is actually hosting the SQL server. When I did that I got a new error message:
SqlDumpExceptionHandler: Process 65 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Unfortunately that error message doesn't mean anything to me, so I was hoping this new error message would shed some light for more knowledgable people. If anyone has any ideas, I'm all ears. Thank you again for all your help.
-NifflerX
September 10, 2008 at 4:53 pm
NifflerX (9/10/2008)
I realize this thread has been quiet for some time but I've finally heard back from some of my providers. They had me try to run the stored procedure on the computer that is actually hosting the SQL server. When I did that I got a new error message:
SqlDumpExceptionHandler: Process 65 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Unfortunately that error message doesn't mean anything to me, so I was hoping this new error message would shed some light for more knowledgable people.
Um, if you are "finally" in contact with your providers (i.e., Microsoft) why would you bring this error to us? I would assume that this error indicates corruption of your binaries, what did Microsoft have to say about it?
Seriously, there are only two things that you should even be considering at this point: 1) talk to Microsoft, or 2) failing that, reinstall.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply