Calling sp_helptext from ASPNET

Hi,

I am executing a call to "sp_helptext" from a web service, in order to
obtain the text of a stored procedure. The call itself seems to be ok but
eventually it throws an exception saying that the specified object does not
exist in the database - which is a false statement, i've checked carefully.

I probably do not have enough rights to access something from the ASPNET
account.
Do i have to login as "sa"? Is this the only solution available?

Bogdan.Bogdan Rechi (bogdan_rechi@.hotmail.com) writes:
> I am executing a call to "sp_helptext" from a web service, in order to
> obtain the text of a stored procedure. The call itself seems to be ok
> but eventually it throws an exception saying that the specified object
> does not exist in the database - which is a false statement, i've
> checked carefully.

What sort of object is it? Not all object types have text.

> I probably do not have enough rights to access something from the ASPNET
> account.

Is this account owner of the database? If not, how do you specify the
object names? Do you include owner, as in

exec sp_helptext 'dho.my_proc'

In SQL 2000, access rights are not likely to be an issue, as all users
have rights to see metadata. SQL2005 is very different in that regard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Calling remote Stored Procedure's

Hi,
I am experiencing some difficulties when it comes to executing a SQL
Stored procedure on a remote SQL server.
I have two SQL servers.
1st Server: Named 'SQLServer' running on Win2k Server (SP4)
2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
When I try to execute the Stored Procedure 'Ten Most Expensive
Products' (from the Northwind database), from my client machine
(SQLClient), on the server machine (SQLServer).
For example.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo].[Ten
Most Expensive Products]
I get the following error.
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'Ten Most Expensive Products' on remote
server 'SQLOLEDB'.
However if I simply query a table directly (from the remote machine
'SQLClient'), all works fine. For example, the Customers table from
the Northwind database.
select * from OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo].Customers
I also tried executing the stored procedure from 'SQLServer', and that
worked fine too. eg.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD=;').[Northwind].[dbo].[Ten
Most Expensive Products]
I think the problem may have something to do with RPC permissions. Can
anyone shed some light on why this doesn't work, and/or how to fix it.
Thanks in advance.
Rick 8-)Rick
EXEC sp_serveroption SERVER, 'data access' , 'true'
select *
from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
Note: I assume you have already created linked server.
"Rick Knight" <knight_rjb@.yahoo.com.au> wrote in message
news:4b3eabf7.0411071804.6933fe62@.posting.google.com...
> Hi,
> I am experiencing some difficulties when it comes to executing a SQL
> Stored procedure on a remote SQL server.
> I have two SQL servers.
> 1st Server: Named 'SQLServer' running on Win2k Server (SP4)
> 2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
> When I try to execute the Stored Procedure 'Ten Most Expensive
> Products' (from the Northwind database), from my client machine
> (SQLClient), on the server machine (SQLServer).
> For example.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo
].[Ten
> Most Expensive Products]
> I get the following error.
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'Ten Most Expensive Products' on remote
> server 'SQLOLEDB'.
> However if I simply query a table directly (from the remote machine
> 'SQLClient'), all works fine. For example, the Customers table from
> the Northwind database.
> select * from
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo
].Customers
> I also tried executing the stored procedure from 'SQLServer', and that
> worked fine too. eg.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD=;').[Northwind].[dbo].[Ten
> Most Expensive Products]
> I think the problem may have something to do with RPC permissions. Can
> anyone shed some light on why this doesn't work, and/or how to fix it.
> Thanks in advance.
> Rick 8-)|||Hi,
Maybe I should give some background on the problem. I discovered a
problem when trying to get data to replicate from the Subscriber back
to the Publisher. I was able to determine that the Subscribers update
trigger (trg_MSsync_upd_<tablename>) was failing to execute the Update
Stored Procedure (sp_MSsync_upd_<tablename>_1) on the Publisher.
I used the Profiler, and determined that the trigger was failing when
executing the command;
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I tried to figure out why the execution of the remote stored
procedure wasn't working.
BTW: With regard to my original post, I found out that if I try and
execute a Stored Procedure from Server (SQLServer) to the client
(SQLClient), it worked fine. So the only problem is going from Client
to Server!
I hope this sheds more light on the problem.
Thanks in advance.
Rick 8-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#l31CxVxEHA.1392@.tk2msftngp13.phx.gbl>...
> Rick
> EXEC sp_serveroption SERVER, 'data access' , 'true'
> select *
> from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
> Note: I assume you have already created linked server.
>

Calling remote Stored Procedure's

Hi,
I am experiencing some difficulties when it comes to executing a SQL
Stored procedure on a remote SQL server.
I have two SQL servers.
1st Server: Named 'SQLServer' running on Win2k Server (SP4)
2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
When I try to execute the Stored Procedure 'Ten Most Expensive
Products' (from the Northwind database), from my client machine
(SQLClient), on the server machine (SQLServer).
For example.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').[Nor
thwind].[dbo].[Ten
Most Expensive Products]
I get the following error.
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'Ten Most Expensive Products' on remote
server 'SQLOLEDB'.
However if I simply query a table directly (from the remote machine
'SQLClient'), all works fine. For example, the Customers table from
the Northwind database.
select * from OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').&#
91;Northwind].[dbo].Customers
I also tried executing the stored procedure from 'SQLServer', and that
worked fine too. eg.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQL;U
ID=sa;PWD=;').[Northwind
].[dbo].[Ten
Most Expensive Products]
I think the problem may have something to do with RPC permissions. Can
anyone shed some light on why this doesn't work, and/or how to fix it.
Thanks in advance.
Rick 8-)Rick
EXEC sp_serveroption SERVER, 'data access' , 'true'
select *
from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]'
)
Note: I assume you have already created linked server.
"Rick Knight" <knight_rjb@.yahoo.com.au> wrote in message
news:4b3eabf7.0411071804.6933fe62@.posting.google.com...
> Hi,
> I am experiencing some difficulties when it comes to executing a SQL
> Stored procedure on a remote SQL server.
> I have two SQL servers.
> 1st Server: Named 'SQLServer' running on Win2k Server (SP4)
> 2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
> When I try to execute the Stored Procedure 'Ten Most Expensive
> Products' (from the Northwind database), from my client machine
> (SQLClient), on the server machine (SQLServer).
> For example.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').[Northwind].
[dbo
].[Ten
> Most Expensive Products]
> I get the following error.
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'Ten Most Expensive Products' on remote
> server 'SQLOLEDB'.
> However if I simply query a table directly (from the remote machine
> 'SQLClient'), all works fine. For example, the Customers table from
> the Northwind database.
> select * from
OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').[Northwind].
[dbo
].Customers
> I also tried executing the stored procedure from 'SQLServer', and that
> worked fine too. eg.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQL;U
ID=sa;PWD=;').[Northwind].[dbo].[Ten[vbc
ol=seagreen]
> Most Expensive Products]
> I think the problem may have something to do with RPC permissions. Can
> anyone shed some light on why this doesn't work, and/or how to fix it.
> Thanks in advance.
> Rick 8-)[/vbcol]|||Hi,
Maybe I should give some background on the problem. I discovered a
problem when trying to get data to replicate from the Subscriber back
to the Publisher. I was able to determine that the Subscribers update
trigger (trg_MSsync_upd_<tablename> ) was failing to execute the Update
Stored Procedure (sp_MSsync_upd_<tablename>_1) on the Publisher.
I used the Profiler, and determined that the trigger was failing when
executing the command;
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').
[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I tried to figure out why the execution of the remote stored
procedure wasn't working.
BTW: With regard to my original post, I found out that if I try and
execute a Stored Procedure from Server (SQLServer) to the client
(SQLClient), it worked fine. So the only problem is going from Client
to Server!
I hope this sheds more light on the problem.
Thanks in advance.
Rick 8-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#l31CxVxEHA.1392@.tk2msftngp13.phx.gbl
>...
> Rick
> EXEC sp_serveroption SERVER, 'data access' , 'true'
> select *
> from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products
]')
> Note: I assume you have already created linked server.
>sql

Calling remote Stored Procedure's

Hi,
I am experiencing some difficulties when it comes to executing a SQL
Stored procedure on a remote SQL server.
I have two SQL servers.
1st Server: Named 'SQLServer' running on Win2k Server (SP4)
2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
When I try to execute the Stored Procedure 'Ten Most Expensive
Products' (from the Northwind database), from my client machine
(SQLClient), on the server machine (SQLServer).
For example.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[Northwind].[dbo].[Ten
Most Expensive Products]
I get the following error.
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'Ten Most Expensive Products' on remote
server 'SQLOLEDB'.
However if I simply query a table directly (from the remote machine
'SQLClient'), all works fine. For example, the Customers table from
the Northwind database.
select * from OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[Northwind].[dbo].Customers
I also tried executing the stored procedure from 'SQLServer', and that
worked fine too. eg.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD= ;').[Northwind].[dbo].[Ten
Most Expensive Products]
I think the problem may have something to do with RPC permissions. Can
anyone shed some light on why this doesn't work, and/or how to fix it.
Thanks in advance.
Rick 8-)
Rick
EXEC sp_serveroption SERVER, 'data access' , 'true'
select *
from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
Note: I assume you have already created linked server.
"Rick Knight" <knight_rjb@.yahoo.com.au> wrote in message
news:4b3eabf7.0411071804.6933fe62@.posting.google.c om...
> Hi,
> I am experiencing some difficulties when it comes to executing a SQL
> Stored procedure on a remote SQL server.
> I have two SQL servers.
> 1st Server: Named 'SQLServer' running on Win2k Server (SP4)
> 2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
> When I try to execute the Stored Procedure 'Ten Most Expensive
> Products' (from the Northwind database), from my client machine
> (SQLClient), on the server machine (SQLServer).
> For example.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[Northwind].[dbo
].[Ten
> Most Expensive Products]
> I get the following error.
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'Ten Most Expensive Products' on remote
> server 'SQLOLEDB'.
> However if I simply query a table directly (from the remote machine
> 'SQLClient'), all works fine. For example, the Customers table from
> the Northwind database.
> select * from
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[Northwind].[dbo
].Customers
> I also tried executing the stored procedure from 'SQLServer', and that
> worked fine too. eg.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD= ;').[Northwind].[dbo].[Ten
> Most Expensive Products]
> I think the problem may have something to do with RPC permissions. Can
> anyone shed some light on why this doesn't work, and/or how to fix it.
> Thanks in advance.
> Rick 8-)
|||Hi,
Maybe I should give some background on the problem. I discovered a
problem when trying to get data to replicate from the Subscriber back
to the Publisher. I was able to determine that the Subscribers update
trigger (trg_MSsync_upd_<tablename>) was failing to execute the Update
Stored Procedure (sp_MSsync_upd_<tablename>_1) on the Publisher.
I used the Profiler, and determined that the trigger was failing when
executing the command;
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I tried to figure out why the execution of the remote stored
procedure wasn't working.
BTW: With regard to my original post, I found out that if I try and
execute a Stored Procedure from Server (SQLServer) to the client
(SQLClient), it worked fine. So the only problem is going from Client
to Server!
I hope this sheds more light on the problem.
Thanks in advance.
Rick 8-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#l31CxVxEHA.1392@.tk2msftngp13.phx.gbl>...
> Rick
> EXEC sp_serveroption SERVER, 'data access' , 'true'
> select *
> from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
> Note: I assume you have already created linked server.
>

Calling All T-SQL Gods & Goddesses

Hi

I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.

When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first page. Any
questions, please don't hesitate contacting me.

Thanks very much to anyone who's having a look at this!

Martin

SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
(i2b_address.Address1) AS PropertyAddress,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
ReturnByDate
FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESCOn 29 May 2004 12:19:20 -0700, Martin wrote:

>Hi
>I'm having trouble with the script below that it just won't do a
>correct ORDER BY for a date field.
>When executing the two SELECT TOP statements on their own the records
>are sorted correctly but when wrapped in the main statement, the ORDER
>BY is just ignored. Tried to play around with the script but with no
>luck. Script is for paging, in this case, select all records meeting
>criteria, order and select Top 25 to display on first page. Any
>questions, please don't hesitate contacting me.
>Thanks very much to anyone who's having a look at this!
>Martin
>SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
>i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
>i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
>(i2b_address.Address1) AS PropertyAddress,
>A.ProgUserName AS ProgUserName,
>CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
>TransactionDate,
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
>i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
> (SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC

Hi Martin,

I'm not entirely sure why you would use TOP 0 for the NOT IN subquery, but
that's not the cause of the problem you post about.

The ANSI standard for SQL allows only columns appearing in the SELECT list
in the ORDER BY clause. The rationale being that first, the query gets
executed and the result set is determined; the sorting is a seperate,
final stage. ANSI SQL allows things as ORDER BY 3 (3 being the third
column in the SELECT LIST) or SELECT MyColumn AS ColumnAlias ... ORDER BY
ColumnAlias.

MS SQL Server expands on this standard. With MS SQL Server, you can ORDER
BY on a column not in the SELECT list. If you use a column name that is
not an alias in the column list, SQL Server will try to locate a column
with that name in one of the tables used in the query and use that for
sorting.

Before I get back to your problem, run the following script. It will
clearly show that SQL Server will first attempt to resolve names in the
ORDER BY clause following ANSI standard before looking at the columns that
make up the data.

CREATE TABLE testtab (a int, b int)
go
INSERT testtab (a, b) SELECT 1, 20
INSERT testtab (a, b) SELECT 2, 10
go
SELECT b
FROM testtab
ORDER BY a
go
SELECT b AS a
FROM testtab
ORDER BY a
go
SELECT b AS a, a AS b
FROM testtab
ORDER BY a
go
DROP TABLE testtab
go

In the first query, no column "a" is in the SELECT list, so SQL Server
will sort on the column a in the table. In the second query, the SELECT
list has a column aliased as "a", so this is used for sorting. In the
third query, the column a itself is used in the select list; aliases are
disregarded and sorting is done on the contents of a.

Now back to your real problem. I'll show you the parts that cause the
unexpected ordering:

>SELECT (...)
> (...)
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> (...)
> ORDER BY ReturnByDate DESC

The column ReturnByDate is not used in the SELECT list (the results of a
function are not cosidered equal to the column itself). There is however
an alias ReturnByDate, so there's no need to get back to the data in the
table either - the aliased column in the result set is used. This column
is the output of a CONVERT function call. It's varchar(10), therefor the
sorting will be done by alphabet, descending. "23-04-2003" will go before
"06-05-2004".

The fix? Either use something else as an alias ([Return By Date], for
example) or use ORDER BY CONVERT(datetime, ReturnByDate).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)