SQL Query Help..!

Matt-Page · 16 Nov 2011 at 19:05

Hello,

Right, I have got the following query to output the correct results:

Code:

SELECT NetSalesAmount
FROM dbo.AccumGrossSales
WHERE PTD_ID = 0 AND BusinessDate = '2011/11/16'

My next stage is to get this to run as a loop. I have a list of server/dns device names I want it to connect to and pull back the same information from the same location.

How can I do this in SQL? I don't mind writting the statement out lots of times but I obviously need it to connect to different servers and authenticate (user / pass is same on each server). Would I need to write this in Excel or similar?

Also, the results window doesn't output the device name or instance name so if I were to run it as a loop, I've no way of knowing which servers figures it has pulled it back from.

Advice appreciated

Matt-Page · 16 Nov 2011 at 19:06

Doh! Posted this in the wrong section, can a mod move please?

Matt-Page · 16 Nov 2011 at 19:25

Vonhelmet said:
Hard to say without knowing exactly what you're trying to do, but I'm going to go out in a limb and guess that the answer will involve joins.

In a nutshell I am trying to connect to 160 servers SQL database, extract a figure from a specified table & column (same DB, table and column on every server) and then display it. I want it to displayu so I can distinguish which server the figure came from.

Does that make more sense?

Matt-Page · 16 Nov 2011 at 19:49

A[L]C;20582101 said:
Use sql management studio 2008. Create a new server group. Add all servers in to that group (new server registration). Right click group. New query. Paste query. Go

Yep, that's fine but I need to be able to give it to multiple people to run. I want them to run the query and it returns all of the results to a spredsheet or text file.

Im sure there must be a way of doing this....

Matt-Page · 16 Nov 2011 at 20:03

A[L]C;20582329 said:
Arghh why don't people give the full requirements!!

please advise your full requirements list. An example of how you would like the output to be formatted and an example dataset output would be good too!

Sorry!

Right:

Task:
Connect to a list of servers using their DNS names and extract a sales figure from a SQL database table.

Once the figure is obtained I would like to output it into an Excel spredsheet like this

SERVERNAME 123456 (123456 is the figure obtained from the below SQL query)

Database name = AccumTotalsSQL
Table Name = AccumGrossSales
Column Name = NetSalesAmount

If I run the following query in SQL I get the desired results:

Code:

SELECT NetSalesAmount
FROM dbo.AccumGrossSales
WHERE PTD_ID = 0 AND BusinessDate = '2011/11/16'

I want the above query to go out to all 160 servers, get the figure and display it without having to connect to each server manually and running the SQL query.

Can this be done using VBA in Excel or a Macro? Apologies for the dumb questions I've not really done anything with this. This is a new platform for us and in the past I have been working with BAT scripts and txt files.

Thanks.

Matt-Page · 16 Nov 2011 at 20:04

A[L]C;20582349 said:
Also, you are specifying one column to be returned (NetSalesAmount) against a specific date. Are you sure that's what you want?

Yup. It is basically a sales figure from the current day. Eachs erver will have a different figure because they are at a different site/store.

Matt-Page · 16 Nov 2011 at 20:10

[TW]Fox;20582405 said:
You have a seperate instance of SQL Server at each store?

Each store has their own SQL server and Databses, yes.

Matt-Page · 16 Nov 2011 at 20:21

Vonhelmet said:
This is sounding more and more like a problem for the surrounding language.

Matt-Page · 16 Nov 2011 at 20:23

[TW]Fox;20582491 said:
Presumably you also have a main SQL server at your head office. Can you not set up linked servers on this to the 160 remote SQL servers, and then construct a single query from your end to query the specified tables on the linked servers, bringing the results through that way?

Our centralised server is down/buggered which is why I am having to do this. It basically gives management a live sales figue for the current day.

Is there no way to do this? I'm googling like mad and it seems it can be done with ODBC but I'm not entirely sure on how.

Matt-Page · 16 Nov 2011 at 20:25

[TW]Fox;20582520 said:
Have you ever used SQL before? This is a bit of a complex project if you've not!

I've used it to backup, restore and create databases but nothing on this scale. I'm trying to prove my worth here, obviously 2p is springing to mind

Matt-Page · 16 Nov 2011 at 20:37

Dolph said:
trying to do it by connecting to each separate server each time is going to be a nightmare for a whole variety of reasons, as well as horrendously inefficient.

Pull the data in to a central table once then link to that, it would be so much better...

Right...

So how do I do that?