Doubt

I need know what is field equal suid from SQL 7.0 and not exist in
SQL 2000.Use suid.|||What are you trying to do ?|||I wanna mean sid. That column exists in sql7.0 too, but the suid dont exist in 2k now.|||SELECT suid, name, dbname, Admin = sysadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator
FROM master..syslogins WHERE loginname = ' & vfdstrLoginID_App & '

I want the the field equal suid.|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

syslogins.suid --> syslogins.sid
sysdatabases.suid --> sysdatabases.sid
sysremotelogins.suid --> sysremotelogins.sid
sysusers.suid --> sysusers.sid
sysalternates.suid --> sysusers.isaliased
sysalternates.altsuid --> sysusers.isaliased

The related niladic functions also have changed: (the old ones are now "broken")
SUSER_ID () --> SUSER_SID (), returns the user sid given the user name
SUSER_NAME () --> SUSER_SNAME (), returns the user name given the sid

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

SELECT
SUSER_SNAME(syslogins.sid) AS '"user name"',
syslogins.loginname,
syslogins.sid,
syslogins.name,
syslogins.dbname,
syslogins.hasaccess,
syslogins.bulkadmin,
syslogins.dbcreator,
syslogins.diskadmin
FROM
syslogins
INNER JOIN
sysusers
ON
syslogins.sid = sysusers.sid|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

For another example see: http://dbforums.com/t561459.html|||syslogins.suid --> syslogins.sid
Binary Hexadecimal

Not is funcionaly in my programation? I want
retorn a value binary, with is the suid ald what retorn
the value sequential of numbers.|||Check out the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;en-us;104829)|||An MSDN link on SUSER_ID: (may be helpful?)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_00mc.asp
As stated in the link: "SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. In SQL Server 7.0, the security identification number (SID) replaces the server user identification number (SUID). SUSER_ID always returns NULL when used in Microsoft SQL Server 2000."|||Okay.
SELECT SUSER_ID ('sa') in SQL7.0
--
1 --> Binary
(1 row(s) affected)

SELECT SUSER_SID ('sa') in SQL2000
--
NULL

(1 row(s) affected)
OR
SELECT SUSER_SID('sa')
--
0x01 -- Hexa
(1 row(s) affected)

I want number sequential and binary per users, understand?|||Q1 I want number sequential and binary per users, understand?

A1 Maybe, (for the most part suid columns were used in joins to return information). Apparently your requirement is different.

Q1 Are you saying the application you are working with requires a 1s and 0s representation of Binary numbers? for example:

User IntRep HexRep Binary
Tom 10 0xA 1010
sa 1 0x1 0001

If you need to use the string '1010' for user Tom instead of 0xA, you could create a function or stored procedure to return 1010 (given a hex representation)?|||What are you trying to do (deja vu) ?

Domain Error variation

Hi there,
I am having problems generating the Geomean using "EXP(AVG(LOG()))" in
SQL Server 2005.
I have looked at the common causes of this, namely LOG(0) returning
NULL and hence causing the problem, but I have queried this out of the
test data and all values being used are greater than 0.
Another interesting point to note is that the query (which I'll post
below) performs this calculation over a grouping of values from
different tables, when I add a level of indirection to this (create a
temporary view of the grouping) it seems to work and returns no
problems this was why I threw the Log(0) theory for this problem out
the window.
I think it could be a rounding problem, but this same query ran fine on
SQL Server 2000 (once we set the flags correctly "SET ARITHABORT ON,
NUMERIC_ROUNDABORT OFF"), and am wondering if it is something wrong
with SQL Server 2005, the version I am using is 9.0.1399 and accessing
it through the Management Studio.
It is obviously very hard to post the test data to give you an accurate
way of reproducing it, but I was hoping that someone could place their
finger on the problem by having a look at the SQL.
SELECT
TEST_BATCH.MACHINE_ID AS MACHINE_ID,
TEST_BATCH.TEST_DATE AS TEST_DATE,
TEST.TEST_TYPE_ID AS TEST_TYPE_ID,
EXP(AVG(LOG(TEST.RESULT))) AS MEAN_RESULT,
COUNT(TEST.TEST_ID) AS RESULT_COUNT
FROM
TEST_BATCH
JOIN
TEST_PROCEDURE
ON
(TEST_BATCH.TEST_BATCH_ID = TEST_PROCEDURE.TEST_BATCH_ID)
JOIN
TEST
ON
(TEST_PROCEDURE.TEST_PROCEDURE_ID = TEST.TEST_PROCEDURE_ID)
JOIN
TEST_TYPE
ON
(TEST.TEST_TYPE_ID = TEST_TYPE.TEST_TYPE_ID)
JOIN
MACHINE_TEST_TYPE
ON
(TEST_BATCH.MACHINE_ID = MACHINE_TEST_TYPE.MACHINE_ID) AND
(TEST_TYPE.TEST_TYPE_ID = MACHINE_TEST_TYPE.TEST_TYPE_ID)
WHERE
(TEST_BATCH.MACHINE_ID IS NOT NULL) AND
(TEST_BATCH.TEST_DATE BETWEEN @.START_TEST_DATE AND
@.END_TEST_DATE) AND
(TEST_PROCEDURE.AUTO_REGISTRATION_TEST_REGIME_ID IS NULL) AND
(TEST_PROCEDURE.SAMPLE_TYPE = 'N') AND
(TEST_PROCEDURE.SAMPLE_ID IS NOT NULL) AND
(TEST_PROCEDURE.IS_SUPERSEDED = 'F') AND
(TEST_TYPE.RESULT_MEAN_TYPE = 'G') AND
(TEST.IS_MISSING_RESULT = 'F')
GROUP BY
TEST_BATCH.MACHINE_ID,
TEST_BATCH.TEST_DATE,
TEST.TEST_TYPE_ID
this query is correct with the data model that we have developed, and
as I say it has previously worked.
Many thanks for any light that can be shed on this
AlastairAlastair,
If you replace the offending expression with
MAX(CASE WHEN TEST.RESULT = 0e0 THEN 0 ELSE NULL END)
do you get NULL or 0? What is the data type of
TEST.RESULT?
I can't test anything on SQL Server 2005 until later, but
I suggest you try this as a workaround:
EXP(AVG(LOG(CASE WHEN TEST.RESULT <= 0 THEN NULL END)))
If there are any 0 values, it's possible that in one
query plan they are filtered out before LOG() is applied,
and in another they are filtered afterwards. The CASE
expression prevents them from causing a problem, regardless
of the actual query execution plan.
If that still fails, you could see what results you get if
you select things like
SUM(TEST.RESULT), MAX(LOG(TEST.RESULT)), and so on, to
consider whether the problem is really with LOG, and whether
there is some overflow somewhere.
What is the distribution of data like? What are the MIN, MAX,
and COUNT of TEST.RESULT, for example?
Steve Kass
Drew University
Alastair wrote:

> Hi there,
> I am having problems generating the Geomean using "EXP(AVG(LOG()))" in
> SQL Server 2005.
> I have looked at the common causes of this, namely LOG(0) returning
> NULL and hence causing the problem, but I have queried this out of the
> test data and all values being used are greater than 0.
> Another interesting point to note is that the query (which I'll post
> below) performs this calculation over a grouping of values from
> different tables, when I add a level of indirection to this (create a
> temporary view of the grouping) it seems to work and returns no
> problems this was why I threw the Log(0) theory for this problem out
> the window.
> I think it could be a rounding problem, but this same query ran fine on
> SQL Server 2000 (once we set the flags correctly "SET ARITHABORT ON,
> NUMERIC_ROUNDABORT OFF"), and am wondering if it is something wrong
> with SQL Server 2005, the version I am using is 9.0.1399 and accessing
> it through the Management Studio.
> It is obviously very hard to post the test data to give you an accurate
> way of reproducing it, but I was hoping that someone could place their
> finger on the problem by having a look at the SQL.
> SELECT
> TEST_BATCH.MACHINE_ID AS MACHINE_ID,
> TEST_BATCH.TEST_DATE AS TEST_DATE,
> TEST.TEST_TYPE_ID AS TEST_TYPE_ID,
> EXP(AVG(LOG(TEST.RESULT))) AS MEAN_RESULT,
> COUNT(TEST.TEST_ID) AS RESULT_COUNT
> FROM
> TEST_BATCH
> JOIN
> TEST_PROCEDURE
> ON
> (TEST_BATCH.TEST_BATCH_ID = TEST_PROCEDURE.TEST_BATCH_ID)
> JOIN
> TEST
> ON
> (TEST_PROCEDURE.TEST_PROCEDURE_ID = TEST.TEST_PROCEDURE_ID)
> JOIN
> TEST_TYPE
> ON
> (TEST.TEST_TYPE_ID = TEST_TYPE.TEST_TYPE_ID)
> JOIN
> MACHINE_TEST_TYPE
> ON
> (TEST_BATCH.MACHINE_ID = MACHINE_TEST_TYPE.MACHINE_ID) AND
> (TEST_TYPE.TEST_TYPE_ID = MACHINE_TEST_TYPE.TEST_TYPE_ID)
> WHERE
> (TEST_BATCH.MACHINE_ID IS NOT NULL) AND
> (TEST_BATCH.TEST_DATE BETWEEN @.START_TEST_DATE AND
> @.END_TEST_DATE) AND
> (TEST_PROCEDURE.AUTO_REGISTRATION_TEST_REGIME_ID IS NULL) AND
> (TEST_PROCEDURE.SAMPLE_TYPE = 'N') AND
> (TEST_PROCEDURE.SAMPLE_ID IS NOT NULL) AND
> (TEST_PROCEDURE.IS_SUPERSEDED = 'F') AND
> (TEST_TYPE.RESULT_MEAN_TYPE = 'G') AND
> (TEST.IS_MISSING_RESULT = 'F')
> GROUP BY
> TEST_BATCH.MACHINE_ID,
> TEST_BATCH.TEST_DATE,
> TEST.TEST_TYPE_ID
> this query is correct with the data model that we have developed, and
> as I say it has previously worked.
> Many thanks for any light that can be shed on this
> Alastair
>|||Hi Steve, thanks for the reply.
We chjecked all the MIN and MAX results and there is nothing out of the
ordinary that SQL Server couldn't handle, such as the case that we were
able to create an intermediate table an then perform the calculation on
that.

> What is the data type of TEST.RESULT?
NUMERIC(10, 4)
We did manage to solve the problem using your suggestion of a CASE
statement and also used (TEST.RESULT > 0) in the WHERE clause (although
again I'll state we know that the results are never 0 or below). It
seems that the if you do not explicitly state you do not want 0 or
below in your results then SQL Server seems to throw a wobbly and use
"Domain Error" as an excuse when using the LOG function.
Thanks for your help.
Alastair