Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wut uhp wi dis? Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 11:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:13 PM
Points: 188, Visits: 424
On one of my development servers:

Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Mar 25 2011 13:33:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

The following code works as expected (with some rather odd length results I won't go into now):

Declare @s VarChar(8000);
Set @s='sqlservercentral.com';
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s=Replace(@s,'.',Char(0));
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s=Replace(@s,Char(0),'.');
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);

However, change the @s declaration to VarChar(max) and my systems hangs indefinitely burning up one of my CPUs!?!

Declare @s VarChar(max);
Set @s='sqlservercentral.com';
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s=Replace(@s,'.',Char(0));
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s=Replace(@s,Char(0),'.');
Select @s,Len(@s),DataLength(@s),Cast(@s as VarBinary);

Incidentally, both versions of the code work on SQL 2008 and SQL 2012.

Does this happen to anyone else? Does anyone have an explanation? And yes, we are migrating to SQL 2012...





PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1536643
Posted Friday, January 31, 2014 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 470, Visits: 823
did you checked it on any other machine?
Post #1536647
Posted Friday, January 31, 2014 10:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:13 PM
Points: 188, Visits: 424
twin.devil (1/31/2014)
did you checked it on any other machine?

Yes, unfortunately I only have access to SQL Serer 2005 64-bit servers so I couldn't test it on a 32-bit server, but it does the same thing on every SQL 2005 server I could find. As stated in my initial post, the code does work as expected on newer versions of SQL server.

I was just wondering if others have stumbled across this behavior...




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1536877
Posted Sunday, February 02, 2014 11:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 470, Visits: 823
i have checked this on 2005, 2008, 2008R2, 2012(all the x64 bit versions) and it worked perfectly. Its a very strange behavior thou. try to put the statistic on for the query and check what is happening in the background.
Post #1537203
Posted Friday, February 07, 2014 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:08 AM
Points: 133, Visits: 1,955
same on a 2005 32bit
Post #1539164
Posted Tuesday, February 18, 2014 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:33 AM
Points: 47, Visits: 362
Got the same CPU burn on our x64 2005 Enterprise servers and my 2008 x64 laptop server.
Post #1542566
Posted Tuesday, February 18, 2014 11:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:12 AM
Points: 2,627, Visits: 19,093
Nothing constructive to add, just wanted to give you props for the thread name...

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #1542683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse