Executing an insert in a linked server.

Hi:
Im performing an insert in a table that has a trigger.
The propossal of this trigger is to keep update two
tables when an insert occurss in server A; it starts a
trigger to insert a row in one table in server B
The servers are linked and the user has permission to
perform insert in the remote server. Im getting the
following error:
Unable to start a nested transaction for OLE DB
provider 'SQLOLEDB'. A nested transaction was required
because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more
transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013:
ISOLEVEL=4096].
Can anybody helpme'
Regards"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
> Hi:
> Im performing an insert in a table that has a trigger.
> The propossal of this trigger is to keep update two
> tables when an insert occurss in server A; it starts a
> trigger to insert a row in one table in server B
> The servers are linked and the user has permission to
> perform insert in the remote server. Im getting the
> following error:
> Unable to start a nested transaction for OLE DB
> provider 'SQLOLEDB'. A nested transaction was required
> because the XACT_ABORT option was set to OFF.
That is - set XACT_ABORT ON at begining of your transaction
> [OLE/DB provider returned message: Cannot start more
> transactions on this session.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionLocal::StartTransaction returned 0x8004d013:
> ISOLEVEL=4096].
>
> Can anybody helpme'
> Regards|||Regards, I appreciate your help
>--Original Message--
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
>> Hi:
>> Im performing an insert in a table that has a trigger.
>> The propossal of this trigger is to keep update two
>> tables when an insert occurss in server A; it starts a
>> trigger to insert a row in one table in server B
>> The servers are linked and the user has permission to
>> perform insert in the remote server. Im getting the
>> following error:
>> Unable to start a nested transaction for OLE DB
>> provider 'SQLOLEDB'. A nested transaction was required
>> because the XACT_ABORT option was set to OFF.
>That is - set XACT_ABORT ON at begining of your
transaction
>> [OLE/DB provider returned message: Cannot start more
>> transactions on this session.]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionLocal::StartTransaction returned
0x8004d013:
>> ISOLEVEL=4096].
>>
>> Can anybody helpme'
>> Regards
>
>.
>

Executing a Views from a Trigger

Hi,

I am new to using SQL. I want to be able to exucute a query that I
place in a view. I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Thank you.

PhilippeForgot to mention, I am using SQL 2000.

Thank you.|||Hi,

> I am new to using SQL. I want to be able to exucute a query that I
> place in a view.

You don't execute queries inside views. You just get to use the
view as if it were a table.

Why would you want to "execute the query"?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01|||Hi

What exactly do you mean by "call the views every time the data change in
the selected table."?

When the data is added/updated/deleted from the underlying table the View is
automatically updated. (It is just a select statement).

Are you concerned about updating the user interface where the View is used
as a source for a datgrid or something. If so you have to find some way to
tell the Client program to requery the database, perhaps using the View.
when a database update occurs.

--
-Dick Christoph
<solidsna2@.gmail.com> wrote in message
news:1141226630.058373.30490@.e56g2000cwe.googlegro ups.com...
> Hi,
> I am new to using SQL. I want to be able to exucute a query that I
> place in a view. I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01
> Thank you.
> Philippe|||I am working with an application that use SQL to store it's data. What
I am trying to achieve is to execute the query below every hour.

UPDATE Tbl1
SET Tbl1.Flag = '0'
WHERE Tbl1.Flag <> '0'

UPDATE Tbl1
SET Flag = '1'
WHERE Tbl1.Key = (SELECT Tbl2.Key
FROM Tbl2
WHERE Tbl2.Key = Tbl1.Key
AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
BY Tbl2.Key)

DELETE Tbl2
WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate

The Query does exactly what I want it to do. Now I want to launch this
query from the application I was talking about earlyer. So I thought
that if I use a trigger that will be iniated by a script in my
application by changing a value to 1 or 0 every hour. This will then
activate the SQL trigger that will execute the query above. I strore
the query in a views. So that is why I ask how to execute a views with
a trigger.

Maybe this is not the way to go, but I hope you can understand what I
am trying to do.

Thank you again.

Philippe|||Hi

If you want to execute this every hour, this would be better handled in a
scheduled job. Which can be scheduled to run a SQL Statement or stored
procedure every hour.

Although you could schedule the application to run an update query (without
a Trigger or View) every hour, what if the application isn't running all the
time? or if there are muttiple instances of it running?

One point of clarification you cannot execute and Update Query in a View.

-Dick Christoph

--
<solidsna2@.gmail.com> wrote in message
news:1141233438.780742.274780@.t39g2000cwt.googlegr oups.com...
>I am working with an application that use SQL to store it's data. What
> I am trying to achieve is to execute the query below every hour.
> UPDATE Tbl1
> SET Tbl1.Flag = '0'
> WHERE Tbl1.Flag <> '0'
> UPDATE Tbl1
> SET Flag = '1'
> WHERE Tbl1.Key = (SELECT Tbl2.Key
> FROM Tbl2
> WHERE Tbl2.Key = Tbl1.Key
> AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
> BY Tbl2.Key)
> DELETE Tbl2
> WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate
> The Query does exactly what I want it to do. Now I want to launch this
> query from the application I was talking about earlyer. So I thought
> that if I use a trigger that will be iniated by a script in my
> application by changing a value to 1 or 0 every hour. This will then
> activate the SQL trigger that will execute the query above. I strore
> the query in a views. So that is why I ask how to execute a views with
> a trigger.
> Maybe this is not the way to go, but I hope you can understand what I
> am trying to do.
> Thank you again.
> Philippe|||How do I run a SQL statement or stored procedure every hour?

It can not run the the query all the time. I am doing other
manipulation withing my application. The 2 jobs needs to be
synchronise +- 5 min.

Thank you again for the help.

Philippe|||(solidsna2@.gmail.com) writes:
> How do I run a SQL statement or stored procedure every hour?

As Dick said, schedule a job to run from SQL Server Agent.

> It can not run the the query all the time. I am doing other
> manipulation withing my application. The 2 jobs needs to be
> synchronise +- 5 min.

In SQL Agent, a job can have several steps that executed in order. This
may be something for you.

It is difficult from your abstract definition to say whether you are
on the right track at all. If you can describe the underlying business
problem, it is more likely that you will get useful advice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

executing a trigger

HI all,
is the only or perhaps the best way of executing a tables trigger via
another trigger or SP by actually updating said table
I have a calulation in table a update trigger. It is based on related values
in table b But if those value in table b are change, table a's values need
to be recalculated.
Am I on the right track, or is there a betterway of doing this.
Thanks
RobertIf you don=B4r have control about the process changing the first table,
thats the only way. If yxou have the chance to control the process, the
logic and the statements fired, just add another statement which does
the update of the second table within the same transaction.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Robert
In that case you need to use a trigger .Create a trigger on table b and
check if the data has changed run a calculation on table Actually if you'd
post samle data and an expected result we'd suggest may be a better solution
"Robert Bravery" <me@.u.com> wrote in message
news:eMqdx6aSGHA.5036@.TK2MSFTNGP12.phx.gbl...
> HI all,
> is the only or perhaps the best way of executing a tables trigger via
> another trigger or SP by actually updating said table
> I have a calulation in table a update trigger. It is based on related
> values
> in table b But if those value in table b are change, table a's values need
> to be recalculated.
> Am I on the right track, or is there a betterway of doing this.
> Thanks
> Robert
>|||HI,
Thanks to all,
Uri, I'm still in design phase with this one. trtying to cover all my bases.
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLS2eGbSGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Robert
> In that case you need to use a trigger .Create a trigger on table b and
> check if the data has changed run a calculation on table Actually if
you'd
> post samle data and an expected result we'd suggest may be a better
solution
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:eMqdx6aSGHA.5036@.TK2MSFTNGP12.phx.gbl...
need
>

executing a package from T-SQL

Is it possible to execute a package in a trigger or stored procedure ?

For most practical reasons, no.

You could use xp_cmdshell to call DTEXEC which will execute a package. This will run your package synchronously, so doing this in a trigger would generally be a very bad idea, as this would block the transaction from completing during the execution. If trying to reference the data from the transaction that invoked the trigger then you will be blocked as well as this has not been committed yet. Long running transactions are generally a very bad idea.

Another method would be to create schedule job that calls the package. You could then call sp_start_job to set this off. This would be asynchronous, so has some benefit potentially.

For both options, the level of security required is generally in excess of what a normal user should be given, and therefore this precludes such methods in most environments.

|||

thanks, the second option was what I needed

executing a DTS package

a scheduled DTS package on a commercial server fails to execute, to transfer
data between two databases that I own.Can I trigger the package to execute
from code within a stored procedure ?
thanks in advance EarnieEarnie
In order to execute a DTS Package from a Stored Procedure you will need to
call the DTSRUN executable using xp_cmdshell. If you already have a Job
created to execute the DTS Package you can copy the DTSRun command from the
Job Step.
ie. EXEC master.dbo.xp_cmdshell 'DTSRun /~Z0x...3'
For more details refer to http://www.sqldts.com/default.aspx?210
- Peter Ward
WARDY IT Solutions
"Earnie" wrote:

> a scheduled DTS package on a commercial server fails to execute, to transf
er
> data between two databases that I own.Can I trigger the package to execute
> from code within a stored procedure ?
> thanks in advance Earnie

Execute trigger as a specific user

Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only symin
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...