Archive for the ‘SQL’ Category

Shrunk your SQL Server database log file

Is your log file too large? Apply this command so that your log file will shrink to 1 MB.
BACKUP log <your databasename> with truncate_only
DBCC shrinkfile (<your database log filename>, 1)

VN:F [1.1.6_502]Rating: 0.0/5 (0 votes cast)

Leave a Comment

Free database comparison tool

DBComparer is a professional database comparison tool for analyzing the differences in Microsoft SQL Server 2008 (and 2005) database structures. You can download its 3.0 version for free from here - http://dbcomparer.com/.
This tool got native database comparison support for Microsoft SQL Server 2005 and 2008.
Anyway, I personally found Redgate’s SQL Compare is more flexible but [...]

Leave a Comment

Get current date format of SQL Server database

Use this command:
DBCC USEROPTIONS

VN:F [1.1.6_502]Rating: 0.0/5 (0 votes cast)

Leave a Comment

Strip SQL Comments using RegularExpression with C#

Here is the code:

Regex r
= new Regex(@"(/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/)|(–.*)");
textBox2.Text
= r.Replace(textBox1.Text, string.Empty);

VN:F [1.1.6_502]Rating: 0.0/5 (0 votes cast)

Leave a Comment

SQL - Time bomb coding

Worth reading.
URL: http://www.sqlservercentral.com/articles/Performance+Tuning/69337/
VN:F [1.1.6_502]Rating: 0.0/5 (0 votes cast)

Leave a Comment

T-SQL – How to return null columns with FOR XML

You just need to do a null checking. Like:

SELECT field1, field2, ISNULL(field3,’’) AS field3
FROM table1
FOR XML AUTO

The behavior is by design. Check more details here: http://support.microsoft.com/kb/296393
VN:F [1.1.6_502]Rating: 0.0/5 (0 votes cast)

Leave a Comment

MS SQL Tables Viewer

This is one of my hobby tool which simply connects to a MS SQL Server database and shows all the available tables and its column names. This tool is programmed with Visual C# 2008 Express Edition.
Download from here: http://kidoos.net/media/p/588.aspx
Let me know if somebody needs source code. I have no plans to extend this app [...]

Leave a Comment

Accessing SQL Server Databases with PHP

Read the whitepaper here - http://msdn.microsoft.com/en-us/library/cc793139(SQL.90).aspx
extension=php_sqlsrv.dll

The SQL Server 2005 Driver for PHP is a Microsoft-supported extension of PHP 5 that provides data access to SQL Server 2005 and SQL Server 2008. The extension provides a procedural interface for accessing data in all editions of SQL Server 2005 and SQL Server 2008. The SQL Server [...]

Leave a Comment

T-SQL Pass GETDATE() as a parameter for stored procedure

When you pass getdate() function as a parameter directly to a stored procedure, you will get an error: Incorrect syntax near ‘)’.

EXEC sp_GetProject GETDATE()
 
Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

To avoid this, you will need to create a variable and pass it to the stored procedure. Like this:

DECLARE @dt DATETIME;
SET @dt=GETDATE();
 
EXEC [...]

Leave a Comment

Get Windows Edition information from SQL Server

One way is to extract from @@VERSION.
Another way is to use exec master..xp_cmdshell ’systeminfo’
One more way will be to use a CLR Function (or stored procedure). Sample below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Function1()
{
[...]

Leave a Comment