How to automate a Visual Studio/SQL Server report

Question

I'm currently working on a reporting tool for a company that conducts the same survey at different clients. So the analysis and report would be the same, except for the conclusions. The ultimate goal of the project would be a tool that automatically extracts, analyses the data and creates the report. SQL Server and Visual Studio are mandatory programs.

I'm completely new to all of this except for the SQL-language. After reading up on this and asking around the usage of stored procedures seemed the best option. If I interpret correct these should be stored in SQL Server and could then be envoked in Visual Studio However, my problem is, I can't figure out how to connect to the external ODBC-server that holds the data in SQL Server 2005. It seems to me there is no way to connect to a remote server in SQL Server 2005. On the other hand, in Visual Studio 2005 I did manage to connect and extract data from the server.

Is this the right way to achieve my goal (repeatable/automated report)? If so, what am I doing/thinking wrong? If not, please enlighten me.

Thx in advance

Answer 1

windows scheduled task. make an executable first that will create the report.

Answer 2

Another way to do this is to use bcp...you can check this article to see if there are any answers here for you. There would be no remote server connections if you are able to run the command and the local box that contains the sql server.

Answer 3

You might be able to set up a linked server in SQL Server 2005 to connect to your remote server/database.