Howto: Shrink MS SQL Database Transaction Log

As some hosting provider do limited the size of MS SQL database to be hosted, you might found out your database is not really used up the allocated size.

For many cases, it is because your database transaction log used up those spaces and you might not need to have this transaction log for any purpose. To get this resolve, you may shrink the database transaction log.

1. Open the MS SQL Management Studio and connect to the console.
2. Run the following command from SQL Query.

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

** Replace the DatabaseName and TransactionLogName with your database information.

In most of the cases, you may schedule this task to be run automatically if needed.

MSSQL: The server principal is not able to access the database

If you accessing MSSQL 2005 with MSSQL 2008 management studio, you might getting error of The server principal is not able to access the database when log into the database server.

To fix it,
1. Connect to SQL Server Management Studio.
2. Connect to the SQL server.
3. Click “View” and then select on the “Object Explorer Details”. (This step could be done from shortcut F7) Continue reading MSSQL: The server principal is not able to access the database

Restrictions on Backup Operations in SQL Server

Today, I just noticed that the SQL Server backup is not generated and it is kinda weird.

After looking around from MSDN, it is found out there are some restriction from the backup operation.

– Backup not running due to any databases in offline status.
– Concurrency restriction during backup.

For more information, you may refer to the URL as below.
http://msdn.microsoft.com/en-us/library/ms175477%28v=SQL.90%29.aspx

Changing MSSQL Ownership of a Table

Sometimes, you are moving your SQL Server database from one server to another server and you found out the connection string is correct but the database could not be connected.

You may check if your schema set correct from your MS SQL server. To done it, you may refer to the step as below.

1. Open the SQL Query Windows.
2. Use the command as below.
==================
USE mydatabase;
GO
ALTER SCHEMA dbo TRANSFER [schema].mytable;
==================
# replace schema with your old schema, mytable with your table name.
3. Execute it.

You will now found the database is connected.

To Identify your SQL Server Version

Some times, it is very hard for you to check your SQL Server version even log into the console as the about of SQL Server did not actually mentioned the actual version.

To get it done, you may run a query from the SQL Management where this is applied to SQL Server 2008, 2005 and 2000.

Open your SQL Server Management and enter the following query to the New Query file.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Result:

SQL: Select

Assume that we have a table named MyTable and it contain 4 column named ID, Name, Date and City.

Today, I will show some SQL Select command that might useful to SQL Administrator daily task.

1) SELECT * FROM MyTable – Returns all columns/Rows from MyTable
2) SELECT * FROM MyTable WHERE ID=2 – Returns the row(s) which ID has value 2
3) SELECT * FROM MyTable WHERE ID BETWEEN 10 and 20 – Return all row(s) between 10 and 20, this result includes 10 and 20
4) SELECT * FROM MyTable WHERE City IN (‘KL’,’Penang’,’Johore’) – Returns all row(s) which city is KL, Penang, Johore
5) SELECT * FROM MyTable WHERE Name LIKE ‘A%’ – Return all row(s) where the name starts letter A
6) SELECT * FROM MyTable WHERE Name LIKE ‘%A’ – Return all row(s) where the name ends letter A
7) SELECT * FROM MyTable WHERE Name LIKE ‘[ABC]%’ – Return all row(s) of name start with A / B / C
8) SELECT * FROM MyTable WHERE Name LIKE ‘[^ABC]%’ – Return all row(s) of name not start with A and B and C
9) SELECT (Name+space(1)+City) AS Address FROM MyTable – Returns single column address, name and city added together with a space
10) SELECT * FROM MyTable WHERE Name IS NULL – Return all row(s) which Name has null values
11) SELECT * FROM MyTable WHERE Name IS NOT NULL – Return all row(s) which Name has not null values
12) SELECT * FROM MyTable Order By ID Desc – Sort the result set descending order, Asc or not using any sort Ascending order
13) SELECT ‘Visual Studio’ as IDE, ‘2010’ as Version – Creating memory resident result set with two columns[IDE and Version]
14) SELECT Distinct ID FROM MyTable – Returns unique rows based on ID
15) SELECT TOP 10 * FROM MyTable – Return 10 customers randomly
16) SELECT getdate() – Shows the current date
17) SELECT db_name() – shows the database name which you are working on
18) SELECT @@Servername – Shows name of the server
19) SELECT serverproperty (‘Edition’) – You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server
20) SELECT user_name() – Get current user
21)  SELECT *  into #test FROM MyTable  – Create temporary table #test and insert all records FROM MyTable
22)  SELECT Max(ID) FROM MyTable – Returns Maximum ID FROM MyTable
23)  SELECT * FROM MyTable Compute Max(ID) – Returns two result sets – getting all rows and maximum value of ID

Prevent SQL injection by using IIS URL Rewrite

Nowadays, a bad or unoptimizes SQL query could easily compromised or get attacked. However you may try to reduce or prevent (Don’t say avoid, it is not possible, keke) the SQL injection through your expression rules.

If you are using IIS as your web engine, you may use some expression to reduce the SQL injection.

[dD][\%]*[eE][\%]*[cC][\%]*[lL][\%]*[aA][\%]*[rR][\%]*[eE][\s\S]*[@][a-zA-Z0-9_]+[\s\S]*[nN]*[\%]*[vV][\%]*[aA][\%]*[rR][\%]*[cC][\%]*[hH][\%]*[aA][\%]*[rR][\s\S]*[eE][\%]*[xX][\%]*[eE][\%]*[cC][\s\S]*

Continue reading Prevent SQL injection by using IIS URL Rewrite

Schedule backup for MSSQL to remote computer

If you familiar with MSSQL maintenance plan and ever configured backup through this tool and you should know that to backup the MSSQL database to remote computer, you have to configure Log On setting from services.

Okay, today we assume that we couldn’t allow to change the setting from Log On setting and wish to set the schedule backup, we could use maintenance tool and batch command to schedule it.

Firstly, create a maintenance plan from MSSQL, then select “Back Up Database Task”. From Database(s) option, select your database name, you may specify the directory that wish to backup. From this article we make it to C:\mssql\backup\.

Next, create a batch file named mssql_backup.bat and insert command as below.

xcopy C:\mssql\backup "\\remote_server\c$\backup" /e /i /h /y

Where remote_server is your remote server name and you would like to place it to C:\backup.

Lastly, create Windows Task Scheduler and configure the mssql_backup.bat to run daily or weekly as your wish.

PHP with MSSQL not working

Sometimes you might wonder why you are not able to connect from PHP script with your MSSQL database as you have enabled php_mssql.dll extension from your php.ini.

Below are the step to resolve this issue;

But before we given the solution, you might get the following error message, however the error might just unable to connect to the MSSQL database without any further detail.

Cause:
Assume you were using IIS6/7 with PHP 5.2.x(Microsoft Windows Server 2003/2008),  you might get such issue if you are install the PHP with source installation.

Error:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost:1433 in C:\Domains\wwwroot\php_mssql.php on line 5
Couldn’t connect to SQL Server on localhost:1433

Solution:
1. Download file ntwdblib.dll from URL of http://www.dlldll.com/ntwdblib.dll_download.html.
2. Go to the server hosted for your PHP script.
3. Go to PHP directory, assume you are using C:\Program Files\PHP5.2.x\, backup original ntwdblib.dll.
4. Replace the file as downloaded from Step 1.

Test again, and your problem should resolved.

You may refer to the URL as below for more information on the PHP_MSSQL connect page available from PHP.Net
http://my.php.net/function.mssql-connect