Restore SQL Server database through network path
I had a problem where I need to restore database using shared folder on the network. I tried to copy it locally to my local harddrive but then the connection is not stable enough. Hence I…
-
-
Read and Update XML Node of XML datatype in SQL Server
Select Node value This is a simple explanation on how to select a particular node from xml data type of a record in SQL server. Sample XML 00003479 Brodie’s HomeSton NULL AU941170 2010-07-22T09:42:20 vcAuditorDesc,chUpdateStaffCode,dtUpdateDateTime,dtUpdateDateTime So what…
-
How to set variable from Dynamic SQL
Here is the scenario, You have a stored procedure that builds dynamic insert SQL which means the “INSERT” statement is being composed on the fly based on the table name but at the same time you…
-
Shrink all databases and Set it to Simple in SQL Server
Normally, I need to run this script against dev sql server in order for me to free up some space create table #temp_dbs_table ( [db_name] sysname not null primary key, [mod] tinyint not null default 1…
-
TRY….CATCH Rollback Transaction In SQL Server 2005
This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block. In this snippet,…
-
Index in Table Variable
One biggest advantage of table variable for me is you can’t put index on the columns. I was having a problem where doing self join in table variable (it has 1000+ records) is taking really really…
-
SQL Server Function using CLR
UPDATED: I’ve added one function to write from BLOB in SQL Server table to the disk straight away I thought this article might be useful for anyone that wants to implement .NET code to SQL server…
-
Self Inner Join with Group By in SQL Server
This is the case: I have a table and basically this table has many redundancy. The solution is to create a link table and this table will contains record association. So what I need to do…