[SQL 2005] SQLCLR UDT Comparison Methods

OK, so I'm creating a simple UDT. I want to expose a method that performs a
comparison of two objects of the type of the UDT and returns a "boolean"
value based on the result that SQL Server can use in T-SQL statements and
expressions. I understand you can't override comparison operators like =
and <>. Ideally I'd like to achieve something like the following:
DECLARE @.x MyUDT
DECLARE @.y MyUDT
SELECT @.x = 'Test Value'
SELECT @.y = 'Test Value'
IF @.x.IsEqual(@.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Or alternatively:
IF MyUDT::IsEqual(@.x, @.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Anyone done this, or can point me to someplace with a code sample?
Thanks in advance.
Almost forgot to mention. IsByteOrdered is not really an option, since this
particular type is an unordered space and <, > operators make no sense on
it. = and <> are valid operations on this type.
Thanks
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like =
> and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>
|||Mike
You want to compare two values , is it possible that
SELECT @.x = 'Test value'
SELECT @.y = 'Test Value'
So , you need to PRINT 'Not Equal' , am I right?
Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
SELECT a.ID, a.CheckSum
From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsA ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsB ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like =
> and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>
|||Uri,
I want to do it in a SQLCLR UDT by exposing a comparison method. I could
avoid the checksum functions and just use IF @.x.ToString = @.y.ToString
directly; or create a UDF to do the comparison. I'd rather expose a method
of the UDT so I can encapsulate the logic all in one spot and avoid
conversions and additional calculations/manipulations.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Mike
> You want to compare two values , is it possible that
> SELECT @.x = 'Test value'
> SELECT @.y = 'Test Value'
>
> So , you need to PRINT 'Not Equal' , am I right?
>
> Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
>
> SELECT a.ID, a.CheckSum
> From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsA ) a
> Inner Join (
> Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsB ) b
> On a.ID = b.ID
> Where a.CheckSum != b.CheckSum
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
>
|||Mike C# (xyz@.xyz.com) writes:
> OK, so I'm creating a simple UDT. I want to expose a method that
> performs a comparison of two objects of the type of the UDT and returns
> a "boolean" value based on the result that SQL Server can use in T-SQL
> statements and expressions. I understand you can't override comparison
> operators like = and <>. Ideally I'd like to achieve something like the
> following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
Apart from that the syntax most certainly would have to be
IF @.x.IsEqual(@.y) = 1
what is really your problem?
In the Point sample in Books Online, there is a method Distance where the
input is another point. See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hello Mike,
I would also suggest not to implement this as a UDT method but a seperate
function. The reason is that once you have the udt used changing the assembly
is very difficult. If you have a bug in your comparision method or you want
to change it slightly this is very difficult. Having it as a seperate assembly
is much easier to maintain.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Uri,
> I want to do it in a SQLCLR UDT by exposing a comparison method. I
> could avoid the checksum functions and just use IF @.x.ToString =
> @.y.ToString directly; or create a UDF to do the comparison. I'd
> rather expose a method of the UDT so I can encapsulate the logic all
> in one spot and avoid conversions and additional
> calculations/manipulations.
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
|||"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a378838c8f04b36aae4dc@.msnews.microsoft .com...
> Hello Mike,
> I would also suggest not to implement this as a UDT method but a seperate
> function. The reason is that once you have the udt used changing the
> assembly is very difficult. If you have a bug in your comparision method
> or you want to change it slightly this is very difficult. Having it as a
> seperate assembly is much easier to maintain.
...Not what I'm interested in doing at all. But thanks.
|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
> Apart from that the syntax most certainly would have to be
> IF @.x.IsEqual(@.y) = 1
> what is really your problem?
My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
returns or compares a boolean value. My problem is that there appears to be
no way to use a SqlBoolean return value from a method in an IF statement. I
want to know if that is correct or not. Is SqlBoolean of any use whatsoever
in the context of a UDT?

> In the Point sample in Books Online, there is a method Distance where the
> input is another point. See
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Unfortunately I'm not interested in returning a numeric result of a
calculation on two UDTs. I'm interested in comparing two UDT's that are not
byte-ordered for equality. I notice that the MS Point sample is
byte-ordered; does this indicate that a point (X, Y+1000000) is actually
"less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
remove byte-ordering (since byte-ordering is inapplicable in my case), I
cannot use the T-SQL "=" comparison operator on two variables of the UDT's
type. The UDT is Format.Native.
If you decide for some reason that "less than" and "greater than" have no
meaning in regards to your UDT, and remove the byte-ordering, what's the
best way to test them for equality/inequality? Is returning arbitrary "flag
values" from methods and comparing those flag values for equality to
constants the only way? Is there no way to return a boolean value (aka,
SqlBoolean) and use it in an IF statement?
|||Simon Sabin (SimonSabin@.noemail.noemail) writes:
> I would also suggest not to implement this as a UDT method but a
> seperate function. The reason is that once you have the udt used
> changing the assembly is very difficult. If you have a bug in your
> comparision method or you want to change it slightly this is very
> difficult. Having it as a seperate assembly is much easier to maintain.
It was a while since I played with it, but as long as you only change the
implementation of methods and properties, ALTER ASSEMBLY works great. But
if you change the physical storage of the type, then it indeed it's painful.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Mike C# (xyz@.xyz.com) writes:
> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
> returns or compares a boolean value. My problem is that there appears
> to be no way to use a SqlBoolean return value from a method in an IF
> statement.
There is:
IF @.x.IsEqual(@.y) = 1
Of if you prefer:
IF @.x.IsEqual(@.y) = 'true'
SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
how you use bit in T-SQL. T-SQL is funny, because while it has boolean
expressions, it does not have a boolean data type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[SQL 2005] SQLCLR UDT Comparison Methods

OK, so I'm creating a simple UDT. I want to expose a method that performs a
comparison of two objects of the type of the UDT and returns a "boolean"
value based on the result that SQL Server can use in T-SQL statements and
expressions. I understand you can't override comparison operators like =
and <>. Ideally I'd like to achieve something like the following:
DECLARE @.x MyUDT
DECLARE @.y MyUDT
SELECT @.x = 'Test Value'
SELECT @.y = 'Test Value'
IF @.x.IsEqual(@.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Or alternatively:
IF MyUDT::IsEqual(@.x, @.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Anyone done this, or can point me to someplace with a code sample?
Thanks in advance.Almost forgot to mention. IsByteOrdered is not really an option, since this
particular type is an unordered space and <, > operators make no sense on
it. = and <> are valid operations on this type.
Thanks
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like =
> and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Mike
You want to compare two values , is it possible that
SELECT @.x = 'Test value'
SELECT @.y = 'Test Value'
So , you need to PRINT 'Not Equal' , am I right?
Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
SELECT a.ID, a.CheckSum
From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsA ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsB ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like =
> and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Uri,
I want to do it in a SQLCLR UDT by exposing a comparison method. I could
avoid the checksum functions and just use IF @.x.ToString = @.y.ToString
directly; or create a UDF to do the comparison. I'd rather expose a method
of the UDT so I can encapsulate the logic all in one spot and avoid
conversions and additional calculations/manipulations.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Mike
> You want to compare two values , is it possible that
> SELECT @.x = 'Test value'
> SELECT @.y = 'Test Value'
>
> So , you need to PRINT 'Not Equal' , am I right?
>
> Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
>
> SELECT a.ID, a.CheckSum
> From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsA ) a
> Inner Join (
> Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsB ) b
> On a.ID = b.ID
> Where a.CheckSum != b.CheckSum
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
>|||Mike C# (xyz@.xyz.com) writes:
> OK, so I'm creating a simple UDT. I want to expose a method that
> performs a comparison of two objects of the type of the UDT and returns
> a "boolean" value based on the result that SQL Server can use in T-SQL
> statements and expressions. I understand you can't override comparison
> operators like = and <>. Ideally I'd like to achieve something like the
> following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
Apart from that the syntax most certainly would have to be
IF @.x.IsEqual(@.y) = 1
what is really your problem?
In the Point sample in Books Online, there is a method Distance where the
input is another point. See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f
535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Mike,
I would also suggest not to implement this as a UDT method but a seperate
function. The reason is that once you have the udt used changing the assembl
y
is very difficult. If you have a bug in your comparision method or you want
to change it slightly this is very difficult. Having it as a seperate assemb
ly
is much easier to maintain.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Uri,
> I want to do it in a SQLCLR UDT by exposing a comparison method. I
> could avoid the checksum functions and just use IF @.x.ToString =
> @.y.ToString directly; or create a UDF to do the comparison. I'd
> rather expose a method of the UDT so I can encapsulate the logic all
> in one spot and avoid conversions and additional
> calculations/manipulations.
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a378838c8f04b36aae4dc@.msnews
.microsoft.com...
> Hello Mike,
> I would also suggest not to implement this as a UDT method but a seperate
> function. The reason is that once you have the udt used changing the
> assembly is very difficult. If you have a bug in your comparision method
> or you want to change it slightly this is very difficult. Having it as a
> seperate assembly is much easier to maintain.
...Not what I'm interested in doing at all. But thanks.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
> Apart from that the syntax most certainly would have to be
> IF @.x.IsEqual(@.y) = 1
> what is really your problem?
My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
returns or compares a boolean value. My problem is that there appears to be
no way to use a SqlBoolean return value from a method in an IF statement. I
want to know if that is correct or not. Is SqlBoolean of any use whatsoever
in the context of a UDT?

> In the Point sample in Books Online, there is a method Distance where the
> input is another point. See
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2a
c722.htm
Unfortunately I'm not interested in returning a numeric result of a
calculation on two UDTs. I'm interested in comparing two UDT's that are not
byte-ordered for equality. I notice that the MS Point sample is
byte-ordered; does this indicate that a point (X, Y+1000000) is actually
"less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
remove byte-ordering (since byte-ordering is inapplicable in my case), I
cannot use the T-SQL "=" comparison operator on two variables of the UDT's
type. The UDT is Format.Native.
If you decide for some reason that "less than" and "greater than" have no
meaning in regards to your UDT, and remove the byte-ordering, what's the
best way to test them for equality/inequality? Is returning arbitrary "flag
values" from methods and comparing those flag values for equality to
constants the only way? Is there no way to return a boolean value (aka,
SqlBoolean) and use it in an IF statement?|||Simon Sabin (SimonSabin@.noemail.noemail) writes:
> I would also suggest not to implement this as a UDT method but a
> seperate function. The reason is that once you have the udt used
> changing the assembly is very difficult. If you have a bug in your
> comparision method or you want to change it slightly this is very
> difficult. Having it as a seperate assembly is much easier to maintain.
It was a while since I played with it, but as long as you only change the
implementation of methods and properties, ALTER ASSEMBLY works great. But
if you change the physical storage of the type, then it indeed it's painful.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mike C# (xyz@.xyz.com) writes:
> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
> returns or compares a boolean value. My problem is that there appears
> to be no way to use a SqlBoolean return value from a method in an IF
> statement.
There is:
IF @.x.IsEqual(@.y) = 1
Of if you prefer:
IF @.x.IsEqual(@.y) = 'true'
SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
how you use bit in T-SQL. T-SQL is funny, because while it has boolean
expressions, it does not have a boolean data type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

[SQL 2005] SQLCLR UDT Comparison Methods

OK, so I'm creating a simple UDT. I want to expose a method that performs a
comparison of two objects of the type of the UDT and returns a "boolean"
value based on the result that SQL Server can use in T-SQL statements and
expressions. I understand you can't override comparison operators like = and <>. Ideally I'd like to achieve something like the following:
DECLARE @.x MyUDT
DECLARE @.y MyUDT
SELECT @.x = 'Test Value'
SELECT @.y = 'Test Value'
IF @.x.IsEqual(@.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Or alternatively:
IF MyUDT::IsEqual(@.x, @.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Anyone done this, or can point me to someplace with a code sample?
Thanks in advance.Almost forgot to mention. IsByteOrdered is not really an option, since this
particular type is an unordered space and <, > operators make no sense on
it. = and <> are valid operations on this type.
Thanks
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Mike
You want to compare two values , is it possible that
SELECT @.x = 'Test value'
SELECT @.y = 'Test Value'
So , you need to PRINT 'Not Equal' , am I right?
Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
SELECT a.ID, a.CheckSum
From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsA ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsB ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Uri,
I want to do it in a SQLCLR UDT by exposing a comparison method. I could
avoid the checksum functions and just use IF @.x.ToString = @.y.ToString
directly; or create a UDF to do the comparison. I'd rather expose a method
of the UDT so I can encapsulate the logic all in one spot and avoid
conversions and additional calculations/manipulations.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Mike
> You want to compare two values , is it possible that
> SELECT @.x = 'Test value'
> SELECT @.y = 'Test Value'
>
> So , you need to PRINT 'Not Equal' , am I right?
>
> Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
>
> SELECT a.ID, a.CheckSum
> From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsA ) a
> Inner Join (
> Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsB ) b
> On a.ID = b.ID
> Where a.CheckSum != b.CheckSum
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns a
>> "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Or alternatively:
>> IF MyUDT::IsEqual(@.x, @.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Anyone done this, or can point me to someplace with a code sample?
>> Thanks in advance.
>|||Mike C# (xyz@.xyz.com) writes:
> OK, so I'm creating a simple UDT. I want to expose a method that
> performs a comparison of two objects of the type of the UDT and returns
> a "boolean" value based on the result that SQL Server can use in T-SQL
> statements and expressions. I understand you can't override comparison
> operators like = and <>. Ideally I'd like to achieve something like the
> following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
Apart from that the syntax most certainly would have to be
IF @.x.IsEqual(@.y) = 1
what is really your problem?
In the Point sample in Books Online, there is a method Distance where the
input is another point. See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns
>> a "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
> Apart from that the syntax most certainly would have to be
> IF @.x.IsEqual(@.y) = 1
> what is really your problem?
My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
returns or compares a boolean value. My problem is that there appears to be
no way to use a SqlBoolean return value from a method in an IF statement. I
want to know if that is correct or not. Is SqlBoolean of any use whatsoever
in the context of a UDT?
> In the Point sample in Books Online, there is a method Distance where the
> input is another point. See
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Unfortunately I'm not interested in returning a numeric result of a
calculation on two UDTs. I'm interested in comparing two UDT's that are not
byte-ordered for equality. I notice that the MS Point sample is
byte-ordered; does this indicate that a point (X, Y+1000000) is actually
"less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
remove byte-ordering (since byte-ordering is inapplicable in my case), I
cannot use the T-SQL "=" comparison operator on two variables of the UDT's
type. The UDT is Format.Native.
If you decide for some reason that "less than" and "greater than" have no
meaning in regards to your UDT, and remove the byte-ordering, what's the
best way to test them for equality/inequality? Is returning arbitrary "flag
values" from methods and comparing those flag values for equality to
constants the only way? Is there no way to return a boolean value (aka,
SqlBoolean) and use it in an IF statement?|||Mike C# (xyz@.xyz.com) writes:
> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
> returns or compares a boolean value. My problem is that there appears
> to be no way to use a SqlBoolean return value from a method in an IF
> statement.
There is:
IF @.x.IsEqual(@.y) = 1
Of if you prefer:
IF @.x.IsEqual(@.y) = 'true'
SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
how you use bit in T-SQL. T-SQL is funny, because while it has boolean
expressions, it does not have a boolean data type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Mike,
> Unfortunately I'm not interested in returning a numeric result of a
> calculation on two UDTs. I'm interested in comparing two UDT's that are
> not byte-ordered for equality. I notice that the MS Point sample is
> byte-ordered; does this indicate that a point (X, Y+1000000) is actually
> "less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
> remove byte-ordering (since byte-ordering is inapplicable in my case), I
> cannot use the T-SQL "=" comparison operator on two variables of the UDT's
> type. The UDT is Format.Native.
> If you decide for some reason that "less than" and "greater than" have no
> meaning in regards to your UDT, and remove the byte-ordering, what's the
> best way to test them for equality/inequality? Is returning arbitrary
> "flag values" from methods and comparing those flag values for equality to
> constants the only way? Is there no way to return a boolean value (aka,
> SqlBoolean) and use it in an IF statement?
How about still using byte ordering, but implemented in your own way? You
could implement IBinarySerialize interface in your UDT and then in the Write
method write additional 8 bytes in the beginning of the serialized value.
You can calculate the value in this 8 bytes anyway you like, so you can
define also what is greater than and what is lower thaan, not just equal.
When you deserialize a value from this UDT, i.e. in the Read method, just
skip these first 8 bytes.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uCbKXtrIHHA.5000@.TK2MSFTNGP03.phx.gbl...
> How about still using byte ordering, but implemented in your own way? You
> could implement IBinarySerialize interface in your UDT and then in the
> Write method write additional 8 bytes in the beginning of the serialized
> value. You can calculate the value in this 8 bytes anyway you like, so you
> can define also what is greater than and what is lower thaan, not just
> equal. When you deserialize a value from this UDT, i.e. in the Read
> method, just skip these first 8 bytes.
Dejan, in some instances greater than and less than are meaningless. This
is the case here. Equality and inequality, however, have meaning. I would
like to get the functionality of equality and inequality without less than,
greater than, etc.
Thanks.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989D60A6568D6Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
>> returns or compares a boolean value. My problem is that there appears
>> to be no way to use a SqlBoolean return value from a method in an IF
>> statement.
> There is:
> IF @.x.IsEqual(@.y) = 1
> Of if you prefer:
> IF @.x.IsEqual(@.y) = 'true'
> SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
> how you use bit in T-SQL. T-SQL is funny, because while it has boolean
> expressions, it does not have a boolean data type.
You know what it boils down to? They need to allow you to override T-SQL
operators for UDT's, plain and simple. For all that, I may as well just
write IF CAST(@.x AS VARBINARY) = CAST(@.y AS VARBINARY). I was hoping taht
(as a next best option), there would be a way to treat a .NET method as a
boolean expression, but that appears to not be the case. Looks like it's
just a matter of returning a value and arbitrarily assigning meaning to it.
Thanks.|||Mike C# (xyz@.xyz.com) writes:
> You know what it boils down to? They need to allow you to override T-SQL
> operators for UDT's, plain and simple.
Because you would wear out your fingers if you have to type "= 1"? C'mon,
I know have C# in your alias, but you are not in Kansas anymore. When in
Rome, do as the Romans.
But if you feel strongly about it, http://connect.microsoft.com is where
you can submit suggestions for enhancements.
> For all that, I may as well just write IF CAST(@.x AS VARBINARY) => CAST(@.y AS VARBINARY).
Yes, if your equality check is that simple. The above would not work for
some of T-SQL data types in some collations.
> I was hoping taht (as a next best option), there would be a way to treat
> a .NET method as a boolean expression, but that appears to not be the
> case. Looks like it's just a matter of returning a value and
> arbitrarily assigning meaning to it.
You would have precisely the same issue with a UDF that returns 0 or 1.
Or for that matter a bit column in a table which represents something
that is off or on.
Why would you need a special syntax just because it's a .Net UDT?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DB977C7FECYazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> You know what it boils down to? They need to allow you to override T-SQL
>> operators for UDT's, plain and simple.
> Because you would wear out your fingers if you have to type "= 1"? C'mon,
> I know have C# in your alias, but you are not in Kansas anymore. When in
> Rome, do as the Romans.
Simple consistency. You use "=1" as the status for a match, Harry uses
"=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'". BTW,
haven't been to Kansas myself and just in case you didn't get the memo, the
Romans did as the Romans do while in Rome, and the Roman Empire went bye-bye
for precisely that reason.
> But if you feel strongly about it, http://connect.microsoft.com is where
> you can submit suggestions for enhancements.
Fell strongly? Not hardly. I was just asking a question - "Is it
possible?" Apparently not. That's the end of that as far as I'm concerned.
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
> Yes, if your equality check is that simple. The above would not work for
> some of T-SQL data types in some collations.
Just wondering what "some of T-SQL data types in some collations" has to do
with comparing the binary representation of a SQLCLR UDT?
>> I was hoping taht (as a next best option), there would be a way to treat
>> a .NET method as a boolean expression, but that appears to not be the
>> case. Looks like it's just a matter of returning a value and
>> arbitrarily assigning meaning to it.
> You would have precisely the same issue with a UDF that returns 0 or 1.
> Or for that matter a bit column in a table which represents something
> that is off or on.
Consistency.
> Why would you need a special syntax just because it's a .Net UDT?
How's this for special syntax?
DECLARE @.m1 MyUDT
DECLARE @.m2 MyUDT
IF @.m1 = @.m2
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Unforunately (as I said previously) it doesn't seem to work when the Byte
Ordering attribute is set to false. It would appear that the Romans feel if
two items can't be sorted, then neither can they be equal.|||Mike C# (xyz@.xyz.com) writes:
> Simple consistency.
Why then ask for a special feature for .Net UDT methods?
> You use "=1" as the status for a match, Harry uses
> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
Using bit to represent boolean values with 1 for truth is quite standard
in the T-SQL world. So why should .Net UDT methods be special?
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
>> Yes, if your equality check is that simple. The above would not work for
>> some of T-SQL data types in some collations.
> Just wondering what "some of T-SQL data types in some collations" has to
> do with comparing the binary representation of a SQLCLR UDT?
As an example that equality is not always possible by byte-comparing,
even if it might be in your case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989E6F414054Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> Simple consistency.
> Why then ask for a special feature for .Net UDT methods?
>> You use "=1" as the status for a match, Harry uses
>> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
> Using bit to represent boolean values with 1 for truth is quite standard
> in the T-SQL world. So why should .Net UDT methods be special?
It is also common in the T-SQL world to use 'Y', 'T', and 'True' for
Yes/True. Yet none of these (1 inclusive) is the same thing as TRUE, or you
could logically expect something like this to work:
IF 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
However the result of 1 = 1 *is* TRUE, so you can expect this to work:
IF 1 = 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
Or even this:
DECLARE @.i INT
DECLARE @.j INT
SELECT @.i = 1
SELECT @.j = 1
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
*Yet this doesn't*:
DECLARE @.i MyUDT
DECLARE @.j MyUDT
SELECT @.i = '(1,2,3)'
SELECT @.j = '(1,2,3)'
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
It appears that .NET UDT equality operators are "special" for non-byte
ordered types. As I stated, the conclusion seems to be if two items can't
be sorted they can't be compared for equality.
>> Just wondering what "some of T-SQL data types in some collations" has to
>> do with comparing the binary representation of a SQLCLR UDT?
> As an example that equality is not always possible by byte-comparing,
> even if it might be in your case.
I don't see how collation even comes into the picture here, but the fact
that equality is not always possible by byte-comparing is another reason it
would be just *dandy* to have the capability of overloading the T-SQL
equality operator for your UDT.
Bottom line is this: I asked if it was possible to do "x". You said it is
*not* possible to do "x".
That's great. Case closed. Problem solved. Over and out.

[OT] User-Defined string Functions Transact-SQL

Ladies and Gentlemen,

I am pleased to offer, free of charge, the following string functions Transact-SQL:

AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

Plus, there are CHM files in English, French, Spanish, German and Russian.

More than 6000 people have already downloaded my functions. I hope you will find them useful as well.

For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115

With the best regards.DON'T LOOK AT THE LIGHT!

If you give me a cheesburger today, I'll gladly pay you next Thursday|||Didn't this guy post before under a different name, and get his code ripped apart?|||Same name, different forum, same ineffecient code

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54333

[ask] sqlserver getdate() function doesnt return seconds value

i have a quite strange condition...

when i add some value in database with getdate() function it only returns date and minute not the seconds...

does somebody have an experience about this

What is the data type of the variable or column that you are using? If it is smaldatetime, the accuracy is only of minutes. If you want seconds, you'll need to use datetime.

If you're doing something else, post your code to see if we can help you figure it out.

Don

|||

getdate() actually returns current date & time. Just try to select getdate() and you will see. You must have assign the value of getdate() to smalldatetime.|||

I've changed to datetime and it works :)

weew... i never thought about it... just a little mistake there :)

thx anyway

[6.5] Server language English but date format is Dutch

I have a Sql 6.5 database-server. When I open a query window and ask for getDate() it returns Tuesday, Sep 30 2003 12:00 AM.

So far so good.

However, when I connect through an ASP page on my webserver (through ODBC) and ask for getDate, I get a Dutch format: 30-09-2003.

My Sql Server is set to default (English US)
My Database user is set to English Us
My DB server's regional settings are set to US
My ODBC is set to use English as a language
My Webserver's regional settings are set to US (d/M/y)

I'm at wit's end. I have no clue as to why I am getting Dutch dates. I know I could just convert(varchar,...) but that is not an option right now.

Thanks in advance,

KristofDoes sound like a puzzle. What are the Web Clients settngs? Don;t know why that would matter unless you are runing code on the client side, but it's the only thing you don't have listed.|||The client side is as English (US) as it gets. Regional settings are English etc.

Glad to see you're puzzled as well ... Little comfort, but comfort :)|||As a test, try changing each of the servers' settings, one at a time, to a third setting (no idea off the top of my head what this would be) to see if and when a change occurs.

This could help give you a new angle (?!).|||Solved.

Apparently, you can have multiple regional settings on a server (NT4) and NT decides which one it picks. So I forced US to be the default system locale, booted the (live *glubs*) database server and all was well.

Thanks a lot for thinking with me!

@@SERVERNAME sql 2k

Hi,
Diff server name for same sql box.
Select @.@.SERVERNAME
and select * from sysservers
WHy?
@.@.SERVERNAME returns incorrect name. How do I correct it?
THnksHi
At some point you probably renamed the server at some point, run
sp_dropserver and sp_addserver as shown in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"mecn" wrote:
> Hi,
> Diff server name for same sql box.
> Select @.@.SERVERNAME
> and select * from sysservers
> WHy?
> @.@.SERVERNAME returns incorrect name. How do I correct it?
> THnks
>
>

@@SERVERNAME sql 2k

Hi,
Diff server name for same sql box.
Select @.@.SERVERNAME
and select * from sysservers
WHy?
@.@.SERVERNAME returns incorrect name. How do I correct it?
THnksHi
At some point you probably renamed the server at some point, run
sp_dropserver and sp_addserver as shown in:
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
John
"mecn" wrote:

> Hi,
> Diff server name for same sql box.
> Select @.@.SERVERNAME
> and select * from sysservers
> WHy?
> @.@.SERVERNAME returns incorrect name. How do I correct it?
> THnks
>
>

@@serverName returns wrong name

I have 2 different SQL 2000 servers that had name changes after SQL was
installed. The first is returning the wrong name when I run "@.@.serverName".
The other is returning "null" when I run this query.
Can anyone tell me what the impact on applications is of changing these
names to what they should be?
tia
jjHi
Just rename the SQL Server as per:
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>|||Use:
SELECT SERVERPROPERTY('ServerName')
David Portas
SQL Server MVP
--
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>|||Hi,
Stop ur sql services and restart the services u will get the real
error.
from
Doller

@@serverName returns wrong name

I have 2 different SQL 2000 servers that had name changes after SQL was
installed. The first is returning the wrong name when I run "@.@.serverName".
The other is returning "null" when I run this query.
Can anyone tell me what the impact on applications is of changing these
names to what they should be?
tia
jj
Hi
Just rename the SQL Server as per:
http://msdn.microsoft.com/library/de...stall_5r8f.asp
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>
|||Use:
SELECT SERVERPROPERTY('ServerName')
David Portas
SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>
|||Hi,
Stop ur sql services and restart the services u will get the real
error.
from
Doller

@@serverName returns wrong name

I have 2 different SQL 2000 servers that had name changes after SQL was
installed. The first is returning the wrong name when I run "@.@.serverName".
The other is returning "null" when I run this query.
Can anyone tell me what the impact on applications is of changing these
names to what they should be?
tia
jjHi
Just rename the SQL Server as per:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>|||Use:
SELECT SERVERPROPERTY('ServerName')
--
David Portas
SQL Server MVP
--
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:unGp3y5wFHA.1168@.TK2MSFTNGP15.phx.gbl...
>I have 2 different SQL 2000 servers that had name changes after SQL was
>installed. The first is returning the wrong name when I run "@.@.serverName".
>The other is returning "null" when I run this query.
> Can anyone tell me what the impact on applications is of changing these
> names to what they should be?
>
> tia
> jj
>|||Hi,
Stop ur sql services and restart the services u will get the real
error.
from
Doller

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
Tom
Hi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>|||Hi Hari,
I have the same scenario as below, and I have even try to do two times
server restart also, but @.@.servername still returns Null to me, which is
different result from SERVERNAME property of SERVERPROPERTY function. Any
idea what else we can try? Thanks in advance!
Regards,
JC
"Hari Prasad" wrote:
> Hi,
> Try this
>
> sp_dropserver 'XYZ'
> go
> sp_addserver 'XYZ', 'local'
> go
> After this stop and start the MS SQL Server service. Then login in Query
> analyzer and execute SELECT @.@.SERVERNAME
> Thanks
> Hari
> SQL Server MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> > We are running SQL 2000 in a non-clustered server. For some reason when
> > we
> > select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> > ...
> > the information in returned by @.@.SERVERNAME may be different than the
> > SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> > automatically reports changes in the network name of the computer. In
> > contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> > changes made to the local server name using the sp_addserver or
> > sp_dropserver stored procedure
> >
> > Assuming that my server name is XYZ - I would assume that by executing the
> > following command we would be OK:
> >
> > use master
> > go
> >
> > sp_addserver 'XYZ', 'local'
> > go
> >
> > But when executing this - we get the following message:
> > Server already exists.
> >
> > Can someone advise the correct way to do this?
> >
> > Thanks,
> > Tom
> >
> >
> >
> >
> >
> >
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

@@servername returns NULL value

I have a SQL 2005 clustered server which is returning a Null value for @.@.servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @.@.servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.

thanks

For a machine to be considered local, srvid = 0 in sysservers. Can you check on this?

Can a simple reboot of the nodes fix the problem? Otherwise how did you get into this state, considering you have replication set up properly already?

|||

Hi,

Have you rename the virtual server name or the participating node name?

Peng

@@SERVERNAME returns NULL

When I do:
SELECT @.@.SERVERNAME
It returns NULL. How, and how can I fix.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
use:
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

@@servername returns NULL

Hi,
sql server 2000 sp3a
select @.@.servername returns null but
select * from sysservers shows the correct servername. This causes problems
to set up replication.
Any idea how to resolve apart of reinstalling?
Thanks
YanLook up sp_dropserver and sp_addserver system procedures in Books Online.
Have you renamed the server recently? (either the machine or the SQL Server
instance)
ML
http://milambda.blogspot.com/|||Yan,
please try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thanks, I found the problem when I used sp_addserver I did not add the
second param @.local so the result is that master..sysservers has no row with
a srvrid 0 which should represent the local server.
Thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:CB9D38F6-E2F0-43F3-84C2-CF5DEA8AF8C8@.microsoft.com...
> Look up sp_dropserver and sp_addserver system procedures in Books Online.
> Have you renamed the server recently? (either the machine or the SQL
> Server
> instance)
>
> ML
> --
> http://milambda.blogspot.com/|||Yeah, those darn extra parameters... :)
ML
http://milambda.blogspot.com/

@@servername returns NULL

I am trying to script out the creation of database scripts. I am trying
to use @.@.servername in the statement. I found out the select
@.@.servername returns NULL. I used sp_dropserver to drop any servernames
first, restarted SQL, ran sp_addserver 'servername' to add the
servername, restarted SQL. select @.@.servername still returns NULL...

Any ideas why this may be happening?

Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!tgru (tgru@.devdex.com) writes:
> I am trying to script out the creation of database scripts. I am trying
> to use @.@.servername in the statement. I found out the select
> @.@.servername returns NULL. I used sp_dropserver to drop any servernames
> first, restarted SQL, ran sp_addserver 'servername' to add the
> servername, restarted SQL. select @.@.servername still returns NULL...
> Any ideas why this may be happening?

Did you specify 'local' as the second parameter to sp_addserver?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland is the man!!

Thanks, I battle syntax on a daily basis...

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

@@ServerName returns NULL

Hi: We use @.@.Servername throughout all our jobs to indicate to the script
which server the script is running upon. However, for some unknown reason,
the statement "Select @.@.ServerName" is returning a null value rather than the
name of the server. This has broken ALL of our job scripts. There is a work
around, however, with over 100 jobs that run, we are not too keen about
modifying existing scripts. Any ideas how we can get this fixed?
SQL Server Man Not
On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:

>Hi: We use @.@.Servername throughout all our jobs to indicate to the script
>which server the script is running upon. However, for some unknown reason,
>the statement "Select @.@.ServerName" is returning a null value rather than the
>name of the server. This has broken ALL of our job scripts. There is a work
>around, however, with over 100 jobs that run, we are not too keen about
>modifying existing scripts. Any ideas how we can get this fixed?
Hi Richard,
Quote from Books Online:
SQL Server Setup sets the server name to the computer name during
installation. Change @.@.SERVERNAME by using sp_addserver and then
restarting SQL Server. This method, however, is not usually required.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the imput Hugo, we have done just that and we still get a Null
result.
"Hugo Kornelis" wrote:

> On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
>
> Hi Richard,
> Quote from Books Online:
> SQL Server Setup sets the server name to the computer name during
> installation. Change @.@.SERVERNAME by using sp_addserver and then
> restarting SQL Server. This method, however, is not usually required.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:

>Thanks for the imput Hugo, we have done just that and we still get a Null
>result.
Hi Richard,
I'm sorry to hear that. Unfortunately, I'm at a loss about what might
cause this. However, you might want to check the following links to see if
they apply to your situation:
http://support.microsoft.com/default...b;en-us;302223
http://support.microsoft.com/default...b;en-us;303774
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||run sp_dropserver
and then run sp_addserver 'local' if the instance is a default
instance .
If the instance is a named instance then run sp_addserver
'machinename\instancename' .
The server name will be changed after SQL is recycled.
HTH.
Venu
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<rh3rm0t9lje6103c41f7f3plke6hoie7nj@.4ax.com>. ..
> On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
>
> Hi Richard,
> I'm sorry to hear that. Unfortunately, I'm at a loss about what might
> cause this. However, you might want to check the following links to see if
> they apply to your situation:
> http://support.microsoft.com/default...b;en-us;302223
> http://support.microsoft.com/default...b;en-us;303774
> Best, Hugo

@@ServerName returns NULL

Hi: We use @.@.Servername throughout all our jobs to indicate to the script
which server the script is running upon. However, for some unknown reason,
the statement "Select @.@.ServerName" is returning a null value rather than the
name of the server. This has broken ALL of our job scripts. There is a work
around, however, with over 100 jobs that run, we are not too keen about
modifying existing scripts. Any ideas how we can get this fixed?
--
SQL Server Man NotOn Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
>Hi: We use @.@.Servername throughout all our jobs to indicate to the script
>which server the script is running upon. However, for some unknown reason,
>the statement "Select @.@.ServerName" is returning a null value rather than the
>name of the server. This has broken ALL of our job scripts. There is a work
>around, however, with over 100 jobs that run, we are not too keen about
>modifying existing scripts. Any ideas how we can get this fixed?
Hi Richard,
Quote from Books Online:
SQL Server Setup sets the server name to the computer name during
installation. Change @.@.SERVERNAME by using sp_addserver and then
restarting SQL Server. This method, however, is not usually required.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the imput Hugo, we have done just that and we still get a Null
result.
"Hugo Kornelis" wrote:
> On Wed, 6 Oct 2004 14:57:02 -0700, Richard wrote:
> >Hi: We use @.@.Servername throughout all our jobs to indicate to the script
> >which server the script is running upon. However, for some unknown reason,
> >the statement "Select @.@.ServerName" is returning a null value rather than the
> >name of the server. This has broken ALL of our job scripts. There is a work
> >around, however, with over 100 jobs that run, we are not too keen about
> >modifying existing scripts. Any ideas how we can get this fixed?
> Hi Richard,
> Quote from Books Online:
> SQL Server Setup sets the server name to the computer name during
> installation. Change @.@.SERVERNAME by using sp_addserver and then
> restarting SQL Server. This method, however, is not usually required.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
>Thanks for the imput Hugo, we have done just that and we still get a Null
>result.
Hi Richard,
I'm sorry to hear that. Unfortunately, I'm at a loss about what might
cause this. However, you might want to check the following links to see if
they apply to your situation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302223
http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||run sp_dropserver
and then run sp_addserver 'local' if the instance is a default
instance .
If the instance is a named instance then run sp_addserver
'machinename\instancename' .
The server name will be changed after SQL is recycled.
HTH.
Venu
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<rh3rm0t9lje6103c41f7f3plke6hoie7nj@.4ax.com>...
> On Wed, 13 Oct 2004 09:51:09 -0700, Richard wrote:
> >Thanks for the imput Hugo, we have done just that and we still get a Null
> >result.
> Hi Richard,
> I'm sorry to hear that. Unfortunately, I'm at a loss about what might
> cause this. However, you might want to check the following links to see if
> they apply to your situation:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;302223
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
> Best, Hugo