Do SQL Server 2005/CLR Triggers support .config Files?

Does anyone know if it's possible to use the standard .config file within a CLR Trigger to read properties via the System.Configuration namespace

I guess it's not possible because the CLR Trigger needs to be compiled as an assembly which is hosted by SQL Server

Thanks

Jason

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6d635c2cd23fed7

CLR stuff in SQL Server does not work like those know from "normal" applications. Although you can use the Configuration class to load a configuration somewhere stored on disk, you have to keep in mind, that every namespace / class that you use with are not approved as safe will lower down your security, because you have to mark them as unsafe / external access.

Perhaps any other posters has some other experiences about that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

Do I need these OLEDB properties when opening a database?

Howdy folks!

If I set the following three properties when creating a database, do I need to set the same properties when I open the same database later?

sscedbprop[0].dwPropertyID = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.intVal = 100;

sscedbprop[1].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;
sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[1].vValue.vt = VT_I4;
sscedbprop[1].vValue.intVal = 4091;

sscedbprop[2].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
sscedbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[2].vValue.vt = VT_BOOL;
sscedbprop[2].vValue.boolVal = (a_bEncrypted==TRUE)?VARIANT_TRUE:VARIANT_FALSE;

Thanks!

BTW, why is the max size not 4096?

The first two properties can vary from session to session, but not the third. When you create a database with encryption, it must always be opened with that property (AFAIK). The auto shrink threshold and max database size have no impact on the database file format and thus don't persist.

The 4096 vs 4091 question also baffles me...

|||Awesome, thanks Joao.

Do child and grandchild packages require passwords?

My packages are now in the filesystem. I have given the parent package a password in the properties window, saved the package and moved it to the run location.

I am using the SQL Job Agent to schedule the execution. My step command line reads:

dtexec /FILE "C:\Path\parentpackage.dtsx" /De mypass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Do the the 20 or so child and grandchild packages require passwords to run this way?

Thanks,

IanO

I would say yes, they will require passwords if you are Encrypting with a password. On the children, you might want to consider using DontSaveSensitive instead, and pass in the passwords for connection manager objects via a configuration file or similar.

More importantly, have you tried it for yourself?

Distributor password in distributor Properties

I am using SQL2k with sp4 using push transactional replication.
When I select 'configure publishing, subscribers and Distribution' from the
SQL EM's tool menu\replication, under the distributor tab, there is the
administrator link password, what do they use for password? When I set up
replication for the first time, it doesn't ask me for it so it is unknown to
me. If I change the password, will it affect anything? I also noticed that
during replication setup, it also creates a SQL login for Distribution_Admin
and I also don't know what password it use. Any ideas for these two
passwords? Just curious, why do they set up the password behind the scene?
wingman
Wing
If you didn't set it (remote distributor) this means you have a local
distributor, and it is randomly generated. If you want to change it, you can
use sp_changedistributor_password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I didn't set it. I assume local distributor means it locates in the
same server as the publisher server.
The reason I ask for the password is that I tried to use the 'generating SQL
script' feature in replication to script the setup. But the script purposely
leaves out the passwrod for security reason so in order for me to run the
script, I need to know the password. So should I run the
sp_changedistributor_password and put the new password in the script to make
it work?
Or is there another way to script a replication setup?
Wing
"Paul Ibison" wrote:

> If you didn't set it (remote distributor) this means you have a local
> distributor, and it is randomly generated. If you want to change it, you can
> use sp_changedistributor_password.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The 'old' password doesn't really need to be retained, and sp_adddistributor
can be fed any value you want for the administrative link password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distributor and publisher

Hi All:
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>Now available on Amazon.com
>http://www.amazon.com/gp/product/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>

Distribution Agent

Within Properties of a Publication, on the "Subscription Options", I need to
enable the option:
"Use a Distribution Agent that is independent of other publications from
this database"
However, this option is "greyed-out" on the properties screen.
How can I enable this option?
-- Will G.
Will,
can you try:
sp_changepublication @.publication = 'publication'
, @.property = 'independent_agent'
, @.value = 'true'
I'm at home at the moment and can't test it here.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)