MS SQL Server :: Rows Deletion Affected By Cursor

Rows Affected? Where Are They From?
After executing a restore command. I got the following result.
Anyone know why is displaying rows affected. I saw the executiong plan and it shows several execution plans? Its the first time i see it. Can anyone explain what is going on?

restore database test_GG_ATRECORDING_QAT2
from disk = 'f:a.bak'
with move 'a' to 'f:MSSQL$INAQATdataa2.mdf',
move 'a_log' to 'f:MSSQL$INAQATdataa2_log.LDF'

Processed 11016 pages for database 'test_GG_ATRECORDING_QAT2', file 'a' on file 1.
Processed 1 pages for database 'test_GG_ATRECORDING_QAT2', file 'a_log' on file 1.

(1 row(s) affected)

(7 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(11 row(s) affected)

(7 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

RESTORE DATABASE successfully processed 11017 pages in 2.330 seconds (38.732 MB/sec).

View Replies !

Rows Affected By Delete
Hello all,

Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!

Brian

View Replies !

ExecuteNonQuery Not Returning Rows Affected
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.

View Replies !

Execute SQL Task With No Rows Affected
Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

View Replies !

ExecuteNonQuery() Not Giving Correct Affected Rows
When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @@RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance -- With if/else ExecuteNonQuery returns -1ALTER PROCEDURE [dbo].[SP_AddObjectContribution] @ObjectId int, @FanId int, @DateContributed DateTime, @Notes nvarchar(512), @ObjectType intASBEGIN BEGIN TRAN    IF @ObjectType = 2    BEGIN      INSERT INTO FighterContributions        (FighterId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 3    BEGIN      INSERT INTO FighterPhotoContributions        (FighterPhotoId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 4    BEGIN      INSERT INTO OrganizationContributions        (OrganizationId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 5    BEGIN      INSERT INTO EventContributions        (EventId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 6    BEGIN      INSERT INTO FightContributions        (FightId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    ELSE IF @ObjectType = 7    BEGIN      INSERT INTO FightPhotoContributions        (FightPhotoId, FanId, DateContributed, Notes) VALUES        (@ObjectId, @FanId, @DateContributed, @Notes)    END    IF @@ERROR <> 0    BEGIN      ROLLBACK RETURN    END COMMIT TRANEND -- Without if/else ExecuteNonQuery returns 1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@ObjectId int,
@FanId int,
@DateContributed DateTime,
@Notes nvarchar(512),
@ObjectType int
AS

BEGIN

BEGIN TRAN
      INSERT INTO FighterContributions
        (FighterId, FanId, DateContributed, Notes) VALUES
        (@ObjectId, @FanId, @DateContributed, @Notes)
    IF @@ERROR <> 0
    BEGIN
      ROLLBACK RETURN
    END

COMMIT TRAN

END

View Replies !

Can't Delete Records From DB .. Says : Too Many Rows Were Affected By Update.
Hi,

I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says

"Key column information is insufficient or incorrect. To many rows were affected by update."

What to do, to delete these records?

Appreciated..

View Replies !

LinqDataSource && GridView: Update Fails (no Rows Affected)
Hi,
Using VS.NET 2008 Beta2, and SQL Server 2005.
I have a gridview bound to a linq data source, and when trying to update a row, I get an exception that no rows were modified.
The query generated is:
UPDATE [dbo].[package]
SET [owner_id] = @p5
WHERE ([package_id] = @p0) AND ([title] = @p1) AND ([directory] = @p2) AND ([owner_id] = @p3) AND ([creation_date] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20006]
-- @p1: Input String (Size = 22; Prec = 0; Scale = 0) [Visual Studio.NET 2005]
-- @p2: Input String (Size = 26; Prec = 0; Scale = 0) [MSI_Visual_Studio.NET_2005]
-- @p3: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10000]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/07/2007 12:00:00 a.m.]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10001]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

If I run it manually on sql server, it fails until the directory column is removed. The type is varchar(50), with a uniqueness constraint. However, this is same type as the title column, which doesn't have this problem.
Thanks,
Jessica

View Replies !

How To Get Return Value For The Number Of Rows Affected By Update Command
Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.

========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.

View Replies !

Rows Affected From Insert,update,delete Using SQLDMO
I am using SQLDMO to implement a tool to do basic management/script running for a MSDE database. The problem i am having is getting the number of rows affected from insert,update,delete statements. Help and MSDN seem to imply that this info is returned in a QueryResults object, but it is always empty unless the query is a select statement. I tried using ExecuteImmediate, ExecuteWithResults, and ExecuteWithResultsAndMessages methods and the info is not in any resultset, message, or property.

mikem

View Replies !

Retrival And Deletion Of Duplicate Rows.
I have a table...say tb1 of 20 columns which has 2.7 million rows. There is no PK and the only way of identifying a unique row can be done with combination of column1+column2+column3.
Can anyone help me how to idetify the duplicate rows and also delete the duplicate rows. And to commit after every 5000 rows.
ITS VERY URGENT....Thanks in advance.

View Replies !

Can Profiler Show Table Rows Affected By I/U/D Action From Within Stored Procs?
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce

View Replies !

Rows Affected- But Update Not &"physically Occurring&"
I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:

USE DATABASE
--DROP PROC sp_EditMember
GO

--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)

AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st

WHERE member_id = @member_id

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------

on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).

any troubleshooting on this?

thanks in advance,

sudeep.

View Replies !

Max Rows In Cursor
hi, is there a max number of rows created in a cursor..... if so, what is the max alowable rows in a cursor?

thanks

Ali

View Replies !

Help With Cursor To Insert 100 Rows At A Time
Hi all,

Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.

Here is what I have till now:

CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON

declare @Col1 int
declare @Col2 char(9)
DECLARE @RETURNVALUE int
DECLARE @ERRORMESSAGETXT varchar(510)
DECLARE @ERRORNUM int
DECLARE @LOCALROWCOUNT int

declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)

set @RETURNVALUE = 0
set @ERRORNUM = 0

BEGIN

open Insert_Cur
fetch NEXT from Insert_Cur into @Col1, @Col2
while (@@FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @Col1,@Col2

SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT
IF @ERRORNUM = 0
BEGIN
IF @LOCALROWCOUNT >= 1
BEGIN
SELECT @RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @@ERROR
RAISERROR (@ERRORMESSAGETXT, 16, 1)
SELECT @RETURNVALUE = 1
END

fetch NEXT from Insert_Cur into @Col1, @Col2
end

close Insert_Cur
deallocate Insert_Cur

RETURN @RETURNVALUE
END

View Replies !

How To Read The Rows In A Cursor Variable
Hi,

I have a dynamic query that returns its values in a cursor variable.
How do I read each row from this cursor in a loop ?
Eg.:
use AdventureWorks
go
DECLARE @sqlnvarchar(4000),
@paramsnvarchar(4000),
@tables_cursorcursor,
@db_namenvarchar(50),
@table_namenvarchar(4000),
@schema_namenvarchar(50);

set @db_name = 'AdventureWorks';
set @schema_name = 'Production';
set @table_name = 'BillOfMaterials, Product';
set @sql =
' select a.name table_name ' +
' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' +
' on (a.schema_id = b.schema_id) ' +
' where b.name= @schema_name1 ' +
' and @table_name1 is null ' +
' order by 1; '
SELECT @params = N' @table_name1 nvarchar(3000) ,' +
N' @schema_name1 nvarchar(100) ,' +
N' @cursor cursor output'

EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT

View Replies !

Use Cursor To Update Rows In Batches Of A Given Size
I'd like to use a cursor to update a large number of rows in a table, updating a batch of 1000 records at a time. I tried updating in one transaction but log fills up. I'd like to update a batch, clear the log, then update another batch. Repeat till done. Don't really want to increase the log size for this one time shot. Can anyone give me an example to follow.
Thanks

View Replies !

Retrieving Multiple Rows For A Cursor Item
Hi, can someone plz give me an idea of how to proceed with this...
I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000)
For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer.
I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation
I appreciate suggestions, thanks again.

Irene

View Replies !

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View Replies !

(1 Row(s) Affected)
I am going to be running a very large query with
multiple inserts and would like to get turn off the
'(1 row(s) affected)' messages temporarily. Does
Anyone know how I can do this?

Thanks!!
Toni

View Replies !

Local DB Affected By VPN
I work on a local copy of a database (Access 2000 MDB with ODBC linkedtables to a SQL 7 database). The SQL Server db I'm working with is on my Cdrive (MSDE). Occasionally I'll connect to a VPN to do something on a remotecomputer, using PC Anywhere to perform the task. Doesn't involve my front orback end at all, except that the computer I'm VPNing to has a SQL databaserunning.OK, so I'm using my db; I connect to the VPN, and do my thing. Everything'sfine. However, if I then disconnect from the VPN (or if the VPN connectiontimes out and disconnects itself), I then can no longer access my local SQLServer database. My Access application still works fine, as long as itdoesn't have to look at any data. When it does have to look at data, I getODBC call failed. I have to close and reopen my Access database, and theneverything is fine.So, it seems that, for some reason, when I connect to the VPN, my local SQLServer or the ODBC driver or something in the mix is looking at that remotedatabase as part of what I'm using. Then, when the VPN connection is closed,it won't access my local database for some reason.Oh, and my local database and the remote database both have the same name.So that may be part of the problem.Any ideas as to what's going on?Thanks!Neil

View Replies !

Records Affected
Hi

How I return Records Affected from a Stored Procedure
with Select to VB ?

example in my Stored Procedure I have

select * from mytable

I have 10 register , how I know that has 10 registers
in the VB ?, but I do not want use count(*)

thank you in advance

View Replies !

Record Affected
Hi,
I am using Nested Sp's
I need to get the Records Affected [Row Count] by the nested SP.

Thanks.

View Replies !

Number Of Affected Records
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005
--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?
-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?
cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int));
cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue;
Please help me out with this.Thanks

View Replies !

Finding Affected Tables
I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty

View Replies !

Row Height Affected When Adding New Row
I am attempting to add a new row to a table. Every time I add the new row (doens't matter where) it has an effect on the height of the other rows in the table when displayed on screen (makes them taller). However the rows retain the required height when printed. I haven't any idea what might be causing this behaviour. The only explanation I can think of is that this is a bug.

I know it's a bit obscure but has anyone else come across this or have any idea what might be causing it?

View Replies !

Retrieve Records Affected Count From ADO?
Hello,If I run an action SP from MS Access using ADO:...cmd.executewhere the SP is something like Create...Update tbl1 set fld1 = 'something' where...how can I retrive the count of records affected like from Queryanalyzer?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Performance Affected For Selected Users
Hi
I have 400 mb db with simple recovery
and 1 mb log file running on sql 2000 sp3

database reorgenazed with shrink db option once a day at 6 PM as part of maintance plan

db used with Powerbuilder application
application and db structure was not changed for last 6 month.

Some users complaining about performance.
When I run trace I don't see any query running more then 0.5 sec, while users complain it takes 3-5 sec to open a window or application freeze.
When I ask user reconnect and repeat the task ,everthing is fine.

How to identify is it db or application problem?

Thanks

View Replies !

Stored Procedure + Return Total Row Affected
Hi..

I have a stored procedure which is used to remove record from database.
Is there anyway to get the total row deleted for each table?

CREATE PROCEDURE dbo.sp_company_delete
(
@Company_Id int,
@Sched int Output,
@Users int Output,
@Asset int Output,
@Fleet int Output

)
AS
SET NOCOUNT OFF;
Begin Transaction delete_company
Select @Sched = COUNT(*) from Scheduler Where Company_id=@Company_Id
Delete From Scheduler Where Company_id=@Company_Id

Select @Users = COUNT(*) From Users Where Company_Id=@Company_Id
Delete From Users Where Company_Id=@Company_Id

Select @Asset = COUNT(*) From Asset Where Company_Id=@Company_Id
Delete From Asset Where Company_Id=@Company_Id
Delete From Fleet Where Company_Id=@Company_Id
Delete From Company Where Company_Id=@Company_Id
Commit Transaction delete_company
GO

Basically I want to get the total row deleted for each table and put it inside output parameters.

I am doing a double job here, first I get the count, put into output parameter, then only I delete the record.

Is there any other way which is more efficient to do this?

I tried using
SET @Sched = Delete From Scheduler Where Company_id=@Company_Id
SET @Users = Delete From Users Where Company_Id=@Company_Id
But doesn't work.

Any suggestion is welcomed.
Thank you in advanced.

View Replies !

Trigger Question (accessing Affected Records)
I'm sure this is a simple question, but I haven't had any luck finding an answer.

With a trigger, how do I access the modified/updated/deleted rows? The point of all this is I need to a database that maintains lockout procedures to email certain people when one of these procedures gets updated/added. I want to be able to build a link that points to our webserver (the interface for the lockout db) and to do this, I need certain fields of the affected records.

Let me know if I need to clarify anything. Any help is greatly appreciated.

Thanks,
Tim C

View Replies !

Indirectly Config The Package But It Didn't Affected
Dear all,
I have built an SSIS package by using the BI Dev Studio and enabled its configuration xml file.
The package have a variable called TranDate and I want to put it dynamically from a Calendar on my website (just like assigning a variable).
I have successfully change the value of that variable in the configuration files (affected to the xml file).
Then I loaded the package and executed it (through my web). It's still get the old value (which I have assigned while creating the package).
I didn't understand that where else can the package get the value of that variable so it still get the old value (that value have never appeared in the xml configuration file anymore when I changed it).

Thanks for reading this, and I am looking forward to seeing any helps from you guys.

View Replies !

Deletion
Hi all,

I have a table in xyz database and there is no column in table like creation_date or modified_date.

The problem is I want to delete records which has been added in the table before 1st jan 2007.

The size of table is 85 GB

Immediate help would be appriciable.

Regards,
Frozen

View Replies !

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies !

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

GO

example useage:

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

Restrict Deletion
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?

Thank you in advance.

View Replies !

Deletion Of Duplicate Row
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard

View Replies !

Replication Without Deletion
Hello there,

We are currently setting up out production server to the following requirements:

1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.

The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.

Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.

Thanks for your time,

Ulrik Rasmussen

View Replies !

Deletion Problem
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.

TIA,

Stella Liu

View Replies !

Deletion Query
Ok, so I have an issue, was wondering if anybody else has any suggestions.

I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.

1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2. Table contains about 70 million records
3. Table has 6 indexes associated to it
4. Table has 2 views associated to it.
5. Table has 8 foreign keys associated to it.

I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.

When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.

The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.

Here's a DBCC SHOWCONTIG on our table

DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)

Any suggestions on how to delete records efficiently?

View Replies !

User Deletion Log SQL
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.

View Replies !

0 KB File Deletion

Hello,
I want to delete a 0KB file on one of the disks in my server. Do any of you have any idea how to do it??
Thanks,
Narayan

View Replies !

How To Prevent Db Deletion
Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View Replies !

Database Deletion
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.

View Replies !

DB Deletion Time
Is there an option to find out the deleted DBs on a server?

------------------------
I think, therefore I am - Rene Descartes

View Replies !

Deletion And Identity Reset
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.

View Replies !

Alert On Data Deletion
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.

View Replies !

Recovering From Transaction Log Deletion In 6.5
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/

View Replies !