Connecting to SQL Server 2005

Hello!
I was wondering if I need to install something on the client in order to
establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have ADO
2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL Server
2005?
Thanks,
IgorHi
It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but you
will not be able to use the new datatypes that SQL Server 2005 has
introduced.
The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
function of the Operating System to update them.
The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
"Provider=SQLNCLI"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:ulvm%23pcCGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hello!
> I was wondering if I need to install something on the client in order
> to establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have
> ADO 2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL
> Server 2005?
> Thanks,
> Igor
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi
> It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but
> you will not be able to use the new datatypes that SQL Server 2005 has
> introduced.
> The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
> function of the Operating System to update them.
> The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
> "Provider=SQLNCLI"
>
This is not quite right. MDAC is part of the operating system, but SQL
Native Client is not.
The SQL Native Client is a seperate install. It is included in the SQL
Server distribution and available here:
http://msdn.microsoft.com/data/sqlnative/default.aspx
There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
(deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
(SQLNCLI). SQLNCLI is the best, but you must install it.
SQL Native Client also has a new ODBC driver.
David|||David,
I am able to connect to SQL Server 2005 using SQL Native Client but
I got en error 'Server unavailable or access denied ...' when trying to
connect using Microsoft OLE DB provider for SQL Server. I am trying to
connect by setting up properties of UDL file on the same box where SQL
Server is installed. Did I get it right that I have to install latest
drivers on client computers from the link your provided?
Thanks,
Igor
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
> This is not quite right. MDAC is part of the operating system, but SQL
> Native Client is not.
>
> The SQL Native Client is a seperate install. It is included in the SQL
> Server distribution and available here:
> http://msdn.microsoft.com/data/sqlnative/default.aspx
> There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
> (deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
> (SQLNCLI). SQLNCLI is the best, but you must install it.
> SQL Native Client also has a new ODBC driver.
> David
>|||David,
I wa able to connect by enabling remote connections using Surface Area.
Thanks,
Igor
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23t2FLdxCGHA.724@.TK2MSFTNGP10.phx.gbl...
> David,
> I am able to connect to SQL Server 2005 using SQL Native Client but
> I got en error 'Server unavailable or access denied ...' when trying to
> connect using Microsoft OLE DB provider for SQL Server. I am trying to
> connect by setting up properties of UDL file on the same box where SQL
> Server is installed. Did I get it right that I have to install latest
> drivers on client computers from the link your provided?
> Thanks,
> Igor
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
>

Connecting to SQL Server 2005

Hello!
I was wondering if I need to install something on the client in order to
establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have ADO
2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL Server
2005?
Thanks,
Igor
Hi
It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but you
will not be able to use the new datatypes that SQL Server 2005 has
introduced.
The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
function of the Operating System to update them.
The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
"Provider=SQLNCLI"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:ulvm%23pcCGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hello!
> I was wondering if I need to install something on the client in order
> to establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have
> ADO 2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL
> Server 2005?
> Thanks,
> Igor
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi
> It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but
> you will not be able to use the new datatypes that SQL Server 2005 has
> introduced.
> The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
> function of the Operating System to update them.
> The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
> "Provider=SQLNCLI"
>
This is not quite right. MDAC is part of the operating system, but SQL
Native Client is not.
The SQL Native Client is a seperate install. It is included in the SQL
Server distribution and available here:
http://msdn.microsoft.com/data/sqlnative/default.aspx
There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
(deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
(SQLNCLI). SQLNCLI is the best, but you must install it.
SQL Native Client also has a new ODBC driver.
David
|||David,
I am able to connect to SQL Server 2005 using SQL Native Client but
I got en error 'Server unavailable or access denied ...' when trying to
connect using Microsoft OLE DB provider for SQL Server. I am trying to
connect by setting up properties of UDL file on the same box where SQL
Server is installed. Did I get it right that I have to install latest
drivers on client computers from the link your provided?
Thanks,
Igor
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
> This is not quite right. MDAC is part of the operating system, but SQL
> Native Client is not.
>
> The SQL Native Client is a seperate install. It is included in the SQL
> Server distribution and available here:
> http://msdn.microsoft.com/data/sqlnative/default.aspx
> There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
> (deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
> (SQLNCLI). SQLNCLI is the best, but you must install it.
> SQL Native Client also has a new ODBC driver.
> David
>
|||David,
I wa able to connect by enabling remote connections using Surface Area.
Thanks,
Igor
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23t2FLdxCGHA.724@.TK2MSFTNGP10.phx.gbl...
> David,
> I am able to connect to SQL Server 2005 using SQL Native Client but
> I got en error 'Server unavailable or access denied ...' when trying to
> connect using Microsoft OLE DB provider for SQL Server. I am trying to
> connect by setting up properties of UDL file on the same box where SQL
> Server is installed. Did I get it right that I have to install latest
> drivers on client computers from the link your provided?
> Thanks,
> Igor
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
>

Connecting to SQL Server 2005

Hello!
I was wondering if I need to install something on the client in order to
establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have ADO
2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL Server
2005?
Thanks,
IgorHi
It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but you
will not be able to use the new datatypes that SQL Server 2005 has
introduced.
The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
function of the Operating System to update them.
The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
"Provider=SQLNCLI"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:ulvm%23pcCGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hello!
> I was wondering if I need to install something on the client in order
> to establish ODBC/OLEDB connection to SQL Server 2005. Currently, I have
> ADO 2.6 installed. Are there any changes in ODBC/OLEDB drivers for SQL
> Server 2005?
> Thanks,
> Igor
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi
> It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but
> you will not be able to use the new datatypes that SQL Server 2005 has
> introduced.
> The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
> function of the Operating System to update them.
> The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
> "Provider=SQLNCLI"
>
This is not quite right. MDAC is part of the operating system, but SQL
Native Client is not.
The SQL Native Client is a seperate install. It is included in the SQL
Server distribution and available here:
http://msdn.microsoft.com/data/sqlnative/default.aspx
There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
(deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
(SQLNCLI). SQLNCLI is the best, but you must install it.
SQL Native Client also has a new ODBC driver.
David|||David,
I am able to connect to SQL Server 2005 using SQL Native Client but
I got en error 'Server unavailable or access denied ...' when trying to
connect using Microsoft OLE DB provider for SQL Server. I am trying to
connect by setting up properties of UDL file on the same box where SQL
Server is installed. Did I get it right that I have to install latest
drivers on client computers from the link your provided?
Thanks,
Igor
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but
>> you will not be able to use the new datatypes that SQL Server 2005 has
>> introduced.
>> The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
>> function of the Operating System to update them.
>> The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
>> "Provider=SQLNCLI"
> This is not quite right. MDAC is part of the operating system, but SQL
> Native Client is not.
>
> The SQL Native Client is a seperate install. It is included in the SQL
> Server distribution and available here:
> http://msdn.microsoft.com/data/sqlnative/default.aspx
> There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
> (deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
> (SQLNCLI). SQLNCLI is the best, but you must install it.
> SQL Native Client also has a new ODBC driver.
> David
>|||David,
I wa able to connect by enabling remote connections using Surface Area.
Thanks,
Igor
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23t2FLdxCGHA.724@.TK2MSFTNGP10.phx.gbl...
> David,
> I am able to connect to SQL Server 2005 using SQL Native Client but
> I got en error 'Server unavailable or access denied ...' when trying to
> connect using Microsoft OLE DB provider for SQL Server. I am trying to
> connect by setting up properties of UDL file on the same box where SQL
> Server is installed. Did I get it right that I have to install latest
> drivers on client computers from the link your provided?
> Thanks,
> Igor
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$2MO8jCGHA.2664@.TK2MSFTNGP15.phx.gbl...
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:%2326c1IiCGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> It is best to use MDAC 2.8, but 2.6 will connect to SQL Server 2005, but
>> you will not be able to use the new datatypes that SQL Server 2005 has
>> introduced.
>> The ODBC/OLEDB drivers no longer ship with SQL Server, as it is now a
>> function of the Operating System to update them.
>> The new way to connect to SQL Server 2005 is SQL Native Client (SQLNCLI)
>> "Provider=SQLNCLI"
>>
>> This is not quite right. MDAC is part of the operating system, but SQL
>> Native Client is not.
>>
>> The SQL Native Client is a seperate install. It is included in the SQL
>> Server distribution and available here:
>> http://msdn.microsoft.com/data/sqlnative/default.aspx
>> There are now 3! OleDb providers for SQL Server. Two in MDAC: MSDASQL
>> (deprecated) and SQLOLEDB (current). Plus one in the SQL Native Client
>> (SQLNCLI). SQLNCLI is the best, but you must install it.
>> SQL Native Client also has a new ODBC driver.
>> David
>

connecting to SQL DB through a firewall

Hi there!

I'm a bit new to all this, so please bear with me! :)

I've got a webserver in our DMZ and I'm trying to create an ODBC connection from that server to a db server within our firewall. When I try and connect, the following message appears:

Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()),
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied

I'm at a bit of a loss as to what's going on, as we have an application on the webserver that connects to another SQL DB server within our firewall with no problem!

webserver:
OS - win2000 standard server sp4

db server:
OS - WinNT 4.0
SQL - 7.0

If anyone can help, it would be much appreciated!!

Cheers,

Ewan :)I'd recommend (so does MS) to have a VPN tunnel for intrasver communications, especially if they are on opposite sides of the firewall.

Connecting to pervasive via ODBC

Has anyone successfully imported data from pervasive over the .net managed odbc bridge? Our admin has set up a odbc connection that works through Excel, but in SSIS the connection manager errors when trying to connect at runtime (the "test connection" button in the connection wizard reports that everything is ok though).

What error are you getting?|||

"Data source name not found and no default driver specified"

|||

A bit more info on this:

The connection seems to work in design time as metadata is fetched from the datasource to populate the column mappings in the datareader source|||

For anyone interested:
This was an issue with running the ODBC driver (presumably 32 bits) in 64bit mode. Running the package in 32 bit mode fixed the problem.

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

Connecting to IBM DB2

We need to connect to an DB2 server using ODBC provider, so we tried witht he folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

Make sure you have the same driver installed on your remote machine as you have installed on the server. It may be possible that you have an out of date DB2 driver on your remote machine if nothing else is different.

Hope that helps,

John

Connecting to Failover Partner using ODBC and OLE DB

I need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but when I exchange server roles, connect fails (the error message is: Cannot open database "TestDB" requested by the login. The login failed. in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=test;Password=test;Failover Partner=10.0.1.162;Initial Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks

With OLE DB the connection string keyword is 'FailoverPartner' (no space) in the provider string for IDBInitialize::Initialize and 'Failover Partner' (one space) for IDataInitialize::GetDataSource.

With ADO it's 'Failover Partner' (one space) and ODBC it's 'Failover_Partner'

Depending on the method you use to connect, you may be using the wrong keyword.

Mirroring happens per database rather than per server, and covers data (tables and indexes) but not user accounts and access rights. These have to be set up in both databases for failover to work, otherwise you can have a login fail after the servers switch roles if the login does not have access rights in the mirror database.

Connecting to a SQL server through WinSocks

Hello,
I want to connect to a SQL Server through WinSocks2. I tried this with the ODBC and it works. Now I want to try it in another way. But this is a hard task for me. Is anybody able to say me, what I have to send with send() and what to recieve? Is it necessary to sync the client and the server? If, yes how can I do this?
I'm thankful for every kind of help.

Regards, PeterTheMeterIf you don't know what to send and how is the format (I bet most people don't know), I recommend you use the client package provided by microsoft such as ODBC, OLEDB,sqlncli, etc. The communication between server and client is actually quite complicated, you cann't learn in a short period.

Thanks,

Connecting to a SQL Server 2005 over SSH using PuTTY

I'm having a problem connecting to my remote db from Management Studio, but I
am able to create an ODBC connection from the Data Sources applet in the
Control Panel.
PuTTY is setup to convert localhost:21433 to 192.168.100.242:1433 (the
address of my remote db server).
When I specify localhost:21433, provide the username, password, and catalog
to the Data Sources applet it connects fine. I then can use that DSN in
Visual Studio 2005 as a connection to the db.
However, when I try localhost,21433 in Management Studio, it gives me the
default connection error which talks about not having remote access enabled.
Please give me a hand with this as it is very important that I be able to
use the Management Studio tools for my app development.
Thanks in advance.
Trevor Murphy wrote:
> I'm having a problem connecting to my remote db from Management Studio, but I
> am able to create an ODBC connection from the Data Sources applet in the
> Control Panel.
> PuTTY is setup to convert localhost:21433 to 192.168.100.242:1433 (the
> address of my remote db server).
> When I specify localhost:21433, provide the username, password, and catalog
> to the Data Sources applet it connects fine. I then can use that DSN in
> Visual Studio 2005 as a connection to the db.
> However, when I try localhost,21433 in Management Studio, it gives me the
> default connection error which talks about not having remote access enabled.
> Please give me a hand with this as it is very important that I be able to
> use the Management Studio tools for my app development.
> Thanks in advance.
Does the connection error mention Named Pipes? If so, in the connection
dialog, click on Options, and under Connection Properties, make sure the
protocol explicitly states "TCP/IP".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I had gone to options>>connection properties and I set he network protocol to
tcp/ip. I couldn't set the connect to database b/c it wasn't connecting with
the server.
Anything else?
"Tracy McKibben" wrote:

> Trevor Murphy wrote:
> Does the connection error mention Named Pipes? If so, in the connection
> dialog, click on Options, and under Connection Properties, make sure the
> protocol explicitly states "TCP/IP".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Connecting to a SQL Server 2005 over SSH using PuTTY

I'm having a problem connecting to my remote db from Management Studio, but
I
am able to create an ODBC connection from the Data Sources applet in the
Control Panel.
PuTTY is setup to convert localhost:21433 to 192.168.100.242:1433 (the
address of my remote db server).
When I specify localhost:21433, provide the username, password, and catalog
to the Data Sources applet it connects fine. I then can use that DSN in
Visual Studio 2005 as a connection to the db.
However, when I try localhost,21433 in Management Studio, it gives me the
default connection error which talks about not having remote access enabled.
Please give me a hand with this as it is very important that I be able to
use the Management Studio tools for my app development.
Thanks in advance.Trevor Murphy wrote:
> I'm having a problem connecting to my remote db from Management Studio, bu
t I
> am able to create an ODBC connection from the Data Sources applet in the
> Control Panel.
> PuTTY is setup to convert localhost:21433 to 192.168.100.242:1433 (the
> address of my remote db server).
> When I specify localhost:21433, provide the username, password, and catalo
g
> to the Data Sources applet it connects fine. I then can use that DSN in
> Visual Studio 2005 as a connection to the db.
> However, when I try localhost,21433 in Management Studio, it gives me the
> default connection error which talks about not having remote access enable
d.
> Please give me a hand with this as it is very important that I be able to
> use the Management Studio tools for my app development.
> Thanks in advance.
Does the connection error mention Named Pipes? If so, in the connection
dialog, click on Options, and under Connection Properties, make sure the
protocol explicitly states "TCP/IP".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I had gone to options>>connection properties and I set he network protocol t
o
tcp/ip. I couldn't set the connect to database b/c it wasn't connecting with
the server.
Anything else?
"Tracy McKibben" wrote:

> Trevor Murphy wrote:
> Does the connection error mention Named Pipes? If so, in the connection
> dialog, click on Options, and under Connection Properties, make sure the
> protocol explicitly states "TCP/IP".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Trevor Murphy wrote:
> I had gone to options>>connection properties and I set he network protocol
to
> tcp/ip. I couldn't set the connect to database b/c it wasn't connecting wi
th
> the server.
> Anything else?
>
What happens if you telnet to localhost:21433 ?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
This is what I got. Forgive my ignorance, but I haven't used telnet in
decades.
Microsoft Telnet> open localhost:21433
Connecting To localhost:21433...Could not open connection to the host, on
port 23: Connect failed
Microsoft Telnet>
"Tracy McKibben" wrote:

> Trevor Murphy wrote:
> What happens if you telnet to localhost:21433 ?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Trevor Murphy wrote:
> Hi Tracy,
> This is what I got. Forgive my ignorance, but I haven't used telnet in
> decades.
> Microsoft Telnet> open localhost:21433
> Connecting To localhost:21433...Could not open connection to the host, on
> port 23: Connect failed
> Microsoft Telnet>
>
Try this at the command line:
telnet localhost 21433
If it connects, you should get a blank DOS window with a blinking
cursor. Otherwise, you'll get some sort of error message - I'm curious
what that error message will be.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Connecting to a linked server with ODBC

I'm currently trying to access data from a Cache DB using MSSQL. I have
linked the Cache server through an ODBC connection. I can see in the Linked
Server
expansion all the tables in Cache for the File(?) I want to access.

Here is my problem: Normally to access a linked sever I would do the
following:

select *
from ServerName.DatabaseName.dbo.TableName

I have tried

select *
from ServerName.DSN.dbo.TableName

select *
from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)

None have worked (error no such object...)

What is the syntax to select data on a linked sever via an ODBC connection?

In the Linked server set up I have also check the RPC and RPC Out options...
My ultimate goal is join tables in Cache and MSSQL into MSSQL.

Any help would be greatly appreciated!

Thanks,

-p"Pippen" <123@.hotmail.com> wrote in message news:<b8iWb.2520$_44.6454@.attbi_s52>...
> I'm currently trying to access data from a Cache DB using MSSQL. I have
> linked the Cache server through an ODBC connection. I can see in the Linked
> Server
> expansion all the tables in Cache for the File(?) I want to access.
> Here is my problem: Normally to access a linked sever I would do the
> following:
> select *
> from ServerName.DatabaseName.dbo.TableName
> I have tried
> select *
> from ServerName.DSN.dbo.TableName
> select *
> from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> None have worked (error no such object...)
> What is the syntax to select data on a linked sever via an ODBC connection?
> In the Linked server set up I have also check the RPC and RPC Out options...
> My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> Any help would be greatly appreciated!
> Thanks,
> -p

I don't know anything about Cache DB, but not all linked servers
support or require all 4 parts of the name. Have you tried these two
alternatives?

select * from LinkedServerName..SQLUser.TableName
select * from LinkedServerName...TableName

Simon|||Your first example works. Thanks for the help.

-p

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402110305.707515bb@.posting.google.c om...
> "Pippen" <123@.hotmail.com> wrote in message
news:<b8iWb.2520$_44.6454@.attbi_s52>...
> > I'm currently trying to access data from a Cache DB using MSSQL. I have
> > linked the Cache server through an ODBC connection. I can see in the
Linked
> > Server
> > expansion all the tables in Cache for the File(?) I want to access.
> > Here is my problem: Normally to access a linked sever I would do the
> > following:
> > select *
> > from ServerName.DatabaseName.dbo.TableName
> > I have tried
> > select *
> > from ServerName.DSN.dbo.TableName
> > select *
> > from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> > None have worked (error no such object...)
> > What is the syntax to select data on a linked sever via an ODBC
connection?
> > In the Linked server set up I have also check the RPC and RPC Out
options...
> > My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> > Any help would be greatly appreciated!
> > Thanks,
> > -p
> I don't know anything about Cache DB, but not all linked servers
> support or require all 4 parts of the name. Have you tried these two
> alternatives?
> select * from LinkedServerName..SQLUser.TableName
> select * from LinkedServerName...TableName
> Simon

Connecting through ODBC

Did anyone try fetching data from any database which is connected through ODBC. If so can you please share the details. In the data flow task of SSIS there is a facility for only oledb and not thru ODBC.

From what I understand, ODBC was not implemented by MS in SSIS.

See thread here

|||An ODBC Destination was not implemented due to time constraints, but you can certainly read from a database via ODBC.
You need to use the ODBC Provider which is a member of the ADO.Net connection types. You then need to use the DataReader source to query your database.
Larry Pope
|||ODBC works as a source but not a destination.

Connecting SQL Server Management Studio to an ODBC DSN

I'm not even sure if this is possible ... but here is my scenario:

We use a proprietary database. The database vendor wrote an ODBC driver which works well. So I have code that can connect to the database, do queries, etc. Can I use SQL Server Management Studio to connect to an ODBC DSN?

Ideally I would like to use the Object Browser and "Query Analyzer" (or whatever it's called) against the tables in this database.

Any help would be greatly appreciated.

Thanks.

-Aaron

No, SSMS only works against SQL Server 2000 and 2005.

The Tools forum is probably more appropriate for this question.

|||Moving the thread...