Can not access the Deleted System Table

I have a problem where I can not perform "Select * from Deleted" within
a trigger, but the problem occurs when I insert/update data from
Enterprise Manager ONLY. The trigger operates fine if I modify data in
Query Analyzer or through VBCode.
I finally found a workaround, modifying the select statement to "Select
* Into #deleted from Deleted", but am worried that will impact
performance on larger databases. I'm 99% certain it is just an access
issue somewhere, I just can't figure out where.
Any help y'all can suggest is appreciated.
Here is a snippet of the trigger:
CREATE trigger InsertUpdate_Table1
On Table1
For Update, Insert As
Declare @.Temp Int
Select * from Deleted
Select @.Temp = @.@.Rowcount
If @.Temp > 0
{rest of the code has been confirmed to work just fine}> Select * from Deleted
> Select @.Temp = @.@.Rowcount
So your trigger returns 2 resultsets? I can see how this would confuse EM
because special application code is needed to handle this situation.
This technique to get the number of updated rows is a bad practice for a
number of reasons. The proper way to get the number of updated rows is with
@.@.ROWCOUNT after the UPDATE statement rather in the trigger itself:
UPDATE dbo.Table1
SET MyColumn = 1
SELECT @.@.ROWCOUNT
Hope this helps.
Dan Guzman
SQL Server MVP
"Fayven" <FayvenWren@.gmail.com> wrote in message
news:1154833842.671883.224220@.b28g2000cwb.googlegroups.com...
>I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>|||FYI - SQL EM is using RPC to run the SQL Statements
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Fayven" wrote:

> I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>sql

can I use MS Access reports on SQL Server 2000 db?

Hi,
I have a SQL Server 2000 db on which I would like to perform querys and have
pretty reports printed. Can I use MS Access (for the pretty reports) to
connect to the SQL Server 2000 db? If yes, can you please tell me how to
set up the connection?
Thanks,
Rogue Petunia"Rogue Petunia" <roguepetunia@.NOSPAMnyc.rr.com> wrote:
> Hi,
> I have a SQL Server 2000 db on which I would like to perform querys and
have
> pretty reports printed. Can I use MS Access (for the pretty reports) to
> connect to the SQL Server 2000 db? If yes, can you please tell me how to
> set up the connection?
--
Hi Rogue,
You can use the Access ADP to link data from SQL Server. The Access ADP uses
OLE-DB to connect to SQL Server.
For more details, please consult your Access doco's.
Hope this helps,
-Eric Cárdenas
SQL Server support|||Hi Eric,
Totally lost. Don't know what ADP is. Looked in Access Help, can't find
ADP.
Further details appreciated.
Thanks.
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:3fce4bf3$1@.news.microsoft.com...
> "Rogue Petunia" <roguepetunia@.NOSPAMnyc.rr.com> wrote:
> > Hi,
> > I have a SQL Server 2000 db on which I would like to perform querys and
> have
> > pretty reports printed. Can I use MS Access (for the pretty reports) to
> > connect to the SQL Server 2000 db? If yes, can you please tell me how
to
> > set up the connection?
> --
> Hi Rogue,
> You can use the Access ADP to link data from SQL Server. The Access ADP
uses
> OLE-DB to connect to SQL Server.
> For more details, please consult your Access doco's.
> Hope this helps,
> -Eric Cárdenas
> SQL Server support
>|||"Rogue Petunia" <roguepetunia@.NOSPAMnyc.rr.com> wrote:
> Hi Eric,
> Totally lost. Don't know what ADP is. Looked in Access Help, can't find
> ADP.
--
Hi Rogue,
ADP stands for "Access database project." The other Access file is MDB for
"Microsoft database." IIRC, the ADP was introduced in Access 2000.
The difference in the ADP and the MDB is that the ADP contains no data. The
ADP is purposely designed to use SQL Server (or any OLE-DB source). The MDB
can store data using Access tables. The MDB uses ODBC to connect to SQL
Server.
What version of MS Access will you use?
Let me know if you need more information.
--
Eric Cárdenas
SQL Server support|||Eric,
A co-worker just showed me how to do it. Sort of like a variation of "a
picture is worth a thousand words" ;) Now it totally makes sense.
thanks anyway,
Rogue Petunia
"Eric Cardenas - ANZ MSFT support" <anonymous@.discussions.microsoft.com>
wrote in message news:3fce5657$1@.news.microsoft.com...
> "Rogue Petunia" <roguepetunia@.NOSPAMnyc.rr.com> wrote:
> > Hi Eric,
> > Totally lost. Don't know what ADP is. Looked in Access Help, can't
find
> > ADP.
> --
> Hi Rogue,
> ADP stands for "Access database project." The other Access file is MDB for
> "Microsoft database." IIRC, the ADP was introduced in Access 2000.
> The difference in the ADP and the MDB is that the ADP contains no data.
The
> ADP is purposely designed to use SQL Server (or any OLE-DB source). The
MDB
> can store data using Access tables. The MDB uses ODBC to connect to SQL
> Server.
> What version of MS Access will you use?
> Let me know if you need more information.
> --
> Eric Cárdenas
> SQL Server support
>|||You can also use an MDB. Base your reports on a pass-through query or
a query against a linked table/view.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 3 Dec 2003 16:55:28 -0500, "Rogue Petunia"
<roguepetunia@.NOSPAMnyc.rr.com> wrote:
>Eric,
>A co-worker just showed me how to do it. Sort of like a variation of "a
>picture is worth a thousand words" ;) Now it totally makes sense.
>thanks anyway,
>Rogue Petunia
>
>"Eric Cardenas - ANZ MSFT support" <anonymous@.discussions.microsoft.com>
>wrote in message news:3fce5657$1@.news.microsoft.com...
>> "Rogue Petunia" <roguepetunia@.NOSPAMnyc.rr.com> wrote:
>> > Hi Eric,
>> > Totally lost. Don't know what ADP is. Looked in Access Help, can't
>find
>> > ADP.
>> --
>> Hi Rogue,
>> ADP stands for "Access database project." The other Access file is MDB for
>> "Microsoft database." IIRC, the ADP was introduced in Access 2000.
>> The difference in the ADP and the MDB is that the ADP contains no data.
>The
>> ADP is purposely designed to use SQL Server (or any OLE-DB source). The
>MDB
>> can store data using Access tables. The MDB uses ODBC to connect to SQL
>> Server.
>> What version of MS Access will you use?
>> Let me know if you need more information.
>> --
>> Eric Cárdenas
>> SQL Server support
>>
>