.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

CLR stored procedures asssemblies working in sql server 2005 but giving issues over sql server 2008

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hi I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'. I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate". In sql logs showing messages like as -- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\. -- AppDomain 2 (mydatabase.dbo[runtime].1) created.   Please let me know what may be the cause for this type of issues. Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?   Thanks in advance.  

View Complete Post

More Related Resource Links

Issues with CLR Stored Procedures - SQL Server 2008


Dear members,

I really hope that someone will be able to help me.

I'm trying to create an assembly in sql 2008, for a dll i've created in c# (net 3.5 frameword).

The dll is calling a WS method, that will do some calculations.

The issues i'm having is, once I try to create the assembly, it tells me "

Msg 10301, Level 16, State 1, Line 1

Assembly 'XYZ' references assembly 'system.runtime.serialization, version=, culture=neutral, publickeytoken=b77a5c561934e089.', 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(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.


" , which I do understand, and following this message, I am trying to create assembly, for the System.Runtim.Serialization dll.

But, if i try to create the dll, it tells me the following:


Warning: The Microsoft .NET Framework assembly 'system.runtime.serialization, version=, culture=neutral, publickeytoken=b77a5c561934e089, process

Howto use Visual Studo 2008 to maintain SQL Server 2005 tables and stored procedures


I I have an ASP.Net web site I built using Visual Studio 2008.  I also have a SQL 2005 database I use with this web site.  I presently use MS SQL Server MManagement Studio Express to add / modify SQL tables, stored procedures, etc. 

But I want to now maintain my SQL datbase directly from my Visual Studo 2008 where I maintain my ASP,Net web site.  I have created a script from my QL DB that creates all my existing tables and stored procedures.  Can I somehow run this to create the necessary source files within VS 2008?


SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

SQL Server 2005 Stored Procedures

  I had written a stored proc. to get date & time in Std. format  dd/mm/yy hh:mm:ss AM/PM. I wanted that  when no value is given to the stored procedure it should produce current date & time in the above std. format & when some date is given , it should display that date in above std. format. The stored proc. is as follows: set  ANSI_NULLS ON set  QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GETDATETIME] @DATE DATETIME = NULL, @NEWDATE VARCHAR(MAX) OUTPUT AS BEGIN --DECLARE @TEMPDATE DATETIME; --SET @TEMPDATE=NULL; --SET @TEMPDATE=; IF (@DATE IS NULL) BEGIN SET @DATE=GETDATE(); SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); GOTO L1; END SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); -- Declare the return variable here --DECLARE @Result int -- Add the T-SQL statements to compute the return value here --SELECT @Result = @p1 -- Return the result of the function --SET @NEWDATE2=CONVERT(VARCHAR(MAX),@NEWDATE); L1: RETURN CONVERT(VARCHAR(MAX),@N

Folders or folder like equivalent in SQL Server 2008 ? for packages & stored procedures ?

Hi,      Currently running SQL-Server 2000.  We have hundreds of stored procedures and DTS packages. We have so many it's becoming a serious challenge for names and such.  We were wondering if SQL Server 2008 has a way to 'nest' both the equivalent of DTS packages (SSIS) and stored procedures in something akin to 'folders'.  We know we have to upgrade one of these days but a version that would handle these would be much more attractive. Just getting this functionality would be worth whatever effort would be required to upgrade.   Thanks in advance for any help on this.

restore SQL server 2005 database on new SQL server 2008 not working

please any one advice me ,i have Great Plain database on SQL server 2005 when i tried to install new SQL server 2008 and restore my Database the GreatPlain not working well as well the Data Base

2008 Report not working on 2005 report server


Hello, I have been researching this issue and it seems if I have created a report using 2008, I cannot upload it to a 2005 report server.

I just wanted to check and see if I have any other options than to re-create the report using 2005? Would copy/paste of the XML from 2008 to 2005 work?

Thanks! Linda


Linked server from SQL Server 2008 R2 to SQL Server 2005 not working


I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script

 EXEC master.dbo.sp_addlinkedserver
 @server = N'TestLS'
 ,@provstr='Integrated Security=SSPI;';

I verified that the Kerberos is working.

When I try to run a four part query as below it gives me the error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".

If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).



SQL Server 2008 R2 on Windows Server 2008 R2 Memory Usage issues and not giving memory back


Okay so i have a physical server i am trying to replace with a virtual server. Its doing a large load process every night, about 190GB on one disk.

The VM is:

On vSphere 4.1 ESXi

O/S is Windows Server 2008 R2 Enterprise

SQL version is SQL Server 2008 R2 Enterprise.

4 vCpus

Now 20GB of memory

Disks assigned to this virtual are all seperate LUNs except for the O/S which is on a shared LUN which is not busy. So 1 LUN/Datastore per 'disk drive on this SQL virtual. Data/Log/TempDB/Backup are on their own LUNs.

All the recommended exclusions are in place for McAfee on a SQL Server.

ESXi 4.1 Enterprise host is not overcommitted. Has 4 x 6core 2.6Ghz AMD processors. BL685c G6 and 128GB of memory. Almost nothing else is running at the time i am testing this load and host is not overcommited or stressed or anywhere near it.

SQLServer process shows 127,236K Working Set and 130,040K Peak working set and memory Private working set 88,828k and Commit Size of 302,792k.

It seems when i check the memory usage in task manager it doesn't show much. In Windows 2003 X64 in would show the GB being used, like 8GB right in task manager. But i don't see that in Windows 2008 R2 task manager. I downloaded SysInternals and it shows the memory on the Windows 2008 r2 box as 15Gb of AWE memory. How can this be? The case is th

sql server 2005 view rights only on stored procedures



 I would like my developers to be able to view stored prcedures in production but not execute or modify them.  Is there a way to do this?  I tried granting view definition to the schema but I think they can still modify the procedure.  Any ideas?




SSRS 2008 Server Exployer not displaying all stored procedures from the database


When I look in the Server Exployer I don't see any of the recently stored procedures that have been developed on the database.  Is there a setting I need to make to be able to see my new stored procedure?

Compatibility issues between Microsoft Visual Studio 2005 and Windows Server 2008 R2


Dear sirs,

When I was installing SQL Server 2005 64 bit on Windows Server 2008 R2, I received a notice informing the software Microsoft Visual Studio 2005 has known compatibility issues with this version of Windows.

So how can I get around these problems?

I need to use Visual Studio 2005 to create the Integration Services packages.

Also, I need to migrate directories of Integration Services packages from a server with SQL Server 2005 32 bit on Windows Server 2003 32 bit for a server with SQL Server 2005 64 bit on Windows Server 2008 R2. How can I proceed to do this?

Thanks in advance.

Jose Luiz

Not working on Windows Server 2008




I had the CSS friendly adapters working great on our old server (windows server 2003 - 32bit) and now we have moved to a 64 bit environment and now I can not get the CSS friendly adapters to work.

We are using SharePoint 2007.

Is there another work thru for 64bit environment.

I notice windows server 2008 has a directory  C:\Windows\Microsoft.NET\Framework64 would I be using the browser file in this directory???

Please help!

SQL server 2005 2008


i cant install sql server 2005 nor 2008 on my windows 7 os

i read about somthing like adding new account when installing sql 2008 to provision the server or so, but didnt know how,,,

can you plz help me??


Indexing services on window server 2008 is not working with .net

Hi, I am useing Microsoft indexing services in windows 2008 64 bit .I am getting error like "Exception from HRESULT: 0xC0000005" while i am createing  new catlog from .net 2.0.I am using  "ciodm.dll" for adding new catlog. i am using this code for create new catelog.   Dim Admin As New CIODMLib.AdminIndexServer   Admin.AddCatalog(varContractNo, txtCatelog.Text + "\" + varContractNo) Admin.Stop() Admin.Start() AddScope(txtDocFolderPath.Text, txtSite.Text, txtCatelogname.Text)   Please help me anybody have fixed this issue on window server 2008 64 bit.  

Replication between SQL Server 2005 and SQL Server 2008

Hi,    We were using SQL Server Transactional Replication between two SQL Server 2005 servers. Now we are going to upgrade the Publisher server to SQL Server 2008, Distributor Server remains as SQL Server 2005.     Can anyone let me know what is the challenges we need to face when configuring Replication between SQL Server 2005 and SQL Server 2008. Replication Method - Transactional Replication Distributor/Subscriber - SQL Server 2005 Publisher     -  SQL Server 208 Regards, Fazlu.

SQL Server 2005 as Distributor, Subscriber - SQL Server 2008 as Publisher

Hi,           I am creating a Distributor on SQL Server 2005 the server name is Server1 and Server1 is the Subscriber as well, we are using Push Subscription           Publisher is on SQL Server 2008, Server2. When i try to create a Publications, it throws the following error.          Server "Server1" is an Older Version of SQL Server and cannot be the Distributor for Server  'Server2'  Server1 - Distributor, Subscriber  Server2 - Publisher Is there any way to overcome this issue..? Regards, Fazlu.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend