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 to install microsoft outlook ?

I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.
Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegr oups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegr oups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>
|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchange.
HTH
"craigkenisston@.hotmail.com" wrote:

> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>

Do I need to install microsoft outlook ?

I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchang
e.
HTH
"craigkenisston@.hotmail.com" wrote:

> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>

Do I need to install microsoft outlook ?

I want to be emailed when certain alerts are trigger.
There's a place where I'm told I need to install microsoft outlook in
order to do this.
Doesn't come SQL Server with some email capabilities installed ?
The IT guys don't allow me install outlook in the server.
What else can I do ? Even if I need, I would program a command-line
email client in an hour if needed, but I wonder if I would be able to
use it.Craig,
See:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||Craig,
See:
http://www.aspfaq.com/show.asp?id=2403
and
http://sqldev.net/xp/xpsmtp.htm
HTH
Jerry
For a non-Outlook environment see:
<craigkenisston@.hotmail.com> wrote in message
news:1129050195.157365.278430@.g44g2000cwa.googlegroups.com...
>I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>|||SQL Mail and SQL Agent mail need a mapi compliant email client to work. It
does not have to be outlook and your mail server does not have to be Exchange.
HTH
"craigkenisston@.hotmail.com" wrote:
> I want to be emailed when certain alerts are trigger.
> There's a place where I'm told I need to install microsoft outlook in
> order to do this.
> Doesn't come SQL Server with some email capabilities installed ?
> The IT guys don't allow me install outlook in the server.
> What else can I do ? Even if I need, I would program a command-line
> email client in an hour if needed, but I wonder if I would be able to
> use it.
>sql

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...

quote:

---

> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks

---

|||> What is the OS of ServerA and Server B?

quote:

---

>

---

The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/defaul...kb;en-us;306843
If not solved with that, reply one more.
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...

quote:

---

> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000

---

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...
> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks|||> What is the OS of ServerA and Server B?
>
The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
If not solved with that, reply one more.
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...
> > What is the OS of ServerA and Server B?
> >
> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000