.NET Framework inside SQL Server Forum

SQLCLR and Transactions

Fri, 27 Nov 2009 03:32:48 Z

I am pretty new to SQLCLR, in fact i didn't started yet :) i want to make a stored procedure that works as interact with the db: modify data in the database. Is this modification part of the transaction where the stored pricedure is executing ? what i mean if say some error happens when executing the stored procedure, can i roll back the db update from outside the clr code ? I know that i might look very noobish and i am, it is just that i am trying to check if i should go the SQLCLR way or not.

Thanks

What should i put here?!

Microsoft.SqlServer.SqlClrProvider...dll ver 10.xx

Thu, 14 Aug 2008 07:46:59 Z

I am developing on a machine (XP Prof SP2 32bit) with sql server 2008, VS 2008 and .NEt v3.0 SP1

I have no problems on my machine but when testing on other machines I get error:

And I did include Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.Management.Collector.dll so, I am really confused and I can't seem to find file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0.
                      Any help would be much apreciated.

************** Exception Text **************
Microsoft.SqlServer.Management.Smo.FailedOperationException ---> Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.SqlClrProvider, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
   at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectLoadInfoManager.LoadHierarchy()
   at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectLoadInfoManager.GetFirstObjectLoadInfo(Urn urn)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetServerVersion(Urn urn, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectInfo(Object ci, RequestObjectInfo req)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetObjectInfo(Object connectionInfo, RequestObjectInfo requestObjectInfo)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.FixPropertyList(Object connectionInfo, Urn urn, String[] fields, RequestFieldsTypes requestFieldsType)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Urn urn)
   at Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(Boolean localOnly)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(Boolean localOnly)
   at ABSystemInfo.Form1.button1_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
AB_System_info
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Ab%20system%20info/AB_System_info.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Management
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
Microsoft.SqlServer.Smo
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.1442.32 ((SQL_PreRelease).080529-2343 )
    CodeBase: file:///C:/Ab%20system%20info/Microsoft.SqlServer.Smo.DLL
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.42 (RTM.050727-4200)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
Microsoft.SqlServer.Management.Sdk.Sfc
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.1442.32 ((SQL_PreRelease).080529-2343 )
    CodeBase: file:///C:/Ab%20system%20info/Microsoft.SqlServer.Management.Sdk.Sfc.DLL
----------------------------------------
Microsoft.SqlServer.ConnectionInfo
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.1442.32 ((SQL_PreRelease).080529-2343 )
    CodeBase: file:///C:/Ab%20system%20info/Microsoft.SqlServer.ConnectionInfo.DLL
----------------------------------------

SQLFacet Return Precision and Scale in VB.NET 2005 fx2.0

Sun, 29 Nov 2009 19:54:16 Z

Hello, I've seem some examples on setting the SQLFacetAttribute on return values of SQLFunctions in C#. I can not make it work in VB.NET. The compiler always notes that this attribute can not be used on this declaration type. Does VB.Net 2005 lack this feature?

.NET Framework execution was aborted by escalation policy because of out of memory.

Wed, 25 Nov 2009 21:28:42 Z

I've a similar issue with my SQL box. It's SQL 2005 ent edition. 9.0.4035. It's x64 with 32gb RAM.

I'm getting the error '.NET Framework execution was aborted by escalation policy because of out of memory.'

I was running an update statement at this time which was calling a function which was using clr.

I've also run your query above and below are the results.

Total avail mem, KB Max free size, KB

8532256848 6661828544

On my server, when I ran the memory consumption report, I see the following big numbers:

Component Type Allocated Mem Virt Mem Reserved Virt Mem Committed AWE mem Allocated

MemoryClerk_SQLCLR 21,520 23,344,384 16,977,220 0

MemClerk,SQLBUFFERPOOL 1,296 33,603,584 32,768 28,785,920

Right now, the server is running fine but the clr is not going to run anymore. I've to restart the server to get rid of the error. the assembly is created as SAFE.

I've also run DBCC FREESYSTEMCACHE('ALL') and the above numbers stayed pretty much the same. SQLCLR virt mem reservation went down by about 300mb and committed mem went down by about 100mb.

Please let me know if something else comes to your mind that we can try.

Thank you.

My First Rodeo in the .Net CLR, not going so well

Sun, 29 Nov 2009 22:07:18 Z

I'm trying to create a trigger that fires after a row is inserted in my Log4Net table. Specifically, I store some JSON in the 4000 character (VARCHAR) field of log4net, and I want to parse that (using NewtonSoft) and insert an companion record in a second table with the results of the parse. I'm not too far down the path yet and am getting a funny error.

I'm pasting my simple code, and the error I'm getting is: "INSERT INTO dbo.Lo4gNetDataMart(Log4NetId,MessageLine1) VALUES(@Id,'YeeHa!')"

using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    [SqlTrigger(Name = "Log4NetParse", Target = "Log4Net", Event = "FOR INSERT")]
    public static void Log4NetParse()
    {
        var triggContext = SqlContext.TriggerContext;
        var log4NetId = new SqlParameter("@Id", System.Data.SqlDbType.Int);

        if (triggContext.TriggerAction == TriggerAction.Insert)
        {
            using (var conn = new SqlConnection("context connection=true"))
            {
                conn.Open();
                var sqlComm = new SqlCommand();
                var sqlP = SqlContext.Pipe;

                sqlComm.Connection = conn;
                sqlComm.CommandText = "SELECT Id from INSERTED";

                log4NetId.Value = sqlComm.ExecuteScalar().ToString();

                sqlComm.CommandText = string.Format(
                    "INSERT INTO dbo.Log4NetDataMart(Log4NetId,MessageLine1) VALUES({0},'{1}')", 
                    log4NetId,"YeeHa!");
                sqlP.Send(sqlComm.CommandText);
                sqlP.ExecuteAndSend(sqlComm);
            }
        }
    }
}

and my table DDDL's.

CREATE TABLE [dbo].[Log4Net] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Date] datetime NOT NULL,
  [Thread] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Level] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Logger] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Message] varchar(4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [Exception] varchar(2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [Log4Net_pk] PRIMARY KEY CLUSTERED ([Id])
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Log4Net_idx] ON [dbo].[Log4Net]
  ([Date])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[Log4NetParse] ON [dbo].[Log4Net]
WITH EXECUTE AS CALLER
FOR INSERT
AS
EXTERNAL NAME [ThreePLogicAccess.SqlServerCLR].[Triggers].[Log4NetParse]
GO

CREATE TABLE [dbo].[Log4NetDataMart] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Log4NetId] int NOT NULL,
  [LoadId] int NULL,
  [ShippingOrderId] int NULL,
  [CargoId] int NULL,
  [GlobalSolutionId] int NULL,
  [ColemanProNumber] nvarchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [ColemanPTCN] nvarchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [ColemanFtpStagingId] int NULL,
  [CargoSetId] int NULL,
  [CompanyId] int NULL,
  [UsersId] int NULL,
  [UserName] varchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [FileNameProcessed] varchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [EllapsedTime] int NULL,
  [MessageLine1] varchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [MessageLine2] varchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [Log4NetDataMart_PK] PRIMARY KEY CLUSTERED ([Id]),
  CONSTRAINT [Log4NetDataMart_fk] FOREIGN KEY ([Log4NetId]) 
  REFERENCES [dbo].[Log4Net] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
)
ON [PRIMARY]
GO

Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider

Doubt on Stored Procedure

Sun, 29 Nov 2009 09:07:08 Z

ALTER PROCEDURE sp_GetBoothNo

(@BoothSize varchar(max))

begin

declare @Sum int

select @Sum=sum(NoOfBooth) From TblBooth where BoothSize=@BoothSize

if(@Sum == null)

begin

set @Sum = 0

end

select @Sum

end

RETURN

how can i compare using if statement like this

CLR UDA debug

Fri, 27 Nov 2009 15:42:42 Z

I found on Check here this code for skewness and kurtosis:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
[Serializable]
[SqlUserDefinedAggregate(
   Format.Native,                  
   IsInvariantToDuplicates = false, 
   IsInvariantToNulls = true,       
   IsInvariantToOrder = true,     
   IsNullIfEmpty = false)]            
public struct Skew
{
       private double rx;         // running sum of current values (x)
       private double rx2;        // running sum of squared current values (x^2)
       private double r2x;        // running sum of doubled current values (2x)
       private double rx3;        // running sum of current values raised to power 3 (x^3)
       private double r3x2;       // running sum of tripled squared current values (3x^2)
       private double r3x;        // running sum of tripled current values (3x)
       private Int64 rn;          // running count of rows
 
       public void Init()
       {
              rx = 0;
              rx2 = 0;
              r2x = 0;
              rx3 = 0;
              r3x2 = 0;
              r3x = 0;
              rn = 0;
       }
 
       public void Accumulate(SqlDouble inpVal)
       {
              if (inpVal.IsNull)
              {
                     return;
              }
              rx = rx + inpVal.Value;
              rx2 = rx2 + Math.Pow(inpVal.Value, 2);
              r2x = r2x + 2 * inpVal.Value;
              rx3 = rx3 + Math.Pow(inpVal.Value, 3);
              r3x2 = r3x2 + 3 * Math.Pow(inpVal.Value, 2);
              r3x = r3x + 3 * inpVal.Value;
              rn = rn + 1;
       }
 
       public void Merge(Skew Group)
       {
              this.rx = this.rx + Group.rx;
              this.rx2 = this.rx2 + Group.rx2;
              this.r2x = this.r2x + Group.r2x;
              this.rx3 = this.rx3 + Group.rx3;
              this.r3x2 = this.r3x2 + Group.r3x2;
              this.r3x = this.r3x + Group.r3x;
              this.rn = this.rn + Group.rn;
       }
 
       public SqlDouble Terminate()
       {
              double myAvg = (rx / rn);
              double myStDev = Math.Pow((rx2 - r2x * myAvg + rn * Math.Pow(myAvg, 2)) / (rn - 1), 1d / 2d);
              double mySkew = (rx3 - r3x2 * myAvg + r3x * Math.Pow(myAvg, 2) - rn * Math.Pow(myAvg, 3)) /
                                         Math.Pow(myStDev,3) * rn / (rn - 1) / (rn - 2);
              return (SqlDouble)mySkew;
       }
 
}

[Serializable]
[SqlUserDefinedAggregate(
   Format.Native,
   IsInvariantToDuplicates = false,
   IsInvariantToNulls = true,
   IsInvariantToOrder = true,
   IsNullIfEmpty = false)]
public struct Kurt
{
       private double rx;         // running sum of current values (x)
       private double rx2;        // running sum of squared current values (x^2)
       private double r2x;        // running sum of doubled current values (2x)
       private double rx4;        // running sum of current values raised to power 4 (x^4)
       private double r4x3;       // running sum of quadrupled current values raised to power 3 (4x^3)
       private double r6x2;       // running sum of squared current values multiplied by 6 (6x^2)
       private double r4x;        // running sum of quadrupled current values (4x)
       private Int64 rn;          // running count of rows
 
       public void Init()
       {
              rx = 0;
              rx2 = 0;
              r2x = 0;
              rx4 = 0;
              r4x3 = 0;
              r6x2 = 0;
              r4x = 0;
              rn = 0;
       }
 
       public void Accumulate(SqlDouble inpVal)
       {
              if (inpVal.IsNull)
              {
                     return;
              }
              rx = rx + inpVal.Value;
              rx2 = rx2 + Math.Pow(inpVal.Value, 2);
              r2x = r2x + 2 * inpVal.Value;
              rx4 = rx4 + Math.Pow(inpVal.Value, 4);
              r4x3 = r4x3 + 4 * Math.Pow(inpVal.Value, 3);
              r6x2 = r6x2 + 6 * Math.Pow(inpVal.Value, 2);
              r4x = r4x + 4 * inpVal.Value;
              rn = rn + 1;
       }
 
       public void Merge(Kurt Group)
       {
              this.rx = this.rx + Group.rx;
              this.rx2 = this.rx2 + Group.rx2;
              this.r2x = this.r2x + Group.r2x;
              this.rx4 = this.rx4 + Group.rx4;
              this.r4x3 = this.r4x3 + Group.r4x3;
              this.r6x2 = this.r6x2 + Group.r6x2;
              this.r4x = this.r4x + Group.r4x;
              this.rn = this.rn + Group.rn;
       }
 
       public SqlDouble Terminate()
       {
              double myAvg = (rx / rn);
              double myStDev = Math.Pow((rx2 - r2x * myAvg + rn * Math.Pow(myAvg, 2)) / (rn - 1), 1d / 2d);
              double myKurt = (rx4 - r4x3 * myAvg + r6x2 * Math.Pow(myAvg, 2) - r4x * Math.Pow(myAvg, 3) + rn * Math.Pow(myAvg, 4)) /
                                         Math.Pow(myStDev, 4) * rn * (rn + 1) / (rn - 1) / (rn - 2) / (rn - 3) -
                                         3 * Math.Pow((rn - 1), 2) / (rn - 2) / (rn - 3);
              return (SqlDouble)myKurt;
       }
 
}

I compiled it to a .dll file with MS Visual C# 2008 Express Edition.

Then i added the CLR UDA with the following code:

-- Skewness and Kurtosis with UDAs
USE AdventureWorksDW;
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
-- Load CS Assembly
CREATE ASSEMBLY DescriptiveStatistics
FROM 'yourpath\yourdllname.dll'
WITH PERMISSION_SET = SAFE;
GO
-- Create the aggregates
CREATE AGGREGATE dbo.Skew(@s float)
RETURNS float
EXTERNAL NAME DescriptiveStatistics.Skew;
GO
CREATE AGGREGATE dbo.Kurt(@s float)
RETURNS float
EXTERNAL NAME DescriptiveStatistics.Kurt;
GO

I can use the aggregation skew and kurt freely but i have found two issues:

[FIRST ISSUE]

SELECT dbo.Skew(Field) AS Skew, dbo.Kurt(Field) AS Kurt
FROM     MyTable
WHERE (Cond1 = 378) AND (Cond2 = 2008)

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "Skew":
System.OverflowException: Arithmetic Overflow.
System.OverflowException:
   at System.Data.SqlTypes.SqlDouble..ctor(Double value)
   at System.Data.SqlTypes.SqlDouble.op_Implicit(Double x)
   at Skew.Terminate().

where

SELECT * FROM MyTable
WHERE (Cond1 = 378) AND (Cond2 = 2008)

returns no rows (empty result)

I'm using SSMS 2008.
I compiled the code as a .dll with MS Visual C# Express Edition.

[SECOND ISSUE]
Kurtosis on a set of values returns: 1585.68

The kurtosis function with matlab returns instead: 1588.69
Without sample bias correction: 1588.65

Can it be an error in the CLR formula for kurtosis?

If somebody can help me to solve this issues (since i don't have C# programming skills) i'm sure the Whole MS SQL community will be thankful.

THANKS IN ADVANCE

Oleg Komarov

Create Assembly from .NET dll

Wed, 25 Nov 2009 21:17:54 Z

I am using a the SharpSvn dll open source. I want to deploy it through a CLR function, but I first need to manually create it in sql server on the database so I can reference the dll in my CLR project. I have searched around intensively and could not find a solution to work. This is what I am attempting:

USE MyDb

ALTER DATABASE MyDb SET TRUSTWORTHY ON

CREATE ASSEMBLY SharpSvn

FROM 'C:\SharpSvn\SharpSvn.dll'

WITH PERMISSION_SET = UNSAFE

This is the error that occurs:

Msg 6218, Level 16, State 3, Line 2

CREATE ASSEMBLY for assembly 'SharpSvn' failed because assembly 'SharpSvn' 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

Any tips would be greatly appreciated. Thanks

how to send message to external message queue in a trigger of sql server ?

Sun, 22 Nov 2009 03:36:49 Z

i write a code to send message to windows message queue,but when i try to add it to clr but failed for need to reference other dll like system.messaging.dll , and when i try to add this dll first, but it need other dll ,too. so i tired to add these dll, and want to know how to
send message to message queue, maybe i'm wrong , maybe there's better way. so ,i hope someone can help me to solve this.
code :
public class MSGQSpiderTrigger
    {
        public void ExportToMSG()
        {
            SqlTriggerContext sqlTrigger = SqlContext.TriggerContext;
            SqlDataReader reader;
            SqlCommand command;
            MSGDocInfo doc = new MSGDocInfo();
            if (sqlTrigger.TriggerAction == TriggerAction.Insert)
            {

                using (SqlConnection connection = new SqlConnection(@"context connection=true"))
                {
                    connection.Open();
                    command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
                    reader = command.ExecuteReader();
                    reader.Read();
                   //.... read my data

                    reader.Close();
                }

                  //then send to windows message queue.
              MessageQueueManager.SendMsg(MessageQueueManager.GetMsgQueue(), doc);
            }
        }
    }

help with this AppDomain event

Tue, 27 May 2008 14:20:34 Z

Hi,

I've a company that use SQL CLR from IIS that connect to the SQL and insert data to tables.
every few days the server stop responding and i see those error in the sql event viewer
Unsafe assembly 'hebfilts, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 9 (system.dbo[runtime].8).

AppDomain 9 (system.dbo[runtime].8) unloaded.
AppDomain 9 (system.dbo[runtime].8) is marked for unload due to memory pressure.

the problem is fixed only when i restart the sql server service.
now i use SQl Server 2005 SP2 build 9.0.3042
I've found an article that talks about this problem but they say that it fix in build 2153 http://support.microsoft.com/kb/917271
and i'v 3042.
does my build include this fix?(i installed sp2 after RTM)
if so how can i fix this error that i have?

THX

How to avoid network problems while calling a webservice in a SQL CLR trigger?

Thu, 19 Nov 2009 14:39:07 Z

Hi All,

My Goal: DB synchronization between SQL server 2005 and Mysql database via web services.

I have created a SQLCLR trigger, in which i'm calling a web service to sync/update remote (MYSQL) DB over a specific constraints. After getting the acknowledgment from web service i'm updating the sync flag to success (In SQL SERVER2005). If any network delay happens i am unable to know whether sync has done successfully or not.

How to avoid network dependency here?

Is there any reliable queuing mechanism available in sql server 2005 to eliminate dependency over network?

I am looking any suitable service/approach in sql server 2005 that can take care of calling web services as asynchronously and update the status of sync flag irrespective of network.

Thanks in advance if anyone provide the good approach as step by step in detail.

encrypt password and salt in sql server 2008 using an external key from web.config

Thu, 19 Nov 2009 18:03:19 Z

I'm using a sql membership provider which saves the password and passwordsalt in the database ok using the machine key define in my web.config. I would like to encrypt the provided clear text password from sql server to see if I get the same stored password.
  <machineKey validationKey="E94B2016343177A8D5EC48B1B71B9B77C1B5CC5DFDA0B619541CC85F4AAA40F79B09B22481279DB6CFEF4895D525F41587F517269D50127E83167614D18A02AC" decryptionKey="E9B358945877F8262649EC7FC7B9ECB190BDCAEADEA0B84F11A51F8FAB8EB889" validation="SHA1" decryption="AES"/>

Encrypted password:   LGemmlj7of9bCTsayS8xA6yaokkEEZuyArvDEisBYbUAjiR5rm4nRpoaEJ13l+Kc

password salt:     15xCCPpCp7mB3AsDKrz0Z/w=

original clear text password: bakishi1

How do you write a query in sql server 2008 using the clear text password,the machine key, and salt password to come up with the same encrypted password.

Getting the name of the updated table

Tue, 07 Mar 2006 15:04:53 Z

I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

CLR Function and Persisted Computed Columns

Wed, 18 Nov 2009 20:47:02 Z

Hi,

I have the following C# method:

[SqlFunction(IsDeterministic = true)]
public static SqlInt16 GetNumber(SqlString id)
{
return new SqlInt16(short.Parse(Regex.Match(id.Value, "^[0-9]{2,3}").Value));
}

I import it on my DB. It works well.
But even if I used the property attribute IsDeterministic = true in my C# code, I can't do this:

ALTER TABLE Cars
ADD Number AS dbo.GetNumber(Id) Persisted

I still have an error: Computed column 'Number' in table 'Cars' cannot be persisted because the column is non-deterministic.

So what can I do to have a Persisted Computed Column which uses a CLR functions?
(I use SQL Server 2008 SP1).

Thanks

Matthieu

clr sql in vs c++

Tue, 10 Nov 2009 14:06:27 Z

Hello, i have a problem

i must use external dll in clr dll file, for example
myDLL.dll contain function int add (int, int)

in CLR project (myCLR.dll) i have function
int clr_add (int, int){

}

Problem: in clr_add i must use function from myDLL.dll
is it possible? how can i registry in myCLR.clr_add function form myDLL.dll?

Problems with Accumulate method when attempting to create a CLR Aggregation in SQL Server 2005

Thu, 12 Nov 2009 22:32:45 Z

I am getting an error with the Accumulate method of my my aggreagation class. Any help to solve this problem would be greatly appreciated.

Here is some information:

(1) I created an assembly in MS Visual Studio 2008 containing a user defined aggregate function.

(2) I deployed the assembly to SQL 2005 from SSMS.

(3) When I attempt to create the aggregate function I have the following error message:

Msg 6558, Level 16, State 1, Line 1

CREATE AGGREGATE failed because type 'CountryLocation' does not conform to UDAGG specification due to method 'Accumulate'.

Msg 6597, Level 16, State 2, Line 1

CREATE AGGREGATE failed.

(4) My CREATE AGGREGATE statement is :

CREATE AGGREGATE CountryLocation(@Value varchar(20))

RETURNS varchar(10)

EXTERNAL NAME MySqlUdf.CountryLocation;

(5) The source code for my aggregate function is below. It is a simple code that I am using to learn about user defined aggregation. In essence, it reads a country record with the possible values Russia, Japan, Australia and New Zealand. The aggregation is supposed to indicated if the country is located on the North or South hemisphere. For example, if the countries read are Russia and Japan then the aggregate value is 'North'.

Here's the code:

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

[Serializable]

[SqlUserDefinedAggregate(

Format.UserDefined, //use clr serialization to serialize the intermediate result

IsInvariantToNulls = true, //optimizer property

IsInvariantToDuplicates = false, //optimizer property

IsInvariantToOrder = false, //optimizer property

MaxByteSize = 8000) //maximum size in bytes of persisted value

]

public class CountryLocation : IBinarySerialize

{

private SqlString _location;

public void Init()

{

_location = "Unknown";

}

public void Accumulate(SqlString value)

{

if (value.IsNull || value == "") return;

if (_location == "Mixed") return;

switch (value.Value)

{

case "Russia":

case "Japan":

if (_location == "North" || _location == "Unknown")

_location = "North";

else

_location = "Mixed";

break;

case "Australia":

case "New Zealand":

if (_location == "South" || _location == "Unknown")

_location = "South";

else

_location = "Mixed";

break;

}

public void Merge(CountryLocation group)

{

_location = group._location;

}

public SqlString Terminate()

{

return _location;

}

public void Read(BinaryReader r)

{

_location = r.ReadString();

}

public void Write(BinaryWriter w)

{

w.Write(_location.ToString());

}

Global Scope for Database CLR Variables

Fri, 13 Nov 2009 01:35:52 Z

I would like to know if we can store variables (from CLR Functions) on global scope; something which is comparable to Application Scope in APS.Net which lets us access variables across processes and calls. I am planning to keep a dictionary object on the global scope. Need to know if this is possible.

Regards- Muralidhar | If my response helped, please help me by marking my response as the answer and voting as appropriate...

Extract a CLR Assembly bit stream into a .DLL file

Tue, 10 Nov 2009 17:38:46 Z

I have taken over a SQL Server 2005 project that uses the SQL Assemblies to invoke a web service. Unfortunately the assembly needs amending but the original Visual Studio project is not available. Is there any way of extracting the CLR Assembly bit stream from sql server into a .dll file so that I can then view the dll using a tool like Reflector.

Andy.

CREATE AGGREGATE failed because type .. does not conform to UDAGG specification due to method 'Init'

Tue, 03 Jul 2007 19:44:45 Z

I am trying to get the CLR user-defined aggregate functions sample working (VB version).

I've saved the sample (from Invoking CLR User-Defined Aggregate Functions ) to a .vb file and compiled it like this:

C:\data\VStudio\projects\SQLutils\SQLutils>c:\WINDOWS\Microsoft.NET\Framework\v2
.0.50727\vbc /t:library /out: SQLutils.dll Class1.vb
Microsoft (R) Visual Basic Compiler versie 8.0.50727.42
voor Microsoft (R) .NET Framework versie 2.0.50727.42
Copyright (c) Microsoft Corporation. Alle rechten voorbehouden.

The SQLutils.dll file is created without warnings or errors.

I've created the assembly

create assembly sqlutildll from 'D:\SQLdata\dll\SQLutils.dll'

and then the function:

CREATE AGGREGATE Concat (@input nvarchar(200)) RETURNS nvarchar(max)

EXTERNAL NAME sqlutildll.Concatenate

and then I got this error:

Msg 6558, Level 16, State 1, Line 1

CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.

Msg 6597, Level 16, State 2, Line 1

CREATE AGGREGATE failed.

What is this UDAGG specification ?

How can I make this function working?

The VB code:

Code Snippet

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlTypes

Imports System.IO

Imports System.Text

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> <Microsoft.VisualBasic.ComClass()> _

Public Class Concatenate

Implements IBinarySerialize

''' <summary>

''' The variable that holds the intermediate result of the concatenation

''' </summary>

Private intermediateResult As StringBuilder

''' <summary>

''' Initialize the internal data structures

''' </summary>

Public Sub Init()

Me.intermediateResult = New StringBuilder()

End Sub

''' <summary>

''' Accumulate the next value, not if the value is null

''' </summary>

''' <param name="value"></param>

Public Sub Accumulate(ByVal value As SqlString)

If value.IsNull Then

Return

End If

Me.intermediateResult.Append(value.Value).Append(","c)

End Sub

''' <summary>

''' Merge the partially computed aggregate with this aggregate.

''' </summary>

''' <param name="other"></param>

Public Sub Merge(ByVal other As Concatenate)

Me.intermediateResult.Append(other.intermediateResult)

End Sub

''' <summary>

''' Called at the end of aggregation, to return the results of the aggregation.

''' </summary>

''' <returns></returns>

Public Function Terminate() As SqlString

Dim output As String = String.Empty

'delete the trailing comma, if any

If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then

output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)

End If

Return New SqlString(output)

End Function

Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read

intermediateResult = New StringBuilder(r.ReadString())

End Sub

Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write

w.Write(Me.intermediateResult.ToString())

End Sub

End Class

While using SQL CLR - Error Occured :: There is no database connection selected, Use the project property pages to select one.

Mon, 09 Nov 2009 10:36:15 Z

Hello Friends,

I am trying to use SQL CLR External Name variable. While Building the SQL Server Project I am getting the following error,
" There is no database connection selected. Use the project property pages to select one "

Thanks in Advance...

Thanks & Regards,
Dilip D. Kulkarni
[ Explore the Unexplored!!! ]

SQL Server CLR and Virtual Memory Alloation

Thu, 12 Nov 2009 16:15:31 Z

We have a CLR Stored procedure that we use to send as a binary string a file to the database server for further processing after it writes it to the FileSystem using a StreamWriter. I am running into a problem on our box where we are sending in a 75mb file (as a binary string) and all of a sudden the server runs out of memory.

The server is a 64 bit 16 processor with 26gb or ram on SQL version 9.00.3186.

I run this query to check on the memory;
select single_pages_kb, multi_pages_kb,virtual_memory_reserved_kb, virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

And I get the following results
single_pages_kb multi_pages_kb virtual_memory_reserved_kb virtual_memory_committed_kb
1144 39920 23080896 16787548

So virtually the CLR is consuming 16gb of memory for the 75mb file ( I can trace that the memory grows to this point only when we do this file upload, there are no loops of data occurring here, nor is anything else occurring within the system in the CLR). At this point the server is unusable and we must reboot the sql instance.

We were having no problems as of a week ago and this started all of a sudden the other day and we are at a loss to explain what is going on here. I am not sure if there is some garbage collecting that is not happening or something else that is going on from a configuration standpoint of the CLR running on the server.

Thank you for any help that you can provide.

How to add CLR dll after restore db on another server? thanks

Fri, 07 Sep 2007 06:21:29 Z

I use VS2005 to deploy my CLR dll, it is easy. But after I copy db over to another server, the new server doesn't have driver W, so I need put dlls on Driver C. I think I have to relink assembly with those dlls. How should I do that, I tried Alter Assembly add file, but it doesn't work, the following is a script I get from one of my assembly. The dll is emaillib.dll, I could not find it in script. I have copied the dll to c:\emaillib.dll, so how can I link it back? I don;t want to drop the assembly since if I do that, I have to drop all CLR objects of the assembly first.

CREATE ASSEMBLY [EmailLib]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000......

WITH PERMISSION_SET = UNSAFE

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000000F00000048000000000000000D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF38C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF.... bery long

AS N'EmailLib.pdb'

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D656D6C696E6B20496E632E22295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D656D6C696E6B20496E632E203230303722295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'

GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'ASSEMBLY',@level0name=N'EmailLib'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'W:\ablelink\ableclr\EmailLib' , @level0type=N'ASSEMBLY',@level0name=N'EmailLib'

Writing to SqlBytes datatype returned error "System.Data.SqlTypes.SqlTypeException: The buffer is insufficient"

Mon, 02 Nov 2009 10:31:50 Z

I am using MSSQL 2005 database and created CLR UDF . Writing to SqlBytes returned error
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient

System.Data.SqlTypes.SqlTypeException:
   at System.Data.SqlTypes.SqlBytes.Write(Int64 offset,
               Byte[] buffer, Int32 offsetInBuffer, Int32 count)

SqlBytes^ MyCLR::e_vrcl_nm(SqlChars pi_Data,
                SqlString Database,
                SqlString Owner,
                SqlString Table,
                SqlString vCol,
                SqlBinary vIV)
{
SqlBytes ^output = gcnew SqlBytes();
.
.
while ((bytesRead = pi_Data.Read(offsetr,Data, 0, chunkSize)) > 0)
{
.
.
.
//converted to binary data and write to ouput
output->Write(offsetw,tmpOutBuffer,0,tmpOutBytes);
}

.
.
.
//return SqlBytes

}

How should I used SqlBytes write method to write large data ~1 GB in chunks and return back to sql

SQLCLR Execution Hangs

Wed, 21 Oct 2009 12:40:31 Z

I have a number of CLR Dlls in the database. Some of these are Safe and some are Unsafe. None of them use Reset Events. Occasionally I find that all the CLR execution against the database stops for anywhere up to a few minutes. Looking at the transactions against the database they are all in the CLR_MANUAL_RESET wait state. The CPU usage of sqlserver is neglibale at these times.

We are running SQL Server 2005 Enterprise Edition (64bit) 9.00.1399.06

Can anybody help my out with some pointers as to what to look for?

Is it worth upgrading to the latest service pack?

Thanks in advance

Alan

sql server 2008 express using a CLR stored procedure - email - couple questions

Wed, 28 Oct 2009 18:47:53 Z

I just came across this:
http://www.mssqltips.com/tip.asp?tip=1795

which shows how to send email from sql server express using a CLR stored procedure. I'm using sql server 2008 express which does not support SQL Mail or Database Mail, so I'm just finding out.

I'm ok with implementing the CLR stored procedure solution, as I have some .net development experience (a little, but enough). But I had a couple questions first..

1. is this a good option considering I'm using sql 2008 express?

2. what does the '

WITH  PERMISSION_SET  =  UNSAFE' line mean? Will I be degrading security by doing this?
 
 3. in the example the smtp server address gets hardcoded into the .net assembly, can I modify that so that it gets the address from an external file? I have a few servers I need to do this with, and they would all be using a different smtp server. Basically, I'm not sure if accessing the .net code from sql would make that not possible? Or, duh, I just realized I could just add the smtp address as a parameter to the .net function and the stored procedure.
 
 any input would be appreciated.... by the way, is this the right group for this?

.Net Framework Error - System.ServiceModel

Wed, 09 Sep 2009 10:33:35 Z

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).

Initially the configuration of the DB Server was
1. Windows 2008 Server - 32 bit
2. SQL Server 2008 - 32 bit

I 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 64 bit. 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.

I'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

Slow thread while counting lines i/o

Mon, 19 Oct 2009 09:22:28 Z

Hallo,
I have a problem with a thread that i made. I have a form which of course runs on his own thread. From that place i want to start a new thread which will count some lines in a tab separated file. The reason why i start a new thread is because i don’t want the counting of the lines to slow other processes on my form. Unfortunately this happens anyway.

I still can’t find out why the thread slows my other processes in the original form. The whole idea of a new thread is to avoid slowlines on other processes....

Does someone have any idea?

   Public Sub LogThreadStart()
        'prepare/start new thread to log data
        'this way the slowlines of datalogging don't interupt the other processes.

        Dim startThreadLogSub As New System.Threading.Thread(AddressOf Me.ThreadLogSub)
        startThreadLogSub.Priority = Threading.ThreadPriority.Normal
        startThreadLogSub.Start(Me.DsDTSettings.Tables("dtDataLogSettings").Rows(0).Item("clmnLogFileName").ToString())

    End Sub
    Private Sub ThreadLogSub(ByVal Filnm As String)
        'start LogFrmDLL.LogDataNow 
        LogFrmDLL.LogDataNow(Me.QueueDataLog, Filnm, MaxLogFileLinesSurpassed)
    End Sub



Public Module LogFrmDLL

    Public Sub LogDataNow(ByVal QueueLog As Queue, ByVal FileNameDataLog As String, ByRef MaxLogFileLinesSurpassed As Boolean)

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' if the logfile >= then x lines then 
        ' change MaxLogFileLinesSurpassed to true, in that way a new file 
        ' is created when in HdForm.TimerDataLog_Tick
        Using FileReader2 As New Microsoft.VisualBasic.FileIO.TextFieldParser(FileNameDataLog)
            FileReader2.Delimiters = New String() {"    "}
            While Not FileReader2.EndOfData
                FileReader2.ReadLine()
                If FileReader2.LineNumber >= 70000 Then MaxLogFileLinesSurpassed = True
            End While
        End Using
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    End Sub

End Module

SQL Server 2005 | Queries Going Into Suspended Mode

Sat, 09 Dec 2006 14:00:33 Z

Hi,

We moved to SQL server 2005 (SP1) recently and are observing a strange problem. During our performance tuning runs, some queries would never finish execution. Looking at the traces on the client side we don't get any hint of root cause. We see traces right upto the DB operation API (for instance C#'s ExecuteCommand) and no traces beyond that. The call never returns/errors out despite setting the command timeout to 30 sec. On the server side we see the SPIDs of these queries in suspended state with one of the below mentioned wait types (so far):
-resource_semaphore_query_compile
-ASYNC_NETWORK_IO
-Cxpacket

Server config:
Winserver 2003
4 dual core Xeon 3.2 Ghz
18 gigs of RAM
parallelism level set to 6

Has anyone experienced something similar on SQL server 2005 ?

Regards,

Create Assembly in SQL Sevrer 2008 without knowing exact path

Fri, 23 Oct 2009 14:59:53 Z

Hi.

I need help with how to create an assembly in a sql server 2008 database witout being able to issue the command "CREATE ASSEMBLY FROM" with a local or networked path.

I have a Database Setup project that references the .dll, I could also have it as a resource if that helps. The Setup project is wpf and will run as an exe and will if needed run from a directory where the .dll in question resides.

What I need is a way to create the assembly in the database without SQL Server knowing where the assembly is (at least from a local or UNC point of view), as install paths and computernames differ...obviously. It needs to be generic and work from all paths therefore I cannot hard code the path into the CREATE ASSEMBLY command. Of course I don't have to issue that command if I can use others, but thats what I know of now.

I will of course be able to establish a SqlConnection between my client and Sql server

Is this even possible?

I could elaborate a whole lot more to get you to understand my problem, but it would most likely be too much so I'll wait and see if anyone gets this.

Best Regards/t

Integrating Website with accounting software

Mon, 26 Oct 2009 12:28:46 Z

Hello Friends,

I have created an Ecommerce website using asp.net and sql server 2008; my question is I have to integrate my website with the OPERA II Accounting software;
How do I do that?
If I am integrating with OPERA II software do I have to write logic to calculate the price options and discounts as presently this is done with OPERA
How do I introduce the banking system; for credit card payments

thanks

Where can I find the dll file which was auto deployed to SQL 2005?

Fri, 23 Oct 2009 21:39:28 Z

I can see the SP it created, but I could not find the DLL, any idea?
thanks!

calling webservice from sql server 2005 trigger

Tue, 20 Oct 2009 17:31:00 Z

Hi,

I am using "HttpRuntime.Cache.Insert" to maintain the data set in the cache. The problem is that when new data is added to the data base table, I need to update data set which is in the cache.

For this I want to call a webservice from the trigger to update the data set(cache). Can anyone help me that how to do that?

Thanks in advance

krishna

unable to open the physical file operating system error 32

Thu, 09 Jul 2009 15:00:36 Z

Hi,

I have installed Sql Server 2005 with an instance name.

I am able to login using SQL Management Studio.

When i try to connect using Visual Studio through database explorer (its a VSTA for infopath), I get the error unable to open the physical file operating system error 32. I am struggling to slove this.

Any help in this regard would be helpful.

Thanks and Regards,
Vijaybabu.N.S

running 64 bit sql server mgmt studio

Tue, 20 Oct 2009 02:00:28 Z

Hello All!

So I got a new computer with Vista Ultimate 64 bit. I have SQL developer, 32 and 64 bit versions. I loaded the 64 bit, but I can't get mgmt studio to load. I tried both 64 and 32. Any thoughts?

TIA!

Rudy

The UDP/UDF/UDT did not revert thread token.

Thu, 24 Jan 2008 10:29:47 Z

I have been struggling with this error for a while now. Not much when I put it in the search engines. I get the error as follows when I execute this CLR stored procedure:

Msg 10312, Level 16, State 49, Procedure SpPICK00, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

The code for the stored procedure is as follows. If anybody could offer any advice on what this error means I would appreciate it. I know through debugging that it's erroring out on the ImpContext = ClientID.Impersonate() line.

Dim DbCon As New SqlConnection("context connection=true")

Dim DbSql As New SqlCommand("SELECT TOP 1 * FROM TblTransactions", DbCon)

Dim TransactionID As Int64

DbSql.Connection.Open()

Dim DbRs As SqlDataReader

DbRs = DbSql.ExecuteReader

While DbRs.Read

TransactionID = DbRs("TransactionID")

End While

DbRs.Close()

Dim MenuID As Int16

Dim MONumber As String

Dim LineNumber As String

Dim PTUse As String

Dim SEQN As String

Dim WorkCentre As String

Dim Stock As String

Dim Bin As String

Dim Qty As Double

DbSql = New SqlCommand("SELECT * FROM VwPickList WHERE TransactionID = @TransactionID", DbCon)

DbSql.Parameters.Add("@TransactionID", SqlDbType.BigInt).Value = TransactionID

DbRs = DbSql.ExecuteReader

If DbRs.Read Then

MenuID = DbRs("MenuID")

MONumber = DbRs("MONumber")

LineNumber = DbRs("LineNumber")

PTUse = DbRs("PT_USE")

SEQN = DbRs("SEQN")

WorkCentre = DbRs("WorkCentre")

Stock = DbRs("Stock")

Bin = DbRs("Bin")

Qty = DbRs("Hours")

End If

DbRs.Close()

DbSql.Connection.Close()

DbCon.Dispose()

DbSql.Dispose()

Dim FSClient As New FSTIClient

Dim ClientID As WindowsIdentity

Dim ImpContext As WindowsImpersonationContext

ClientID = SqlContext.WindowsIdentity

ImpContext = ClientID.Impersonate

Shell("NET USE K: \\FPTEST\FShift", AppWinStyle.Hide)

FSClient.InitializeByConfigFile("K:\Mfgsys\fs.cfg", False, False)

If FSClient.IsLogonRequired Then

FSClient.Logon("VBS", "visib", "")

End If

If MenuID = 2 Then

Dim Pck As New PICK08

Pck.OrderType.Value = "M"

Pck.IssueType.Value = "I"

Pck.OrderNumber.Value = MONumber

Pck.LineNumber.Value = LineNumber

Pck.PointOfUseID.Value = PTUse

Pck.OperationSequenceNumber.Value = SEQN

Pck.ItemNumber.Value = WorkCentre

Pck.Stockroom.Value = Stock

Pck.Bin.Value = Bin

Pck.IssuedQuantity.Value = Qty

FSClient.ProcessId(Pck)

ElseIf MenuID = 1 Then

Dim Pck As New PICK04

Pck.OrderType.Value = "M"

Pck.IssueType.Value = "I"

Pck.OrderNumber.Value = MONumber

Pck.LineNumber.Value = LineNumber

Pck.PointOfUseID.Value = PTUse

Pck.OperationSequenceNumber.Value = SEQN

Pck.ItemNumber.Value = WorkCentre

Pck.IssuedQuantity.Value = Qty

FSClient.ProcessId(Pck)

End If

FSClient.Terminate()

ImpContext.Undo()

Sql Server Express Multiple Connection

Tue, 20 Oct 2009 17:51:44 Z

Hi All,

I have a C# application that runs on Sql Server. The application has multiple SQLConnections to the same database. When I try to run the application on Sql Express 2005, it does not allow more than 1 SQLConnection.

Is there anything I can do to fix this?

Any insight is most appreciated.

Mark G

convert smalldatatime to datatime

Wed, 21 Oct 2009 20:46:14 Z

I try to query a calendar database which created by a third-party. The

value stored in the event start datetime is a 18-digits number, for

example: 633979008000000000, which is actually 2010/1/1/, 12:00AM, I

try to understand how does this long integer/char calculate and how

does the number/char related to a regular datetime, any function in

SQL can do this conversion? Basically, I want to get the actually

date and time from this number.







Thank you.

CLR Stored Procedures as system objects

Tue, 13 Oct 2009 16:06:50 Z

Hi there,

Is is possible (and how) to invoke CLR Stored procedures living in master from other databases, such that the sproc is executed from with in that database's context?

This is simple in TSQL procs, marking the proc name as 'sp_' and executing the 'sys.sp_MS_marksystemobject'.

This process seems to fail when I deploy my CLR proc.

Any insight?

How can i assign 5 digit Identity Increment in Microsoft Sql Server? HELP PLZZ.

Tue, 20 Oct 2009 17:09:58 Z

Hi i have created a database, now i have a table that contain the primary key on my bill number column with tinyint and in column properties i have changed the identity specification to yes and its identity increment and seeds are 1 now what i need is i want my starting value increment begins with 5 digits number, for instance instead of when its just arranged by 1 2 3 4 5 and etc i want its increment to be like 00001 00002 00003 00004 so on and so forth and when it reaches 00009 it must automatically change the next increment to 00010 for each of my row. but unfortunately i couldnt do it with any possible datatype for my column, can anybody concern about this?

thanks.

Handle "Query was cancelled by user." in CLR StoredProc

Tue, 13 Oct 2009 12:25:20 Z

Hi everyone

How can one elegantly handle the situation where the user cancels the CLR procedure (Alt+Break or job cancellation etc)?

There doesn't appear to be any event or way to handle it. I've spent the past few hours trying to find a way to determine if the procedure had been cancelled by the user. What seems to be happening is that the CLR runtime for the procedure seems to get cleaned up immediately from when it is cancelled - it's like the runtime for the procedure has already been disposed. If I set a breakpoint and cancel the SP then I find my code context is immediately invalid.

I was hoping to use a global Mutex but I whilst I can create a global mutex if the proc gets cancelled then the mutex still remains because it hasn't been released, due to the abrupt termination of the CLR procedure.

This may be by-design, but is there any way this can be handled?

Thanks