Getting Connectionstring

Getting Connectionstring

Post by Michael Wals » Sun, 30 Mar 2003 09:14:02



Im using an activex script in dts to do a bulkload.
Instead of me having a connectionstring like
objXBulkLoad.ConnectionString =
PROVIDER=SQLOLEDB.1;SERVER=.;UID=uid;PWD=pwd;DATABASE=Books;"

I came across DTSGlobalVariables.Parent.Connections("ConnN")
If I created a connection called ConnN and used windows security and
pointed it at the books database,
could I use some dts method to create the needed connectionstring using
that?

Thanks in advance,
Michael

 
 
 

Getting Connectionstring

Post by Darren Gree » Tue, 01 Apr 2003 16:05:13


There is no "DTS method", but you can still build a trusted connection
string from a DTS connection object in script. This function returns the
connection string, from the DTS connection passed, e.g.

Function Main()
.....

    sConnstring =
GetConnectionString(DTSGlobalVariables.Parent.Connections("ConnN"))
.....
End Function

Function GetConnectionString(ByVal oConn)
 If oConn.UseTrustedConnection Then
  GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;" & _
   "Initial Catalog=" & oConn.Catalog & ";Data Source=" & oConn.DataSource &
";"
 Else
  GetConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" &
_
   "Initial Catalog=" & oConn.Catalog & ";Data Source=" & oConn.DataSource &
";User ID=" & oConn.UserID & ";"

  '***' Err.Raise vbObjectError + 10, "GetConnectionString", "Connection """
& oConn.Name & """ does not use trusted security"
 End If
End Function

--
Darren Green
http://www.sqldts.com


Quote:> Im using an activex script in dts to do a bulkload.
> Instead of me having a connectionstring like
> objXBulkLoad.ConnectionString =
> PROVIDER=SQLOLEDB.1;SERVER=.;UID=uid;PWD=pwd;DATABASE=Books;"

> I came across DTSGlobalVariables.Parent.Connections("ConnN")
> If I created a connection called ConnN and used windows security and
> pointed it at the books database,
> could I use some dts method to create the needed connectionstring using
> that?

> Thanks in advance,
> Michael


 
 
 

1. ODBC connectionstring

This is probably a really stupid question, but here goes:

Normally, when I connect to an Sql-server database(from an ASP script), it
is usually done this way:

conn.open("DSN=XXX;PWD=XXX;UID=XXX")

This means that the webserver that i am running the code on, has to have a
ODBC connection to the database.  This is not always possible/desirable.When
using an Access database, you can use the following statement:

conn.open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\nmfk.mdb"))
This way you don't have to define an ODBC connection on the webserver.

Is it possible to write an connection string for MS Sql server that acts the
same way?

Regards,
Erling

2. Joining a tables in SQL Server and Oracle

3. Connectionstring for TCP/IP

4. Dynamic ADO Recordset Not Dynamic (Does Not Refresh Data)

5. Error in connectionstring

6. Export 7.3 problem

7. SqlConnection.ConnectionString Please helppppppppppppppppp :(

8. Another never seen message

9. Cannot Use ConnectionString w/ OpenRowset

10. ADO Connectionstring does a tricky!

11. ConnectionString

12. Change de default connectionstring at startup

13. Connectionstring problem with SQL6.5 and ADO-OLEDB ?