Connection managers - OLE DB

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

SeanDL wrote:

Hi There

Quick question.In DTS when you had multiples tasks connecting to the same Database, it was reccomended that you create multiple connections for the same database and let each task use a different connection, thereby making a new connection for each one.
In SSIS if i define 1 OLE DB connection to a database, but i have say 6 tasks using that connection that run simultaneously, does eachone open a new connection to the database to make it more efficient? Or do i still need to define multiple connections to the same database and assign each task a different connection?

Thanx

Sean,
In that situation each of the 6 tasks will use the same connection as far as I know. I guess this is why the connection managers are so called - its because they manage a single connection thereby enabling many tasks to use it.

I'm no DBA but I suspect that this is more efficient than opening multiple connections.

-Jamie|||Hi Jamie

In DTS it was definately better to use multiple connections when extracting large amounts of data from each connection.But no mention has been made of it in SSIS, i guess i could do some tests myself to see if it helps performance?

Thanx|||I'd be interested in seeing any metrics you get.

I don't claim to know much about DTS but I suspect that there are different reasons that multiple connections are recommended that outweigh the benefit of only having 1 connection to the server.
I also suspect that even if one connection object were used each task that used it would still have its own connection (I stand to be corrected here). This is not the case with SSIS.

-Jamie|||Sean, the reason connection managers are called that is to distinguish them from connections as named in DTS. The reason it was recommended to use multiple connections in DTS is because workflow would be serialized if pointing to the same connection. Connection managers actually generate a new connection everytime you call AcquireConnection so the workflow isn't serialized. Each task or transform etc. that calls AcquireConnection on the connection manager receives a new physical connection.
So, for SSIS, just use one connection manager. Also, consider using Data Sources and Data Source views for your OLEDB connections. I blogged about it a bit recently. That simplifies matters even more and makes your packages that much more portable.
Thanks,|||Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie|||Hi Kirk

Thanx for the info, that is what i thought but i just could not find any documentation to verify it.

Although Jamies issue is a bit worrying because set rowcount's scope is definately within a connection/transaction?

Thanx|||Hi Kirk

Ok i am also still confused, my concurrent connections (5 of them) using the same conenction manager we all executing in parrellel, then i changed the connection manager to use the OLE DB for SQL Server NOT the native client that iw as using), now only 3 of the 5 tasks execute concurrently when 1 finishes the next one starts.Therefore it seems only 3 concurrent conenctions are allowed, i have checked BOL , but i cannot find where this setting is as i would like all tasks to run in parallel, if you know it would be greatly appreciated.

Thanx|||Check the MaxConcurrentExecutables property of the package by clicking on the package design surface and then looking at the properties. If it's set to -1, the heuristic used to determine max executables is n+2, where n is the number of processors.
Change it to a higher number and watch them all fly together. :)
K|||Thanx Kirk

Spot on , i did see that setting but i thought -1 was unlimited.Thanx A million works like a bomb !|||

Jamie Thomson wrote:

Kirk,
Right then, I'm a wee bit confused. I'll tell you why.

I was recently working on a package where I had an Execute SQL Task followed by a data-flow which extracted from a SQL Server DB. Both tasks used the same connection manager.
In the Execute SQL Task there was a SET ROWCOUNT 20000 statement. When I ran the package, the data-flow only processed 20000 rows - obviously because it was affected by the SET ROWCOUNT command in the previous task.

Now, if both tasks had seperate connections that wouldn't happen. Or would it? I've done a quick scan of google to find out if I could find out the scope of SET ROWCOUNT but couldn't find anything (and its getting late :)

Any ideas?

-Jamie

OK, I've just built a simple mock-up to demo this and lo and behold it doesn't happen anymore. Setting rowcount in the Excute SQL Task didn't affect the dataflow.
I swear that when I did this before though it DID affect the data-flow...and I have witnesses to prove it. It occurred on April CTP whereas I am now using June CTP. perhaps that's why!!!

For now, ignore this. If it happens again I'll raise it.

Weird!!

-Jamie|||Did you have retain same connection set to true?
I don't have a good answer here. Without looking at the package, it's hard to know for sure.|||Yes, did so myself. Couldn't repro it either...

connection issues when running multiple instances of same application

I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).

The problem is that somehow these two applications are sharing something either with the connection or commands.

For instance if the two instances are in the following function at the same time then they both thow an SEH exception:

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."

And afterwords when they go to release the command an error occurs on

First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.

Code Snippet

inline void _bstr_t::Data_t::_Free() throw()

{

if (m_wstr != NULL) {

::SysFreeString(m_wstr);

}

if (m_str != NULL) {

delete [] m_str;

}

}

This is being called from.

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.

Similarly when one instance releases a command using

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The second instance of the application fails when running the command at the exact same time.

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.

All of these access issues are with pointers being passed into these functions. As such, we don't have much context with which to help you with these issues. Any issue could be causing these problems including: double freeing, not allocating memory elsewhere or failure to properly initialize memory, failure to check return codes of functions that return memory, etc. In these cases, we need to see the context surrounding the variables that are causing the AV, in for example: for Command15::Execute, RecordsAffected and Parameters would be the variables of interest, we would need to see how they are allocated, initialized, and used prior to this function call in the execution context of a failure, and how they are allocated, initialized, and used prior to this function call in the execution context of success. The most likely cause of this AV is failure to handle error conditions coming back from functions that allocate memory on success.

Thanks,

John

|||

But when I run only one instance of the application at a time it works fine, I have been running it already for a long time and never had a problem. How is it that running two seperate instances of the application could cause this error. The memory allocation would not change just because there are two instances? would it?

connection issues when running multiple instances of same application

I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).

The problem is that somehow these two applications are sharing something either with the connection or commands.

For instance if the two instances are in the following function at the same time then they both thow an SEH exception:

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."

And afterwords when they go to release the command an error occurs on

First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.

Code Snippet

inline void _bstr_t::Data_t::_Free() throw()

{

if (m_wstr != NULL) {

::SysFreeString(m_wstr);

}

if (m_str != NULL) {

delete [] m_str;

}

}

This is being called from.

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.

Similarly when one instance releases a command using

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The second instance of the application fails when running the command at the exact same time.

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.

All of these access issues are with pointers being passed into these functions. As such, we don't have much context with which to help you with these issues. Any issue could be causing these problems including: double freeing, not allocating memory elsewhere or failure to properly initialize memory, failure to check return codes of functions that return memory, etc. In these cases, we need to see the context surrounding the variables that are causing the AV, in for example: for Command15::Execute, RecordsAffected and Parameters would be the variables of interest, we would need to see how they are allocated, initialized, and used prior to this function call in the execution context of a failure, and how they are allocated, initialized, and used prior to this function call in the execution context of success. The most likely cause of this AV is failure to handle error conditions coming back from functions that allocate memory on success.

Thanks,

John

|||

But when I run only one instance of the application at a time it works fine, I have been running it already for a long time and never had a problem. How is it that running two seperate instances of the application could cause this error. The memory allocation would not change just because there are two instances? would it?

Connection Bussy.

Hello to everyone,

I have developed a package with multiple Data Flow Tasks, all of the data goes to a sql server database. This package worked perfectly well on a development machine (with win 2k3), but now I 've deployed the package on a production server (with win 2k3 too), and when I execute the package with a web service (network service user) I get the following error:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command". Source: My DataFlow TimeStamp:7/7/2007 2:18:08 PM Error: The "input "OLE DB Destination Input" (27)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (27)" specifies failure on error. An error occurred on the specified object of the specified component.

I wonder if it could be permission issue with the Network Service user or maybe SQL SERVER must have any parameter set that I'm not taking into consideration.

any clue?

regards

Sounds like a problem with SQL Server rather than SSIS. Did you try searching the web it? I did and I came up with this:

FIX: "Connection is busy with results for another command" error message occurs when you run a linked server query

(http://support.microsoft.com/default.aspx/kb/822668/)

-Jamie

|||hello, thanks for your response.

well, I was taking into consideration to install ServicePack 2, so i'm downloading it now. I guess if this could be a problem with the version of SQL SERVER, On a previous machine I was using the Enterprise Edition and everything was running well and now I'm just with the Standard one. Could it be an issue to take care of?. I'll install SP2 and I'll tell you what happened.

best regards.|||
I found the answer,

well, my package has 20 DataFlow Tasks, and when i debugged the hole package i realized that I always had 3 or 4 failing tasks with the "Connection is busy...." error message, so I decided to link them with a Completion Precedence Constraint (the blue one) and it worked fine.

I wonder if this problem occurs is because a lack connections on the Sql Server connection pool or something related.

thanks anyway, It worked for me.|||

I received the same error today and only have 4 data flows although it has been working without any issues for the last few weeks. Is there a SQL server or SSIS parameter that needs to be bumped up somewhere?

Connecting to SQL Server 2005 from Classic ASP

I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.

The ASP code looks like this:

strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = strConnect

conn.Open

Set objCommand = Server.CreateObject("ADODB.Command")

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Set objRs = Server.CreateObject("ADODB.Recordset")

With objCommand

.ActiveConnection = conn

.CommandText = "sp_GetShowList"

.CommandType = adCmdStoredProc

If Len(strBeginDate) And IsDate(strBeginDate) Then

.Parameters.Append .CreateParameter("@.i_DateStart",adDate,adParamInput,,strBeginDate)

If Len(strEndDate) And IsDate(strEndDate) Then

.Parameters.Append .CreateParameter("@.i_DateEnd",adDate,adParamInput,,strEndDate)

End If

End If

End With

Set objRs = objCommand.Execute

arrEvents = objRs.GetRows()

objRs.NextRecordset

arrSched = objRs.GetRows()

The error message below occurs at the 2nd GetRows() command.

Operation is not allowed when the object is closed.

Can anyone tell me what I might be doing wrong?

-Dan

You don't assign the second recordset returned by NextRecordset to the recordset variable

Try

Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs = objRs.NextRecordset
arrSched = objRs.GetRows()

|||

That was so obvious I feel like an idiot. Thanks for the response!

-Dan

Connecting to SQL Server 2005 Express

I am having major issues connecting to SQL Server 2005 Express from Visual Basic 2005 Express, which I have tried on multiple PCs but to no avail. Can somebody please explain to me what I'm doing wrong?

1) I install SQL Server 2005 Express and Visual Basic 2005 Express (With .NET framework and SQL native client of course)
2) I open SQL Server using Management Studio and going in with windows authentication. It automatically sets the log on name to computername\SQLEXPRESS.
3) I create a new database called Test and add one table to it
4) I go into Visual Basic 2005 and create a new project.
5) I go to the projects application settings (Properties - Settings).
6) I set up a new connection string, choosing SQL server as the conection type and then on the Connection Properties window it asks me to select the mdf file for the database. I choose the file Test.mdf from the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
7) I click on Test Connection.

When I do this on two out of the three home PCs I get the error:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf".Operating system error 32: "32 (error not found)".
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The third PC gives me a different error:

Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

I get the same errors when I try to use a connection string inside the code directly. Does anyone have a clue as to why these errors are occuring? Do I need to set up security or something else on the database first, or is there another reason? If anyone can please help me get to the bottom of this or provide a web address where I can get the answer (step by step instructions on how to connect) then I would be most grateful.

error code 32 means:
"The process cannot access the file because it is being used by another process."

So, either use a different file name for your connection in Visual Basic 2005, or try disconnect your connection in SQL Server using Management Studio.

|||

pse post the Connection string... it seems like u r trying to attach a db called Test....

in the second error .. check what is the Default Database given for user DAVE\David.... the default database should exists and this user should be a user of that db.... for debuggin purpose ... just change the default database of this user to Master and try... it will connect...

Madhu

|||

Hi, I get the same error as the original poster of this thread. I have no other database with similar name or anything, and i have not, as far as i know, any other connections to this database, since I just created it. Do you have any idea of how I should go about this?

Thanks

Niclas

Connecting to SQL Server 2005 Express

I am having major issues connecting to SQL Server 2005 Express from Visual Basic 2005 Express, which I have tried on multiple PCs but to no avail. Can somebody please explain to me what I'm doing wrong?

1) I install SQL Server 2005 Express and Visual Basic 2005 Express (With .NET framework and SQL native client of course)
2) I open SQL Server using Management Studio and going in with windows authentication. It automatically sets the log on name to computername\SQLEXPRESS.
3) I create a new database called Test and add one table to it
4) I go into Visual Basic 2005 and create a new project.
5) I go to the projects application settings (Properties - Settings).
6) I set up a new connection string, choosing SQL server as the conection type and then on the Connection Properties window it asks me to select the mdf file for the database. I choose the file Test.mdf from the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
7) I click on Test Connection.

When I do this on two out of the three home PCs I get the error:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf".Operating system error 32: "32 (error not found)".
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The third PC gives me a different error:

Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

I get the same errors when I try to use a connection string inside the code directly. Does anyone have a clue as to why these errors are occuring? Do I need to set up security or something else on the database first, or is there another reason? If anyone can please help me get to the bottom of this or provide a web address where I can get the answer (step by step instructions on how to connect) then I would be most grateful.

error code 32 means:
"The process cannot access the file because it is being used by another process."

So, either use a different file name for your connection in Visual Basic 2005, or try disconnect your connection in SQL Server using Management Studio.

|||

pse post the Connection string... it seems like u r trying to attach a db called Test....

in the second error .. check what is the Default Database given for user DAVE\David.... the default database should exists and this user should be a user of that db.... for debuggin purpose ... just change the default database of this user to Master and try... it will connect...

Madhu

|||

Hi, I get the same error as the original poster of this thread. I have no other database with similar name or anything, and i have not, as far as i know, any other connections to this database, since I just created it. Do you have any idea of how I should go about this?

Thanks

Niclas