[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[File System Task] Error: An error occurred with the following error message: "Access to th

Hi -

I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:\dir\file.txt) it works fine. When I change it to a virtual directory (\\server\dir\file.txt) I get a security error:

[File System Task] Error: An error occurred with the following error message: "Access to the path '\\grace\hbtest\to\S2TMM_Live_Title_000002.xml' is denied.".

Where do I change the security settings?

Thanks - Grace

Security to network shares is dependent on the user who is running the package. If you are running it yourself, make sure you have permissions to the share. If you are running it from SQL Agent, you might need to use a proxy account (search for SQL Agent Proxy in Books Online) to access it.