February 3, 2013 at 4:33 am
Let me try with first defining what I want to achieve, as I'm very new to SQL I don't know what tools/methods to use.
When an insert happens in a specific table that has a specific value (eg. "Info") run a script (iim thinking sqlclr?) that takes information from that insert with it (computer name, user SID in my case) and go to that computer name to get volatile information stored in the registry on that computer name.
Once that info is retrieved, insert it back into a table (different from the one that triggered the script to begin with) but link it with the ID field from the insert that triggered.
I have tried with a trigger but if that fails my insert never happens. IMO trying to understand if DDL/DDM and sqlclr could help me here?
February 3, 2013 at 7:40 am
That's a trigger, well the insert into another table part is.
For an insert to read from the client machine's registry, you will need to embed that functionality into the client application, as at the point that the insert happens in SQL, there's no call back to the client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2013 at 10:07 am
If possible i would like to avoid triggers, as if something happens to my trigger (fails for whatever reason) the original INSERT i triggered on wont happen. Thats why i thought of sqlclr - if i understand that correctly i could write sqlclr code that would make that call to the remote computer, gather the data from the registry and insert it back into a table or am i missing something there?
February 3, 2013 at 10:18 am
Missing, no, seriously, majorly overcomplicating, yes. For what you describe you'll need unsafe CLR, elevated permissions on the client computer (so that SQL can talk to the client machine), lots of error handing, complex transactional handling and you'd still need a trigger to call it.
Personally I'd just have the client app responsible for both inserts if registry data from the client is necessary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply