23 December 2008
SELECT name, compatibility_level FROM sys.databases
To change the compatibility level:
In Sql Server 2008, the return format is
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
10 December 2008
The following T-SQL performes the task:
30 September 2008
Concatenation of strings with a null value will result in NULL.
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, '')
16 September 2008
25 July 2008
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.
The best solution to fix this behavior is to set the DeferredLoadingEnabled property of the DataContext to false of the retrieve entity query.
The DeferredLoadingEnabled property of the DataContext controls the deferred loading options of LINQ.
26 June 2008
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
04 June 2008
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.
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
3) The Proxy account is in the group
30 May 2008
05 May 2008
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.
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.
31 March 2008
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:
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:
18 March 2008
05 March 2008
A common problem in developing with asp.net is the user pressing more than once on a submit button.
There is only one problem: When the button is disabled, the page is not submitted and the button event does not fires on the server.
Fortunately the validation library sets the Page_IsValid to false if validation fails or to true otherwise.
This solution changes the button image, but doesn't solve the problem, since the user can still press the button.
A more elegant solution is to use two Divs, on with the wait image and other with the button. When the button is pressed it's Div is hidden and the one with the Wait image is shown.
This solution works with ASP.Net Ajax and with ASP.Net validators.
The final step is to create a Server Control... :)
The parser doesn't return the virtual directory.
A possible solution is to create a custom library and add the following functions:
04 March 2008
The first attempt to solve this problem is use code blocks (<%= ... %>) with ResolveUrl, which give the following error:
The Controls collection cannot be modified because the control contains code blocks (i.e. <% … %>).
The second attempt is to use <%# ... %>) and ResolveUrl, but nothing is returned to the browser.
The third attempt is to data bind the HtmlHead in code behind of the master page, since it derives from Control:
In the master page write:
The css problem can also be resolved with:
The path is relative to the master page, and Asp.Net will solve the path based on the master page location.
A more elaborate solution would be to use resources and the WebResource.axd handler....