Creating a SQL Stored procedure to call a CF function

I see what you are saying Dave… What I didn’t think about was in order to run a CFM template I would need to launch some sort of web browser to run them in from the SQL server machine.  I don’t think I need to launch a web browser to call a web service though… 

What I am currently doing is what Owain and Jochem were talking about.  I have three processes that are scheduled to run every third minute effectively making the internal CF server poll the DMZ SQL Server every minute looking for records to be migrated inside the DMZ.  I would like a more elegant way of telling the CF Server there are records waiting to be migrated inside.   The current process works but is becoming unwieldy as the templates that run every minute now have around 8,500 lines of code between the three of them.

I am allowed, by security rules, to run read only queries on the internal SQL Servers but I am not allowed to write to them or to talk directly to the internal CF Servers where my migration logic is running.  What I was hoping I could do was to send a message to the internal SQL Server when there is a record to be migrated.  The SQL Server would then send a message to the CF Server telling it there was an external record waiting to be pulled inside the firewall. I wanted the stored procedure to send that message…  Can you think of a way to have the SQL Server send a message to the CF Server and if so what would it look like???

I have been doing some reading about the SQL Server being able to invoke a web service, which is pretty much what I wanted to accomplish by sending a URL request…  Down side is I am proficient in CF, AS3, and MXML…  I don’t use .net, VB, or any of the Microsoft technologies to develop my apps. Nor do I have any of the tools required to write and or compile such things…

Thank you all for your insights!!!

Have an Ordinary Day...

Kurtis   ~|:-)

I would recommend a different model. Add a table to your database and have the webserver in your DMZ insert URLs or messages or whatever there. Then schedule a task on your internal webserver to poll that table every minute and whenever it finds a record process it to update the data internally.

So far this is what I got... When I "execute' this code in SQ

L Server management studio it compiles without any errors but when I run it I get the message "http: is not recognized as an internal or extermnal command."

/**********************************  Code to create the SP **************************/

USE

[KDLTransfer]

GO

/****** Object: StoredProcedure [dbo].[Migrate_DMZ_Data] Script Date: 03/07/2011 16:48:50 ******/

SET

ANSI_NULLS ON

GO

QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: KDL

-- Create date: 3/7/2011

-- Description: Migrate data

-- =============================================

ALTER

PROCEDURE [dbo].[Migrate_DMZ_Data]

-- Add the parameters for the stored procedure here

@MyIntValue

int = null

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

EXEC xp_cmdshell 'http://localhost/IREC/KDL/KDL.cfm?@MyIntValue, no_output';

I don't think you can do that in Transact-SQL. So, you'll probably need to write your stored procedure in C# (or another .NET language, I guess):

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

You may also have to talk to your network administrator to allow outbound traffic from your database server - it's fairly common (and a security best-practice) to block outbound traffic from your database server by default.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/