ODBC Connection failure

My application (written in a proprietary old 4GL) is connecting to a SQL 2000 database via ODBC

This works fine nearly all the time, but occasionally, it throws the following error when trying to connect :

Connection Failed

SQLState '01000'

SQL Server Error: 10060

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection Open(Connect()).

Connection Failed:

SQLState '08001'

SQL Server Error: 17

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

This error is displayed in a Windows dialog box. When the OK button on this dialog is clicked, it throws up a login/password box. When this is filled in and submitted, the connection is usually made cleanly and the application continues.

The big problem is that the application is an unattended service, and this dialog can go unnoticed for quite a while.

If these dialogs are 'Cancelled' rather than 'OK'd, the 4GL receives the error and arranges to re-try the connection itself.

Is ODBC responsible for popping up the interactive dialog, and, if so, can I do anything to force it to return an error instead ?

Hi Steve,

Interesting situation. The error 10060 means this: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. So you are probably experiencing a timeout in one of the socket-relation ops, most probably connect() (different from the SQL netlib's Connect() mentioned above).

The login popup is implemented by the SQL ODBC driver. You are probably using SQLDriverConnect in ODBC? It has a parameter "DriverCompletion", which could be:

SQL_DRIVER_PROMPT
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
SQL_DRIVER_NOPROMPT

You probably have passed the first one (SQL_DRIVER_PROMPT). In order to make sure the SQLDriverConnect fails without popping up the troublesome dialog, you need to pass the SQL_DRIVER_NOPROMPT. I truly hope you are able to modify the 4GL application?

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

|||

Thanks a lot Jivko.

Unfortunately, the only parameter the 4GL allows me to specify is the DSN name.

I'll be on to the vendor's support desk rather forcefully first thing on Monday morning - thanks for the Ammo!

ODB Datareader time columns - updated 26th June

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns - help needed

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?