Post reply

Script works on one server, errors on others

  • July 9, 2020 at 6:14 pm

    #3768776

    I am running a script that yields exactly what I'm looking for in test, but when I copy and paste the identical script to a prod box I get:

    Msg 537, Level 16, State 3, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    I can recopy and repaste it back to test, so I know there are no changes to the script, and it still works great.

    What does this mean? Granted, test is 2019 and prod is 2017. But it's a fairly simple T-SQL script. I've attached it... but I don't think it's really an issue with the script, and I really don't believe it's an issue with the versions.

    Signed,

    Perplexed

  • July 9, 2020 at 6:30 pm

    #3768777

    I don't see your T-SQL. Paste it as "code" into the body of your post.

    Are the tables & columns identical on the 2 servers ?

    • This reply was modified 3 years, 9 months ago by  homebrew01.

  • July 9, 2020 at 6:45 pm

    #3768792

    I've reattached the script as a *.txt file. The forum didn't accept the use of a *.sql file. Understandable.

    It runs against all DBs, tables and dbnames are not relevant.

    Attachments:
    You must be logged in to view attached files.

  • July 9, 2020 at 6:46 pm

    #3768794

    SELECT db.name AS database_name, mf.name as actual_logical_name,

    LEFT(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1)),

    LEN(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))) - 4) as new__logical_name,

    'ALTER DATABASE ['+db.name +'] MODIFY FILE (NAME=N'''+ mf.name + ''',NEWNAME=N'''+

    LEFT(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1)),

    LEN(REVERSE(LEFT(REVERSE(mf.Physical_Name),CHARINDEX('\', REVERSE(mf.Physical_Name), 1) - 1))) - 4) +''')' as change_script

    FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id

    WHERE db.database_id>4 AND mf.Physical_Name NOT LIKE '%'+mf.name+'%'

  • July 9, 2020 at 7:12 pm

    #3768797

    I ran it on a 2012 , 2016 and 2019 servers without errors.

    • This reply was modified 3 years, 9 months ago by  homebrew01.

  • July 9, 2020 at 7:16 pm

    #3768799

    Figured it out.  It was a cloud thing. Backlashes needed to be forward slashes.

    Thanks for looking at it though.

Viewing 6 posts - 1 through 5 (of 5 total)

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