Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

script uses INT variable as if one lower than set value Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 2:09 PM
Points: 7, Visits: 16
This should delete all records from these tables where the archiveID is greater than 14, but it is taking out values greater than or equal to 14.
What did I do wrong?

Declare @good_archive int
set @good_archive = 14

DELETE FROM [arx_Archive].[dbo].[AppSettings] WHERE archiveID > @good_archive
DELETE FROM [arx_Archive].[dbo].[Employees] WHERE archiveID > @good_archive
DELETE FROM [arx_Archive].[dbo].[EmployeeWorkCodes] WHERE archiveID > @good_archive

Post #1381725
Posted Tuesday, November 6, 2012 3:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
Is the datatype for archiveID int? Do you have any triggers on these tables?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381727
Posted Tuesday, November 6, 2012 4:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
As previous poster asked whats the Datatype for ArchiveID? possibly could be varchar?

Can you show a sample set of Information? like archiveID 13/14/15 if its confidential then just mock something up.
Post #1381745
Posted Wednesday, November 7, 2012 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 2:09 PM
Points: 7, Visits: 16
It didn't occur to me to verify the datatypes were the same, good point. Unfortunately it didn't help this time as the datatype is int.
I didn't design the database, I just need to clean it up when something happens in a customer installation.
The archiveID field will have a number of 1, 2, 3, 4, etc, in increasing order with no theoretical max, depending on how long the customer is using the system with a new set of archives added every week or two.
I did some research and learned enough about triggers to be fairly confident that there are none associated with archiveID in any of the tables I am working with.
There are several more tables involved in this. The first time I used it I did not create the variable, I simply had the value at the end of each line and it worked fine. The problem came up when I tried to use a variable so I would only have to edit the one line each time I used it.
Post #1381960
Posted Wednesday, November 7, 2012 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
fwob04 (11/7/2012)
It didn't occur to me to verify the datatypes were the same, good point. Unfortunately it didn't help this time as the datatype is int.


Just checking here.


I did some research and learned enough about triggers to be fairly confident that there are none associated with archiveID in any of the tables I am working with.


Triggers don't work on a column as you are suggesting. If there are triggers on these tables that very well could be the issue.

I am certainly willing to help you here but I need to get some more information from you in order to be able to help. I see that you are pretty new around here so you probably don't know the type of information required to help. We need to see ddl (create table statements including ALL triggers, constraints, indexes etc), some sample data (insert statements). There is no need to post actual data but you should post enough to cover the issue at hand. If you take a look at the first link in my signature it will explain the best practices for posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382002
Posted Wednesday, November 7, 2012 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 2:09 PM
Points: 7, Visits: 16
I don't have code for creating the database. As I indicated previously, I didn't create the thing, I just get stuck cleaning it up when the end user does something unforeseen.

This is code from a backup script, but it assumes the database already exists and is working with individual tables. When I try to use it to create a table, it runs with no errors, but I can't find the table anywhere.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archives]') AND type in (N'U'))
BEGIN
CREATE TABLE [Archives](
[ArchiveID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
CONSTRAINT [PK_Archives] PRIMARY KEY CLUSTERED
(
[ArchiveID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
SET IDENTITY_INSERT[Archives] ON
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (1, CAST(0x0000A08800000000 AS DateTime), CAST(0x0000A08E00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (2, CAST(0x0000A08F00000000 AS DateTime), CAST(0x0000A09500000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (3, CAST(0x0000A09600000000 AS DateTime), CAST(0x0000A09C00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (4, CAST(0x0000A09D00000000 AS DateTime), CAST(0x0000A0A300000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (5, CAST(0x0000A0A400000000 AS DateTime), CAST(0x0000A0AA00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (6, CAST(0x0000A0AB00000000 AS DateTime), CAST(0x0000A0B100000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (7, CAST(0x0000A0B200000000 AS DateTime), CAST(0x0000A0B800000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (8, CAST(0x0000A0B900000000 AS DateTime), CAST(0x0000A0BF00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (9, CAST(0x0000A0C000000000 AS DateTime), CAST(0x0000A0C600000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (10, CAST(0x0000A0C700000000 AS DateTime), CAST(0x0000A0CD00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (11, CAST(0x0000A0CE00000000 AS DateTime), CAST(0x0000A0D400000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (12, CAST(0x0000A0D500000000 AS DateTime), CAST(0x0000A0DB00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (13, CAST(0x0000A0DC00000000 AS DateTime), CAST(0x0000A0E200000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (14, CAST(0x0000A0E300000000 AS DateTime), CAST(0x0000A0E900000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (15, CAST(0x0000A0EA00000000 AS DateTime), CAST(0x0000A0F000000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (16, CAST(0x0000A0F100000000 AS DateTime), CAST(0x0000A0F700000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (17, CAST(0x0000A0F800000000 AS DateTime), CAST(0x0000A0FE00000000 AS DateTime))
INSERT[Archives] ([ArchiveID], [StartDate], [EndDate]) VALUES (18, CAST(0x0000A0FF00000000 AS DateTime), CAST(0x0000A10500000000 AS DateTime))
SET IDENTITY_INSERT[Archives] OFF

Post #1382038
Posted Wednesday, November 7, 2012 9:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 648, Visits: 1,874
fwob04 (11/7/2012)
When I try to use it to create a table, it runs with no errors, but I can't find the table anywhere.


EITHER the table already exists (it's called Archives), as the script only creates where the table doesn't already exist,
OR you're looking in the wrong place. Are you running your script in 'master' database perhaps?

Where did I do wrong?


If you run the code below (your code modified) you should see the rows in scope for deletion.

Declare @good_archive int
set @good_archive = 14

select * FROM [arx_Archive].[dbo].[AppSettings] WHERE archiveID > @good_archive
select * FROM [arx_Archive].[dbo].[Employees] WHERE archiveID > @good_archive
select * FROM [arx_Archive].[dbo].[EmployeeWorkCodes] WHERE archiveID > @good_archive

Finally if you run the code below you should see if you have any triggers on these tables.

SELECT  so.name ,
st.name
FROM arx_Archive.sys.triggers st
INNER JOIN arx_Archive.sys.objects so ON st.parent_id = so.object_id
WHERE so.name IN ( 'AppSettings', 'Employees', 'EmployeeWorkCodes' )

(no rows means no triggers etc.)
Post #1382050
Posted Wednesday, November 7, 2012 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318

I don't have code for creating the database. As I indicated previously, I didn't create the thing, I just get stuck cleaning it up when the end user does something unforeseen.

This is code from a backup script, but it assumes the database already exists and is working with individual tables. When I try to use it to create a table, it runs with no errors, but I can't find the table anywhere.


What you posted is great for the table Archives. Unfortunately this table was not mentioned in your original post. I think you want to delete rows 3 other tables and far as I can tell this Archives tables has nothing to do with it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382086
Posted Wednesday, November 7, 2012 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 2:09 PM
Points: 7, Visits: 16
As I mentioned previously there are several more tables involved. If one of them is the "key" table in the database, that archives one would be it, so that's the one I provided.

I forgot to say thank you for the code. It did show the records that would be removed, and they were correct.
After running your sample, I tried my original again a few minutes ago and today it works.
I hate ghosts.
Post #1382095
Posted Wednesday, November 7, 2012 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
fwob04 (11/7/2012)
As I mentioned previously there are several more tables involved. If one of them is the "key" table in the database, that archives one would be it, so that's the one I provided.

I forgot to say thank you for the code. It did show the records that would be removed, and they were correct.
After running your sample, I tried my original again a few minutes ago and today it works.
I hate ghosts.


Glad you got it figured out and thanks for letting us know.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382134
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse