Getting PHP and Microsoft SQL Server (MSSQL) to play nice

I have a Windows Server 2008 with IIS7 installed. I’ve been pounding my head against a Microsoft constructed brick wall for the past day trying to get PHP to interact with SQL Server 2008.

A brief Google search pulls up a download page from Microsoft with the newest SQL Server drivers for PHP:

In the package you download, there are about 9 different files. You only need to install 1 or 2 and there are no instructions as to which one is the one to install.

In my case, even installing ALL of them didn’t work. My PHPInfo() said there was sql server support, but it wouldn’t connect to the sql server.

FINALLY, I found a single post that explained it. Turned out my PHP version which was installed by IIS7 was too old! For SqlServer to work – you have to have PHP 5.2.4 or newer.
The help instructions that come with it aren’t specific enough.

Lastly, here’s a great link that goes over how to actually use the sqlserver connection. Good god!

One other thing to note if you’re used to working with .NET or Classic ASP. In PHP the field names are case sensitive.

Written by Tom in: PHP,SQL Server | Tags: ,

MSSQL: Joining another server in a select statment

I’ve often wondered if I could have a select statement that retrieved data from two databases at a time. I just got a SQL Server tip in the mail this morning that walks you through the steps. It’s awesome! Does everything and more. The keyword is OPENROWSET.

Here’s the link:,289483,sid87_gci1353663_mem1,00.html

Example from above article:
SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee'
) AS e1
'C:\Data\Employees.mdb'; 'admin';' ',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM Employees'
) AS e2
ON e1.EmployeeID = e2.EmployeeID
ORDER BY e2.LastName, e2.FirstName

Here’s the MSDN link on OPENROWSET.

Written by Tom in: SQL Server | Tags:

MSSQL Large Sub Query Impossibility

I recently had to query to find out how many records from one table were not in another really large table. When you do a select statement like this in MSSQL it overflows and gives you a response of 0. Not very helpful:

select count(*) from Table1 where Field Not In (Select Field from Table2)

All other ideas failing, I finally resorted to writing a query using a temp table variable:
set nocount on;
declare @Tables table (
PK int IDENTITY(1,1),
Field1 varchar(100), Field2 varchar(100)
insert into @Tables(Field1 , Field2 )

select t1.Field, t2.Field from Table1 t1
left outer join Table2 t2 on
t1.Field = t2.Field;

set nocount off;
select count(*) as Count, Field1 from @Tables
where Field2 is Null
Group by Field1
Having Count(*) < 2;

Talk about complicated! If you have any other better ways of doing this – please let me know!

Written by Tom in: SQL Server |

SQL String Functions came out with a great article titled SQL String User Function Workbench

It has some great sql functions that I’ve spent hours looking for in the past (or had to write myself):

  • Split function
  • SplitLines
  • Within (not from python)
  • EndsWith
  • StartsWith
  • Contains (not from python)
  • Join
  • Parts (not from python)
  • Partition
  • RPartition

    Anyway, I bookmarked it to go back to in the future.

Written by Tom in: SQL Server |

MSSQL Table Size for all tables

As far as I could tell there was no easy way to query MSSQL to get a list of all of its tables and their respective sizes. I wrote a sql script that does this for you.

It’s below:

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
INSERT into @Tables (tablename)

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

Written by Tom in: SQL Server |

@@Identity Best Practices (MS SQL)

I just ran across this post that was referenced from an article written on MSDN.

For the past 2-3 years I’ve been working under the idea that @@Identity would return the identity of the most recently added row in the table you were just working with. Not so – it will give you the identity of the most recently added row in the entire database! I believe, however, that @@identity is limited to working within the connection to the database. For instance, if you have 2 scripts and they both add a row to the database, they would be using separate connections to the database and thus wouldn’t be confused. If your database uses triggers and your trigger adds a row in another table, @@identity will return the identity of that row, not the one you want.

The alternative offered in the article is to use scope_identity(). It acts the same way, only without this little idiosyncrasy.

Written by Tom in: SQL Server |

Choosing MSSQL Data Types

This may not interest many of you, but I found a great article on designing database tables in Microsoft SQL Server 2000. Before reading, I had a general understanding of all the types, but I didn’t have any base for choosing which type to use where.

You may want to check it out:

Written by Tom in: SQL Server |

Intellisense for SQL – Free

SQL Prompt is a great program put out by Red Gate.

They just released a deal where they are giving it away for free until September 1st 2006 (That’s when they will release a new version of the product.)

I downloaded it and tried it out, and it works great – very handy to have when you are dealing with tables with lots of fields, etc. It works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32.

I personally tried it out with Microsoft Query Analyzer.

Here’s the link to the download page: SQL Prompt

Written by Tom in: SQL Server |


It’s fairly easy to take an MDB database and convert it to SQL Server, but how about taking an SQL Server database and downloading it into an MDB file (Microsoft Access)?

This article has some code to do just that:

Written by Tom in: SQL Server |

Update using an inner join (MSSQL)

Just searched the net trying to figure out how to do an update using an inner join. I figured it was possible, and I was right. Here’s how to do it:

SET Table1.Title = Table2.Title
FROM Table1, Table2
where Table1.ID = Table2.ID

If you are using aliases – for instance if you are updating a table you are joining to itself, use this code:

UPDATE Table1Alias
SET Table1Alias.Title = Table2Alias.Title
FROM Table1 as Table1Alias, Table2 as Table2Alias
where Table1Alias.ID = Table2Alias.ID

Another scenario you may encounter is where you want to update a table, but use an inner join to further filter the rows you want to update.

Here’s an example of how to do this:

UPDATE Table1 Set Field1 = Value
FROM Table1 as t1
INNER JOIN Table2 as t2 on t1.Field2 = t2.Field2
WHERE Field3 = Value

That seems too simple, but it eluded me for a long time.

Written by Tom in: SQL Server |

Powered by WordPress | Aeros Theme | WordPress Themes