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 a database

Hi,
I am trying to find out how to connect to a database, inside a stored
procedure...including identification (Username and passsword)?
I tried the CONNECT TO statement, but when I try to use it, I get the error:
[ODBC SQL Server Driver]Syntax Error or Access violation
I am using the connect to statemente this way:
Connect to {pc12.DB} User us.abc
DB= Databasename;
pc12= Servername;
us= Username;
abc= password
Can somebody tell me what is wrong with the way I use the connect statement
or tell me a better way to connect to databases?
ThanksCONNECT TO is IIRC only supported in Embedded SQL for C, not in stored
procedures and T-SQL.
If you are trying to connect to a database on the same server, you can just
access it by prefixing the tables etc with the database name and the owner
name in the form of:
SELECT some_column FROM other_database.dbo.some_table
If the database is on a different server, you can set up a linked server
(see BOL), and than add the server name to the select:
SELECT some_column FROM other_server.other_database.dbo.some_table
Jacco Schalkwijk
SQL Server MVP
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:eN6LxuKVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am trying to find out how to connect to a database, inside a stored
> procedure...including identification (Username and passsword)?
> I tried the CONNECT TO statement, but when I try to use it, I get the
> error:
> [ODBC SQL Server Driver]Syntax Error or Access violation
> I am using the connect to statemente this way:
> Connect to {pc12.DB} User us.abc
> DB= Databasename;
> pc12= Servername;
> us= Username;
> abc= password
> Can somebody tell me what is wrong with the way I use the connect
> statement or tell me a better way to connect to databases?
> Thanks
>

Connection to a database

Hi,
I am trying to find out how to connect to a database, inside a stored
procedure...including identification (Username and passsword)?
I tried the CONNECT TO statement, but when I try to use it, I get the error:
[ODBC SQL Server Driver]Syntax Error or Access violation
I am using the connect to statemente this way:
Connect to {pc12.DB} User us.abc
DB= Databasename;
pc12= Servername;
us= Username;
abc= password
Can somebody tell me what is wrong with the way I use the connect statement
or tell me a better way to connect to databases?
Thanks
CONNECT TO is IIRC only supported in Embedded SQL for C, not in stored
procedures and T-SQL.
If you are trying to connect to a database on the same server, you can just
access it by prefixing the tables etc with the database name and the owner
name in the form of:
SELECT some_column FROM other_database.dbo.some_table
If the database is on a different server, you can set up a linked server
(see BOL), and than add the server name to the select:
SELECT some_column FROM other_server.other_database.dbo.some_table
Jacco Schalkwijk
SQL Server MVP
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:eN6LxuKVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am trying to find out how to connect to a database, inside a stored
> procedure...including identification (Username and passsword)?
> I tried the CONNECT TO statement, but when I try to use it, I get the
> error:
> [ODBC SQL Server Driver]Syntax Error or Access violation
> I am using the connect to statemente this way:
> Connect to {pc12.DB} User us.abc
> DB= Databasename;
> pc12= Servername;
> us= Username;
> abc= password
> Can somebody tell me what is wrong with the way I use the connect
> statement or tell me a better way to connect to databases?
> Thanks
>

Connection string for big uploading files

I have a webpage where I want to upload pdf files to my database (arround 2 MB). Putting them inside the database is the choice based on audit definitions the client has.

The problem is that for big file uploads, I get the "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."error.

I believe the solution is expanding the connection timeout time in the SQL connection. But I don't want to do that in web.config, as a small timeout is good for every page except this one.

What I was thinking about was something like, on button command:

- "create new connection string"

- use that connection string to upload the file using the datatables and tableadapters

- turn back to the default connection string.

How do I explicitly tell the system to "use THIS connection string on the next task" ?

Alternatively, another way of doing is also appreciated!

Doesn't sound like a database timeout. However, if it were, then you control the command timeout on the sqlcommand object, not the sqlconnection. It has nothing to do with the connection string.|||

How do you access the sqlcommand object? Because I do not explicitly declare any sqlcommand, open, close, etc; I declare a TableAdapter object, a DataTable Object and then access the query method of that datatable, so basically two lines to execute the query (that is actually in a tableadapter in app_code). How can one change the command timeout using this method?

|||No idea, I never use table adapters. IMHO they suck, and serve no real purpose other than to try and abstract out something that isn't that hard to begin with. If you want something done right...|||

In the Solution Explorer, go toyourDataSet.xsd->openyourDataSet.Designer.cs, then locate the TableAdapter class, you'll see it has a member like:

private System.Data.SqlClient.SqlCommand[] _commandCollection;

Then in some event (may be InitCommandCollection) you can set the property of the SqlCommands, for example:

private void InitCommandCollection() {
this._commandCollection = new System.Data.SqlClient.SqlCommand[1];
this._commandCollection[0] = new System.Data.SqlClient.SqlCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate," +
" ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, \r\n " +
" ShipPostalCode, ShipCountry\r\nFROM Orders";
this._commandCollection[0].CommandType = System.Data.CommandType.Text;
this._commandCollection[0].CommandTimeout = 6000;
}

Connection problems to SQL2005 with odbc using port 1733

Hi,
I'm trying to connect to a SQL2005 server inside a firewall from another
Windows2003 server that is placed in DMZ. Both servers is hosted by our ISP
but they can't seem to find the problem.
Because the sqlserver is used by many companies we have to communicate with
the sql server on port 1733 and not default port 1433. Therefore I have made
an alias for the connection that I have called sql2005 that is set up to use
port 1733.
After configuring the odbc connection everything works, but after a while
(eg. if I restart my own computer) I reconnect to the server and I get "Sql
server does not excist or access denied". If I then enters the client
configuration and spesify port 1733 again, it works. I looks like the odbc
connection doesn't "remember" the port that is spesified.
Anybody know how to specify a custom port that the odbc connection can
remember?
Or how to specify port in a connectionstring for a asp web application?
In advance thanks a lot for your help!!
Kind regards,
Jon Haakon Ariansen
Does something like "Datasource=127.0.0.1,1733" work for you?
"Jon Haakon Ariansen" <jona@.spama.no> wrote in message
news:%23$tfZHJ3GHA.2196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I'm trying to connect to a SQL2005 server inside a firewall from another
> Windows2003 server that is placed in DMZ. Both servers is hosted by our
> ISP but they can't seem to find the problem.
> Because the sqlserver is used by many companies we have to communicate
> with the sql server on port 1733 and not default port 1433. Therefore I
> have made an alias for the connection that I have called sql2005 that is
> set up to use port 1733.
> After configuring the odbc connection everything works, but after a while
> (eg. if I restart my own computer) I reconnect to the server and I get
> "Sql server does not excist or access denied". If I then enters the client
> configuration and spesify port 1733 again, it works. I looks like the odbc
> connection doesn't "remember" the port that is spesified.
> Anybody know how to specify a custom port that the odbc connection can
> remember?
> Or how to specify port in a connectionstring for a asp web application?
> In advance thanks a lot for your help!!
> Kind regards,
> Jon Haakon Ariansen
>

Connection problems to SQL2005 with odbc using port 1733

Hi,
I'm trying to connect to a SQL2005 server inside a firewall from another
Windows2003 server that is placed in DMZ. Both servers is hosted by our ISP
but they can't seem to find the problem.
Because the sqlserver is used by many companies we have to communicate with
the sql server on port 1733 and not default port 1433. Therefore I have made
an alias for the connection that I have called sql2005 that is set up to use
port 1733.
After configuring the odbc connection everything works, but after a while
(eg. if I restart my own computer) I reconnect to the server and I get "Sql
server does not excist or access denied". If I then enters the client
configuration and spesify port 1733 again, it works. I looks like the odbc
connection doesn't "remember" the port that is spesified.
Anybody know how to specify a custom port that the odbc connection can
remember?
Or how to specify port in a connectionstring for a asp web application'
In advance thanks a lot for your help!!
Kind regards,
Jon Haakon AriansenDoes something like "Datasource=127.0.0.1,1733" work for you?
"Jon Haakon Ariansen" <jona@.spama.no> wrote in message
news:%23$tfZHJ3GHA.2196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I'm trying to connect to a SQL2005 server inside a firewall from another
> Windows2003 server that is placed in DMZ. Both servers is hosted by our
> ISP but they can't seem to find the problem.
> Because the sqlserver is used by many companies we have to communicate
> with the sql server on port 1733 and not default port 1433. Therefore I
> have made an alias for the connection that I have called sql2005 that is
> set up to use port 1733.
> After configuring the odbc connection everything works, but after a while
> (eg. if I restart my own computer) I reconnect to the server and I get
> "Sql server does not excist or access denied". If I then enters the client
> configuration and spesify port 1733 again, it works. I looks like the odbc
> connection doesn't "remember" the port that is spesified.
> Anybody know how to specify a custom port that the odbc connection can
> remember?
> Or how to specify port in a connectionstring for a asp web application'
> In advance thanks a lot for your help!!
> Kind regards,
> Jon Haakon Ariansen
>