CLR trigger in another schema

CLR trigger in another schema

• sharonl327

  If I remember well, you should be member of sysadmin role on the sql2005 db you are trying to debug
  

• María

  This is a known bug in Visual Studio that was postponed to a later release.  You can submit a comment/vote on it here: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx feedbackid=3732fb5d-55b0-4888-a101-090360681c38

  There is a pretty easy workaround however to drop/create the trigger yourself to the schema you want using predeployscript.sql and postdeployscript.sql in your Visual Studio Project.  This also lets you use SQLCLR debugging from VS.

  
  
  

• sburggsx

  I have two problems; A simple Insert Trigger.
  
  Problem 1:
  If I use the example in MSDN and follow all the direction to the letter
  but I get this error and I know it exists!
  
  Cannot find the object "[dbo].[Users]" because it does not exist or you
  do not have permissions.
  
  If I remove the attribute
  [Microsoft.SqlServer.Server.SqlTrigger(Name = "UserUpdate", Target =
  "[dbo].[Users]", Event = "FOR INSERT")]
  
  It compiles okay, I have to your postdeployscript.sql to deploy the
  Trigger
  
  Problem 2: Kinda show stopper for me
  
  If I employ above work around using postdeployscript.sql every thing
  works, my test scripts fire up the trigger and all the right rows are
  affected but no break point is hit in C# trigger code. It just passes
  through the breakpoint, NO red circle with exclaimation mark in it! No
  errors, but this outout;
  
  Auto-attach to process '[2112] [SQL] entropy' on machine 'entropy'
  succeeded.
  Debugging script from project script file.
  
  (2 row(s) affected)
  (0 row(s) returned)
  Finished running sp_executesql.
  The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
  (0x0).
  The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
  (0x0).
  The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
  (0x0).
  The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
  (0x0).
  The program '[2112] [SQL] entropy: entropy\sqlexpress' has exited with
  code 0 (0x0).
  
  If I fire up msvsmon.exe and hit the debug button in VS.NET It says;
  
  31/08/2006 8:50:41 AM ENTROPY\p4r1tyB1t connected.
  
  
  ANY HELP WOULD BE GREATLY APPRICIATED
  
  
  System State Information:
  Login account is part of Administrator account.
  VS.NET 2005 Pro on Core Duo Machine 1 GB Ram
  Windows XP SP2
  I have set proper permission in DCOM configuration
  TCP/IP protocol is enabled, firewalls are not running
  I am also a member of sysadmin group
  SQL Expresss
  CLR Debuggin is enabled
  Debug generation is set to full
  Assembly is marked as unsafe (It does not matter, same problem even
  safe)
  Project configuration is debug
  Permission in msvsmon.exe is also set to my login account, which is
  admin account
  

• Eytan Seidman

  postdeploy.sql and predeploy.sql should be in ypur project root. no script is needed in post-build event

  
  

• nittany92

  This bug has been fixed in SQL 2005 SP1
  

• tr00per

  Ok. Here is the solution. Comment the SqlTrigger Attribute. Write the following postdeployscript.sql
  
  

  USE [TestSQL2k5]

  GO

  CREATE TRIGGER [Trigger1] ON [MySchema].[Table1] AFTER INSERT AS

  EXTERNAL NAME [CM.Library.Blocks.SecurityEngine.SQL.Functions].[Triggers].[Trigger1]
  
  deploy assembly. VS SQLCLR debug works fine

  
  

• Peter Bauwens

  I have SQL 2005 SP1 installed and deploying from Visual Studio is still not possible. I believe the prolem lies in VS not SQL.
  

• entanglement

  Thanks. Can you supply me an example of postdeploy e predeploy scripts What should I write in the SQLTrigger attribute Or should I comment out this attribute
  
  
  

• Alex281

  to Haplo_69:
  which folder should i save the postdeployscript.sql
  when and how should i call the postdeploysript.sql
  
  i have tested your solution:
     1. create a postdeployscript.sql at 'test scripts' folder in project directory
     2. in project post-build event command, wrote script to call the postdeployscript.sql
  but it failed,
  
  

  
  

• J. Dev

  I mean: postdeployscript.sql and predeployscript.sql of course

  
  

• Catana

  Ugh... that's sad. :(

   

  Maybe Niels Berglund's MSBUILD task can help with this:

   

  http://www.sqljunkies.com/WebLog/nielsb/archive/2005/05/03/13379.aspx

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Haplo_69@discussions.microsoft.com> wrote in message news:95f705e6-a736-44ba-8f20-41cc0d5843cd@discussions.microsoft.com...

  Hi,
  
  I'm trying to write a clr trigger. I have created Myschema schema under schemas folder and Table1 under this schema (i.e.: Myschema.Table1). 
  I created a clr trigger and tagged it with the Sqltrigger attribute as shown here:
  

  [SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")] 
  
  When I try to deploy my assembly from VS 2005 I got the following error:
  Cannot find the object "Myschema.Table1" because it does not exist or you do not have permissions
  
  If I move Table1 in the dbo schema the assembly is deployed succesfully
  
  If I add the assembly from the Assemblies folder in sql2005 db, it deploys successfully. then I can define a trigger using this code
  
  CREATE TRIGGER [SampleTrigger] ON [Myschema].[Table1] AFTER INSERT AS

  EXTERNAL NAME [CM.Library.Blocks.SecurityEngine.SQL.Functions].[Triggers].[SampleTrigger]
  

  In this manner, the trigger fires successfully but I'm not able to debug it from VS 2005
  
  Can someone help me Thanks
  
  Thank you

  
  
• CLR trigger in another schema