SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / script uses INT variable as if one lower than set value / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 17:14:24 GMT20RE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxI 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.Wed, 07 Nov 2012 14:15:19 GMTfwob04RE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspx[quote][b]fwob04 (11/7/2012)[/b][hr]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.[/quote]Glad you got it figured out and thanks for letting us know.Wed, 07 Nov 2012 12:36:22 GMTSean LangeRE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxAs 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.Wed, 07 Nov 2012 11:09:10 GMTfwob04RE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspx[quote]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.[/quote]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.Wed, 07 Nov 2012 10:43:56 GMTSean LangeRE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspx[quote][b]fwob04 (11/7/2012)[/b][hr]When I try to use it to create a table, it runs with no errors, but I can't find the table anywhere.[/quote]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?[quote]Where did I do wrong?[/quote]If you run the code below (your code modified) you should see the rows in scope for deletion.[code="sql"]Declare @good_archive intset @good_archive = 14select * FROM [arx_Archive].[dbo].[AppSettings] WHERE archiveID &gt; @good_archiveselect * FROM [arx_Archive].[dbo].[Employees] WHERE archiveID &gt; @good_archiveselect * FROM [arx_Archive].[dbo].[EmployeeWorkCodes] WHERE archiveID &gt; @good_archive[/code]Finally if you run the code below you should see if you have any triggers on these tables.[code="sql"]SELECT so.name , st.nameFROM arx_Archive.sys.triggers st INNER JOIN arx_Archive.sys.objects so ON st.parent_id = so.object_idWHERE so.name IN ( 'AppSettings', 'Employees', 'EmployeeWorkCodes' )[/code] (no rows means no triggers etc.)Wed, 07 Nov 2012 09:16:02 GMTDavid McKinneyRE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxI 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.[code="sql"]SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archives]') AND type in (N'U'))BEGINCREATE 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]ENDSET 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[/code]Wed, 07 Nov 2012 08:58:04 GMTfwob04RE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspx[quote][b]fwob04 (11/7/2012)[/b][hr]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.[/quote]Just checking here. [quote]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.[/quote]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.Wed, 07 Nov 2012 08:03:53 GMTSean LangeRE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxIt 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.Wed, 07 Nov 2012 06:58:36 GMTfwob04RE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxAs 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.Tue, 06 Nov 2012 16:38:11 GMTTavaRE: script uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxIs the datatype for archiveID int? Do you have any triggers on these tables?Tue, 06 Nov 2012 15:15:12 GMTSean Langescript uses INT variable as if one lower than set valuehttp://www.sqlservercentral.com/Forums/Topic1381725-391-1.aspxThis 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?[code="sql"]Declare @good_archive intset @good_archive = 14DELETE FROM [arx_Archive].[dbo].[AppSettings] WHERE archiveID &gt; @good_archiveDELETE FROM [arx_Archive].[dbo].[Employees] WHERE archiveID &gt; @good_archiveDELETE FROM [arx_Archive].[dbo].[EmployeeWorkCodes] WHERE archiveID &gt; @good_archive[/code]Tue, 06 Nov 2012 15:10:32 GMTfwob04