InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:28:40 GMT20http://www.sqlservercentral.com/Forums/Topic1304740-386-1.aspxFirst of all, let me make it clear, I am not against the CLR integration at all ;-) It serves much better than TSQL in some scenarios. But being a DBA, my question is, do not CLRs make it a bit difficult to keep the environment secure with optimal performance. Especially, when there is requirement to keep up to date with the latest SQL Server version, learn new enhancements and other daily responsibilities etc. and not to say personal preferences (With least priority though). My concerns are• How to make sure that the CLR code is safe to import in my environment? Is it handling the exceptions correctly? Is there any code that can be exploited? I agree, most/some of us do have knowledge of .NET, but complex code is hard to look into?• For security reasons, one have the option to mark the assemblies as SAFE, EXTERNAL_EXCESS etc. which are quite manageable but what about when you have to declare it as UNSAFE? (In my environment it is needed as the CLR is accessing non-SQL database through OLEDB. In this case even Microsoft would not give support until the CLR is disabled temporarily from SQL Server instance). • Moreover, is it the DBA’s responsibility to keep up to date with .NET framework hotfixes, Service Packs, Upgrade etc.? The Upgrade itself needs refreshing of the assemblies in most cases :(• Testing. How you go about testing the CLR? What factors should be looked into? • Should there be a baseline above which CLR functionality should not be allowed? What is the balanced approach?I know most of the environments would have the CLRs installed, but what is the best way to manage them and how the segregation of the responsibilities is done? At the end of the day, in most environments, the management seeks an immediate response from the DBAs, when it comes to performance downgrade, server hang etc. and in some cases you find yourself in a predicament :-) Thanks in advance for suggestions and help.Wed, 23 May 2012 01:21:54 GMTUsman Butthttp://www.sqlservercentral.com/Forums/Topic1269696-386-1.aspxIn my CLR, I first tried a DataAdapter - when writing back to the database the performance is awful. 8000 rows take over a minute to write. So I've changed to using SqlBulkCopy - and the advantage is minimal - just shaved maybe 10% off the time.The CLR performs some very complex processing (very quickly) and writes it back to a table in the calling T-SQL. I've tried all the usual tweaks - drop all indexes before the bulkCopy, set recovery mode to Simple, send the data with an Order BY - But these 8000 rows, which only take a fraction of a second to create in the CLR still take over a minute to write back to the SQL Table.Has anyone got any performance tips for writing data back from a CLR?EDIT: Performance on development 2008 was great about 1 sec (still targeting .NET 3.5 libraries), but moved to production (SQL 2005) and performance is bad bad bad, though since the production server is a 16-processor monster with 32Gb Ram, the actual .NET processing is quicker its only the writing back to SQL Server thats wading-through-treacle slow.Tue, 20 Mar 2012 10:11:18 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic588105-386-1.aspxHere's my class {ref:msdn}:[code]public static long DirSize(DirectoryInfo d) { long Size = 0; FileInfo[] fis = d.GetFiles(); foreach (FileInfo fi in fis) { Size += fi.Length; } DirectoryInfo[] dis = d.GetDirectories(); foreach (DirectoryInfo di in dis) { Size += DirSize(di); } return (Size); }[/code]So I created the assembly (successful). But when I'm trying to create a function from the assembly...[code]CREATE FUNCTION FileSize (@dir VARCHAR(max))RETURNS BIGINTEXTERNAL NAME MyAssembly.MyClass.DirSizeGO[/code]...I am getting this error:[code]Msg 6552, Level 16, State 3, Procedure FileSize, Line 1CREATE FUNCTION for "FileSize" failed because T-SQL and CLR types for parameter "@dir" do not match.[/code]Sat, 18 Oct 2008 03:57:18 GMTMarlonRibunalhttp://www.sqlservercentral.com/Forums/Topic1288635-386-1.aspxI have no knowledge on c#. However i have the following code below that converts double byte to unicode. My requirement is reverse engineering i.e convert unicode (non english characters) to double byte ANSI.I have no idea what changes I should make to achieve that.Please help. thanks.[code="other"]using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Runtime.InteropServices;using System.ComponentModel;using System.Data.SqlTypes;namespace ConvertToUnicode{ public static class ConvertStringToUnicode { public static string ConvertToUnicode(int codePage, SqlString input) { string output; Encoding srcEncoding = Encoding.GetEncoding(codePage); byte[] byteInput = input.GetNonUnicodeBytes(); byte[] byteOutput =UnicodeEncoding.Convert(srcEncoding, Encoding.Unicode, byteInput); output = Encoding.Unicode.GetString(byteOutput); return output; } }}[/code]Mon, 23 Apr 2012 22:11:30 GMTMisLeadhttp://www.sqlservercentral.com/Forums/Topic1284070-386-1.aspxIs there a way to enable clr on database level instead of the code below or a a gui to do that.sp_configure 'clr enabled', 1GORECONFIGUREGOMon, 16 Apr 2012 06:54:58 GMTgissahhttp://www.sqlservercentral.com/Forums/Topic1170905-386-1.aspxHi,I have developed my clr integrated call according to the following logic:1) call a SSRS report2) save the report as a PDF in a specific folder location using foll code: // get pdf of report Byte[] results = rsExec.Render("PDF", deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs); FileStream fs = new FileStream(@"C:\Temp\PDF\rep.pdf", FileMode.OpenOrCreate, FileAccess.Write); fs.Write(results, 0, results.Length); fs.Close(); 3) I have created by assembly using sgen fine,4) created the mapped stored proceedure to call the assembly defined codeCREATE PROCEDURE dbo.testSP(@Param(20))AS EXTERNAL NAMEAssembly.[Assembly.StoredProcedures].sp_clr5) However, whenever I exculte the SP, an error is always thrown as per below:Msg 6522, Level 16, State 1, Procedure sp_clr, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "testSP": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.CredentialCache.get_DefaultCredentials() at emlPDFStatement.StoredProcedures.sp_clr(String cust)6) Because my clr code is writing a file, the error is most probably related to a security issue. I have used credidential that has the requiired rights.7) I have googled the error and applied recommeded solutions but wihout any success.I would be very much if any one could advise on the resolution or better point me to a small project that works fine along the above lines.Thank you very much in advance.:-)Wed, 07 Sep 2011 02:49:01 GMTcmsUserhttp://www.sqlservercentral.com/Forums/Topic1258245-386-1.aspxI've been fiddling around with some cutesy export functions via CLR;I've been able to use Visual Studio to deploy my project, and I can also script stuff out and pass the path to the dll i created;i've seen some other TSQL [b]only [/b]deployment scritps that look something like this:[code]CREATE ASSEMBLY [RegEx]AUTHORIZATION [dbo]FROM 0x4D5A900{snip!}WITH PERMISSION_SET = SAFE[/code]How can you generate the suite of commands to deploy a dll that way?all i can think of is the "hard" way, of loading the dll as a varbinary, then selecting it and copy pasting the value, and hten adding all the scripts for each procedure in the dll.is there an easier way?[code]/****** Object: StoredProcedure [dbo].[CLR_ExportTableToHTML] Script Date: 02/27/2012 09:28:31 ******/CREATE PROCEDURE [dbo].[CLR_ExportTableToHTML] @TableName [nvarchar](4000), @FilePath [nvarchar](4000), @IncludeHeaders [int], @FileName [nvarchar](4000), @Title [nvarchar](4000), @Summary [nvarchar](4000), @HTMLStyle [int]WITH EXECUTE AS CALLERASEXTERNAL NAME [Lowell.FavoriteCLRS].[Lowell.FavoriteCLRS.FavoriteCLRs].[CLR_ExportTableToHTML]GOEXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'FavoriteCLRs.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=252 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'GO[/code]Mon, 27 Feb 2012 07:37:11 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1267884-386-1.aspxHi,I have assembly that transform xml but i have a problem with script element<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts"> <msxsl:script language='C#' implements-prefix='user'> <![CDATA[ public string replace(string inStr, string pattern,string replacement){ return inStr.Replace(pattern,replacement); } ]]> </msxsl:script><xsl:template match="/"> <ahoj><xsl:value-of select="user:replace(/ROOT/RECORD,'hello','bye')" /></ahoj> </xsl:template></xsl:stylesheet>--------------------------------------------------------------<ROOT> <RECORD>hello , bye</RECORD></ROOT>--------------------------------------------------------------When i call assembly i get this errorA .NET Framework error occurred during execution of user-defined routine or aggregate "GetTransform": System.Xml.Xsl.XslTransformException: Cannot find a script or an extension object associated with namespace 'urn:my-scripts'.System.Xml.Xsl.XslTransformException: at System.Xml.Xsl.Runtime.XmlQueryContext.InvokeXsltLateBoundFunction(String name, String namespaceUri, IList`1[] args) at <xsl:template match="/">(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime, XPathNavigator {urn:schemas-microsoft-com:xslt-debug}current) at Root(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at Execute(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlSequenceWriter results) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlWriter writer) at System.Xml.Xsl.XslCompiledTransform.Transform(XmlReader input, XsltArgumentList arguments, Stream results) at Transformation.MyTransformation(SqlXml xmlData, SqlXml xsltData, SqlXml& retSqlXml)---------------------------------------------------------------------------------------Can you help me with this? assembly works fine ,but when i use c# in xslt i get errorI'm sorry for my english :-)Thu, 15 Mar 2012 15:03:40 GMTtomas.skorvanhttp://www.sqlservercentral.com/Forums/Topic719450-386-1.aspxI built a simple method sending an ADODB recordset via MSMQ and successfully tested in a console app, but when I import into sql server2005 CLR and exectue I get an error.-- Send object (all code works except this line):[SqlProcedure]public static void Send(SqlString queue, SqlString point, SqlString bodynumber, out SqlString msg){ [b]ADODB.Recordset rs = new ADODB.Recordset(); [/b] - call causing error} // end Send-- sql codecreate assembly SqlMSMQtools authorization dbo from 'C:\temp\SqlMSMQtools.dll' with permission_set = unsafego-- proccreate PROCEDURE usp_MSMQ_SendAP46@queue nvarchar(500),@point nvarchar(100),@body_number nvarchar(10),@msg nvarchar(MAX) outputAS EXTERNAL NAME SqlMSMQtools.[SqlMSMQtools.Basic].SendGO-- executeDECLARE @test nvarchar(1024)EXEC usp_MSMQ_SendAP46 @queue='FormatName:DIRECT=TCP:1.2.3.4\\private$\\XXX', @point='TestPoint',@body_number='123456', @msg = @test OUTPUTselect @test-- errorMsg 6522, Level 16, State 1, Procedure usp_MSMQ_SendAP46, Line 0A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_MSMQ_SendAP46': System.UriFormatException: Invalid URI: The URI is empty.System.UriFormatException: at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind) at System.ComponentModel.Design.RuntimeLicenseContext.GetLocalPath(String fileName) at System.ComponentModel.Design.RuntimeLicenseContext.GetSavedLicenseKey(Type type, Assembly resourceAssembly) at System.ComponentModel.LicenseManager.LicenseInteropHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth) at SqlMSMQtools.Basic.Send(SqlString queue, SqlString point, SqlString bodynumber, SqlString& msg)Mon, 18 May 2009 15:24:53 GMTbeyonder422http://www.sqlservercentral.com/Forums/Topic1265389-386-1.aspxThis is the first time I've tried a recursive CLR routine. It all compiles and runs from a .NET test application. but whenever recursion starts, when called from SQL, the function call fails with [code="plain"]Msg 6535, Level 17, State 49, Line 12.NET Framework execution was aborted. Another Query caused the MyNameSpace.dbo.[runtime].x to be unloaded[/code]The recursive routine uses generics - and has method signature private int EvaluateSubClause(List<string> subClause)Can anyone point me to a working example of a recursive C# method used in a SQL CLR function or procedure?Mon, 12 Mar 2012 11:00:17 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1261077-386-1.aspxI've been trying to install a SAFE assembly which is supplied as a block of hexadecimal bytes.I know categorically that it is safe and it installs absolutely fine under local instances of SQL Server, both 2005 & 2008.On our shared servers I get the dreaded 6218 error. The only thing I can think of is that these servers us a domain account for the MSSQLSERVER service and that this account does not have suitable permissions to access the .NET framework. The service account is NOT a local administrator.Does anyone know of any specific permissions that are necessary to register assemblies?I've heard the term "blessed" assemblies but I'm not sure if this is the same thing as the GAC. Do I need specific permissions to reference these assemblies?Fri, 02 Mar 2012 15:58:38 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic607488-386-1.aspxHi I am trying to call the WCF service from SQL CLR Stored procedure. I am able to create the procedure. but when I am executing the stored proc. it is showing the following error Msg 6522, Level 16, State 1, Procedure SubmitFaxRequest, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "SubmitFaxRequest": System.TypeInitializationException: The type initializer for 'System.ServiceModel.ClientBase`1' threw an exception. ---> System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.The protected resources (only available with full trust) were: AllThe demanded resources were: Synchronization, ExternalThreadingSystem.Security.HostProtectionException: at System.ServiceModel.DiagnosticUtility.GetUtility()at System.ServiceModel.DiagnosticUtility.get_Utility()at System.ServiceModel.ClientBase`1..cctor()System.TypeInitializationException: at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()at System.ServiceModel.ClientBase`1..ctor()at OutboundFaxClient..ctor()at StoredProcedures.SubmitFaxRequest() What could be problem? ThanksMon, 24 Nov 2008 06:37:12 GMTGunjanhttp://www.sqlservercentral.com/Forums/Topic1222624-386-1.aspxhi,I am trying to create a CLR stored procedure to call an external webservice, with very little success!!!I have created a test c# component that uses the webservice fine. When I create a CLR class I get an errorMsg 6522, Level 16, State 1, Procedure VALIDATE_ACCOUNT, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "VALIDATE_ACCOUNT": System.InvalidOperationException: Could not find default endpoint element that references contract 'ServiceReference1.validateSoap' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.System.InvalidOperationException: at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName) at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName) at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address) at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress) at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory() at System.ServiceModel.EndpointTrait`1.CreateChannelFactory() at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait) at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef() at System.ServiceModel.ClientBase`1..ctor() at ValidateFinancialDetails.ServiceReference1.validateSoapClient..ctor() at ValidateFinancialDetails.BaseFunctionClass.ValidateAccount(SqlString strSortCode, SqlString strAccountCode, SqlString& strResult).I have setup the CLR component as follows:-using System;//using System.Collections.Generic;//using System.Text;//using System.IO;using System.Data.SqlTypes;using System.Data.SqlClient;using Microsoft.SqlServer.Server;using System.Net;namespace ValidateFinancialDetails{public class BaseFunctionClass { #region "Default Constructor" public BaseFunctionClass() { } #endregion #region "Welcome Function" [SqlProcedure] public static void ValidateAccount(SqlString strSortCode,SqlString strAccountCode,out SqlString strResult) { string SortCode = Convert.ToString(strSortCode); string AccountCode = Convert.ToString(strAccountCode); bool AuthRequired = false; // Try default credentials (e.g. for ISA with NTLM integration) //WebRequest.DefaultWebProxy.Credentials = CredentialCache.DefaultCredentials; WebRequest.DefaultWebProxy.Credentials = new NetworkCredential("xxxx", "xxxx", "xxxx"); AuthRequired = false; // Assume this will allow us to connect for now string keycode = ""; ServiceReference1.validateSoapClient sc = new ServiceReference1.validateSoapClient(); //ServiceReference1.validateAccountRequest AccObj = new ServiceReference1.validateAccountRequest(); //ServiceReference1.paccnum pp = new ServiceReference1.paccnum(); //pp = sc.validateAccount(keycode, SortCode, AccountCode, "", "", "GB", ""); //strResult = pp.validity.ToString(); strResult = "WIBBLE"; } #endregion }}The setup in SQL server is as follows:-ALTER DATABASE H21_STAGING SET TRUSTWORTHY ONGOCREATE ASSEMBLY ValidateFinancialDetailsAUTHORIZATION dboFROM 'C:\Projects\CSHARP\ValidateFinancialDetails\ValidateFinancialDetails\bin\Debug\ValidateFinancialDetails.dll'WITH PERMISSION_SET = UNSAFEGOCREATE PROCEDURE VALIDATE_ACCOUNT ( @SORT_CODE NVARCHAR(255), @ACCOUNT_CODE NVARCHAR(255), @OUTPUT NVARCHAR(MAX) OUTPUT )AS EXTERNAL NAME ValidateFinancialDetails.[ValidateFinancialDetails.BaseFunctionClass].ValidateAccountGOI run it as follows:-DECLARE @A NVARCHAR(255)EXEC VALIDATE_ACCOUNT '123','123',@A OUTPUTprint @AAny help very much appreciated.ThanksPeter Gadsby0777 6090490Thu, 15 Dec 2011 10:32:43 GMTPeter Gadsbyhttp://www.sqlservercentral.com/Forums/Topic617715-386-1.aspxHi,Help me to solve the issue.I created wrapper class that used Microsoft Enterprise Library - Cryptography dll and when i try to create assembly in sql server, it asks that supported assembly is not in the same location, then I copied cryptography library to the current bin folder and tried once again.there was an error saying that 'system.management' assembly is not in the current database. [I have included it in my assembly]at last there was an problem with 'system.runtime.serialization.formatter.soap' has nameCache as a static member, and that has to be in readonlythanks in advance.Thu, 11 Dec 2008 03:32:03 GMTSatheesh Mohanhttp://www.sqlservercentral.com/Forums/Topic1231899-386-1.aspxI am trying to create a dynamic round robin scoring sheet with SQL stored procedure & VB and suggestion on how to do this?Some weekend we may have 6 players and other weekend we may have 12 players. If you have a script may help to do this will be greatful.Sat, 07 Jan 2012 08:19:57 GMTAlohahttp://www.sqlservercentral.com/Forums/Topic1224833-386-1.aspxhello everyone im new here and newbi about CLR , i need ur help about how to export XML file to a folder in the server through CLR bcuz my attempt with xp_cmdshell has failed i could create the file but its doesnt match my requirement , so im gonna try with CLR i just need to understand the concepts of how it works which environment im gonna use , and any help would be appreciated :)Tue, 20 Dec 2011 19:21:41 GMTthe.rahmonyhttp://www.sqlservercentral.com/Forums/Topic1215010-386-1.aspxHi everybody,I'm a DBA for a Company database; and of course a sysadmin. We have few CLR stored procedures embedded into SQL Server from .NET application.The modify option is greyed out and the script returns just the name of stored proc and it's parameters.Is there any way I can view and search inside the entire code in these CLR's within the SSMS?Your reply is very much appreciated.Thu, 01 Dec 2011 14:25:19 GMTAERhttp://www.sqlservercentral.com/Forums/Topic1198244-386-1.aspxDetails:Physical memory:6GB64bit OSwindows 2008sqlserver 20082 processors/2.67 GhzI have a windows 2008 and sql server 2008 on it.Currently sqlserver.exe is using up all the memory.It fluctuates between 90 to 95% of the memory present.(Physical box not virtual.)I would like to set the min and max memory utilised by sql server .How to calculate the min and max values.Can I get any t-sql to calculate these min /max valuesTask manager shows:CPU used:1% to 5%Physical memory :5.77 GB.Once I restart the sqlserver.exe service,it comes down to 2.0Gb physical memory used.Obviously sql server is not releasing the memory back to the OS once it grabs the memory.How do I prevent this from happenning.Are there any repurcussions if I leave it the way it is presently.ThanksMon, 31 Oct 2011 12:21:39 GMTsqlserver12345http://www.sqlservercentral.com/Forums/Topic1200897-386-1.aspxI am looking for a script I can run that will take a large output table and split it into several smaller tables of approximately equal size. The large table is an order detail table used to generate an email notice to customers. The customers can not span multiple tables as I only want to send one email per customer. I attach the tables to a 3rd party app that generates the email message. I have no idea where to start on this and need some direction.Any ideas and code examples are appreciated.Fri, 04 Nov 2011 14:29:56 GMTMRitchhttp://www.sqlservercentral.com/Forums/Topic1199075-386-1.aspxHere goes the scenario:Need to pass an active directory security group and get the memberOf.Tried table value function does not allow EXEC to execute and return a table containing the results simply because I am using openquery with LDAP.So, calling an sp within function is out of question.The results can be achieved by a stored procedure but the "caller" must be able to get the results in a table form.I am open for suggestions, but at this time, I am planning on finding out if there is a way to convert an SP to a CLR.Ideas are welcome.Mean while, there is my code in SP that works.[code="sql"]ALTER PROCEDURE [dbo].[USP_DisplayMemberOf_ADGroup] @ADGroup nvarchar(64) = '__MyGroup__'ASBEGIN SET NOCOUNT ON; declare @PartOne nvarchar(1000), @PartTwo nvarchar(2000) set @PartOne = 'SELECT cn, company, department, displayName, employeeID, facsimileTelephoneNumber, givenName , groupType, lastLogoff, mail, manager, mobile, ou, physicalDeliveryOfficeName, sAMAccountName , sAMAccountType ,sn, telephoneNumber, title, url, userAccountControl, userPrincipalName FROM ''LDAP://DomainName'' WHERE memberOf = ''cn='+ @ADGroup + ',OU=Groups,OU=ParentGroup,DC=CompanyNameOrDomain,DC=ad''' set @PartTwo =N' SELECT cn , department , displayName , employeeID , givenName , sn , mail , manager , physicalDeliveryOfficeName , sAMAccountName , telephoneNumber , title , userPrincipalName FROM OPENQUERY (ADSI, ''' + replace(@PartOne, '''', '''''') + ''' )' print @PartTwo exec (@PartTwo) END[/code]The only way that the MemberOf in openquery is with double string and that is not possible in a table returned function.appreciate your help.Please let me know if there is any other additional information that's lacking in here. .thxTue, 01 Nov 2011 22:35:47 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic1189421-386-1.aspx24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) is marked for unload due to memory pressure.24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) unloaded.24:14.6 spid65 AppDomain 66 (SMS_N07.dbo[runtime].69) created. select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'Result:1790760Keep getting these.Any thoughts.We have a 64bit OS.How do I identify the initial -g switch value.Where do I look for this default value.Is there t-sql which can identify the startup parameters?ThanksWed, 12 Oct 2011 13:47:18 GMTsqlserver12345http://www.sqlservercentral.com/Forums/Topic905648-386-1.aspxI am just trying to get my head around CLRs i have one example working however :w00t: there is a catch heheI have created a basic class library in visual studio 08 which looks likeImports System.Data.SqlImports System.Data.SqlClientImports Microsoft.SqlServer.ServerImports System.Runtime.InteropServicesPublic Class SVFs Public Shared Function FormatCurrency(ByVal Number As Double) As SqlTypes.SqlString Return String.Format("{0:c}", Number) End FunctionEnd ClassIn sql 2005 SSMS I have enabled clr with sp_configure 'clr enabled', 1go ReconfiguregoThen I have created the assembly Create assembly SQLLib from 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\ClassLibrary1\ClassLibrary1\bin\Debug\ClassLibrary1.dll'with permission_set = safeHowever when I go to create a function referencing the assembly it fails referencing the name space withMsg 6505, Level 16, State 1, Procedure clrFormatCurrency, Line 1Could not find Type 'SQLLib.UDFs' in assembly 'ClassLibrary1'.Create function clrFormatCurrency(@Number float) RETURNS nVarChar(100)as EXTERNAL NAME SQLLib.[SQLLib.SVFs].FormatCurrency-- i have also tried the namespace SQLLib.[SQLLib.SVFs].FormatCurrencyThanks for your input or ideasMsg 6505, Level 16, State 1, Procedure clrFormatCurrency, Line 1Could not find Type 'SQLLib.UDFs' in assembly 'ClassLibrary1'.Sun, 18 Apr 2010 19:59:13 GMTtchaplin 75200http://www.sqlservercentral.com/Forums/Topic1183201-386-1.aspxIs there a way to only allow CLR assemblies on specific databases rather than just an all or nothing approach?Thanks!Thu, 29 Sep 2011 08:28:41 GMTtullytehttp://www.sqlservercentral.com/Forums/Topic1179573-386-1.aspxHi,I have a SQL 2005 stored procedure that calls .Net clr procedure that read that call a SSRS report, generate a PDF and save it under a directory.The procedure runs fine under user credidential userA which has necessary rights on the folder. The challenge is to call the stored proceedure in a scheduled job to run successfully.I have 1)modfied the procedure to 'execure as 'userA' 2)I have also configured the step in the job to run as 'userA' 3) execute as user = 'userA; exec the_procedurewithout any success.Grateful if any of you could please advise on the solution.Thank you very much in advanceThu, 22 Sep 2011 10:22:37 GMTcmsUserhttp://www.sqlservercentral.com/Forums/Topic1161429-386-1.aspxIf I run a set of SQL statements in SQL management Studio I can return the results of SQL query statements to a grid while getting record counts, print messages and errors on the messages TAB. I want to store the messages tab dircetly to a file - but still show the query results in a grid. Can this be done?Wed, 17 Aug 2011 14:13:45 GMTcraig-962225http://www.sqlservercentral.com/Forums/Topic1168478-386-1.aspxI need help in converting below function code in sql clr based function for data manipulation Background: we need to load more than 10 gb of flat file daily and this require modification of date fields as wellInput data example: 20111012============================================================================Create FUNCTION [dbo].[USF_CONVERT_SAP_DATE_TO_SQL_DATE]( -- Add the parameters for the function here @SAPDATE VARCHAR(250))RETURNS DATETIMEASBEGIN -- Declare the return variable here DECLARE @SQLDATE VARCHAR(250) SET @SAPDATE = LTRIM(RTRIM(@SAPDATE)) IF LEFT(@SAPDATE,1) = '0' OR @SAPDATE = '' OR @SAPDATE IS NULL OR ISNUMERIC(@SAPDATE) = 0 OR LEN(@SAPDATE)<8 BEGIN RETURN NULL END IF CAST(@SAPDATE AS BIGINT) < 17530101 OR CAST(@SAPDATE AS BIGINT) > 99993112 RETURN NULL -- Add the T-SQL statements to compute the return value here SELECT @SQLDATE = LEFT(@SAPDATE,4) + '-' + RIGHT(LEFT(@SAPDATE,6),2) + '-' + RIGHT(LEFT(@SAPDATE,8),2) IF ISDATE(@SQLDATE) = 0 RETURN NULL -- Return the result of the function RETURN CAST(@SQLDATE AS DATETIME)END============================================================================Code which i have written for the same isusing System;using System.Data;using System.Data.Sql;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace SQLCLR{ public class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction()] public static DateTime USF_CLR_CONVERT_SAP_DATE_TO_SQL_DATE(String SAP_DATE) { try { String SQLDATE = SAP_DATE.Trim(); String SQLLEFT = SQLDATE.Substring(0,1); if ((SQLLEFT == "0") || (string.IsNullOrEmpty(SQLDATE) == true) || (SQLDATE.Length < 8)) { return Convert.ToDateTime(DBNull.Value); } int d1 = Convert.ToInt32(SQLDATE); if (( d1 < 17530101 || d1 > 99993112)) { return Convert.ToDateTime(DBNull.Value); } string s1 = SQLDATE.Substring(0,4) + '-' + SQLDATE.Substring(4,2) + '-' + SQLDATE.Substring(6,2); if (Convert.ToInt32(s1) == 0) { return Convert.ToDateTime(DBNull.Value); } return Convert.ToDateTime(s1); } catch (Exception ex) { throw ex; } } }}Wed, 31 Aug 2011 23:48:15 GMTarpitsinghal.tcshttp://www.sqlservercentral.com/Forums/Topic1164449-386-1.aspxHi All,I want to import data from flat file into SQL Server table using SQL CLR ..Also, we need to do some manipulation /formating of the datetime columnSize of the flat file is 20 gb per daily job..As of now we are using bulk insert utility============================================================================CREATE TABLE [dbo].[I_ERCH]( [BELNR] [varchar](12) NULL, [BUKRS] [varchar](4) NULL, [SPARTE] [varchar](2) NULL, [GPARTNER] [varchar](10) NULL, [VKONT] [varchar](12) NULL, [VERTRAG] [varchar](10) NULL, [BEGABRPE] [datetime] NULL, [ENDABRPE] [datetime] NULL, [ABRDATS] [datetime] NULL, [STORNODAT] [datetime] NULL, [ABRVORG] [varchar](2) NULL, [SIMULATION] [varchar](4) NULL, [BERGRUND] [varchar](2) NULL, [TOBRELEASED] [varchar](1) NULL, [HVORG] [varchar](4) NULL, [ADATSOLL] [datetime] NULL, [BELEGDAT] [datetime] NULL, [ABLEINH] [varchar](8) NULL, [BEGRU] [varchar](4) NULL, [INSTGRTYPE] [varchar](4) NULL, [KOFIZ] [varchar](2) NULL) ON [PRIMARY]GO===============================Stored Procedure ================================USE [SP_Reporting]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[USP_IMPORT_WEEKLY_ERCH]AS BEGIN /*--Declaring variables to store file name and location*/ DECLARE @DATAFILE VARCHAR(1024) ,@ERRORFILE VARCHAR(1024) ,@FORMATFILE VARCHAR(1024) ,@STRQRY VARCHAR(2000) ,@FLAG VARCHAR(1) ,@STARTDATE DATETIME ,@ENDDATE DATETIME ,@DESCRIPTION VARCHAR(8000) ,@UPDATE DATETIME ,@CHKFLAG VARCHAR(1) /*--Getting the location of data files, error files and format files*/ SELECT @DATAFILE = DataFileFolder + DataFileName ,@FORMATFILE = FormatFileFolder + FormatFileName ,@ERRORFILE = ErrorFileFolder + ErrorFileName ,@CHKFLAG = Flag ,@UPDATE=UpdatedOn FROM T_Weekly_BacklogJob_Status WHERE TableName = 'I_ERCH' EXEC [USP_CHECK_WEEKLY_FILE_EXISTANCE] @DATAFILE,@CHKFLAG,@FLAG OUT, @STARTDATE OUT/*Delete the previous records*/ IF @FLAG = 'A' BEGIN TRUNCATE TABLE I_ERCH update [SAP_Reporting].[dbo].[T_Weekly_BacklogJob_Status] set Flag='R',UpdatedOn=GETDATE() where TableName='I_ERCH' EXEC [USP_DROP_INDEX] 'I_ERCH'/*--Create bulk insert query and execute it inside the try catch block*/ SELECT @STRQRY = 'INSERT INTO dbo.I_ERCH WITH (TABLOCK) ([BELNR] ,[BUKRS] ,[SPARTE] ,[GPARTNER] ,[VKONT] ,[VERTRAG] ,[BEGABRPE] ,[ENDABRPE] ,[ABRDATS] ,[STORNODAT] ,[ABRVORG] ,[SIMULATION] ,[BERGRUND] ,[TOBRELEASED] ,[HVORG] ,[ADATSOLL] ,[BELEGDAT] ,[ABLEINH] ,[BEGRU] ,[INSTGRTYPE] ,[KOFIZ]) SELECT document.[BELNR] ,document.[BUKRS] ,document.[SPARTE] ,document.[GPARTNER] ,document.[VKONT] ,document.[VERTRAG] ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[BEGABRPE]) ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ENDABRPE]) ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ABRDATS]) ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[STORNODAT]) ,document.[ABRVORG] ,document.[SIMULATION] ,document.[BERGRUND] ,document.[TOBRELEASED] ,document.[HVORG] ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[ADATSOLL]) ,dbo.USF_CONVERT_SAP_DATE_TO_SQL_DATE(document.[BELEGDAT]) ,document.[ABLEINH] ,document.[BEGRU] ,document.[INSTGRTYPE] ,document.[KOFIZ] FROM OPENROWSET(BULK N''' + @DATAFILE +''' ,FORMATFILE = ''' + @FORMATFILE +''' ,ERRORFILE = ''' + @ERRORFILE +''') AS DOCUMENT' EXEC [USP_CREATE_INDEX] 'I_ERCH' BEGIN TRY EXEC (@STRQRY) SET @FLAG = 'C' SET @DESCRIPTION= 'The data has been loaded successfully' END TRY BEGIN CATCH SET @FLAG = 'F' SET @DESCRIPTION= ERROR_MESSAGE() END CATCH SET @ENDDATE = GETDATE() EXEC [USP_UPDATE_WEEKLY_LOG] 'I_ERCH',@STARTDATE,@ENDDATE,@FLAG,@DESCRIPTION END ELSE BEGIN IF @CHKFLAG <> 'C' AND @FLAG <> 'N' BEGIN SET @FLAG = @CHKFLAG SET @DESCRIPTION= '' EXEC [USP_UPDATE_WEEKLY_LOG] 'I_ERCH',@STARTDATE,@ENDDATE,@FLAG,@DESCRIPTION END END ENDGO==============================Format_ERCH.fmt=================================9.0211 SQLCHAR 0 14 "|" 1 BELNR Latin1_General_CI_AS2 SQLCHAR 0 6 "|" 2 BUKRS Latin1_General_CI_AS3 SQLCHAR 0 4 "|" 3 SPARTE Latin1_General_CI_AS4 SQLCHAR 0 12 "|" 4 GPARTNER Latin1_General_CI_AS5 SQLCHAR 0 14 "|" 5 VKONT Latin1_General_CI_AS6 SQLCHAR 0 12 "|" 6 VERTRAG Latin1_General_CI_AS7 SQLCHAR 0 10 "|" 7 BEGABRPE Latin1_General_CI_AS8 SQLCHAR 0 10 "|" 8 ENDABRPE Latin1_General_CI_AS9 SQLCHAR 0 10 "|" 9 ABRDATS Latin1_General_CI_AS10 SQLCHAR 0 10 "|" 10 STORNODAT Latin1_General_CI_AS11 SQLCHAR 0 4 "|" 11 ABRVORG Latin1_General_CI_AS12 SQLCHAR 0 6 "|" 12 SIMULATION Latin1_General_CI_AS13 SQLCHAR 0 4 "|" 13 BERGRUND Latin1_General_CI_AS14 SQLCHAR 0 3 "|" 14 TOBRELEASED Latin1_General_CI_AS15 SQLCHAR 0 6 "|" 15 HVORG Latin1_General_CI_AS16 SQLCHAR 0 10 "|" 16 ADATSOLL Latin1_General_CI_AS17 SQLCHAR 0 10 "|" 17 BELEGDAT Latin1_General_CI_AS18 SQLCHAR 0 8 "|" 18 ABLEINH Latin1_General_CI_AS19 SQLCHAR 0 4 "|" 19 BEGRU Latin1_General_CI_AS20 SQLCHAR 0 4 "|" 20 INSTGRTYPE Latin1_General_CI_AS21 SQLCHAR 0 2 "\r" 21 KOFIZ Latin1_General_CI_AS============================================================================Wed, 24 Aug 2011 01:12:43 GMTarpitsinghal.tcshttp://www.sqlservercentral.com/Forums/Topic1146121-386-1.aspxWell, I am a bit unsure if this is the right forum for the question. Still I will seek support...I have a table which holds documents in varbinary(max) column. Full text search is enabled and works fine. Document formats include common MS office formats, pdf, html and txt. My requirement is to open the document and focus on the search criteria (word or phrase) in the document. I find the location of the search criteria in the document based on CHARINDEX. Can anyone guide me? ThanksThu, 21 Jul 2011 10:44:07 GMTgeo123abramhttp://www.sqlservercentral.com/Forums/Topic1152436-386-1.aspxwe have to create one clr stored procedure for that we are using .net 2005 and created sql server project. for this project how can we add the ms word and excel references.is there any way to create the clr stored procedures.please help me.Regards,SarathTue, 02 Aug 2011 06:02:49 GMTSarath Vellampallihttp://www.sqlservercentral.com/Forums/Topic1142512-386-1.aspxMy project involves financial calcuations and to gain performance I'm trying to move the calculations (along with much of the lookups required for parameters to the calculations) into the database by using SQLCLR. For many reasons, simple Transact-SQL won't do the job.Unfortunately, one of the calculations is contained in a C .dll written by a third party and a .Net rewrite is not going to happen. Also, due to licensing costs, the C .dll is only installed for some of our clients. This has been dealt with in the past by using LoadLibrary to determine the existence of the C .dll.I have been attempting to use LoadLibrary within my SQLCLR code without success. The section I have marked as "For debugging" throws an exception that lets me know that the C .dll has not been found, even though it exists on the database server in the correct location (I've even tried moving everything to the root of C:).According to the sparse references I can find on this topic, I'm doing this right - but it's not working. I'm hoping that I've done something wrong that one of you more experienced with SQLCLR can point out.// All variables, classes and file names have been made generic to protect mepublic class Calc{ [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)] internal static extern IntPtr LoadLibrary([In, MarshalAs(UnmanagedType.LPStr)] string lpFileName); [DllImport("kernel32.dll", SetLastError = true)] static extern bool FreeLibrary(IntPtr hModule); [DllImport("kernel32.dll", CharSet=CharSet.Auto, SetLastError=true)] internal static extern IntPtr GetProcAddress([In] IntPtr hModule, [In, MarshalAs(UnmanagedType.LPStr)] string lpProcName); private IntPtr _moduleAddress = IntPtr.Zero; private IntPtr _calcAddress = IntPtr.Zero; public bool LoadCalcModule() { if (_moduleAddress == IntPtr.Zero) { _moduleAddress = LoadLibrary("c:\\Program Files\\Our Company\\calc.dll"); // For debugging, throw an exception so that // SSMS shows the results of LoadLibrary if (_moduleAddress == IntPtr.Zero) throw new Exception("Calc Module NOT Loaded"); else throw new Exception("Calc Module Loaded"); _calcAddress = _moduleAddress != IntPtr.Zero ? GetProcAddress(_moduleAddress, "DoCalc") : IntPtr.Zero; } return (_calcAddress == IntPtr.Zero); }}Fri, 15 Jul 2011 08:08:01 GMTjohn.delahunthttp://www.sqlservercentral.com/Forums/Topic1134313-386-1.aspxI used CLR Stored Procedures with a signed Assembly on a SQL Server and kept this running a few of those CLR Stored Procedures. They worked fine, until 2 weeks later we had found the following exception when any of the Stored Procedures was executed in the database.Here, the procedure SP_Test essentialy just called SqlContext.Pipe.ExecuteAndSend to run a SQL batch.Msg 6522, Level 16, State 1, Procedure SP_Test, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "SP_Test": System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Server.SmiContextFactory' threw an exception. ---> System.Threading.ThreadAbortException: Exception of type 'System.Threading.ThreadAbortException' was thrown.System.Threading.ThreadAbortException: System.TypeInitializationException: at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at Centrify.DirectAudit.AuditDatabase.StoredProcedures.SP_Test.StoredProcedure().After I restart the SQL Server instance, this problem did not happen again so far.In order to find out the cuase, I read the SQL Server ERRORLOG and could only identity the following possibly relevant messages.2011-06-15 10:02:20.90 spid53 Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\.2011-06-15 10:02:21.00 spid53 Error: 701, Severity: 17, State: 35.2011-06-15 10:02:21.00 spid53 There is insufficient system memory to run this query.I also got the process dump and found the stack trace as below.0:063> !dumpstackOS Thread Id: 0x1918 (63)Child-SP RetAddr Call Site000000000ca7daf0 000007fef985ffb3 KERNELBASE!RaiseException+0x39000000000ca7dbc0 000007fef9bb7477 mscorwks!RaiseTheExceptionInternalOnly+0x2ff000000000ca7dcb0 000007fef9bb9656 mscorwks!RaiseTheException+0x57000000000ca7dce0 000007fef9c3c525 mscorwks!BStrFromString+0x66000000000ca7dd10 000007fef9c3c53b mscorwks!RealCOMPlusThrow+0x35000000000ca7dd80 000007fef98117f9 mscorwks!RealCOMPlusThrow+0xb000000000ca7ddb0 000007fef9c96b38 mscorwks!MethodTable::DoRunClassInitThrowing+0x669000000000ca7e830 000007fef9cc3cca mscorwks!MethodTable::CheckRunClassInitThrowing+0x68000000000ca7e870 000007ff002cc14b mscorwks!JIT_GetSharedNonGCStaticBase_Helper+0xfa000000000ca7ea30 000007ff002cba59 System_Data!Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()+0x1b000000000ca7ea70 000007ff002c9c91 System_Data!Microsoft.SqlServer.Server.SqlContext.get_Pipe()+0x9000000000ca7eaa0 000007ff00450633 Centrify.DirectAudit.AuditDatabase.StoredProcedures.SP_Test.StoredProcedure()+0x51000000000ca7eaf0 000007ff002c9ab9 SqlAccess!DynamicClass.SQLCLR_Eval(IntPtr, IntPtr)+0x13000000000ca7eb30 000007fef990f18a System_Data!DomainBoundILStubClass.IL_STUB(Int64, Int64)+0x2c9aba000000000ca7eb60 0000000001be3151 mscorwks!UMThunkStubAMD64+0x7a000000000ca7ebf0 000007fef9d15744 sqlservr!AppDomainCallback<FunctionCallBinder_2<void,void (__cdecl*)(CXVariant * __ptr64,CXVariant * __ptr64),CXVariant * __ptr64,CXVariant * __ptr64> >+0x11000000000ca7ec20 000007fef9d9bc46 mscorwks!ExecuteInAppDomainHelper+0x94000000000ca7eca0 0000000001d30f38 mscorwks!CorHost2::ExecuteInAppDomain+0x226000000000ca7ee90 0000000002082abf sqlservr!CallProtectorImpl::CallWithSEH<AppDomainUserCallTraits,long,MethodCallBinder_3<long,ICLRRuntimeHost,long (__cdecl ICLRRuntimeHost::*)(unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64) __ptr64,unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64> >+0x28000000000ca7eed0 0000000002294936 sqlservr!CallProtectorImpl::CallExternalFull<AppDomainUserCallTraits,long,MethodCallBinder_3<long,ICLRRuntimeHost,long (__cdecl ICLRRuntimeHost::*)(unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64) __ptr64,unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64> >+0x1af000000000ca7efe0 000000000246bc17 sqlservr!CAppDomain::InvokeClrFn+0x146000000000ca7f090 000000000260e1b6 sqlservr!ExecUdx+0x3a7000000000ca7f240 0000000001ae0ba4 sqlservr!CClrProc::FExecute+0x7a6000000000ca7f440 00000000017053e2 sqlservr!_chkstk+0x20bf54000000000ca7f580 000000000170a361 sqlservr!process_request+0x312000000000ca7f810 00000000016bc32e sqlservr!process_commands+0x1c1000000000ca7fad0 00000000016bc5c9 sqlservr!SOS_Task::Param::Execute+0xee000000000ca7fbe0 00000000016b5cc4 sqlservr!SOS_Scheduler::RunTask+0xc9000000000ca7fc70 000000000155e837 sqlservr!SOS_Scheduler::ProcessTasks+0xb4000000000ca7fce0 0000000001522f59 sqlservr!SchedulerManager::WorkerEntryPoint+0xe7000000000ca7fd80 00000000015372b0 sqlservr!SystemThread::RunWorker+0x59000000000ca7fdc0 00000000014f72f8 sqlservr!SystemThreadDispatcher::ProcessWorker+0x130000000000ca7fe60 0000000074b037d7 sqlservr!SchedulerManager::ThreadEntryPoint+0x128000000000ca7ff00 0000000074b03894 msvcr80!endthreadex+0x47000000000ca7ff30 0000000076de652d msvcr80!endthreadex+0x104000000000ca7ff60 0000000076f1c521 kernel32!BaseThreadInitThunk+0xd000000000ca7ff90 0000000000000000 ntdll!RtlUserThreadStart+0x1dThe environment context was- Windows 7 Service Pack 1 64-bit with .Net Framework 2.0 Service Pack 2- SQL Server 2005 Standard 64-bitBy the way, it does not give me any hint on the cause of the exception. Also, the problem would not always occur and is hard to reproduced.It is a rare problem, but hope anyone could leave some advise here.Thu, 30 Jun 2011 03:58:34 GMTcwfongworkhttp://www.sqlservercentral.com/Forums/Topic1133988-386-1.aspxI was reading an [url=http://msdn.microsoft.com/en-us/library/ms254498(VS.85).aspx]article from MSDN [/url]which says that a stored procedure created with managed code can run on a client computer, I've been looking for several sites in the MSDN and the web and have not found how to achieve it, I hope some of you may guide me or give me a reference where I can achieve this, thanks for your kind attention.Regards.Wed, 29 Jun 2011 12:31:12 GMTChacMollhttp://www.sqlservercentral.com/Forums/Topic1130198-386-1.aspxHi,I have a CLR Procedure which calls a TSQL procedure and handles the results via a SQlPipe. The reason for this is the TSQL Proc returns mutliple result sets, and the CLR Proc handles them to only return certain ones that the caller requested.When the CLR Proc returns a table, it is accompanied by the error message:--------------------------------Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.--------------------------------If the CLR Proc returns a scalar single value as a result, the error is not returned.I'm researching it but does anyone know if the CLR integration has a problem with handling result sets as output?Thu, 23 Jun 2011 01:42:38 GMTtuseauhttp://www.sqlservercentral.com/Forums/Topic1013509-386-1.aspxHello, thanks for viewing this. I've been working on a sendmail feature based on this post http://www.mssqltips.com/tip.asp?tip=1795 but ran into some errors.I get this: Msg 6218, Level 16, State 3, Line 1CREATE ASSEMBLY for assembly 'sqlserversendmail' failed because assembly 'sqlserversendmail' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this messageThe assembly .cs file I created looks like thisusing System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Net;using System.Net.Mail;public partial class StoredProcedures{ [Microsoft.SqlServer.Server.SqlProcedure] public static void SQLServerSendMail(string msgBody, string msgSubject) { System.Net.Mail.MailMessage msg = new MailMessage(); msg.Body = msgBody; msg.Subject = msgSubject; // Put your code here SmtpClient sc = new SmtpClient("smtp.xxx.xxx.xxx"); sc.Credentials = new NetworkCredential("myemail@mail.com", "mypw"); sc.Send(msg); }};I created the dll using this: csc /target:library /out:c:\sqlserversendmail.DLL c:\sqlserversendmail.csI also tried using the MSDB database as the article suggested. Then I tried it in the User database.I enabled CLR. Notes:I'm using Sql Server 2008 Express.Using VS2010I created the dll with .net v4.0.30319.I also tried this...alter database MembersSQL set trustworthy onand this...sp_configure 'clr enabled', 1reconfigureHere is the code I ran in SQL Server 2008 management studio.CREATE ASSEMBLY SqlServerSendMail From 'c:\sqlserversendmail.dll'WITH PERMISSION_SET = UNSAFEGOCREATE PROCEDURE [dbo].[spSqlServerSendMail] @msgBody nvarchar(2000), @msgSubject nvarchar(50)WITH EXECUTE AS SELFASEXTERNAL NAME [SqlServerSendMail].[SqlServerSendMail.StoredProcedure].[spSqlServerSendMail]Any ideas? Thanks.Sat, 30 Oct 2010 14:06:12 GMTelsamsamhttp://www.sqlservercentral.com/Forums/Topic1121677-386-1.aspxI am having an issue with blank values in smalldatetime fields.The problem being that a stored procedure inserts data into a table, when the data inserts a blank field into a smalldatetime error295 is displayed, what I want is when the field is blank the column is set to Null. I have looked at the replace command but am not sure of the syntax to set the column to Null the column is set the allow Null values.REPLACE(@FIELD24,'''','NULL')Any help would be much appreciatedWed, 08 Jun 2011 06:18:28 GMTclucasihttp://www.sqlservercentral.com/Forums/Topic1119962-386-1.aspxI am trying to create a non-clustered index an a CLR-derived column in an indexed view, but it doesn't allow me. Here is the error message:Cannot create index or statistics 'Ix_IndexedView_One' on view 'vwIndexedViewTest' because cannot verify key column 'idPlusOne' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.Books online says that CLR derived columns can't be part of the clustered index, but doesn't mention non-clustered indexes.Here is the test script:The table:[code="sql"]create table [dbo].[IndexedViewTest]( [id] [int] identity(1,1) not null, [name] [varchar](50) null constraint PK_id primary key clustered ( [id] asc ))[/code]CLR function:[code="other"][Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,IsPrecise=true, DataAccess=DataAccessKind.None, SystemDataAccess=SystemDataAccessKind.None)]public static SqlInt32 AddOne(SqlInt32 number){ return number + 1;}[/code]Indexed view:[code="sql"]create view dbo.vwIndexedViewTestwith schemabindingasselect id, name, dbo.AddOne(1) as idPlusOnefrom dbo.IndexedViewTestgocreate unique clustered index Ix_IndexedView on vwIndexedViewTest(id)[/code]Now trying to create an index on the CLR column:[code="sql"]create index Ix_IndexedView_One on vwIndexedViewTest(idPlusOne) --getting an error[/code]You can definitely create indexes on calculated CLR columns as long as they are persisted. As you can see, all settings for the CLR are correct, I don't see why SQL Server wouldn't be able to create an index on that column..Sun, 05 Jun 2011 09:53:16 GMTfayilthttp://www.sqlservercentral.com/Forums/Topic1113495-386-1.aspxhi guys,i have encountered a condition where i need to read selective data from a typical excel and sheet and write that data in required format in other excel sheet.i have done this thing on text files but not too sure about excel files. It was also giving me some office.dll version problem when creating assembly and sp containing this code. cant copy here as i am home.anyone tried this, please help. thanks.Mon, 23 May 2011 11:11:31 GMTtayyab_ssuethttp://www.sqlservercentral.com/Forums/Topic1027478-386-1.aspxA developer wanted an UNSAFE assembly deployed to production, and, after fiddling with the C# code a bit, I found that a string declared as static in the code could be declared as a constant instead. This was necessary for deploying the assembly with EXTERNAL_ACCESS permission set, instead of UNSAFE.I got the following error when I attempted to create the assembly with EXTERNAL_ACCESS perm set, while static variable was in the code:[quote]Msg 6212, Level 16, State 1, Line 3CREATE ASSEMBLY failed because method 'xxxx' on type 'StoredProcedures' in external_access assembly 'xxxx' is storing to a static field. Storing to a static field is not allowed in external_access assemblies.[/quote]Is there a CLR performance cost from changing a string from static to a constant?Am I doing the right thing by not allowing the assembly to be deployed with UNSAFE perm set?Mon, 29 Nov 2010 11:01:38 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic1111672-386-1.aspxI have the following SQL CLR procedure....It is called like the following: exec sp_LoopCalcUnits1 'select top 5000 invoicekey from rb_ruleinvoice where rb_ruleid = 8251 ', 8251,3As I run tests using the top 1000,2000, 3000 etc the performance is grea, but as I move into the 5000-10000 range it seems to be getting exponentially slower and I do not understand why.Can anyone offer any advise?Tony [Microsoft.SqlServer.Server.SqlProcedure] public static void sp_LoopCalcUnits1(String zSql, String zRuleId, int iCalcType) { SqlCommand cmd; SqlConnection con = new SqlConnection("context connection=true"); cmd = new SqlCommand(zSql); using (con) { try { // run the base query cmd.Connection = con; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); List<String> myList = new List<string>(); long lCount; while (rdr.Read()) { myList.Add((String)rdr["invoicekey"]); } rdr.Close(); rdr.Dispose(); zSql = "insert into tftest values (1 ,CURRENT_TIMESTAMP)"; cmd.CommandText = zSql; cmd.ExecuteNonQuery(); foreach (String zTemp in myList) { CalcUnits1(con, zTemp, zRuleId, iCalcType); } zSql = "insert into tftest values (100 ,CURRENT_TIMESTAMP)"; cmd.CommandText = zSql; cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { if (con != null) con.Close(); } } } public static void CalcUnits1(SqlConnection con,String zInvoiceKey, String zRuleId, int iCalcType) { // SqlConnection con = new SqlConnection("context connection=true"); SqlDataReader sdrResults; SqlCommand cmd; //SqlPipe pipe = SqlContext.Pipe; String zSql; decimal dSupplierID; decimal dInvoiceTotal; decimal dInvoiceSubtotal; decimal dTermsDiscountAmount; decimal dQueryUnits; decimal dQueryGross; decimal dQueryNet; decimal dGrossTotal; decimal dNetTotal; decimal dUnitsTotal; decimal dRebateUnitsTotal; int iLoopCount; dInvoiceTotal = 0; dInvoiceSubtotal = 0; dTermsDiscountAmount = 0; dGrossTotal = 0; dNetTotal = 0; dUnitsTotal = 0; dRebateUnitsTotal = 0; // get the base invoice information //using ( con ) //{ try { // run the base query zSql = ""; dSupplierID = 0; cmd = new SqlCommand("select TotalInvoiceAmount,InvoiceSubTotal,termsdiscountamount,supplierkey from invoicetotal a,invoice b where a.invoicekey = b.invoicekey and a.invoicekey = " + zInvoiceKey); cmd.Connection = con; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { dSupplierID = (decimal)rdr["supplierkey"]; dInvoiceTotal = (decimal)rdr["TotalInvoiceAmount"]; dInvoiceSubtotal = (decimal)rdr["InvoiceSubTotal"]; dTermsDiscountAmount = (decimal)rdr["termsdiscountamount"]; } rdr.Close(); rdr.Dispose(); cmd.Dispose(); if (dSupplierID != 0) { // if we found a supplier, make 3 passes at the invoice // 1- lineitems // 2- charges\allowances // 3 - taxes // for (iLoopCount = 1; iLoopCount <= 3; iLoopCount++) for (iLoopCount = 1; iLoopCount <= 1; iLoopCount++) { switch (iLoopCount) { case 1: zSql = "select sum(UnitsShipped),sum(GrossAmount),sum(NetAmount) from InvoiceLineItem a,Invoice b where "; zSql = zSql + " a.invoicekey = " + zInvoiceKey; zSql = zSql + " and a.invoicekey = b.invoicekey "; zSql = zSql + " and a.ProductRebateCategory in ("; zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId; zSql = zSql + " and c.SupplierId = B.supplierkey"; zSql = zSql + ") "; break; case 2: zSql = "select 1,a.TaxAmount,a.TaxAmount,a.ProductRebateCategory,'C' as AmountType from invoicetotaltax a,Invoice b where "; zSql = zSql + " a.invoicekey = " + zInvoiceKey; zSql = zSql + " and a.invoicekey = b.invoicekey "; zSql = zSql + " and a.ProductRebateCategory in ("; zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId; zSql = zSql + " and c.SupplierId = B.supplierkey"; zSql = zSql + ") "; break; case 3: zSql = "select 1,Amount,Amount,ProductRebateCategory,Type as AmountType from InvoiceTotalAdditionalCosts a,Invoice b where "; zSql = zSql + " a.invoicekey = " + zInvoiceKey; zSql = zSql + " and a.invoicekey = b.invoicekey "; zSql = zSql + " and a.ProductRebateCategory in ("; zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId; zSql = zSql + " and c.SupplierId = B.supplierkey"; zSql = zSql + ") "; break; } dQueryUnits = 0; dQueryGross = 0; dQueryNet = 0; // get the units and update the total information if (zSql != "") { cmd.CommandText = zSql; sdrResults = cmd.ExecuteReader(); while (sdrResults.Read()) { dQueryUnits = (decimal)sdrResults[0]; dQueryGross = (decimal)sdrResults[1]; dQueryNet = (decimal)sdrResults[2]; dGrossTotal = dGrossTotal + dQueryGross; dNetTotal = dNetTotal + dQueryNet; dUnitsTotal = dUnitsTotal + dQueryUnits; } sdrResults.Close(); sdrResults.Dispose(); cmd.Dispose(); switch (iCalcType) { case 1: dRebateUnitsTotal = dRebateUnitsTotal + dQueryGross; break; case 2: dRebateUnitsTotal = dRebateUnitsTotal + dQueryNet; break; case 3: dRebateUnitsTotal = dRebateUnitsTotal + dQueryUnits; break; } } } } zSql = "insert into tftest values (" + zInvoiceKey + " ,CURRENT_TIMESTAMP)"; cmd.CommandText = zSql; cmd.ExecuteNonQuery(); cmd.Dispose(); } catch (Exception ex) { throw ex; } finally { } //} }Thu, 19 May 2011 07:22:40 GMTtfeuzhttp://www.sqlservercentral.com/Forums/Topic1109340-386-1.aspxI know that EXTERNAL_ACCESS and UNSAFE assemblies cannot be created in a SQL Server database, unless they are either signed or the TRUSTWORTHY bit is set to ON.SAFE assemblies, on the other hand, can be created as signed or unsigned.What would be the advantages of signing a SAFE assembly?I know one of them is that no third party would be able to alter its contents.Are there any other reasons why signing a SAFE assembly would be worth it?Mon, 16 May 2011 07:59:03 GMTMarios Philippopoulos