Calling .Net Assembly Or Dll From SQL Server 2005 At Config Level 80
Jul 26, 2006
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.
View 21 Replies
ADVERTISEMENT
Dec 14, 2007
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
View 1 Replies
View Related
Apr 7, 2008
I have a custom assembly that uses a config file to obtain database connection settings. This dll is then used by my sql report. I have added refrerenced to all the required dll's in my report and added the required code groups to all the necessary files. I have also placed my app.config file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer and C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies
However when I actually run the report in the designer (vs2005) I am receiving Null reference exceptions when my code tries to read my config file because it is reading the machine.config file and not my app.config file. If I hard code my connection string everything works fine.
Can anyone tell me why the machine.config file would be referenced instead of my app.config file and how I can stop it from doing this?
View 6 Replies
View Related
Sep 26, 2006
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
May 15, 2008
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
View 8 Replies
View Related
Apr 17, 2008
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
View 1 Replies
View Related
Aug 25, 2015
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?
View 2 Replies
View Related
Apr 20, 2007
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
View 2 Replies
View Related
Nov 13, 2007
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
View 3 Replies
View Related
Feb 29, 2008
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
View 20 Replies
View Related
Apr 11, 2008
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
View 4 Replies
View Related
Oct 11, 2007
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.
View 2 Replies
View Related
Jul 12, 2007
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.
View 5 Replies
View Related
Jan 19, 2007
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
View 4 Replies
View Related
Jan 8, 2006
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
View 10 Replies
View Related
Oct 6, 2006
My project currently has task which have their own individual event handlers that get called onError (setup event messages). I also have a package level event handler that performs a generic task (sending events to the windows eventviewer) In the package level event handler there is a script task that decides on a boolean variable whether to "Success" or "Failure" to different task. When I fail one task of the main control flow, the task level event handler runs, then the package level event handler runs, and then it also runs again for some unknown reason. The second time it runs it picks up the value of a variable set in the variables window. However, I change this value at runtime to the value from a database. I can't understand why it would run the second time, and if it did run why it would have the value from the variables window and not the value that is set in memory. It's like the event handler runs with the value from memory and then runs and picks the values back out of the variables window, replacing the db values and re-runs.
Maybe the package itself is failing all together and then re-runing the package level event handler?
Any help would be greatly appreciated.
-Chris
View 1 Replies
View Related
Nov 29, 2007
As we know Sql Server 2005 provides its ability to deploy clr assembly on it. However my problem is that my assembly has a configuration file. I used ConfigurationManager.GetSection to create some of the object. When I am deploying the project the trouble is that the app.config is missing. I used a class lib project type to create the assembly. Besides changing a storing method, can I still use this config file to reach my goal? Sqlserver.exe.config is not a good idea and it is not workable. The sqlserverexe host cannot find the assembly files.
Thanks very much!
View 1 Replies
View Related
Mar 29, 2008
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
View 1 Replies
View Related
Aug 21, 2007
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
View 13 Replies
View Related
Feb 25, 2008
Hi all asp.net 2.0 users,
I want to send the mail to the users of my system when there is any changes in the database or when i assign any user to the project giving them the information about the changes or asignee.
For this first i have to configrue Database mail in the SQL Server 2005. I tried this by going to the SQL Server 2005 Management Studio -> Object Explorer -> Management -> .............but there i donot find the Database mail option.
Again i went to the configuration tool -> SQL Server Surface Area Configuration
-> Surface Area Configuration....and there I enabled database mail.......
But still I have a problem of getting Database mail Option Object Explorer -> Management -> .......
I have been using SQLServer2005_SSMSEE for this as this is windows installer package.....what is the problem
I am tried for searching this..........and its very important for me.....
Will please help me in this condition its very urgent.....
View 1 Replies
View Related
Jun 5, 2006
Does anyone know if it's possible to use the standard .config file within a CLR Trigger to read properties via the System.Configuration namespace
I guess it's not possible because the CLR Trigger needs to be compiled as an assembly which is hosted by SQL Server
Thanks
Jason
View 1 Replies
View Related
Feb 22, 2008
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.
View 9 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Apr 23, 2008
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
View 4 Replies
View Related
Jul 18, 2006
hi
i want to know if row level trigger works with sql 2005.
because in 2000.. only statement level trigger was used.
Thks in advance.
View 3 Replies
View Related
Jul 19, 2006
hi
actually i have a temporay table wich has four columns
say col_1,col_2,col_3,col_4.
so firstly i wud bulk insert from a text file wich mite contain thousands of rows.
then from this temp table col_1 and col_2 shd go to master_a.wich has an identiy column as primary key.
say id,col_1, col_2.
i will have another table detail_b where in there is a foreign key to the table master_a for id.
so it will have f_id, col_3,col_4.
so i am riting a trigger on master_a. so whenver row is inserted in master_a. coresponding id and col 3 col4 shd be inserted into detail_b.
For this in oracle we have row level trigger. where in for each insertion in master a .. correspoding trigger will fire but in sql 2000..
but i wud like to implemtn row level trigger..
can u hlep me out..
View 1 Replies
View Related
Sep 13, 2006
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
May 30, 2008
hi,
How to get 2 level tree node in the sql server 2005. We have table with data.On load we populate the tree in Asp.net.
Regards,
Vinayak Panchal
View 2 Replies
View Related
Jan 22, 2007
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 !
View 4 Replies
View Related
Sep 27, 2006
We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies
View Related
Oct 23, 2007
Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
Thanks in Advance,
Mitesh
View 4 Replies
View Related
Oct 2, 2006
I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.
Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000
If any one know something regarding this please reply me.
Thx,
Buddhika
View 10 Replies
View Related
Nov 2, 2006
We want to use sql 2005 failover clustering feature, so that upgrade sql 2000 is necessary. But some of the stored procedures built in 2000 are not working directly in 2005, set the compatibility level to 80 could make them work. In this case, can somebody here tell me if down grade the database level will affect failover clustering running properly? e.g. will data still be synchronized properly? Thanks in advance.
View 3 Replies
View Related