Calling SqlDataSource from DLL instead of CodeFile C#.

Hello,

Is it possible to call a SqlDataSource from a DLL within a method like you can from the CodeFile? I'm getting this error: The Name SqlDataSource1 does not exist in the current context when trying to call it from within a method defined in a DLL. Thanks!

It would be much much better if you can post your code here.

The Name SqlDataSource1 does not exist in the current context

Most probably you didn't declare this parameter before you refer to it.

Is it possible to call a SqlDataSource from a DLL within a method like you can from the CodeFile?

Well, i think it totally depends on whethere you have passed that sqldatasource value to your dll.If yes, you can call that sqldatasource within your dll.

BTW, remember to add necessary dlls and using proper namespaces in your dll, thus sqldatasource class may not be identified.

Hope my suggestion helps

Calling sp_oa* in function

I'm faced with a situation where I will need to calculate a column for
a resultset by calling a component written as a VB6 DLL, passing
parameters from the resultset to the component and setting (or
updating) a column with the result. I thought that perhaps the best
way out would be to create a UDF that passes the parameters to the VB
component using the sp_oa* OLE stored procs.

For a test, I created an ActiveX DLL in VB6 (TestDLL) with some
properties and methods. I then created a function that creates the
object, sets the required properties and returns a result. I use
sp_oaDestroy at the end of the function to remove the object
reference. The function seems to work surprisingly well except for a
small problem; when I use the function to calculate a column for a
resultset with more that one row, the DLL appears to stay locked up
("the file is being used by another person or program"). This leaves
me with the impression that the object reference is not being
destroyed. I have to stop/restart the SQL Server in order to free the
DLL.

Question:
Is the UDF approach the best way? I don't like the idea of creating
and destroying the object at every pass which is what the UDF does.
As an alternative, I suppose that I could have a single SP where I
create the OLE object once, loop through the result set with a cursor
and do my processing/updating, then close the OLE object. I must say
that I'm not too fond of that approach either.

Thanks for your help,

Bill E.
Hollywood, FL

(code is below)
___________________________
--Test the function
Create Table #TestTable(Field1 int)
INSERT INTO #TestTable VALUES (1)
INSERT INTO #TestTable VALUES (2)
SELECT Field1, dbo.fnTest(Field1,4) AS CalcCol
FROM #TestTable
Drop Table #TestTable

___________________________

CREATE FUNCTION dbo.fnTest
/*
This function calls a VB DLL

*/
(
--input variables
@.intValue1 smallint,
@.intValue2 smallint
)
RETURNS integer
AS
BEGIN
--Define the return variable and the counter
Declare @.intReturnValue smallint
Set @.intReturnValue=0

--Define other variables
Declare @.intObject int
Declare @.intResult int
Declare @.intError int

Set @.intError=0

If @.intError = 0
exec @.intError=sp_oaCreate 'TestDLL.Convert', @.intObject OUTPUT
If @.intError = 0
exec @.intError = sp_OASetProperty @.intObject,'Input1', @.intValue1
If @.intError = 0
exec @.intError = sp_OASetProperty @.intObject,'Input2', @.intValue2
If @.intError = 0
exec @.intError = sp_oamethod @.intObject, 'Multiply'
If @.intError = 0
exec @.intError = sp_oagetproperty @.intObject,'Output',
@.intReturnValue Output
If @.intError = 0
exec @.intError = sp_oadestroy @.intObject
RETURN @.intReturnValue

ENDBill Ehrreich (billmiami2@.netscape.net) writes:
> Question:
> Is the UDF approach the best way? I don't like the idea of creating
> and destroying the object at every pass which is what the UDF does.
> As an alternative, I suppose that I could have a single SP where I
> create the OLE object once, loop through the result set with a cursor
> and do my processing/updating, then close the OLE object. I must say
> that I'm not too fond of that approach either.

While the UDF may give you slicker SQL code, I would definitely recommend
the stored-procedure approach, as this appears to be a lot more effective.
After all, using a scalar UDF in a set-based query, more or less converts
it to a cursor behind the scenes, so the difference is not that large.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

I have found UDFs to be very appealing in that I can seemingly package
functionality into a nice reusable box--a little like what we all
strive to do in object oriented programming. However, I suppose that
I should be much more conservative in using UDFs.

Do you have any good tips (guidelines) for when and when not to use a
scalar-valued UDF or table-valued UDF?

Also, while you say that the calculation of the UDF is a bit like a
cursor behind the scenes (which makes sense), how is it different from
an expression (calculated colummn) calculated on one or more columns
of a query? For example,

SELECT Column1, Column2, Column1*Column2+3 AS CalculatedColumn
FROM tbl1

or even

SELECT Column1, Column2, CASE WHEN Column1>20 THEN 1 ELSE 0 END AS
CalculatedColumn
FROM tbl1

How does the SQL Engine process this logic?

Thanks,

Bill|||Bill Ehrreich (billmiami2@.netscape.net) writes:
> I have found UDFs to be very appealing in that I can seemingly package
> functionality into a nice reusable box--a little like what we all
> strive to do in object oriented programming. However, I suppose that
> I should be much more conservative in using UDFs.
> Do you have any good tips (guidelines) for when and when not to use a
> scalar-valued UDF or table-valued UDF?

First of all, the performance problem is *only* with scalar-value
functions.

Table-valued functions are of two kinds. Inline functions are in fact
not really functions at all, but macros. That is, the optimizer will
consider the expanded query and may recast computation order. (As long
as it does not affect the final result of course). A multi-step function
is like first loading a temp table, and then use that temp table in a
query. (Except that there is no statistics, so the optimizer will have
to guess.)

But scalar functions can really wreck performance. The one guideline I
have is simple: benchmark!

Generally, if you have

SELECT col1, col2, dho.udf(col3, col4)
FROM tbl
JOIN tbl2 ...

and there are two million rows in each table, but the query only hits
10 rows, then the UDF is not likely to be a problem. But if you have:

SELECT *
FROM tbl
WHERE dbo.udf(col1) = @.value

not only do you get the cost of a table scan, but the query also gets
serialized.

> Also, while you say that the calculation of the UDF is a bit like a
> cursor behind the scenes (which makes sense), how is it different from
> an expression (calculated colummn) calculated on one or more columns
> of a query? For example,

In the latter case, SQL Server does not have to build a call stack and
all that.

Again, the best way to compare is to benchmark.

The good news is that in SQL 2005, Microsoft has addressed several of
these issues, and the cost of a UDF is not as severe there. In fact for
a complex expression, a UDF in written a CLR language may be faster than
the corresponding expression using built-in T-SQL functions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

I did a little test case in my query designer on a table with just
under 300,000 rows as follows:

--Scenario A - Query with calculated column--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS CalcColumn
FROM TestAssignment

--Scenario B - Query with calculated column as criterion--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS CalcColumn
FROM TestAssignment
WHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1

--Scenario C - Query using scalar UDF--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment

--Scenario D - Query using scalar UDF as crierion--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment
WHERE dbo.fnIsEven(TestAssignmentID)=1

ALTER FUNCTION dbo.fnIsEven
(
@.intValue int
)
RETURNS bit
AS
BEGIN
Declare @.bitReturnValue bit

If @.intValue % 2 = 0
Set @.bitReturnValue=1
Else
Set @.bitReturnValue=0
RETURN @.bitReturnValue
END

--Scenario E - Cursor but no resultset--
Declare @.intCurrentValue int
Declare @.bitIsEven bit
Declare @.crsrTest cursor

Set @.crsrTest = cursor for
SELECT TestAssignmentID FROM TestAssignment

Open @.crsrTest
Fetch Next From @.crsrTest INTO @.intCurrentValue

While @.@.Fetch_Status = 0
Begin
If @.intCurrentValue % 2=0
Set @.bitIsEven=1
Else
Set @.bitIsEven=1

Fetch Next From @.crsrTest INTO @.intCurrentValue
End

Close @.crsrTest
Deallocate @.crsrTest

--Scenario F - Cursor with resultset, 30,000 out of 295,310 rows
set nocount on
Declare @.intCurrentValue int
Declare @.crsrTest cursor

SELECT TestAssignmentID, Null AS CalcColumn
INTO #Temp
FROM TestAssignment
WHERE TestAssignmentID<30000

Set @.crsrTest = cursor for
SELECT TestAssignmentID
FROM #Temp

Open @.crsrTest
Fetch Next From @.crsrTest INTO @.intCurrentValue

While @.@.Fetch_Status = 0
Begin
If @.intCurrentValue % 2=0
UPDATE #Temp SET CalcColumn=1 WHERE
TestAssignmentID=@.intCurrentValue
Else
UPDATE #Temp SET CalcColumn=0 WHERE
TestAssignmentID=@.intCurrentValue

Fetch Next From @.crsrTest INTO @.intCurrentValue
End

Close @.crsrTest
Deallocate @.crsrTest

SELECT * FROM #Temp
DROP TABLE #Temp

--Results--
Scenario Time(ms)
A 1608
B 940
C 4091
D 4535
E 8773
F 52946

Note that the column TestAssignmentID is an integer type.

The scalar UDF was significantly slower than the calculated column,
but it didn't seem to move as slowly as the cursor in E. Using the
UDF in the WHERE clause increased processing time, but not as badly as
I was expecting. In contrast, using the calculated column in the
WHERE clause decreased processing time.

In F, I was trying to get a resultset with the cursor in the fastest
possible way (maybe you could suggest a faster way?) but that took
almost a minute for only 30,000 rows. Scenarios E and F seem to be
telling me that the cursor itself isn't so bad; it's creating a
resultset with successive INSERT or UPDATE statements that is by far
the most costly.

Is this a reasonable test? Did you see what you expected to see?

Bill|||Bill Ehrreich (billmiami2@.netscape.net) writes:
> The scalar UDF was significantly slower than the calculated column,
> but it didn't seem to move as slowly as the cursor in E. Using the
> UDF in the WHERE clause increased processing time, but not as badly as
> I was expecting. In contrast, using the calculated column in the
> WHERE clause decreased processing time.
> In F, I was trying to get a resultset with the cursor in the fastest
> possible way (maybe you could suggest a faster way?) but that took
> almost a minute for only 30,000 rows. Scenarios E and F seem to be
> telling me that the cursor itself isn't so bad; it's creating a
> resultset with successive INSERT or UPDATE statements that is by far
> the most costly.
> Is this a reasonable test? Did you see what you expected to see?

Yes, this is a good test. The one thing I would have done different, is
that I would have made the cursor INSENSITIVE (and I would not have used
a cursor variable). INSENSITIVE is not likely to have any signficant impact,
but I always go with INSENSITIVE, since the default keyset-driven cursors
have sometimes given me completely horrible query plans. (In SQL 6.5, but
I'm not taking a chance that things have changed.)

Yes, the times are about what I would expect.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

I never use a cursor if I can help it--I've been trained to avoid them.
I would normally use a SELECT loop as in

--
Declare @.intCurrentValue int
Declare @.bitIsEven bit

Set @.intCurrentValue=0

While @.intCurrentValue Is Not Null
Begin
SELECT @.intCurrentValue=Min(TestAssignmentID) FROM TestAssignment
WHERE TestAssignmentID>@.intCurrentValue

If @.intCurrentValue % 2=0
Set @.bitIsEven=1
Else
Set @.bitIsEven=1

End
--
However, the execution time for this loop turns out to be very close to
the execution time for my cursor loop in Scenario E so perhaps I
shouldn't be so afraid of using cursors.

Bill|||(billmiami2@.netscape.net) writes:
> I never use a cursor if I can help it--I've been trained to avoid them.

Good!

> I would normally use a SELECT loop as in

But you didn't learn the lesson!

The reason that you should avoid cursors is that you foremost look for
a set-based solution.

But once you need to iterate, the cursor is probably the best way. Some
of my colleagues appear to prefer a "poor man's cursor" like in your
example. If there is an index on your control column, the difference to
a cursor may not be significant. But if you do this on an indexless
temp table with tens of thousands of rows, the penalty is severe. A cursor
sets up the iteration once, so for a cursor the index does not matter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But you didn't learn the lesson!
> The reason that you should avoid cursors is that you foremost look
for
> a set-based solution.
> But once you need to iterate, the cursor is probably the best way.
Some
> of my colleagues appear to prefer a "poor man's cursor" like in your
> example. If there is an index on your control column, the difference
to
> a cursor may not be significant. But if you do this on an indexless
> temp table with tens of thousands of rows, the penalty is severe. A
cursor
> sets up the iteration once, so for a cursor the index does not
matter.

In fact, I did learn the lesson, Erland and yes, I can see clearly that
the set based solution is the answer if at all possible. The
experiment and this discussion has been very enlightening.

Bill

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
msftngp13.phx.gbl" target="_blank">http://groups.google.com/groups?hl=...ftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/defaul...kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/defaul...kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u%24dR%233nTEHA.3476%40tk2msftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter
"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.c om...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=e...tngp13.phx.gbl
David
|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default...b;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default...b;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David
|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling non-static methods in dll

Hello,

I have a dll which is developed in C#. I have made a reference to it from Reporting Services under Report Properties.

My problem is, that the methods in this dll are not static. Therefore under the Classes in Report Properties I have written the class-name and the instance-names of the instances that I need to use from this class (as I understand it, this is what you have to do if using non-static methods). In a text-box, I call one of the instances in the class, but I get this error:

[rsInvalidName] 'MyMethod()' is not a valid code class name. Names of objects must be CLS-compliant identifiers.

What exactly have I done wrong here?

Thanks
/Peter

Hi Peter,

In the code window, you'll need something similar to:

Dim InstanceName As Instance

Then you should be able to set the value of your textbox to: =Code.InstanceName.FunctionName(Params).

Keep in mind that all of these lines are case-sensitive.

If this isn't working, could you post a snippet of what you're doing, so we can take a further look at it?

-Jessica

|||

Hi Jessica. Thanks for your reply :-)

I have a class (let's call it myClass) and a method in that class (let's call it myMethod).

The Class is declared as: public class myClass and the method is declared as: public string myMethod(params).

The resulting dll (let's call it myDLL) is added to the references-tab of Report Properties. In the Code-tab I have written (I have tried many combinations): Public Dim class1 As myDLL.myClass. I have used the Public-keyword because it complained that class1 was declared private. In the textbox-controller I have written: =code.class1.myMethod(params). But it doesn't work. The text-box writes: #Error and I get the warning:

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The Value expression for the textbox 'textbox5' contains an error: Object reference not set to an instance of an object.

So it appears that class1 is not an instance of myDLL.myClass. I have tried many combinations and also with and without adding "Class name" and "Instance name" in the References tab of Report Properties.

Any suggestions ?

Thanks

/Peter

|||

Hi again,

I works now. I removed the code in the Code-tab and added the class to the References-tab under "Class name" and "Instance name". I then wrote: =code.InstanceName.MethodName(param) in the text-box and it works. I didn't have the "code" part to begin with, so that was my problem.

Thanks for your input. I appreciate it :-)

/Peter

Calling methods, functions threw Extended SP in a DLL

Hi,

I alredy tried to search this problem in last posts, but I couldn't
find the answer.
I try to access via Extended SP the method in a dll.

I registered the dll as a ExSP, with a name of method. But after
calling it in T-SQL, I became such a error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the
function SendGeneralNotify_FromA in the library [LibraryName.dll].
Reason: 127(error not found).

In this dll I have only one class an it has events, properities and
methods.
I will to call one of these methods.

aha ... very importand. Please don't say that this dll should be
written in c++ because it is made like that (no VB).

Maybe somebody of you have an example how I should call it, to became
an access on this dll?

Sorry for my not well english.

With best regards, looking forward for reply

----------
Matik
marzec@.sauron.xo.plMatik,

> In this dll I have only one class an it has events, properities and
> methods.
> I will to call one of these methods.
> aha ... very importand. Please don't say that this dll should be
> written in c++ because it is made like that (no VB).

If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
want to call it as an extended stored procedure. Unfortunately, VB 6 can't
export a function, which is what an extended stored procedure must do. You
have three choices:

1. Use a different language (C, C++, Delphi, etc): I understand that you
don't want to do this. I don't know if VB.NET will let you export a
function, but if it will that might be an option.

2. Buy a tool from Desaware that enables you to export a function from VB 6.
I don't remember the name and I've never used it, but you should be able to
find it at Desaware.

3. Instead of using the extended stored procedure functionality, use the
sp_OA... stored procedures to instantiate your class and call the method
you want. Check books online or google for examples: sp_OACreate is a good
one to start with an google.

My standard extended sp warning: extended stored procedure are dangerous. I
refuse to allow them where I work because a single errant pointer can cause
untold damage. I know people use them to great effect, but it gives me the
shivers. I feel the same way (but not as strongly) about the sp_OA...
procedures. My real advice is to find another way to do it. And... if
writing an extended sp doesn't make you nervous, you probably shouldn't
write one :)

Craig|||Hej Craig,

> If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
> want to call it as an extended stored procedure. Unfortunately, VB 6 can't
> export a function, which is what an extended stored procedure must do. You
> have three choices:

Maybe I explained it a little nfortunately. One more time:
- this is a object (in dll) which has properities and methodes.
- I must call one of these methods,
- it is written in C++,

If I try to do that via ExSP, it do not works (I have this error as
above alredy written).
If I do that with sp_OA, then It works ... but not like I expected:-)

First, I create the object:

exec @.iRetVal = sp_OACreate '{034188F2-8DBC-4613-829A-76D5279C35A3}',
@.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUTPUT
IF @.iRetVal <> 0
begin
set @.sLog = 'No object created. Source: ' + @.sSource + '
Description: ' + @.sDescription
print @.sLog
end

This is without problems.
Then I try to call this methode:

exec @.iRetVal = sp_OAMethod @.iObject,'SendNotification_FromA',
@.sProperty OUT, @.nMessageNr = 555, @.bstrDateTime = @.dDateEVT,
@.textFromA1 = @.sText1 OUTPUT, @.textFromA2 = @.sText2 OUT
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT

IF @.iRetVal <> 0
begin
set @.sLog = 'Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

IF @.iRetVal <> 0
begin
set @.sLog = 'Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

print @.iRetVal

PRINT @.sProperty
PRINT @.sText1
PRINT @.sText2

... and I became no errors, but the values are the same I set at the
beginning.
On my pc works also the small software, which should capture this
values.
This software recieves the data sended with this method.
I wrote the small VB programm, where I call the same method, with the
same values, and then the second programm reives the data ...

Hopeless?

Thank You for Your cindly reply.

Matik|||Matik,

Please see inline

> Maybe I explained it a little nfortunately. One more time:
> - this is a object (in dll) which has properities and methodes.
> - I must call one of these methods,
> - it is written in C++,

Ah... I see. I apologize for the misunderstanding.

> If I try to do that via ExSP, it do not works (I have this error as
> above alredy written).
> If I do that with sp_OA, then It works ... but not like I expected:-)

If you already have C++ and you really want an ExSP then you can look in SQL
Server Books Online for the topic "Extended Stored Procedure Programming"
under the main heading "Build SQL Server Applications". Basically you would
need to #include a special header or two and export a very specific function
type. But since you already have a COM object...

> First, I create the object:

Remember that the object identifier you're getting is basically a handle to
a COM interface pointer, so you need to treat it like you would any other
resource: if sp_OACreate succeeds, then you need to call sp_OADestroy.

Another gotcha that seems simple but people seem to forget: the COM object
needs to be registered on the SQL Server (if you're instantiating a local
COM Server, which you are here). I watched people nearly strangle a
laughing coworker when they realized that registering a COM component on the
computer running Query Analyzer isn't the same :) I know this isn't your
problem, but if it hasn't bitten you yet, it will...

<whitespace edited>
> exec @.iRetVal = sp_OAMethod
> @.iObject,
> 'SendNotification_FromA',
> @.sProperty OUT,
> @.nMessageNr = 555,
> @.bstrDateTime = @.dDateEVT,
> @.textFromA1 = @.sText1 OUTPUT,
> @.textFromA2 = @.sText2 OUTPUT

OK, I couldn't see what was wrong, so I created a quick COM object (I
cheated and used VB :) that matched what I *think* your COM object looks
like. Here's the IDL:

HRESULT SendNotification_FromA(
[in] long nMessageNr,
[in] BSTR bstrDateTime,
[in, out] BSTR* textFromA1,
[in, out] BSTR* textFromA2,
[out, retval] BSTR* );

This is the important part: you need to make sure that the parameter names
match up in the call to sp_OAMethod and your IDL. Since you're trying to
get output parameters, you also need to make sure that textFromA1 and
textFromA2 are [in,out] and are a datatype that allows that (such as BSTR*).

Also, you need to make sure that you've correctly implemented IDispatch and
that all your parameters are OLE compatible. A good test is to re-write
your VB program to use late-binding. For example (untested and coded in my
newsreader):

Dim obj As Object
Dim s, a1, a2
Set obj = CreateObject("SomeLib.SomeClass")

a1 = "in 1"
a2 = "in 2"
s = obj.SendNotification_FromA(555, "SomeDateTime", a1, a2)

MsgBox s & vbcrlf & a1 & vbcrlf & a2

Set obj = Nothing

Regardless, I got it to work, so I imagine there's something wrong with the
way your COM code is working. After checking your IDL and matching T-SQL
code, I would try stripping the COM method down to as little as possible
(maybe just log out input parms and set the output parms to constants) and
see if you can get that to work.

Good Luck,

Craig

Calling into a C# DLL from T-SQL

Hi:
I have an encryption DLL written in C#. Is there a way I can call into it
for encrypting and decrypting data during a T-SQL query?
Thanks,
CharlieSQL Server 2000? No; there is no supported way.
SQL Server 2005 -- yes. You can create a CLR UDF and wrap the DLL. But in
2005 it would make more sense to use the built-in encryption features that
SQL Server provides.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>|||Hello Adam,

> SQL Server 2000? No; there is no supported way.
Not that I like to disagree with Adam [ :) ] but... could you generate a
COM callable wrapper then write an XP that use that to do this work?
But I do agree, with SQL Server 2005, use the built in stuff instead!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||You -could-, but it is explicitly mentioned as not supported in BOL.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
> Hello Adam,
>
> Not that I like to disagree with Adam [ :) ] but... could you generate a
> COM callable wrapper then write an XP that use that to do this work?
> But I do agree, with SQL Server 2005, use the built in stuff instead!
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>
There is in SQL2005, not in SQL2000|||... and here is my canned response to calling managed code from SQL Server
(not including Yukon,
which is of course a different story):
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsmkX9t6FHA.636@.TK2MSFTNGP10.phx.gbl...
> You -could-, but it is explicitly mentioned as not supported in BOL.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
>

Calling Com dll object from SQL Server

Hello All,

I wonder how this can be accomplished.

We have created a class lib wrapper that imports the com dll. Upon placing the assembly in sql 2005, both the wrapper Dll and the com dll (probably a converted version) gets placed inside the sql 2005 assembly store. Next a CLR sp references the 2 assemblies above, but within the call to the CLR sp things fail with exception:

System.UriFormatException: Invalid URI: The URI is empty

The works fine from a windows forms app.
What is the methodology to invoke a COM Dll? How far are we from implementing this in SQL CLR

Thank you,

LubomirJust an addition. The full exception is:

"System.UriFormatException: Invalid URI: The URI is empty.\r\n at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)\r\n at System.ComponentModel.Design.RuntimeLicenseContext.GetLocalPath(String fileName)\r\n at System.ComponentModel.Design.RuntimeLicenseContext.GetSavedLicenseKey(Type type, Assembly resourceAssembly)\r\n at System.ComponentModel.LicenseManager.LicenseInteropHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)\r\n at HexillionCom.Hexillion..ctor()\r\n at TestHexillionCLR.TestHexillionClass.Test()"

Lubomir

Calling a stored procedure from a dll

When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem?
Thanks...Run PROFILER and see the activity when trying to call from the .DLL.

Also you can add that SP as extended stored procedure by uisng sp_addextendedproc as specified in the books online.

calling a dll method whcih is registered from Sql server 2000 stored procedure

calling a dll method whcih is registered from Sql server 2000 stored
procedureI need to call the method of a dll which is wriiten in vb.net and
registered using regasm, from a asql server stored procedure.|||Seems you should investigate sp_OACreate and the related extended stored pro
cedures (assuming it is
a COM dll).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sara" <saravanankbe@.gmail.com> wrote in message
news:1127201748.583551.42790@.g49g2000cwa.googlegroups.com...
> calling a dll method whcih is registered from Sql server 2000 stored
> procedure
>

calling .net assembly in DTS package

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

Edit

You need full edition like the developer edition because you need to use SQL Server Agent to run xp_cmdshell to run it within your application. Try the thread below for all you need do to get it to work. It works post again if you still have questions. Wrong link originally posted now corrected. Hope this helps.

http://forums.asp.net/thread/1359106.aspx

|||

The article that you told me about is discussing something about nullable values. I was more looking for some active x script that I can call in my DTS package that in turns call my .net dll. I am using sql server 2000 ( full professionsal version) and visual studio.net ( full professional version 2003) .

Thanks.

|||

My apologies I gave you the wrong link and trust me and we will get you up and running.

http://forums.asp.net/thread/1359106.aspx

|||

Hi,

I am not sure if we are talking about same thing. I have a dll from visual studio.net project and I want this dll to be called in my DTS. The link that you told me about, that person is trying to call the DTS package in .net project. Please advise.

Thanks.

|||

DTS(data transformation service) in the data business is a basic ETL(extraction transformation and loading) tool we have created other uses for it like calling it from web application and going anywhere on the enterprise to move data but your dll must be some form of data. So I need to know the content of your dll before I can tell you other options. If you are in the full version of SQL Server 2005 you have a full ETL tool SSIS (sql server integration service) that can do most things expensive ETL tools do. There is a site dedicated for it check it out in the link below.

http://www.sqlis.com/?15

|||

Hi,

My dll is justcreating a simple xml file and calling a web service to send the xml file to that web service. I get a response from the web service to show whether the xml file is accepted or not. I am using sql server 2000. I don't have sql server 2005 version. I looked at one article and it explains about calling the dll in dts package, but I am having lot of problems creating the strong name. Below is the link to the article.

http://www.c-sharpcorner.com/UploadFile/frankalonzo/NetComSqlJob09122005042555AM/NetComSqlJob.aspx?ArticleID=ee58ac62-a9fd-4957-95a6-8586c58a4dd3

When I am following this article, I am getting this error

Assembly generation failed -- Referenced assembly 'Microsoft.ApplicationBlocks.Data' does not have a strong name. Although I created the strong name for my project. I also tried creating the strong name for 'Microsoft.ApplicationBlocks.Data' .

Thanks.

|||You see that the article is not rated because when you get your dll into DTS you will still do all that was covered in my links because Jobs operate under SQL Server Agent context and must have admin or proxy to admin account to run as explained in my links but he did not cover it because until 2005 that was not even covered in the docs. So if you can get you dll into DTS then post again so I can help you call it in your application. Hope this helps.|||

Thanks Caddre. I will let you know, I will work on this today.

|||You know I did not tell that if you get it working it will run as a job if you run the job manually because it is running in the context of you permission but in Asp.net it must run in the context of SQL Server Agent, the automation part of SQL Server.

Callback Function in C#

Hello,
Does anyone know if a C# (or other managed language) DLL can be used as a
callback library for the Callback parameter during setup? I have experience
writing C# applications/libraries but not C++. If it can't be done in C#
then does anyone know of any good C++ templates that I could use as a basis
for creating a callback DLL?
-- Thanks, Jeff
C# would not be an appropriate language for writing a Windows callbackup
function. Actually the best language would be C since it does not mangle the
function names, but C++ will work. The Windows SDK has numerous examples of
how to write a callback function in a DLL. Visual Studio 6 has a wizard for
creating a DLL project and it would be a simple matter of including your
callback declaration and function in the DLL.
Jim
"Jeff B." <jsb@.community.nospam> wrote in message
news:%23Hj9DasJFHA.2980@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Does anyone know if a C# (or other managed language) DLL can be used as a
> callback library for the Callback parameter during setup? I have
> experience writing C# applications/libraries but not C++. If it can't be
> done in C# then does anyone know of any good C++ templates that I could
> use as a basis for creating a callback DLL?
> -- Thanks, Jeff
>

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error "I

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

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 'PositivePay.class', @.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,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error &qu

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

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 'PositivePay.class', @.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,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

call a program (activex exe, dll or something) from a script

Is it possible to do this? I am evaluating a bunch of possible solutions to
a problem. Someone came up with this idea, but could not remember for
certain if it possible.
Thanks.1. Lookup the sp_OAxxxxxx routines in SQL Online Books and it will show you
how to create COM objects and call method and set properties
2. Lookup xp_cmdshell on how to launch processes
3. Write an extended stored proc to have tighter control over what is
happening (inside your proc you can call CreateProcess() API and do many
other things)
All three of these options have security/stability implications. Let me
know if you need more details on a specific option.
Mike
"Stephanie" <IwishICould@.NoWay.com> wrote in message
news:O8qomUBTFHA.2916@.TK2MSFTNGP15.phx.gbl...
> Is it possible to do this? I am evaluating a bunch of possible solutions
to
> a problem. Someone came up with this idea, but could not remember for
> certain if it possible.
> Thanks.
>

Call a DLL or EXE file from SQL Trigger

I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:

---

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

CREATE TRIGGER CallProgIfParentID

ON Toc

FOR INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

-- I want to be able to programmatically change '57660' from an ASPX page

EXEC

-- This is where the call function goes

GO

---

Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?

Thank you in advance

I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:

1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.

I am wondering what kind of scenarios you have, but there got to be a better way to do it :)

Anyway, if you still decide to go with this route, you could call external exes from a trigger by:

- xp_cmdshell '<some exe>'
- For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)

For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.

|||

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'

---

Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called. I can't figure out why it's not working.

|||

I figured out what wasn't working and fixed it. Now I can't get a variable to append to the end of the command line. It keeps telling me the + isn't valid. Does anyone have any ideas?

---

ALTER TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = (SELECT TocId FROM inserted)

EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @.MyTocId

GO

|||Hi,

for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted

EXEC master ..xp_cmdshell @.command = @.MyTocId

Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Call a Dll from Stored Procedure

Can you call a .dll from a stored procedure?
I am looking to use this as a trigger to run some other software.
I have a piece of software that I was going to run as a service when a
record is updated. But I have no way to trigger the service that something
has happened.
For example:
If a record is updated, I want a program to run that will run a web service.
If a stored procedure can call a .dll, that method could call the web
service.
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
Look into extended stored procs. If you run C++ and select new project one
of the project types is extended store proc.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
> Thanks,
> Tom
>
In SQL 2005 you can put .NET code directly in a proc.
In SQL 2000 you can create your own extended proc or use the sp_OA
automation extended procs. Neither option is great and extended procs are
now deprecated. Use 2005 if you can.
Alternativey, you could just have your external code poll the table
regularly and action any changes based on a date-timestamp of new rows.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On second thoughts and in light of Michael's response, my reply deserves a
bit of clarification. When I say "not great" what I mean is that in my
experience the COM interface frequently leaks memory. That's based on
several brushes with the sp_OA procs in 2000. I don't have personal
experience of developing extended procs in C++ so I shouldn't comment on
those beyond what Books Online says about them (below). I have used some
third party XP (xp_smtp_sendmail) and not noticed any particular problem in
that case.
<quote>
Extended Stored Procedures
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use CLR Integration instead.
[...]
Extended stored procedures may produce memory leaks or other problems that
reduce the performance and reliability of the server. You should consider
storing extended stored procedures in an instance of SQL Server that is
separate from the instance that contains the referenced data. You should
also consider using distributed queries to access the database. For more
information, see Distributed Queries.
</quote>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000.
It might be lousy implemenations of the particular COM methods you
have used.
But nevertheless, you were right on target. Writing extended stored
procedures, or calling your own COM methods through sp_OAxxx comes
with big warning signs. Performance is not fantastic, as there is some
context switching. But what is really ugly is that since the DLLs
are in-process, an execution error like an access violation will
crash the entire SQL Server.
On SQL 2005 writing a trigger in a CLR language might be the best choice.
But that depends on how long that web service takes. Triggers should
be swift and quick, since you are in a transaction. Triggers that runs
for several seconds in a busy system is a recipe for diasster.
On SQL 2000 the best is have the DLL to poll, or possibly start a job
with sp_start_job.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My problem is we are not using Sql 2005.
I need to get something to work now. Is the Extended Stored Procedures the
same as Com Interface?
Also, the quote mentions CLR Integration. Is that the same thing as your
"NET code directly in a proc"?
Thanks,
Tom
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ONO%23At7RGHA.2176@.TK2MSFTNGP10.phx.gbl...
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000. I don't have personal
> experience of developing extended procs in C++ so I shouldn't comment on
> those beyond what Books Online says about them (below). I have used some
> third party XP (xp_smtp_sendmail) and not noticed any particular problem
> in that case.
> <quote>
> Extended Stored Procedures
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use CLR Integration instead.
> [...]
> Extended stored procedures may produce memory leaks or other problems that
> reduce the performance and reliability of the server. You should consider
> storing extended stored procedures in an instance of SQL Server that is
> separate from the instance that contains the referenced data. You should
> also consider using distributed queries to access the database. For more
> information, see Distributed Queries.
>
> </quote>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9787723711E9Yazorman@.127.0.0.1...
> But nevertheless, you were right on target. Writing extended stored
> procedures, or calling your own COM methods through sp_OAxxx comes
> with big warning signs. Performance is not fantastic, as there is some
> context switching. But what is really ugly is that since the DLLs
> are in-process, an execution error like an access violation will
> crash the entire SQL Server.
What this is saying (and the MSDN article quoted by David) is that you might
have a bug in one particular technique therefore don't use that technique.
To me this just means greater caution should be used when using said
technique.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:upCIsG8RGHA.4456@.TK2MSFTNGP14.phx.gbl...
> My problem is we are not using Sql 2005.
> I need to get something to work now. Is the Extended Stored Procedures
> the same as Com Interface?
No, they are 2 different techniques. Extended stored proc is where you write
code, usually in C++, to create a dll. You can add functions from the dll
similar to other stored procs. You could use this as a wrapper for your
existing dll. Have a look at, for eg, the xp_subdirs extended stored proc in
the master database, xp_subdirs is a function in xpstar.dll (you can see
this by double clicking the ex stored proc).
Com interface is something different. You can get sql2k to call functions on
an existing com interface. If your dll isn't com then this isn't a lot of
use to you unless you write a com wrapper.

> Also, the quote mentions CLR Integration. Is that the same thing as your
> "NET code directly in a proc"?
This method is not available to you in sql2k
Michael|||How time sensitive are your needs? As erland mentioned earlier,
triggers aren't really designed to be event-handlers; they should be
used for quick responses to data changes (such as low-level
validation). If you change several 1000 rows of data in a second, your
trigger needs to be able to fire that many times.
What is that you're trying to do? It sounds like some form of
event-handling, which should be done at a higher level than your data
source. If your needs are not time sensitive, than you may consider
polling your database periodically to see if your criteria is met, or
you might consider having your application call the web service when it
changes the database.
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1142384900.721926.212080@.p10g2000cwp.googlegroups.com...
> How time sensitive are your needs? As erland mentioned earlier,
> triggers aren't really designed to be event-handlers; they should be
> used for quick responses to data changes (such as low-level
> validation). If you change several 1000 rows of data in a second, your
> trigger needs to be able to fire that many times.
> What is that you're trying to do? It sounds like some form of
> event-handling, which should be done at a higher level than your data
> source. If your needs are not time sensitive, than you may consider
> polling your database periodically to see if your criteria is met, or
> you might consider having your application call the web service when it
> changes the database.
>
That may be the best bet.
I still need to look into the requirements as we are just starting to design
it. But as you say, having the App Call a Web Service might be the best
way.
Thanks,
Tom

> Stu
>