Upgrade Your Drupal Skills
We trained 1,000+ Drupal Developers over the last decade.
See Advanced Courses NAH, I know EnoughSome SQL Server 2008 Express Tips to Help You Along
Submitted by randy on Wed, 03/30/2011 - 09:19
I'm working on another SQL Server 2008 and Drupal 7 project right now. This particular project has a great deal of stored procedures and triggers in it to massage extrenal data to the right format for display in Drupal.
Doing Drupal/PHP development with SQL Server 2008 is easy using the 2008 Express (free) edition for those of us who don't use SQL Server regularily as a database on our development machines. However, there is one small problem I ran in to on this current project: SQL 2008 Express does not have any UI mechanisms in the SQL Management Studio to allow you to manage triggers!
The UI has been pared down as this is a free database engine from Microsoft after all. Rightfully so -- if you want to have all of the features and benefits of SQL Server, you should buy a legitimate license for it (You can't run SQL Express in a production environment anyways).
However for development (especially for my current project), this is an issue that needed to be overcome. In this particular instance, I couldn't use the customer's environment to do development directly on -- just too cumbersome over a vpn without any PHP Development tools loaded in their environment.
So the following few Transact-SQL statements are incredibly helpful for those of us using SQL 2008 Express for trigger management:
To see a listing of all of the triggers in your database:
SELECT * FROM sys.triggers
To delete/drop a specific trigger:
DROP TRIGGER triggername
To see the actual textual data that makes up your trigger:
sp_helptext 'triggername'
Finally, here's a great little stored procedure I put together that produces text output in the SQL management studio to help you cleanly get the text out of an existing trigger:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE _getTriggerCode
@triggerName varchar(100)
AS
BEGIN
DECLARE @txt varchar(500) -- used to store the row data we're picking off
SET NOCOUNT ON;
CREATE TABLE #tmp
(
txt varchar(500)
)
INSERT #tmp EXEC sp_helptext @triggerName--now define a simple cursor to iterate thru the results
DECLARE txtCursor CURSOR FOR
SELECT txt FROM #tmp
OPEN txtCursor
FETCH NEXT FROM txtCursor INTO @txt
WHILE @@FETCH_STATUS = 0
BEGIN
print @txt
FETCH NEXT FROM txtCursor INTO @txt
END
CLOSE txtCursor
DEALLOCATE txtCursor
DROP TABLE #tmp
END
Use the stored procedure like this:
DECLARE @RC int
DECLARE @triggerName varchar(100)
set @triggerName = 'triggername'
EXECUTE @RC = _getTriggerCode
@triggerName
GO
Then just simply look at the Messages tab output in SQL Server Management Studio and voila, you have your trigger code.
About Drupal Sun
Drupal Sun is an Evolving Web project. It allows you to:
- Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
- Facet based on tags, author, or feed
- Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
- View the entire article text inline, or in the context of the site where it was created
See the blog post at Evolving Web