Data is upload into txt files through job

Question

Post reply

Data is upload into txt files through job

arooj300

Hall of Fame

Points: 3433
More actions
March 3, 2014 at 8:24 am

#304691

Hi All,
I have created stored procedure for checking the cpu usage and connection string.
----check the number of users---
EXEC sp_who2 'Active'
-----check the number of connection----
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
for above script I have created one job which will run every 1 hr and that information will be stored into the txt files through
job-properties-step-advanced-outfiles.
But the problem is the all the information is stored in one file.
actually what I want, for today when job will be started it will create one txt file and all the data will be stored in the same file and tomorrow again the job will start the samething happen.
So everyday I want to create new txt files and when the job will start the data will be stored in that file.
Is there any script or anyother solution, please share with me.
Thanks...
GROUP BY
dbid, loginame

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

You could use Powershell here. A job that calls this script and outputs to a filename based on the date.

There's a rename script here: http://ss64.com/ps/syntax-stampme.html

Here's a text file export: http://stackoverflow.com/questions/19686694/execute-sql-file-using-powershell-and-store-the-output-in-a-text-file

If you struggle with Powershell, then perhaps you can build a job that takes the current date and passes that to your export as the file name.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

arooj300 (3/3/2014)
Hi All,
I have created stored procedure for checking the cpu usage and connection string.
----check the number of users---
EXEC sp_who2 'Active'
-----check the number of connection----
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
for above script I have created one job which will run every 1 hr and that information will be stored into the txt files through
job-properties-step-advanced-outfiles.
But the problem is the all the information is stored in one file.
actually what I want, for today when job will be started it will create one txt file and all the data will be stored in the same file and tomorrow again the job will start the samething happen.
So everyday I want to create new txt files and when the job will start the data will be stored in that file.
Is there any script or anyother solution, please share with me.
Thanks...
GROUP BY
dbid, loginame

I just wouldn't do this. You're creating a nightmare for yourself when someone asks to see the data. Just store the data in a table reserved for DBAs and call it a day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)