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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Permissions for xp_cmdshell

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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!
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-24 : 15:46:14
Why not just use replication?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -