XAMPP MSSQL connection problem, php_sqlsrv.dll

I recently tried to install or add the PHP mssql driver to connect with the ms sql server.  Official compiled dll for the php_sqlsrv is until version 5.4, and does not support PHP version 5.5 and beyond.

I tried adding the extension, but XAMPP keeps on telling me there is something wrong.  It gives a message like "startup: sqlsrv: unable to initialize module..".  I searched the internet for some time, until I came across a solution on my particular problem.

It seems there is a need to compile a newer version of the php_sqlsrv driver.  Thankfully someone did that for me since I certainly have no idea how to compile this source file.  This is only for test purposes only, so hopefully the compiled dll does not have anything else besides the intended purpose of connecting to the ms sql server.

Here is the link to that file: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e1d37219-88a3-46b2-a421-73bfa33fe433/unofficial-php-55-drivers-x86

Save not permitted in MS SQL 2008 Server Management Studio

I encountered this first time on editing the fields in design, the management studio pops up a dialog box with message the changes cannot be saved due to drop and create of table.

Luckily, I found the answer on google search right away.  Here is the website of that solution: http://msdn.microsoft.com/en-us/library/bb895146.aspx

MS SQL Roles

This is a good topic to discuss.  MS SQL has made granular roles for specific tasks for each user, this is for security purposes of the Database Server.

You surely want certain database owner just to be able to access their database and nothing else.  Or just allow them to do a backup of their own server, this is where the database or server level roles come in.

I'm familiar of some of the roles but not all of them and at times I also forget, which role overrides the other roles, or which role is not needed.

Microsoft has the list on their site on their server level role: http://msdn.microsoft.com/en-us/library/ms188659(v=sql.105).aspx

This looks an okay reference on which is the role needed and which is not.

There is also the database level role: http://msdn.microsoft.com/en-us/library/ms189121(v=sql.105).aspx

A little off topic, I found a great blog on MS SQL versions, the list is pretty good and you will also know which version you are using.  Here is the link: http://sqlserverbuilds.blogspot.com/

Minimum & Maximum Year in MS SQL

You guys remembered the millennium bug, in year 2000?  It's where some people fear, the computers will crash, and planes will also crash when in flight, because of the bug on the year, where the computers only allow until year 2000.  Well I didn't believe it, and wasn't affected by that scare.

But, there is such things as limits I supposed.

In MS SQL 2000 & 2005, they have the following limits:

• minimum date of 1753-01-01 00:00:00.000 
• maximum date of 9999-12-31 23:59:59.999

This was fixed in MS SQL 2008, where they have the following limits to the dates:

• Minimum date: 0001-01-01 00:00:00.000
• Maximum date: 9999-12-31 23:59:59.999

If we ever reached that year 10000, then they will probably panic again like the millennium bug scare.  Or people will be cyborgs with numbers on their names and probably fear they will be affected by the limit.

Shrinking a transaction log file in MSSQL 2008

This is always a problem with MS SQL, the log file usually grows bigger than the data file itself.  Sure, it is very important in recovery.  But do you really need it, if you have full back-up? You will only need the log file to restore a deferential back-up.


Before in MS SQL 2000 and 2005, I use the following query (before MS SQL 2008):

BACKUP LOG DBname WITH TRUNCATE_ONLY

DBCC SHRINKFILE(DBname_Log, 1)

And that did the trick, but 'TRUNCATE_ONLY' is not recognized by MS SQL 2008 anymore.

You can use 'ALTER DATABASE' and setting the recovery to 'SIMPLE' then do the shrinking, otherwise by just using the 'DBCC SHRINKFILE(DBname_Log, 1)' query, there won't be any effect.

After you do the shrinking, return the recovery back to full, as such: 'SET RECOVERY FULL'.

This is only one of the solutions found on the web, there could be others involving running your own stored procedures.  But I prefer a simple few line approach.  Solution was found here: http://msdn.microsoft.com/en-us/library/ms189493.aspx

Here is the solution I used to shrink my log file on a MS SQL 2008.


ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (dbname_log, 1)
GO

ALTER DATABASE dbname
SET RECOVERY FULL;
GO

what happened here is a shrinking of a log file to a specific target size.  This sample code was found in the solution link I provided above from a Microsoft website as Example B.

Others, suggest you backup the log file first before you shrink it.  But a 300MB backup took me a long time, what if it is 5GB of log file?

Anyways, if you prefer to back it up first, you could add this code before you alter the database recovery:


BACKUP LOG dbname TO DISK = N'D:\dbname_log.bak'
GO

You may also, run this to view the database info:

EXEC sp_helpfile

It will show the logical name, fieldid, filename(path), filegroup, size, maxsize, growth, and usage.

Hope you found your solution here. Till next time.