Is this possible? SQL Server 2000 Write times

I created an online application for one of our educational programs. The da
ta is inserted via website entry -> client side validation -> stored procedu
re -> database. Given a table stucture as follows, if 100 or even 1000 peop
le applied at the exact same time using an online application, what is the m
aximum time interval that could separate each record. How long would SQL Se
rver 2000 generally take to insert each record into the database. The probl
em is that each year, only the top 100 students are admitted in the program,
but there is one student who says they applied at 9:00 am and their record
insert time shows 9:59am. There are records before it and after it that sho
ws times like 9:58, 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL
Server 59 minutes to actually write the record if 1000 people hit the datab
ase at the same time? Please help, they want to get counsil involved.
ApplicantID int primary key identity
SSN varchar(11)
FirstName varchar(30)
LastName varchar(50)indexed
Address varchar (50)
City varchar (50)
State char (2)
Zip varchar (11)
Phone varchar (20)
Email varchar (50)
dateEntered smalldatetimeShawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?

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

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.
ThanksThis is a multi-part message in MIME format.
--050400050305070002010307
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--050400050305070002010307
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Chris wrote:
<blockquote cite="midC523C0ED-D38E-4D06-BEC0-224A86E53B4A@.microsoft.com"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--050400050305070002010307--|||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.aspx?scid=kb;en-us;283811&sd=tech
and
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&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/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-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:
> >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
> >
> >
>|||This is a multi-part message in MIME format.
--020606040805080803020309
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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:
>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.aspx?scid=kb;en-us;283811&sd=tech
>and
>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&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/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-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:
>>
>>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
>>
>>
--020606040805080803020309
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I see. Sorry, I mistakenly thought the remote server would be a
SQL instance.<br>
<br>
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.<br>
<br>
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.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Chris wrote:
<blockquote cite="mid9494A9BE-C96D-46AB-AC02-0DD4B05E27F2@.microsoft.com"
type="cite">
<pre wrap="">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
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech</a>">http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech">http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech</a>
and
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1</a>">http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1">http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1</a>
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
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94</a>">http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94">http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94</a>
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:
</pre>
<blockquote type="cite">
<pre wrap="">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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Chris wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--020606040805080803020309--|||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:
> >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.aspx?scid=kb;en-us;283811&sd=tech
> >
> >and
> >
> >http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&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/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-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:
> >>
> >>
> >>
> >>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
> >>
> >>
> >>
> >>
>|||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:
> >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.aspx?scid=kb;en-us;283811&sd=tech
> >
> >and
> >
> >http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&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/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-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:
> >>
> >>
> >>
> >>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
> >>
> >>
> >>
> >>
>|||This is a multi-part message in MIME format.
--090304030609010602050101
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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:
>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:
>>
>>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.aspx?scid=kb;en-us;283811&sd=tech
>>and
>>http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&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/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-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:
>>
>>
>>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
>>
>>
>>
--090304030609010602050101
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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...<br>
</tt><br>
<tt>Server-side:</tt><br>
<blockquote><tt>create proc GetOrderFromQueue</tt><br>
<tt>as</tt><br>
<tt>Â Â Â select top 1 OrderID from dbo.OrderQueue</tt><br>
<tt>Â Â Â order by PickingDate asc</tt><br>
<tt>go</tt><br>
<br>
<tt>create proc RemoveOrderFromQueue (@.OrderID int)</tt><br>
<tt>as</tt><br>
<tt>Â Â Â delete dbo.OrderQueue</tt><br>
<tt>Â Â Â where OrderID = @.OrderID</tt><br>
<tt>go</tt><br>
</blockquote>
<tt>Client-side (pseudo-code, what language are you writing it in? C#
with ADO.NET?):</tt><br>
<blockquote><tt>1) call SQL proc GetOrderFromQueue</tt><br>
<tt>2) call my C# printing function passing in the OrderID I just got
from the server</tt><br>
<tt>3) call SQL proc RemoveOrderFromQueue (giving it the OrderID we
got from the 1st proc call)</tt><br>
</blockquote>
<tt><br>
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.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Chris wrote:
<blockquote cite="mid67C0EC48-5360-4229-80AE-542C23654905@.microsoft.com"
type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Chris wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech</a>">http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech">http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech</a>
and
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1</a>">http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1">http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1</a>
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
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94</a>">http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94">http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94</a>
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:
</pre>
<blockquote type="cite">
<pre wrap="">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: <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
Chris wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--090304030609010602050101--sql

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 o
n
the same server with SQL Server using the sp_OACreate.
ThanksWell, 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/defaul...;283811&sd=tech
and
http://support.microsoft.com/defaul...&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
[url]http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx
?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94[/ur
l]
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 whic
h
>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/defaul...;283811&sd=tech
>and
>http://support.microsoft.com/defaul...&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
>[url]http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.msp
x?dg=microsoft.public.sqlserver.server&mid=1d0c8d8f-76f9-4764-a85f-696d95d48c94[/u
rl]
>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 wil
l
>take care of routing the message to MSMQ but further research indicates tha
t
>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 componen
t
>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 tha
t.
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 tabl
e
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 tab
le
>I have to connect to sql server, after printing, to delete from queue. I wa
s
>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:
>
>

Is this an efficient way to return a comma string

Hi there,
I have created a sp and function that returns amongst other things a
comma seperated string of values via a one to many relationship, the
code works perfectly but i am not sure how to test its performance.. Is
this an efficient way to achieve my solution.. If not any suggestions
how i can improve it.. What are the best ways to check query speed?
MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC
END
GO
---
MY Function:
CREATE FUNCTION fn_GetJobLocations (@.JobID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @.LocList varchar(5000)
SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @.JobID
RETURN @.LocList
END
Any help or guidance much appreciated...First of all, what you have in your UDF is a unsupported construct. It
exploits certain physical behaviours that might seem to work in some cases,
but can fail in a variety of situations. Being undocumented, it can change
between versions, service packs or patches.
Doing this in SQL Server invariably requires some level of looping, either
using a cursor, WHILE loop, recursion etc. In SQL 2005, there are some work
arounds using FOR XML method which in some cases can be complex and error
prone.
A good approach is to retrieve the resultset to the client side and generate
the string you need to create.
Also, just noted that you use sp_ prefix to your procedure which is not at
all recommended, since they are reserved for system procedures and can
affect performance adversely.
Anith|||3rd time tonight i've posted this solution, interesting :).
Anyway, something like this (SQL Server 2005) will do the trick and will
perform blisteringly...
select j.Id, j.Inserted, Title, Reference,
(
select location_name + ',' as [text()]
from Jobs_Locations soi
where soi.Job_ID = t.Job_ID
order by location_name
for xml path( '' ), type
)
from Jobs_Jobs as j
It will give one line per job and concatenating each location seperating
them by commas.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<anthonykallay@.hotmail.com> wrote in message
news:1133804859.768819.33930@.g14g2000cwa.googlegroups.com...
> Hi there,
>
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed?
>
> MY SP:
> CREATE PROCEDURE sp_Jobs_GetJobs
> AS
> BEGIN
> SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
> AS location, salary, summary, logo
> FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
> ORDER BY j.Inserted DESC
>
> END
> GO
> ---
> MY Function:
> CREATE FUNCTION fn_GetJobLocations (@.JobID int)
>
> RETURNS varchar(5000) AS
> BEGIN
> DECLARE @.LocList varchar(5000)
> SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
> FROM Jobs_Locations l inner join List_Locations ll on
> ll.LocationID = l.LocationID
> WHERE l.JobID = @.JobID
> RETURN @.LocList
>
> END
>
> Any help or guidance much appreciated...
>

Is there anyway to do Data Mining model test via SSIS package?

Hi, all here,

I am wondering if there is any kind of ways for us to test data mining models via SSIS package? That'll be quite helpful if there is such a way.

Looking forward to hearing from your guidance and thanks a lot in advance.

With best regards,

Yours sincerely,

I would believe there is, but nothing immediately straightforward. The approach I would take is to use SQL Profiler to capture the query sent to the server when the accuracy chart is created. You could then put that query into SSIS and capture the results. The response to the query is (pretty much) just the coordinates for the chart you see, so they are easy to interpret.

|||

Hi, Jamie, thanks a lot for your guidance.

I was thinking about if there is any possible data flow component in SSIS for data mining models verification straightforward like what they got for 'data mining model training'.

Best regards,

Yours sincerely

Is there any sql query where my tables stored in the database should automatically

Is there any sql query where my tables stored in the database should
automatically come in xlsheet
*** Sent via Developersdex http://www.examnotes.net ***Hi Satish
You can check the following query
http://chanduas.blogspot.com/2005/0...n-database.html
this will list the tables in the database. You can save the result in
the excel sheet
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||swata
You can write a simple VB program to declare SQL-DMO objects library and
script all objets to the file.
Dim oSS
Dim oDb
Dim oT
Set oSS = CreateObject("SQLDMO.SQLServer")
Set oDb = CreateObject("SQLDMO.Database")
Set oT = CreateObject("SQLDMO.Transfer")
oSS.Connect "SERVER", "User", "Pass"
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT,2,"C:\pubs.sql"
Set oT = Nothing
Set oDB = Nothing
Set oSS = Nothing
"swata sathish" <deepa_teenu@.yahoo.co.in> wrote in message
news:ufVn3t8pFHA.3664@.TK2MSFTNGP10.phx.gbl...
>
> Is there any sql query where my tables stored in the database should
> automatically come in xlsheet
> *** Sent via Developersdex http://www.examnotes.net ***