one ado connection object - multiple spids?

Hi,
We have asp application (ado connection) which connects to the sql server
2000 (sp3). There is one asp page which sequentially executes set of 6
stored procedures. When I execute same set of stored procedures in query
analyzer, I get response in less than 1 second.When application does that,
response gets back in 15-20 seconds (only when we have problem, otherwise in
2-3 seconds). In the trace, I noticed that each of these 6 stored procedures
(which btw use 1 connection object) gets a different SPID. Why is that?
Shouldn't they use the same SPID, if they use the same connection object
(executed sequentially!)?
Also, the trace shows that each of them gets executed almost instantly
(1ms), but between end of previous, and beginning of the next one, there is
delay of 2-3 seconds. During these 2-3 seconds, there is nothing going on on
the database server (very few events).
Does anybody have an idea?
ThanksPedja wrote:
> Hi,
> We have asp application (ado connection) which connects to the sql
> server 2000 (sp3). There is one asp page which sequentially executes
> set of 6 stored procedures. When I execute same set of stored
> procedures in query analyzer, I get response in less than 1
> second.When application does that, response gets back in 15-20
> seconds (only when we have problem, otherwise in 2-3 seconds). In the
> trace, I noticed that each of these 6 stored procedures (which btw
> use 1 connection object) gets a different SPID. Why is that?
> Shouldn't they use the same SPID, if they use the same connection
> object (executed sequentially!)? Also, the trace shows that each of them
> gets executed almost instantly
> (1ms), but between end of previous, and beginning of the next one,
> there is delay of 2-3 seconds. During these 2-3 seconds, there is
> nothing going on on the database server (very few events).
> Does anybody have an idea?
> Thanks
Are you sure you are not closing the connection and opening it up each time
your ASP code executed some SQL?
David Gugick
Quest Softwaresql

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.

Oledb Provider having problem in MS SQL Server 2005

Hi,

I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message
"Invalid object name 'UserGroup'. "

I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.

'Online Connection String - Working
<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>

'Local Connection String - Not working
(SQL Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>
OR
(Windows Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/
Is the Oledb provider having problem in MS SQL Server 2005?

Any help would be more appreciate.

Calvin

First thing I would try it to make sure you do have a table named UserGroup, and also try prefixing with with the owner.

Next, you should be using the SQL provider and not the OLEDB providers for SQL Server.

|||Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug. Is there any way to solve this problem? And I don't understand why I use my "Online" connection string(bottom) without error? Even the provider isSQLOLEDB.1?

<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxxxx;Connect Timeout=3600"/>

The difference to my local connection is just the "UID", "Password" and Data Source only. I'm sure that the login account for local is created perfectly. The host and my computer also using MS SQL Server 2005.

|||

EDUStreet:

Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug.

OLEDB provider is meant to be used for older data sources. For SQL Server 7.0+ you should really be using the SQL Providers, that is what it is there for. In terms of coding changes there should not be too many apart from renaming all the declarations and instantiations from Oledb to Sql.

One of the best ways to make sure you got the right connection string is to drag and drop something like a gridview or sqldatasource and go through the wizard to connect to your datasouce and use the auto generated connection string

Here is a web site that has all connection string formats

http://www.connectionstrings.com/?carrier=sqlserver2005

|||

Finally, I change all the code which is from OLEDB provider to SQL provider

This is the connection string i'm using now.

<addkey="OLEDB"value="User ID=xxxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx"/>

|||

EDUStreet:

"The ConnectionString property has not been initialized. "

When you create the SqlConnection object you need to supply the connection string. Please ensure you have done this.

|||

Thanks Jimmy :)

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLE Object extraction

Hi,

I have virtually no ms access skills as am an Oracle dba.

I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.

When I open the table the field reads "Long Binary Data"

Many thanks

RobertHi,

I have virtually no ms access skills as am an Oracle dba.

I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.

When I open the table the field reads "Long Binary Data"

Many thanks

Robert
Import to what ? Oracle or Sql server 2000 ?|||sorry, I just want to export the files into the file system|||sorry, I just want to export the files into the file system

We generally do it using BLOB..
Check this link
Link (http://www.vbwm.com/forums/topic.asp?TOPIC_ID=4073)
Hope it will help you.

OLE Automation and "global variable"

I have an OLE automation object that I am instantiating inside of a stored
proc using
sp_OACreate 'myObject', @.object OUT.
The stored proc is called multiple times, each time instantiating a new
instance of the object (and then destroying it when it is finished).
Is it possible in SQL Server to create a global variable to refer to the
OLE automation object?
IOW can I instantiate the object outside the proc, assign the @.object
pointer to a global variable and then refererence this global variable
inside my stored proc so I don't have to create the object every time it is
called?
Perhaps a more basic question is: is it possible to create global variables
in T-SQL and if so, how?
Thanks
DaveAs far as I know, OLE Automation objects in SQL Server can only exist inside
the scope where they were created.
There are also no global variables in SQL, but you could create a temporary
(or permanent) table to store values that can be accessed "globally".
I strongly recommend you destroy all objects after use, even those
implicitly created by the methods used. But then again you mentioned that yo
u
already do so. :)
SQL 2005 also supports (and controls) CLR, maybe that can provide you with
more functionality.
ML
http://milambda.blogspot.com/

Old MDAC changes, 2.1 - 2.5

We have a large project that is importing msado21.tlb. We want to import least msado25.tlb to access the ADODB::_Stream object, but are unsure what exactly this entails. I've noticed no problems testing that change, but I cannot find any changelog for the MDAC upgrade. Is anyone aware of, or can point me to, a list of changes (fixes, modifications, caveats, etc) between MDAC 2.1 and MDAC 2.5? The binary msado file is always much newer, of course, but we want to link with the older type libraries to maintain maximum compatibility.

Any help is appreciated, I'm at a dead end!

Some of the MDAC 2.5 changes are discussed here: http://msdn2.microsoft.com/en-us/library/ms810823.aspx#mda_comp

You can look at the readme for 2.5 RTM which discusses the changes for that version.
See the download for MDAC 2.5 and find MDAC readme html file in the package. http://www.microsoft.com/downloads/details.aspx?FamilyID=83e8f178-94c9-4e7d-b0b6-a8a94c4eb912&DisplayLang=en