April 11, 2014 at 5:06 am
I need to send data from a MS SQL Server table to a PostgreSQL table on INSERT/UPDATE. I was thinking about developing a CLR trigger on the source SQL Server table: the assembly must then reference the npgsql.dll and the mono.security.dll (http://npgsql.projects.pgfoundry.org/), establish a connection to the PostgreSQL server, send the data, close the connection.
If I test the above mentioned dll in a desktop application, no problem.
After developing the CLR trigger, when I try to deploy the trigger to SQL Server and I try to create the assembly first using the next T-SQL code:
create assembly SimpleTrigger
authorization dbo
from 'd:\TTC\ArchivioCircolare\Lavoro\Tests\SimpleTrigger\SimpleTrigger\bin\Debug\SimpleTrigger.dll'
WITH PERMISSION_SET = unsafe
go
then I got the following exception:
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'SimpleTrigger' failed because assembly 'Mono.Security' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::SquarePositive][mdToken=0x600005f][offset 0x0000003C][trovato address of Int32][previsto unmanaged pointer] Tipo ...
Any suggestion? Any other way to send data from MS SQL Server to PostgreSQL Server?
Thank you in advance, regards
Pier
April 11, 2014 at 8:19 am
First thought would be the PERMISSION_SET, check this out on the subject: http://technet.microsoft.com/en-us/library/ms345101.aspx
Ciao 😎
April 11, 2014 at 9:33 am
Hello Mr Eiriksson,
I plaied around a little with the PERMISSION_SET and the UNSAFE setting gets the best result, i.e. what you can read in my previous post (the other PERMISSION_SETs just required the UNSAFE PERMISSION_SET, even if I read it is a little dangerous).
Do you know of any other way to send data from SQL Server to another type of database. I am also considering Service Broker and an external .NET application that read messages and write autonomously into the other server.
Regards
April 11, 2014 at 9:39 am
It all depends on how quickly you have to get the data from A to B, first option would be a linked server (via ODBC), have you tried this?
😎
April 11, 2014 at 12:42 pm
Gosh... don't do this with a trigger. If the destination can't be reached for some reason, your source system is dead. Also, the scope of the INSERTED and DELETED tables in the trigger is that they won't be available directly and you'll need to make copies of them in some sort of an array. You're begging for this process to be slow and VERY memory intensive.
My recommendation would be to have the trigger (and NOT a (bleaaahhh!!!!) generic trigger, for the same reasons) write the data to a permanent staging table and have a job do the transfer once a minute.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2014 at 1:45 pm
I agree with Jeff here, a trigger is not a good idea. Could you shed a little more light on the setup?
Eirikur
April 13, 2014 at 12:48 am
Thank you all for your suggestions. Short description of the system:
- the aim is production tracking
- there are 2 PCs each with its own MS SQL Server and a software that collects production data and feeds its database. Only one pc is active at a time: the other is a hot backup, i.e. powered on, but doing nothing. The database in the hot backup is synchronized by the primary pc using MS Sync Framework
- last but not least the factory has another system running PostgreSQL which must be updated continuously with production data as well.
That's why I was thinking about triggers firing on the primary pc to update PostgreSQL.
What about Service Broker? I could send messages in those triggers and then let a .NET application collect them and feed the Postgre database. Anyone who has esperience of Service Broker?
April 13, 2014 at 8:26 am
I don't know if it's an option or not but this reminds me of a very early computer game that I played in the evenings after having a nice Brontosaurus steak for dinner. 😀 I got "stuck" in the game and it wouldn't let me move up, down, left, right, forward, or back but kept asking me what I wanted to do. I was ticked off because it seemed like it was an easy black'or'white question. That's when it struck me and I typed "change color" and it let me proceed.
The problem is similar here. You have SQL Server on two boxes on one side and they have absolutely no problem with data transfers from one to the other. You want to get the data to another box with a different SQL engine and are having great difficulty in doing so. You'll continue to have this problem for future "adventures", as well.
Since SQL Server has some nifty and high performance built-in tools to transfer the data, wouldn't it be easier, at this point, to "change color" and redact the Postgre SQL Server to be an MS SQL Server (SQL Express might even be enough and it's FREE) to solve not only the current problem but all future problems of the same and different nature?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2014 at 1:09 pm
Thank you for your answer Jeff. This was also one of my thought but there are 2 cons:
- 10gb limits as of version 2012 express and the database should store at least 10 years production
And
- the other box runs Linux and they are in love with Postgre
Do you think that Service Broker is not a good solution? Why? Performance, memory, possible memory leaks?
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply