Home MS SQL Server

Accessing XML column of SQL Server from PHP (PDO)

One cool feature that SQL Server provides is the XML data type. This enables to save XML data as it is just like any other data type in the database.

Once we put the xml, then we can apply XPath to query the data from the sql server nifty IDE.

But, when you try to use the same query outside of the IDE, you would not be able to get the data as expected.

I was working on PHP project where the data (XML) has to be retrieved from SQL Server. Here is the hack step by step

1. Configure your PDO
Say: 


try {
    $hostname = "host";            //host
    $dbname = "dbname";            //db name
    $username = "user";            // username like 'sa'
    $pw = "pass";                // password for the user

    $dbh = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "n";
    exit;
  } 
**taken from php.net

2. Once you got the handle ($dbh) add the following on the handle:


$handle = getHandle(); //function like above to give you the handle
$handle->exec('SET QUOTED_IDENTIFIER ON');
$handle->exec('SET ANSI_WARNINGS ON');
$handle->exec('SET ANSI_PADDING ON');
$handle->exec('SET ANSI_NULLS ON');
$handle->exec('SET CONCAT_NULL_YIELDS_NULL ON');

That is all, this are actually additional parameters that sql server would add when sending xml based query to the engine.

Happy XMLing :)

Renaming Tables and Columns in SQL Server

Changing table or colum in SQL Server is as follows,
To change the name of the table:
sp_RENAME ‘tbloldname’, ‘tblnewname’;
To chagne the name of the column:
sp_RENAME ‘tbl.columnoldname’,’columnnewname’,’COLUMN’;

** You might face a problem on changing names of enforced (integrity) columns.

Using Checks

One of the handy tools regarding the constraint in SQL Server is check.
This simple, but powerful, tool allows us to use column based constraints on the data.
Say you have a table with age column. If you want to add constraint on age for values less than or equal to zero and above 100 – thinking we would live 100 years…
ALTER TABLE tbl ADD CONSTRAINT age_constraint CHECK(age < 100);
That is all.
If I try to run a query like this..
INSERT INTO tbl (age,..) VALUES (-12);
Then the engine would tell me that this cannot happen.

How to add and drop primary key in SQL Server

If you want to add primary key after you create the table in the sql server, then
1. go to Microsoft SQL Server Management Studio
2. Right click on the database and select new query
3. ALTER TABLE tbl_name ADD PRIMARY KEY(pr_key_column);
tbl_name – name of the table you want to assign primary key
pr_key_column – column/field name which you want it to be primary key

If the pr_key_column is allowed null values, then you can’t assign it as primary key. So if that is the case use this query first:
ALTER TABLE tbl_name ALTER COLUMN pr_key_column datatype not null;

To revoke primary key:
ALTER TABLE tbl_name DROP CONSTRAINT(pr_key_column);

Previous releases of Microsoft Visual Studio 2008 failed error

While you are trying to install MS SQL Server 2008 on the machine which already has Visual studio, you might come up with this problem.

Here is the remedy.
Go to command prompt and move to the MS SQL Server installation DVD like:
C:…>E: – assuming your DVD drive letter is ‘E’
Then provide the following command:
SETUP /action=install /skiprules=vsshellinstalledrule
While you are installing, you might come up with error message but pass it selecting ok.

The other solution might be the trivial one – uninstall VS and install MS SQL Server then install VS – :-(
I hope it will help a lot.