Implicit Conversion - What can affect it

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

  • 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