How to open a socket connection based on an insert TRIGGER?

Hello,

Would it be possible to open a socket connection and send few data based on an insert TRIGGER?
My client would basically like to send the ID of  a product via socket connection when a new product is inserted into the SQL database.
DATABASE:SQL server 8 Database


Thanks for your help
Arnold
arnololo1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher KileCommented:
There aren't any socket I/O primitives in Transact-SQL.  For SQL Server 2000, you'll need to use the OLE interface to access a COM object that handles socket I/O (I THINK WSOCK32.DLL is such an object, but you'll need to research it).  In SS2005, you can link in .NET assemblies, so you would use the NetClient interface, I would expect.
0
arnololo1Author Commented:
Thanks for your answer,  your idea sounds good. Could you give me more detail on how to use the OLE interface to access a COM object that handles socket I/O with SQL. I am pretty familiar with sql server, store procedure, but I have no idea on how to do what you described.
The first thing would be, How do you access the DLL from  a  TRIGGER?

Any sample code would be great.

Thanks
0
Christopher KileCommented:
As it turns out, I can't find any COM DLL's that encapsulate the Windows Socket API.  There may be some out there somewhere, I just don't know where they are.  Your only other option is to write an extended stored procedure in C/C++ (which is the required interface for dealing directly with the WinSock libraries) or to write a COM object yourself (either in C/C++ or, if you're truly brave, in VB6) that will encaspulate the WinSock API.  Do you have any experience with Windows Sockets?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

arnololo1Author Commented:
I have absolutely no experience with Windows Sockets and I have also never written a store procedure in c/c++ .  And I do really not see myself writting a COM object.
So if you have any code regarding the store procedure in C/C++ to do what I need, I would greatly apreciate.

Thanks
0
Christopher KileCommented:
I don't.  My suspicion is that you'll have to go buy it somewhere.  Also, if you have no experience with Sockets, you're gonna have a steep learning curve.  Sockets are no longer in common use because of this, and because other protocols are simply easier to use.

Now, have you ever written an extended stored procedure?  Do you know C/C++?

If your answer to the last two questions is No, you have some serious shopping to do, because it is highly unlikely that you're gonna find what you want for free.  Sorry to be the bearer of bad news, and I hope someone makes a liar of me for your sake, but if I were you I'd be thinking of some way to talk my client out of this.
0
arnololo1Author Commented:
Thanks for thos explanation.
I know C/C++  but not enough to write something to open a socket connection.

As far as buying a  component, that would be fine, but I could not find any.

My client want to use the socket connection because as soon as a new product is entered is the sql database, he needs to print a label from his ERP (QAD). His ERP uses a prelude database, the ODBC driver that exists to connect both database creates a lots of problem of connection. Therefore, the client would like me to open a socket connection to send the ID of the new product to his ERP. On his side he would have an open socket that would only "listen".

If you have any other idea of how we could do that, please let me know.
Thanks
0
Christopher KileCommented:
Does the printing of the label truly need to be instantaneous??  Because if not, it would be MUCH easier to send him a file using DTS which he could import and use to generate the label list.

Failing that, I'd take another look at that ODBC driver, which you could use to do a remote database update.
0
arnololo1Author Commented:
The printing does not need to be instantaneous but at least within few seconds of the insert.
Is it possible to use a DTS within a TRIGGER?
Can the DTS create a text file?
Whe you say send a file using DTS, do you mean FTP the file or is there other way to send the file?

Thanks very much for your help
0
Anthony PerkinsCommented:
>>Is it possible to use a DTS within a TRIGGER?<<
Yes, but not recommended.
>>Can the DTS create a text file? <<
Yes.  Use the FileSystemObject in an ActiveX Script Task.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher KileCommented:
Wait a minute....

acperkins,

is there any impediment to accessing a COM object using the OLE_* procedures within a trigger?

arnololo1,

do you have Visual Studio 2003?  It just occurred to me that the "socket" you want to open isn't called a socket in .NET - it's called a TcpClient.  

Ask your client if you can open a TCP port on his machine and shove through a set of bytes over without any surrounding protocol such as HTTP.  If so, building a COM object in VS 2003 using VB is pretty easy (all it takes is adding a GUID to the assembly definition, creating the DLL, then registering the DLL, while making sure that every public access method uses COM-compatible types).  Your COM object will have one method that accepts your product ID.  It will then create a TcpClient, connect to the host and port of your customer's server, then the TcpClient.GetStream() method will provide a read-write data stream to that host and port.  Write the data, close the stream, close the port, message is sent.

Naturally, the devil is in the details; however, those details can be worked out if you have VS 2003.
0
Anthony PerkinsCommented:
>>is there any impediment to accessing a COM object using the OLE_* procedures within a trigger?<<
There is an impediment to running any time consuming action in a Trigger.  Simply Triggers should not be used for that. Period.

But don't take my word for it:
How can I execute a DTS package from a trigger
http://www.sqldts.com/219.aspx
0
Christopher KileCommented:
>There is an impediment to running any time >consuming action in a Trigger.  Simply Triggers >should not be used for that. Period.

Very true.

arnololo1,

Is the insertion of a new Product ID being done in a stored procedure?  If so, you should move the transmission of the ProductID to your customer into the stored procedure.  If you're not using a stored procedure, you should consider building one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.