SQL Query Help..!

Matt-Page

Matt-Page

Matt-Page

Soldato
Joined
23 Nov 2004
Posts
3,794
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 :)
 
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.
 
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.
Click to expand...

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?
 
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
 
I don't think you understand what SQL is. SQL is a language for querying a database. You don't specify the server address, username and so forth in the SQL and you don't do loops. You generally use a programming language for that. Also, just saying SQL is a little vague. People would be able to help you more if you specified what DBMS system(s) you are connecting to. Are they MS SQL Server? MySQL? PostgreSQL?
 
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
Click to expand...

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....
 
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!
 
Also, you are specifying one column to be returned (NetSalesAmount) against a specific date. Are you sure that's what you want?
 
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!
Click to expand...

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.
 
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?
Click to expand...

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.
 
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?
 
[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?
Click to expand...

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.
 
[TW]Fox;20582520 said:
Have you ever used SQL before? This is a bit of a complex project if you've not!
Click to expand...

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 :D
 
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...
 
You must log in or register to reply here.
Back
Top Bottom