| Author |
Topic |
|
mcrowley
Aged Yak Warrior
771 Posts |
 Posted - 2007-02-06 : 15:55:06
|
This was originally posted on DBForums.com, so here is the link:http://www.dbforums.com/showthread.php?t=1614086Since some of the Microsoft staff come around here occasionally, I figured I should at least link to it here. This is the gist of the problem, though. I was asked to come up with a script to create all required data directories in case an emergency was declared, and someone had to rebuild one of our database servers. Most of you are probably thinking of hitting up the sysaltfiles table about now, but this will turn into a cautionary tale. Try it if you dare. The one requirement is that you install the data for SQL Server in a non-standard directory that has a short path (such as C:\MSSQL8, instead of the whole C:\Program files\...).What I am unclear on is whether this is a problem in the reverse function, the r(l)trim function, or the fixed-width datatype. I have confirmed that transferring the data to a temp table did not eliminate the...oddity.select filenamefrom master..sysaltfileswhere dbid = 2goselect reverse(rtrim(filename)), filenamefrom sysaltfileswhere dbid = 2goselect reverse(rtrim(filename))from sysaltfileswhere dbid = 2 I have also had two independent DBAs confirm this oddity exists, so this should be relatively easy to replicate. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-06 : 17:23:26
|
| What's the problem you see - I'm not getting anything in v2000 or 2005 (unless I'm missing it).There are oddities with char datatypes and string operators.Would be nice if you stated what the problem was when asking a question rather than leaving people to run code - especially when it means creating a database to do it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-07 : 09:41:25
|
Sorry, nr. The problem does not seem to exist in SQL 2005, but I am stuck on SQL 2000 for the moment. This is the result I get:filename -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- E:\MSSQL8\MSSQL\data\tempdb.mdfE:\MSSQL8\MSSQL\data\templog.ldf(2 row(s) affected) filename -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- fdm.bdpmet\ATAD\LQSSM\revre fdm.bdpmet\atad\LQSSM\8LQSSM\:E E:\MSSQL8\MSSQL\data\tempdb.mdf fdl.golpmet\ATAD\LQSSM\revr fdl.golpmet\atad\LQSSM\8LQSSM\:E E:\MSSQL8\MSSQL\data\templog.ldf(2 row(s) affected) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (2 row(s) affected) |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-07 : 09:50:16
|
| What is the Service pack status on your OS and SQL Server?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-07 : 10:17:36
|
These are the combinations I have at my disposal. All of which display this behaviour:SQL Server Windows---------- -------SP3 2003 RTMSP3 2003 SP1SP3 2000 SP4SP4 2003 RTM Honestly, I think this is a bug, and wanted to have Paul have a look. My current workaround is to simply ignore tempdb in my script, but that leaves a bit of a hole in its flexibility. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-02-07 : 12:22:43
|
| This does seem very strange. Is anyone else able to repro this? Could it be something to do with your collation or some embedded chars in the filenames?Have you checked with product support?Paul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-07 : 13:04:30
|
| I have not checked with Product Support, since this is hardly a "production down" situation. I can not think of any special characters in the filenames, as only the records pertaining to tempdb and master are affected. All others exhibit "normal" behaviour, even though they all share the same paths. The collation in all cases is the default (SQL_Latin1_General_CP1_CI_AS). So far I have gotten Thrasymachus from DBForums to reproduce (at least I think) this at the link in the original post, and also a poster named JFoushee at http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1157940&SiteID=17 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2007-02-07 : 13:51:18
|
| ok - suggest you contact Product Support to debug as language intrinsics aren't my thing. I've never heard of this problem before though.Paul RandalPrincipal Lead Program Manager, Microsoft SQL Server Core Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)http://blogs.msdn.com/sqlserverstorageengine/default.aspx |
|
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-02-07 : 18:02:46
|
| I'm just spitballing here, but I seem to remember a similar problem before with Null terminated strings.It appears to me that the reversed versions are pulling out left over text from a previous install when the path included "Microsoft SQL Server".You can see the left over "revre" and "revr" from the word "server".It would be interesting to see if what looks like a space in the reversed string is, in fact, ASCII 0. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-07 : 18:20:18
|
| I am seeing the same behavior on 2000 but only if I output my results to Text. In grid format, the data doesn't even show up. If I use a variable, it still shows the incorrect behavior. I wonder if there's an invalid character in the data that is causing reverse function to screw up.In 2005, everything works fine.Tara Kizer |
|
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-02-07 : 18:36:15
|
quote:
Originally posted by tkizer
I wonder if there's an invalid character in the data that is causing reverse function to screw up.
That was my point exactly. I don't have a server to test it on at the moment, but my assumption is that the Null character (ASCII 0) is preventing the output from displaying in the Grid View.It also appears to prevent the output in Text View unless another field is output as well. The second query in the sample above showsboth the reversed string and the original, but when only the reversedstring is selected (in the third query) the output is blank. |
|
|
|
|
Odd behavior