script uses INT variable as if one lower than set value

  • 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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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.)

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I didn't really figure anything out. It didn't work on Tuesday, but it works on Wednesday. I have no idea why.

    I did learn some things in the process, so it was still a good day, but I really hate not knowing.

    Thanks again for the help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply