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
>

Can someone solve this?

Hi,
I am trying to create a local report that retrieves the data from a web
service in my business layer application but I can't figure out how to get
this dataset into my report.
Here is what I do:
I have 3 projects in my solution:
Project1.
A class library which contains a typed dataset 'myTypedDataSet'.
Project2.
A web service application with one method that returns 'myTypedDataSet'
Project3.
A windows application that contains one form and one report.
- I open my report in the designer
- Added a new datasource based on my webservice method (and 'myTypedDataSet'
showed up in the data source explorer view)
- I added the fields from 'myDataSet' to my report.
- I created a button with a click event where I call my web service that
returns an object of 'myTypedDataSet'.
Now - how can I assign this dataset to the report? I can't see anything
documented about how to do this.
Kind Regards
ThomasAhh, this is different than I thought you were asking. The thread
2005 SQL Reporting Service XML Data Source (WebService)
talks about using a web service as a source for a server based report. Your
question really has nothing to do with a web service, rather if I understand
you correctly you are wanting to use the Winform control and give it the
dataset. I have just started using this control but so far in server mode. I
know that the way the control works in local mode is you give it the dataset
and the report to use. If no one answers I suggest posting with the subject:
How do you assign dataset for Winform Control
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Andersson" <1qa2ws3ed4rf5tg6yh@.newsgroup.nospam> wrote in message
news:8B4AD63D-6CF0-4BCB-A3E0-0B1EDE447659@.microsoft.com...
> Hi,
> I am trying to create a local report that retrieves the data from a web
> service in my business layer application but I can't figure out how to get
> this dataset into my report.
> Here is what I do:
> I have 3 projects in my solution:
> Project1.
> A class library which contains a typed dataset 'myTypedDataSet'.
> Project2.
> A web service application with one method that returns 'myTypedDataSet'
> Project3.
> A windows application that contains one form and one report.
> - I open my report in the designer
> - Added a new datasource based on my webservice method (and
> 'myTypedDataSet'
> showed up in the data source explorer view)
> - I added the fields from 'myDataSet' to my report.
> - I created a button with a click event where I call my web service that
> returns an object of 'myTypedDataSet'.
> Now - how can I assign this dataset to the report? I can't see anything
> documented about how to do this.
> Kind Regards
> Thomas|||Hi Bruce,
> I have just started using this control but so far in server mode. I
> know that the way the control works in local mode is you give it the dataset
> and the report to use.
But how do you do this in runtime with a dataset retrieved from a web
service? This must be a very common scenarion when you use the report in
local mode.
Actually - I have implemented a solution that does this but I am not happy
with the way I do it.
On my winform button click event:
//Create an inctance of my web service proxy class and get the dataset
wsASPService myService = new wsASPService();
DataSet ds = myService.GetSalesName();
//Loop through the records of the first tables in the returned dataset and
add those
//records to my report's dataset 'dsSalesName'
//('dsSalesName' is a dataset created in this winform project)
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
dsSalesName.Tables[0].Rows.Add(ds.Tables[0].Rows[i].ItemArray[0],
ds.Tables[0].Rows[i].ItemArray[1]); //Only 2 columns in my table
reportViewer2.Refresh();
This works ok but in this solution I have created my typed dataset in my win
forms project and this is a poor design. I want to keep this dataset in a
separate project so I can share my typed dataset definitions to be used
across my solution (see my first post in this thread where I have the dataset
in a class library project).
Any ideas?
Thanks
Thomas Andersson|||I would not think you should have to copy from one dataset to another. I
would think you could just set the data source for the control to this
dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Andersson" <1qa2ws3ed4rf5tg6yh@.newsgroup.nospam> wrote in message
news:D78561C5-D531-409D-93EB-48AAFBA386CD@.microsoft.com...
> Hi Bruce,
>> I have just started using this control but so far in server mode. I
>> know that the way the control works in local mode is you give it the
>> dataset
>> and the report to use.
> But how do you do this in runtime with a dataset retrieved from a web
> service? This must be a very common scenarion when you use the report in
> local mode.
> Actually - I have implemented a solution that does this but I am not happy
> with the way I do it.
> On my winform button click event:
> //Create an inctance of my web service proxy class and get the dataset
> wsASPService myService = new wsASPService();
> DataSet ds = myService.GetSalesName();
> //Loop through the records of the first tables in the returned dataset and
> add those
> //records to my report's dataset 'dsSalesName'
> //('dsSalesName' is a dataset created in this winform project)
> for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
> dsSalesName.Tables[0].Rows.Add(ds.Tables[0].Rows[i].ItemArray[0],
> ds.Tables[0].Rows[i].ItemArray[1]); //Only 2 columns in my table
> reportViewer2.Refresh();
> This works ok but in this solution I have created my typed dataset in my
> win
> forms project and this is a poor design. I want to keep this dataset in a
> separate project so I can share my typed dataset definitions to be used
> across my solution (see my first post in this thread where I have the
> dataset
> in a class library project).
> Any ideas?
> Thanks
> Thomas Andersson|||> I would not think you should have to copy from one dataset to another. I
> would think you could just set the data source for the control to this
> dataset.
I get this error message when I try to do that: "Value does not fall within
the expected range."
wsASPService myService = new wsASPService();
DataSet ds = myService.GetSalesName();
reportViewer2.LocalReport.DataSources[0] = new
Microsoft.Reporting.WinForms.ReportDataSource("dsSalesName", ds);
Is this the right way of doing it? Can you get it to work if you do a quick
sample?
Kind Regards
Thomas|||Hi Thomas,
You may use the code like below
reportViewer.LocalReport.DataSources.Add(
new ReportDataSource("DataSet1_Orders", LoadOrdersData()));
LocalReport.SubreportProcessing Event
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localr
eport.subreportprocessing.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Got it to work. First, the datasource needs to be a table, not a dataset
(remember, datasets can have multiple tables).
Here is working code (in my case I have a dataset with a single table that
gets the data from Sybase, doesn't matter though, works the same once you
have a dataset).
Dim dsReport As New Microsoft.Reporting.WinForms.ReportDataSource()
dsReport.Value = ds.Tables(0) 'ds is your dataset
dsReport.Name = "DatasourcenameYourReport is expecting"
Me.ReportViewer1.LocalReport.DataSources.Add(dsReport)
Me.ReportViewer1.RefreshReport()
If you add it by the wrong datasource name the reportviewer will tell you.
If you are not sure of the datasource name then add a break point and use
this in immediate window:
?me.ReportViewer1.LocalReport.GetDataSourceNames(0).ToString
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Andersson" <1qa2ws3ed4rf5tg6yh@.newsgroup.nospam> wrote in message
news:A6EFD849-F73F-4435-A7FF-118319B16449@.microsoft.com...
>
>> I would not think you should have to copy from one dataset to another. I
>> would think you could just set the data source for the control to this
>> dataset.
> I get this error message when I try to do that: "Value does not fall
> within
> the expected range."
> wsASPService myService = new wsASPService();
> DataSet ds = myService.GetSalesName();
> reportViewer2.LocalReport.DataSources[0] = new
> Microsoft.Reporting.WinForms.ReportDataSource("dsSalesName", ds);
> Is this the right way of doing it? Can you get it to work if you do a
> quick
> sample?
> Kind Regards
> Thomas

Can NOT login in to SQL2005 with SQl Server Mangement Tool

I get the following error when trying to connect to my server that is on my local maching:

provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

It happened after I changed the allowed remote connections to 1. It appears tha I can not log into the server at all. Any way to fix this besides reinstalling SQL Server?

Thanks, Doug

You do not need to re-install. Start here for a complete tutorial:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

And then check here as well:

http://support.microsoft.com/default.aspx/kb/265808/

If you've done all that, make sure your firewall is set up, as referenced by the "see also" links.

Buck Woody

Can NOT login in to SQL2005 with SQl Server Mangement Tool

I get the following error when trying to connect to my server that is on my local maching:

provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

It happened after I changed the allowed remote connections to 1. It appears tha I can not log into the server at all. Any way to fix this besides reinstalling SQL Server?

Thanks, Doug

You do not need to re-install. Start here for a complete tutorial:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

And then check here as well:

http://support.microsoft.com/default.aspx/kb/265808/

If you've done all that, make sure your firewall is set up, as referenced by the "see also" links.

Buck Woody