Sometimes I need to run SQL Server Stored Procedures from PHP. Some development managers prefer to keep all the SQL queries stored inside SQL Server rather than embedded into the PHP code directly.
I can understand the philosophy but it’s certainly not something I’m used to. The argument is that if the query is stored in an SP then any member of the team can access and update or fix the query whether they have any PHP knowledge or not.
It kind of makes sense.
Fortunately PHP has some functions to handle this.
Read More >
Posted in PHP Queries SQL Server Tutorials
Tagged with PHP SQL SQL Server
If you’re anything like me you’ll like things to be nice and clean and tidy.
One thing that really bugs me when I launch an application to the world, is ID fields that don’t start at zero.
If you have done any testing on your app then you will, at some stage, have inserted some data into one or more tables along the way.
Fields that have an auto_increment or identity set on them update the number in that field automatically.
Read More >
Posted in Queries SQL Server
Tagged with Query SQL SQL Server
If like me you have a poor memory and you forget where you wrote that wicked piece of code this is the post for you.
The other day I was looking for some code I have written in a Stored Procedure but I couldn’t remember which SP the code was in.
I personally think that this is functionality that Microsoft has missed the boat on, I should be a standard feature but it’s not.
A colleague found s a way round it.
I knew my SP had the text “Service Team” inside it but that was about all I could remember.
Open the SQL Server Management Studio and go to the correct database.
Open a new query and paste this code in. You will need to change the search text to suit your needs but remember to keep the % signs otherwise the search will only find exact matches.
SELECT OBJECT_NAME(ID)
FROM syscomments WHERE [text] like '%Service Team%'
AND OBJECTPROPERTY(ID,'IsProcedure') = 1
GROUP BY OBJECT_NAME(ID)
The only downfall for this code is that you will have to run it on each database on your server.
Posted in Management Studio SQL Server
Tagged with SQL SQL Server Stored Procedure
Have you ever wanted to get just the first record from a table?
As an example I have a table where the user has a sort order for their custom pages. What I need to do is to find the page that should appear first.
My current user is UserID 1. I need to then run a query that will find out the lowest sort order for UserID 1 which is marked in green. The query should tell me that the page I’m looking for is PageID 2.
So here’s the query
SELECT TOP 1 PageID FROM dbo.tblPages WHERE UserID='1' ORDER BY SortOrder
It’s as simple as that. By using the TOP clause I can just return one record from the recordset.
Posted in Queries SQL Server
Tagged with Query SQL SQL Server
From time to time I come across the need to be able to query a database on a different server. There are ways to link servers and such but for a quick one off kind of thing, this is what I do.
SELECT *
FROM OPENDATASOURCE
('SQLOLEDB', 'Data Source=SERVERNAME;User ID=USER;Password=PASSWORD')
.DATABASE.dbo.TABLENAME
Don’t forget that you will need to replace SERVERNAME, USER, PASSWORD, DATABASE and TABLENAME with the correct values for your server connection.
I hope this helps someone out. What’s you preferred method of achieving this?
Posted in SQL Server
Tagged with Query SQL SQL Server
