Wednesday, April 20, 2011

[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

Wednesday, April 13, 2011

[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)

Thursday, April 7, 2011

[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.

Wednesday, April 6, 2011

[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