Keyboard Face

when you find QWERTY imprinted on your cheek - it's time to go to bed.

Tags

asp CSS css menus dreamweaver flash gallery2 hosting iis7 Javascript jquery jquery ui left mac mssql os9 php printer rdp SEO sql sql server windows wordpress

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

by Leave a Comment

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.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20098

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.

http://social.msdn.microsoft.com/Forums/en/ssdsgetstarted/thread/5fa1215f-5104-40f3-ac5c-872c4812f416

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

http://social.technet.microsoft.com/wiki/contents/articles/whitepaper-accessing-sql-server-databases-from-php.aspx

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

MSSQL: Joining another server in a select statment

by Leave a Comment

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:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1353663_mem1,00.html

Example from above article:
SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle
FROM OPENROWSET(
'SQLNCLI',
'Server=SqlSrv1;Trusted_Connection=yes;',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee'
) AS e1
INNER JOIN OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'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.
http://msdn.microsoft.com/en-us/library/ms190312.aspx

MSSQL Large Sub Query Impossibility

by Leave a Comment

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!