September 22, 2013 at 6:44 pm
Hi,
On one hand, I would like to use SQL Server database to store all the data and use this database for all type of editing.
On the other hand, I would like to use Elastic Search to run queries.
Therefore I need both "databases" to systematically be synchronized.
Since both "systems" are independent from each other, in order to make sure that both are in sync, I thought of the following way of proceeding:
I would use a stored procedure with 2 transactions:
Transaction B would apply all necessary modifications to the data, stored in SQL server database
Transaction A would contain Transaction B but also calls to Elastic Search via HTTP POST. If this HTTP Post returns 200 (OK), then Transaction A is committed.
This would give something like:
BEGIN TRANS A
BEGIN TRANS B
... do everything linked to SQL Server database
COMMIT TRANS B
-- Invoke ElasticSearch through HTTP
COMMIT TRANS A
In order to invoke Elastic Search via HTTP, from SQL Server Stored Procedure, I found the following piece of code:
DECLARE @uri varchar(8000),
@output_XML varchar(8000),
@result int,
@object int
set @uri = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT'
EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUT
IF @result <> 0
BEGIN
RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @uri, false
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod Open failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, SEND, NULL, ''
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod SEND failed', 16,1)
RETURN
END
EXEC @result = sp_OAGetProperty @object, 'responseText', @output_XML OUTPUT
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
SELECT @output_XML StockQouteXML
EXEC @result = sp_OADestroy @object
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
Everything looks fine EXCEPT that ServerXMLHTTP has a limit of 8K for the POST and I need to POST much more than 8K ...
Does anyone have a solution to suggest?
Many thanks in advance,
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply