SQLServerCentral / SQL Server 2005 / CLR Integration and Programming.

Complex Functions

roelofsleroux — Fri, 20 Nov 2009 01:51:25 GMT

I have a formula that I want to place in a scalar-function so it can calculate and return a result. Problem is ... in this formula there are some INSERT/UPDATE statements that must be executed in order for the formula to be successful.INSERT/UPDATE are not allowed in scalar-functions.I tried to create a table-valued function that will return a recordset of data that i can then use to INSERT/UPDATE in the procedure that uses the function. But a table-valued function seems to be just a view with parameters.Any ideas on how to solve this?

Isolation Levels

thudi.rama — Thu, 19 Nov 2009 14:11:29 GMT

how to use the isolation levels in sql server ,is there any option to change the type of isolation levels in sql server.

problems involved in migration of databases from 2005 to 2008

thudi.rama — Thu, 19 Nov 2009 14:09:13 GMT

I need how to do Migration of databases from sql server 2005 to 2008,What kind of problems involved in this process.

This SqlTransaction has completed; it is no longer usable.

spencer-682903 — Tue, 17 Nov 2009 22:54:40 GMT

HiI am getting the following error trying to commit transaction - [b]This SqlTransaction has completed; it is no longer usable[/b]. The transaction involves saving a quite large object and its associated entities. The entire transaction involves multiple select and insert statements as well potentially a couple of updates. The code runs fine up until the point where the transaction is commited at which point the following exception is thrownSystem.Data.SqlClient.SqlException was caught Message="The transaction operation cannot be performed because there are pending requests working on this transaction." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060The transaction object involved contains the following'dbTrans.transaction.IsolationLevel' threw an exception of type 'System.InvalidOperationException'This SqlTransaction has completed; it is no longer usable.At the point prior to the commit statement, the connection is open and the transaction appears to be valid. I've gone through my code and I'm certain there is only one connection and one transaction object is created during the process. Running the exact same code not wrapped in a transaction works fine, so this is probably not a problem with the database. I've also run DBCC CHECKDB command to verify this. SQL server profiler and logs also do not appear to yield any clues. We are running SQL Server 9.0.3175 if that helps at all.I'm no DBA, just a mere developer with only enough database knowledge to get by, so I hoping one of you serious SQL Server guys can help me out with some pointers on how to track this problem down! Thanks in advanceSpencer

using 2005 Custom Data Extensions in BIDS 2008

dharmen.somagani — Wed, 18 Nov 2009 10:00:46 GMT

hi All,we are using CDE assembly which is compiled in VS2005 for SSRS 2005 every thing is hunky dory. Recently we moved to VS 2008 and SSRS 2008 I have compiled the assembly using VS 2008 to make sure it is referencing 2008 libraries followed by configuring the SSRS config files like adding <Extension> tag and <Codegroup> in policy files, but when I run the report it is throwing the errors like - "assembly 'Microsoft.ReportingServices.Diagnostics, Version=9.0.242.0, ....' or one of its dependencies. The located assembly's manifest definition doesn't match the assembly reference. "- "An attempt has been made to use a data extension 'Myextention' that is either not registered for this report server or is not supported in this edition of Reporting Services.I'm breaking my head to figure out what the problem is . I'm totally stuck with this problem . What could be the problem ?Can some one shed some light on my problem and drive me in correct direction ? Any help is appreciated. thanks in advanceDharmen.

Imports System.Drawing SSIS script task does not work

ava1over — Tue, 17 Nov 2009 16:05:03 GMT

I am trying to parse a number of image files in a sub directory to determine their height and width using an SSIS script task. Based on the picture dimensions, I was going to sort large (> 600x600) pictures into one folder and small (<600x600) pictures into another. To do so, I was going to write a simple script similar to the script below. When I go to import System.Drawing, I type Imports System. ... and expect to see Drawing there through Intellisense. Unfortunately that option is not available. My guess is that I need to somehow reference this .Net framework piece; but past that I am pretty lost. Any help would be GREATLY appreciated.Thanks! Dim UploadedImage As Bitmap = New Bitmap("C:\DATA\IMAGES\figure2.jpg") Dim UploadedImageWidth As Single = UploadedImage.PhysicalDimension.Width Dim UploadedImageHeight As Single = UploadedImage.PhysicalDimension.Height

CLR function to translate from English to other languages

yosiasz — Mon, 16 Nov 2009 10:26:10 GMT

Hi there,How would you use the following function in C# to create a CLR function in TSQL to use as a translator function?http://dnknormark.net/post/Translate-text-in-C-using-Google-Translate.aspxThank you!

Units of cost column in sys.dm_clr_appdomains?

Marios Philippopoulos — Mon, 16 Nov 2009 15:30:12 GMT

What are the units of the cost column in DMV sys.dm_clr_appdomains?bytes, kb, mb?BOL is silent on this:[url]http://msdn.microsoft.com/en-us/library/ms187720.aspx[/url]I'm building a query that will help me monitor the cost of each appdomain, but, without units, it's not of much use.I'm guessing the units are in KB, but I would like a confirmation from someone.Here is the query:[code="sql"]--http://www.sqlskills.com/resources/whitepapers/sql%20server%20dba%20guide%20to%20sqlclr.htm#_Toc110615518--http://msdn.microsoft.com/en-us/library/ms187720.aspx--Currently loaded appdomains (run in each database)SELECT ad.[appdomain_name], ad.cost, ad.value, ad.appdomain_id, ad.creation_time AS AppdomainCreationTime, ad.[state] AS AppdomainState, DB_NAME(ad.[db_id]) AS DBName , sa.[name] AS AssemblyName, clr.[load_time] AS AssemblyLoadTimeFROM sys.dm_clr_appdomains AS adINNER JOIN sys.dm_clr_loaded_assemblies AS clrON clr.appdomain_address = ad.appdomain_addressINNER JOIN sys.assemblies AS saON clr.assembly_id = sa.assembly_id;[/code]

problem with dll in clr project

marox — Thu, 12 Nov 2009 01:44:12 GMT

I have problem with clr project.for example:i have external MyDLL.dll with function int add (int i, int j)in CLR project I have MyCLR.dll with function int clr_add (int i, int j)Problem:Can i use in clr_add external dll (MyDLL.dll)?in MyCLR.dll I have:......[Microsoft::SqlServer::Server::SqlProcedure] static int clr_add(int i, int j) { typedef int (*CALLBACK LPFNDLLFUNC1)(int,int); HINSTANCE hDLL = NULL; // Handle to DLL LPFNDLLFUNC1 lpfnDllFunc1; // Function pointer hDLL = LoadLibrary(L"MyDLL.dll"); lpfnDllFunc1 = (LPFNDLLFUNC1)GetProcAddress(hDLL,"add"); int i = lpfnDllFunc1(4,6); return i; }--------------------------------------------------------------------in MS SQL :CREATE ASSEMBLY assembly_addFROM 'c:\_clr\MyCLR.dll'WITH PERMISSION_SET = UNSAFEgoCREATE PROCEDURE MyStoredProc @i int, @j intAS EXTERNAL NAME assembly_add.[add].clr_adddeclare @w intexec @w = MyStoredProc 6,2print @wError:Msg 6522, Level 16, State 1, Procedure MyStoredProc, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "MyStoredProc": .in my CLR project Common Language Runtime Support = /clr: pure,my question: how can i in CLR SQL project use external .dll??

are CLR Functions at the server/instance level or db specific?

SQL Iron Chef — Thu, 12 Nov 2009 15:05:31 GMT

thanks

Getting non-sensical values of free MemToLeave Space in Virtual SQL-2008 environment

Marios Philippopoulos — Thu, 12 Nov 2009 09:09:50 GMT

I use the query below to determine the amount of fragmentation and free space I have in the MemToLeave area of memory in my SQL-2008 virtual-machine environment.I'm getting non-sensical results.RAM: 7.75 GBMax buffer memory: 4.5 GB1 SQL-2008 instance (64-bit Enterprise Edition).Here is the query[code="sql"]-- from http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/-- MemToLeave values for instance of SQL Server:-- [Total avail mem, KB] : total memory assigned to MemToLeave-- [Max free size, KB] : free memory available to MemToLeave;WITH VAS_Summary AS ( SELECT [Size] = VAS_Dump.Size, Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size], region_allocation_base_address [Base] FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size], region_allocation_base_address [Base] FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump GROUP BY [Size])--Get size of all free mem. areas--http://www.sqlservercentral.com/Forums/Topic624152-386-2.aspx?Update=1--SELECT CAST(Size AS BIGINT)/1024 AS [Free size, KB] FROM VAS_Summary WHERE Free <> 0;[/code]Here are the top 15 values (in ascending order); the top 2 values are in the terabytes range!Free size, KB------------801609110897756999681399641468682569002569283455003517806116922072960209585618637004606704040256

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?