I have to do a query on a table that I've never done before and am a bit unsure how to proceed. The table contains log entries for numerous servers, with columns: id, ProfileName, Date_Time, Message. What I need to figure out how to do is get the latest
1,000 log entries for each server. So for example, if there's 20 servers, I'll need to return 20,000 results.
I know how to do it for just one server: SELECT TOP 1000 * FROM TableName WHERE ProfileName LIKE 'ServerName1'. However, I'm not sure how to return all the servers at once and I'd hate to have to write 20+ queries to get it all seperately. Any ideas?
Thanks, that seems like a good way of doing it. However, and I probably should have mentioned this at the start, I do not have the greatest experience with t-sql, I primarily stick to the administration side of SQL. Is it possible I could get an example
of how the join would work in this case?
Is it possible I could get an example of how the join would work in this case?
Hi,
1)Fetching all the remote servers——you can just use SQLDMO(SQL Distributed Management Objects,and you can refer it just from "Add references……"=>"COM").
2)Then please do the codes……
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
List<string> serverNames = new List<name>();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item(i + 1);
if(srv != null)
{
serverNames.Add(srv.ToString());
}
}
//Here you should loop each of value from "serverNames" and then execute to fetch values with the help of SqlCommand
syogod
0 Points
2 Posts
Select query
Jul 26, 2012 01:44 PM|LINK
Hi,
I have to do a query on a table that I've never done before and am a bit unsure how to proceed. The table contains log entries for numerous servers, with columns: id, ProfileName, Date_Time, Message. What I need to figure out how to do is get the latest 1,000 log entries for each server. So for example, if there's 20 servers, I'll need to return 20,000 results.
I know how to do it for just one server: SELECT TOP 1000 * FROM TableName WHERE ProfileName LIKE 'ServerName1'. However, I'm not sure how to return all the servers at once and I'd hate to have to write 20+ queries to get it all seperately. Any ideas?
ignatandrei
All-Star
134511 Points
21576 Posts
Moderator
MVP
Re: Select query
Jul 26, 2012 01:46 PM|LINK
first, select distinct servername in a table.
Then join.
MattsDotNetU...
Contributor
3178 Points
515 Posts
Re: Select query
Jul 26, 2012 01:50 PM|LINK
You can do this with a Row_Number() like below:
syogod
0 Points
2 Posts
Re: Select query
Jul 26, 2012 02:09 PM|LINK
Thanks, that seems like a good way of doing it. However, and I probably should have mentioned this at the start, I do not have the greatest experience with t-sql, I primarily stick to the administration side of SQL. Is it possible I could get an example of how the join would work in this case?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Select query
Jul 28, 2012 01:48 AM|LINK
Hi,
1)Fetching all the remote servers——you can just use SQLDMO(SQL Distributed Management Objects,and you can refer it just from "Add references……"=>"COM").
2)Then please do the codes……
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers(); List<string> serverNames = new List<name>(); for(int i=0;i<sqlServers.Count;i++) { object srv = sqlServers.Item(i + 1); if(srv != null) { serverNames.Add(srv.ToString()); } } //Here you should loop each of value from "serverNames" and then execute to fetch values with the help of SqlCommand