Re: delete row query doesn't remove all row when run from access (ms sql)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This is the procedure run:

ALTER PROCEDURE usp_delete_dataByDate
@dDate AS SMALLDATETIME
AS

DELETE FROM _settings_date WHERE [date] = @dDate

and there is a trigger on _settings_date:

ALTER TRIGGER [Trigger Update _settings_date]
ON _settings_date
FOR DELETE
AS
SET NOCOUNT ON

DECLARE @dDate AS SMALLDATETIME

SELECT @dDate = [date] FROM deleted

DELETE FROM ut_t1 WHERE [data_date] = @dDate
DELETE FROM ut_t2 WHERE [data_date] = @dDate
DELETE FROM ut_t3 WHERE [data_date] = @dDate
DELETE FROM ut_t4 WHERE [data_date] = @dDate

If the proc is run from Query Analyzer it works fine but form access
some entries matching the date criteria still appear.

[MVP] S.Clark wrote:
Post the SQL. The joins and the WHERE clause dictate what records are
involved, so that should give us a hint.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html


<xavier.goulay@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1154602559.476921.175370@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I have a stored procedure which delete rows from a table for a specific
date. When I run the query from Query Analyzer it does work perfectly.
But when I run the query from the corresponding adp project some rows
matching the delete criteria (by date) are still existing. It seems
that adp apply the query only to the visible rows (the ones we can see
in the datagrid). Is there any way to get it applied on the all set of
rows from access?

Thanks


.

Relevant Pages

  • Re: Access to SQL
    ... USE tempdb ... dt SMALLDATETIME ... SET NOCOUNT ON ... > tranferred to SQL-server and we cannot convert the query to SQL. ...
    (microsoft.public.sqlserver.programming)
  • RE: If or case?
    ... but here is the query: ... int NOT NULL, ... future_verification_date smalldatetime NULL, ... SET NOCOUNT OFF ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Analyser Resource Usage
    ... a new post detailing the SQL question, as it is going a little off-topic.. ... You say Query Analyzer is consuming lots of memory....I have noticed this ... > SET NOCOUNT ON ... > FROM #TEMPCUSTGROUP WHERE TARIFFSETID IS NULL ...
    (microsoft.public.sqlserver.tools)
  • Re: Cross Tab Query Help!
    ... This is my first query: ... FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum ... WHERE (DateCode BETWEEN CAST('20060713' AS smalldatetime) ... Are you doing this in Access against a linked SQL Server table, ...
    (microsoft.public.access.queries)
  • Re: Using DTS to Insert 1 Second record for every second in a month?
    ... Okay...so you basically need a calendar table by seconds. ... And yes that will perform pretty fast for the type of query ... >set NoCount OFF ... >This stored procedure took about 21 minutes. ...
    (microsoft.public.sqlserver.dts)