How to delete a temporary table in TSQL

When a TSQL script creates a temporary table, it is useful in developing mode to drop the table if it exists.

The following script does the job:

IF OBJECT_ID( N'tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable

How to: Restore a SQL Server database with TSQL

The task of restoring a SQL Server database with a TSQL script is as simple as executing the following script:

RESTORE DATABASE [MyDatabase]
FROM DISK = N'C:\Backup\MyDatabase.bak'
WITH  FILE = 1,
MOVE N'MyDataLogicalName' TO N'D:\DatabasesData\MyDatabase.mdf', 
MOVE N'MyLogLogicalName' TO N'D:\DatabasesLog\MyDatabase_log.ldf', 

The only problem with the previous script is that if there are any open connections to the database the following error is generated:

Exclusive access could not be obtained because the database is in use.

To fix the issue one solution is to put the database in single user mode before the backup.

-- Put the database in single user Mode
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

-- Restore the Database
RESTORE DATABASE MyDatabase FROM DISK ...

-- Put the database back in multiuser mode
ALTER DATABASE MyDatabase SET MULTI_USER
GO

If this script fails, execute the last statement to put the data base back in multiuser mode.

To simply restore the database use:
-- Put the database in single user Mode
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
-- Restore the Database
RESTORE DATABASE MyDatabase FROM DISK = N'C:\Backup\MyDatabase .bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
-- Put the database back in multiuser mode
ALTER DATABASE MyDatabase SET MULTI_USER
GO

Render aspx page to a HTML string

To render a aspx page to a HTML simply execute the following code:

   1: StringWriter writer = new StringWriter();

   2: HttpContext.Current.Server.Execute("~/MyPage.aspx", writer);

To convert to a string:

   1: writer.ToString()

How To: Change the tempdb data files location

To change the tempdb data files location:
1) Determine the logical file names for the tempdb database.
The logical name for each file is contained in the NAME column.

USE tempdb
GO
EXEC sp_helpfile

2)Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DBData\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog,FILENAME = 'E:\DBData\templog.ldf')
GO
3) Stop and restart SQL Server.

Fix: Saving changed to a table not permitted on SQL Server 2008

An error message occurs when you try to save table chnages in SQL Server 2008 Management Studio:
Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

This problem occurs when the Prevent saving changes that require the table re-creation option is enabled.

To disable it:
1) Open SQL Server Management Studio (SSMS).
2) On the Tools menu, click Options.
3) Click Designers.
4) Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note that if you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

This is a bug from microsoft, more information here.

How To: Drop a user that owns a schema

SQL Server returns the error

The database principal owns a schema in the database, and cannot be dropped

when a user is being dropped that owns a schema in the database.

The delete the user go to Sql Server Management Studio, expand your database -> Security and press on Schemas. In the Object Explorer Details (if not visible go to the View Menu e select Object Explorer Details) you can see a list of the schemas and the owners.

Now locate the schema(s) the user you want to delete is the owner, right click and select properties. In the General you can see the schema owner, change it to the new owner (dbo for example).

When the user you want to delete has no schemas owned you can delete it.

How To: Turn on database statistics

Execute the following T-SQL:
ALTER DATABASE SET AUTO_UPDATE_STATISTICS ON

HOW TO: Get the database compability level

Execute the following query to get the compability level of all the databases.

SELECT name, compatibility_level FROM sys.databases

To change the compatibility level:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 100

How To: Get the lastest version of SQL Server 2008

The @@VERSION returns the version, build date and other information about the SQL Server.
In Sql Server 2008, the return format is

major.minor.build.incremental-build

Example: SELECT @@VERSION

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

HO TO: Clear procedure cache and empty the data cache

When profiling and optimizing a stored procedure or query, it is usefull to clean the sql server stored procedure and data cache.
The following T-SQL performes the task:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Transact-SQL: Control whether concatenation results are treated as nullor empty string values

Concatenation of strings with a null value will result in NULL.

For example:

DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + @NullString

Will return NULL and not ‘MyString.

Until now we could use

SET CONCAT_NULL_YIELDS_NULL OFF

When concatenating a null value with a string yields the string itself (the null value is treated as an empty string).

Microsoft has issued the following warning:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And this leaves us with the ISNULL solution:

DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + ISNULL(@NullString, '')

 

 

Linq e WCF

Exposing LINQ Generated Classes through WCF is very simple.
Just set the Serialization Mode property to Unidirectional.
The Linq designer will decorate the Linq classes with the DataContract and DataMember attributes.

Linq - How to attach objects from different data contexts

After retrieving an object with Linq to Sql, if an update (Attach) is tried on the same object, the following exception is thrown:

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.

Example:

Product product = null;

Product originalProduct = new Product();

// Gte product to change its properties

using (myDataContext dc1 = new myDataContext())

{

   product = dc1.Products.Single(p=>p.ID==457);

}

product.Description = "MyProduct";

using (myDataContext dc2 = new myDataContext())

{

   dc2.Products.Attach(product , originalProduct);

   dc2.SubmitChanges(); 

}

The best solution to fix this behavior is to set the DeferredLoadingEnabled property of the DataContext to false of the retrieve entity query.

Example:

Product product = null;

Product originalProduct = new Product();

// Gte product to change its properties

using (myDataContext dc1 = new myDataContext())

{

   dc1.Datacontext.DeferredLoadingEnabled = false;

   product = dc1.Products.Single(p=>p.ID==457);

}

product.Description = "MyProduct";

using (myDataContext dc2 = new myDataContext())

{

   dc2.Products.Attach(product , originalProduct);

   dc2.SubmitChanges(); 

}

 

The DeferredLoadingEnabled property of the DataContext controls the deferred loading options of LINQ.

How to use the DBCC SHRINKFILE statement to shrink the transaction log file

To shrink the transaction log file, execute the following statements:

USE MyDatabase
BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS

If the transaction log does not shrinks, run the statements again to make more of the virtual log files inactive.

1) BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
Backup the transaction log to make most of the active virtual log files inactive

2)DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS
Shrink the transaction log file

Running SSIS package on SQL Server Agent

A SSIS package can be executed using SQL Server agent.

To execute the package a Proxy Account and a credential must be configured in SQL Server.

Problems:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314).  The step failed.

The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:

1) The proxy account have the "Act as part of the operating system" privilege.

2) The SQL Server Account is in the group

SQLServer2005MSSQLUser$<server>$<instance>

3) The Proxy account is in the group

SQLServer2005DTSLUser$<server>$<instance>

SSIS - Send mail from script task

Sending a mail form an integration service package (SSIS) is implemented using the .Net framework classes.

The only question is how to use a SMTP Connection Manager task to configure the SMTP Server.

Public Sub Main()

    Dim mailMessage As MailMessage

    Dim smtpClient As SmtpClient

    Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection Manager").AcquireConnection(Dts.Transaction), String)

    Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)

    mailMessage = New MailMessage("from@myMail.net", "to@myMail.net")

    mailMessage.Body = "myMessage"

    mailMessage.Subject = "mySubject"

    smtpClient = New SmtpClient(smtpServer)

    smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

    mailMessage.IsBodyHtml = True

    smtpClient.Send(mailMessage)

    Dts.TaskResult = Dts.Results.Success

End Sub

SQL Server Defragmentation

SQL Server stores the data and indexes on data files in a physical disk, this is called external fragmentation. As all files on the windows system, fragmentation degrades I/O performance.
SQL Server has also a internal fragmentation that occurs when records are removed from the database pages, but the space is not freed.
If data files have external fragmentation, index rebuilding will take longer since there is a I/O bottleneck.

Defragmentation is the process to fix fragmentation.
For each type of fragmentation, there is a solution:
1) Internal fragmentation:
Use the following script from SQL Server 2005 BOL to defragment or DBCC DBREINDEX .

2) External fragmentation:
Use a defragmentation tool like diskeeper to defragment the file system.

Developing a maintenance plan for SQL Server is a best practice.

Prevent Windows from paging the SQL Server data to virtual memory on disk

Paging the SQL Server data to virtual memory on disk reduces overall performance.
The Enable the Lock Pages in Memory Option is a windows policy that prevents Windows from paging the SQL Server data to virtual memory on disk.
The option is disabled by default.

This privilege must be enabled to configure Address Windowing Extensions (AWE), that must be activated on sql server.

To enable the lock pages in memory option:
1) Execute gpedit.msc on Start->Run
2) Expand Computer Configuration->Windows Settings
3) Expand Security Settings->Local Policies
4) Select the User Rights Assignment folder
5) Double-click Lock pages in memory and add the account that runs sqlservr.exe

In SQL Server Activate AWE.

Max Degree of Parallelism

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.

The value must be equal to the number of physical processors, not including hyperthreading.

One possible calculation formula is:

select case

         when cpu_count / hyperthread_ratio > 8 then 8

         else cpu_count / hyperthread_ratio

       end as optimal_maxdop_setting

from sys.dm_os_sys_info;

 

Configure via Management Studio:

1. Open SQL Server 2005 Management Studio
2- Once the tool loads, navigate to the intended server in the Object Explorer and right click on server name
3. Select the 'Properties' option
4. Select the 'Advanced' page from the left navigation
5. Review the configurations for the 'Parallelism' section

Configure via T-SQL Script:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 8;

GO

RECONFIGURE WITH OVERRIDE;

GO

T-SQL And Linq To Sql Reference

Blogger Tags:

Exists

1. T-SQL

select * from Orders o

where exists (select 1 from Product where ProductType = 'Memory'               

              and ProductId = o.ProductID)

2. Linq

using (MyDataContext dc = new MyDataContext())

{    

    var orders = from o in dc.Orders    

                  where (from p in dc.Products    

                         where p.ProductType == 'Memory'    

                         select p.ProductID).Contains(o.ProductID)

                  select o;

}