Re: More newbie questions on mass delete

Consider putting your query inside a proc:

1 - create proc in sybsystemprocs
2 - make sure proc name starts with 'sp_'
3 - pass @mytable as a parameter
4 - execute proc in desired database with 'exec <db_name>..sp_<proc_name> @mytable'

As for the resultant row count ... you could pass this back as a return code (return @rows), as an output parameter, or as a result set.

GWood wrote:

Thanks to everybody who has helped out so far. I am using some of R.
Verschoor's code to run dynamic sql in our 11.9.2 environment. The one
thing (ha!) I'm still having problems with is getting an accurate count of
rows impacted by DML e xecuted via this dynamic sql. I am not at liberty to
alter system procedures.

So I am thinking about trying to get a "before" and "after" count of table
rows. For my deletion project purposes, this should suffice. I have
scrounged up code similar to the following (and I realize it is statistics
dependent) :

select @rows = rowcnt(doampg)
from sysindexes idx, sysobjects obj
where idx.indid < 2 and idx.id = obj.id and obj.type = 'U'
and obj.name = @mytable

The issue I now have is that I need to run this programatically for many
databases in my environment. When I attempt to qualify the table names with
a database name, the query hurls with 'OAM page 47336 passed to rowcnt
function is an unallocated page in database 70.' Essentially it seems to
dislike the rowcnt function being used when accessing a non-current DB. I
have tried bringing back the "doampg" value and performing the rowcnt
locally, but it also generates the same error.

Anybody know how I can circumvent this, or have another idea on how to get
to a rowcount for a non-current DB table?

Tnx
Gary


.

Relevant Pages

  • Re: Standard DBI Proposal
    ... If you're asserting that Tcl entirely lacks an interface to ODBC ... logics (server side sql functions with the different server languages) ... has to be changed by changing the database or - maybe - by implementing ... # +proc OpenConnection ...
    (comp.lang.tcl)
  • RE: Using query values for field name in table
    ... "Proc" is a caption given to acalculated field in the query ... should fldName be defined as a string variable which I can then ... >> Dim dbsGeneralThoracic As Database ...
    (microsoft.public.access.modulesdaovba)
  • strangest hanging problem with re-attaching functions...
    ... back-end database. ... So the ribbon button option calls a macro ... I put up a browse dialogue in the StartProgram ... and put an onAction call on the button (see proc below). ...
    (comp.databases.ms-access)
  • Re: A little help on a proc
    ... I'm confuzzled about how to access the initial query throughout the ... >> create proc users ... >> David Lozzi ... If the login exists and the password is correct the third ...
    (microsoft.public.sqlserver.programming)
  • Re: A little help on a proc
    ... So the best process is to rerun the query? ... > create proc foo as ... > The return code will be 1 if the login does not exist, ... > record set, look at me go. ...
    (microsoft.public.sqlserver.programming)