MSDataShape problem using SQL server 2000

[Yves Touze]

Hi All,

I'm trying to migrate from SQL Server 7.0 to SQL Server 2000.
I've got some ASP page which call VB components that retrieve shaped
recordsets from SQL Server using the MSDATASHAPE provider.

Precisely, here is the code i have
Dim Cmdobj As New ADODB.Command
Cmdobj.ActiveCo nnection = oconn
Cmdobj.CommandT ype = adCmdStoredProc
Cmdobj.CommandT ext = "SP_GET_RES ULT"
Cmdobj.Prepared = True

We're using stored procedure and the tables are exactly the same.
Although they work fine with SQL Server 7.0, they fail when I moved to
SQL
Server 2000 and i got this message: "error '800706be'"

If i don't set "prepared=True" , then it works fine with SQL Server
2000.

Strange isn'it ?

Thanks for any answers,

Yves

[Val Mazur]

Re: MSDataShape problem using SQL server 2000

Hi,

But do you have any parameters? If yes, then try to set Prepared property
after you created and added parameters to the collection

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated

http://www.microsoft.com/security/incident/blast.asp

"Yves Touze" <yves.touze@mix ad.com> wrote in message
news:f15a76b4.0 312090443.62097 1c5@posting.goo gle.com...[color=blue]
> Hi All,
>
> I'm trying to migrate from SQL Server 7.0 to SQL Server 2000.
> I've got some ASP page which call VB components that retrieve shaped
> recordsets from SQL Server using the MSDATASHAPE provider.
>
> Precisely, here is the code i have
> Dim Cmdobj As New ADODB.Command
> Cmdobj.ActiveCo nnection = oconn
> Cmdobj.CommandT ype = adCmdStoredProc
> Cmdobj.CommandT ext = "SP_GET_RES ULT"
> Cmdobj.Prepared = True
>
> We're using stored procedure and the tables are exactly the same.
> Although they work fine with SQL Server 7.0, they fail when I moved to
> SQL
> Server 2000 and i got this message: "error '800706be'"
>
> If i don't set "prepared=True" , then it works fine with SQL Server
> 2000.
>
> Strange isn'it ?
>
> Thanks for any answers,
>
> Yves[/color]

[Chirag]

Re: MSDataShape problem using SQL server 2000

I do remember

Just Read the URL

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/

html/adoapp.asp


One point From this URL

5. Beware of Prepared Statements and Temporary Stored Procedures
You can use prepared statements to execute statements that SQL Server might
reuse several times during a user session but not enough times to warrant
your creating a stored procedure. You use the ADO Command object and the
Prepared property to set up prepared statements, as Listing 5 shows. But
beware of the possible pitfalls of using prepared statements. Applications
might needlessly generate prepared statements. In SQL Server 6.5, the SQL
Server ODBC driver, which creates, executes, and drops temporary stored
procedures, supports prepared statements. The creation of temporary stored
procedures can cause error 1105, which means you've run out of space on the
system segment of the tempdb database. To avoid this problem, make sure that
the Create temporary stored procedures for prepared SQL Statements check box
in the ODBC data source is unchecked. Note that this selection is
unavailable in SQL Server 7.0. The overhead of creating, executing, and
dropping the temporary stored procedures is high, and often this procedure
doesn't provide a benefit. For example, while investigating an application
deadlock problem, I found that the drop statements on the temporary stored
procedures were also deadlocking in the tempdb database. If you see
excessive statements referring to objects called #ODBC# in a SQL Server
trace, then the application might be needlessly using prepared statements. I
once traced an application that had an average of 3000 temporary stored
procedures in the tempdb database at a given time, all of which were used
once and dropped.
SQL Server 7.0 implements prepared statements internally. A SQL Profiler
trace shows prepared statements as extended stored procedures sp_prepare and
sp_execute. By setting the Prepared property of an ADO Command object to
true (you need to set the CommandType property to adCmdText), you tell the
data-access layers and the SQL Server that you plan to reuse the query with
varying parameter values during the application session. In my experience,
this method works well if the prepared statement is actually reused, but it
can present pitfalls, too. At a customer site, developers used an old
version of ODBC and the prepare and execute functions in the ODBC API to
write a C++ application. When the company upgraded to SQL Server 7.0, the
application slowed down. A SQL Profiler trace showed sp_prepare and
sp_execute calls where simple SELECT statements had existed before, which
hampered communications between the client and SQL Server. SQL Server 7.0
ships with a utility in the MSSQL7\Binn folder called ODBCCMPT.EXE. It
implements the SQL Server 6.5 prepare and execute behavior for individual
applications. After I applied this fix and made sure that the ODBC data
source wasn't using temporary stored procedures, the application's
performance improved. To set an ODBC Data Source Name (DSN) to not create
temporary stored procedures when it points to a SQL Server 7.0 server, you
need to add the UseProcForPrepa re value, set to 0, to the Registry entry for
that DSN. (SQL Server Books Online-BOL-documents the ODBCCMPT.EXE utility.)
Similar to the situation with temporary stored procedures in SQL Server 6.5,
excessive calls to sp_prepare and sp_execute in a SQL Trace warrant further
investigation if you think the prepared statements aren't being reused.
Listing 5 shows the code for the ADO Command object to execute the stored
procedure.





"Yves Touze" <yves.touze@mix ad.com> wrote in message
news:f15a76b4.0 312090443.62097 1c5@posting.goo gle.com...[color=blue]
> Hi All,
>
> I'm trying to migrate from SQL Server 7.0 to SQL Server 2000.
> I've got some ASP page which call VB components that retrieve shaped
> recordsets from SQL Server using the MSDATASHAPE provider.
>
> Precisely, here is the code i have
> Dim Cmdobj As New ADODB.Command
> Cmdobj.ActiveCo nnection = oconn
> Cmdobj.CommandT ype = adCmdStoredProc
> Cmdobj.CommandT ext = "SP_GET_RES ULT"
> Cmdobj.Prepared = True
>
> We're using stored procedure and the tables are exactly the same.
> Although they work fine with SQL Server 7.0, they fail when I moved to
> SQL
> Server 2000 and i got this message: "error '800706be'"
>
> If i don't set "prepared=True" , then it works fine with SQL Server
> 2000.
>
> Strange isn'it ?
>
> Thanks for any answers,
>
> Yves[/color]