Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki Finding the SQL Data folder
    • Sign In
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities sisNET - English
    • +OpenUtilities Substation
    • Promis.e Paths
    • -Promis.e Wiki
      • +CONNECT Edition - Promis.e
      • About Promis.e
      • +Administration Features - Promis.e
      • +APIs_VBA - Promis.e
      • +Automation Features - Promis.e
      • +Component Features - Promis.e
      • +Component Management - Promis.e
      • +CONNECT Services - Promis.e
      • +Data Manager - Promis.e
      • -Database Platform Support - Promis.e
        • Backing Up Promis.e Data Automatically
        • Converting to an SQL Parts Database
        • +Creating and Updating the Project Database
        • Desired SQL server Not Listed in the available Servers List
        • Disable Auto Shrink to avoid performance degradation
        • Error - Could not find SQL Server instance named BENTLEYECAD
        • Error - Could not find stored procedure
        • Error - Database version does not match the application
        • Error - Index was outside the bounds of the array (Microsoft.SqlServer.smo)
        • Error - Invalid database name - on Project Database Utility
        • Error - Invalid object name
        • Error - Login failed for Ecaduser: Reason: The Password of the account Expired
        • Error - No version resource could be located in database
        • Error - OraOLEDB.Oracle provider is not registered
        • Error - T-SQL execution command line utility stopped working
        • Error - The database is for product... you cannot use it
        • Error at Startup - Error 0 No connection could be made because the target machine actively refused it.
        • Error at Startup - Error 40 Could Not Open a Connection
        • Error at startup - The Type initializer for ECT.ECAD.API.SystemSetup threw an exception
        • Error at Startup - Unrecognized database format, catalog.mdb
        • Error deleting project - transaction log full
        • Error messages encountered due to case sensitive collation of SQL Server
        • Error on backup - Invalid database object reference
        • Error on Project Database Utility - Cannot find the file specified
        • Error running SQL scripts - Incorrect syntax
        • Exception thrown when creating a project due to SQL permissions
        • Exception thrown when opening a page, transaction log is full
        • Finding the SQL Data folder
        • Installing SQL Server
        • Minimum SQL user permissions
        • Project Database Version - Promis.e
        • Rebuild indexes in the SQL project database
        • Rules for Regular Identifiers
        • SQL Server FAQ
        • Troubleshooting Project Database Connection Problems
      • +Display - Promis.e
      • +Drawing Management - Promis.e
      • +Engineering Design Considerations - Promis.e
      • Error - Object reference...General
      • +Find_Replace - Promis.e
      • Finding the Tutorial
      • +Installation_Configuration - Promis.e
      • +Interoperability - Promis.e
      • +Licensing - Promis.e
      • +Maintenance Features - Promis.e
      • +Migration - Promis.e
      • +OS Support - Promis.e
      • +Panel Layout - Promis.e
      • +Performance - Promis.e
      • +PowerPlatform Support - Promis.e
      • +Print_Publish Features - Promis.e
      • +ProjectWise Integration - Promis.e
      • Promis.e Help file
      • +Reports_Output Features - Promis.e
      • +Template Management - Promis.e
      • +Text Tools - Promis.e
      • Training FAQs
      • +Wire Numbering - Promis.e
      • +Wiring Features - Promis.e
      • +Project Management - Promis.e
      • Promis.e Title
      • Working from home with Promise.e
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

     
     Questions about this article, topic, or product? Click here. 

    Finding the SQL Data folder

      Product(s): Promis.e, Bentley Substation
      Version(s): through 08.11.13.140
      Environment: N/A
      Area: Database Platform Support
      Subarea: N/A

    Background

    When creating SQL Server databases using a script, the SQL data folder path included in paths to the database file and the transaction log in the script may not reflect the actual directory structure of the machine.  In this case the paths need to be corrected, since the CREATE DATABASE instruction will not create folders that do not exist and will instead fail.

    Below is a script useful for determining the data folder path used by the instance of SQL Server to which SQL Server Management Studio is currently connected. The database and transaction log can be created in this path. 

    Script

    DECLARE @device_directory NVARCHAR(520)
    SELECT @device_directory =
    SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
    FROM master.dbo.sysaltfiles
    WHERE dbid = 1 AND fileid = 1
    print @device_directory

    Steps to Accomplish

    1. In SQL Server Management Studio, click the New Query button.
    2. Copy the above script into the new query pane.
    3. Right click the query pane and select Execute, or click the Execute button on the toolbar.

    The path that results in the Messages pane can then be used as needed.  For example: highlight the path and press CTRL+C or right-click > Copy to copy to the clipboard, then highlight the similar but incorrect portion of the database path in the database creation script, and press CTRL+V to paste and overwrite the incorrect path.  Repeat for the incorrect portion of the transaction log path.

    See Also

    Creating and Updating the Project Database

    External Links

    Retrieving the DATA folder installation path from MS SQL Server 

      Original Author: Matt_P
    • Database Platform Support
    • promis.e
    • SQL Server
    • Bentley Substation
    • en
    • Share
    • History
    • More
    • Cancel
    • Matt_P Created by Communities MVP Matt_P
    • When: Wed, Oct 10 2012 5:35 PM
    • Michael Kelley Last revision by Bentley Colleague Michael Kelley
    • When: Mon, May 11 2020 4:18 PM
    • Revisions: 10
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies