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 xmlMicrsoft 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 modeSQL 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 ServerRemote 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 ManagerList 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 objectsSteps 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 ServerHere 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 2012Indexed 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.”Windows Server 2008 R2 has a strict firewall by default. It will not let you run FTP (client) from the server and get files from other servers/sites It will also not let you run “SQL Server Management Studio” from your PC and connect that into the instance running on the server. To fix this, apply […]
Read More Windows Server 2008 R2 Firewall for SQL Server and FTPUse 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