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

OleDB Connection to Excel

Hi,

My application connects to excel files.
I am using this code:

dc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=YES;\"");

It works fine on Excel 2000 on all computers. Also it works on some computers with Excel 2002/XP, but on some computers with Excel 2002/XP it doesn't work.

How can I solve it?

Thank's
AlexeiHi,
I used above code sample for my program. but i did not worked.can u help me. I want to do ,take excel information to one record set or data set.I can't understand how to do it.I send u my connection coding.please check it send me wht is the correct syntax or correct code and tell me wht is the componts want. this program using the .net
following has my codes:

MyConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=D:\Oven Sheet (Track)\Data For Symix_23082005-Tracks.xls;Extended Properties=\Excel 8.0")

|||Are you sure you have the right version of Jet on the machine? Check this by right clicking on the desktop and create a new text file, call it demo.udl. This is a datalink file. If you double click on the file it should show you a list of OLE DB Providers, check that Jet 4.0 is installed.

If it is installed then try changing \Excel 8.0 to plain Excel 8.0

OLE DB Command not updating

I have a data flow task, and in that is an OLE DB Source that upon success, connects to an OLE DB Command. The OLE DB Source runs a sql command from a variable. (I've tested the sql; it parses and returns values.) The OLE DB Command is a very simple update sql statement with one column mapping. When I execute the task, it says it is successful, but when I check the table, nothing has been updated. I have ensured the connection is successful, and I am connecting to the correct db. Any suggestions would be helpful! Thanks!

Use SQL Profiler to capture the SQL statements that are being sent to the database. Odds are there is something related to the parameter mapping that is preventing the command from doing what you need it to.

Another thing to check is to ensure that the commands are being executed against the database that you think they are. If you have multiple connection managers, this can be easy to do.

|||

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

|||Is the variables EvaluateAsExpression property set to true? This would cause that behavior.|||

Lindsay wrote:

I found the problem. The variable used in the OLE DB source I thought I had changed to call a new stored procedure. However, its value was not actually changing and it was calling the wrong procedure, returning no values. My next question is why when I change the value in the properties window, does the variable not take the change?

Did SQL Profiler end up being useful?

|||

Phil, yes the EvaluateAsExpression property was set to True; how embarassing LOL. Thank you!!

|||

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

|||

Lindsay wrote:

Matthew, I've never used SQL Profiler before today, and am not familiar with it. I must need to modify the trace, because it has been running for a few hours now (actually I totally forgot about it)!!

Oh no! It's probably best to just stop the trace at this point.

I'm sorry - I didn't mean to make things more complex. If you start the trace right before you run the package, and then stop it right after the package completes, you can then look through a (relatively) small set of queries sent to the server. In t his context, one of them should leap out because it the same query repeating over and over again. There are many ways to filter the data before and after it's recorded, but this is often the quickest and easiest way to see what the client application (in this case, SSIS) is REALLY sending to the server.