SQL Server Performance Forum – Threads Archive
non-programmer DBA grapples with CLR
Hello all, I have a problem with a CLR function. This is something I have inherited (I need hardly add!), I am using it to manage file deletion for automated backups. The problem is this: the function & assembly work fine on SQL Server 2005 32-bit. however I have a new pair of servers which are 64-bit, these are being set up for mirroring. I am finding this CLR function errors on 64-bit with the following error:-Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate ‘DirectoryList’:
System.OverflowException: Value was either too large or too small for an Int32.
System.OverflowException:
at System.Convert.ToInt32(Int64 value)
at UserDefinedFunctions.GetFiles(String d, String wildCard, ArrayList rowsArray)
at UserDefinedFunctions.DirectorySearch(String directory, String wildCard, Boolean subDirectories, ArrayList rowsArray)
at UserDefinedFunctions.DirectoryList(String rootDir, String wildCard, Boolean subDirectories)
this is the function itself
ALTER FUNCTION [dbo].[DirectoryList](@rootdirectory [nvarchar](max), @wildcard [nvarchar](max), @subdirectories [bit])
RETURNS TABLE (
[filename] [nvarchar](max) NULL,
[FileSize] [bigint] NULL,
[CreateDate] [bigint] NULL,
[CreateTime] [bigint] NULL,
[WriteDate] [bigint] NULL,
[WriteTime] [bigint] NULL,
[AccessDate] [bigint] NULL,
[AccessTime] [bigint] NULL,
[Attributes] [bigint] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlClrAssembly].[UserDefinedFunctions].[DirectoryList] Naively I’ve tried to change the calling function to use BIGINTs but I just get this error..
Msg 6258, Level 16, State 1, Procedure DirectoryList, Line 2
Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function’DirectoryList’ due to column 2.
this is how the function is called:- select * from dbo.DirectoryList(‘E:sqldbbackup’,’admin-20070122-091131.bak.gz’,0) I can’t post the CLR assembly itself because if I script it all I get is a load of hex, I guess I have to look at it in Visual Studio? Anyway – I can always just revert to t-sql and xp-cmdshell to do this manually. But it’s all working beautifully on 32-bit, if there’s a quick way to modify it for 64-bit, please let me know and save me a load of effort!
How do you post underscores? It’s such a pain not being able to!!
Underscores problem? We know about xp_cmdshell – only works if you insert dummy bold markers inside the name of the extended sproc, like so: x p _ [ b ] [ / b ] c m d s h e l l
Ah, thanks. So what about my CLR problem, Adriaan!??!?
Sorry, no experience with CLR.
I guess you should modify you clr-code so it handles int64 in stead of int32 !
There is an implicit conversion (System.Convert.ToInt32(Int64 value)) going on !! you should avoid it. And also compile your clr-proc for 64-bit so you avoid 32-bit "emulation" and latency. I don’t know how to pull your clr-sourcecode from sql2005. I know VS2005 can store it, but where and how is still a mistery to me ;(
Ok, thanks. I’ll get a programmer who’s more CLR-savvy than me to pull it apart somehow.
I’m nearly there now! I found a blog with something similar to what I want, http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx I need a bit more than his function gives, so I think I just need to add the relevant columns in this section
foreach (string f in Directory.GetFiles(d, wildCard))
{
FileInfo fi = new FileInfo(f); object[] column = new object[2]; column[0] = fi.FullName; column[1] = fi.LastWriteTime; ‘Add My columns here rowsArray.Add(column);
But I have compiled this on the 64-bit server and it works, so I’m nearly there! One of my devs is having a look at it for me now, so I know which columns I need to add to the CS file. It’s a day for learning something new!
]]>