You can download SQL 2012 Developer edition if you are a MSDN subscriber
April 13, 2012
April 6, 2012
Export the query to excel sheet automatically
You can use powershell to export query results to a Excel file. You can download script from QueryResultsToExcel.ps1
running the powershell script:
View Excel file:
April 4, 2012
Run multiple SQL script files using Invoke-SqlCmd ( SQL Powershell)
# Right click on Server in Object Explorer ( SQL Management Studio)
# Select Start Powershell, In Powershell window, type in following command. replace c:\sqlscripts with your own folder
dir C:\sqlscripts\*.sql | foreach-object { invoke-sqlcmd -InputFile $_.FullName }
March 6, 2012
Execute multiple scripts at Once
You can use SQL Powershell’s invoke-sqlcmd to run multiple sql script files at once. Related thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/44eefaa7-bd02-48fb-9578-b120a8f49813
January 11, 2012
Powershell script to run query against many servers and send combined output as email
This powershell script is sample code to run query against multiple SQL Servers and export the combined output to Excel. This Excel file is later sent as email attachment using hotmail.
Related email thread: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/987d48c0-a82d-4424-8699-cdd0b2bc9c51
# This posting is provided “AS IS” with no warranties, and confers no rights.
Script can be also downloaded from emailResultsMultiServerQuery.ps1
January 9, 2012
December 31, 2011
2011 in review
The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.
Here’s an excerpt:
A San Francisco cable car holds 60 people. This blog was viewed about 1,700 times in 2011. If it were a cable car, it would take about 28 trips to carry that many people.
September 11, 2011
IBM AT 100
http://www.ibm.com/ibm100/common/images/junespecial/ibm_centennial.pdf
“I believe that if an organization is to meet the challenges of a changing world, it must be prepared to change everything about itself, except its beliefs”.
—Thomas J. Watson Jr.
Values therefore force choices: Whom you hire. The ways you serve the customer. How you develop talent at all levels. Which businesses you create, enter and exit, and when. How much risk taking you promote.
When we have lived our values, IBMers and our company have thrived. When we haven’t, it hurt us.
“If your business is based on moving to the future, you can’t be emotionally tied to your past.”
Samuel J. Palmisano,
Chairman, President and CEO, IBM
…
It means behavior that consistently meets high standards, because future growth depends on trust….
…
Despite repeated pronouncements of its imminent demise, we’ve increased installed mainframe capacity 1,000% over the past 13 years.
June 24, 2011
Get list of unsigned binaries in a given folder
get-childitem c:\binpath\*.* | foreach-object {Get-AuthenticodeSignature $_} | where {$_.status -ne "Valid"} | select-object Path | fl
March 30, 2011
Auditing & XE Event sessions
— Audits changes done to syscollector_execution_log_internal directly / through stored procs
— Also captures XEvents when validation error 14262 occurs
USE [master]
GO
– Step #0 – Create a folder c:\TraceLogs
– Step #1 – Create Audit session and start it
— Audit Sessions
– Create a Server Audit to log all audit events to folder
CREATE SERVER AUDIT [DataCollectorObjectAccess_Audit]
TO FILE
( FILEPATH = N’C:\TraceLogs\’
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = ‘e1f7d882-b26e-4b70-bc03-87af197eb7de’
)
ALTER SERVER AUDIT [DataCollectorObjectAccess_Audit]
WITH (STATE = ON)
GO
USE [msdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [DataCollectorObjectAccess_Audit_MSDB]
FOR SERVER AUDIT [DataCollectorObjectAccess_Audit]
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionbegin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionend] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionstart] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionstop] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onerror] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackagebegin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackageend] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackageupdate] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_purge_collection_logs] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_sysutility_mi_upload] BY
[dbo]),
ADD (INSERT ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo]),
ADD (UPDATE ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo]),
ADD (DELETE ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo])
WITH (STATE = ON)
GO
– Step #2 – Create XE Session to capture validation error 14262
– XEvent Sessions
CREATE EVENT SESSION Error14262_Session
ON SERVER
ADD EVENT sqlserver.error_reported (
ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
WHERE (error = 14262 ))
ADD TARGET package0.asynchronous_file_target(
– file target
SET filename=‘C:\TraceLogs\14262Errors.xet’,
metadatafile=‘C:\TraceLogs\14262Errors.xem’)
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE =
ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY =
10 SECONDS,
MAX_EVENT_SIZE =
0KB,
MEMORY_PARTITION_MODE =
NONE,
TRACK_CAUSALITY =
OFF,
STARTUP_STATE =
OFF
)
GO
– Start event session
ALTER EVENT SESSION Error14262_Session
ON SERVER
state=start
GO
– Step #3 – Enable data collector, run till problem reproes
– Step #4 – turn off XE Session & auditing session
ALTER SERVER AUDIT [DataCollectorObjectAccess_Audit]
WITH (STATE = OFF)
GO
ALTER EVENT SESSION Error14262_Session
ON SERVER
state=stop
GO
– Step #5 – Reading Audit logs and XE Logs
SELECT * FROM sys.fn_get_audit_file (‘C:\TraceLogs\*.*’,default,default);
GO
SELECT CAST(event_data as XML) eventdata
FROM sys.fn_xe_file_target_read_file(‘c:\TraceLogs\*.xet’,
‘c:\TraceLogs\*.xem’,
NULL,
NULL)
GO
