Basic Question: Setting a varaiable to a datasource.select value

Hi!

I am trying to do something that I would think should be rather simple.

I have a SQLDataSource that has a simple select statement.

I have a String Variable st_temp

I know my SQLDataSource returns only one value, and it's a string.

HOW DO I DO somehitng like st_temp=SQLDataSource.Select;

I am getting nothing but errorrs

Thanks in advance

Dan

What errors are you getting?|||

If you are using just a single value then there is no need for SQLDataSource object. You can directly use SQLCommand.ExecuteScalar() method.

|||

2 things:

1. sorry to be so dense but how do I use SQLCommand.ExecuteScalar() method. in C#?

2. Errors are

":CS0201: Only assignment, call, increment, decrement, and new object expressions can be used as a statement" when I call SDSSelect.Select;

and

":CS0428: Cannot convert method group 'Select' to non-delegate type 'string'. Did you intend to invoke the method?" when I call Label1.Text= SDSSelect.Select;

Thanks again for the help...i shouldda stayed in Java land!|||

Thanks for all the HELP!!!

Dan

basic question for sp

Hi,
I need to write a sp. In the sp first I do a select statement (select cycle
from table where ... ) it only returns a single value. and the rest of the s
p
will do different select based the value. how can assign the result to a
varable? ThanksJen,
Try:
DECLARE @.MYVAR INT --OR CHAR OR VARCHAR OR...DEPENDING ON VALUE
SELECT @.MYVAR = COL FROM...
HTH
Jerry
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:CB644854-6C94-48BC-8E45-9DC9A959F0C5@.microsoft.com...
> Hi,
> I need to write a sp. In the sp first I do a select statement (select
> cycle
> from table where ... ) it only returns a single value. and the rest of the
> sp
> will do different select based the value. how can assign the result to a
> varable? Thanks|||Something along the lines of:
SET @.var = ( SELECT ... ) ;
You will have to make sure the select statement returns a single value or
you will get an error.
Anith|||DECLARE @.Variable <type>
SELECT @.Variable = cycle FROM table WHERE ...
John Scragg
"Jen" wrote:

> Hi,
> I need to write a sp. In the sp first I do a select statement (select cycl
e
> from table where ... ) it only returns a single value. and the rest of the
sp
> will do different select based the value. how can assign the result to a
> varable? Thankssql

Basic evaluations in SELECT statement

Is there a way to determine several fields under one CASE or IF in a SELECT statement? For example, I don't think I can do this (though I'd like to):

@.Type INT

AS

SELECT

CASE @.Type

WHEN 1 THEN

Field1 = <some calculation>

Field2 = <Some calculation>

WHEN 2 THEN

Field1 = <some calculation>

Field2 = <some calculation>

END

The alternative, of course, is to evaluate @.Type twice, once for Field1 and again for Field2. But this seems like such a waste. Is there a better way?

Thanks,

One alternative might be to use an inline table -- maybe something like this:

declare @.type integer
set @.type = 2

select type,
field1,
field2
from ( select 1 as type, 1+3+5 as field1, 1*3*5 as field2 union all
select 2, 2+4+6, 2*4*6
) a
where type = @.type

/*
type field1 field2
-- -- --
2 12 48
*/

|||

Jararaca,

You are correct that a CASE can only return a single value.

So in YOUR case, you must use two CASE statements: one for Field1, and then another for Field2.

I think the speed of evaluation of the CASE usually exceeds the speed of retrieving the rows of data and/or formatting the result set.

Dan

|||

If only one condition value, How about this.

Code Snippet

If @.Type = 1

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

if @.Type = 2

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

|||

Another method of doing this if you are wanting to apply it to an entire table is something like this:

declare @.tString char(20)
set @.tString = convert(char(10), 2*4*6)
+ convert(char(10), 1*3*5)

declare @.demo table
( rid integer,
type tinyint
)
insert into @.demo
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2

select rid,
rtrim(substring (@.tString, 10*type-9, 10))
computedField
from @.demo

/*
rid computedField
-- -
1 48
2 15
3 48
4 48
5 15
*/

bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct results.
Therefore the problem does not appear to be with the ODBC driver but with the
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;
That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/default...b;en-us;280106
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;
|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('select
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/default...b;en-us;280106
>HTH
>Jerry
|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...[vbcol=seagreen]
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:
|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in the
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:[vbcol=seagreen]
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>[quoted text clipped - 19 lines]
|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via droptable.com" wrote:

> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
>
|||the results list is missing 99 unique new records, so that is not the answer.
WORKAROUND: MS techs were unable to solve this either, but what we did do was
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:[vbcol=seagreen]
>TJI: What is the possibility that the table on the Oracle side does not have
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>[quoted text clipped - 17 lines]
Message posted via http://www.droptable.com
|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>
> --
> Message posted via http://www.droptable.com

bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct result
s.
Therefore the problem does not appear to be with the ODBC driver but with th
e
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/defaul...kb;en-us;280106
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('selec
t
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/defaul...kb;en-us;280106
>HTH
>Jerry|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...[vbcol=seagreen]
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in th
e
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:[vbcol=seagreen]
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>[quoted text clipped - 19 lines]|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via droptable.com" wrote:

> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in
the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
>|||the results list is missing 99 unique new records, so that is not the answer
.
WORKAROUND: MS techs were unable to solve this either, but what we did do wa
s
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and
i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:[vbcol=seagreen]
>TJI: What is the possibility that the table on the Oracle side does not hav
e
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>[quoted text clipped - 17 lines]
Message posted via http://www.droptable.com|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via droptable.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>
> --
> Message posted via http://www.droptable.com

bad results from linked server to oracle

Using an openquery select statement against a linked server to oracle is
consistently yielding an incomplete result set. Using the Oracle Client, 225
records are returned, yet the SQL Server 2000 linked server returns only 126
records. Both statements were run using the same security credentials. The
linked server is connected to Oracle via odbc and as a test, i ran the same
statement against the same ODBC DSN in Access and did get the correct results.
Therefore the problem does not appear to be with the ODBC driver but with the
linked server itself.
Here is a representation of the tests i have done thus far:
in Sql server i am accessing the linked server using the OPENQUERY statement
as follows:
SELECT *
FROM OPENQUERY(oracledb, 'SELECT *
FROM ORACLETABLE') Rowset_1
running this statement has yielded 126 records
from ms access using the same ODBC system dsn i am getting 225 records with
this statement:SELECT *
FROM ORACLETABLE
and from the SQL Plus Oracle client i am getting 225 records with the
following statement:SELECT *
FROM ORACLETABLE;That is a bit weird. Have you tried using a different table? OPENROWSET
(as a test)? Drop/recreate the LS to Oracle?
Might look here as well:
How to set up and troubleshoot a linked server to Oracle in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56172ad97fe9e@.uwe...
> Using an openquery select statement against a linked server to oracle is
> consistently yielding an incomplete result set. Using the Oracle Client,
> 225
> records are returned, yet the SQL Server 2000 linked server returns only
> 126
> records. Both statements were run using the same security credentials. The
> linked server is connected to Oracle via odbc and as a test, i ran the
> same
> statement against the same ODBC DSN in Access and did get the correct
> results.
> Therefore the problem does not appear to be with the ODBC driver but with
> the
> linked server itself.
> Here is a representation of the tests i have done thus far:
> in Sql server i am accessing the linked server using the OPENQUERY
> statement
> as follows:
> SELECT *
> FROM OPENQUERY(oracledb, 'SELECT *
> FROM ORACLETABLE') Rowset_1
> running this statement has yielded 126 records
> from ms access using the same ODBC system dsn i am getting 225 records
> with
> this statement:SELECT *
> FROM ORACLETABLE
> and from the SQL Plus Oracle client i am getting 225 records with the
> following statement:SELECT *
> FROM ORACLETABLE;|||good suggestion. i did look at that msdn article earlier, i have not been
able to check all of the tables yet, but so far this behavior is not on all
of the tables. update from testing: if i run select * from openquery ('select
count(*) from oracletable') recordset_1 I do get the magic number 225.
I have also queried using a where statement for one of the missing records
and the missing record DOES return. so why it does not return using select *
is the mystery.
Jerry Spivey wrote:
>That is a bit weird. Have you tried using a different table? OPENROWSET
>(as a test)? Drop/recreate the LS to Oracle?
>Might look here as well:
>How to set up and troubleshoot a linked server to Oracle in SQL Server
>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>HTH
>Jerry|||NULLs on the other end? Might try COUNT(COLUMN) just to see what it
returns. I'd be looking at the column values for a row that doesn't come
back without the WHERE as compared to one that does.
HTH
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:56175b9b94e48@.uwe...
> good suggestion. i did look at that msdn article earlier, i have not been
> able to check all of the tables yet, but so far this behavior is not on
> all
> of the tables. update from testing: if i run select * from openquery
> ('select
> count(*) from oracletable') recordset_1 I do get the magic number 225.
> I have also queried using a where statement for one of the missing records
> and the missing record DOES return. so why it does not return using select
> *
> is the mystery.
>
> Jerry Spivey wrote:
>>That is a bit weird. Have you tried using a different table? OPENROWSET
>>(as a test)? Drop/recreate the LS to Oracle?
>>Might look here as well:
>>How to set up and troubleshoot a linked server to Oracle in SQL Server
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
>>HTH
>>Jerry|||Jerry,
no nulls on the other end. SELECT *
FROM OPENQUERY(oracledb, 'SELECTcount (*)
FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
if i put a where clause on it, i can get any of the 255 actual records in the
table, but i can't get them all from a general select statement.
Jerry Spivey wrote:
>NULLs on the other end? Might try COUNT(COLUMN) just to see what it
>returns. I'd be looking at the column values for a row that doesn't come
>back without the WHERE as compared to one that does.
>HTH
>Jerry
>> good suggestion. i did look at that msdn article earlier, i have not been
>> able to check all of the tables yet, but so far this behavior is not on
>[quoted text clipped - 19 lines]
>>Jerry|||TJI: What is the possibility that the table on the Oracle side does not have
a unique key and what you are seeing are the unique records?
I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"inteldsn via SQLMonster.com" wrote:
> Jerry,
> no nulls on the other end. SELECT *
> FROM OPENQUERY(oracledb, 'SELECTcount (*)
> FROM ORACLETABLE') Rowset_1 yields a result of 255 just as it should.
> if i put a where clause on it, i can get any of the 255 actual records in the
> table, but i can't get them all from a general select statement.
>
> Jerry Spivey wrote:
> >NULLs on the other end? Might try COUNT(COLUMN) just to see what it
> >returns. I'd be looking at the column values for a row that doesn't come
> >back without the WHERE as compared to one that does.
> >
> >HTH
> >
> >Jerry
> >> good suggestion. i did look at that msdn article earlier, i have not been
> >> able to check all of the tables yet, but so far this behavior is not on
> >[quoted text clipped - 19 lines]
> >>
> >>Jerry
>|||the results list is missing 99 unique new records, so that is not the answer.
WORKAROUND: MS techs were unable to solve this either, but what we did do was
to abandon using the provider for odbc to create the linked server. Here's
what i have learned so far. using the microsoft provider for odbc driver to
create the linked server, the linked server fails to provide consistently
correct results. I switched to using the provider for ole db for oracle and i
am now getting good results. However, you have to download the provider for
ole db from the oracle site in order to install this and get it working if
your oracle installation is anything more recent than 8.2. there are other
dependencies requiring additional software and configuration if you need to
use distributed transactions as well.
so basically, this is a flaw in the way sql server 2000 handles linked
servers created through the odbc provider.
jrpm wrote:
>TJI: What is the possibility that the table on the Oracle side does not have
>a unique key and what you are seeing are the unique records?
>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
Message posted via http://www.sqlmonster.com|||UGH!!! I should have asked you that. I had an issue using the ODBC driver
for ORACLE (similar but not the same) at a submarine base consult one time.
I dropped the LS and recreated it using the OLE-DB provider for ORACLE and
it worked fine.
Sorry didn't think of that sooner.
Jerry
"inteldsn via SQLMonster.com" <u15033@.uwe> wrote in message
news:5623fa2fc9ebd@.uwe...
> the results list is missing 99 unique new records, so that is not the
> answer.
>
> WORKAROUND: MS techs were unable to solve this either, but what we did do
> was
> to abandon using the provider for odbc to create the linked server. Here's
> what i have learned so far. using the microsoft provider for odbc driver
> to
> create the linked server, the linked server fails to provide consistently
> correct results. I switched to using the provider for ole db for oracle
> and i
> am now getting good results. However, you have to download the provider
> for
> ole db from the oracle site in order to install this and get it working
> if
> your oracle installation is anything more recent than 8.2. there are other
> dependencies requiring additional software and configuration if you need
> to
> use distributed transactions as well.
> so basically, this is a flaw in the way sql server 2000 handles linked
> servers created through the odbc provider.
> jrpm wrote:
>>TJI: What is the possibility that the table on the Oracle side does not
>>have
>>a unique key and what you are seeing are the unique records?
>>I have seen that OpenQuery/OLEDB has a preference for unique identifiers.
>> Jerry,
>>[quoted text clipped - 17 lines]
>> >>
>> >>Jerry
>
> --
> Message posted via http://www.sqlmonster.com

bad case statement?

CASE WHEN u.sImage1_nm is NULL then 0
ELSEWHEN u.sImage2_nm is NULL then 1
ELSEWHEN u.sImage3_nm is NULL then 2
ELSEWHEN u.sImage4_nm is NULL then 3
ELSEWHEN u.sImage5_nm is NULL then 4
end
-- what's wrong here?
Looks good to me. Can you show the whole (simplified) query? Or, better yet, a repro?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.c om...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?
|||Tibor
It is wrong syntax assuming that below query (he missed addition CASE and
END respectively)
select CASE WHEN EndDate is NULL then 0
ELSE CASE WHEN InDate is NULL then 1
ELSE CASE WHEN Stardate is NULL then 2
end END END
from table
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8UlrvdfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Looks good to me. Can you show the whole (simplified) query? Or, better
yet, a repro?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0408082134.496c2e97@.posting.google.c om...
>
|||The extra else's are what is killing you... Try
select
CASE
WHEN u.sImage1_nm is NULL then 0
WHEN u.sImage2_nm is NULL then 1
WHEN u.sImage3_nm is NULL then 2
WHEN u.sImage4_nm is NULL then 3
WHEN u.sImage5_nm is NULL then 4
end from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.c om...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?

bad case statement?

CASE WHEN u.sImage1_nm is NULL then 0
ELSE WHEN u.sImage2_nm is NULL then 1
ELSE WHEN u.sImage3_nm is NULL then 2
ELSE WHEN u.sImage4_nm is NULL then 3
ELSE WHEN u.sImage5_nm is NULL then 4
end
-- what's wrong here?Looks good to me. Can you show the whole (simplified) query? Or, better yet, a repro?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?|||Tibor
It is wrong syntax assuming that below query (he missed addition CASE and
END respectively)
select CASE WHEN EndDate is NULL then 0
ELSE CASE WHEN InDate is NULL then 1
ELSE CASE WHEN Stardate is NULL then 2
end END END
from table
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8UlrvdfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Looks good to me. Can you show the whole (simplified) query? Or, better
yet, a repro?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0408082134.496c2e97@.posting.google.com...
> > CASE WHEN u.sImage1_nm is NULL then 0
> > ELSE WHEN u.sImage2_nm is NULL then 1
> > ELSE WHEN u.sImage3_nm is NULL then 2
> > ELSE WHEN u.sImage4_nm is NULL then 3
> > ELSE WHEN u.sImage5_nm is NULL then 4
> > end
> >
> >
> > -- what's wrong here?
>|||The extra else's are what is killing you... Try
select
CASE
WHEN u.sImage1_nm is NULL then 0
WHEN u.sImage2_nm is NULL then 1
WHEN u.sImage3_nm is NULL then 2
WHEN u.sImage4_nm is NULL then 3
WHEN u.sImage5_nm is NULL then 4
end from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?