Creating a SQL Stored procedure to call a CF function

[KomputerMan_com-L7dcfe]

I need to create a method to migrate data from a DMZ inside a firewall.  To do this I was thinking of making a call from the DMZ web server (using CF) to an internal SQL server database stored procedure.  The stored procedure would then call a CF template on the internal web server to pull data inside the firewall.  Not my first choice on implementation but as a consultant I don’t make the rules I create the code to enforce them... Anyway these are the steps I need to follow.

1.)    Call the stored procedure and pass an int value with the call.

2.)    The Stored procedure calls a URL and passes the INT with it. I.E.  http://Mylocalhost/Xferdata/MyXferTemplate.cfm?MyIntValue=”PASSED IN INT VALUE”

3.)    The MyXferTemplate does the actual data migration for me.

My question is how do I create a simple stored procedure to do this on SQL Server 2008???  After looking at the CF documentation I don’t think there will be much of a problem making the call to the stored procedure… its just writing the stored procedure…  Any and all help is greatly appreciated!!!

Have an Ordinary Day...

Kurtis   ~|:-)

[Dave Watts]

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/

[KomputerMan_com-L7dcfe]

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

SET

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';

END

/**************** Code to call the SP ******************/

execute

Migrate_DMZ_Data @MyIntValue = N'5'

[Dave Watts]

The xp_cmdshell system stored procedure doesn't let you "execute" a URL. It lets you execute a shell command. You could write a console program or batch script that accepts a URL as an argument, then call that from xp_cmdshell. Or you could use C# to write your SP, and use that to make HTTP calls directly, as mentioned previously.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

[Owainnorth]

That's also assuming that SQL Server has permissions to execute a shell command, which if set up properly it shouldn't really.

Would it not be far easier to have a CF template do its work, call the proc which inserts into a temp table, then the CF template carries on working?

At the point I find myself bullying technologies into doing things they really don't want to it's normally time to take a step back and rethink.

[Jochem van Dieten]

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.

[KomputerMan_com-L7dcfe]

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   ~|:-)

[Owainnorth]

You could easily speed it up a bit by using a database trigger that kicks off your procedure as soon as CF inserts or edits a row.

As for the callback, I'd guess you'd be needing to write a .NET component which you can run from within SQL.

[KomputerMan_com-L7dcfe]

That is what I am trying to do... When CF inserts a row into the DMZ Database server I want to send the message

to the internal SQL Server to kick off the CF process... Problem is I can't write to the internal SQL server from the DMZ but I can call a SP.  I need the SP to kick off the migration process on the internal CF server. 

Security is such a pain in the butt... Why can't people just be cool and leave things alone that don't belong to them???!!!  

[Owainnorth]

It's not a security issue as such, it's no more than asking CF to store all your data in a relational format - it's not what it's designed for.

Just to clarify, what's the issue with CF doing its thing, calling the proc then continuing in a serial manner? Why does the DB need to initiate a CF page?

[KomputerMan_com-L7dcfe]

The problem is security rules, I don't define them I just have to work with them.

Rule 1.) External CF Server CAN NOT write to internal SQL Server. 

Rule 1a.)  External CF Server can read from an internal SQL Server. So I can call an internal Stored procedure as long as I don't write anything.

Rule 2.) External CF Server CAN NOT talk to any internal Server other than the SQL Servers that tunnels are built for.

When the external CF Server writes to external SQL Server is when I want to call the stored procedure telling the internal CF Server there is a record to migrate.