Scripting database using Smo.Scripter


You can automate script generation using SMO and power shell using the following power shell script. Script generated would be similar to the script generated using “Generate Script Wizard” in ssms

You can download script from here

scriptdb_ps1

This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Tagged with:
Posted in Scripting, SQL Power shell, SQL Server

How to specify DB name in CMS Query


You can set the database name for the registered server in the “Connection Properties” tab. Then open a “New Query” on the server group and run your query

207076

 

Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjappmsmgmt/thread/69576d3a-20f5-4641-9e55-0e805429839a

Posted in SSMS

2012 in review


The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 9,500 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 16 years to get that many views.

Click here to see the complete report.

Posted in Uncategorized

Collecting User mode dumps in even if Windows Error reporting is disabled


At times mini dumps may not be sufficient to debug a specific issue. We may need a full process dump to further investigate the problem. came across a MSDN article that explains how to enable collecting user mode dumps  to a local folder.  Here is the link http://msdn.microsoft.com/en-us/library/bb787181(VS.85).aspx

Copy the following to notepad and name the file as localdumps.reg, You can then import it to Registry. Make sure that you have created a folder %LOCALAPPDATA%\CrashDumps

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\LocalDumps]

“DumpCount”=dword:00000100

“DumpType”=dword:00000002

“DumpFolder”=hex(2):25,00,4c,00,4f,00,43,00,41,00,4c,00,41,00,50,00,50,00,44,\   00,41,00,54,00,41,00,25,00,5c,00,43,00,72,00,61,00,73,00,68,00,44,00,75,00,\   6d,00,70,00,73,00,00,00

You can download .reg file from here. Sample .NET application that simulates a Crash is available here

This posting is provided “AS IS” with no warranties, and confers no rights. 

Posted in debugging

SQL 2012 Developer edition for MSDN subscribers


You can download  SQL 2012 Developer edition if you are a MSDN subscriber

https://msdn.microsoft.com/en-us/subscriptions/securedownloads/hh442898#searchTerm=&ProductFamilyId=461&Languages=en&PageSize=50&PageIndex=0

Posted in Uncategorized

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:

 

Posted in Uncategorized

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 }

 

Posted in Uncategorized