Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data is upload into txt files through job Expand / Collapse
Author
Message
Posted Monday, March 3, 2014 8:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
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
Post #1546942
Posted Monday, March 3, 2014 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:43 PM
Points: 31,036, Visits: 15,462
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1546966
Posted Monday, March 3, 2014 12:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 35,216, Visits: 31,673
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1547061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse