June 4, 2012 at 6:19 pm
My project involves creating a CLR trigger which will take a jpg file out of an NTFS share in bulk insert into a SQL table.
The CLR trigger is running with @"context connection=true" as the connection string. The first T-SQL statement gets the account number and of the inserted row as well as the file path to the jpeg file.
The second T-SQL statement actually does the bulk insert to a separate table in the database.
My question has to do with what user context or permissions the bulk insert will run as.
First, I tried running the bulk insert using a SQL account that is a member of the db_bulkadmin role.
In this case, I got the error "Transaction context in use by another session." since the bulk insert was running in a SQL connection other than the trigger context.
Next, I tried running the bulk insert using the trigger context connection. Doing this generated an error "Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.)". For testing purposes, I added db_bulkadmin to the domain users group; however the SqlServer service on my SQL server is running under the Local System account.
I would appreciate some recommendations on how to best tackle this challenge so that my CLR trigger can add bulk insert files to a separate table in the same database. I've only built a handful of CLR triggers in this job so I don't have a deep background here.
Thanks!
Andre
June 4, 2012 at 7:46 pm
error: "Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied)
It seems your SQL Service account couldn't access your file. Make sure you grant enough permission for this service account.
June 5, 2012 at 5:40 pm
Andre Ranieri (6/4/2012)
My project involves creating a CLR trigger which will take a jpg file out of an NTFS share in bulk insert into a SQL table.The CLR trigger is running with @"context connection=true" as the connection string. The first T-SQL statement gets the account number and of the inserted row as well as the file path to the jpeg file.
The second T-SQL statement actually does the bulk insert to a separate table in the database.
My question has to do with what user context or permissions the bulk insert will run as.
First, I tried running the bulk insert using a SQL account that is a member of the db_bulkadmin role.
In this case, I got the error "Transaction context in use by another session." since the bulk insert was running in a SQL connection other than the trigger context.
Next, I tried running the bulk insert using the trigger context connection. Doing this generated an error "Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.)". For testing purposes, I added db_bulkadmin to the domain users group; however the SqlServer service on my SQL server is running under the Local System account.
I would appreciate some recommendations on how to best tackle this challenge so that my CLR trigger can add bulk insert files to a separate table in the same database. I've only built a handful of CLR triggers in this job so I don't have a deep background here.
Thanks!
Andre
Why did you decide to use the SQLCLR? Ideally you want to limit the operations you do inside a trigger. You especially want to avoid doing external file system access from within a trigger. Consider what would if your file share became unavailable for some reason. In this case your trigger would attempt to reach it and would wait the prescribed timeout period before giving up and returning an error. During that time however your database transaction is open meaning you are potentially blocking other sessions from accessing the underlying table.
Another option might be to setup a Service Broker queue that you can insert messages into that correspond to each jpg you need to import. This will allow your trigger to complete quickly, and Service Broker guarantees your message will be handled, but on a separate thread, i.e. asynchronously.
If you want more information about how that might work please provide more information about what you;re trying to accomplish inside your trigger and maybe we can help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply