January 22, 2009 at 7:15 pm
Background - I mistakenly reindexed my prd db with a very low fill factor (10). The db grew from 20 gb to 128. I have since reindexed and things are pretty much back to normal. The db is still 56 gb, but I can live with that.
About the time I was going through all of this, a sql statement from an app using the same db stopped working. I don't think my little misadventure caused this, but I admit it looks highly suspect. Here is that code:
SELECT A.DEPTID, A.DESCR AS DEPTDESCR, (CONVERT(CHAR(10),A.EFFDT,121)) as EFFDATE,A.SETID
FROM PS_DEPT_TBL A
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL A_ED
WHERE A.SETID = A_ED.SETID
AND A.DEPTID = A_ED.DEPTID
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.DEPTID <> 'ALL'
AND A.DEPTID = 2010
ORDER by DEPTDESCR
__________________
The problem is with the "AND A.DEPTID = 2010". This was running fine in prd with no quotes around the 2010. Now it is failing with
Syntax error converting the varchar value 'ALL ' to a column of data type int. It will work with quotes around it, but the point is that obviously, something changed.
Here is part of the scripted table def :
CREATE TABLE [dbo].[PS_DEPT_TBL] (
[SETID] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
[DEPTID] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[EFFDT] [PSDATE] NOT NULL ,
[EFF_STATUS] [char] (1) COLLATE Latin1_General_BIN NOT NULL ,
____________
My question is what could have happened that would cause that sql statement to stop working?? It works fine in the last good backup I have before my reindex fiasco (told you it looks very suspect). I just don't know exactly how it could have happened. A bonus would be how to fix it.
Thanks for any and all help!
January 23, 2009 at 5:32 am
I fixed it by manually dropping and recreating the indexes. I thought the following did the same thing.
SET QUOTED_IDENTIFIER ON
exec sp_MSforeachtable
@command1 = "Print '?'",
@command2 = "DBCC DBREINDEX ('?')"
Wouldn't the about do the same thing??
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply