[Jobs] Quick & Dirty - is the job running (another approach)

Cribbed from Gregory A. Larsen.

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2/Detecting-The-State-of-a-SQL-Server-Agent-Job.htm

SET NOCOUNT ON
create table #enum_job ( 
Job_ID uniqueidentifier, 
Last_Run_Date int, 
Last_Run_Time int, 
Next_Run_Date int, 
Next_Run_Time int, 
Next_Run_Schedule_ID int, 
Requested_To_Run int, 
Request_Source int, 
Request_Source_ID varchar(100), 
Running int, 
Current_Step int, 
Current_Retry_Attempt int, 
State int 
)       
insert into #enum_job 
     exec master.dbo.xp_sqlagent_enum_jobs 0,sa,@job_id

IF (select COUNT(*) from #enum_job WHERE running = 1) = 1
PRINT 'running'
drop table #enum_job
SET NOCOUNT OFF

[Code] Using INFORMATION_SCHEMA with temp tables.

Was recently trying to parse a temp table and do things based on the columns. I'd come up with this....

  SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME LIKE '#yourtemptablehere%'

But then I came across this post from Michael Valentine Jones (a pseudonym?), on the SQLTeam forums. Many thanks, Michael

select
 *
from
 tempdb.information_schema.columns
where
 object_id('tempdb..#yourtemptablehere') = object_id('tempdb..'+TABLE_NAME)

[Powershell] Basic SQL query exported to CSV

Invoke-Sqlcmd -query "select getdate(), getdate()+1" -serverinstance "yourservername"|Export-Csv c:\temp\testps2.txt –notypeinformation

or, broken down by line so you can see all of it...

Invoke-Sqlcmd 
 -query "select getdate(), getdate()+1" 
 -serverinstance "yourservername"
 |Export-Csv 
 c:\test\test.txt 
 –notypeinformation

-query: the query.
-serverinstance: server name
-notypeinformation: removes the “#TYPE System.Data.DataRow” line at the top.

And if you don't want a header row... you have to use a different export process, and then tell a different process to iterate through the array and write to disk. Really, guys? Too hard to add a -noheader option?

(and all this is on one line; you can use a ` to split it across lines.

Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" -serverinstance "yourservername"|ConvertTo-Csv -notypeinformation -outvariable outdata; $outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}

or 
Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" `
-serverinstance "ftw-sv-db-03"|ConvertTo-Csv -notypeinformation -outvariable outdata;`
 $outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}
 (then hit enter again to tell it you're done for realsies)

Oh, and it for some reason outputs the full file to console, but saves what you want to a file.

[Powershell] Basics to run a SQL query

Putting this here for when the new guy starts. The learning curve can suck at certain points, like the installer. See my other post about it. Grr.

• Install Powershell 2
  
• Install SQL Server 2008 Feature Pack: Powershell Extensions http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
  
• Install SQL Powershell Extensions: http://sqlpsx.codeplex.com/
  
• Add this line to My Documents\WindowsPowerShell\profile.ps1: "add-pssnapin SqlServerCmdletSnapin100; add-pssnapin SqlServerProviderSnapin100;" (no quotes)
  
• Start Powershell and see if it works:
  Set-ExecutionPolicy RemoteSigned (or
  Invoke-Sqlcmd -query "select getdate(), @@version" -serverinstance "yourservername"|Export-Csv c:\testps.txt –notypeinformation
  

[Maintenance] Checking age of statistics

Simple stuff, saved here in case anybody needs it

SELECT objects.name AS object_name, indexes.name AS index_name,
STATS_DATE(indexes.OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
INNER JOIN sys.objects ON indexes.object_id = objects.object_id
--WHERE objects.OBJECT_ID = OBJECT_ID('dbo.yourtablename')
GO