Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    18

    Unanswered: Function Return Value

    I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

    Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

    You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "\" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQL\Data" instead of "D:\MSSQL\Data" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

    Any suggestions? Thank you.

    HERE IS T-SQL FOR THE FUNCTION
    IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
    BEGIN
    DROP FUNCTION fn_sqlmgr_get_mdf_filepath
    END
    GO

    CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
    @MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
    )
    RETURNS NVARCHAR
    AS

    BEGIN

    --Extract the file path for the database MDF physical file.
    SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
    FROM master..sysfiles mdf
    WHERE mdf.groupid = 1

    SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@MDF_FILE_PATH)))

    RETURN @MDF_FILE_PATH

    END



    HERE IS THE TEST I AM USING AGAINST THE FUNCTION
    SET NOCOUNT ON

    DECLARE
    @MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.

    SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH )
    PRINT @MDF_FILE_PATH

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Any suggestions?

    YEah, rethink what you're doing....

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Posts
    18
    If you didn't have any information that would possibly be helpful in my question, please leave future posts to those who would be more intelligent in their responses.

    If you see something that I am doing wrong, then why not offer a suggestion instead of either keeping the answer to yourself or acting more intelligent than what you are. After all, this is what this forum is intended for.

    Thank you.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry you feel that way...

    If you can supply us with what you're doing, I'm sure the people here can assist...if you don't like what I say, I'm sure someone will step...need more details though....

    This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.
    Any "AUTO-ADMIN" stuff is always risky....(my own opinion) MOO

    Why do you have to do this? Are you releasing hundreds of databases?

    Also, unless there are performance issues involved, why deviate from standard practices...

    AND HOW DARE YOU ACCUSE ME OF BEING INTELLIGENT!

    The nerve...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    Quote Originally Posted by A3VDO
    CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
    @MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
    )
    RETURNS NVARCHAR
    AS
    The return value of the function is declared as NVARCHAR, a single character. You need to declare the return value as a character array.

    RETURNS NVARCHAR(1000)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
    @MDF_FILE_PATH NVARCHAR(1000)
    )
    RETURNS VARCHAR(1000)
    AS
    
      BEGIN
    
    	SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
    	FROM master..sysfiles mdf
    	WHERE mdf.groupid = 1
    	
    	SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@MDF_FILE_PATH)))
    	
    	RETURN @MDF_FILE_PATH
    
      END
    GO
    
    SET NOCOUNT ON
    DECLARE @MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
    SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH )
    PRINT @MDF_FILE_PATH
    SET NOCOUNT OFF
    GO
    
    DROP FUNCTION fn_sqlmgr_get_mdf_filepath
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Yeah .. but in spite of all the help from BK .... you need to rethink it anyway
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that Brett saw you doing something potentially VERY dangerous, and was trying to get some more information so that we could either:

    a) give you appropriate code
    b) help you find a better (safer) solution to your problem
    c) warn you that, as cartographers of olde would say: "here be dragons".

    -PatP
    Last edited by Pat Phelan; 06-14-04 at 14:36.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    I think that Brett saw you doing something potentially [i[VERY[/i] dangerous, and was trying to get some more information so that we could either:

    a) give you appropriate code
    b) help you find a better (safer) solution to your problem
    c) warn you that, as cartographers of olde would say: "here be dragons".

    -PatP
    You know the funny thing about this?

    Trying to build a rocket ship, but can't get past retrun nvarchar
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    And i dont remember exactly ... but isnt there a registry key we could read to get the default location where a file would be created ....

    Hmmm ... what the hell I am thinking .. the files would be created int the default location anyway .... if you do not specify ... so whats with the function ... i am confused (once again )
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    May 2004
    Posts
    18
    Thank you Homer37 for your assistance. Your answer worked just fine.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Improper setting at the time of server installation leads to situations like this, where there is a need to write extra code. However, it also appears that you ARE trying to auto-create databases (I already picture a scene where something in other parts of your code got missed and you are sitting at a non-responsive server because your code ended up creating databases), unless I am misreading the post. To alter the default location you need to use xp_instance_regwrite rather than for every database to be created to reference the same server, following a call to xp_instance_regread.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •