Is this possible

Hi,
Is it possible to execute a COM .dll from Enterprize Services hosted on
another server via SQL Server? I know it is possible to execute a COM .dll on
the same server with SQL Server using the sp_OACreate.
Thanks
Well, I suppose you could create a proc on your remote server (with the
sp_OA... statements in it) and then just call that proc through the link
(ie. exec MyRemoteServer.MyRemoteDB.dbo.MyRemoteProc). Assuming you had
the permissions set up correctly then that would be possible. But it
sounds like a fairly dodgey thing to do (I consider executing any
external process, like COM or stuff with xp_cmdshell, from within SQL
code fairly suspect except in pretty rare circumstances). Why would you
want to do this?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Chris wrote:

>Hi,
>Is it possible to execute a COM .dll from Enterprize Services hosted on
>another server via SQL Server? I know it is possible to execute a COM .dll on
>the same server with SQL Server using the sp_OACreate.
>Thanks
>
|||Hi,
I have an app which is used by 2 departments. One dept post damaged orders
to a database and the other fulfills the damaged orders. One order ticket
will have several items. When all the items are picked for an order, a
trigger is fired whenever an order is picked to determine if an order is
complete, the trigger sends a queue to MSMQ and another app picks up the
queue and prints a bill. I had decided on using the xp_cmdshell to execute
an .exe program I created so that the trigger will execute xp_cmdshell which
will execute the program I created and pass the order id as a parameter and
the .exe will send that to MSMQ the only drawback I had was I got the error
A severe error occurred on the current command. The results, if any,
should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
604.
and after looking at some documentation
http://support.microsoft.com/default...283811&sd=tech
and
http://support.microsoft.com/default...NoWebContent=1
I'll have to give extra rights to the calling app user to execute
xp_cmdshell and I am afraid of messing with SQL Server admin security.
my original post can be found here
http://www.microsoft.com/technet/com...f-696d95d48c94
I then tries to create a COM dll and call it using sp_OACreate so the
trigger will execute the dll and pass the ID as a parameter and the dll will
take care of routing the message to MSMQ but further research indicates that
the dll needs to be installed on the SQL Server and will rin in SQL Server
process space. So that's why I wanted to know if I can load my dll component
on our Enterprise Service server and then call the component from that
server.
Thanks
"Mike Hodgson" wrote:

> Well, I suppose you could create a proc on your remote server (with the
> sp_OA... statements in it) and then just call that proc through the link
> (ie. exec MyRemoteServer.MyRemoteDB.dbo.MyRemoteProc). Assuming you had
> the permissions set up correctly then that would be possible. But it
> sounds like a fairly dodgey thing to do (I consider executing any
> external process, like COM or stuff with xp_cmdshell, from within SQL
> code fairly suspect except in pretty rare circumstances). Why would you
> want to do this?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Chris wrote:
>
|||I see. Sorry, I mistakenly thought the remote server would be a SQL
instance.
Couldn't you get your trigger to write your event data into a local
table and then the executable code you've written, that resides on the
remote server, can establish a connection to the SQL server and poll
that event table on a regular basis to look for new events? That way
SQL Server only does what it was intended to do (store & manipulate
data) and the external process is running in its own address space (on a
whole other server even) and is interacting with SQL Server just like
any other well-behaved application does.
At least that would be the general strategy I'd start with if it were
me. IMHO, 9 times out of 10 getting SQL Server to do things it wasn't
intended to do, like launch external processes & execute
non-sqlservr.exe code in the sqlservr.exe address space (like extended
stored procs), is a bad idea.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Chris wrote:
[vbcol=seagreen]
>Hi,
>I have an app which is used by 2 departments. One dept post damaged orders
>to a database and the other fulfills the damaged orders. One order ticket
>will have several items. When all the items are picked for an order, a
>trigger is fired whenever an order is picked to determine if an order is
>complete, the trigger sends a queue to MSMQ and another app picks up the
>queue and prints a bill. I had decided on using the xp_cmdshell to execute
>an .exe program I created so that the trigger will execute xp_cmdshell which
>will execute the program I created and pass the order id as a parameter and
>the .exe will send that to MSMQ the only drawback I had was I got the error
>A severe error occurred on the current command. The results, if any,
>should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
>604.
>and after looking at some documentation
>
>http://support.microsoft.com/default...283811&sd=tech
>and
>http://support.microsoft.com/default...NoWebContent=1
>I'll have to give extra rights to the calling app user to execute
>xp_cmdshell and I am afraid of messing with SQL Server admin security.
>my original post can be found here
>http://www.microsoft.com/technet/com...f-696d95d48c94
>I then tries to create a COM dll and call it using sp_OACreate so the
>trigger will execute the dll and pass the ID as a parameter and the dll will
>take care of routing the message to MSMQ but further research indicates that
>the dll needs to be installed on the SQL Server and will rin in SQL Server
>process space. So that's why I wanted to know if I can load my dll component
>on our Enterprise Service server and then call the component from that
>server.
>Thanks
>
>
>"Mike Hodgson" wrote:
>
|||I guess I'll have to use the table in SQL. You are the second person to
suggest that. I was only afraid of polling SQL too many tomes like ever 5
sec. I taught DTS can handle MSMQ though. I can seem to find any info on that.
Thanks
"Mike Hodgson" wrote:

> I see. Sorry, I mistakenly thought the remote server would be a SQL
> instance.
> Couldn't you get your trigger to write your event data into a local
> table and then the executable code you've written, that resides on the
> remote server, can establish a connection to the SQL server and poll
> that event table on a regular basis to look for new events? That way
> SQL Server only does what it was intended to do (store & manipulate
> data) and the external process is running in its own address space (on a
> whole other server even) and is interacting with SQL Server just like
> any other well-behaved application does.
> At least that would be the general strategy I'd start with if it were
> me. IMHO, 9 times out of 10 getting SQL Server to do things it wasn't
> intended to do, like launch external processes & execute
> non-sqlservr.exe code in the sqlservr.exe address space (like extended
> stored procs), is a bad idea.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Chris wrote:
>
|||One issue I have with using the table is after selecting an id form the table
I have to connect to sql server, after printing, to delete from queue. I was
thinking of using a temptable to store the data after picking up from the
queue table like
Create Procedure GetQueue
AS
Set Nocount on
If Exists(select id form dbo.orders)
Begin
Create Table #tempqueue(
ID INT)
Insert Into #tempqueue
Select id From Queue
Delete From Queue M
INNER JOIN #tempqueue t ON t.ID = M.ID
Select * From #tempQUEUE
drop table #tempqueue
Set Nocount Off
End
If an items are found every 5 sec it will be creating and dropping a temp
table ever 5 sec. Whould that affect performance?
Thanks
"Mike Hodgson" wrote:

> I see. Sorry, I mistakenly thought the remote server would be a SQL
> instance.
> Couldn't you get your trigger to write your event data into a local
> table and then the executable code you've written, that resides on the
> remote server, can establish a connection to the SQL server and poll
> that event table on a regular basis to look for new events? That way
> SQL Server only does what it was intended to do (store & manipulate
> data) and the external process is running in its own address space (on a
> whole other server even) and is interacting with SQL Server just like
> any other well-behaved application does.
> At least that would be the general strategy I'd start with if it were
> me. IMHO, 9 times out of 10 getting SQL Server to do things it wasn't
> intended to do, like launch external processes & execute
> non-sqlservr.exe code in the sqlservr.exe address space (like extended
> stored procs), is a bad idea.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Chris wrote:
>
|||Why bother using a temp table on the server side just to temporarily
store the ID of the order you're printing (and then deleting)? You're
dealing with these orders one at a time on the client side anyway, so
why not just get an order ID from the queue table, do what you have to
do with it on the client side (print it?) and then delete that order ID
from the queue table? Something like...
Server-side:
create proc GetOrderFromQueue
as
select top 1 OrderID from dbo.OrderQueue
order by PickingDate asc
go
create proc RemoveOrderFromQueue (@.OrderID int)
as
delete dbo.OrderQueue
where OrderID = @.OrderID
go
Client-side (pseudo-code, what language are you writing it in? C# with
ADO.NET?):
1) call SQL proc GetOrderFromQueue
2) call my C# printing function passing in the OrderID I just got
from the server
3) call SQL proc RemoveOrderFromQueue (giving it the OrderID we got
from the 1st proc call)
And you could have the client-side code looping continually (based on a
timer elapsing) and if the first step gets no rows from the server skip
the other 2 steps in the loop and go to sleep until the timer elapses
again. No temp table creation, very simple "get, print, delete"
algorithm, minimal activity & locking duration on the server, and easily
implemented in both T-SQL code & client-side (C#?) code. I would think
this could happily support multiple orders per second (2 or 3?) with
fairly low-end hardware for your SQL box, and most of the delay would be
on the client-side when the app's communicating with printer queues I
would think, so the SQL box probably wouldn't get anywhere near that
number of get order/delete order requests. But it would be the kind of
thing you could monitor with Profiler and just tweak the polling
interval in the client app if the load on the SQL box is too much.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Chris wrote:
[vbcol=seagreen]
>One issue I have with using the table is after selecting an id form the table
>I have to connect to sql server, after printing, to delete from queue. I was
>thinking of using a temptable to store the data after picking up from the
>queue table like
>Create Procedure GetQueue
>AS
>Set Nocount on
>If Exists(select id form dbo.orders)
>Begin
>Create Table #tempqueue(
>ID INT)
>
>Insert Into #tempqueue
>Select id From Queue
>Delete From Queue M
>INNER JOIN #tempqueue t ON t.ID = M.ID
>Select * From #tempQUEUE
>drop table #tempqueue
>Set Nocount Off
>End
>If an items are found every 5 sec it will be creating and dropping a temp
>table ever 5 sec. Whould that affect performance?
>Thanks
>"Mike Hodgson" wrote:
>
sql