Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: sp_prepexec problems

    Hi All:

    We have a booking engine developed using cold fusion MX and
    sql server, our sql server is performing real slow, cpu utilization
    is abt 70 -95 % all the time.

    Below is the stored procedure which is running slow, found after running the profiler.

    declare @P1 int set @P1=NULL declare @P2 varchar(8000) set @P2=NULL
    exec sp_prepexec @P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,
    @P2 output select @P1, @P2 151.34 usr001 NULL server_name NULL 65
    2003-08-06 11:35:47.733 2003-08-06 11:38:19.077

    Please let me know what could be the problem.

    Regards

    Bhakta Ram

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    This looks like something I see sometimes with MS Access. The sp_prepexec is done when the frontend sends an T-SQL statement to the backend and the connection has been setup to turn all statements into store procedure calls. Prior to this you should have seen sp_prepare been executed.

    In MS Access I've setup the ODBC connection with :
    UseProcForPrepare=0

    Which enlimits the creation of the temporay procedure. However if the same T-SQL statement is used multiple times within the same connection this temporary procedure can be a preformance boost.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •