Undo A &"delete Tbl_test&" TSQL Statement

Sep 15, 1998

I accidentaly deleted data from the wrong table. Is there a way to un-delete what I had deleted.

need help ASAP.

Regards,
Steve

View 5 Replies


ADVERTISEMENT

Is There A TSQL Statement To Delete File Like Xp_delete_file For SQL2000

Jun 29, 2006

I know there is a command xp_delete_file for SQL 2005 but if I want to delete a file from a 2000 database is there a command to do the same.

View 4 Replies View Related

How To Undo A Delete With Cascades

Aug 30, 2005

Hello,Somebody accidently deleted a record with cascade deletesand took out about 80 records.The database has been used heavily since then.How do I put back the data?I have spent hours researching BOL and all I can findis how to restore the database to a point in time.But that will lose all the work done after the mistake.(We do a full backup every week and log backup every day)Is there a standard way to do this?Morgan

View 2 Replies View Related

Undo Last Statement

Apr 20, 2006

Is there anyway in SQL Server to rollback an SQL statement which wasalready executed. I know there is a transaction log but what itcontains and how it works is still a mystery to me.Assuming I delete all records from a table - can I somehow undo this?

View 5 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

Delete Sql Server Registration In Tsql

Aug 28, 2006

Is there any way in tsql to delete a sql server registration? (I knowhow to do this from Enterprise Manager).Thanks!Jennifer

View 6 Replies View Related

Cascade Delete Contraints - Accessible Through TSQL?

Sep 25, 2006

Hi all,

I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal.

Thanks for any info in advance!

 

I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?

 

 

 

View 3 Replies View Related

TSQL : How To Delete Duplicated Rows Except The Top 1 Order By Some Fields?

Apr 15, 2004

the table is like :

ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......

the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.

how can I delete rows with ID in (2 , 3 , 5)?

View 7 Replies View Related

I Need Help With This Tsql Statement

Apr 2, 2007

Every time I try this statement I keep getting a syntext error near count  I must be over looking something can some one help me with this. 
 
SELECT 'Quarter 1' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 2' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 3' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 4' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE      
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_cOMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE         Order By 'QTR' asc

View 4 Replies View Related

TSQL - WITH Statement

Sep 5, 2007

Hi guys,
I need help with this one...
Iam Trying to understand how to use the statement WITH
I am running the code below, but getting error.

note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server)


Thanks in advance,
Aldo.




Code Snippet
WITH MyCTE (FILTER, SORTGROUP)
AS
(
SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS
)
SELECT * FROM MyCTE AS CTE_01;

Error Messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

View 6 Replies View Related

TSQL From An Access SQL Statement

Feb 21, 2001

Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View 1 Replies View Related

How Can I Use Variables In This TSQL Statement

Mar 4, 2008

Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg

View 2 Replies View Related

Tsql Case Statement

Sep 12, 2007

Hi,
Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table.
And below is the syntax.

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select
a. store_key,
a.fisc_date_key,
sum(a.net_sale_Dollars) as sale_TY ,
sum ( b.net_sale_dollars ) as sale_LY ,
a.division_name,
a.department_number

fromFact 1 as a

,Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
a.division_name,
a.department_number,
a.fisc_date_key,
a.store_key

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


The current table from this query is showing like:

store_key date_key sale_TY sales_LY div dept
------------------------------------------------------------------------------------------------
1 1 30 20 ABC 1
2 1 20 20 ABC 3

But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded.

The structure of table that I want to create must look like:


store_key date_key sale_TY sales_LY div dept
------------------------------------------------------------------------------------------------
1 1 30 20 ABC 1
2 1 20 20 ABC 3
2 1 15 0 ABC 2

>>>> want to put 0 value where only one side ( current or parrallel period) has sales info.

So, I'm thinking the case statement like:


Case statement logic like:
------------------------------------------------------------
if a. dept not exist in b.dept
then Sale TY -> a.net_sale_dollars
Sale LY -> 0

if b.dept not exist in a.dept
then sale TY -> 0
sale LY -> b. net_sale_dollars
-------------------------------------------------------------

below is the syntax which doesn't work (it's wrong):

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select
a. store_key,
a.fisc_date_key,
sum(case when a.department_number = b.department_number then a.net_sale_dollars
else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end)
as sale_TY ,
sum ( case when b.department_number =a.department_number then b.net_sale_dollars

else case when a.department_number NOT IN (b.department_number) as sale_LY,
a.division_name,
a.department_number

fromFact 1 as a

Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
a.division_name,
a.department_number,
a.fisc_date_key,
a.store_key

,
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Is it possible to create these kind of structure?
Please give me some comments.
Thanks.

View 1 Replies View Related

What Is Wrong With This TSQL Statement

Mar 3, 2008



hello,

I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null


--code


EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name

FROM information_schema.columns c

INNER JOIN information_schema.tables t

ON c.table_name = t.table_name

WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1),

((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))

AND c.column_name = ''to_be_transferred''

AND t.table_type = ''BASE TABLE''

)

BEGIN



SELECT * FROM ?

WHERE to_be_transferred IS NOT NULL

END'



But I am getting the following error


Msg 207, Level 16, State 3, Line 12

Invalid column name 'to_be_transferred'

I thought the if statement filters all those tables with the specified column and do select statement fot those tables only

what did I do wrong, any suggestionm?

Thanks

View 6 Replies View Related

TSQL: Conditional Union Statement

Jul 20, 2005

Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO

View 5 Replies View Related

Help With TSQL Statement To Check For Table Size

Jun 19, 2002

I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated

select Name=left(c.name,20),
c.prec, scale = ISNULL(c.scale,0),
t.name from syscolumns c inner join systypes
t on c.xtype = t.xtype where id in
(select id from sysobjects where name = 'mh_demographic2_')
order by c.colid COMPUTE sum(c.prec)

View 1 Replies View Related

TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'

Jul 23, 2005

I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,

View 18 Replies View Related

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View 1 Replies View Related

How To Free Memory Used By Prior Query Statement Within A Batch By TSQL?

Feb 13, 2004

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.

View 4 Replies View Related

Whqt Is The TSQL Statement To Make A Backup Of A Specific Databese??

May 10, 2008

i have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????

pls help

View 4 Replies View Related

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

Jan 18, 2008

Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

View 3 Replies View Related

Delete Statement

Apr 11, 2007

Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients
WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN
(SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan 

View 1 Replies View Related

Help With DELETE Statement

Nov 13, 2007

Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C:   A's primary key is aID and has no foreign key   B's primary key is bID and has 1 foreign key: aID (linking this table to table A);   C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C     INNER JOIN B     ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB 

View 2 Replies View Related

Sql Delete Statement

Sep 15, 2004

hi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything),
so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?

View 10 Replies View Related

DELETE Statement

Dec 14, 2001

When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.

View 7 Replies View Related

SQL Delete Statement

Oct 2, 2006

Hi what do i need to add to this stmt to delete the result ?
---------------
select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1
---------

i have tried delete * from TABLE_NAME where (select...)

not great at SQL appreciate any help...

View 3 Replies View Related

DELETE Statement Conflicted

Dec 6, 2006

Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn

View 6 Replies View Related

Trigger On Delete Statement

Sep 26, 2007

Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks!
James

View 2 Replies View Related

Only One Of My SQL Delete Statement Are Firing

Mar 12, 2006

Hey guys...
I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select.
I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply.
however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire.
Do anyone on the forum know why this has happened?
 
Sub DeleteUserTicket(sender as Object, e as EventArgs)
Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
End Sub
'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size
Sub RunSQL(strSQL)
Dim objCmd As SqlCommand
Dim sqlConn = New SqlConnection(strConn)
objCmd = New SQLCommand(strSQL, sqlConn)
objCmd.Connection.Open()
'Check that the rows can be deleted if not then return a error.
Try

objCmd.ExecuteNonQuery()
response.redirect("ticketsystemtest2.aspx")
Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>"
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else

Message.InnerHtml = "ERROR: Could not update record, please " _
& "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number
Message.Style("color") = "red"
End If
End Try
objCmd.Connection.Close()
sqlConn = nothing
objcmd = nothing
End Sub
 
cheers

View 2 Replies View Related

Recover After Delete Statement

Sep 6, 2001

One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up)

Thanks-

Jack

View 2 Replies View Related

Delete Statement Won&#39;t Work!!

Feb 1, 2001

i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?

i viewed the delete statement with the execusion plan and this is what i saw.

delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.

View 1 Replies View Related

Delete Statement Very Slow

Dec 12, 2005

Hi,

I've got a table with about 500 000 records and growing monthly by about 40 000 records

When I perform the following query:

DELETE from [myTable] WHERE Month = '07' AND Year='2005'

This query will take about 10 minutes to execute. Columns Month & Year are both indexed.

Surely MSSQL can't be this slow on only 500 000 records.

Must I do some other database optimization ???

Thanks

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved