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
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 MachanicPro SQL Server 2005, available nowhttp://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 permissionsIf I move Table1 in the dbo schema the assembly is deployed succesfullyIf I add the assembly from the Assemblies folder in sql2005 db, it deploys successfully. then I can define a trigger using this codeCREATE 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 2005Can someone help me? ThanksThank you|||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.
|||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?|||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 
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,
postdeploy.sql and predeploy.sql should be in ypur project root. no script is needed in post-build event
|||I mean: postdeployscript.sql and predeployscript.sql of course
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|||If I remember well, you should be member of sysadmin role on the sql2005 db you are trying to debug
