CLR Trigger deploy to second database

I have a CLR Trigger deployed to a database and working in development SQL Server.

I now need to deploy it to a second database in the same SQL Server instance. I have a script that creates the assembly and each of the four triggers but the triggers crash when launched in the second database.

I am using "Using cn As New SqlConnection("context connection=true")" for the connection and I surmise it is still trying to the original database.

If I use a regular connection can I get the trigger to read a file and update the connection string?

Are there alternative ways of doing this?

Thanks
Paul BrooksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
A context connection uses the same connection where the function itself was invoked. So: no, it's not pointing to the original database.

Do you have a stack trace? Have you reviews the CLR triggers source code? Do you make some assumptions, which are not met in your second database?
0
Paul BrooksAuthor Commented:
Thanks for clarifying that it isn't pointing at the original database.

The second database is a restored backup of the first one so although some data is different the structures are the same.

This is an "on insert" trigger and if I invoke it in the original database it works. Both databases call the same .dll file.

I don't have a stack trace but I'll try to create one.
0
Paul BrooksAuthor Commented:
This stack trace and while the Message is fairly clear when I invoke the same trigger in the original database it doesn't error.

System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  LineNumber=1
  Message="The context transaction which was active before entering user defined routine, trigger or aggregate "SAlloc" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."
  Number=3991
  Procedure="SAlloc"
  Server=""
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at SMS_Express.Sop_Details.sub_allocate_02(String strPickLine) in P:\Software Development\Antar Stockman 2008\SMS Express\SMS Express\Sop_Details.vb:line 280
       at SMS_Express.Order_well.APicker() in P:\Software Development\Antar Stockman 2008\SMS Express\SMS Express\Order_well.vb:line 1559
       at SMS_Express.Order_well.btClearP_Click(Object sender, EventArgs e) in P:\Software Development\Antar Stockman 2008\SMS Express\SMS Express\Order_well.vb:line 1644
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at SMS_Express.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
0
ste5anSenior DeveloperCommented:
Message="The context transaction which was active before entering user defined routine, trigger or aggregate "SAlloc" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."

Check your data which is processed in the trigger in your second database. Seems like there is some unique constraint in your source database, either declared or inherent, but not in the second database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul BrooksAuthor Commented:
It must have been the data, thanks.

I checked in one database, ran a backup which I restored to the second database then tested it again and it was fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.