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_PATHAny suggestions?
YEah, rethink what you're doing...
MOO|||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.|||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...|||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)|||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|||Yeah .. but in spite of all the help from BK ... you need to rethink it anyway ;)|||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|||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|||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 ;))|||Thank you Homer37 for your assistance. Your answer worked just fine.|||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.
