Opening Mulitple Recordsets

I have a single .asp page that opens a connection and then sequentially
opens and closes 14 recordsets from stored procedures to obtain various
product information before closing the connection.

Is it common practice to do something like this? Or is opening 14
recordsets going to become a real problem when the page goes live and starts
getting high web traffic?

Thank you in advance for any information you might provide.

DaveHi

There is not really enough detail to know if you will have problems with
this although 14 seems quite a few it may not necessarily cause any problems
if the number of records being retrieved is not excessive. You should
simulate different levels of usage on your system before it goes live to get
some idea of the response.

If you are making muliple calls to different stored procedures, you may wish
to use a controlling procedure that returns multiple result sets see:
http://www.aspfaq.com/show.asp?id=2319

John

"Toonman" <toonman@.NOSPAMtoonman.com> wrote in message
news:4fZLb.10540$uF6.3301521@.news1.news.adelphia.n et...
> I have a single .asp page that opens a connection and then sequentially
> opens and closes 14 recordsets from stored procedures to obtain various
> product information before closing the connection.
> Is it common practice to do something like this? Or is opening 14
> recordsets going to become a real problem when the page goes live and
starts
> getting high web traffic?
> Thank you in advance for any information you might provide.
> Dave|||John,

Your information was extremely helpful... thank you again.

Dave

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:btpp2d$8ja$1@.sparta.btinternet.com...
> Hi
> There is not really enough detail to know if you will have problems with
> this although 14 seems quite a few it may not necessarily cause any
problems
> if the number of records being retrieved is not excessive. You should
> simulate different levels of usage on your system before it goes live to
get
> some idea of the response.
> If you are making muliple calls to different stored procedures, you may
wish
> to use a controlling procedure that returns multiple result sets see:
> http://www.aspfaq.com/show.asp?id=2319
> John
> "Toonman" <toonman@.NOSPAMtoonman.com> wrote in message
> news:4fZLb.10540$uF6.3301521@.news1.news.adelphia.n et...
> > I have a single .asp page that opens a connection and then sequentially
> > opens and closes 14 recordsets from stored procedures to obtain various
> > product information before closing the connection.
> > Is it common practice to do something like this? Or is opening 14
> > recordsets going to become a real problem when the page goes live and
> starts
> > getting high web traffic?
> > Thank you in advance for any information you might provide.
> > Dave

Open SQL file using SQL 2005 Management Studio

Everytime when I open .SQL file using SQL 2005 management Studio, it ask me
about connection info. Is there any way to use the previous one like SQL
2000? For example, first time when I open .SQL file, I put connection info,
and if I open another .SQL file, it will default to the previous connection
info without pop up the "Connect to Server" screen.
Thanks
Yong
This behavior has been changed in SQL Server 2005 Service Pack 2. You can
checkout the December CTP: http://www.microsoft.com/sql/ctp.mspx
FAQ on SP2:
http://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Yong" <Yong@.discussions.microsoft.com> wrote in message
news:1710CE37-0664-4A98-B0A8-CD84D16EECCD@.microsoft.com...
> Everytime when I open .SQL file using SQL 2005 management Studio, it ask
> me
> about connection info. Is there any way to use the previous one like SQL
> 2000? For example, first time when I open .SQL file, I put connection
> info,
> and if I open another .SQL file, it will default to the previous
> connection
> info without pop up the "Connect to Server" screen.
> Thanks
> Yong
|||Thanks
"Paul A. Mestemaker II [MSFT]" wrote:

> This behavior has been changed in SQL Server 2005 Service Pack 2. You can
> checkout the December CTP: http://www.microsoft.com/sql/ctp.mspx
> FAQ on SP2:
> http://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> "Yong" <Yong@.discussions.microsoft.com> wrote in message
> news:1710CE37-0664-4A98-B0A8-CD84D16EECCD@.microsoft.com...
>

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

|||

I just ran into this problem using the 64bit client also. I solved in in a similar fashion by doing the following:

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select CAST(numID AS NUMBER) as numID from OracleTable');

To shed a little more light on the subject, the problem appears to be isolated to only INTEGER or NUMBER(n) values that end with a 0.

I found that I get this error whenever I select rows where the column contains a number like 10 or 1090. But if no rows contain a value that ends with a 0, the query succeeds.

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

|||

I just ran into this problem using the 64bit client also. I solved in in a similar fashion by doing the following:

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select CAST(numID AS NUMBER) as numID from OracleTable');

To shed a little more light on the subject, the problem appears to be isolated to only INTEGER or NUMBER(n) values that end with a 0.

I found that I get this error whenever I select rows where the column contains a number like 10 or 1090. But if no rows contain a value that ends with a 0, the query succeeds.

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

Open query error: Invalid data for type "numeric".

Hi guys....

I am upgrading from SQL Server 2000 to 2005 x64.

I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).

So, when I run this query:

select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');

I get this message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

(I do see some rows returned before the error.)

However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');

This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

Does anyone know how to get this to work without doing multiple conversions?

Thanks,

Forch

not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of

select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');

do you get any hits?

how about with

select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');

|||

I have the very same issue on my x64.

My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)

select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES

from openquery (pibd,'select * from term_ref where term_cd = ''AE''')

The query runs fine with out the TERM_DAYS field. This is a numeric field.

One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field

I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.

I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.

Thanks

|||

From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.

One suggestion in the forum is to create a view to round the numerics.

http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0

|||

Above suggestion is not working..

|||

I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:

I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...

Hope this helps someone, and saves someone some time...

Rob

|||

I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..

as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');

|||

It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.

Select my_num_field1,

my_char_field2,

my_num_field3

from my_table;

Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).

Change it to:

Select to_number(my_num_field1) my_num_field1,

my_char_field2,

to_number(my_num_field3) my_num_field3

from my_table;

Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.

I'm using x64 bit Oracle InstantClient version 10.2.0.3.

Are there other's seeing this same problem? Any other suggestions or workarounds?

|||

I too have the error. i have only just started to investigate.

I had a select working fine on 32bit which seems to have broken without any change

|||

I just ran into this problem using the 64bit client also. I solved in in a similar fashion by doing the following:

select Convert(Int, numID) as numID
from Openquery(myConnection, 'select CAST(numID AS NUMBER) as numID from OracleTable');

To shed a little more light on the subject, the problem appears to be isolated to only INTEGER or NUMBER(n) values that end with a 0.

I found that I get this error whenever I select rows where the column contains a number like 10 or 1090. But if no rows contain a value that ends with a 0, the query succeeds.

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:
> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
> > I have a connection string that has the clause "initial catalog=XXXXXX" in
> > it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> > opened and the login failed. Any ideas?
> >
> > Thank you.
> >
> > Keivn
> >
> >

Open intial catalog?

I have a connection string that has the clause "initial catalog=XXXXXX" in
it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
opened and the login failed. Any ideas?
Thank you.
KeivnThis is the exact error message:
Cannot open database \"XXXXXXX\" requested by the login. The login
failed.\r\nLogin failed for user 'developer'
"Kevin Burton" wrote:

> I have a connection string that has the clause "initial catalog=XXXXXX" in
> it. When I use SqlConnection.Open I get an exception that XXXXXX cannot be
> opened and the login failed. Any ideas?
> Thank you.
> Keivn
>|||Looks like the user 'developer' does not have access to this database. Have
you checked that?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
[vbcol=seagreen]
> This is the exact error message:
> Cannot open database \"XXXXXXX\" requested by the login. The login
> failed.\r\nLogin failed for user 'developer'
> "Kevin Burton" wrote:
>

Open db connection, disable Screen Saver?

We have a application developed in house, which runs on SQL 2000 server.
Some people have reported that there XP desktop screen saver does not run
when they are in the application.
Can a open db connection disable the screen saver from running?
Not that I can see how. If the software that has the db connection open is
doing something, then the computer may think the user is still using it.
"Courtney R" <CourtneyR@.discussions.microsoft.com> wrote in message
news:A7BD6B04-1ABA-4F50-8734-514606BF8B10@.microsoft.com...
> We have a application developed in house, which runs on SQL 2000 server.
> Some people have reported that there XP desktop screen saver does not run
> when they are in the application.
> Can a open db connection disable the screen saver from running?

Open db connection, disable Screen Saver?

We have a application developed in house, which runs on SQL 2000 server.
Some people have reported that there XP desktop screen saver does not run
when they are in the application.
Can a open db connection disable the screen saver from running?Not that I can see how. If the software that has the db connection open is
doing something, then the computer may think the user is still using it.
"Courtney R" <CourtneyR@.discussions.microsoft.com> wrote in message
news:A7BD6B04-1ABA-4F50-8734-514606BF8B10@.microsoft.com...
> We have a application developed in house, which runs on SQL 2000 server.
> Some people have reported that there XP desktop screen saver does not run
> when they are in the application.
> Can a open db connection disable the screen saver from running?

Open db connection, disable Screen Saver?

We have a application developed in house, which runs on SQL 2000 server.
Some people have reported that there XP desktop screen saver does not run
when they are in the application.
Can a open db connection disable the screen saver from running?Not that I can see how. If the software that has the db connection open is
doing something, then the computer may think the user is still using it.
"Courtney R" <CourtneyR@.discussions.microsoft.com> wrote in message
news:A7BD6B04-1ABA-4F50-8734-514606BF8B10@.microsoft.com...
> We have a application developed in house, which runs on SQL 2000 server.
> Some people have reported that there XP desktop screen saver does not run
> when they are in the application.
> Can a open db connection disable the screen saver from running?

open connection side DB after close adodb connection side client

when i close the connection ado in the script ASP the
server DB relieve that the connection is more open for 60
seconds.
ThanksYou're seeing the effect of Connection Pooling.
324686 Support WebCast: ODBC Connection Pooling and OLEDB Session Pooling in
http://support.microsoft.com/?id=324686
191572 INFO: Connection Pool Management by ADO Objects Called From ASP
http://support.microsoft.com/?id=191572
176056 INFO: ADO/ASP Scalability FAQ
http://support.microsoft.com/?id=176056
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Open connection in sql server

Try
Dim l_connString As String

l_connString = "Server=kangalert;database=Order;user id=sa;password=123;"
m_cn = New SqlConnection(l_connString)
m_cn.Open()

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
End Try

i got this in my mobile application, which try to open a connection directly to the sql server, but i got the following message

General Network error, check your network documentation.

Please go ahead and do general network troubleshooting to make sure you can connect (ping, port scan, etc.). That can be done with free vxUtils tool.

|||

i cannot connect directly to the sql server, but i can pull data from sql server.

It is really can connect directly to the sql server using mobile?

|||

If by “pull” you mean RDA/Replication, that is done via IIS. Direct connection is not going through IIS as it's just that - direct.

Yes, it is possible and just works assuming you have popper SQL Server and network setup.

If you not familiar with SQL Server connectivity configuration and/or network troubleshooting, you should seek help from your IT department.

|||

This is confusing - your code shows an attempt to use the System.Data.SqlClient and open a SqlConnection directly on the server using SQL Server authentication. You're reporting a network error when you try to establish the connection but then say that you can pull data from Sql Server. If you are running SqlCommands on the remote SqlServer and getting data back, you're in good shape. If you are trying to do RDA.Pull, your code is the wrong sort of connection. For RDA you need to be using System.Data.SqlServer.Ce and creating a SqlCeConnection.

Please clarify what you're trying to do and we'll try to help.

Darren

|||

yes, i agree with you. but my problem is the direct connection not the RDA . pull.

so do you have any idea of this error....

"General Network error, check your network documentation"

for you information i debug this simple application through a emulator. Should i use a real device to perfrom this testing?

|||You could install vxUtils to the emulator or device and perform general network diagnostics (ping, port scan, etc.) to see of server is reachable.

Open Connection

Hi to all,
I know that we should close sql connection when it's complete its job for
performance and sql server's resources. But i can't find article or text
for
this subject on the internet. Or i know not true?
Could you share with me if you have like this article ?For internet applications you should use connection pooling, and close your
connection as soon as you are finished. The provider (or driver) will
automatically keep the connection open for you and re-use it if you open
another connection using the same connection string within the connection
pool timeout period. Just search for "SQL Server connection pooling" and
I'm sure you will find more information
"Erencan SAIROLU" <erencans@.hotmail.com> wrote in message
news:uaeY1jc%23GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi to all,
> I know that we should close sql connection when it's complete its job for
> performance and sql server's resources. But i can't find article or text
> for
> this subject on the internet. Or i know not true?
> Could you share with me if you have like this article ?
>|||Thank you Brain
"Brian Pursley" <bp@.cinlogic.com> wrote in message
news:eS5EPNj%23GHA.4800@.TK2MSFTNGP05.phx.gbl...
> For internet applications you should use connection pooling, and close
> your connection as soon as you are finished. The provider (or driver)
> will automatically keep the connection open for you and re-use it if you
> open another connection using the same connection string within the
> connection pool timeout period. Just search for "SQL Server connection
> pooling" and I'm sure you will find more information
> "Erencan SAIROLU" <erencans@.hotmail.com> wrote in message
> news:uaeY1jc%23GHA.5092@.TK2MSFTNGP04.phx.gbl...
>

Open Connection

Hello everybody

I would like to know more about the number of possible connection to a sql server

is it by pool ? or there is max for all the database ? all the server ?

how I can get the number of connection open ?

Thx in advance

If you run sp_configure and look for "user connections" it will tell you the max number of connections to the server. If you run sp_who2 you can see total connections at that time, and sp_who2 active gives active connections doing something.

|||

Ok, perfect

thank you very much

Open connection

How can I tell if I left a connection open? I'm calling slq thru ASP pages.
Thanks,
SueOriginally posted by belewe92614
How can I tell if I left a connection open? I'm calling slq thru ASP pages.
Thanks,
Sue

Use the profiler and run a trace on connections. There are trace options to track when a connection opens and closes.|||You can use the sp_who procedure to see what processes are connected in the server: "EXEC sp_who"|||I you are using ADO to connect through an ASP you can check your status

adStateClosed 0 The object is closed
adStateOpen 1 The object is open
adStateConnecting 2 The object is connecting
adStateExecuting 4 The object is executing a command
adStateFetching 8 The rows of the object are being retrieved

See more from this link State Property (http://www.w3schools.com/ado/prop_state.asp)

Open Connection

Hi to all,
I know that we should close sql connection when it's complete its job for
performance and sql server's resources. But i can't find article or text
for
this subject on the internet. Or i know not true?
Could you share with me if you have like this article ?For internet applications you should use connection pooling, and close your
connection as soon as you are finished. The provider (or driver) will
automatically keep the connection open for you and re-use it if you open
another connection using the same connection string within the connection
pool timeout period. Just search for "SQL Server connection pooling" and
I'm sure you will find more information
"Erencan SAÐIROÐLU" <erencans@.hotmail.com> wrote in message
news:uaeY1jc%23GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi to all,
> I know that we should close sql connection when it's complete its job for
> performance and sql server's resources. But i can't find article or text
> for
> this subject on the internet. Or i know not true?
> Could you share with me if you have like this article ?
>|||Thank you Brain
"Brian Pursley" <bp@.cinlogic.com> wrote in message
news:eS5EPNj%23GHA.4800@.TK2MSFTNGP05.phx.gbl...
> For internet applications you should use connection pooling, and close
> your connection as soon as you are finished. The provider (or driver)
> will automatically keep the connection open for you and re-use it if you
> open another connection using the same connection string within the
> connection pool timeout period. Just search for "SQL Server connection
> pooling" and I'm sure you will find more information
> "Erencan SAÐIROÐLU" <erencans@.hotmail.com> wrote in message
> news:uaeY1jc%23GHA.5092@.TK2MSFTNGP04.phx.gbl...
>> Hi to all,
>> I know that we should close sql connection when it's complete its job for
>> performance and sql server's resources. But i can't find article or text
>> for
>> this subject on the internet. Or i know not true?
>> Could you share with me if you have like this article ?
>

Open an Password Protected Access-2000 Database (OLE DB)

Hi,
I have created reports for a password protected database using Crystal Reports-8 thru OLE DB Connection. Whenever calling the report from a VB Form(thru Crystal Report Active-x Control), it reports "Unable to logon to SQL Server".

But if I try same thru ODBC Connection thru a DSN, it works perfect.

Since, there is a need to change the name of the source database (during runtime, the report has to access data from different databases), I require the same thru an OLE DB Connection. OR there is any way to change the database name in an odbc dsn by modifying connection parameters?

I remain.

Thanks.If u r tring to open Access database which has password. to change the database at runtime u can use the following code in vb.My report using Direct Database file connection not oledb or others.

Report.Database.Tables.Item(1).Location = App.Path & "\" & dbName
Report.Database.Tables(1).SetSessionInfo "", Chr(10) & txtPass

if u have more then on table u can use loop.