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 on SQL SELECT Performance

Hi, I hope this is an appropriate group to ask a question about basic SQL
SELECT performance.
I'm creating a VIEW which draws together two tables; T_ONE and T_MANY.
Basically, the VIEW creates a LEFT JOIN from T_ONE to T_MANY so that the Ad
Hoc SQL submitted from the software is simply a SELECT * from the VIEW. (I
like to use views to keep software-embedded SQL as simple as possible.)
Now here's the thing. The item from the WHERE clause when selecting from
this view is from a column in T_MANY. Does this indicate that for
performance reasons I should place T_MANY on the left side of the JOIN? I've
always thought that relative JOIN placement defines logical join
relationship, but that the optimizer would intelligently decide upon the
quickest path to the data, regardless of where a particular table is placed
in the JOIN.
Disclaimer: I'm using a different DBMS right now, Interbase, and was getting
absolutely horrible performance until I switched T_MANY around to the left
side of the join. We're migrating to SQL Server and I'm wondering whether
what I'm seeing is actually a feature of SQL, or whether Interbase simply
doesn't 'have the brains' to optimize the query properly. Would this be a
problem in SQL Server as well? Would an INNER JOIN be more efficient than a
LEFT join (effectively, our relationship constraints mandate that these must
be INNER joins anyway - it's not possible for an entry to exist in either
table without at least one match in the other table) or should it make no
difference?
Thanks for your advice.
Joe GeretzDefintely Inner join is better then Outer Joins.
In the Query, is that you are only filtering the rows between the two tables
with a where clause or is it that you are trying to find out all the left
items and replacing them with values.
From my understanding you are trying to do a simple join. So please use
Inner Join and it should give you a good performance and make sure indexes
are done properly on the tables where the join is made. And if it contains
too many rows then collect statistics before you do the join.
Shyam
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OxNKHB4DEHA.1544@.TK2MSFTNGP09.phx.gbl...
> Hi, I hope this is an appropriate group to ask a question about basic SQL
> SELECT performance.
> I'm creating a VIEW which draws together two tables; T_ONE and T_MANY.
> Basically, the VIEW creates a LEFT JOIN from T_ONE to T_MANY so that the
Ad
> Hoc SQL submitted from the software is simply a SELECT * from the VIEW. (I
> like to use views to keep software-embedded SQL as simple as possible.)
> Now here's the thing. The item from the WHERE clause when selecting from
> this view is from a column in T_MANY. Does this indicate that for
> performance reasons I should place T_MANY on the left side of the JOIN?
I've
> always thought that relative JOIN placement defines logical join
> relationship, but that the optimizer would intelligently decide upon the
> quickest path to the data, regardless of where a particular table is
placed
> in the JOIN.
> Disclaimer: I'm using a different DBMS right now, Interbase, and was
getting
> absolutely horrible performance until I switched T_MANY around to the left
> side of the join. We're migrating to SQL Server and I'm wondering whether
> what I'm seeing is actually a feature of SQL, or whether Interbase simply
> doesn't 'have the brains' to optimize the query properly. Would this be a
> problem in SQL Server as well? Would an INNER JOIN be more efficient than
a
> LEFT join (effectively, our relationship constraints mandate that these
must
> be INNER joins anyway - it's not possible for an entry to exist in either
> table without at least one match in the other table) or should it make no
> difference?
> Thanks for your advice.
> Joe Geretz
>
>

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 query question

Hello,
I was wondering how best to write a valid query that
Assimilates the following query.
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
GROUP BY id;
basically it's a query that returns a recordset with
unique values for one of the alias fields
any suggestions greatly appreciatedYou can't use the column alias in the where clause, but if I understand your
query correctly, you don't need it.
SQL Server will only returns rows for id's which actually occur, so their
count will always be greater than 0.
Just use hte query you have without the WHERE clause.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"aylwin" <aylwinagena@.hotmail.com> wrote in message
news:00ed01c3939d$db056be0$a001280a@.phx.gbl...
> Hello,
> I was wondering how best to write a valid query that
> Assimilates the following query.
> SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
> GROUP BY id;
> basically it's a query that returns a recordset with
> unique values for one of the alias fields
> any suggestions greatly appreciated|||Hi thank you,
My actual query is different. my example is simplified.
My actual query is more like:
SELECT z.application_id as AppID, queued_by_user, queued_date,
'-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
WHERE suspendprinting = 0
and
a.customer_id = b.customer_id
and
b.application_id = z.application_id
--Group By AppID <<problem>>
ORDER BY first_name ASC
the query returns many AppID's. I was trying to use a Group By but there
seems to be a problem with the where clause.
any ideas' thanks again!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See my replies in the other group you posted to. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"aylwin agena" <aylwinagena@.hotmail.com> wrote in message
news:%23PWmhh6kDHA.2964@.tk2msftngp13.phx.gbl...
> Hi thank you,
> My actual query is different. my example is simplified.
> My actual query is more like:
> SELECT z.application_id as AppID, queued_by_user, queued_date,
> '-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
> FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
> WHERE suspendprinting = 0
> and
> a.customer_id = b.customer_id
> and
> b.application_id = z.application_id
> --Group By AppID <<problem>>
> ORDER BY first_name ASC
> the query returns many AppID's. I was trying to use a Group By but there
> seems to be a problem with the where clause.
> any ideas' thanks again!!!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||select id, count(*) as cnt from table_name
group by id having count(*) > 0|||The HAVING clause is unnecessary here as you won't ever ever count an id
value if it doesn't appear in the data, so the counts will always be
positive.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"bill" <anonymous@.discussions.microsoft.com> wrote in message
news:2DBC44C3-5418-4B4C-8B4F-CD18F62A9761@.microsoft.com...
> select id, count(*) as cnt from table_name
> group by id having count(*) > 0

Basic query question

Hi,
I have a basic query question and hope that someone can clarify for me.
2 queries...
#1
\\\
SELECT *
FROM
Table1 AS A
INNER JOIN Table2 AS B ON
B.Column1 = A.Column1 AND
B.Column2 = A.Column2
///
#2
\\\
SELECT *
FROM
Table1 AS A, Table2 AS B
WHERE
B.Column1 = A.Column1 AND
B.Column2 = A.Column2
///
I suppose I will get the same result among these 2 queries, I just wonder is
there any performance different between these 2 queries? Any opinion which
one is better?
Thanks in advance.it will really give you the same result
but as a matter of standard
i suggest you use the first one.
performance wise i dont know.
its just that the first one is my preferred
choice
<hr>
MCP #2324787
"Kent" wrote:

> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
is
> there any performance different between these 2 queries? Any opinion whic
h
> one is better?
> Thanks in advance.|||AFAIK, there will be no performance difference between these two
particular queries; the optimizer should recognize them as being the
same and generate an identical execution plan for them (something which
you can easily test in Query Analyzer).
I prefer the JOIN syntax because it
a) is easier to read (to me), and
b) allows you to perform more complex joins. You can easily join three
tables with an INNER JOIN between two of those tables, and an OUTER
JOIN with the last table.
HTH,
Stu|||Kent,
This issue is a old issue and I think that our gurus here could answer you
with guarantee. From my own point of view it's the same although I prefer th
e
first one.
Regards,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> it will really give you the same result
> but as a matter of standard
> i suggest you use the first one.
> performance wise i dont know.
> its just that the first one is my preferred
> choice
> --
>
> <hr>
> MCP #2324787
>
> "Kent" wrote:
>|||Kent
No, should not be any differences bettwen two queries. See an execution
plan to make sure.
"Kent" <Kent@.discussions.microsoft.com> wrote in message
news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
> is
> there any performance different between these 2 queries? Any opinion
> which
> one is better?
> Thanks in advance.|||The optimizer will recognize these doing the same thing and optimize them th
e same way. The first is
preferred and the more modern syntax.
Outer joins is another story, though... In short, don't use the old ( *= ) s
yntax.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kent" <Kent@.discussions.microsoft.com> wrote in message
news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
is
> there any performance different between these 2 queries? Any opinion whic
h
> one is better?
> Thanks in advance.|||Ya, they are the same in Execution Plan.
I think the first one is more neat when I get to join more tables.
Thanks all for help. :)
"Tibor Karaszi" wrote:

> The optimizer will recognize these doing the same thing and optimize them
the same way. The first is
> preferred and the more modern syntax.
> Outer joins is another story, though... In short, don't use the old ( *= )
syntax.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kent" <Kent@.discussions.microsoft.com> wrote in message
> news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
>

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
*/

Basic Aggregate/HAVING question

I want to find the order that has the largest quanity in pubs.dbo.sales
If I try to use an aggregate in the WHERE clause I get an error
--
SELECT ord_num
FROM pubs.dbo.sales
WHERE qty=max(qty)
/*
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause
or a select list, and the column being aggregated is an outer reference.
*/
But I am almost certain that I used to be able to do this in Sybase T-SQL
and it worked:
SELECT ord_num
FROM pubs.dbo.sales
HAVING qty=max(qty)
Now in MS T-SQL I get these errors:
/*
Column 'sales.ord_num' is invalid in the select list because it is not
contained in an aggregate function
and there is no GROUP BY clause.
Column 'sales.qty' is invalid in the HAVING clause because it is not
contained in an aggregate function
and there is no GROUP BY clause.
*/
It looks like I can get what I want by either one of two ways
1. A subquery:
SELECT ord_num, qty
FROM pubs.dbo.sales
WHERE qty=(SELECT max(qty) FROM sales)
/*
ord_num qty
-- --
QA7442.3 75
*/
2. A GROUP BY with TOP
SELECT TOP 1 ord_num, max(qty) AS 'qty'
FROM pubs.dbo.sales
GROUP BY ord_num
ORDER BY max(qty) DESC
/*
ord_num qty
-- --
QA7442.3 75
*/
I have 2 questions:
1. Are there other (better) ways to write this?
2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?> But I am almost certain that I used to be able to do this in Sybase T-SQL
> and it worked:
> SELECT ord_num
> FROM pubs.dbo.sales
> HAVING qty=max(qty)
Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause is
applied after the aggregation so it doesn't make sense to reference a base
table column in this way unless it exists in the aggregate result.
> 1. Are there other (better) ways to write this?
I doubt it's any "better" but just for variety here's an alternative:
SELECT ord_num, qty
FROM Sales
WHERE qty >= ALL
(SELECT qty
FROM Sales)
I prefer the WHERE qty= subquery method.
> 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?
Yes. HAVING without GROUP BY implies an aggregation across the whole set in
the same way that an aggregate function does without GROUP BY. The result
will be, at most, one row. You can use HAVING to return a value only if some
condition is met:
SELECT MIN(ord_date)
FROM Sales
HAVING MIN(ord_date)<='20000101'
And it can be especially useful in conjunction with EXISTS:
...
EXISTS -- Is ord_date unique?
(SELECT 1
FROM Sales
HAVING COUNT(DISTINCT ord_date)=COUNT(*))
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David for the detailed answers.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:H4idnZ3fHI4t8lOi4p2dnA@.giganews.com...
> > But I am almost certain that I used to be able to do this in Sybase
T-SQL
> > and it worked:
> >
> > SELECT ord_num
> > FROM pubs.dbo.sales
> > HAVING qty=max(qty)
> Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
> syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause
is
> applied after the aggregation so it doesn't make sense to reference a base
> table column in this way unless it exists in the aggregate result.
>
> > 1. Are there other (better) ways to write this?
> I doubt it's any "better" but just for variety here's an alternative:
> SELECT ord_num, qty
> FROM Sales
> WHERE qty >= ALL
> (SELECT qty
> FROM Sales)
> I prefer the WHERE qty= subquery method.
>
> > 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so,
when?
> Yes. HAVING without GROUP BY implies an aggregation across the whole set
in
> the same way that an aggregate function does without GROUP BY. The result
> will be, at most, one row. You can use HAVING to return a value only if
some
> condition is met:
> SELECT MIN(ord_date)
> FROM Sales
> HAVING MIN(ord_date)<='20000101'
> And it can be especially useful in conjunction with EXISTS:
> ...
> EXISTS -- Is ord_date unique?
> (SELECT 1
> FROM Sales
> HAVING COUNT(DISTINCT ord_date)=COUNT(*))
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

Base64

Franck Fouache wrote:
> Hi,
> is there a way to extract data in base64 ...
> i'd like to do something like : select cast(A_PHOTO as binary base 64) from
> article
> does anyone know a way to do it.
What's the version of SQL Server?
In SQL Server 2005 I would implement a simple scalar CLR function
returning base64 encoded string. The function could be used in that way:
SELECT dbo.base64_encode(A_PHOTO) from article
In SQL Server 2000 CLR integration is not available.
In that case please refer to:
http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp
Best regards,
Marcin Guzowski
http://guzowski.info
"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:em986e$mkl$1@.atlantis.news.tpi.pl...
> In SQL Server 2000 CLR integration is not available.
> In that case please refer to:
> http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp
>
There's an updated version at
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp
as well.

Base64

Hi,
is there a way to extract data in base64 ...
i'd like to do something like : select cast(A_PHOTO as binary base 64) from
article
does anyone know a way to do it.
Best regards.Franck Fouache wrote:
> Hi,
> is there a way to extract data in base64 ...
> i'd like to do something like : select cast(A_PHOTO as binary base 64) fro
m
> article
> does anyone know a way to do it.
What's the version of SQL Server?
In SQL Server 2005 I would implement a simple scalar CLR function
returning base64 encoded string. The function could be used in that way:
SELECT dbo.base64_encode(A_PHOTO) from article
In SQL Server 2000 CLR integration is not available.
In that case please refer to:
http://www.sqlservercentral.com/col...eencryption.asp
Best regards,
Marcin Guzowski
http://guzowski.info|||"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:em986e$mkl$1@.atlantis.news.tpi.pl...
> In SQL Server 2000 CLR integration is not available.
> In that case please refer to:
> http://www.sqlservercentral.com/col...eencryption.asp
>
There's an updated version at
http://www.sqlservercentral.com/col...oolkitpart4.asp
as well.sql

Base64

Hi,
is there a way to extract data in base64 ...
i'd like to do something like : select cast(A_PHOTO as binary base 64) from
article
does anyone know a way to do it.
Best regards.Franck Fouache wrote:
> Hi,
> is there a way to extract data in base64 ...
> i'd like to do something like : select cast(A_PHOTO as binary base 64) from
> article
> does anyone know a way to do it.
What's the version of SQL Server?
In SQL Server 2005 I would implement a simple scalar CLR function
returning base64 encoded string. The function could be used in that way:
SELECT dbo.base64_encode(A_PHOTO) from article
In SQL Server 2000 CLR integration is not available.
In that case please refer to:
http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp
Best regards,
Marcin Guzowski
http://guzowski.info|||"Marcin A. Guzowski" <tu_wstaw_moje_imie@.guzowski.info> wrote in message
news:em986e$mkl$1@.atlantis.news.tpi.pl...
> In SQL Server 2000 CLR integration is not available.
> In that case please refer to:
> http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp
>
There's an updated version at
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp
as well.

Bad Syntax

I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)

Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent

Thank you for any help,Thanks anyway, I got it. Long Day.

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 executed Plan and wrong Result by SQL

Hi, I didn't receive any answer about my problem. I'll repeat that!:
I have one query:
SELECT count(*) FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G WHERE G.PEG= 752074
AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
This query, after many updates after rebuid at saturday, return column
E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
merger join with hash aggregate, that too lazy. I test this at monday(one day
after rebuil), but the result is correct, but when that query are executed at
middle of week, change some G.PEG values, the result is total wrong.
Plese, anyone help me to this question!Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan
> mount merger join with hash aggregate, that too lazy. I test this at
> monday(one day after rebuil), but the result is correct, but when
> that query are executed at middle of week, change some G.PEG values,
> the result is total wrong.
> Plese, anyone help me to this question!
Could be a parallel plan issue. When using COUNT(*) it can help to add a
MAXDOP 1 to the query to avoid the possible issue:
http://support.microsoft.com/kb/822746
http://support.microsoft.com/kb/q277738/
--
David Gugick
Imceda Software
www.imceda.com|||In addition to David's reply: Service Pack 4 included a few fixes with
respect to parallelism, so installing SP4 could solve your problem.
Gert-Jan
Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
> merger join with hash aggregate, that too lazy. I test this at monday(one day
> after rebuil), but the result is correct, but when that query are executed at
> middle of week, change some G.PEG values, the result is total wrong.
> Plese, anyone help me to this question!