Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / CLR / September 2008

Tip: Looking for answers? Try searching our database.

CLR SqlString / String Parameter Length

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mspzzz - 25 Jun 2008 15:12 GMT
I am using OPENQUERY in a CLR (for quering OLAP) and noticed that any
string/sqlstring parameter assigned is limited to 4000 chars. As
nvarchar(max) can hold more than 4000 chars, the CLR parametertypes should
handle also more than 4000 chars. Are there any work arounds besides using
two or more parameters  to get a max of 8000,12000 etc??

example (if you call this CLR from TSQL with MDXString > 4000 result is
always 4000- it gets cut):

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void testU(SqlString MDXString)
   {
        int i=0;

        try
        {
            i = MDXString.Value.Length;
        }
        catch
        {
        }
        throw new Exception ("Length= "+i.ToString());
    }
Bob Beauchemin - 26 Jun 2008 05:45 GMT
Are you doing automatic deployment from Visual Studio? If so, you need to
use the SqlFacet attribute with MaxSize = -1 as the default for SqlString is
nvarchar(4000). So...

public static void testU([SqlFacet(MaxSize=-1)] SqlString MDXString)

This will cause VisualStudio to make up DDL with varchar(max) type.

Hope this helps,
Bob Beauchemin
SQLskills

>I am using OPENQUERY in a CLR (for quering OLAP) and noticed that any
> string/sqlstring parameter assigned is limited to 4000 chars. As
[quoted text clipped - 19 lines]
>         throw new Exception ("Length= "+i.ToString());
>     }
mspzzz - 26 Jun 2008 12:16 GMT
That's it!!! Thx a lot!

> Are you doing automatic deployment from Visual Studio? If so, you need to
> use the SqlFacet attribute with MaxSize = -1 as the default for SqlString is
[quoted text clipped - 31 lines]
> >         throw new Exception ("Length= "+i.ToString());
> >     }
Adam Machanic - 26 Jun 2008 20:35 GMT
Or, use SqlChars rather than SqlString...

> Are you doing automatic deployment from Visual Studio? If so, you need to
> use the SqlFacet attribute with MaxSize = -1 as the default for SqlString
[quoted text clipped - 33 lines]
>>         throw new Exception ("Length= "+i.ToString());
>>     }
SvdSinner - 22 Sep 2008 18:18 GMT
> Or, use SqlChars rather than SqlString...

Just be aware that
[url=http://www.solidrockstable.com/blogs/PragmaticTSQL/Lists/Posts/Post.aspx?ID=26]S
qlChars
appears to be broken in SQL2008[/url].  (Worked fine in 2005)
Adam Machanic - 28 Sep 2008 16:24 GMT
SqlChars is not broken; Visual Studio 2008 is broken.  If you manually
deploy the assembly there is no issue.

>> Or, use SqlChars rather than SqlString...
>
> Just be aware that
> [url=http://www.solidrockstable.com/blogs/PragmaticTSQL/Lists/Posts/Post.aspx?ID=26]S
qlChars

> appears to be broken in SQL2008[/url].  (Worked fine in 2005)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.