Hi,
I have got a simple C# assembly which call a ASP.net web service and executes a webmethod, now i want to call this assembly from sql server 2005 within the sql server function. can any body let me know how to do it.
Thanks
Hi,
I create a dll assembly with the strong name in VB.Net environment.
Created assembly is registered also.
SQL Server 2005 configuraton level is set at "80"
I want to call that assembly from stored procedure with the database config level at "80"
But when i execute the stored proecure i get the following error
Error Source: "ODSOLE Extended Procedure"
Description: "Invalid Class String"
My Code in VB.Net is given below:
Imports System
Imports System.Reflection
Imports System.Globalization
Imports System.IO
Imports System.data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports GreatDataAccess
Namespace Test
Public Class clsTest
Public Shared Sub GenTest()
''''............ some code is here
End Sub
End Class
End Namespace
Stored Procedure
CREATE PROCEDURE [dbo].[PPGenerateFile]
AS
BEGIN
Declare @retVal INT
Declare @comHandler INT
declare @errorSource nvarchar(500)
declare @errorDescription nvarchar(500)
declare @retString nvarchar(100)
-- Intialize the COM component
EXEC @retVal = sp_OACreate Test.clsTest, @comHandler OUTPUT
IF(@retVal <> 0)
BEGIN
--Trap errors if any
EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
SELECT [error source] = @errorsource, [Description] = @errordescription
Return
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandler,'GenTest()',@retString
IF (@retVal <>0 )
BEGIN
EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
SELECT [error source] = @errorsource, [Description] = @errordescription
Return
END
select @retString
END
Please Help me to solve this problem.
Hi everyone, I want to call the .net assembly(DLL) in a DTS package. Can anyone help me as to how to achieve this. I read numerous articles on internet, but couldn't find the one that can help me with this problem.Any help or directing me to an article will be greatly appreciated.Thanks.Vinki
View 9 Replies View Related
Hi everyone,
i am trying to register a assembly in SSAS which i actually did fine as per BOL by right clicking on assembly folder and pointing to the dll file. And then i tried to call the function from management studio using Call command and Select statement...both outputs a error which looks like
Executing the query ...
Query (1, 6) The '[TestKPIMeasures].[KPIMeasures].[getKPIData]' function does not exist.
Execution complete
Let me explain my scenario so that you ll get a better picture. I have a cube named 'FourH KPI values' and i have registerd the below as dll file.
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.AnalysisServices.AdomdClient;
using System.Reflection;
using System.Reflection.Emit;
namespace TestKPIMeasures
{
public class KPIMeasures
{
public static System.Data.DataTable getKPIData(String Connstring, String ExecCommand, String kpimeasures)
{
CellSet cellset;
//DataSet ds = new DataSet("KpiDataSet");
DataTable dt = new DataTable("KpiMeasures");
// ds.Tables.Add("KpiMeasures");
try
{
//Create AdomdConnection object
AdomdConnection asConnection = new AdomdConnection(Connstring);
asConnection.Open();
String[] keyindicators = kpimeasures.Split(',');
string commandText = ExecCommand;
AdomdCommand command = new AdomdCommand(commandText, asConnection);
DataColumn column = new DataColumn();
column = new DataColumn("KPI Name");
column.DataType = typeof(string);
dt.Columns.Add(column);
column = new DataColumn("KPI value");
column.DataType = typeof(string);
dt.Columns.Add(column);
column = new DataColumn("KPI goal");
column.DataType = typeof(string);
dt.Columns.Add(column);
column = new DataColumn("KPI status"); column.DataType = typeof(string);
dt.Columns.Add(column);
foreach (string item in keyindicators)
{
command.Parameters.Clear();
command.Parameters.Add(new AdomdParameter("Value", item));
command.Parameters.Add(new AdomdParameter("Goal", item));
command.Parameters.Add(new AdomdParameter("Status", item));
command.Parameters.Add(new AdomdParameter("Trend", item));
cellset = command.ExecuteCellSet();
DataRow row = dt.NewRow();
row["KPI NamE"] = item;
row["KPI value"] = cellset.Cells[0].FormattedValue;
row["KPI goal"] = cellset.Cells[1].FormattedValue;
row["KPI status"] = cellset.Cells[2].FormattedValue;
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
}
}
Now i am trying to call the assembly from management studio,
CALL TestKPIMeasures.KPIMeasures.getKPIData("Provider=MSOLAP.3;DataSource=D64ETL01;Initial Catalog=FourH KPI values", "SELECT{KPIValue(@Value), KPIGoal(@Goal), KPIStatus(@Status)} ON COLUMNS FROM [Key Performance Indicators]", "Test measure,Test measure 2,KPI 3")
also tried Select statment...still no luck
Can anyone tell me what i am missing out.......Thanks in advance
Hi,
I am trying to access a .Net assembly in script component, which internally uses Microsoft Enterpise library dll's.
The problem I am facing is when I copy the config sections needed for the Enterprise library from web.config to dtsdebughost.exe.config file and run the package, It ends in failure with below message
"Error: The script files failed to load."
My dtsdebughost.exe.config looks like below:
Code Snippet
<configuration>
<startup>
<requiredRuntime version="v2.0.50727"/>
</startup>
<configSections>
<section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<section name="exceptionHandling" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Configuration.ExceptionHandlingSettings, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<loggingConfiguration name="Logging Application Block" tracingEnabled="true"
defaultCategory="" logWarningsWhenNoCategoriesMatch="true">
<listeners>
<add fileName="LogMedtrack-Error.log" rollSizeKB="5000" timeStampPattern="dd-MMM-yyyy"
rollFileExistsBehavior="Overwrite" rollInterval="Day" formatter="Default Formatter"
header="----------------------------------------" footer="----------------------------------------"
listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.RollingFlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.RollingFlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Rolling Flat File Trace Listener" />
</listeners>
<formatters>
<add template="Timestamp: {timestamp}
Message: {message}
Category: {category}
Priority: {priority}
EventId: {eventid}
Severity: {severity}
Title:{title}
Machine: {machine}
Application Domain: {appDomain}
Process Id: {processId}
Process Name: {processName}
Win32 Thread Id: {win32ThreadId}
Thread Name: {threadName}
Extended Properties: {dictionary({key} - {value}
)}"
type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Default Formatter" />
</formatters>
<logFilters>
<add categoryFilterMode="AllowAllExceptDenied" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.CategoryFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Category Filter" />
<add minimumPriority="0" maximumPriority="2147483647" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.PriorityFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Priority Filter" />
</logFilters>
<categorySources>
<add switchValue="All" name="Tracing">
<listeners>
<add name="Rolling Flat File Trace Listener" />
</listeners>
</add>
</categorySources>
<specialSources>
<allEvents switchValue="All" name="All Events">
<listeners>
<add name="Rolling Flat File Trace Listener" />
</listeners>
</allEvents>
<notProcessed switchValue="All" name="Unprocessed Category" />
<errors switchValue="All" name="Logging Errors & Warnings" />
</specialSources>
</loggingConfiguration>
<exceptionHandling>
<exceptionPolicies>
<add name="Business Policy">
<exceptionTypes>
<add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
postHandlingAction="NotifyRethrow" name="Exception">
<exceptionHandlers>
<add logCategory="Tracing" eventId="100" severity="Error" title="Agility Application Log."
formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
priority="0" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Logging Handler" />
</exceptionHandlers>
</add>
</exceptionTypes>
</add>
</exceptionPolicies>
</exceptionHandling>
</configuration>
Please let me konw, If there is anything wrong I am doing or is there any other way to handle the situation
Regards,
Kalyan
I have a SSRS 2012 report which references a custom c# assembly.  This report exists in multiple environments (alpha, beta and  production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
I created a CLR function based on following VB code:
Imports Microsoft.SqlServer.Server
Public Partial Class SqlClrVB
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function GetTotalPhysicalMemory() As Integer
GetTotalPhysicalMemory = My.Computer.Info.TotalPhysicalMemory
End Function
End Class
The VB code was complied into a DLL called totalmem.dll and call following TSQL to map it into a SQL function:
create assembly totalmem from '!WORKINGDIR! otalmem.dll'
WITH PERMISSION_SET=UNSAFE
go
create function fnGetTotalMem()
returns int
as external name totalmem.SqlClrVB.GetTotalPhysicalMemory
go
When I call this function, it returned following error:
select dbo.fnGetTotalMem()
Msg 6522, Level 16, State 2, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'fnGetTotalMem':
System.IO.FileNotFoundException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at Microsoft.VisualBasic.MyServices.Internal.ContextValue`1.get_Value()
at My.MyProject.ThreadSafeObjectProvider`1.get_GetInstance()
at SqlClrVB.GetTotalPhysicalMemory()
.
Anyone knows why I'm hitting this error? I didn't reference any System.Web interface why it needs to load System.Web assembly? The same code runs OK if I compile it as a separate VB application out side of SQL Server 2005.
Thanks much,
Zhiqiang
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
Hello,
I have problem with starting SQL Servre 2005 (Standard Edition), during start I get bellow errors.
----------------------------------------------------------------------
Error:
Could not start the SQL Server (SQL2005) service on Local Computer.
Error 14001: This application has failed to start because the
application configuration is incorrect.
Reinstalling the application may fix this problem.
--
Event Viewer:
Event Source: Service Control Manager
The SQL Server (SQL2005) service failed to start due to the following error:
This application has failed to start because the application configuration
is incorrect.
Reinstalling the application may fix this problem.
Event Source: SideBySide
Generate Activation Context failed for
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe.
Reference error message: The referenced assembly is not installed on your
system.
Event Source: SideBySide
Resolve Partial Assembly failed for Microsoft.VC80.ATL. Reference error
message:
The referenced assembly is not installed on your system.
Event Source: SideBySide
Dependent Assembly Microsoft.VC80.ATL could not be found and Last Error was
The referenced assembly is not installed on your system.
----------------------------------------------------------------------
Could you help, how resolve problem. I don't want reinstall all SQL Server.
This probably happened after Windows install (automatic) updates from
Windows Update web site.
Thanks in advance
ps.
Windows 2003 Enterprise Edition
--
Regards,
anxcomp
Hi to all,
i have one VS 2003 assembly,now i want to laod this asseembly in SQL Server 2005 .then i am going to write SQL-CLR 2005 function in which i want to use some functions of this VS 2003 assembly.is this possible?
please help me....
Thanks in advance.
Chetan S. Raut.
I am trying to create an assembly in SQL Server 2005 from a compiled VS 2005 solution in VB.net
I am getting the following error in SQL Server Management Studio:
Assembly 'PrintWorkOrder' references assembly 'crystaldecisions.crystalreports.engine, version=10.2.3600.0, culture=neutral, publickeytoken=692fbea5521e1304.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Can someone explain to me how I can load the crystal assembly it is asking for?
(My SQL Server Instance is on a remote machinf from my Development Environment.)
Thanks.
In the past we've days we've had an assembly that had worked previously which now fails with the error:
Failed to open malformed assembly 'System.Data' with HRESULT 0x80070008.
There have been no changes to the .NET framework at the server level, all other assemblies on the server continue to function and the assemly does work when deployed to another server. The assembly has been recreeated from scratch yet the error persisted. In another attempt to narrow down the cause we moved the system.data.dll to another server in an attempt to see if the particular DLL was the issue, however the other server had no issues.
I'm assuming that the error we're getting has some cause other than with System.Data.dll.Does anyone have any ideas what could be causing this or how to proceed in troubleshooting this issue?
Thanks
Bill
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
I designed dll file to get data from sql server, i am calling the dll from ssrs 2005
in preview , everything is ok but after deploying and put the dll file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin and add this to rssrvpolicy.config
<CodeGroup
class="SqlClientPermission"
version="1"
Name="MySecurityExtensionCodeGroup"
Description="Code Group for Don's test security extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQLReporting
ServicesReportServerinMyAssembly.dll"
/>
</CodeGroup>
i still get #Error
i try everything , i don't know what i can do.
pls help
Hiiiiiiii all
I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration
I have tried like this
CREATE ASSEMBLY abc
FROM 'C:abc.dll'
WITH PERMISSION_SET = SAFE
but it gives me
incorrect syntax error
so plzzzzz anyone help me wht to do in my probbbbbbbbb???????
Pratik Kansara
I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
· The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
· The signatures of methods in the assembly that are called from other assemblies.
· The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
· The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
· The FillRow method name attribute for CLR table-valued functions.
· The Accumulate and Terminate method signature for user-defined aggregates.
· System assemblies.
· Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
· Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
· Adding new public methods.
· Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
Hi ,
I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo .
Can I do that ?
Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
Thank you
zkar
Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help.
View 2 Replies View Related
I have created a SOAP/HTTP end point which returns .NET DataSet ( ... FORMAT=ROWSETS_ONLY ... ). Then I created a proxy class using Visual Studio 2005 and wrote a cusumer .NET class in which I call that web service's method synchronously. Everything is Ok but when I call asynchronously "TargetInvocationException" is raised. I need urgent solution to this problem.
Thanks !
Goodday.
I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way
Code Block
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _
"Initial Catalog='DBName';Integrated Security='SSPI';"
cnn.Open
cmd.ActiveConnection = cnn
cmd.CommandText = "sp_DefaultEntityData"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
rst.MoveFirst
Do While Not rst.EOF
lstEntity.AddItem (rst.Fields(0)), 0
'lstEntity.AddItem (rst.Fields(1)), 1
rst.MoveNext
Loop
The Stored Proc is as follow:
Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_DefaultEntityData]
AS
BEGIN
SET NOCOUNT ON;
SELECT tblEntities.[Name], tblEntities.PrimaryKey
FROM tblEntities
ORDER BY tblEntities.PrimaryKey;
END
The table contains 24 entries
As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.
I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.
Here are my Q's:
1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment?
2) How do I read the values into the listbox, without displaying the primary key in die box?
Thank you in advance!
Any help is very much appreciated.
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
Hi
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
namespace inlineCLRsql{
public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());
}
}
}
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Thanks for your help
M
I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?
thanks!
Hi.
One of my databases has an assembly that uses system.web. Therefore, in the deploy script, the developer has put in a create assembly for it like so
Create Assembly [System.Web]
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE
followed by the create assembly statements for the actual bespoke assemblies.
The deploy script all works fine, as do the assemblies it creates.
However, when SQL restarts, the calls to the assemblies no longer work, and the only way to get them to is to run the deploy script (which drops them if they exist and recreates them) again.
Not particularly familiar with 2005 & CLR. Is this normal behaviour (I would have expected that once deployed, they were there after re-boot). If it's not how things are supposed to work, what's wrong, and how do I fix it?
Thanks
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but can€™t get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' 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
[ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed.
[token 0x02000008] Type load failed.
i deigned assembly and referced it to my report in preview
when deployed the report , i get #Error
i don't add any cas code.
pls help
Hello ,
when I try to create an assembly on SQL2005
Create assembly LibSql
FROM 'c:SQLNETLIBSQLLIBSQL.dll'
WITH PERMISSION_SET = External_Access
I got the following error:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.
Configuration:
Server: 8 processors with 16 GB RAM
select @@version returns:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
AWE configuration is enabled.
CLR is enabled
Server was upgreaded from sql 2000 SP3a
Restart of Sql doesn't help
Thank you for your advice.
artek
I have a clr assembly that access the internet via Sockets and file access. This require me to set the Assembly permissions level to External.
There are lots of messages on the net regarding this issue. none have worked for me.
Currently I'm trying to install the assembly with "sign the assembly" checked, createing a strong name key file.
When I run the installation I get the following message from the server
"Create failed for SqlAssembly 'xxx'.(Microsoft.sqlServer.Smo)
an exception occured while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
A severe error occured in the current command. The results, if any, should be discarded.
This is the messag I get.
The following link does not display any usefull information.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for SqlAssembly 'KBTTriggers'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View 1 Replies View RelatedI am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.
I import €œSystem.IO€? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is €œon€?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators].
2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to €œMy Windows Authentication ID€?.
3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON
4) In Visual Studio .NET 2005 Set the permission levels to €˜external€™
5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated.
Regards Steve
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.
VS.NET creates some SQL that looks pretty interesting:
CREATE ASSEMBLY [AssemblyNameHere]
FROM 0x4D5A90000300000004000000FFFF000......<continue binary data>
WITH PERMISSION_SET = EXTERNAL_ACCESS
Boy howdy!
I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.
Anyone out there been able to get this to work?
-Ben
Hi,I am learning SQL Server 2005. I need to call .NET assembly procedurefrom T-SQL.Here's part of my assembly:using System;using System.Data.Sql;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace DemoSQLServer{public sealed class Demo{[SqlProcedure(Name="PodajKsi¹¿ki")]public static void PodajKsi¹¿ki(){SqlCommand cmd = new SqlCommand("SELECT * FROM Ksi¹¿ki");SqlDataReader dr = cmd.ExecuteReader();SqlContext.Pipe.Send(dr);}...}}I have created assembly in Object Explorer (Programmability /Assemblies).How to call procedure? I tried:exec DemoSQLServer.PodajKsi¹¿kibut I got a message:Could not find stored procedure 'DemoSQLServer.PodajKsi¹¿ki'.Please help.Thank you./RAM/
View 8 Replies View RelatedDear Group.Wondered if any of you has any suggestion for the following?Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, mymistake was probably that I had installed Visual Studio 2005Professional Eval before which installed an MSSQL Express instance.Since then I didn't get 'Enterprise Manager' (excuse my ignorance, Iknow it's called differently in2005 but it's late and I'm tired) forMSSQL 2005 installed.Don't remember the error exactly but after sometrying I received some error during the MSSQL Management Studio (isthat right?) installation that it needs to be upgraded in some ini filewhich I looked for to no avail. ANYWAY, after some desperate registrydeletes and following MS Kbase articles I arrived at: 'The installerhas encountered an unexpected error installing this package. This mayindicate a problem with the package. The error code is 2718.' duringthe installation of SQL Server Backward-Compatibility files.Which is of course complete rubbish. We all now that Microsoft has donean awful job in getting SQL Express and SQL Server installedside-by-side but it never has been as bad as this on any machinebefore. Needless to say the package is fine as I've used it many timesbefore. Any suggestions?Gratefully yours,Martin'Just wanna get on with work. Tired of starring at error messages.'
View 1 Replies View Related