473,388 Members | 873 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

MSDataShape problem using SQL server 2000

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.ActiveConnection = oconn
Cmdobj.CommandType = adCmdStoredProc
Cmdobj.CommandText = "SP_GET_RESULT"
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
Jul 20 '05 #1
2 7300
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" <yv********@mixad.com> wrote in message
news:f1**************************@posting.google.c om...
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.ActiveConnection = oconn
Cmdobj.CommandType = adCmdStoredProc
Cmdobj.CommandText = "SP_GET_RESULT"
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

Jul 20 '05 #2
I do remember

Just Read the URL

http://msdn.microsoft.com/library/de...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 UseProcForPrepare 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" <yv********@mixad.com> wrote in message
news:f1**************************@posting.google.c om...
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.ActiveConnection = oconn
Cmdobj.CommandType = adCmdStoredProc
Cmdobj.CommandText = "SP_GET_RESULT"
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

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: chris.dunigan | last post by:
I'm looking for an example of how to execute an existing DTS­ package from an ASP (VB)script and would appreciate any and all response. ­I don't even know if it's possible Thanks - Chuck...
8
by: FS Liu | last post by:
Hi, I am writing ATL Service application (XML Web service) in VS.NET C++. Are there any sample programs that accept XML as input and XML as output in the web service? Thank you very much.
2
by: Eric Peterson | last post by:
I use the shape object to make recordsets that have no db connection for use in grids and such. For example (mPhoneNumbersRS as ADODB.Recordset) ****************************************...
14
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can...
1
by: Alex | last post by:
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to...
1
by: Screenbert | last post by:
After finding nothing anywhere in google I am posting this so everyone can benefit by it. The formating is not pretty since I copied it from my word document, but you should benefit by it. ...
0
by: screenbert | last post by:
Managing DHCP Servers using C# They said it was impossible. It couldn't be done. But you can in fact manage DHCP servers using C#. This includes creating and deleting Scopes, SuperScopes,...
8
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table,...
0
by: G.S. | last post by:
Hi all, We've encountered an issue and would appreciate some help. A SHAPE command, executed via MSDataShape-provider connection doesn't seem to be able to participate implicitly in an ongoing...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.