September 19, 2012 at 6:47 am
hello all
shortly, this
declare @sqlStr varchar(max)
set @sqlStr = 'aa, '
print len(@sqlStr)
returns 3 on one server and 4 on another...
if i try it with varchar(8000) i get 3 on both...
unfortunately using 8000 is not an option...
both servers have Latin1_General_CI_AS collation
September 19, 2012 at 7:17 am
The LEN function won't give you trailing spaces. Try this instead:
declare @sqlStr varchar(max)
set @sqlStr = 'aa, '
print DATALENGTH(@sqlStr)
September 19, 2012 at 7:43 am
thanks, I will try this...
just a point... I'm not interested in trailing spaces, I'm only interested in consistency...
If I try the above-mentioned example with 'aa, a' I will get consistent values, which means that one of the servers ignore trailing spaces while using len(), one doesn't...
September 19, 2012 at 7:47 am
It will be interesting to see what results you get. I should think they would be consistent.
September 19, 2012 at 8:17 am
Yes, they are indeed consistent...
Although I am still curious about why len() doesn't work...
September 25, 2012 at 10:12 pm
ANSI_PADDING may not be set to the same value on both databases.
Try:
SELECT name, is_ansi_padding_on
FROM msdb.sys.databases
WHERE name = 'yourdatabase'
On both databases to check this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 26, 2012 at 1:43 am
gorgeous idea, but this isn't it
it's 0 on both servers...
September 26, 2012 at 1:47 am
What versions are those two servers?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 2:33 am
both are 2005
9.00.5000.00 on win 3790 on Intel x86
9.00.1399.06 on win 7601 on AMD x64
September 26, 2012 at 3:06 am
SQL 2005 RTM? Oooh.. There were some nasty issues around memory usage fixed in SP2.. I'd recommend you upgrade to SP4 on that one.
Also check your client connections, see if there's a difference in the ansi padding setting (it's a client option, what the DB has is just a default if not overridden)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 3:11 am
The owner of that instance played around with some settings/files and now she cannot update her SQL 🙂
So basically I shouldn't worry about this as it's most likely to be a RTM issue...
Thanks for the knowledge...
September 26, 2012 at 3:16 am
That's not what I said....
There were some nasty memory issues on SQL 2005 RTM, security holes and other unpleasant bugs and hence I strongly recommend that the instance be upgraded because of those.
Uninstall and reinstall if it can't be patched.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 3:26 am
That's not what I said....
I know that you mentioned memory issues, but I took this as 'there must have been some a bunch of other bugs as well'
Either way, I will no longer pursue this issue until I manage to get both instances onto the same version...
Thanks for the replies
September 26, 2012 at 3:40 am
There were. I have no idea if there was one relating to LEN of a max data type. Maybe worth reading the kb articles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 12:44 pm
norbert.manyi (9/26/2012)
gorgeous idea, but this isn't itit's 0 on both servers...
From BOL:
Columns defined with char, varchar, binary, and varbinary data types have a defined size.
This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.
In other words - it only matter what the ANSI_PADDING setting was when you created the tables or added the columns. If the server default was changed after the column was created, it wouldn't have changed the behavior.
You might care to start by recreating the table now that you know the ANSI_PADDING is the same on both environments.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply