ODBC Error

Does anyone have idea on how to reslove this error message
below.
Error Source 4, code 01000 10054
[microsoft][ODBC SQL SERVER Driver] [ TCP/IP Sockets]
ConnectionRead (recv())
Hello!
This could be due to multiple reasons. Can you locate the ERRORLOG.* files
(found in the LOG sub-folder under the SQL Server installation folder) and
send it across? That might help establish if anything's wrong on the server
side.
Also:
- do you get this issue from all clients or is it specific to a set of
client(s)?
- do you receive this issue only when running a specific query? what are you
trying to execute when you experience this issue?
- are the client and server separated by any kind of network equipment like
a router / firewall?
- will the issue occur if you run the query locally on the server?
Thanks!
Arvind.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
This posting is provided "AS IS" with no warranties, and confers no rights.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:6b8301c475a3$bfab9a70$a401280a@.phx.gbl...
> Does anyone have idea on how to reslove this error message
> below.
>
> Error Source 4, code 01000 10054
> [microsoft][ODBC SQL SERVER Driver] [ TCP/IP Sockets]
> ConnectionRead (recv())
>
sql

ODBC Error

Does anyone have idea on how to reslove this error message
below.
Error Source 4, code 01000 10054
[microsoft][ODBC SQL SERVER Driver] [ TCP/IP Sockets]
ConnectionRead (recv())Hello!
This could be due to multiple reasons. Can you locate the ERRORLOG.* files
(found in the LOG sub-folder under the SQL Server installation folder) and
send it across? That might help establish if anything's wrong on the server
side.
Also:
- do you get this issue from all clients or is it specific to a set of
client(s)?
- do you receive this issue only when running a specific query? what are you
trying to execute when you experience this issue?
- are the client and server separated by any kind of network equipment like
a router / firewall?
- will the issue occur if you run the query locally on the server?
Thanks!
Arvind.
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
This posting is provided "AS IS" with no warranties, and confers no rights.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:6b8301c475a3$bfab9a70$a401280a@.phx.gbl...
> Does anyone have idea on how to reslove this error message
> below.
>
> Error Source 4, code 01000 10054
> [microsoft][ODBC SQL SERVER Driver] [ TCP/IP Sockets]
> ConnectionRead (recv())
>sql

ODBC Destination Issues with SSIS

Hi All,

In Migrating from DTS to SSIS we have scenario where data from OLE DB (SQL Server) is inserted into ODBC source (transoft driver). In case of DTS, ODBC component was provided. For SSIS we used the Destination Script Component and programmatically used the ODBC related calls to design similar functionality. However we are getting the following error –

Error[42000][Transoft] [TSODBC][usqld] Name expected@.

However the same program pointing to Microsoft Access ODBC driver works fine.

Any help in this direction will be greatly helpful.

Thanks,

S Suresh

Well, perhaps Transoft needs more information in their ODBC settings. That is, MS Access isn't really a database engine and hence is more "simple" to use.|||try writting the script code to insert a record into your odbc destination and run this script code from some other context, like a script task or even from VB. the goal is to separate your script/communication with driver away from something that might be SSIS specific like our ado.net connection manager or the script component. in other words, get the script to work outside of SSIS before adding more complexity :)

ODBC DataSource name problem

Hi All
We have an application which is used the ODBC Data Source to comunicate
with the database. So during the installation of the product the
installation creating
the database name like : VAPost and server = datasource name which is 'VAPos
t'
not the real server name in the Microsoft SQL server DSN Configuration optio
n.
Also the installation is created the Alias in SQL Server Client Network
Utility for
'VAPost' which has Server alias = 'VAPost', NetWork library = 'TCP/IP' and
the Connection parameters is the ip address of the server.
So my application is used the DSN name 'VAPost' to connect to the database b
ut
there is an error message regarding "Communication Link Failure". I could
not figure out where is the problem but the same configuration it works on
other server. Any help is greatly appreciated.
Thanks,
JodieHi Jodie,
You are using a system DSN? You should access that DSN using a ".Net
Providers\ODBC data providers" Connection. Configure it to use the DSN.
Then within the dataflow, create a DataReader data source. Set it to use th
e
DSN connection. You control what is being pulled by the SQLCommand on the
second tab.
-Steven
"Jodie" wrote:

> Hi All
> We have an application which is used the ODBC Data Source to comunicate
> with the database. So during the installation of the product the
> installation creating
> the database name like : VAPost and server = datasource name which is 'VAP
ost'
> not the real server name in the Microsoft SQL server DSN Configuration opt
ion.
> Also the installation is created the Alias in SQL Server Client Network
> Utility for
> 'VAPost' which has Server alias = 'VAPost', NetWork library = 'TCP/IP' and
> the Connection parameters is the ip address of the server.
> So my application is used the DSN name 'VAPost' to connect to the database
but
> there is an error message regarding "Communication Link Failure". I could
> not figure out where is the problem but the same configuration it works on
> other server. Any help is greatly appreciated.
> Thanks,
> Jodie
>

ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?
I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegr oups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegroups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Data Source On Clustered SQL Server

We created an ODBC datasource on a SQL Server in the cluster. Then there was
a hardware failover and subsequently unrelated DTS processes failed because
the data source wasn't defined on the failover machine. Even though those
processes did not use the ODBC source, in fact nothing used the ODBC source,
it was just created to be used in testing.
So, how do you create an ODBC datasouce that will be cluster-wide and not
just machine specific? Do you have to do it through the Cluster Management
console?
Thanks,
Bob
ODBC DSN's are not services and not applications; they are specifications,
typically stored in the registry. If you need it, just like mapped
networked drives, then you will need to create them separately on each
cluster node that requires them. There is no way to cluster them.
However, if you switched to using Data Links or ODBC file-based DSNs, then
you could store them on one of the shared clustered drives. In this case,
they would failover with the group, retain the same drive letter and path;
so, they would always be present with the virtual server.
On to another topic. Although we allow retaining the DTS package in the
MSDB repository and logging to that package, we do not allow the execution
of DTSRun on the DBMS servers. We require our clients to execute those on
an Application Server.
We try to minimize the number of external processes that run alongside the
DBMS memory space in order to maximize performance and system stability.
The last thing we want is for an external process to foul connected to an
internal SQL Server thread.
Sincerely,
Anthony Thomas

"Bob C" <BobC@.discussions.microsoft.com> wrote in message
news:65CC8979-C05A-43A9-92D6-9B156958E8CD@.microsoft.com...
> We created an ODBC datasource on a SQL Server in the cluster. Then there
was
> a hardware failover and subsequently unrelated DTS processes failed
because
> the data source wasn't defined on the failover machine. Even though those
> processes did not use the ODBC source, in fact nothing used the ODBC
source,
> it was just created to be used in testing.
> So, how do you create an ODBC datasouce that will be cluster-wide and not
> just machine specific? Do you have to do it through the Cluster Management
> console?
> Thanks,
> Bob

ODBC Data Source error

As other contributors, all I am trying to do is import data from an ODBC source (spelled 'non-Microsoft data source') into a SQL 2005 table. I can easily do this in SQL 2000 with DTS, but when I use the same DSN in VS 2005 it doesn't work.

I created an integration project, and made a connection in Connection Manager to the DSN and clicked Test Connection. It succeeded, or so it claimed.

Click OK and drag a DataReader Source onto the Data Flow surface Doubleclick it and select the connection manager per above. Note the error: Error at Data Flow Task [DataReader Source[50]]: Cannot acquire a managed connection from the run-time connection manager.

What does that mean? More to the point, how to fix it?

I wonder if you are using an ODBC connection manager? That will not work with the Data Reader Source.

The ODBC connection manager uses native ODBC and can be used with the ExecuteSQL Task.

The DataReader source needs an ADO.Net Connection Manager which can in turn reference a DSN through the ODBC Data Provider for .Net.

Donald Farmer

|||

Donald, thanks for your reply. Clearly I am missing something, though. In the designer, the Execute SQL task is in the toolbox for Control Flow, and the Ole Db Destination task is in the toolbox for Data Flow. There is no task in Data flow that I can find that will map columns from the ODBC source to the OLE DB destination, and there doesn't appear to be a task in Control Flow to do this either.

Again, I am trying to append records from an ODBC table to a SQL 2005 table. The tables have identical column names and equivalent data types. What do I need to do?

Thanks,

Steve

|||

ODBC is supported in the data flow by the Data Reader source adapter. I think the original problem may have been that you were trying to use the Data Reader source with an ODBC connection manager.

To extract data from an ODBC source, try the following:

Add an ADO.Net Connection Manager.|||

Thank you so much - that was the problem. I have another problem, but it may be the ODBC driver itself - for some reason, it is showing all the string fields as nvarchar, and the destination fields are varchar. Is there a way of globally converting Unicode to non-Unicode?

|||

Your suggestion works for establishing the connection in the dataflow, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes using the NotesSQL ODBC driver 3.02g in a System DSN. Utilizing the technique you describe above with the same DSN on the same machine as SQL 2000 DTS, the same transfer takes about 57 minutes in SQL 2005 SSIS.

Is there anything that can be done to improve the performance in SSIS to retrieve data from NotesSQL via ODBC? Thanks!

sql

ODBC Data Source error

As other contributors, all I am trying to do is import data from an ODBC source (spelled 'non-Microsoft data source') into a SQL 2005 table. I can easily do this in SQL 2000 with DTS, but when I use the same DSN in VS 2005 it doesn't work.

I created an integration project, and made a connection in Connection Manager to the DSN and clicked Test Connection. It succeeded, or so it claimed.

Click OK and drag a DataReader Source onto the Data Flow surface Doubleclick it and select the connection manager per above. Note the error: Error at Data Flow Task [DataReader Source[50]]: Cannot acquire a managed connection from the run-time connection manager.

What does that mean? More to the point, how to fix it?

I wonder if you are using an ODBC connection manager? That will not work with the Data Reader Source.

The ODBC connection manager uses native ODBC and can be used with the ExecuteSQL Task.

The DataReader source needs an ADO.Net Connection Manager which can in turn reference a DSN through the ODBC Data Provider for .Net.

Donald Farmer

|||

Donald, thanks for your reply. Clearly I am missing something, though. In the designer, the Execute SQL task is in the toolbox for Control Flow, and the Ole Db Destination task is in the toolbox for Data Flow. There is no task in Data flow that I can find that will map columns from the ODBC source to the OLE DB destination, and there doesn't appear to be a task in Control Flow to do this either.

Again, I am trying to append records from an ODBC table to a SQL 2005 table. The tables have identical column names and equivalent data types. What do I need to do?

Thanks,

Steve

|||

ODBC is supported in the data flow by the Data Reader source adapter. I think the original problem may have been that you were trying to use the Data Reader source with an ODBC connection manager.

To extract data from an ODBC source, try the following:

Add an ADO.Net Connection Manager.|||

Thank you so much - that was the problem. I have another problem, but it may be the ODBC driver itself - for some reason, it is showing all the string fields as nvarchar, and the destination fields are varchar. Is there a way of globally converting Unicode to non-Unicode?

|||

Your suggestion works for establishing the connection in the dataflow, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes using the NotesSQL ODBC driver 3.02g in a System DSN. Utilizing the technique you describe above with the same DSN on the same machine as SQL 2000 DTS, the same transfer takes about 57 minutes in SQL 2005 SSIS.

Is there anything that can be done to improve the performance in SSIS to retrieve data from NotesSQL via ODBC? Thanks!

ODBC data source doesn't like my SQL query

Good morning all,

I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:

SELECT DISTINCT
WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3)
AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty],
WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC]
FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN
C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN
C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN
C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0
WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @.RP1 + N'%') OR
(WIP.R0 LIKE N'%' + @.RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1)))
ORDER BY [Job No], [Pack No]

I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.

Has anyone else had a similar problem at all, and could you direct me to a solution if you have?

Thanks in advance,

Chris

you could use a table function to execute your query and get results from it...

Select * From MyTableFunction(<parameters>)

This should work...

|||You'd be better off building this into a stored procedure and executing the procedure via your ODBC connection.|||

Check the ODBC driver documentation for the canonical functions. You need to use those instead of data source specific functions to run the same SQL statement using different drivers. This also applies to the SQL grammar. Of course, this depends on the capability of the driver and the level of implementation. For example, below are some of the mappings:

LEN() -> {fn LENGTH(<col_or_expr>)}

LTRIM -> {fn LTRIM(<col_or_expr>)}

RTRIM -> {fn RTRIM(<col_or_expr>)}

|||

Yes the ODBC driver is very limited in what it allows you to do.

Thanks for the nod in the right direction.

ODBC data source doesn't like my SQL query

Good morning all,

I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:

SELECT DISTINCT
WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3)
AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty],
WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC]
FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN
C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN
C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN
C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0
WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @.RP1 + N'%') OR
(WIP.R0 LIKE N'%' + @.RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1)))
ORDER BY [Job No], [Pack No]

I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.

Has anyone else had a similar problem at all, and could you direct me to a solution if you have?

Thanks in advance,

Chris

you could use a table function to execute your query and get results from it...

Select * From MyTableFunction(<parameters>)

This should work...

|||You'd be better off building this into a stored procedure and executing the procedure via your ODBC connection.|||

Check the ODBC driver documentation for the canonical functions. You need to use those instead of data source specific functions to run the same SQL statement using different drivers. This also applies to the SQL grammar. Of course, this depends on the capability of the driver and the level of implementation. For example, below are some of the mappings:

LEN() -> {fn LENGTH(<col_or_expr>)}

LTRIM -> {fn LTRIM(<col_or_expr>)}

RTRIM -> {fn RTRIM(<col_or_expr>)}

|||

Yes the ODBC driver is very limited in what it allows you to do.

Thanks for the nod in the right direction.

ODBC Data Source Administrator

Hi all, hope you can help?
I have created a system DSN in the usual way using the above ODBC tool
(I am trying to create a connection to our Open Accounts package using
the OpenEdge 10.1A driver). I provide the host name, the port number
and the database name, the user ID and password and test the
connection. All works a treat...'Connection established'.
I open up Excel and try to import data using my newly created DSN...it
exposes all of the tables within the database and lets me select
fields from those table. When I get to the 'return data to Microsoft
Excel' part I get the following error message 'Access denied
(Authorisation failed) (7512)'.
Any ideas guys?
Regards
PaulHi Paul,
What database is the Open Accounts package using to store it's data, SQL
Server, Access, Oracle etc. We need to know this because we need to
troubleshoot the security settings/accounts etc. of the database
containing the Open Accounts data.
Jonathan
thePriest wrote:
> Hi all, hope you can help?
> I have created a system DSN in the usual way using the above ODBC tool
> (I am trying to create a connection to our Open Accounts package using
> the OpenEdge 10.1A driver). I provide the host name, the port number
> and the database name, the user ID and password and test the
> connection. All works a treat...'Connection established'.
> I open up Excel and try to import data using my newly created DSN...it
> exposes all of the tables within the database and lets me select
> fields from those table. When I get to the 'return data to Microsoft
> Excel' part I get the following error message 'Access denied
> (Authorisation failed) (7512)'.
> Any ideas guys?
> Regards
> Paul
>

ODBC data source + SQL 2000

Hello,
I'm running a SQL 2000 server on a win 2k3 machine.
I'm not familiar with Win 2k3 so I'm having a hard time looking for the
correct information.
I know my SQL server is up and running. I can connect to it from my
workstation through Enterprise Manager (EM) and the query analyzer (QA).
The funny thing is , when I login to the 2k3 machine and open up the
ODBC Data Source Administrator and try to create a system DSN, the 2k3
machine is not listed in the drop down menu.
It reads,
Which SQL Server do you wan tot connect to?
Server: <Dropdown menu>
I did just type the servername, winbox, and the test at the end was
sucessful. The reason I'm asking is because I'm having a hard time
getting my web app server (CF 5.0 on RH 9.0 w/Apache) creating a DSN
connection I can use to the SQL server.
Thanks.So what's the issue?
Linchi

quote:

---

>--Original Message--
>Hello,
>I'm running a SQL 2000 server on a win 2k3 machine.
>I'm not familiar with Win 2k3 so I'm having a hard time

---

looking for the

quote:

---

>correct information.
>I know my SQL server is up and running. I can connect to

---

it from my

quote:

---

>workstation through Enterprise Manager (EM) and the query

---

analyzer (QA).

quote:

---

>The funny thing is , when I login to the 2k3 machine and

---

open up the

quote:

---

>ODBC Data Source Administrator and try to create a system

---

DSN, the 2k3

quote:

---

>machine is not listed in the drop down menu.
>It reads,
>Which SQL Server do you wan tot connect to?
>Server: <Dropdown menu>
>I did just type the servername, winbox, and the test at

---

the end was

quote:

---

>sucessful. The reason I'm asking is because I'm having a

---

hard time

quote:

---

>getting my web app server (CF 5.0 on RH 9.0 w/Apache)

---

creating a DSN

quote:

---

>connection I can use to the SQL server.
>Thanks.
>.
>

---

|||I can't get my app server (MacroMedia CF server 5.0 running on RH 9) to
create an ODBC connection to SQL Server 2000 running on a 2k3 server.
I feel like I am taking crazy pills. I have done this config numerous
times with the same system architect. The only thing that is different
this time is that the SQL server is running on a 2k3 Machine.
CF server defaults the connection to 1433. I checked up the TCPIP
properties to make sure I did not close that port. It looks good.
Any ideas?
Linchi Shea wrote:

quote:

---

> So what's the issue?
> Linchi
>

---

sql

ODBC data source + SQL 2000

Hello,
I'm running a SQL 2000 server on a win 2k3 machine.
I'm not familiar with Win 2k3 so I'm having a hard time looking for the
correct information.
I know my SQL server is up and running. I can connect to it from my
workstation through Enterprise Manager (EM) and the query analyzer (QA).
The funny thing is , when I login to the 2k3 machine and open up the
ODBC Data Source Administrator and try to create a system DSN, the 2k3
machine is not listed in the drop down menu.
It reads,
Which SQL Server do you wan tot connect to?
Server: <Dropdown menu>
I did just type the servername, winbox, and the test at the end was
sucessful. The reason I'm asking is because I'm having a hard time
getting my web app server (CF 5.0 on RH 9.0 w/Apache) creating a DSN
connection I can use to the SQL server.
Thanks.So what's the issue?
Linchi
>--Original Message--
>Hello,
>I'm running a SQL 2000 server on a win 2k3 machine.
>I'm not familiar with Win 2k3 so I'm having a hard time
looking for the
>correct information.
>I know my SQL server is up and running. I can connect to
it from my
>workstation through Enterprise Manager (EM) and the query
analyzer (QA).
>The funny thing is , when I login to the 2k3 machine and
open up the
>ODBC Data Source Administrator and try to create a system
DSN, the 2k3
>machine is not listed in the drop down menu.
>It reads,
>Which SQL Server do you wan tot connect to?
>Server: <Dropdown menu>
>I did just type the servername, winbox, and the test at
the end was
>sucessful. The reason I'm asking is because I'm having a
hard time
>getting my web app server (CF 5.0 on RH 9.0 w/Apache)
creating a DSN
>connection I can use to the SQL server.
>Thanks.
>.
>|||I can't get my app server (MacroMedia CF server 5.0 running on RH 9) to
create an ODBC connection to SQL Server 2000 running on a 2k3 server.
I feel like I am taking crazy pills. I have done this config numerous
times with the same system architect. The only thing that is different
this time is that the SQL server is running on a 2k3 Machine.
CF server defaults the connection to 1433. I checked up the TCPIP
properties to make sure I did not close that port. It looks good.
Any ideas?
Linchi Shea wrote:
> So what's the issue?
> Linchi
>

ODBC Data Source

I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.

Thanks,
Carson

Carson,

Use the OLE DB Source. That's generally regarded as the default option and, I think, will be quicker than datareader source.

-Jamie

|||

Then this is my issue with the OLE DB Source. I go into the OLE DB Source Editor, nothing is listed in my OLE DB Connection Manager dropdown, so I click the "New..." button.

The Configure OLE DB Connection Manager window pops up, but nothing is listed under Data Connections, the listbox is blank. So I click the "New..." button on this popup window. (Keep in mind, in my Server Explorer I have two Data Connections my localhost and the ODBC DSN i explained in my first post. Also, in the bottom of the package, under the tab Connection Managers I also have defined an ODBC DSN connection by right clicking and selecting New Connection and then ODBC.)

Then up comes the Connection Manager popup, yet ODBC is not in the drop-down list of Providers like it was when I created a Connection Manager by right-clicking in the section Connection Manager found underneath the package DataFlow.

I do not have an OLE driver for this old Btrieve data source, only a working ODBC driver.

Please advise.

Thanks!

|||

Carson,

The only way to use ODBC connections in SSIS is by using ADO .NET provider for ODBC. That implies you have to use Data Reader source with it. There is no a custom UI for the Data Reader source component so you would need to type your query (select * from <tableName> or equivalent one) directly into the SQLCommand property. You will not be able to see available tables.

HTH.

|||

Thank you for this explanation, that helps. I'm assuming that before I can set the SQLCommand property, I have to set the Connection Manager in the first tab of the Advanced Editor. When I do, I get the following error: "Error at Data Flow Task [DataReader Sourcer [175]]: Cannot acquire a managed connection from the run-time connection manager." Can someone please explain what to do with this error?

I created the connection manager for my ODBC data source by right-clicking in the area directly under the package data flow UI, and selecting "New Connection", then selecting "ODBC", then I create a new connection manager by setting the system data source name to the ODBC DSN that I created. The same DSN I can browse the tables with in the Server Explorer.

Please advise.

Thanks!!

|||

You have to choose "New ADO .NET Connection..." from the menu, click on the "New..." button of the next dialog, select .NET Providers/Odbc Data Provider in the first drop-down of the following dialog and then choose your DSN.

This way created connection can now be used in the advanced UI of the Data Reader Source.

Thanks.

|||Bob - You 'Da Man!! That worked!|||

Bob, where is the option for new ado .net connection?

Thanks

|||

Right-click in the "Connection Managers" window and then follow my previous post.

HTH.

ODBC Data Source

I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do?

Where are you looking to find this data source?

Mike

|||In the Import wizard.|||

Thanks,

The Import wizard is not supported in SQL Express so this isn't the best place to get information about it. I'm going to move this thread into a forum where the folks who should be able to answer this question hang out.

Mike

|||Well, great! We use SQL Express. Won't my boss love this! Thanks for the info.|||

There's some very good reasons why SQLExpress is free. You don't get much with it

-Jamie

ODBC Data Source

I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.

Thanks,
Carson

Carson,

Use the OLE DB Source. That's generally regarded as the default option and, I think, will be quicker than datareader source.

-Jamie

|||

Then this is my issue with the OLE DB Source. I go into the OLE DB Source Editor, nothing is listed in my OLE DB Connection Manager dropdown, so I click the "New..." button.

The Configure OLE DB Connection Manager window pops up, but nothing is listed under Data Connections, the listbox is blank. So I click the "New..." button on this popup window. (Keep in mind, in my Server Explorer I have two Data Connections my localhost and the ODBC DSN i explained in my first post. Also, in the bottom of the package, under the tab Connection Managers I also have defined an ODBC DSN connection by right clicking and selecting New Connection and then ODBC.)

Then up comes the Connection Manager popup, yet ODBC is not in the drop-down list of Providers like it was when I created a Connection Manager by right-clicking in the section Connection Manager found underneath the package DataFlow.

I do not have an OLE driver for this old Btrieve data source, only a working ODBC driver.

Please advise.

Thanks!

|||

Carson,

The only way to use ODBC connections in SSIS is by using ADO .NET provider for ODBC. That implies you have to use Data Reader source with it. There is no a custom UI for the Data Reader source component so you would need to type your query (select * from <tableName> or equivalent one) directly into the SQLCommand property. You will not be able to see available tables.

HTH.

|||

Thank you for this explanation, that helps. I'm assuming that before I can set the SQLCommand property, I have to set the Connection Manager in the first tab of the Advanced Editor. When I do, I get the following error: "Error at Data Flow Task [DataReader Sourcer [175]]: Cannot acquire a managed connection from the run-time connection manager." Can someone please explain what to do with this error?

I created the connection manager for my ODBC data source by right-clicking in the area directly under the package data flow UI, and selecting "New Connection", then selecting "ODBC", then I create a new connection manager by setting the system data source name to the ODBC DSN that I created. The same DSN I can browse the tables with in the Server Explorer.

Please advise.

Thanks!!

|||

You have to choose "New ADO .NET Connection..." from the menu, click on the "New..." button of the next dialog, select .NET Providers/Odbc Data Provider in the first drop-down of the following dialog and then choose your DSN.

This way created connection can now be used in the advanced UI of the Data Reader Source.

Thanks.

|||Bob - You 'Da Man!! That worked!|||

Bob, where is the option for new ado .net connection?

Thanks

|||

Right-click in the "Connection Managers" window and then follow my previous post.

HTH.

ODBC data source

All I want to know is...

Is it possible to have an ODBC data source in Analysis Services 2005?
(I am using the June CTP of SQL 2005. I create a new Analysis Services Project using VS 2005. When I try to add a new data source, "ODBC" is not listed in the Provider list. What am I doing wrong?)

Reply from newsgroup from Microsoft:
AS2005 is not going to support ODBC data source. However, you can try to
create a data source in SSIS project. Then, add the data source into new AS
project. You should be able to create DSV, cube and dimension, etc.

However, since this feature is not supported. Believe that this area has not
been tested enough and this may break engine or not working probably. It is
"AS IS" if you really want to use it.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Kwok
SQL Server Analysis Services

sql

ODBC data source

I am trying to use the ADO.NET bridge to connect to an old database on RISC6000 via an ODBC connection, but I receive an error.

So, I would use a script component to retrieve data directly from an ODBC connection, using a stored procedure.

Any sample code ?

Any idea ?

Thanks

You can use the DataReader source in the data flow as another option connecting to an ODBC source.

ODBC Data Source

I hope that I am being a real dunce about this.
Using SSIS, I create an ODBC data connection to our transactional system,
from which we build our data warehouse. In the data flow, when I go to add
the data source, I find nothing that will reference the ODBC data connection
.
Suggestions?
StevenRight click on the data flow source and select edit. From
here you can enter the Connection Manager that you added.
What are you using as the Data Flow Source?
-Sue
On Tue, 23 May 2006 08:01:03 -0700, Steven
<Steven@.discussions.microsoft.com> wrote:

>I hope that I am being a real dunce about this.
>Using SSIS, I create an ODBC data connection to our transactional system,
>from which we build our data warehouse. In the data flow, when I go to add
>the data source, I find nothing that will reference the ODBC data connectio
n.
>Suggestions?
>Steven|||Sue,
Thanks for your response. Here is what I found after some digging. You use
a .Net Providers/ODBC data providers connection. You create it and then
configure it to use a system dsn. Within the dataflow, you access the
connection with a data reader source. You point it to the connection that
you configured. You control what is pulled by using the SQLCommand on the
component properties tab.
Simple enough once you know what to do.
-Steven
"Sue Hoegemeier" wrote:

> Right click on the data flow source and select edit. From
> here you can enter the Connection Manager that you added.
> What are you using as the Data Flow Source?
> -Sue
> On Tue, 23 May 2006 08:01:03 -0700, Steven
> <Steven@.discussions.microsoft.com> wrote:
>
>