| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-03-05 : 18:20:15
|
| Hey all, here's my scenario:All on a single box:Win Nt 4.0 sp 6aMSDE 2000 (no SP's yet, this may be the issue)I have a VB "Server" app that listens on a port on this box. There is a VB COM object that has methods that send data to this port on this box, using TCP and the localhost ip of 127.0.0.1. This COM object is called from a Trigger on a table. The Object Executes just fine (I get HRESULT of 0), but my VB "Server" app never recieves any connections or anything.Is SQL limited to port 1433 for communications? Is that why the Server App never gets the connection?Basically what I need to happen is have one app that keeps one instance of an object open, and have a trigger somehow pass data to that Object. I've though of polling the DB, but our current solution does that with a VFP DB, and needless to say it chews up about 50% of the CPU (Dual 800's) all of the time.Any ideas guys?TIAMichaelmichaelp@televox.com |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-05 : 18:35:46
|
| One other thing I forgot to mention. The Object that the Trigger calls, works ok from a stand alone VB EXE. |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-05 : 20:16:34
|
This is probably a really dumb question (hey - I'm famous for them) - but why are you using winsock at all? Aren't you just trying to get data from the Trigger to the app? In that case, why not have the trigger do xp_cmdshell and put all the data onto the command line.I'm probably missing something really obvious - so please don't hit me when you read this..... --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-05 : 20:29:02
|
| You're not alone Rob, I'm not seeing the benefit of his approach either...One thing you can check for however is which service pack you're running on SQL Server. There is a documented bug/bad registry setting that affects how SQL 7.0 instantiates objects. The threading model defaults to the wrong setting, and SP3 fixes it. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-06 : 01:26:10
|
| Two good ideas guys, I've bene looking for how to execute a app via cmd shell. Thx for pointing me to that function RRB.That MAY be a solution. The biggest problem is I have to have one app running that keeps one and only one instance of teh Object open. So I somehow have to communicate with this app. It's possible i can create a VB app that itterates through all the running processes, and find the right one that has my object (i know how to do that). The problem is, how do i talk to that running app once i find it?That's what lead me to the whole daemon / client thing.I think i'm going to explore the xp_cmdshell thing and figure out how to talk to the app once i get there. maybe even jsut change the caption of the form and haev an even tht fires when that happens. Very much a hack, but anything would be better than what we have now.Thanks for the responses guys!Michael |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-06 : 12:35:24
|
| Hey guys, we used xp_cmdshell and created a VB app that did the winsock stuff. Works like a champ!Thanks guys@!!@Michael |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-03-07 : 09:55:17
|
| I'm curious to know how you are able to keep one instance open? Every time you use xp_cmdshell, don't you create a seperate instance of the VB app, connect and disconnect and then the app goes away right? So every time your trigger gets called, it opens the app, connects and disconnects, and then shuts down right? If this is the case, this is a very expensive solution. You should look into your VB app doing a UDP broadcast instead of a TCP/IP connection. However, if you have created only a single instance, I'd like to know how you did it.*************************Just trying to get things done |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-07 : 11:30:49
|
| Royv, this is what we haveServer App Running all the tme on the local PC.Inside of that app. we keep an instance of this Magic Object Open.Client App that is called by the trigger.This app makes a connection (I'm not sure if it's TCP or UDP) to the server app running on the same box. Command line parameters are passed to the client app from te trigger. The Client App reads these params, and sends a message to the Server App. The server App then calls methods on the "Magic Object" and that's it. The Client App dies, and the trigger continues.Michael |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-07 : 18:36:10
|
quote:
Royv, this is what we have
How bizarre - I've just started developing a solution that works almost exactly the same way. V Interested to hear comments from smarter people if they can see problems....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-07 : 18:54:44
|
| Remember, everything in this project was local to one box. We didn't execute anything across the network, just on the loopback adapter.I'm interested if anyone see's any problems with this solution as well. We've done some minor testing with it, and it works perfectly.Michael |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-03-07 : 20:51:16
|
| I guess the real question is how often does the trigger get fired. If it is going to get fired alot, then you are going to run into some issues with speed and stability. But the cool thing here is that with the trigger, you are actually streaming data out of the table real time as it is about to be inserted into the table. I bet this would actually work if you are using a UDP solution, because with UDP you do not have to wait for a connect, you simply stream the data out. And the loopback adapter definetly helps you speed wise. I wonder how this would perform over a network....*************************Just trying to get things done |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-08 : 02:01:01
|
| The trigger might fire once a minute worst case. i think since we are using 127.0.0.1 as our target address, I think it's pretty quick when connecting. Currently, it fires so fast, it's basically instant./me pats teammate and self on backWe do good work :)As far as performance over the network, UDP would be better, and I imagine it would be rather slow since it's my understanding that xp_cmdShell halts execution until the app it fires returns. it's would still be pretty darn fast, but if you had to call this a bunch of times very often, this would no be a very good solution.Michael |
|
|
|
|
SQL and WinSock