Microsoft Sql Server search and replace data inside xml

If column is of XML type or you can change to XML type, update can be done in a single statement: ALTER TABLE myTable ALTER COLUMN col2 XML ; GO UPDATE myTable SET col2.modify ( ‘replace value of (/loadTest/Groups/Group[Name/text()[1]=(“testScript1”)]/VUsersNumber/text())[1] with (“2300”)’ ) WHERE col1 = 358 ; GO Otherwise use cast: DECLARE @l_xml XML SELECT […]

Read More Microsoft Sql Server search and replace data inside xml
March 21, 2018

Windows PowerShell run script on all SQL Servers recovery mode

Micrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases. To check all databases in a SQL Server instance, run the below script in a SQL command window. Optionally uncomment the EXEC line to have this script switch databases to full […]

Read More Windows PowerShell run script on all SQL Servers recovery mode
November 5, 2015

Shrink Transaction Logs in all every database SQL Server

SQL Server transaction logs need backed up regularly, or they will grow and grow forever, eventually filling up all available disk space. That is best done in a maintenance plan. Unfortunately you have to remember to set that up post install, it doesn’t happen out of the box by default (why doesn’t it? Surely it […]

Read More Shrink Transaction Logs in all every database SQL Server
November 29, 2013

Remote Desktop Connection Manager

Remote Desktop Connection Manager, a free download from http://www.microsoft.com/en-gb/download/details.aspx?id=21101, lets you switch quickly between windows servers, saves the servers and your login details in a list.

Read More Remote Desktop Connection Manager
November 22, 2013

SQL Server recently changed objects

List recently changed procedures and views in a database: SELECT name , create_date , modify_date FROM sys.objects WHERE ( create_date >= DATEADD ( DAY , -7 , GETDATE() ) OR modify_date >= DATEADD ( DAY , -7 , GETDATE() ) ) AND type_desc IN ( ‘VIEW’ , ‘SQL_STORED_PROCEDURE’ ) ORDER BY name

Read More SQL Server recently changed objects
October 25, 2013

Shrink msdb database SQL Server

Steps to shrink msdb database in SQL Server 2005: T-SQL: truncate table msdb.dbo.log_shipping_monitor_history_detail T-SQL: exec msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-07T13:55:10'(change date above as required) T-SQL: alter index all on msdb.dbo.sysjobhistory reorganize Management Studio: system databases > msdb > tasks > shrink > databaseOR, same thing in T-SQL: dbcc shrinkdatabase('msdb') To see disk usage for a database: Management Studio: […]

Read More Shrink msdb database SQL Server
May 18, 2013

One Comment

Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012

Here is the crib sheet/study notes I used for the three exams: 70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1 70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 2 70-459 Transition your MCITP on SQL Server 2008 to MCSE: Data Platform […]

Read More Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012
May 1, 2013

2 Comments

Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”

Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, […]

Read More Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”
December 5, 2011

Howto remove carriage return line feed from SQL Server for displaying in Excel

Use this SQL to remove carriage return line feed from SQL server for displaying in excel: SELECT REPLACE ( column_name , CHAR(13) + CHAR(10) , ‘, ‘ ) FROM table_name GO Based on posts by Aaron Bertrand and David Seruyang. Update 2015: Carriage return (chr13) + line feed (chr10) is the default line terminator for […]

Read More Howto remove carriage return line feed from SQL Server for displaying in Excel
March 13, 2009

8 Comments