SQLServerCentral / SQL Server 2005 / CLR Integration and Programming.

How To Over come the problem in Time-out Expried

faijurrahuman17 — Fri, 30 Oct 2009 07:26:12 GMT

Dear Friends; The Problem like I have to Write the sp in Sqlserver 2005 the Sp run around 6min;Because The Lot of tables and various database to fetch the records; I want to use Asp.net front End to Run the Problem; To Throw Error at Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.How Can i Over come the Problem;In Asp.net Configuration To Set sessionTime out in 20min;In other way to run the sp in with in 3 min;How it;s possiblePls help me;Thanks advance;A.Faijurrahuman

OUTPUT CLASS

faijurrahuman17 — Mon, 19 Oct 2009 01:44:55 GMT

DEAR ALL; How can i use the Output class;for Instance I want to Delete that on record these record are shown in Resultgrid;pls help this;Thanks Advance ;A.faijurrahuman MCACoimbatore

Step into Stored Procedure failing with message "Cancelled by User"

Venkat Rao-440631 — Mon, 26 Oct 2009 01:11:23 GMT

Hi Friends,I am trying to debug a stored procedure using "Step into Stored Procedure" in VS 2008. The operation is getting end with the message "Cancelled by User" in Output windo. Could some one take me out from this problem.Thanks in advance for your help.Thanks & Regards,Venkat.

Invoking a SQL Server Webservice

coboljobs2003 — Tue, 20 Oct 2009 08:18:27 GMT

I am new to SQL server 2005 and I am trying to work on a project to invoke an existing webservice from SQL server 2005. (This webservice which is a .WSDL file is already being used by another Java application and this webservice connects to mainframe and brings data back from Mainframe). Can anybody guide me (preferably step by step) how I would go about doing this. Note: We do not have .net environment.

SQL CLR code needed to replace fn_regex_split

Marios Philippopoulos — Sat, 10 Oct 2009 20:54:25 GMT

Hi,I have been looking at this post ([url]http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx[/url]) for an effective SQL-CLR equivalent of the xp-based fn_regex_split.That solution returns a 1-column table where each row contains each split segment in the sequence.For example, parsing string 'a,b,c,d' (delimiter ",") with Adam's functionality returns the following 1-column table:abcdMy problem is that I need to replace fn_regex_split code where the UDF is passed [b]3[/b] arguments: the string to parse, the delimiter and [b]the number of segment in the sequence to be returned[/b].In the above example, specifying "2" for the 3rd parameter would return "b".So I need a scalar-UDF equivalent of Adam's code in the above URL that returns to me a specific segment after the split and not the entire sequence as a column of values.If anyone could help me with this, it would be very much appreciated.

Selecting data from multiple rows into a single row

mspiegel — Mon, 19 Oct 2009 13:54:42 GMT

I have a table called Miscextra that is setup as shown below:ID (primary key)Number (secondary key)TitleThedataThere are multiple rows for each Number in this table; one for each Title to be specific. There are various different Title's in this table which are repeated for certain groupings of Number's. For example, for 10 Numbers, there are different 5 Title's for each number. The titles are as follows:statedobnamephonebankIf I do a select * from miscextra where number = '1234' I'll get 5 rows returned. I'd like to get a single row returned containing all of the data elements for that record. I don't have to have the Title row returned; I'm more concerned about the "Thedata" records.Any help would be greatly appreciated. Thanks!

Dev Studio 2008 with BIDS - missing a C# compiler? Say it ant so?

gking-756277 — Mon, 12 Oct 2009 11:54:39 GMT

I’ve just installed Visual Studio 2008 with SQL Server Business Intelligence Development Studio. I wrote my standard “Hello World” in C#, but as I sip my coffee and click Debug on the pull down menu something horrible happens. Mostly nothing, the button is grayed out and there is no option to compile or run the application. Is it just my imagination or did the development studio install without a C# complier? This has me flummoxed?:ermm:I don't think it's important but this is the code. // Hello1.cspublic class Hello1{ public static void Main() { System.Console.WriteLine("Hello, World!"); }}

CRL Assembly after reboot

Dale Burrell — Sat, 03 Oct 2009 07:44:43 GMT

Silly question, but if I load a CLR assembly and then reboot/restart SQL Server does the assemble remain loaded?Thanks

Memory pressure when calling a C# assembly

danielmanke — Wed, 02 Sep 2009 08:40:27 GMT

I am running an application that calls code in a C# assembly. Over time, the committed CLR memory approaches the reserved CLR memory amount, and the application fails with error:Error: 701 Severity: 17 State: 13There is insufficient system memory to run this query.I am running 32 bit servers, (64 bit servers are not an option currently), SQL Server 2005 SP2One solution is to allocate more memory at server startup to MemToLeave. But this only gives me more time until the same failure happens. Will SQL Server 2005 SP3 help sql server to release committed CLR memory? Or can I force SQL Server to release this committed memory without ?In addition, my understanding of CLR memory is that it should get 256MB reserved by default, but when I start my server, I get only 100MB...What configuration should I change to enable the MEMTOLEAVE to get at least 256 MB of memory? Thanks in advance for any help I can get!

SQL CLR assembly as a remotable object

Nabha — Wed, 30 Sep 2009 12:24:31 GMT

I searched for a while about this and could not get any lead. Please forgive if its already discussed. Thanks. I am creating a dot net remoting application. I have created one SQL CLR stored procedure which is acting as a client (in remoting) and a windows service application as a remoting host.Planning to expose one SQL CLR assembly as a remotable server object. Is it possible please?I mean, when a client calls a method on server(remoting server) it has to be executed in sql server memory. Hence the plan for exposing sql server assembly as a remotable server. Any help is appreciated and please let me know if you need some more information. Thanks.

Cloning table structure, indexes, foreign keys etc. using SMO

emrahakdeniz — Thu, 24 Sep 2009 01:17:56 GMT

Hi,I am trying to add a column to a specified position to an existing table using SMO. I couldn't do this with Columns.Add(Column, index) because this was working only if the table is not created already. I found some forum posts that cloning the table, adding the column, dropping existing table and renaming the newly created table is the way to go. I can iterate through all the objects in the table and create copies of them.But is there any simple way to implement this?Thanks.

Web Service CLR resulting in Timeouts

kramaswamy — Fri, 25 Sep 2009 07:47:38 GMT

Hi all - not sure if this is the proper forum for this post, since it kinda spans several different areas, but this was the closest fit I could find.I have a set of CLR functions, written in C#, which invoke a web service call, and then return a value back based on the result of the query.Recently, this CLR has been timing out on the database.I've tested this to the limit of my knowledge, but could not locate the problem.If I run the web service from my browser, it works - I get the response fine.If I run the CLR straight from Visual Studio, it still works.If I log on to the machine that the server is hosted on, and try the web service, it works.From the server, if I run a very similar CLR, which accesses the same web service, but against a different website, it works.So, my conclusion from all of this, is that it must have something to do with the communication between the server and the website. But I can't really continue any further, to try and figure out exactly what the problem is, and how to address it.Any insight would be appreciated!

Issues with CLR Table Valued Function when used with large amount of data

Bhavesh-1094084 — Wed, 23 Sep 2009 08:01:54 GMT

I'm not sure if this topic is covered before, but surely SQLCLR developer must have came accross the situation I explain here.I created my first CLR TVF for spliting string. Here is my .NET code[code] [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillMethodInt", TableDefinition = "Item INT", DataAccess = DataAccessKind.Read)] public static IEnumerable CLRFN00SplitStringToInt(string SplitString) { List listItems = new List(); if (SplitString != string.Empty) listItems.AddRange(Array.ConvertAll(Regex.Split(SplitString, ","), ConvertStringToInt)); return listItems; } public static void FillMethodInt(object listItem, out int Item) { Item = Convert.ToInt32(listItem); } private static int ConvertStringToInt(string param) { if (param == string.Empty) return 0; return Convert.ToInt32(param); }[/code]I use my CLR function within the EXIST function in the WHERE clause of my query. My query returns about 20011 rows. With my T-SQL string split function used same way returns resultset in around 1 sec. But when I substitute it with CLR TVF for some reason the query takes forever.Can someone advice what impact CLR objects have in this case. Is this a known issue?

Migrating to SQL 2008 from 2005

kent.kester — Tue, 22 Sep 2009 06:10:30 GMT

I'm in the process of upgrading from SQL Server 2005 to 2008. I have a few CLR packages written in VB.NET 2005 that I need to migrate to my SQL 2008 server. The problem is I can't connect to the 2008 server from VS 2005 and when I try to open my VB.NET 2005 solution in Visual Studio 2008 it wants to run the conversion wizard. The conversion wizard runs and reports success. Then VS 2008 refuses to open the solution saying the project file type "vbproj" is not supported by this version of Visual Studio.Has anyone run into this before? If so how do you get around it?

Anyone have a suggestion for a good book on CLR

Bradley Jacques — Fri, 18 Sep 2009 12:36:57 GMT

Hello everyone, I've not worked with the common language runtime much, and I was wondering if anyone has a favorite book / site which they visit.Thanks,Brad

How do I send an XML file to a POX service

Michael Morris-330315 — Mon, 14 Sep 2009 12:45:02 GMT

I've created a simple CLR to save a properly formatted FOR XML result set to a file on my SQL 2005 Server. I now need to send that file to an external hosted Plain Old XML (POX) service and read the results back in order to send an e-mail notification in case of failure.

How can I deploy SQL CLR assembly using SMO?

emrahakdeniz — Thu, 17 Sep 2009 07:36:12 GMT

I am trying to make an sql clr deployer application with smo. I can create assembly, but cannot find anything about functions/aggregates/sp's. Is there any information about functions/aggregates/sp's to create them too or should I know every element in the assembly to create them?

Gotcha with dates

Alan_C — Thu, 10 Sep 2009 05:45:28 GMT

Came across an interesting issue the other day. I wrote a UDF to convert a 4 character code into a date (this is some user data which uses a code to define a period eg. 2804 equates to 1 Apr 2008, don't ask me what happens at the end of this year!). I used this function in a stored procedure.The code worked fine when run from Management Studio, however when I ran the stored procedure from a VB .NET (VS2008) app it appeared at first that the procedure was not running. I eventually found that the reason was that the date being returned by my function was in US format rather than UK.I reworked the routine to use month names instead of numbers when casting to the date (which I realise makes the function more robust anyway in the event of moving to a server with different settings).I was surprised however as my query in Management Studio just provided a 4 char string and the parameters to the command object in VB was a four char string. I would have expected that all execution from there on would be down to the SQL Server.Does anyone know of a reason for this.

Debugger option not available in Visual Studio

Hassie — Mon, 23 Feb 2009 13:24:31 GMT

I have SQL Server 2005 Enterprise Edition on a Windows Server 2003 server. I am trying to debug a stored procedure in Visual Studio 2005 but the option Step Into Stored procedure is not available.It is not a permissions issue as I have tried a couple of logins with different rights could it be my version of Visual Studio - I don't think it is the Pro copy - or is it something else?Very frustrating - the rest of the box is working fine.

CLR Trigger won't fire on a View

sbowell — Thu, 27 Aug 2009 14:49:06 GMT

Hi there,I have a T-SQL view built on several underlying tables. When one of the records in any of the tables gets updated I want to fire a CLR Trigger on the changes. However I only want to have one trigger on the view.In this way I will only need one trigger on the view and if I add or remove tables from the view I won't have to add or remove all the triggers as well.When I have tried to do this I get an error statingThe object 'VW_DSC_AccountValuationData' does not exist or is invalid for this operation. The view definitely exists and the only thing I can imagine is that you can't have a CLR Trigger on a View.Does anyone know if this is the case?Many ThanksStuart

CLR initialization permanently failed

Tim Peters-435423 — Wed, 02 Sep 2009 11:21:15 GMT

Hi All,I'm having a problem with CLR failing on a server and with little CLR development experience I'm not sure where to look next. We have one database on a server hosting many others that is using CLR in a single procedure. When the proc stopped working the other day I found the message "CLR initialization permanently failed" from dm_clr_properties. Most posts I've seen on this indicates memory pressure as the cause of failure, but PerfMon stats show plenty of memory available, average SQL Memory around 10 GB of 64 GB total and spikes of ~48 GB.Anyone have other suggestions on how to troubleshoot? Thanks,Tim

.Net Framework Error - System.ServiceModel

sams.sail — Wed, 09 Sep 2009 04:54:08 GMT

Hi, I have some some CLR methods which invokes WCF methods. I've deployed the required assemblies along with my SQL Project. The assembly got successfully deployed in SQL Server and the user defined functions got created. I'm able to invoke CLR methods which are not invoking WCF, but when WCF invoking UFS's are called. The below mentioned error is raised.A .NET Framework error occurred during execution of user-defined routine or aggregate "SendRequest": System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.System.IO.FileLoadException: at SQL.Utility.ServiceManager.SendRequest(String serviceAddress, Int64 deviceSerialNo, Int64 serialNo, Int64 requestId).[b]Initially the configuration of the DB Server was [/b]1. Windows 2008 Server - 32 bit2. SQL Server 2008 Enterprise Edition - 32 bitI was able to deploy and access all functions including UDF's invoking WCF's without any issue.Now I've formatted the server and configured the same with [b]64 bit[/b]. All the required 64 bit assemblies have been deployed to the Server. The SQL Project which I've created was not able to deploy with X64 build. So I've used "Any CPU" option and it sql project was deployed successfully. I'm able to access simple UDF's generated by my SQL Project, but the above mentioned error is raised on accessing UDF's invoking WCF methods.[b]The assemblies deployed in SQL Server are[/b]Microsoft.Transactions.Bridge SMDiagnostics System.Configuration.Install System.DirectoryServices System.DirectoryServices.Protocols System.Drawing System.Drawing.Design System.EnterpriseServices System.Messaging System.Runtime.Remoting System.Runtime.Serialization.Formatters.Soap System.ServiceProcess System.Web System.Web.RegularExpressions System.Windows.Forms System.IdentityModel System.IdentityModel.Selectors System.Runtime.Serialization System.ServiceModelI've checked the GAC and the assembly version, publickeytoken is the same for assembly in GAC and SQL.Any pointers would be helpful.Thanks in advance,Shyam SS

CLR Memory Issues since switching to SP3

tom-505312 — Tue, 08 Sep 2009 07:43:37 GMT

I have been use CLR intensily for over 2 years without any problems to handle lots of automation using SP1/SP2. However after recenlty switching over to SP3 I need to restart the service ~2 days due to memory issues. It seems as though I now have memory leaks where this was not an issue until we swithed to SP3. background of CLR usage:----------------------------filesystem / ftp / zip / regexweb service calls (salesforce.com,google,mapquest,etc...)xlst transformationsServer setup:-------------32bit W2003 12GB using /PAEinitially setup wo/AWE but have tried w/AWE 6GB max with no luck.I have be using the -g switch and have been in the ranges of 512-2024.Errors in eventvwr:.NET Framework execution was aborted by escalation policy because of out of memory. Any suggestions?

Use DSN in SQLCLR

meelan — Fri, 04 Sep 2009 19:42:22 GMT

I am trying to create a stored procedure using .NET in SQL 2005 (SQLCLR) to refer the customer table in a DSN (local) Database in another server.The following is the error:[quote]Msg 6522, Level 16, State 1, Procedure uspGetCustomerInformation, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "uspGetCustomerInformation": System.Security.SecurityException: Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, 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.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.Odbc.OdbcConnection.PermissionDemand() at System.Data.Odbc.OdbcConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at uspCustomerInfo.StoredProcedures.uspGetCustomerInformation(String sCode).[/quote]I have set the DATABASE TRUSWORTHY level ON and running this query in SA mode while testing.I dunno how to set this permission exactly. If any one could share a piece of working code, i would be much grateful.Thank you

Difference between LINQ and Entity Data Model (EDM)

RAM-1062518 — Tue, 21 Jul 2009 23:22:58 GMT

Dear All,I am working in asp.net and along with Sql server 2008, i have requirement to implement LINQ or Entity Data Model (EDM) in my application. so i need to know what is the defference between LINQ and EDM. And, why not SQL Server?

sql problem

x198yn — Sat, 29 Aug 2009 02:36:44 GMT

Hi,I have a question that maybe it like a scenario....in the network i want to know how i can write a CLR stored procedure(with c#) that can insert a record to the table????...and then after writing this stored procedure what should i do as an administrator that when i insert a record to the table all the clients can see this inserted record real time in the network??FIRST OF ALL THANKS SO MUCH FOR YOUR HELP.I RECENTLY UNDERSTAND THAT I CAN USE MESSAGING OR MSMQ FOR MY QUESTION BUT I DON'T KNOW ABOUT MSMQ AND WHAT SHOULD I DO???IF SOMEONE KNOW PLEASE TELL IT...THANKS IN ADVANCE

Shared Static data in CLR without UNSAFE?

RBarryYoung — Sun, 16 Aug 2009 11:31:53 GMT

Here's a really cool tip that I just heard from Remus Rusanu: Apparently, you can use the [url=http://msdn.microsoft.com/en-us/library/system.runtime.compilerservices.compilergeneratedattribute.aspx]CompilerGenerated [/url]decoration to allow Static Shard data in a CLR with[i]out[/i] requiring the UNSAFE permission set. I suspect that this use is not supported though ... :-)

Question(How to) regarding returning a table using CLR?

Ryan Christopher Surrao-235526 — Thu, 27 Aug 2009 07:57:16 GMT

HiI am new to CLR programing and I have a requirement where in I pass a stored procedure and need to to get the resultset which is a table as the output to the database.I figure out the part on how to run the sp but I dont know how to get the result set in the table form.I would appreciate if someone could let me know or write me a sample code on how to do this in VB.net using CLR sp or function please?ThanksRyan

XMLSerializer - assembly does not allow partially trusted callers

beyonder422 — Tue, 21 Apr 2009 15:34:05 GMT

Farily new to 2005 CLR - I am trying to send an xml message via MSMQ from a stored procedure.I am passing an XmlDocument object to a Message and am receiving errors.First I resolved this [url=http://kbalertz.com/913668/Error-message-common-language-runtime-object-Server-dynamically-generated-serialization.aspx]issue[/url] (I think), only to start seeing this [url=http://www.kodyaz.com/blogs/software_development_blog/archive/2006/10/15/455.aspx]issue[/url] and can't seem to get past the "assembly does not allow ..." issue.This method works via web page, but this has to be a SQL Server 2005 security issue, becuase that is the only place it is failing.I have been looking everywhere and have tried at least 20 different iterations.I created (sgen) the serialized version, my assembly is strong named, the XmlSerializer is strong named, the assembly property AllowPartiallyTrustedCallers is set.My db is set trustworthy onassemblies Permission set: Safe - primary and XmlSerializerWhat else can I do, how else can I gather more info to help me troubleshoot?

Post XML to stored procedure - is this possible?

iain.west — Tue, 25 Aug 2009 15:08:26 GMT

Hello,I have a stored procedure which creates records in two tables when data is entered in one web form, there are around 15 parameters which send the data to the tables. What I been asked to create is to allow an XML file to be posted from the webform directly to the SQL Server then the stored procedure will read the XML and current functionality will populate the tables.What I have found so far it seems the web service abilities in SQL only allow the getting of data not posting, is this correct or I am missing something? The XML message has not be defined as of yet and there is no issue in using the HTTP EndPoints as the SQL Server 2008 will be deployed and operational for at least 10 years.Many thanks in advanced for any pointers.

Deleting an arbitrary filetype without using x_cmdshell?

commhorizons — Mon, 17 Aug 2009 13:15:33 GMT

Using CLR or T-SQL, is there a way of deleting an arbitrary file type in SQL based on date, without using xp_cmdshell? xp_delete_file is very limited in the types of files it will delete (besides being officially unsupported).Thanks

Call excel from CLR Trigger

Manish-468430 — Tue, 18 Aug 2009 01:00:41 GMT

Sql server project in visual studio does not allow to add reference to all assemlies.It allows only sql server registered assemblies.So we need to add Excel related Dlls in to sql server.Following will do the related work.EXEC dbo.sp_changedbowner @loginame = N'sa', @map = falseALTER DATABASE wm58jb1 SET TRUSTWORTHY ON;create assembly [stdole] from'C:\Program Files\Microsoft.NET\Primary Interop Assemblies\stdole.dll'WITH PERMISSION_SET = UNSAFEcreate assembly [Office] from'C:\WINDOWS\assembly\GAC\office\12.0.0.0__71e9bce111e9429c\OFFICE.DLL'WITH PERMISSION_SET = UNSAFEcreate assembly [Vbe] FROM 'C:\WINDOWS\assembly\GAC\Microsoft.Vbe.Interop\12.0.0.0__71e9bce111e9429c\Microsoft.Vbe.Interop.dll'WITH PERMISSION_SET = UNSAFEcreate assembly [Microsoft.Office.Interop.Excel.dll] from 'C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll'WITH PERMISSION_SET = UNSAFEAfter doing this we need to give proper rights to account under which sql server runs. Normally it is networkservice.Give admin rights and try to run your app.Regards,Manish

CLR - Access Denied Error

vinojsasidharan — Thu, 13 Aug 2009 17:46:22 GMT

Hi Friends, We do use CLR functions for encrypting and decrypting sensitive information. The CLR works fine on the Dev and Test environments. When the assemblies along with the CLR functions got moved to the UAT environment, I am getting this error Msg 6522, Level 16, State 2, Line 1A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_COM_DecryptInfo": System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))System.UnauthorizedAccessException: at System.ConfigServer.RunParser(IConfigHandler factory, String fileName) at System.ConfigTreeParser.Parse(String fileName, String configPath, Boolean skipSecurityStuff) at System.Security.Cryptography.CryptoConfig.InitializeConfigInfo()Any idea what could be the possible reason. ThanksVinoj

Problems with using CLR to add message to MSMQ from Clustered SQL Server

rob-1080171 — Tue, 11 Aug 2009 01:52:19 GMT

Hi,We have a clustered SQL Server database from which we need to add a message to a Microsoft Message Queue. I wrote a simple CLR app that gets called from a stored procedure that posts a message to a queue.When we run this app in our development environment (non-clustered), the message gets added to the queue but when we promote it to the clustered environment, the message does not get added and we get the error "Message queue service is not available".The Message queue isn't clustered, just the database. We installed the CLR app on a shared drive on the Cluster so that it would be available to all instances of SQL Server running on the cluster but still no luck.Does anybody have any ideas as to what I need to do to get this to work? Does the CLR app somehow need to be "cluster aware" or anything?Any help would be most appreciated.Thanks.

Unable to start T-SQL Debugging

scottdkohler — Thu, 30 Jul 2009 06:58:34 GMT

I am encountering this error when I attempt to use the VS2005 debugger for CLR code...I did search this forum for my error text with no results, so I figured I woould post this up.Environment:1. "Visual Studio" Workstation (WinXP SP3, VS 2005 8.0.50727.42)2. SQL Server named cmesqltest1 (Win Server 2003 SP1, MS Sql Server 2005 Developer Edition SP1)I tried this on SQL 2K5 Enterprise Edition (64-bit) Service Pack 2 with the same resultsNo firewalls running on either machine1. Firewall disabled on VS Workstation2. Win Firewall not running on SQL Server because ICS Service is not runningTest Function:I have a function called fnInternalSelect(), which is a C# object that returns a single field value from a simple select statement.DataAccess = DataAccessKind.Read. It is deployed to SQL Server Error:(A). [b]Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'cmesqltest1'. Remote calls are not allowed for this process[/b]The Tests...I generate this error when:1. Trying to run test.sql (which calls fnInternalSelect()) in debug mode (F5). It executes properly if I Start without Debugging (Cntl-F5)2. Right click on fnInternalSelect under data connections in Server ExplorerWhen trying to Debug | Attach to Process:Transport = Default, Qualifier=browse to domain\sqlservertest generates:(B). [b]Unable to connect to the Microsoft Visual Studio Remote Debugging Monitor named 'cmesqltest1'. The Visual Studio 2005 Remote Debugger service is unable to connnect to this computer. Please see help for assistance.[/b]I have tried the above tests with msvsmon.exe running and not running on the SQL Server machine.DOes not seem to alter the error message (A) at all.I have two RPC services running on my SQL Server box.Remote Procedure Call (RPC) and Remote Procedure Call (RPC) Locator. Both are running as NT AUTHORITY\NetworkServiceAs per msdn, I have my domain login account registered with SQL Server as a sysadmin via:sp_addsrvrolemember 'domain\myusername', 'sysadmin'Been scouring the internet for any ideas.Not sure if this is a permissions thing. Any thoughts would be appreciated.thanks in advance, Scott

CLR Fail to load

jqd2001 — Mon, 20 Jul 2009 22:57:09 GMT

( I also post the same question to SQLTEAM.com)Hi,I wrote a SQL CLR user function and it has been working fine for a few days. Then last week on thursday we start to see the following error:Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration feature.Why SQL server failed to load CLR ? how should I configure to not starting server every now and them.ThanksJack

What credentials are used when executing a CLR stored procedure?

jds-1115722 — Wed, 29 Jul 2009 12:52:24 GMT

I can't figure out what account to grant access to in order to allow a clr stored procedure to call a webservice configured to use windows authentication. I've tried the accounts running the following services so far...Sql Server serviceSql Agent serviceSSIS service(yes they all are running under domain accounts)Even when i open up the webservice to "Everyone" the clr sproc still gets a http 401 access denied...

SQL 2005 64 bit running 32 bit dll

markwnorsoft — Wed, 22 Jul 2009 14:32:58 GMT

Hi, What I have is a SQL 2005 32 bit running 32 bit dll and we are upgrading to SQL 2005 64 bit. The dll is being call from a system extended stored procedure. When it gets call I get an error "Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ept.dll, or one of the DLLs it references. Reason: 193(%1 is not a valid Win32 application.)." I tried putting the dll into the syswow64 and changing the stored procedure and get the same error. I can't convert the dll to 64 because I do not have the source.ThanksMark

Create GAC Assembly

flash.rsn — Thu, 25 Jun 2009 02:09:11 GMT

Dear AllHow to create an assembly from GAC, does it necessary to define path of assembly?My second question is, how to deal with referenced assembly?Thanks

Opinions needed on a SQL-CLR Book - any good?

Marios Philippopoulos — Tue, 28 Jul 2009 13:10:40 GMT

Has anyone got a hold of this book?[quote]Professional SQL Server 2005 CLR Programming: with Stored Procedures, Functions, Triggers, Aggregates, and Types (Paperback) by Derek Comingore (Author), Douglas Hinson (Author) [/quote]Any good?