Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
Tarvirdi
answered in microsoft.public.sqlserver.programming
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:eiDuCG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
Showing posts with label dll. Show all posts
Showing posts with label dll. Show all posts
Sunday, March 11, 2012
can define UDF in DLL
Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>
Sunday, February 12, 2012
calling web service in stored procedure (SQL 2005)
I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...
by the way: I created serialization file with sgen tool.
the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:
CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies
thus I did it in unsafe mode (both of them). than I created the procedure.
Now, I tried to call that procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>
--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at ConsoleApplication11.Program.Main()
That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method
I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error
my code in C#:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
namespace ConsoleApplication11
{
public class Program
{
public static void Main()
{
const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
string baseUrl = "http://localhost:22278/projectserver/";
CookieContainer cookies = new CookieContainer();
LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();
loginWindows.Url = baseUrl + LOGINWINDOWS;
loginWindows.Credentials = CredentialCache.DefaultCredentials;
loginWindows.Login();
ProjectWebSvc.Project project = new ProjectWebSvc.Project();
project.Credentials = loginWindows.Credentials;
project.Url = baseUrl + "_vti_bin/psi/project.asmx";
ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();
Guid projectGuid = Guid.NewGuid();
projectRow.PROJ_UID = projectGuid;
projectRow.PROJ_TYPE = 0;
projectRow.PROJ_NAME = "Jakis";
projectRow.PROJ_SESSION_UID = Guid.NewGuid();
dsProject.Project.AddProjectRow(projectRow);
Guid jobGuid = Guid.NewGuid();
project.QueueCreateProject(jobGuid, dsProject, false);
System.Threading.Thread.Sleep(5000);
loginWindows.Logoff();project.Credentials = null;
}
}
}
any help will be appreciated. my email is: stro.na@.interia.pl
Dear fcjh,
I too am trying to call a web service from a stored procedure in SQL Server 2005. I have followed the advice in the following very useful article:
http://www.awprofessional.com/articles/article.asp?p=473457&seqNum=10&rl=1
I have built a solution in Visual Studio 2005 to do this. In order to follow the article as closely as possible, I have three projects in the solution:
1) The web site including the web service.
2) A SQL Server Project for the Stored Procedure, which calls a User Defined Function, defined in the same project. This successfully deploys both to SQL Server 2005 and allows me to step into them to debug. The code of the User Defined Function (from the above article) is:
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(Precision = 9, Scale = 2)]
public static decimal GetStockWS(string symbol)
{
Decimal price;
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
StockService s = new StockService();
// use the current credentials
s.Credentials =
System.Net.CredentialCache.DefaultNetworkCredentials;
price = s.GetStockPrice(symbol);
c.Undo();
}
return price;
}
3) A SQL Server Project for the Web Service Proxy. This also appears to deploy to SQL Server successfully.
However, at the point where the User Defined Function invokes the constructor of the Web Service Proxy (statement StockService s = new StockService(); above), I get an exception as follows:
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
I can't find anything useful about the cause of this. Has anyone else succeeded in calling a web service from SQL Server 2005?
Any help would be much appreciated.
|||Hi there,You might find Vineet's post here useful.
Cheers,|||
Dear Isaac,
Vineet's post is excellent. Everything now works perfectly and turns out to be very simple, thanks to the power of the Visual Studio 2005 SQL Server Project type and the use of a Web Reference.
Thanks very much for pointing me to that article.
Friday, February 10, 2012
Calling VB dll method: sp_OAMethod Error
I have VB dll with a few methods that I need to access from my database.
Here's the code w/in sql server:
DECLARE @.retVal INT
DECLARE @.comHandle INT
DECLARE @.errorSource VARCHAR(8000)
DECLARE @.errorDescription VARCHAR(8000)
DECLARE @.retString VARCHAR(180)
--
--INSTATIATE OBJECT--
--
EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle OUTPUT
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
--CALL METHOD--
--
EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
@.param='254616'
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
SELECT @.retString
EXEC sp_OADestroy @.comHandle
I know the object is being instantiated because no errors are returned if I
only exec that piece. However, when I try to call my method, I received the
following error:
Error Source:
ODSOLE Extended Procedure
Description:
Unknown name.
I've verified the method's name (and tried calling other ones).Couple of thoughts:
Did you test your DLL from VBScript using late binding?
Did you check if the value of comHandle non zero or non NULL?
What is the function prototype of the VB function you are calling? Some
constructs are not supported, you are limited IDispatch (COM Automation) at
best and have to substract the SQL XP limitation on top of that, since not
all objects are supported, for example types as Object and Variant are not
understood.
Is your object STA or MTA?
GertD@.SQLDev.Net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>I have VB dll with a few methods that I need to access from my database.
> Here's the code w/in sql server:
> DECLARE @.retVal INT
> DECLARE @.comHandle INT
> DECLARE @.errorSource VARCHAR(8000)
> DECLARE @.errorDescription VARCHAR(8000)
> DECLARE @.retString VARCHAR(180)
> --
> --INSTATIATE OBJECT--
> --
> EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle
> OUTPUT
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> --
> --CALL METHOD--
> --
> EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
> @.param='254616'
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> SELECT @.retString
> EXEC sp_OADestroy @.comHandle
> I know the object is being instantiated because no errors are returned if
> I
> only exec that piece. However, when I try to call my method, I received
> the
> following error:
> Error Source:
> ODSOLE Extended Procedure
> Description:
> Unknown name.
> I've verified the method's name (and tried calling other ones).
>|||I'm already using late binding (set obj = CreatObject) and it works when
called from an ASP page.
The value of column handle is not null. I have no idea what you are
referring to in the next point (function prototype).
The threading model is set to Apartment Threaded.
"Gert E.R. Drapers" wrote:
> Couple of thoughts:
> Did you test your DLL from VBScript using late binding?
> Did you check if the value of comHandle non zero or non NULL?
> What is the function prototype of the VB function you are calling? Some
> constructs are not supported, you are limited IDispatch (COM Automation) a
t
> best and have to substract the SQL XP limitation on top of that, since not
> all objects are supported, for example types as Object and Variant are not
> understood.
> Is your object STA or MTA?
> GertD@.SQLDev.Net
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>
>
Here's the code w/in sql server:
DECLARE @.retVal INT
DECLARE @.comHandle INT
DECLARE @.errorSource VARCHAR(8000)
DECLARE @.errorDescription VARCHAR(8000)
DECLARE @.retString VARCHAR(180)
--
--INSTATIATE OBJECT--
--
EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle OUTPUT
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
--CALL METHOD--
--
EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
@.param='254616'
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
SELECT @.retString
EXEC sp_OADestroy @.comHandle
I know the object is being instantiated because no errors are returned if I
only exec that piece. However, when I try to call my method, I received the
following error:
Error Source:
ODSOLE Extended Procedure
Description:
Unknown name.
I've verified the method's name (and tried calling other ones).Couple of thoughts:
Did you test your DLL from VBScript using late binding?
Did you check if the value of comHandle non zero or non NULL?
What is the function prototype of the VB function you are calling? Some
constructs are not supported, you are limited IDispatch (COM Automation) at
best and have to substract the SQL XP limitation on top of that, since not
all objects are supported, for example types as Object and Variant are not
understood.
Is your object STA or MTA?
GertD@.SQLDev.Net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>I have VB dll with a few methods that I need to access from my database.
> Here's the code w/in sql server:
> DECLARE @.retVal INT
> DECLARE @.comHandle INT
> DECLARE @.errorSource VARCHAR(8000)
> DECLARE @.errorDescription VARCHAR(8000)
> DECLARE @.retString VARCHAR(180)
> --
> --INSTATIATE OBJECT--
> --
> EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle
> OUTPUT
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> --
> --CALL METHOD--
> --
> EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
> @.param='254616'
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> SELECT @.retString
> EXEC sp_OADestroy @.comHandle
> I know the object is being instantiated because no errors are returned if
> I
> only exec that piece. However, when I try to call my method, I received
> the
> following error:
> Error Source:
> ODSOLE Extended Procedure
> Description:
> Unknown name.
> I've verified the method's name (and tried calling other ones).
>|||I'm already using late binding (set obj = CreatObject) and it works when
called from an ASP page.
The value of column handle is not null. I have no idea what you are
referring to in the next point (function prototype).
The threading model is set to Apartment Threaded.
"Gert E.R. Drapers" wrote:
> Couple of thoughts:
> Did you test your DLL from VBScript using late binding?
> Did you check if the value of comHandle non zero or non NULL?
> What is the function prototype of the VB function you are calling? Some
> constructs are not supported, you are limited IDispatch (COM Automation) a
t
> best and have to substract the SQL XP limitation on top of that, since not
> all objects are supported, for example types as Object and Variant are not
> understood.
> Is your object STA or MTA?
> GertD@.SQLDev.Net
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)