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 4 years, 10 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 4 years, 10 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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy