Bug in 2005?

This code snippet works fine in 2000 but not in 2005. Is this a bug? I
get a conversion error on the 2005 server. Any ideas?
Create table Sample
(DESCR Char(30),
Ref Char(50),
Name Char(30))
Insert into sample
values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
Insert into sample
values ('Something else','Not date related','Jack')
DECLARE @.Ref_DT datetime
SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
Select Name From Sample
Where SubString(DESCR, 1, 20) = 'Valid Row'
and Convert(datetime, REF, 121) = @.Ref_DTshub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
>
Evaluation order is not guaranteed in any query so this can't be called
a bug even though it is inconvenient.
In the example given it would anyway be much better to make @.Ref_DT a
CHAR(50) instead of DATETIME. That way you can avoid the conversion for
each row.
If you must use CONVERT then try making a CASE expression of it (watch
out for line wrapping in this example):
...
WHERE SUBSTRING(DESCR, 1, 20) = 'Valid Row'
AND CASE WHEN REF
LIKE '[012][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]
[012][0-9]:[0-5][0-9]:[0-5][0-9] [AP]M'
THEN CONVERT(DATETIME, REF, 121)
END = @.Ref_DT;
The above isn't foolproof but it does at least force the right
evaluation order (usually). In your case you can also use the ISDATE
function in place of my LIKE expression. ISDATE has the disadvantage
that it depends on implicit conversion so it isn't suitable for all
date formats.
The most important lesson is, don't store dates as strings.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||shub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
> Create table Sample
> (DESCR Char(30),
> Ref Char(50),
> Name Char(30))
> Insert into sample
> values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
> Insert into sample
> values ('Something else','Not date related','Jack')
> DECLARE @.Ref_DT datetime
> SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
> Select Name From Sample
> Where SubString(DESCR, 1, 20) = 'Valid Row'
> and Convert(datetime, REF, 121) = @.Ref_DT
>
The "bug" is that you're trying to convert a non-date value to a
DATETIME. How is this SQL's fault?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Brilliant blog posting (Full-text search ideas)

I was just browsing SQL Server blogs appearing in my feeds, and this
one struck a note with me because I would kill for BOTH of the features
he mentions.
http://www.yafla.com/dforbes/categories/SQL/
Sorry for spamming some guy's blog, but if the SQL dev team reads this
please take a look at it. I doubt it's new ideas, but it's worth
reminding yourself of.Forwarded to the dev lead of Full-text search.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Seyton" <jseyton@.gmail.com> wrote in message
news:1126113969.155725.262800@.g49g2000cwa.googlegroups.com...
>I was just browsing SQL Server blogs appearing in my feeds, and this
> one struck a note with me because I would kill for BOTH of the features
> he mentions.
> http://www.yafla.com/dforbes/categories/SQL/
> Sorry for spamming some guy's blog, but if the SQL dev team reads this
> please take a look at it. I doubt it's new ideas, but it's worth
> reminding yourself of.
>|||seems to be pretty well known, I recall reading something like that on
Tom Kyte's site a while ago|||John,
The issues under "Improved Indexing in SQL Server" are well known on the
fulltext newsgroup. While both concerns are not features in SQL 2000 FTS or
SQL 2005 FTS, both issues are solvable at the application level with data
duplication. I've emailed DForbes to see if he's had previous emails on this
topic per his request.
Paul, I'm sure that the FTS Dev Lead knows about this issue and hopefully
post-Yukon it will be incorporated in the next SQL Server version, besides
he knows how to contact me.. <g>.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23vTs1d9sFHA.4076@.TK2MSFTNGP11.phx.gbl...
> Forwarded to the dev lead of Full-text search.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "John Seyton" <jseyton@.gmail.com> wrote in message
> news:1126113969.155725.262800@.g49g2000cwa.googlegroups.com...
>>I was just browsing SQL Server blogs appearing in my feeds, and this
>> one struck a note with me because I would kill for BOTH of the features
>> he mentions.
>> http://www.yafla.com/dforbes/categories/SQL/
>> Sorry for spamming some guy's blog, but if the SQL dev team reads this
>> please take a look at it. I doubt it's new ideas, but it's worth
>> reminding yourself of.
>

Brainstorming: Ideas for categories database structure please

Hi

Thanks in advance for any ideas.

What I'm trying to do, is have a category system like Yahoo or these
newsgroups where you have a parent "COMP" and multiple children "LANG"
or "DATABASES". However, these can also have children. So for
"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc

My question is, what is the best way to store these in a database and
also allow me to retrieve the data

I'm not sure how to procede

Thanks

SamWhat you want to do is store a tree structure. Celko has written
extensively on Nested Sets - you can also use a modified adjacency list
(something I prefer), storing the full path to each node with each record.
Anyway, if you search on Nested Sets or Adjacency List, you will find all
the information you need.

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> Hi
> Thanks in advance for any ideas.
> What I'm trying to do, is have a category system like Yahoo or these
> newsgroups where you have a parent "COMP" and multiple children "LANG"
> or "DATABASES". However, these can also have children. So for
> "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> My question is, what is the best way to store these in a database and
> also allow me to retrieve the data
> I'm not sure how to procede
> Thanks
> Sam|||Thanks

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> What you want to do is store a tree structure. Celko has written
> extensively on Nested Sets - you can also use a modified adjacency list
> (something I prefer), storing the full path to each node with each record.
> Anyway, if you search on Nested Sets or Adjacency List, you will find all
> the information you need.
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > Hi
> > Thanks in advance for any ideas.
> > What I'm trying to do, is have a category system like Yahoo or these
> > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > or "DATABASES". However, these can also have children. So for
> > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > My question is, what is the best way to store these in a database and
> > also allow me to retrieve the data
> > I'm not sure how to procede
> > Thanks
> > Sam|||You can do it in one table.

Use a few fields like child-id (unique index), name, parent_id

so your table might look something like

CHILD_ID NAME PARENT_ID
1 COMP 0
2 LANG 1
3 DATABASES 1
4 MS_SQLSERVER 3
5 ACCESS 3
6 ORACLE 3
7 VB 5 2
8 COBOL 2
9 ORACLE FORMS 2
10 ASP 2
11 HTML 2
12 JSCRIPT 2

So you can immediately , by knowing the parent ID, which would be a hard
coded static reference code (so languages will always have a parent code of
2), find all child codes and therefore types

???????

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> Thanks
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > What you want to do is store a tree structure. Celko has written
> > extensively on Nested Sets - you can also use a modified adjacency list
> > (something I prefer), storing the full path to each node with each
record.
> > Anyway, if you search on Nested Sets or Adjacency List, you will find
all
> > the information you need.
> > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > Hi
> > > > Thanks in advance for any ideas.
> > > > What I'm trying to do, is have a category system like Yahoo or these
> > > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > > or "DATABASES". However, these can also have children. So for
> > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > My question is, what is the best way to store these in a database and
> > > also allow me to retrieve the data
> > > > I'm not sure how to procede
> > > > Thanks
> > > > Sam|||You have here an adjacency list. Pretty standard way of doing things BUT
very difficult to write queries about subtrees and relations that aren't
immediate parent/child. You might also store the full path with each
record, as I do (which is usually efficient enough) to allow easy subtree
queries using "LIKE" operator.

"cor_blimey" <no_spam@.no_spam.com> wrote in message
news:1Jdcb.523$_N1.384673@.newsfep1-win.server.ntli.net...
> You can do it in one table.
> Use a few fields like child-id (unique index), name, parent_id
> so your table might look something like
> CHILD_ID NAME PARENT_ID
> 1 COMP 0
> 2 LANG 1
> 3 DATABASES 1
> 4 MS_SQLSERVER 3
> 5 ACCESS 3
> 6 ORACLE 3
> 7 VB 5 2
> 8 COBOL 2
> 9 ORACLE FORMS 2
> 10 ASP 2
> 11 HTML 2
> 12 JSCRIPT 2
>
> So you can immediately , by knowing the parent ID, which would be a hard
> coded static reference code (so languages will always have a parent code
of
> 2), find all child codes and therefore types
> ???????
>
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> > Thanks
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > > What you want to do is store a tree structure. Celko has written
> > > extensively on Nested Sets - you can also use a modified adjacency
list
> > > (something I prefer), storing the full path to each node with each
> record.
> > > Anyway, if you search on Nested Sets or Adjacency List, you will find
> all
> > > the information you need.
> > > > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > > Hi
> > > > > > Thanks in advance for any ideas.
> > > > > > What I'm trying to do, is have a category system like Yahoo or these
> > > > newsgroups where you have a parent "COMP" and multiple children
"LANG"
> > > > or "DATABASES". However, these can also have children. So for
> > > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > > > My question is, what is the best way to store these in a database
and
> > > > also allow me to retrieve the data
> > > > > > I'm not sure how to procede
> > > > > > Thanks
> > > > > > Sam