SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending data from MS SQL Server do PostgreSQL server using CLR triggers


Sending data from MS SQL Server do PostgreSQL server using CLR triggers

Author
Message
pgmoschetti
pgmoschetti
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38287 Visits: 19417
First thought would be the PERMISSION_SET, check this out on the subject: http://technet.microsoft.com/en-us/library/ms345101.aspx
Ciao Cool
pgmoschetti
pgmoschetti
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38287 Visits: 19417
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?

Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202785 Visits: 41943
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38287 Visits: 19417
I agree with Jeff here, a trigger is not a good idea. Could you shed a little more light on the setup?

Eirikur
pgmoschetti
pgmoschetti
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202785 Visits: 41943
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. :-D 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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pgmoschetti
pgmoschetti
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search