1

I have an idea for an application, but ideally it would rely on being able to write a sort of "sql rewriting" engine for MSSQL (much the same way that HTTP servers can have "URL Rewriting" modules) which could intersect sql statements before the server handles them and run some other code first.

Is this even possible with MSSQL or will I need to pursue an alternative method of achieving my goal?

1
  • Doing this would be extremely complex as the options available within a T/SQL statement are huge. What's the end result that you are looking for?
    – mrdenny
    Jul 14, 2009 at 3:40

5 Answers 5

1

The question is not "how" to make a rewriting but "where" you want rewriting to happen. You said you want something to be like in IIS, but this is not possible in MSSQL. In IIS you have request processing pipeline and there are many points where you can insert your custom code into this pipelene (i.e. create your own ISAPI extention, HTTPModule, events in Global.asax)

In SQL Server there is no SQL pipeline that is opened for developers. When SQL request came to SQL server, you cannot rewrite it. Triggers BEFORE or INSTEAD OF update/insert/delete will not solve the problem if you want to replace the table name in original query because if this table doesn't exists, you will get an exception prior you will be able to execute any custom business logic in triggers.

However you can "rewrite" SQL on the client (not on SQL server) by creating the custom data provider (if you have ADO.NET application). See MSDN - Implementing a .NET Framework Data Provider. This is not an easy task and will require a lot of efforts. In you code you may reuse most standard features of standard MSSQL Data Provider (System.Data.SqlClient) and override only functions where you want to parse SQL and replace certain commands.

Please also look on http://sqlfaultretryprovide.codeplex.com/ - this project was to build a custom .NET data provider to SQL Server that can provide automatically "retry on error" function. So this is not a SQL rewriter, but can be used as an example to build your own.

1

You can use triggers for INSERT, UPDATE, and DELETE, but there isn't an equivalent trigger for SELECT.

An alternative approach would be to write a program that intercepts TDS calls, modifies them, and passes them on to SQL server. So you would have your program listen on port 1433 or something and in your setup program change the SQL server port to listen on port 1434.

1
  • Such an application would be called an SLQ proxy.
    – Chris Nava
    Jul 14, 2009 at 5:45
1

You're probably looking at creating a business layer into SQL. If you are trying to modify an existing app then you may have some trouble as the SQL adapters are all proprietary, but if your apps can be modified, you could probably write a business layer to sit on the DB, which would process the commands into the SQL server using a shared memory connection.

Another option is a magic CLR stored procedure which you feed the SQL to which then edits it and runs it against the SQL database (using a context connection which is even faster than shared memory).

Beware though that the SQL server is pretty well written, something like your trying to achieve would probably best be achieved at the client using an API so that the processing is spread by the number of clients and allowing the SQL server to optimise the actual SQL being run.

1

When you say "URL Rewriting" do you really mean "URL"? Because I wasn't aware that SQL Server was ever accessed by URL.

If you meant that as an analogy, could you please clarify?

2
  • Yes. The same way that a web server can rewrite URLs I would (preferably) like MSSQL to "rewrite" SQL. Jul 14, 2009 at 1:18
  • I fixed the question to be a bit more clear. Thank you :) Jul 14, 2009 at 1:19
0

You can write triggers that employ .net classes.

http://www.15seconds.com/issue/041006.htm

http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all

http://www.google.com/search?rlz=1C1_____enUS397US397&sourceid=chrome&ie=UTF-8&q=sql+.net+trigger

good luck.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .