Script works on one server, errors on others

  • 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

  • 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 2 years, 2 months ago by  homebrew01.
  • 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.
  • 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+'%'

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

    • This reply was modified 2 years, 2 months ago by  homebrew01.
  • 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 6 (of 6 total)

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