InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 02:56:21 GMT20http://www.sqlservercentral.com/Forums/Topic1454154-386-1.aspxI am a total newbie and have a question to ask. I am the only DBA on a team managing several SQL servers and we have a new project that require the creation of a CVS flat file.I was give a note pad document with ASP script and (here is the crazy part) I don’t know what to do with it. What’s also weird is that the DBA that gave it to me said it’s a very old version of ASP that it was done in and at the top of the script it says <%@ language="vbscript" %><!--#include file="Connections/connSecurity.asp" --><%Also a user needs to enter a start and end date and can enter SSN to get specific info. Is there a way to implement Report Server to generate CVS Flat files and maybe use this scriptCan i design this using Report Server alone. Please let me know. Your help is appreciated. Fri, 17 May 2013 12:00:02 GMTjayoub1http://www.sqlservercentral.com/Forums/Topic1449544-386-1.aspxHello guys,I have problem that I can't resolve .. I want to make simple store procedure using .net 4.5 and [b]I cant deploy my project[/b] .. I have 3 projects in my solution,and one of them is Sql_Clr (Sql Server Database Project) which can't deploy.. I can build solution,but when I click deploy nothing happens..In net 3.5 everything was fine... Otherwise,when I try to create assembly in SQL 2008R2 like this:[code="plain"]create assembly Sql_Clrfrom 'F:\IS-AutoSkola\Sql_Clr\bin\Debug\Sql_Clr.dll'with permission_set = unsafeGo[/code]it shows me an error:[i]CREATE ASSEMBLY for assembly 'Sql_Clr' failed because assembly 'Sql_Clr' 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 message[/i]Any help is appreciated .. :)Sun, 05 May 2013 15:13:17 GMTjordanovskibojan 74807http://www.sqlservercentral.com/Forums/Topic1442433-386-1.aspxI Have A Simple MDX Query "Select [Measures].[Book Value GAAP USD] on COLUMNS From [Positions Cube]"I Need To Run THis Query Using CLR Program (C# Code) and Register It In SSAS Server I Mean In AssembliesI Tried SomeThing Like This .... Its Not Working (I am Pretty Much New To These Type Of Things)using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using Microsoft.AnalysisServices;using Microsoft.AnalysisServices.AdomdServer;namespace BI.MDX{ public sealed class BI_MDX_PosTrans_SP { public static float GAAPBV(float bv) { AdomdCommand objCommand; string sConnString; string strCommand; AdomdConnection objConnection; strCommand = "Select [Measures].[Book Value GAAP USD] on COLUMNS From [Positions Cube]"; sConnString = "Data Source=dev.bisqlas003.metlife.com;Initial Catalog=Inv_PosTrans"; AdomdConnection objConnection.ConnectionString = sConnString; objConnection.Open() objCommand = new AdomdCommand(strCommand, objConnection); objCellSet = objCommand.ExecuteCellSet();Mon, 15 Apr 2013 11:55:40 GMTgopinathreddy.dhttp://www.sqlservercentral.com/Forums/Topic1441906-386-1.aspxHi Guys,What I need to do is find rows that may have credit card in the text. Here is the regular expression I am using, but this one ignores the ones with space or hyphens.(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})What I need to do is:Find character string that may have CC#s and the CC# might have space, hyphen or no delimeters between the group of numbers. The regex should work with all major CCs. The CC#s will be somewhere in between the text not at the start.For example: "blah blah... 4258-1234-5678-1234. Email sent blah blah..." The RegEx should catch the above string as the string of numbers look likes Visa CC #.Anyone who has such RegEx? Thanks,LauraFri, 12 Apr 2013 13:50:46 GMTLaura_SqlNovicehttp://www.sqlservercentral.com/Forums/Topic1442059-386-1.aspxHi guys i have one problem when i create linked server show data from excel sheet1 in sql server 2005 it give me this error ----error OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST65442339" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST65442339" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST65442339". ---error--------------- the linked server i create is as following : EXEC master.dbo.sp_addlinkedserver @server = N'TEST6544233', @srvproduct=N'Excel 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\spare.XLSX', @provstr=N'Excel 12.0;HDR=Yes' select * from TEST6544233...[sheet1$] why this problem madeSat, 13 Apr 2013 16:05:48 GMTahmed_elbarbary.2010http://www.sqlservercentral.com/Forums/Topic1438144-386-1.aspxHi,I've enjoyed several of the Stairway series to date. I'm just wondering if you could provide one on creating CLR stored procs and UDFs?NickTue, 02 Apr 2013 19:00:08 GMTnick.gekashttp://www.sqlservercentral.com/Forums/Topic1426740-386-1.aspxPlease consider the following process:I have a database table which may contain one of 15 different columns like Forename, Surname, Address1, Address2 etc.. Sometimes the table contains forename, surname and sometimes the table may contain Fullname. Other times the table may contain Address1, Address2, Address3 and other it may contain Address1, Town, Postcode etc..This data needs to go into a standard staging table which has a fixed number of columns.My current process uses a series of IF statements to work out what columns are in the original table, then it builds an INSERT INTO SELECT statement. Along the way it references several SQL CLR C# UDFs.This works fine at the moment but I would like to change the design and pass the entire row of data to one SQL CLR C# stored procedure which can then perform all the required string manipulation and return a new row which I can insert into my standardised staging table.I'm considering the use of table valued parameters but I wonder if this is appropriate?Any ideas?Tue, 05 Mar 2013 04:29:40 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic523889-386-1.aspxI just want to create a group login then all the users i will create i'l just add them to the group login i created.can you please help how to create group login and how to input the users login in the group login created? thanx for advanceThu, 26 Jun 2008 00:17:44 GMTrolyn_jimenezhttp://www.sqlservercentral.com/Forums/Topic1421810-386-1.aspxI am posting XML file to Web services using C# , but I am getting error when I am requesting the response 'Server Error - 500 - You are not allowed to access the system . Any help will be appreciated.[quote]protected void Page_Load(object sender, EventArgs e) { WebRequest req = null; WebResponse rsp = null; try { string fileName = Server.MapPath("~\\test.xml"); string uri = "http://212.170.239.71/appservices/http/FrontendService"; req = WebRequest.Create(uri); //req.Proxy = WebProxy.GetDefaultProxy(); // Enable if using proxy req.Credentials = new NetworkCredential("myusername", "mypassword"); req.Method = "POST"; // Post method req.ContentType = "text/xml"; // content type // Wrap the request stream with a text-based writer StreamWriter writer = new StreamWriter(req.GetRequestStream()); // Write the XML text into the stream writer.WriteLine(this.GetTextFromXMLFile(fileName)); writer.Close(); // Send the data to the webserver rsp = req.GetResponse(); //I am getting error over here StreamReader sr = new StreamReader(rsp.GetResponseStream()); string result = sr.ReadToEnd(); sr.Close(); Response.Write(result); } catch (WebException webEx) { Response.Write(webEx.Message.ToString()); Response.Write(webEx.StackTrace.ToString()); } catch (Exception ex) { Response.Write(ex.Message.ToString()); Response.Write(ex.StackTrace.ToString()); } finally { if (req != null) req.GetRequestStream().Close(); if (rsp != null) rsp.GetResponseStream().Close(); } } //Function to read xml data from local system /// <summary> /// Read XML data from file /// </summary> /// <param name="file"></param> /// <returns>returns file content in XML string format</returns> private string GetTextFromXMLFile(string file) { StreamReader reader = new StreamReader(file); string ret = reader.ReadToEnd(); reader.Close(); return ret; }[/quote]Tue, 19 Feb 2013 12:46:00 GMTCitiinvestorhttp://www.sqlservercentral.com/Forums/Topic1419790-386-1.aspxI have a C# project to implement a complex number type and wanted to execute[code="sql"]SELECT DISTINCT c1 FROM complex[/code]This gives the error:[code]Msg 421, Level 16, State 1, Line 1The ComplexNumber data type cannot be selected as DISTINCT because it is not comparable.[/code]I then implemented the IComparable interface for the C# struct, but this made no difference.Is there a way to make a CLR implemented data type recognized as 'comparable' to SQL?I've already found that there doesn't seem to be a way to overload operators in SQL so I have to do[code="sql"]declare @c ComplexNumberdeclare @d ComplexNumberset @c = '1+0i'set @d = @cselect @c.Plus(@d).ToString()// Result is 2+0i[/code]So I won't be surprised if the answer is a flat "No".I also found that[code="sql"]SELECT c1 FROM complex GROUP BY c1[/code]gives[code]Msg 249, Level 16, State 1, Line 2The type "ComplexNumber" is not comparable. It cannot be used in the GROUP BY clause.[/code] As expected! :)So, as an alternative, is there some way to do the equivalent of 'SELECT DISTINCT' or 'SELECT ... GROUP BY' in a set based way when using CLR defined types?By the way, I'm actually using SQLserver 2012 (not 2005) but decided this was the most appropriate forum.Wed, 13 Feb 2013 18:44:31 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic1409320-386-1.aspxI'm looking for some guidance as to when to use things like String Vs SqlString in CLR functions.From what I can gather there are differences in the collation and locale but what are the other implications?How is performance affected?Also, can anyone point me towards an article on VS2008 unit tests with SQL CLR functions?thanksSun, 20 Jan 2013 15:56:50 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspxI am looking into writing a regular expression for validating emails. I am an absolute novice at this, could some explain what are regular expressions, with an example of how to implement it when validating emails using a CLR function.links to clear good exmples and explanations would be very much appreciatedMon, 14 Jan 2013 08:38:56 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic437924-386-1.aspxIm working in a Oracle to SQL migration project, I need to migrate a functionwhich is using Raiserror() I have a function in Oracle like this,create function fn_name( parameters )returns intas beginif ( condition )-- do some logicelseraiseerror()endI need to migrate this to SQL server 2005.From next version we wont have Extended procedure, so its better to avoid. Instead that we can use CLR integration.Can anyone help me out...Wed, 02 Jan 2008 05:23:16 GMTsun.psnahttp://www.sqlservercentral.com/Forums/Topic1317342-386-1.aspxI am very new to using CLRs but I am starting with a small problem and I am having a lot of problems with it. I am trying to create an aggregate to calculate the median. I have the C# done and I deployed it via Visual Studio (VS 2005). When I run the code with one select it works fine all the time. Sometimes though when I run multiple selects at once (for a union query), I get a null reference exception or the sql server services will restart. In one instance the service never restarted. Here is my median code:[code="plain"]using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Collections.Generic;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000)]public struct Median : IBinarySerialize{ public void Init() { dataset = new List<double>(); } public void Accumulate(SqlDouble Value) { if (!Value.IsNull) dataset.Add(Value.Value); } public void Merge(Median Group) { this.dataset.AddRange(Group.dataset.ToArray()); } public SqlDouble Terminate() { return findMedian(); } private SqlDouble findMedian() { if (dataset.Count == 0) return SqlDouble.Null; dataset.Sort(); //get the median int size = dataset.Count; int mid = size / 2; SqlDouble median = (size % 2 != 0) ? dataset[mid] : (dataset[mid] + dataset[mid - 1]) / 2; return median; } public void Read(System.IO.BinaryReader r) { int cnt = r.ReadInt32(); this.dataset = new List<double>(); for (int i = 0; i < cnt; i++) { this.dataset.Add(r.ReadDouble()); } } public void Write(System.IO.BinaryWriter w) { if (this.dataset.Count > 0) { w.Write(this.dataset.Count); foreach (double d in this.dataset) { w.Write(d); } } else w.Write(0); } // List to hold all of the values for the Median. private List<double> dataset;}[/code] The only thing that makes sense would be the serialization not working correctly but I have tried three separate implementations and one of them was written for the median by an MVP. Any help would be greatly appreciated.NickMon, 18 Jun 2012 08:28:48 GMTnick947http://www.sqlservercentral.com/Forums/Topic1380779-386-1.aspxGood day all!!! I'm new to this forum. So please forgive me if I have posted in the wrong forum. I'm create a CLR Stored Procedure using VS 2010, VB.Net using MS.SQL Server 2008. See code below[code="vb"]Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub user_login1(ByVal User_UserName As SqlString, ByVal User_Password As SqlString, ByRef Station As SqlString, <Out()> ByVal Users_RowID As SqlInt32, <Out()> ByVal Users_Name As SqlString, <Out()> ByVal Success As SqlBoolean, <Out()> ByVal Default_Message As SqlString) Using conn As New SqlConnection("context connection=true") Dim LoginCommand As New SqlCommand() Dim sqlparam(6) As SqlParameter sqlparam(0) = New SqlParameter("@User_UserName", SqlDbType.VarChar, 50) sqlparam(1) = New SqlParameter("@User_Password", SqlDbType.VarChar, 50) sqlparam(2) = New SqlParameter("@Station", SqlDbType.VarChar) sqlparam(3) = New SqlParameter("@Users_RowID", SqlDbType.Int, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0) sqlparam(4) = New SqlParameter("@Users_Name", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty) sqlparam(5) = New SqlParameter("@Success", SqlDbType.Bit, 1, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, 0) sqlparam(6) = New SqlParameter("@Default_Message", SqlDbType.VarChar, 200, ParameterDirection.Output, False, 0, 0, String.Empty, DataRowVersion.Default, String.Empty) sqlparam(0).Value = User_UserName sqlparam(1).Value = User_Password sqlparam(2).Value = Station sqlparam(3).Value = Users_RowID sqlparam(4).Value = Users_Name sqlparam(5).Value = Success sqlparam(6).Value = Default_Message LoginCommand.Parameters.Add(sqlparam) LoginCommand.CommandText = "DECLARE @rowsaffected INT;" & "DECLARE @User_Status_RowID INT;" & "DECLARE @User_Cursor CURSOR" & "SET @Success = 0" & "SET @Users_RowID = 0" & "SET @Users_Name = ''" & "SET @User_Status_RowID = 0" & "IF EXISTS (SELECT * FROM [dbo].[user] WHERE User_UserName = @User_UserName AND CONVERT(varbinary, User_Password) = CONVERT(varbinary, @User_Password))" & "BEGIN " & "SELECT @Users_RowID = User_RowID, @User_Status_RowID = User_Status_RowID " & "FROM dbo.[user] " & "WHERE User_UserName = @User_UserName AND User_Password = @User_Password" & "SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) " & "FROM dbo.[user] " & "INNER JOIN dbo.title ON (dbo.[user].Title_RowID = dbo.title.Title_RowID) " & "WHERE User_UserName = @User_UserName AND User_Password = @User_Password" & "IF ((LOWER(@User_UserName) <> 'administrator') AND (@User_Status_RowID = 1))" & "BEGIN" & "SET @Default_Message = 'User ''' + @Users_Name + ''' is already logged in.'" & "SET @Success = 0" & "RETURN" & "END" & "ELSE" & "BEGIN" & "EXEC [dbo].[user_status_update] @User_UserName, @Station, 1, @rowsaffected OUTPUT" & "IF ((@rowsaffected = 2) AND (@@ERROR = 0))" & "BEGIN" & "SET @Default_Message = 'You have been logged in successfully'" & "SET @Success = 1" & "END" & "ELSE" & "BEGIN" & "ROLLBACK TRANSACTION" & "SET @Default_Message = 'An error occured while attempting to log you in. Please try again'" & "SET @Success = 0" & "RETURN" & "END" & "END" & "END" & "ELSE " & "BEGIN" & "ROLLBACK TRANSACTION" & "SET @Default_Message = 'Invalid username and/or password. Try again'" & "SET @Success = 0" & "RETURN" & "END" LoginCommand.Connection = conn conn.Open() LoginCommand.ExecuteNonQuery() conn.Close() End Using End SubEnd Class[/code]When I try to run it I keep getting the same error:Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6580, Level 16, State 1, Procedure user_login1, Line 1 Declarations do not match for parameter 4. .NET Framework reference and T-SQL OUTPUT parameter declarations must match.Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6552, Level 16, State 3, Procedure user_login1, Line 1 CREATE PROCEDURE for "user_login1" failed because T-SQL and CLR types for parameter "@Users_RowID" do not match.I have tried changing the type to: Int32, Integer, SqlInt32, SqlInteger, and so on and so on; with no luck...Please helpThanx in advance...Sun, 04 Nov 2012 03:50:21 GMTazinyamahttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxI want to be able to pass a string and have it perform the equation within the string. For example, if I pass it "(7 + 1) / 3)", I owuld expect to get back 2.67.I have seen several posts on here, but none of them appear to work. So I attempted to create one myself. This is what I have in my stringEval function:Partial Public Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction()> _ Public Shared Function stringEval(ByVal equation As String) As SqlDecimal Dim table As New DataTable Dim eqObject As Object eqObject = table.Compute(equation, "") Dim results As SqlDecimal = SqlDecimal.Parse(eqObject.ToString()) Return results End FunctionEnd ClassI then try to test it with the following query: declare @results decimal(18,2) select @results = dbo.stringEval('(7 + 1) / 3') select @results as resultsWhen I run this in debug mode in VS2010, I see that the variable 'results' contains 2.6666666667 when it gets to the return statement. However, the query returns '3.00'. I feel like I am so very close to what everyone is asking for, but nobody seems to have an answer for.Can anyone help me resolve this?Many thanks!P.S., yes I know that this is VB instead of C#. I started my effort thinking that I could use the VB Eval function, but then realized that went away with VB.Net.Tue, 30 Oct 2012 15:04:44 GMTsteve.laforgehttp://www.sqlservercentral.com/Forums/Topic1374833-386-1.aspxI'm researching to create a C# CLR trigger that implements the auditing for any table in a database. One generic piece of code would be able to handle the auditng for any table in the db. The experiments look good, except for one thing: I can not get for which table the trigger was fired.Here's some experimental c# code I've got so far. [code="other"]using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server;using System.Data.SqlTypes;namespace Jfh.Sql.Auditing{ public partial class AuditTriggers { static AuditTriggers() { SqlPipe pipe = SqlContext.Pipe; SqlTriggerContext context = SqlContext.TriggerContext; pipe.Send(String.Format("static constructor called: column count = {0}, Trigger action = {1}.", context.ColumnCount, context.TriggerAction)); } public AuditTriggers() { SqlPipe pipe = SqlContext.Pipe; SqlTriggerContext context = SqlContext.TriggerContext; pipe.Send(String.Format("public constructor called: column count = {0}, Trigger action = {1}.", context.ColumnCount, context.TriggerAction) ); } [Microsoft.SqlServer.Server.SqlTrigger (Name="AuditTrigger", Target="dbo.Table1", Event="FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger() { SqlPipe pipe = SqlContext.Pipe; SqlTriggerContext context = SqlContext.TriggerContext; pipe.Send(String.Format("Trigger fired: column count = {0}, Trigger action = {1}.", context.ColumnCount, context.TriggerAction)); using (SqlConnection conn = new SqlConnection(@"context connection=true")) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"select @@procID, OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID);"; SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow | CommandBehavior.SequentialAccess); if (reader.Read()) { SqlInt32 object_id = reader.GetSqlInt32(0); SqlString object_name = reader.GetSqlString(1); pipe.Send(String.Format("{0}, {1}", object_id, object_name)); } else { pipe.Send("read found no data"); } } } }}[/code]I deployed the resulting assembly on my server and then execute the following T-SQL to create 2 test tables plus a trigger on each table:[code="sql"]create table dbo.Table1 ( ID int identity(1,1) not null, name nvarchar(256) not null, constraint PK_Table1 primary key clustered (ID));goCREATE TRIGGER [dbo].[AuditTrigger1] ON [dbo].[Table1] FOR INSERT, DELETE, UPDATE AS EXTERNAL NAME [Jfh.Sql.Auditing].[Jfh.Sql.Auditing.AuditTriggers].[AuditTrigger]gocreate table dbo.Table2 ( ID int identity(1,1) not null, name nvarchar(256) not null, constraint PK_Table2 primary key clustered (ID));goCREATE TRIGGER [dbo].[AuditTrigger2] ON [dbo].[Table2] FOR INSERT, DELETE, UPDATE AS EXTERNAL NAME [Jfh.Sql.Auditing].[Jfh.Sql.Auditing.AuditTriggers].[AuditTrigger]go[/code]Now when I execute this test script:[code="sql"]insert dbo.Table1( name)values( 'test1'), ('test2') update dbo.Table1set name = replace(name, 'test', reverse('test')) delete dbo.Table1insert dbo.Table2( name)values( 'test1'), ('test2') update dbo.Table2set name = replace(name, 'test', reverse('test')) delete dbo.Table2[/code]The output is:[code="plain"]Trigger fired: column count = 2, Trigger action = Insert.991838031, Null(2 row(s) affected)Trigger fired: column count = 2, Trigger action = Update.991838031, Null(2 row(s) affected)Trigger fired: column count = 2, Trigger action = Delete.991838031, Null(2 row(s) affected)Trigger fired: column count = 2, Trigger action = Insert.991838031, Null(2 row(s) affected)Trigger fired: column count = 2, Trigger action = Update.991838031, Null(2 row(s) affected)Trigger fired: column count = 2, Trigger action = Delete.991838031, Null(2 row(s) affected)[/code]I need a way for the CLR code to know whether it was called for Table1, for Table2 or any other table in the database.The test code above tries to use @@procID for the purpose, but -[url=http://msdn.microsoft.com/en-us/library/ms174408.aspx]as documented here[/url]- @@procid does not return an object_id when called from within an in-process CLR routine. It does return some value but it is the same value when called for either table, and it is not a value that is to be found in sys.objects.Does anyone have more suggestions for a way to determine what table the trigger fired for?Fri, 19 Oct 2012 07:56:15 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1375112-386-1.aspxI'm trying to stream FILESTREAM data from an unsafe SQL CLR assembly.The connection string is "Data Source=LAPTOP2\SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Enlist=False" but when creating a new SqlFileStream (inside an SqlTransaction, of course), I'm getting "The request is not supported" at OpenSqlFilestream.So I decided to try native OpenSqlFilestream directly, but then I'm getting an invalid handle (-1) while GetLastWin32Error returns that same "The request is not supported" (ERROR_NOT_SUPPORTED). Have also tried SqlContext.WindowsIdentity.Impersonate() with no apparent effect.I couldn't find any documentation referencing this restriction. Is it really unsupported? If unsupported, is there a good reason? maybe a workaround?Sat, 20 Oct 2012 10:59:28 GMTneverending 74701http://www.sqlservercentral.com/Forums/Topic1347656-386-1.aspxHi,I am currently in the process of migrating our database server (which is Windows Server 2003 32-Bit, SQL2005) over to a VM environment which will be Windows 2008 R2 64-Bit, SQL 2005 64 Bit.One of the issues I am currently facing is that I am having difficulty with the database integration which has been developed using a CLR Assembly (C#). This assembly when called uses OLE DB to connect to a legacy Visual FoxPro Database (V7), using the VFPOLEDB provider, and updates data in the database. Under testing the new environment, this database integration fails. The OLEDB driver reports the following error any time an attempt is made to update the data : “The 'VFPOLEDB.1' provider is not registered on the local machine. “I did some further investigation and found out that the OLEDB driver is not supported in a 64-bit environment, but could be run in a 32-bit environment running under WOW64. I created a small test application in C# that would write some data using the OLEDB driver, complied it for x86 CPU and ran the test application. The test application ran fine and updated the data in the Visual FoxPro Application.My next step is then to re-compile the CLR Assembly for x86 CPU and import it back into SQL2005. I do this without any issues but when I then try and then do an update I get the following message from SQL Server : Msg 10314, Level 16, State 11, Procedure usp_UpdateFoxPro, Line 45An error occurred in the Microsoft .NET Framework while trying to load assembly id 65542. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'foxprointegration, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b23e91ab0ea90eca' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)System.IO.FileLoadException: at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString)The statement has been terminated.(I also get the same error when creating a small test CLR Assembly compiled for x86 CPU)My question is how can I fix this error, or is it not possible to run 32-bit CLR Assemblies from a 64-Bit SQL Server ?I would really like to get this solved as the whole point of moving to a 64 bit environment is to take advantage of the additional memory we can put on the server. If I have to still run in a 32-bit environment it kind of defeats the point of doing the migration, other than the fact the hardware needs replacing this year!Tue, 21 Aug 2012 03:32:28 GMTchris.crowhursthttp://www.sqlservercentral.com/Forums/Topic1348187-386-1.aspxI have a DLL that uses Microsoft Excel objects and works fine in my local environment. My local desktop has a full version of MS Office 2003 installed. But when I deploy it on Windows 2003 server (it cannot have Office 2003 installed), we are getting the following error.[i]Msg 10301, Level 16, State 1, Line 1Assembly 'DashboardExcelCharts' references assembly 'microsoft.office.interop.excel, version=11.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.[/i]I downloaded the interop library and installed them on the server but still facing challenges. Can anyone suggest any solutions to this issue.Tue, 21 Aug 2012 23:27:50 GMTsanjivinatarajanhttp://www.sqlservercentral.com/Forums/Topic1324000-386-1.aspxHi,I am facing problem with clr dll, I have created one web service and calling that web service through SQL using CLR dll. But when multiple users are accessing the web service through external SP the clr dll is unregistered and it is not working then i re-registered the dll and it is working fine what is the problem. i created the assemblys usinf UNSAFE mode.Please help on this.Whether multiple users can access the clr procedures at a time?Regards,SarathMon, 02 Jul 2012 13:34:19 GMTbslchennaihttp://www.sqlservercentral.com/Forums/Topic715449-386-1.aspxHi All,I have a Table1 containing bit values '0' and '1'.In need to combine this column value to a bit array say 11011 and perform a bitwise OR operation aganist a column in another table2 which stores correspoding bit sequence in varchar data type variable. I can achieve the same through .net code. I,e creating a bit array from the column values of Table1 and store it into a bitarray type variable. And performing the bitwise operation aganist the another table's column.Kindly let me know if there is a need for creating CLR function or is there a way to achieve the same Sql Server.Regards,Tue, 12 May 2009 14:11:52 GMTnaveenreddy.84http://www.sqlservercentral.com/Forums/Topic1318422-386-1.aspxHi,I have a C# dll which refers to an Interop dll inside it. When I'm trying to install my assembly in sql clr, I am getting an error "interop dll was not found in sql catalog". sql error -6503. I tried installing the Interop dll first successfully, then tried installing my C# dll. But no luck. Any pointers?Thanks,SrinivasTue, 19 Jun 2012 20:29:32 GMTsrinivas pallikondahttp://www.sqlservercentral.com/Forums/Topic1318993-386-1.aspxHi, I need to take a stored procedure that currently returns a simple and small record set, and change it to return JSON as an nvarchar(MAX) instead. There seem to be a good articles out there on how, but my knowledge of C# and Visual Studio is miniscule, so I'm missing some basic steps. Can someone help me bridge the gap between these two?http://blog.sqlauthority.com/2008/10/19/sql-server-introduction-to-clr-simple-example-of-clr-stored-procedure/and http://www.sqlservercentral.com/articles/CLR/74160/Or, if it's not much harder, I'd like to see if I can implement the CLR using fastJSON, as that seems to have much better performance.http://www.codeproject.com/Articles/159450/fastJSONI'm using SQL Server 2008 R2 and Visual Studio 2010 btw, thanks in advance!Wed, 20 Jun 2012 15:24:54 GMTRekonnhttp://www.sqlservercentral.com/Forums/Topic1317262-386-1.aspxHi GuysI am attempting to use CLR to circumvent some microsoft 'features'namely, I'm using it to call a UDF remotely :) The purpose of the function is to predict what date a transaction is going to hit our main accounts system based on what date the transaction was performed on, taking into account bank holidays etc so I don't want to have to maintain the bank holiday lists and the programs on 2 separate servers.it works fine, the C# function takes a string as a parameter, uses an SqlConnection to access the remote server and executes a query string that is essetially "SELECT dbo.functionName('" + parameter + "')"It then takes the resulting DateTime value and returns it. There is a try-catch in there that on error returns 1900-01-01 00:00:00 and writes the message from .net to the eventlog.If I call this function from ssms on a one-off it works fine.If I embed this function in an update statement on an 18000 row table (it's going to be slow, I know, I don't care though as it's for a over-night job) I get a mass of errors in the event-log saying :[code="plain"]Event Type: WarningEvent Source: RemoteUDF.dllEvent Category: NoneEvent ID: 0Date: 18/06/2012Time: 12:04:33User: N/AComputer: FINTESTSQLDescription:Error processing GL Date. Error: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.[/code]I cancel the query and then when I try and run it in the ssms as a one-off I get this error:[code="plain"]Event Type: WarningEvent Source: RemoteUDF.dllEvent Category: NoneEvent ID: 0Date: 18/06/2012Time: 12:07:57User: N/AComputer: FINTESTSQLDescription:Error processing GL Date. Error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.[/code]Now, I'm assuming the server with the UDF on it that I'm trying to call is booting me off because I'm throwing too many queries at it in a very short time. I have made sure that the program always closes the connection wether it is successfully executed or not but it still seems to be using up all the connections on the server.What I want to know is, how do I prevent the partner transaction manager from disabling it's support for remote network transactions?Any ideas at all ?? So far I've been through about 10 different errors and fixed them with the help of good old google but this one they're saying things about the MSDTC settings on component management and seeing as I know absolutely nothing about this technology I don't want to message with it based on some googled info.Thanks,BenMon, 18 Jun 2012 05:37:42 GMTBenWardhttp://www.sqlservercentral.com/Forums/Topic1312741-386-1.aspxI am creating a small project to read match regex from sql,.cs fileusing System;using System.Data;using System.Data.Sql;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Collections;using System.Text.RegularExpressions; public partial class RegExBase { [SqlFunction(IsDeterministic = true, IsPrecise = true)] public static bool RegexSHIP(string matchString, string pattern) { Regex r1 = new Regex(pattern.TrimEnd(null)); return Regex.Match(matchString.TrimEnd(null), pattern.TrimEnd(null)).Success; } };Test.sqlselect * from [databasename].[dbo].tablename as P, [databasename].[dbo].tablename as Swhere P.UserID = 'Rep'and S.UserID = P.UserIDand dbo.RegexSHIP('T_SHIP_123456.txt', S.Pattern) = 1I have enable clr, created function but still getting the attach error and help to solve that.ThanksThu, 07 Jun 2012 13:14:04 GMTgissahhttp://www.sqlservercentral.com/Forums/Topic1312115-386-1.aspxFolks, Is it possible to programmatically (using T-SQL) to list all the assemblies on my server (different db's) with the associated version numbers ? The idea is to make sure all the assemblies are at the same version in our system. Currently I can double-click each assembly and get the version number , but it is somewhat tedious.Wed, 06 Jun 2012 12:35:39 GMTmivanov007http://www.sqlservercentral.com/Forums/Topic1309683-386-1.aspxHello All, I have created SQL CLR Function for Timezone conversion and successfully deployed. here is my code, public partial class UserDefinedFunctions{ [Microsoft.SqlServer.Server.SqlFunction] public static SqlDateTime TimezoneConversion(SqlDateTime _date, SqlString _currentTZ, SqlString _convertingTZ) { SqlDateTime _convertedDate = new SqlDateTime(); TimeZoneInfo _currentZone = TimeZoneInfo.FindSystemTimeZoneById(_currentTZ.Value); TimeZoneInfo _convertingZone = TimeZoneInfo.FindSystemTimeZoneById(_convertingTZ.Value); _convertedDate = TimeZoneInfo.ConvertTime(_date.Value, _currentZone, _convertingZone); return _convertedDate; }};the assembly is Dot.net 3.5 Framework and Database is SQL Server 2008, i deployed with SAFE Permission Level. When I tried to do with External or Unsafe Mode, my visual studio 2010 shows the deployment is failed, i couldn't know much about it, because it doesn't any other message, just shows deployment failed. I tried to execute the function, but it throws an error as below, Msg 6522, Level 16, State 2, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "TimezoneConversion": 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: MayLeakOnAbortSystem.Security.HostProtectionException: at UserDefinedFunctions.TimezoneConversion(SqlDateTime _date, SqlString _currentTZ, SqlString _convertingTZ)please some one help me to resolve the issue. Thanks in advance. Raja. Fri, 01 Jun 2012 05:58:40 GMTilayaraja84http://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 GMTgravitysuckshttp://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 GMTGunjan