MBC Computer Solutions Ltd.

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Monday, 17 March 2008

Handling long strings with CLR SQL Stored Procedures

Posted on 13:49 by Unknown

I've been banging my head on this one for a while now, though only now did I finally sit down to figure out to solve it.  I have (inherited from another dev) a CRL SQL Stored Procedure which outputs a long string.  The signature looks like:

public static void DoSomething(SqlString input,out SqlString output)

When output was long, an exception would be thrown similar to:

System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 28726 bytes to a T-SQL type with a smaller size limit of 8000 bytes.

Now you'd think that SqlString would map to varchar(max), if possible, but this is not the case. (I should also mention that the output is limited to 4000 bytes when using nvarchar).

The solution is to use SqlChars instead of SqlString - somewhere behind the scenes, this will get translated back to nvarchar, so the following will work:

DECLARE @output varchar(max)
EXEC DoSomething 'Loren Ipsum Dolar', @output output
SELECT @output

Of course you will have to convert your string to the SqlChars type before returning:

char[] chars = "Some String".ToCharArray();
output = new SqlChars(chars);       

Hope this helps!

Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • How to change the temperature scale on a Honeywell T6575 Thermostat
    [The complete documentation can be found at http://customer.honeywell.ca/techlit/pdf/95c-00000s/95c-10897.pdf ]   This was bugging me fo...
  • C# – Converting IP’s to Numbers and Numbers to IP’s in 2 lines of code
    I don’t know why everywhere I searched had such complex implementation of this, but converting from a dotted IP to a number (integer) and ba...
  • Why does iTunes setup need to close Outlook?!
    Everytime I update iTunes I remember why I left it so long - the install process is quite annoying! Can someone please explain to me why it...
  • Mac OSX 10.5.2 Freezing Intermittently
    I've been having an issue with my MacBook (you know, that computer I hide under my desk most of the time) where intermittently, the UI w...
  • ScottGu’s Color Scheme for Visual Studio 2010
    ScottGu was nice enough to provide the world with his awesome Visual Studio 2008 color scheme.  I’ve been using this for many years now an...
  • Don’t forget about the defer attribute for non-essential external scripts
    I was recently reviewing a customers eCommerce site and I noticed that the “Please Wait” page that occurs after completing an order but befo...
  • Windows Search 4.0 Released .....and searching finally works!
    I've been dealing with Outlook 2007's search problems since installing it way back then.  Most frequently, I'd search a keyword;...
  • Recursively finding controls - where to start?
    I love hearing about bugs and problems in components I have authored.  Most people hate hearing about bugs (I assume because they like to th...
  • Dell PowerEdge & Broadcom Issues
    For some time now we've been experiencing a problem across the board with Dell PowerEdge 2900/2950 servers equipped with Broadcom Gigabi...
  • Popup Window Manager
    I was just reading a post by Rick Strahl about managing popup windows in the browser.  I actually authored a mini popup window manager a wh...

Blog Archive

  • ►  2012 (1)
    • ►  February (1)
  • ►  2010 (1)
    • ►  April (1)
  • ►  2009 (7)
    • ►  December (1)
    • ►  November (1)
    • ►  October (1)
    • ►  July (1)
    • ►  April (2)
    • ►  February (1)
  • ▼  2008 (36)
    • ►  November (3)
    • ►  October (2)
    • ►  September (1)
    • ►  August (1)
    • ►  July (2)
    • ►  June (6)
    • ►  May (4)
    • ►  April (1)
    • ▼  March (4)
      • Dell, Broadcom & Scalable Networking Pack - Relief...
      • Handling long strings with CLR SQL Stored Procedures
      • Internet Explorer "Operation Aborted" - No more in...
      • Microsoft Surface at MIX08
    • ►  February (7)
    • ►  January (5)
  • ►  2007 (35)
    • ►  December (1)
    • ►  November (9)
    • ►  October (3)
    • ►  September (6)
    • ►  August (7)
    • ►  July (9)
  • ►  2006 (3)
    • ►  May (3)
Powered by Blogger.

About Me

Unknown
View my complete profile