accessing fake data inside an INSTEAD OF trigger

Hello,
Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?
For example :
UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.
Is there a way to access the value 'abc' in such a situation?
thank you
sNo, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>Hello,
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>For example :
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>Is there a way to access the value 'abc' in such a situation?
>thank you
>s|||OK ... but this data should exist somewhere.
I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?
thank you
Roy Harvey wrote:
> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
> Roy Harvey
> Beacon Falls, CT
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>|||On 3 Mar 2006 07:30:03 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>OK ... but this data should exist somewhere.
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>thank you
Nothing like that I've ever heard of. Or imagined, for that matter.
Roy Harvey
Beacon Falls, CT

Access/SQL Linked Tables

I have linked a number of tables from a SQL database to a Access front end. I am able to update all but one of the tables (the main table I need!!!). I get the error, cannot update record as it has been changed by another user although there is no other user. I believe it may be to do with the way the recordset is linked to the access database or the referential integrity of the table associations in the SQL database.

I can change the data using query analyser without issue.

Any help would be greatfully apreciated.Are you changing the data through a form?
Can you change the data by opening up the linked table?|||I am using a form but cannot change the data in that or the linked table. The only place I have successfully changed data is through QM or directly in SQL server.|||Try dropping and recreating your table link. Verify permissions on your database table.|||I had this problem a while ago. Make sure that table has no triggers associated with it. This is very important. Are you using Access front end and linked SQL server tables, or is it a .ADP (Access Data Project)? If its not, you might want to look into upsizing and making it a .ADP file|||Okay, have checked all of the tables and there are no apparent triggers that I can see. I have removed and re-linked the table and also removed the properties to update referential integrity on the SQL database but still no luck. I am now looking into upsizing the database to an access data project. Although I am not sure how the linked tables will differ with this.|||I presume you specified a PK\ unique constraint in the BE or, if not, a candidate key when linking?|||Guy's, I have solved the problem by adding a timestamp field to the BE table and re-attaching to the FE.

Thanx for all your help :)

Access vs. MSDE

I understand how msde is more powerfull than assess but is the only way to use MSDE through the web matrix. If I want to update a table or change data how can I open the file and work with it or is this when I buy SQL server. I travel full time and do all my work from my laptop so I do not want to install any server software on it. Or should I just us access and use the upsize wizard to use MSDE.

Adam TaylorCould use a little more info.

It all depends:

Are you developing applications for other people?
Is it for personal/business use, contact management, etc?
What exactly is it that you are trying to accomplish?

Thanks.|||You can get to the tables etc using Access (At least you can with access 2002)

start Access
select the New Project (Existing Data)

Then select the machine (or type it) where the tables are
Supply login details (should be same as you require when using web matrix)

click the drop down to select a database to work on

It should work - does on mine

then you can use all access tools to manipulate the data - plus add tables and stored procedures

Pk

Access Update Query Problem

Are you comparing apples to apples? In your VBA code, are you
executing the same join syntax that you are trying to excute through
the Access GUI. I have a suspicion that you are not, and the reason
the query is bombing out on you is that the join is forcing your
workstation to bring over all of the SQL Server data and working on the
update data client side. However, without code to examine, no freaking
clue :)
StuOn 7/28/2005 5:13 PM, Stu wrote:
> Are you comparing apples to apples? In your VBA code, are you
> executing the same join syntax that you are trying to excute through
> the Access GUI. I have a suspicion that you are not, and the reason
> the query is bombing out on you is that the join is forcing your
> workstation to bring over all of the SQL Server data and working on the
> update data client side. However, without code to examine, no freaking
> clue :)
> Stu
>
Access Query def:
UPDATE dbo_Order_Line_Invoice INNER JOIN tblOrderTypeValues ON
(dbo_Order_Line_Invoice.Cono = tblOrderTypeValues.cono) AND
(dbo_Order_Line_Invoice.LineNum = tblOrderTypeValues.lineno) AND
(dbo_Order_Line_Invoice.OrderNumber = tblOrderTypeValues.OrderNum) SET
dbo_Order_Line_Invoice.SXUser10 = tblOrderTypeValues.ordertype;
dbo_Order_Line_Invoice is the SQL server table
VBA code that works:
Sub UpdateOrdertype()
Dim rsOrderType As Recordset
Dim strSQL As String
Dim intCounter As Long
Set rsOrderType = CurrentDb.OpenRecordset("tblOrderTypeValues")
Do While Not rsOrderType.EOF
intCounter = intCounter + 1
Debug.Print intCounter: DoEvents
strSQL = "UPDATE dbo_Order_Line_Invoice SET
dbo_Order_Line_Invoice.SxUser10 = '" & rsOrderType.OrderType &
"' WHERE (dbo_Order_Line_Invoice.Cono = 1) AND
(dbo_Order_Line_Invoice.OrderNumber = '" & rsOrderType.OrderNum
& "' ) AND (dbo_Order_Line_Invoice.LineNum =
" & rsOrderType.Lineno & ");"
CurrentDb.Execute (strSQL)
rsOrderType.MoveNext
Loop
rsOrderType.Close
MsgBox "Done updating ordertype."
End Sub

Access Update Query Problem

Looks like my suspicion was correct; in the join scenario, you're
dragging the complete recordset over and attempting to do the join
client side. If your data set is large (or your machine is
over-taxed), then it'll take a while to do the updates all at once.
In the second scenario (VBA), you're updating one record at a time on
the SQL Server, so the machine is not dealing with multiple records.
Does that make sense? As to how to fix it, not sure.
StuOn 7/29/2005 1:22 PM, Stu wrote:
> Looks like my suspicion was correct; in the join scenario, you're
> dragging the complete recordset over and attempting to do the join
> client side. If your data set is large (or your machine is
> over-taxed), then it'll take a while to do the updates all at once.
> In the second scenario (VBA), you're updating one record at a time on
> the SQL Server, so the machine is not dealing with multiple records.
> Does that make sense? As to how to fix it, not sure.
> Stu
>
In the join scenario, the msaccess process spikes the CPU but I/O is
stagnant. So I think there is a bug somewhere as the update join query
originally worked.
Dan|||On 8/1/2005 12:44 PM, Stu wrote:
> Which I/O? Disk or network? I don't think Access manages ODBC by
> writing it to a temporary file; I believe it tries to do it in-memory.
> I could be wrong about that.
>
Both.

access update query

I have an Access update query that is not working.

Here's my SQL:UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)="2"));

It sets HeartAttackDOD = " " even if HeartAttack = "1" and I only want it to set HeartAttackDOD = " " if HeartAttack = "2".

Can anyone help?

Is ' tablename.HeartAttack ' a string or number?

You are evaluating against strings.

Access to execute Store procedures

Hello there
I would like to give my users permission to insert/update/delete data
from/to tables and views
and i also need to give them full access to execute Store Procedures.
In order to do so i gave them on Dababase mode db_datareader and
db_datawriter roles.
This roles are giving them full access to insert/update and delete data, but
it doesn't give them access to execute Store procedures.
Which permission i sould give them wituout give them db_owner?Roy
GRANT EXECUTE ON storeprocedure TO username
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I would like to give my users permission to insert/update/delete data
> from/to tables and views
> and i also need to give them full access to execute Store Procedures.
> In order to do so i gave them on Dababase mode db_datareader and
> db_datawriter roles.
> This roles are giving them full access to insert/update and delete data,
> but it doesn't give them access to execute Store procedures.
> Which permission i sould give them wituout give them db_owner?
>|||Shalom Uri
This i've already know
I'm looking for role for this and not go spesific on each store procedure.
on the business i'm working on they are afraid to give the users db_owner's
permission which i know that it is not a problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Roy
> GRANT EXECUTE ON storeprocedure TO username
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
>|||Roy
Ok, so create a new role, add to the role users that you want to execute
sp. Go to the permission tab and click/check EXEC column for stored
procedure
Also , you can write script to grant an execute permission for all stored
procedure tospecific user. If you are interested I will post it out
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Shalom Uri
> This i've already know
> I'm looking for role for this and not go spesific on each store procedure.
> on the business i'm working on they are afraid to give the users
> db_owner's permission which i know that it is not a problem.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Roy
Be aware that if you have dymanic sql within a stored procedure you will
have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
as well
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Roy
> Ok, so create a new role, add to the role users that you want to execute
> sp. Go to the permission tab and click/check EXEC column for stored
> procedure
> Also , you can write script to grant an execute permission for all stored
> procedure tospecific user. If you are interested I will post it out
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Whell Uri
For this i have already gave db_datareader and db_datawriter role which
supply this need.
In fact before i deal there, it was as you said.
Now after i add db_datareader and db_datawriter this problem has gone and
there is full permission
it is seems to be some stupide of microsoft not to have role to execute
store procedures. it looks like the is reason for it. i'm wondering why
thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ed005aLHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Roy
> Be aware that if you have dymanic sql within a stored procedure you will
> have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
> as well
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> For this i have already gave db_datareader and db_datawriter role which
> supply this need.
> In fact before i deal there, it was as you said.
> Now after i add db_datareader and db_datawriter this problem has gone and
> there is full permission
> it is seems to be some stupide of microsoft not to have role to execute
> store procedures. it looks like the is reason for it. i'm wondering why
You can't do this in SQL 2000, as far as I know. But in SQL 2005,
permissions cascade, and you can say things like:
grant execute on database::yourdb to somerole
grant execute on schema::dbo to someotherrole
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

Access Sql server using Javascript

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Quote:

Originally Posted by lindy

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Two Ideas for you:

1. Change your close button to use ASP.Net

2. Use AJAX to call a page that will update your information.

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.
Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish|||You could set it up using a view and a linked server rather than a linked ta
ble. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.
Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish
|||You could set it up using a view and a linked server rather than a
linked table. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around
CecilCable
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message922826.html

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
--
Thanks
Yogish|||You could set it up using a view and a linked server rather than a
linked table. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around
CecilCable
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message922826.html

Access linked to SQL Server - 2 day offset error

We recently translated the backend db from Access(97) to SQL Server.
We are still using Access frontends. I have an update query in the
Access front end that uses a lookup table to populate fields. The
common fields between the table and the lookup table are the primary
key (LocID) and date & time fields. The query is:

UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
tblPT.Salinity = [tblPT_Offsets].[Salinity]
WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
[EndDate]));

This worked fine in Access and seemed to work fine after switching to
Access, but on closer look, there is exactly a 2 day error being
introduced. A quick search of the newsgroups brings up lots of Access
to SQL date problems, but a 2 day offset seems rather strange? Any
ideas??

I know the field names Date and Time are inappropriate, but legacy
issues are a pain in the butt to resolve!! Could this be a problem?

Davidarchean1@.yahoo.com (David) wrote in message news:<31e424c8.0405251702.14950a8f@.posting.google.com>...
> We recently translated the backend db from Access(97) to SQL Server.
> We are still using Access frontends. I have an update query in the
> Access front end that uses a lookup table to populate fields. The
> common fields between the table and the lookup table are the primary
> key (LocID) and date & time fields. The query is:
> UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
> tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
> tblPT.Salinity = [tblPT_Offsets].[Salinity]
> WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
> ((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
> [EndDate]));
> This worked fine in Access and seemed to work fine after switching to
> Access, but on closer look, there is exactly a 2 day error being
> introduced. A quick search of the newsgroups brings up lots of Access
> to SQL date problems, but a 2 day offset seems rather strange? Any
> ideas??
> I know the field names Date and Time are inappropriate, but legacy
> issues are a pain in the butt to resolve!! Could this be a problem?
> David

Can you post some sample data to show the problem? It's not really
clear from the details above what you're seeing. Are you using the
query above with linked tables in Access?

Simon|||On 25 May 2004 18:02:25 -0700, David wrote:

>We recently translated the backend db from Access(97) to SQL Server.
>We are still using Access frontends. I have an update query in the
>Access front end that uses a lookup table to populate fields. The
>common fields between the table and the lookup table are the primary
>key (LocID) and date & time fields. The query is:
>UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
>tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
>tblPT.Salinity = [tblPT_Offsets].[Salinity]
>WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
>((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
>[EndDate]));
>This worked fine in Access and seemed to work fine after switching to
>Access, but on closer look, there is exactly a 2 day error being
>introduced. A quick search of the newsgroups brings up lots of Access
>to SQL date problems, but a 2 day offset seems rather strange? Any
>ideas??
>I know the field names Date and Time are inappropriate, but legacy
>issues are a pain in the butt to resolve!! Could this be a problem?
>David

Hi David,

I don't know if there is any relation at all, but just yesterday I
answered a question in another newsgroup about a 2-day difference being
introduced when copying data between SQL Server and Excel. That poster
seemed to use the internal date representation instead of formatted dates.
Your query does contain code to format the date, so it should not cause
this effect - but the fact your time difference is 2 days as well does
strike me as funny.

In case you want to check it out, follow the link below.

http://www.google.com/groups?q=exce...04ax.com&rnum=1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funnily enough, I did fix my problem with a simple fix, but I'm glad
to understand the problem. (Simon, the queries are in Access
frontends and the tables are linked via ODBC to a SQL Server.)

The sql code I posted (from the access frontend) actually already had
the fix. I had already changed

([Date] + [Time])

to ((Format([Date]+[Time],"mm/dd/yy hh:nn"))

and this fixed the problem. After reading the replies (Hugo, you hit
it on the head), it seems that passing SQL Server dates formatted both
as dates and double and then having SQL Server do date lookups, etc.
is dangerous. But by making sure all dates are passed as dates, I'm
assuming the ODBC translation takes care of the differences in the
date numbering scheme of Access/SQL. I think that sums it up?

thanks!
David

Access Issue after Automatic Update

I finally added WinXP SP2 to my system yesterday (along with a lot of other security updates/patches through Automatic Updates) and now my backups and data exports are failing. Further, when I open my "Enterprise Manager" and try to export data ("All Tasks-Export-...") using the Import/Export Wizard, it won't let me select the "Export From" database.Can you give error message here or something?