Hi,
We are having problems with those two procedures, they are blocking other processes. I have seen sp_prepexec block another process for 5 hours, beforeI finally killed the process and the blocked process executed. Has anyone experienced behavior like this? What else can I do to troubleshoot this and determine the issue that is causing this? We could really use some assistance to determine the root of the problem.
Thanks.
Hi, I was wondering if anybody has ever had a problem with blocking and the sp_unprepare statement. We have an application that is inserting data into a table and the process will freeze until we kill spid executing the sp_unprepare statement because it is blocking another spid. We have just recently upgraded to SP2 could this be the problem?
View 1 Replies View RelatedHi,
I'm running a profiler trace and I'm finding that sP_prepexec is running over 6 seconds everytime.
I looked at the SQL profiler and found out that the ODBC driver generates the update statements using sp_prepexec, I tried to run the update(without sp_prepexec) in Query analyzer, it returns the results right away.
Is there any known problems with ODBC driver about this or sp_prepexec related problems, if yes, how can I work around this.
The following was captured from SQL Profiler during an interaction betweenAccess Xp and SQL Server 2000:declare @P1 intset @P1=1exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT"totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENID","FromFPW","prevdown","tempifpssysid","upsize_ts"FROM "dbo"."Family"WHERE "totalid" = @P1', N'-:1044463096'select @P1goI think I understand that sp_prepexec prepares a parameterized query with@P1 as a variable...but sp_prepexec is not in Books Online...could someone please explain the syntax of sp_prepexec?why is @P1 used for both the parameter within the select query and the nameof the prepared statement?the other seemingly strange thing, is that when I run the above in QueryAnalyzer and try to use the parameterized query like soexec sp_execute 1, N'-:1044463096'goI receive the error messageCould not find prepared statement with handle 1
View 1 Replies View RelatedOne of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".
I have no idea how to resolve this nor why it is happening.
Rob.
I have a statement that works in compatibility mode 8 of SQL Server2000 while it fails in 7:declare @P3 intexec sp_prepexec @P3 output, N'@P1 float', N'select custname fromcustomer where custnr = @P1', 12600034custnr is a varchar(15). As long as the float p1 is less than1,000,000 the query returns correct results, but as soon as p1 isequal or greater than 1,000,000 the resultset is empty. I couldfigure, that above 1 million, sp_exec converts the float intoexponential format and compares this format to the string containingfixed format values. As said above, in mode 8 the statement returnscorrect results above 1,000,000.Please don't ask me, why we use float variables - I don't have anyinfluence on the statement - we isolated the statement with theprofiler.We are also currently dependent on the compatibility mode 7.I would be very grateful if you have any ideas, help or other issuesfor me.Thank you,Peter
View 1 Replies View Related