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.
we've seen the ease of calling sqlclr stored procedures whose parameters are known sql data types but are wondering what we're in for when we wish to pass .net objects to a sqlclr stored proc. We're confident that the sp's external assembly itself would build if instructed to "receive" .net objects etc, but can't envision how/if the sp's signature would generate nor can we imagine how such an sp would be called. We're not even sure we can envision how another sqlclr object (eg a trigger) would pass .net objects to the sp. Are we faced with using user defined data types if we need to pass .net objects to such sp's?
I am trying to generate a SQL CLR stored procedure and i have done it too. Now the next step is to convert that *.dll file in to assembly. I am getting erro that. My server Database is on the LAN, not on my PC.
Here is the code which i have tried to register an assembly...
/* CREATE ASSEMBLY MyAssembly FROM 'C:Documents and SettingsAdministrator.ORC80My DocumentsVisual Studio 2005ProjectsMyDB1MyDB1inDebugMyDB1.dll'
WITH PERMISSION_SET=SAFE
GO */
and here is the error that i am getting...
/* Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation. */
Msg 6532, Level 16, State 49, Procedure clrsp_RetailPriceOnDate, Line 0 .NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at Data_SQLCLR.StoredProcedures.clrsp_RetailPriceOnDate(SqlInt32 iSQLsysid_Individual, SqlInt32 iSQLStore, SqlInt32 iSQLGroup, DateTime dtPriceDate, SqlInt32 iSQLInclude) Msg 6532, Level 16, State 49, Procedure clrsp_RetailPriceOnDate, Line 0 .NET Framework execution was aborted by escalation policy because of out of memory.
Problem is that it is intermittent - runs fine several times(takes c 30 secs to run, returns 15k records), then fails, then runs ok, fails/runs/runs/fails etc... etc...
Using the Microsoft Patterns and Practices "Object Builder" (Dependency Injection/Builder library), I wrote an SQL CLR Stored Procedure (using VS 2005 Professional).
All compiles and deploys ok (to SQL Server 2005 Express).
However, at run-time, I get the following error upon a "BuilderContext" object's instantiation: {"Attempted to perform an operation that was forbidden by the CLR host."}
Thoughts on how to get ObjectBuilder working in the SQLCLR?
Thanks!
Andy
(posted below is the sample code...with the run-time exception occuring on the ...BuilderContext cxt = new ... call)
Microsoft.Practices.ObjectBuilder.BuilderStrategyChain chain = new Microsoft.Practices.ObjectBuilder.BuilderStrategyChain();
Microsoft.Practices.ObjectBuilder.Locator locator1 = new Microsoft.Practices.ObjectBuilder.Locator(null);
// Get error when new'ing BuilderContext: "Attempted to perform an operation that was forbidden by the CLR host." Microsoft.Practices.ObjectBuilder.BuilderContext cxt = new Microsoft.Practices.ObjectBuilder.BuilderContext(chain, locator1, null);
Hi, I have developed a website in asp.net 2. I have tester it and it is working fine on my computer but when I have uploaded it to my server I'm getting an error message when the user signup. The error occurs when I'm setting the user role to 'members'.
Error line > Roles.AddUserToRole(user.UserName, "members")
The strage thig is that it is working on my computer but not on the server. My home computer and the server are running the same software versions and the website database is the same as well.
To double check that my code is not generating the error I have lonched 'SQL Query Analizer' and executed the folowing code on my database: NOTE: In my database I have create the user “teeluk12� and a role “members�
I have created a SQL Server Project with table-valued function. I compile and deploy the project to SQL server successfully. I did some testing by calling the function and encounters no error.Then, I decided to add another function which is scalar function. Again, I compile the project and deploy it to SQL server without any problem. Now, when I try to call the second function I created, I encounter some error during execution. But calling the first function, the error did not occur; it execute without any problem. Please help and tell me what should I do to resolve it. Below is the message return after calling the second function.
Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'dgaUDF_GetUserStatus': System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) System.IO.FileLoadException: at System.Diagnostics.Switch.InitializeWithStatus() at System.Diagnostics.Switch.get_SwitchSetting() at System.Diagnostics.TraceSwitch.get_Level() at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp) at System.Data.SqlClient.SqlPerformanceCounters..ctor() at System.Data.SqlClient.SqlPerformanceCounters..cctor() System.TypeInitializationException: at System.Data.SqlClient.SqlConnectionFactory..ctor() at System.Data.SqlClient.SqlConnectionFactory..cctor() System.TypeInitializationException: at System.Data.SqlClient.SqlConnection..cctor() System.TypeInitializationException: at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at UserDefinedFunctions.dgaUDF_GetUserStatus(SqlString userid, SqlDateTime workdate)
I am trying to execute a store procedure from ASP/VB but it fails with the message: Incorrect syntax near 'InitProject'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'InitProject'.Source Error:
Line 24: cmd.Parameters("@ProjectId").Value = 3 Line 25: cn.Open() Line 26: cmd.ExecuteNonQuery() Line 27: cn.Close() Line 28: End Sub Here is my code: 'Execute the InitProject stored procedure 'Create the connection from the string in the web.config file Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString) 'I want to execute InitProject stored procedure Dim cmd As SqlCommand = New SqlCommand("InitProject", cn) 'With the parameter @ProjectId = 3 cmd.Parameters.Add(New SqlParameter("@ProjectId", Data.SqlDbType.Int)) cmd.Parameters("@ProjectId").Direction = Data.ParameterDirection.Input cmd.Parameters("@ProjectId").Value = 3 cn.Open() 'But this fails cmd.ExecuteNonQuery() cn.Close() And my stored procedure is defined as: [dbo].[InitProject] @ProjectId int -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereinsert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )select @ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID from MATERIAL_TEMPLATEEND The store procedure works fine when I do exec InitProject 3 in sql query.
I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.However, when I try to run this in the ASP.Net application, error occured.This error message is {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."} Calling the SP via a Class1 Public Function ExecuteStoredProcedure(ByVal Name As String, ByVal Para() As String, ByVal Value() As String) 2 If Para.Length <> Value.Length Then Exit Function 3 SqlCmd = New SqlCommand 4 SqlCmd.Connection = SqlConn 5 SqlCmd.CommandText = Name 6 SqlCmd.CommandType = CommandType.StoredProcedure 7 For i As Integer = 0 To Para.Length - 1 8 'SqlCmd.Parameters.AddWithValue(Para(i), Value(i)) 9 Dim p As New SqlParameter(Para(i), SqlDbType.NVarChar) 10 p.Direction = ParameterDirection.Input 11 p.Value = Value(i) 12 SqlCmd.Parameters.Add(p) 13 Next 14 SqlCmd.ExecuteNonQuery() 15 End Function The Code behind file 1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 If txtFromDate.Text = "" OrElse txtToDate.Text = "" Then 3 trMsg.Visible = True 4 Exit Sub 5 End If 6 7 Dim ts As New Thread(AddressOf GenerateReport) 8 ts.Start() 9 10 MultiView1.ActiveViewIndex = 1 11 lblConfirmationMessage.Text = "Report generating in progress." & vbNewLine & "You can continue using other functions." 12 End Sub 13 14 Protected Sub GenerateReport() 15 Dim userName As String = CType(Session("User"), clsUser).UserName 16 Dim from As String = txtFromDate.Text 17 18 Dim conn As New clsConnector 19 conn.OpenConnection() 20 'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';") 21 conn.ExecuteStoredProcedure("gen_report", _ 22 New String() {"@name", "@remark", "@by", "@fromdate", "@todate"}, _ 23 New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text}) 24 conn.CloseConnection() 25 End Sub 26 Thanks!
I'm having trouble with the select below. The problem is in the WHERE clause. The Query Builder says this: "Query Definitions Differ. Error in WHERE clause near '('. Unable to parse query text."Here is my code: SELECT Members.MemberID, MemberFamily.FamilyID, MemberFamily.FamFirstName, MemberFamily.FamLastName, MemberFamily.Relation, Members.Street, CASE WHEN MemberFamily.Phone IS NULL THEN (SELECT TOP 1 Phone.Phone FROM Phone WHERE Phone.MemberID = MemberFamily.MemberID) ELSE MemberFamily.Phone END AS Phone, CASE WHEN MemberFamily.Email IS NULL THEN (SELECT TOP 1 Email.Email FROM Email WHERE Email.MemberID = MemberFamily.MemberID) ELSE MemberFamily.Email END AS Email, Checkin.DateFROM MemberFamily INNER JOIN Members ON MemberFamily.MemberID = Members.MemberID INNER JOIN Checkin ON MemberFamily.FamilyID = Checkin.FamilyIDWHERE (SELECT * FROM Checkin WHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '02/01/2008' AND '03/01/2008')) AND (NOT (SELECT * FROM Checkin WHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '04/16/2008' AND '04/19/2008'))) I can run it without the WHERE clause and it works fine and I can run these arguments separately and they run without errors also: SELECT *FROM CheckinWHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '02/01/2008' AND '03/01/2008') SELECT *FROM CheckinWHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '04/16/2008' AND '04/19/2008')
I have some very simple SQL statements in stored procedures. How simple? Try this: CREATE PROCEDURE [Insert_Inv_LevelA] AS truncate table BI_Inv_LevelA insert into BI_Inv_LevelA select * from Input_Inv_LevelA
I can run the script fine in Query analyser both remotely and from the server console. When I schedule the script to execute as a step in a job in SQL Server Agent, it falls over and returns the following error (in the step details of the job history): Cannot convert parameter '@handle' to int data type expected by procedure. [SQLSTATE 42000] (Error 214). The step failed.
Every called SQL script returns an identical error, yet they all parse and run fine in the query analyser. The syntax of the step is: sp_execute spInsert_Inv_LevelA This is run as T-SQL on the target database as user dbo (or "(Self)" - the same error occurs either way).
Has anyone come across this before or have any clues? Technet and the MS site say nothing about it that I can find.
I am trying to call an RPC from SQL2KSP3 to Sybase ASE 12.5. SQL2K is on an IBM Intel Server running Win2KSP4, ASE is on an IBM RS6000 w/ AIX 5.2.
I have the ASE server set up as a linked server in SQL 2000, and doing queries is working fine, i.e. I can SELECT * FROM asesrvr.testdb.dbo.tablename and get back all the data in tablename from the testdb database on the ASE server asesrvr.
However, when I try to run an RPC using the same syntax, I get a generic 7212 error (Could not execute procedure 'procname' on remote server 'asesrvr'. I am calling the rpc with DECLARE @output char(1) EXEC asesrvr.testdb.dbo.procname @output output
I'm using Sybase's ASE OLEDB provider; on the Linked Server Properties, I have
General Tab Product name: asesrvr Data source: devprod Provider string: <empty> Location: greyed out Catalog: <empty>
Security Tab One local login, sa, which has impersonate checked as the sa passwords on the two servers are the same (I want to eliminate security as an issue while I try to get this working).
Server Options Tab Collation Compatible: checked Data Access: checked RPC: checked RPC Out: checked Use Remote Collation: unchecked Collation Name: <empty> Connection Timeout: 0 Query Timeout: 0
SQL2K's @@version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any ideas on how I can troubleshoot this further? It does not appear to be limited to this one procedure (this one only has one output parameter and one line of code, so it's about as simple as it can get for testing purposes). Again, queries work fine, so the linked server itself is connected and working, at least for queries.
I have an ASP program calling a stored procedure. Users enter their data, click submit, and the ASP program calls the SP which inserts the data into the SQL table. Initially it did this without checking for duplicate recs but now I have added code to block dup recs in the stored procedure. The SP is blocking the dups but when it returns to the ASP program I get the error:
ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal.
There was no change of code in the ASP code but it is pointing to a line in that code. Not sure what is causing this, Thanks, J.
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle. I have taken one Execute SQL task and set the SQLStatement as call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard! Thanks in advance
I am using SQL Server 2000. I have some files with SQL-Statements.The SQL-Serveragent runs jobs which execute the SQL-Files:(e.g. osql /E /n /i \serverd$lager_pool.sql)How can I implement an error handling.If an error occurs, the script stops, and I can't read the variable@errorMy script - table xy doesn´t existSELECT * FROM XYSELECT @@errorSELECT 33The execution stops with an error after the first lineThanks for your help.aaapaul
I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.
I have tried the following things: Setting integrated windows authentication in IIS Setting the NTFS permissions of those web site directories to EVERYONE Using a credential / proxy in SSIS and running it from SQL Agent Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account
I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo
Also in the security event log it shows all authentication as successful.
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle. I have taken one Execute SQL task and set the SQLStatement as call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard! Thanks in advance.
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."
I've been trying to create an async transform component and one of the things I'd like to do is add a new output and/or columns to existing outputs for each input column that is selected. I thought that the SetUsageType method would be a good place to do this as it will allow me to delete output's and columns based on the user's selections but, despite the fact that my code doesn't actually throw or cause errors, when I return from the SetUsageType method I'm informed that error 0xC0047041 (DTS_E_OBJECTNOTINHASHTABLE) has occurred . I pared the method down to an absolute minimum and found that a call to GetVirtualInput is enough to push it over the edge even if it does nothing! What is wrong here? Thanks Charlie
Sample: public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType) {
I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). It will only work when the Access database is not open. The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009 Source: NewPackage Connection manager "SourceConnectionOLEDB" Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
I am developing an integration solution for MS CRM.
The basic idea is to have a CLR stored procedure that draws data from a SQL database, transforms the data, and then adds it to MS CRM via the webservice.
When executing the stored proc, it randomly fails (although at approximately the same time, everytime).
This is the error message:
Msg 6522, Level 16, State 1, Procedure add_CCU_information, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'add_CCU_information': System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: Only one usage of each socket address (protocol/network address/port) is normally permitted System.Net.Sockets.SocketException: at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) System.Net.WebException: at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at CRM_Integration.CRM_Service.CrmService.Execute(Request Request) at StoredProcedures.add_CCU_information()
If someone could please give some advice, I would really appreciate it.
I have problem executing several SSIS-packages in a chain.
To be more precise: I have implemented a Biztalk 2006 application which via a local webservice executes an SSIS package. The package it self calls another SSIS package, which is located in the same folder as the calling package. The second package then calls a third package etc.
The problem arises, when the first package calls the second package. An Access Denied exception is received. Can any one explain me how to fix this?
The user connected to the application-pool that the weservice is running under, has execution-rights on all of the packages.
This is the first time I've tried creating an "execute sql task" with a "full result set".
I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.
[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anyone else had success with a full result set?
I'm having a problem executing a SQLCLR function: this function calls a web services that processes a query to a data base and returns a table to be used in a stored procedure. In a low concurrency scenario (not to many clients connected), the function returns correctly, however when the concurrency level is increased we have a SQLCLR command execution problem (all the SQLCLR processes hangs), making the server unavailable to all web services processes.
At first we thought the problem could be the SQLCLR, since the web services is 100% available, all the time. We monitored to come to this conclusion. Do you know of some SQLCLR bug?
Could someone help me with this? I'm in a difficult situation with my client, considering that we defended the MS SQLServer technology and now it's not working properly.
I'll keep trying new threads here... sooner or later, I'm sure an expert Microsoft CLR employee will gladly lend a helping hand!
The pieces:
1. SQL 2005, MS Windows Server 2003, Standard Edition, SP 1
2. .NET 2005/C#
3. Instance of SQL 2005 running locally.
Trigger on local SQL2005 DB table INSERT calls 2 CLR Functions:
1. Retrieve data from SQL2005 DB table and populate local DBF through OLEDB
2. Call external 16-bit application (written in Clipper) that iterates through local DBF records (added from step 1 above) and populates DBF on domain resource.
Step 2 detail:
External 16-bit application is called by CreateProcessAsUser after impersonating token.
THIS IS SUCCESSFUL - IF: I populate SQL2005 DB table using TSQL insert statement. The trigger executes, Step 1 and Step 2 execute perfectly!
THIS IS UNSUCCESSFUL - IF: The SQL2005 DB table is populated by synchronizing SQL Mobile Server database from a SQL Mobile Edition 2005 PDA emulation. The trigger executes. Step 1 executes perfectly. Step 2 executes without exceptions. HOWEVER, the 16-bit application does not execute! Remember, no exceptions. In fact, the result variable returns true from function below:
result = ProcessUtility.CreateProcessAsUser(
hDupedToken,
null,
@"C:MobileDBMobile.exe",
ref sa, ref sa,
false, 0, IntPtr.Zero,
@"C:MobileDB", ref si, ref pi
);
Also, if I Right-click on the Step 2 function in the Server Explorer and click "Execute", it works perfectly. Domain DBF is updated successfully.
So, in short CLR "CreateProcessAsUser" is not doing it's job when the trigger is fired after SQL 2005 DB is populated via replication/sychronization. I would appreciate a response... Thx!
When I call ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) I am getting the following error when the data type is Numeric(18, 4): Expression evaluation caused an overflow. [ Name of function (if known) = ]
The numbers involved are not that big and work fine when ExecuteReader() or ExecuteResultSet(SqlServerCe.ResultSetOptions.None) are called on the same SQL.
Any ideas? Thanks in advance!
Cheers, Dave
Code: Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim errorDescription As String = String.Empty Dim numericNumber As String = String.Empty Try Using sqlCE As New System.Data.SqlServerCe.SqlCeConnection("Data Source = '" & My.Application.Info.DirectoryPath & "MyDatabase.sdf';")
sqlCE.Open()
Dim sqlCECommand As SqlServerCe.SqlCeCommand = sqlCE.CreateCommand() sqlCECommand.CommandText = "SELECT SUM(MT.TPM_Measure1) AS CurrentAmount FROM BUS_Table MT"
Dim reader As System.Data.IDataReader = Nothing If RadioButton1.Checked Then reader = sqlCECommand.ExecuteReader() 'Works fine ElseIf RadioButton2.Checked Then reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.None) 'Works fine Else reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) 'Causes the error! End If
If reader.Read() Then numericNumber = reader(0).ToString() End If
reader.Close() reader.Dispose() End Using Catch ex As Exception errorDescription = ex.Message Finally Me.lblError.Text = errorDescription Me.lblNumeric.Text = numericNumber End Try End Sub
TPM_Measure1 datatype is Numeric(18,4)
When the above query works the value is: 4053723.6300
My .NET SQL UDF needs do very complex computation on every call regardless on user input. I would be very happy if I could cache this computed data somewhere in SQL Server memory. And then I should not need to recompute this complex information on every UDF call. Is it possible to cache something inside SQL Server from CLR ?
I'm personally in favor of using the SQL CLR where appropriate, although I'm wondering what the negative consequences of enabling SQL CLR might be? Its disabled by default within SQL Server 2005 and most likely 2008, so what was the reason behind this ... beyond the fear of the DBA enabling something he might not himself fully understand.
By using impersonation I am able to push data to network path / old fashioned DBF's. Of course, I must use unsafe. I am unable, however to successfully run a "cmd.exe" process(), even if I populate the Username, Domain and password (with securestring). It authenticates correctly (checked by giving wrong password or bad username). But when it runs (even just a "cmd.exe"), I get an access is denied error...
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in "secret")
password.AppendChar(c);
Process p = new Process();
ProcessStartInfo si = new ProcessStartInfo("cmd.exe");
si.UserName = "MyName";
si.Password = password;
si.Domain = "MySecretDomain";
si.UseShellExecute = false;
try
{
p.StartInfo = si;
p.Start();
}
catch (Exception ex)
{
//handle the exception here (This exception handler will not handle the exception, but I get a
//Window popup (While executing my CLR!!!)
}
finally
{
sw.Close();
}
The message popup says: The application failed to initialize poperly (0xc0000142). Click on OK to terminate the application. I'm kind of at a loss...
I'm using a Windows 2003 server box with the latest SQL Server 2005, .NET 2.0/2005. Let me know if you need anything else.
Oh, just FYI - I am moving replicated data from SQL2005 server to a legacy app using DBF (FoxPro driver). I really need an external DOS app to process some data for the DOS application (Clipper).
I am searching for a way of using SQLCLR to do Bulk Insert/Copy within SQL Server 2005. I am not permit to use SQL command BULK INSERT with any of text based file csv, or xml etc. I did tried to use SqlBulkCopy within SQLCLR but failed, the context connection was not allowed, I did also use normal connection string with sa & pwd but no luck.
I understand that I can move bulk insert to a service such as windows, web or WCF but it is not on the card at present. Is there a way of doing this within CLR, size of bulk is fair about 2000+ rows.
If you have solution, sample or link would be appriciated.
