ConnectionManagerOleDb usage in a Script Component

Hi,

I have a need to use a shared connection manager of type OLEDB inside a Script Component. I guess that the right way to get the OLEDB connection is to override the AcquireConnections method, case the shared connection manager to the ConnectionManagerOleDb interface and call the AcquireConnection method.

The problem is in the return value of the AcquireConnection method (it’s Object). My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface. Does some of you tried to cast it to some interface/class exposed by DTS (or some standard COM interface from MDAC)?

PS I’m aware about the possibility to use ADO.NET connection managers, however it’s not a case for me because of DataReader Source limitations (OLE DB Source suits my needs much better).

I would really appreciate your answer. Thanks in advance!

Regards,

Dmytro

Hi,

I am not using override acquireconnection method for sharing connections. Instead of that I have created a global connection and created a property for opening the connection. Inside the property, if it is null I will open a new connection, otherwise I will return the existing connection. I have overriden the 'releaseconnection' method, to release that connection. It is working fine. Hope this would help u.

|||

Hi,

I am facing similar situation. Have you got a solution. It will be great if you can share the way you resolved it.

Thanks

|||

Hi,

Thanks for your reply! What is the type of the global connection that you created and what would happen if your package is executed in a transaction? Do you take transactions into consideration?

Thanks!

/Dmytro

|||

Hi,

Unfortunately I haven't found a way how to solve this issue yet. If I find it, I would definitely post a solution here.

/Dmytro

|||

Dmytro Kryvko wrote:

I have a need to use a shared connection manager of type OLEDB inside a Script Component.
...
My assumption is that in case of OLEDB connection usage, the AcquireConnection method returns some OLE DB COM interface.

This is correct, the object is the OLEDB COM object, namely OLEDB session created by IDBCreateSession::CreateSession call. For using it, see OLEDB reference in MSDN.

Note that these interfaces are not meant to be used from .NET. If you really need to use it, you should create a custom component in C++, rather than using script component.

connection to Analysis server failed

May you help for beginner.
I have installed MS SQL server 2005 to local computer. I can not connect to
Server type "Analysis Services" from Management studio.
Message "A connection cannot be made. Ensure that the server is running.
(Microsoft.AnalysisServices.AdomdClient)" appears.
But I can connect to the following Server types: Database Engine
Reporting service
Integration service
I have reinstalled application, but same behaviour.
Please, how I can ensure that AnalysisServices server is running?
Thank you for your help.
Zdenek
You can use the SQL Server Configuration Manager to check
the service.
Open up SQL Server Configuration Manager and on the left,
select SQL Server 2005 Services. On the right, check SQL
Server Analysis Services. You can start it from here if it's
not running and you can configure how, when you want the
service to start (manual, automatic, etc)
-Sue
On Sat, 17 Mar 2007 04:21:03 -0700, Zdenek
<Zdenek@.discussions.microsoft.com> wrote:

>May you help for beginner.
>I have installed MS SQL server 2005 to local computer. I can not connect to
>Server type "Analysis Services" from Management studio.
>Message "A connection cannot be made. Ensure that the server is running.
>(Microsoft.AnalysisServices.AdomdClient)" appears.
>But I can connect to the following Server types: Database Engine
>Reporting service
>Integration service
>I have reinstalled application, but same behaviour.
>Please, how I can ensure that AnalysisServices server is running?
>Thank you for your help.
>Zdenek

connection to Analysis server failed

May you help for beginner.
I have installed MS SQL server 2005 to local computer. I can not connect to
Server type "Analysis Services" from Management studio.
Message "A connection cannot be made. Ensure that the server is running.
(Microsoft.AnalysisServices.AdomdClient)" appears.
But I can connect to the following Server types: Database Engine
Reporting service
Integration service
I have reinstalled application, but same behaviour.
Please, how I can ensure that AnalysisServices server is running?
Thank you for your help.
ZdenekYou can use the SQL Server Configuration Manager to check
the service.
Open up SQL Server Configuration Manager and on the left,
select SQL Server 2005 Services. On the right, check SQL
Server Analysis Services. You can start it from here if it's
not running and you can configure how, when you want the
service to start (manual, automatic, etc)
-Sue
On Sat, 17 Mar 2007 04:21:03 -0700, Zdenek
<Zdenek@.discussions.microsoft.com> wrote:

>May you help for beginner.
>I have installed MS SQL server 2005 to local computer. I can not connect to
>Server type "Analysis Services" from Management studio.
>Message "A connection cannot be made. Ensure that the server is running.
>(Microsoft.AnalysisServices.AdomdClient)" appears.
>But I can connect to the following Server types: Database Engine
>Reporting service
>Integration service
>I have reinstalled application, but same behaviour.
>Please, how I can ensure that AnalysisServices server is running?
>Thank you for your help.
>Zdenek

Connection string problem

Hi people,
I have my web application running on my machine and it works on the current machine where I have created the project when I type localhost on the web browser. I tried running the application from another machine within the same network by typing the IP address of the machine where my asp.net project is located, the application works, but SQL Server wouldn't allow connection to be made when I start calling data from the database. I have set Impersonate="true" in the web.config file. Am I doing something wrong here?
Here's the configuration on the web.config file

<identityimpersonate="true"/>

<appSettings>

<addkey="Connection"

value="Server=SQLOLEDB.1;Data Source=SQLServer;Initial Catalog=myDatabase;Trusted_Connection=True;"/>

</appSettings>

Please help....
Kero

When you do that you impersonate IUSR_COMPUTERNAME; so, in that scenario that account must have rights to the database.
I think you may have to use Sql Server authentication and pass a userid/pwd pair in your conn string.
|||Thanks ChicoUser,
I have sort out my impersonate problem by following one of the tutorials from microsoft website. I have set up a Customer user instead using Aspnet account on the machine and given appropriate permissions to access the files. In the Web.config file I have added impersonate =true and provided username and password which allows the created account to get access to my SQL SERVER. It's working now.
kero :)

Connection String for "local" connection

I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
using a "localhost" type of connection would be faster than referring to the
server name and thus invoking the DNS lookup. The connection string I am
using as an ASP include is:
Dim Connect
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.open "DRIVER={SQL
Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
Thanks
Something even better would be to use OLEDB instead of ODBC. You can also
use (local) (with the parenthesis) instead of localhost to use named pipes
instead of TCP/IP but I'm not really sure is this will impact the
performance.
Replace localhost with 127.0.0.1 if you want to make really sure that the
DNS lookup has been shortcutted.
Finally, be default, the connections are pooled, so you shouldn't see any
improvement about shortcutting the DNS lookup.
S. L.
"MICHAEL SHIFFMAN" <mettaworks@.verizon.net> wrote in message
news:rSfAd.7050$1U6.2738@.trnddc09...
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string I
> am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
> Thanks
>
|||MICHAEL SHIFFMAN (mettaworks@.verizon.net) writes:
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string
> I am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
To echo what Sylvain said: change DRIVER=(SQL Server) to Provider=SQLOLEDB.
Changing providers is not entirely painless - some things may broke. But
the default OLEDB-over-ODBC driver is deprecated for SQL Server, and since
it means one extra layer, there is definitely an extra overhead.
The gain in performance you may get by chaning the SERVER parameter is
probably neglible. However, as long as you run SQL Server on the web
server - which is dubious practice - you gain some maintainability by
dropping SERVER altogther. If the server changes name, you don't have
to change the connection string. Then again, it is not unlikely that you
move the SQL Server to a separate box one day - in which case your
question becomes moot.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Connection String for "local" connection

I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
using a "localhost" type of connection would be faster than referring to the
server name and thus invoking the DNS lookup. The connection string I am
using as an ASP include is:
Dim Connect
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.open "DRIVER={SQL
Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
ThanksSomething even better would be to use OLEDB instead of ODBC. You can also
use (local) (with the parenthesis) instead of localhost to use named pipes
instead of TCP/IP but I'm not really sure is this will impact the
performance.
Replace localhost with 127.0.0.1 if you want to make really sure that the
DNS lookup has been shortcutted.
Finally, be default, the connections are pooled, so you shouldn't see any
improvement about shortcutting the DNS lookup.
S. L.
"MICHAEL SHIFFMAN" <mettaworks@.verizon.net> wrote in message
news:rSfAd.7050$1U6.2738@.trnddc09...
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string I
> am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
> Thanks
>|||MICHAEL SHIFFMAN (mettaworks@.verizon.net) writes:
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string
> I am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
To echo what Sylvain said: change DRIVER=(SQL Server) to Provider=SQLOLEDB.
Changing providers is not entirely painless - some things may broke. But
the default OLEDB-over-ODBC driver is deprecated for SQL Server, and since
it means one extra layer, there is definitely an extra overhead.
The gain in performance you may get by chaning the SERVER parameter is
probably neglible. However, as long as you run SQL Server on the web
server - which is dubious practice - you gain some maintainability by
dropping SERVER altogther. If the server changes name, you don't have
to change the connection string. Then again, it is not unlikely that you
move the SQL Server to a separate box one day - in which case your
question becomes moot.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp