Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GregDDDD
Posting Yak Master
120 Posts |
 Posted - 2011-01-24 : 12:28:30
|
| I'm working with another company to put a trigger on a table of demographics data so we can keep our databases in sync with theirs. The updates and changes need to be as real-time as possible. The trigger works, but the xp_cmdshell call fails when data is changed by the client front end.The trigger produces a CSV file when one of about 8 fields is updated or a new one is inserted. This CSV file is then scooped up by a program called Mirth, converted to HL7, and sent to me. I've since learned that this is a permissions issue on the Windows account. I have read some threads about how to make changes to a user account on the Master database, but that is a little more than I am willing to ask of him.I can use the trigger to write the data to a table in the database, but I still need to get it out to a CSV file. It needs to be one file per new row added or a file when a change has happened on a single row. Does anyone have an idea of how I can do this?Greg |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-24 : 12:38:54
|
| using xp_cmdshell from trigger is certainly not a good idea. why dont you something like OPENROWSET to write the details onto csv file?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2011-01-24 : 13:30:17
|
| Thanks, yes it seems using xp-cmdshell is not a good idea, I just couldn't find another way to do it. If fact, now as I read about OPENROWSET all I read about are how to use it to get data IN to the database FROM a text file. I need to go the other way. I do see some examples of using the bcp utility. I'm afraid I might run in to similar permissions issues with that, though. |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2011-01-24 : 14:44:38
|
| {Light Bulb!}The application Mirth, which creates and sends the HL7 messages based on the CSV can connect to the SQL Server DB. In the trigger, I post the results to a table and set a bit field to NULL. Mirth polls the table every few seconds looking for new records with NULL in the bit field. Reads the data, transforms it to HL7, sets to the bit to 1, and sends me the resulting message.Done! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-24 : 15:46:14
|
| Why not just use replication? |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2011-01-24 : 16:16:06
|
| Being more of a programmer and less of an administrator it is not always easy for me to envision how something like replication would work in this situation. Given my limited knowledge of replication I think the real problem would be the scope of the data that we want to have in sync. I could be wrong.The data we are pulling is 8 basic demographic fields from 2 tables (ID, name, dob, address, phone, etc). The 2 tables have 75 columns, maybe more. I don't think they want us to have access to that much data, and all we want are updates and new entries. Essentially, I will get a data dump of the fields, and then this system will ensure they remain the same over time.Greg |
|
|
|
|
|
|
|
|
Permissions for xp_cmdshell