CLR trigger on another schema

Hi,

I'm trying to write a clr trigger. I have a table under a user defined schema, say Myschema.Table1. If I write something like this

[SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")]
I got this error when deploying my assembly on SQl server:
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
Can someone help me

Thank you

This is a known bug. You will need to manually deploy the trigger to the non-dbo schema.

CLR trigger in another schema

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

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

|||This bug has been fixed in SQL 2005 SP1?|||I have SQL 2005 SP1 installed and deploying from Visual Studio is still not possible. I believe the prolem lies in VS not SQL.|||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|||If I remember well, you should be member of sysadmin role on the sql2005 db you are trying to debug

CLR SqlTrigger and Schemas

I cannot get a Trigger to work on a schema table set that is not dbo. For
example with AdventureWorks if I used:
<Microsoft.SqlServer.Server.SqlTrigger(Name:="utrigPersonas",
Target:="Person.Address", Event:="FOR UPDATE")>
When I try to deploy this it returns the error:
Cannot find the object "Persona.Address" because it does not exist or you do
not have permissions.
Can you not create CLR triggers with tables that use schemas other than dbo?You can, but Visual Studio can't -- deploy it yourself (use CREATE ASSEMBLY,
then CREATE TRIGGER) and you should have no problems.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"developer@.stf.com" <developer@.stf.com@.discussions.microsoft.com> wrote in
message news:8F71BCCD-3F05-4CAE-9772-B8483658910A@.microsoft.com...
>I cannot get a Trigger to work on a schema table set that is not dbo. For
> example with AdventureWorks if I used:
> <Microsoft.SqlServer.Server.SqlTrigger(Name:="utrigPersonas",
> Target:="Person.Address", Event:="FOR UPDATE")>
> When I try to deploy this it returns the error:
> Cannot find the object "Persona.Address" because it does not exist or you
> do
> not have permissions.
> Can you not create CLR triggers with tables that use schemas other than
> dbo?

Close to real time sync for data in relational and flat schemas

Hi all,
Can someone point me to the right direction?
I need to synchronize data updates in relational schema with data in it's
flat (denormalized) equivalent in near real time mode.
What is the best way to achieve this if using triggers is not an option ?
Are there any proven approaches or tools (incl. 3rd party) which do that ?
Is there more appropriate microsoft.public.sqlserver.server sub-group for
this topic ?
Any good idea or suggestion is greatly appreciated,
Thank you,
VT
"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
>
A denomalized schema is simply the result of joins in a normalized schema.
That's easily achieved just by using views.
I suspect you want something more than a denormalized schema though. Are you
talking about data warehousing? If so there is a whole industry of products
to support you but I hesitate to suggest anything without knowing more about
what you are trying to achieve.
Have you seen the new Change Data Capture feature in SQL Server 2008?
David Portas
|||"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
Like David pointed out, you can use views, indexed views, and several other
possible alternatives including third-party tools. Can you be more specific
about your situation and ultimate goal?
|||Hi all,
Thank you for your responces.
What I am trying to achieve is to search against flat schema which as it was
rightfully pointed out - same as a join.
Profiling of the search against relational schema shows bulk of the time
with each query goes into joining of the tables.
I think of flat table as a substitute for that join.
Datawarehousing solutions may not be applicable for this as they are not
designed to work in realtime. They are more like batch type processes which
happen periodically.
Using view is great, but problem is that join has outer joins and such view
has nulls and therefore cannot be indexed.
Thanks,
VT
"Mike C#" wrote:

> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Like David pointed out, you can use views, indexed views, and several other
> possible alternatives including third-party tools. Can you be more specific
> about your situation and ultimate goal?
>
>

Close to real time sync for data in relational and flat schemas

Hi all,
Can someone point me to the right direction?
I need to synchronize data updates in relational schema with data in it's
flat (denormalized) equivalent in near real time mode.
What is the best way to achieve this if using triggers is not an option ?
Are there any proven approaches or tools (incl. 3rd party) which do that ?
Is there more appropriate microsoft.public.sqlserver.server sub-group for
this topic ?
Any good idea or suggestion is greatly appreciated,
Thank you,
VT"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
>
A denomalized schema is simply the result of joins in a normalized schema.
That's easily achieved just by using views.
I suspect you want something more than a denormalized schema though. Are you
talking about data warehousing? If so there is a whole industry of products
to support you but I hesitate to suggest anything without knowing more about
what you are trying to achieve.
Have you seen the new Change Data Capture feature in SQL Server 2008?
--
David Portas|||"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
Like David pointed out, you can use views, indexed views, and several other
possible alternatives including third-party tools. Can you be more specific
about your situation and ultimate goal?|||> A denomalized schema is simply the result of joins in a normalized schema. That's easily achieved
> just by using views.
Which can be created in the source database. You can then use transaction replication to replicate
the source database and query off of the destination database (if you want to off-load reporting
from the production db, that is).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1ISdnQ1HG_gX_EnanZ2dneKdnZydnZ2d@.giganews.com...
> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
>> Hi all,
>> Can someone point me to the right direction?
>> I need to synchronize data updates in relational schema with data in it's
>> flat (denormalized) equivalent in near real time mode.
>> What is the best way to achieve this if using triggers is not an option ?
>> Are there any proven approaches or tools (incl. 3rd party) which do that ?
>> Is there more appropriate microsoft.public.sqlserver.server sub-group for
>> this topic ?
>> Any good idea or suggestion is greatly appreciated,
>> Thank you,
>> VT
>
> A denomalized schema is simply the result of joins in a normalized schema. That's easily achieved
> just by using views.
> I suspect you want something more than a denormalized schema though. Are you talking about data
> warehousing? If so there is a whole industry of products to support you but I hesitate to suggest
> anything without knowing more about what you are trying to achieve.
> Have you seen the new Change Data Capture feature in SQL Server 2008?
> --
> David Portas
>|||Hi all,
Thank you for your responces.
What I am trying to achieve is to search against flat schema which as it was
rightfully pointed out - same as a join.
Profiling of the search against relational schema shows bulk of the time
with each query goes into joining of the tables.
I think of flat table as a substitute for that join.
Datawarehousing solutions may not be applicable for this as they are not
designed to work in realtime. They are more like batch type processes which
happen periodically.
Using view is great, but problem is that join has outer joins and such view
has nulls and therefore cannot be indexed.
Thanks,
VT
"Mike C#" wrote:
> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> > Hi all,
> >
> > Can someone point me to the right direction?
> >
> > I need to synchronize data updates in relational schema with data in it's
> > flat (denormalized) equivalent in near real time mode.
> >
> > What is the best way to achieve this if using triggers is not an option ?
> >
> > Are there any proven approaches or tools (incl. 3rd party) which do that ?
> >
> > Is there more appropriate microsoft.public.sqlserver.server sub-group for
> > this topic ?
> >
> > Any good idea or suggestion is greatly appreciated,
> >
> > Thank you,
> > VT
> Like David pointed out, you can use views, indexed views, and several other
> possible alternatives including third-party tools. Can you be more specific
> about your situation and ultimate goal?
>
>

cloning a schema?

Is there a way to clone an entire database, without the data? Thanks for
any tips.Backup / restore + Clean-up the tables through scripts '
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||right click on the database in enterprise manager and generate sql script,
from there you can choose your options and get as detailed as you want.
thanks,
nivek
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Also, you can right-click on the database in the Query Analyzer and choose
Script Object to New Window As -> Create to generate a script for the schema
.
Changing options in the Query Analyzer will allow you to modify the amount
of detail generated (Tools -> Options -> Script Tab).
"Neil W." wrote:

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>
>|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...

> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Thanks for the reply. Sorry to be dense, but which tool are you referring
to? I want to clone all aspects of the schema including triggers and
contraints.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||The article lists several tools, quite simply. Use the one which serves your
purpose and does what
you want it to do. If you want to do this from code, for instance, consider
writing an app that uses
DMO. Etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:%23WTtmWAJFHA.2852@.TK2MSFTNGP09.phx.gbl.
.
> Thanks for the reply. Sorry to be dense, but which tool are you referring
> to? I want to clone all aspects of the schema including triggers and
> contraints.
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
>
>