Can SQL Server Make A call to a Windows Service?

I have an application which needs to be able to use our local SQL server
database to get data (go figure, what else would it do right?). When the
data being sought is NOT in the database I need to be able to connect to a
MySQL database which is located... oh maybe 1500km. from here.
I use a Windows Service to do some time based updates from this remote site
already. I would like to be able to have the process work like this:
SQL server does lookup.
Data does not exist.
SQL server calls Windows Service to update database.
Windows Service Updates tables and makes data available.
Can I do this?
Does it matter that the windows service is running on .Net?
The firewall rules dictate that we can get data from them, but they cannot
push data to us. Since the transfer medium is the Internet I don't want the
SQL server to talk directly to the remote server. (I want to keep that
access closed).
Thanks.Am Wed, 16 Nov 2005 11:10:44 -0500 schrieb Roger Twomey:

> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote sit
e
> already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
.. till here no problems, You can do all this

> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want th
e
> SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
You are still not complete safe. Because somebody could capture the data
transfered from MySQL to SQL-Server, change it and send it to SQL-Server.
The only safe way would be VPN or SSL.
bye,
Helmut|||If you're using SQL2005 you can create a .net stored proc to call the
windows service.
If you're using SQL2000 you'll need to create a COM wrapper for the .NET
call.
research into: sp_OACreate, sp_OADestroy, sp_OAMethod, sp_OAGetProperty.
etc.
Thanks,
- Becky
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:uuQXkis6FHA.4076@.tk2msftngp13.phx.gbl...
> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote
site
> already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want
the
> SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
>|||IIS allows free https setup. Do you think that would make this person
secure?

> Am Wed, 16 Nov 2005 11:10:44 -0500 schrieb Roger Twomey:
>
> .. till here no problems, You can do all this
>
> You are still not complete safe. Because somebody could capture the data
> transfered from MySQL to SQL-Server, change it and send it to SQL-Server.
> The only safe way would be VPN or SSL.
> bye,
> Helmut
new|||I don't know if you are a C++ programmer. If you are then you'll want to
research into extended stored procedures. We use this for WinInet/Winsock
communications as well as http/ftp and many other uses. Very powerful, very
fast, very dangerous if you don't know what you are doing.

> I have an application which needs to be able to use our local SQL server
> database to get data (go figure, what else would it do right?). When the
> data being sought is NOT in the database I need to be able to connect to a
> MySQL database which is located... oh maybe 1500km. from here.
> I use a Windows Service to do some time based updates from this remote
> site already. I would like to be able to have the process work like this:
> SQL server does lookup.
> Data does not exist.
> SQL server calls Windows Service to update database.
> Windows Service Updates tables and makes data available.
> Can I do this?
> Does it matter that the windows service is running on .Net?
> The firewall rules dictate that we can get data from them, but they cannot
> push data to us. Since the transfer medium is the Internet I don't want
> the SQL server to talk directly to the remote server. (I want to keep that
> access closed).
> Thanks.
new|||We don't have IIS on our SQL servers. We try to keep each server to a single
purpose, then secure it as much as we can.
"beginthreadex" <beginthreadex@.hotmail.com> wrote in message
news:OK0wyOt6FHA.2600@.tk2msftngp13.phx.gbl...
> IIS allows free https setup. Do you think that would make this person
> secure?
>
> --
> new|||I most definately am NOT a c++ programmer. I use vb.Net primarily.
Maybe some day I will get into C++, but only if there is a need. My brain is
pretty much full already!
;)
"beginthreadex" <beginthreadex@.hotmail.com> wrote in message
news:%23TKEtPt6FHA.2600@.tk2msftngp13.phx.gbl...
>I don't know if you are a C++ programmer. If you are then you'll want to
> research into extended stored procedures. We use this for WinInet/Winsock
> communications as well as http/ftp and many other uses. Very powerful,
> very
> fast, very dangerous if you don't know what you are doing.
>
> --
> new|||Thanks for the suggestion. I think your answer sounds the most promising,
and the most re-useable. There could be many other uses for the knowlege
once I have it.
Thanks.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437b60ce$0$138$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you're using SQL2005 you can create a .net stored proc to call the
> windows service.
>
> If you're using SQL2000 you'll need to create a COM wrapper for the .NET
> call.
> research into: sp_OACreate, sp_OADestroy, sp_OAMethod,
> sp_OAGetProperty.
> etc.
>
> Thanks,
> - Becky
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:uuQXkis6FHA.4076@.tk2msftngp13.phx.gbl...
> site
> the
>