Problem returning SQL data

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<!-- END OF THE GUESTS AND LINKS SECTION -->

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?Is you asp code showing anything ? As you are referencing the column e.g. GuestName, it should not display anything for this attribute as it does not exists in the recordset. The attribute is renamed (in your query to Expr3, so you either will have to delete the AS Expr3 part or rename the reference of the recordset, e.g. rs("Expr3").

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the DB
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 … where
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
of them has timestamp < maximum value of smallint and half of them higher,
I’ll get no 1000 records but only that lower half of them.
Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:

> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 … where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> of them has timestamp < maximum value of smallint and half of them higher,
> I’ll get no 1000 records but only that lower half of them.
|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.
|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for next
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:

> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp to
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timestamp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestamp
> was also called rowversion for a while (a better name) and contains no time.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by the
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
>
>
|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from a
> table as a decimal number Int64. They are asking server giving this version
> mark if something new is present to download. The timestamp is not nullable
> and contains DB-blobal number (i think binary(8)) incrementing by one. I am
> selecting top 1000 records which has this mark higher then given and for next
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geting
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I saw
> the help, I started to use casting, that is simple. I know several steps how
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is: is
> that behaviour I described ok? I think it is not and I supose there is a bug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the D
B
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 … wher
e
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, hal
f
of them has timestamp < maximum value of smallint and half of them higher,
I’ll get no 1000 records but only that lower half of them.Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:

> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decim
al
> number (Int64), so I do some casting to timestamp in my queries. Once the
DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it b
y
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 … wh
ere
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, h
alf
> of them has timestamp < maximum value of smallint and half of them higher,
> I’ll get no 1000 records but only that lower half of them.|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
>|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/d...atasqltypes.asp
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
>|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for nex
t
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:

> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp t
o
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timesta
mp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestam
p
> was also called rowversion for a while (a better name) and contains no tim
e.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by t
he
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in messa
ge
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
>
>|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields
.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/d...atasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
>|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible
.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from
a
> table as a decimal number Int64. They are asking server giving this versio
n
> mark if something new is present to download. The timestamp is not nullabl
e
> and contains DB-blobal number (i think binary(8)) incrementing by one. I a
m
> selecting top 1000 records which has this mark higher then given and for n
ext
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geti
ng
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I sa
w
> the help, I started to use casting, that is simple. I know several steps h
ow
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is:
is
> that behaviour I described ok? I think it is not and I supose there is a b
ug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:
>

Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the DB
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 â?¦ where
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
of them has timestamp < maximum value of smallint and half of them higher,
Iâ'll get no 1000 records but only that lower half of them.Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 â?¦ where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> of them has timestamp < maximum value of smallint and half of them higher,
> Iâ'll get no 1000 records but only that lower half of them.|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
> > Hello,
> >
> > During my work I have found following problem. All tables that I am using
> > have column RecTimeStamp (timestamp). I do the selection based on last
> > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > following example:
> >
> > select cast (cast (2147483648 as bigint) as timestamp)
> > result: 0x0000000080000000 - OK
> > select cast (2147483648 as timestamp)
> > result: 0x0A00000100000080 - MISSMATCH
> > select cast (2147483647 as timestamp)
> > result: 0x000000007FFFFFFF - OK
> >
> > Is this a bug of MSSQL server?
> >
> > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > of them has timestamp < maximum value of smallint and half of them higher,
> > Iâ'll get no 1000 records but only that lower half of them.|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
AMB
"Michal Valenta" wrote:
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
> > Michal Valenta,
> >
> > Can you tell us the data type of that column?
> >
> > SQL Server has timestamp data type but it has nothing to do with datetime
> > data type. it is used for row versioning.
> >
> > select getdate(), current_timestamp, @.@.dbts;
> >
> >
> > AMB
> >
> > "Michal Valenta" wrote:
> >
> > > Hello,
> > >
> > > During my work I have found following problem. All tables that I am using
> > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > > following example:
> > >
> > > select cast (cast (2147483648 as bigint) as timestamp)
> > > result: 0x0000000080000000 - OK
> > > select cast (2147483648 as timestamp)
> > > result: 0x0A00000100000080 - MISSMATCH
> > > select cast (2147483647 as timestamp)
> > > result: 0x000000007FFFFFFF - OK
> > >
> > > Is this a bug of MSSQL server?
> > >
> > > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > > of them has timestamp < maximum value of smallint and half of them higher,
> > > Iâ'll get no 1000 records but only that lower half of them.|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for next
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:
> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp to
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timestamp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestamp
> was also called rowversion for a while (a better name) and contains no time.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by the
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> > Hello,
> >
> > During my work I have found following problem. All tables that I am using
> > have column RecTimeStamp (timestamp). I do the selection based on last
> > remembered timestamp. For some reasons my last timestamp is saved as
> > decimal
> > number (Int64), so I do some casting to timestamp in my queries. Once the
> > DB
> > global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> > by
> > following example:
> >
> > select cast (cast (2147483648 as bigint) as timestamp)
> > result: 0x0000000080000000 - OK
> > select cast (2147483648 as timestamp)
> > result: 0x0A00000100000080 - MISSMATCH
> > select cast (2147483647 as timestamp)
> > result: 0x000000007FFFFFFF - OK
> >
> > Is this a bug of MSSQL server?
> >
> > Note: If I do no casting and simply use for example select top 1000 .
> > where
> > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> > half
> > of them has timestamp < maximum value of smallint and half of them higher,
> > I'll get no 1000 records but only that lower half of them.
>
>|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields.
"Alejandro Mesa" wrote:
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
> > Alejandro Mesa,
> >
> > The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> > it has nothing to do with DateTime type. ;)
> >
> > "Alejandro Mesa" wrote:
> >
> > > Michal Valenta,
> > >
> > > Can you tell us the data type of that column?
> > >
> > > SQL Server has timestamp data type but it has nothing to do with datetime
> > > data type. it is used for row versioning.
> > >
> > > select getdate(), current_timestamp, @.@.dbts;
> > >
> > >
> > > AMB
> > >
> > > "Michal Valenta" wrote:
> > >
> > > > Hello,
> > > >
> > > > During my work I have found following problem. All tables that I am using
> > > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > > remembered timestamp. For some reasons my last timestamp is saved as decimal
> > > > number (Int64), so I do some casting to timestamp in my queries. Once the DB
> > > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
> > > > following example:
> > > >
> > > > select cast (cast (2147483648 as bigint) as timestamp)
> > > > result: 0x0000000080000000 - OK
> > > > select cast (2147483648 as timestamp)
> > > > result: 0x0A00000100000080 - MISSMATCH
> > > > select cast (2147483647 as timestamp)
> > > > result: 0x000000007FFFFFFF - OK
> > > >
> > > > Is this a bug of MSSQL server?
> > > >
> > > > Note: If I do no casting and simply use for example select top 1000 â?¦ where
> > > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, half
> > > > of them has timestamp < maximum value of smallint and half of them higher,
> > > > Iâ'll get no 1000 records but only that lower half of them.|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from a
> table as a decimal number Int64. They are asking server giving this version
> mark if something new is present to download. The timestamp is not nullable
> and contains DB-blobal number (i think binary(8)) incrementing by one. I am
> selecting top 1000 records which has this mark higher then given and for next
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geting
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I saw
> the help, I started to use casting, that is simple. I know several steps how
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is: is
> that behaviour I described ok? I think it is not and I supose there is a bug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:
> > Michal,
> >
> > I have to admit that I am fogged as to why you want to convert timestamp to
> > anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> > (The only meaning is that more recently changed rows have a higher timestamp
> > value than previously changed rows.)
> >
> > Just in case you are expecting something different, remember that timestamp
> > was also called rowversion for a while (a better name) and contains no time.
> >
> > Then I see your WHERE clause. Actually, you should never care how the
> > Binary(8) converts to compare with the interger value 0. Simply order by the
> > timestamp without a comparison, unless you allow the timestamp to be
> > nullable. If that is the case then, you may want:
> > WHERE RecTimeStamp IS NOT NULL
> >
> > For your comparison to evaluate as you apparently intended:
> > WHERE RecTimeStamp > 0x00
> >
> > RLF
> >
> >
> > "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
> > news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> > > Hello,
> > >
> > > During my work I have found following problem. All tables that I am using
> > > have column RecTimeStamp (timestamp). I do the selection based on last
> > > remembered timestamp. For some reasons my last timestamp is saved as
> > > decimal
> > > number (Int64), so I do some casting to timestamp in my queries. Once the
> > > DB
> > > global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> > > by
> > > following example:
> > >
> > > select cast (cast (2147483648 as bigint) as timestamp)
> > > result: 0x0000000080000000 - OK
> > > select cast (2147483648 as timestamp)
> > > result: 0x0A00000100000080 - MISSMATCH
> > > select cast (2147483647 as timestamp)
> > > result: 0x000000007FFFFFFF - OK
> > >
> > > Is this a bug of MSSQL server?
> > >
> > > Note: If I do no casting and simply use for example select top 1000 .
> > > where
> > > RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> > > half
> > > of them has timestamp < maximum value of smallint and half of them higher,
> > > I'll get no 1000 records but only that lower half of them.
> >
> >
> >

problem maybe with IN

Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Crisif you dynamically build your query
and then execute it

will that do ?|||The query can be

select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

Originally posted by bortolo
Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Cris|||select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

nice solution !|||create proc sp_return_users (
@.uid char(10) = null,
@.pwd varchar(16) = null,
@.eml varchar(128) = null)
as
declare @.tbl table (
uid char(10) null, @.pwd varchar(16) null, @.eml varchar(128) null)

insert @.tbl values (isnull(@.uid, '%'), isnull(@.pwd, '%'), isull(@.eml, '%'))

select u.* from dbo.tblusers u
inner join @.tbl t
on (u.uid like t.uid
and u.pwd like t.pwd
and u.eml like t.eml)
go