How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

|||I am referring to the path that you see in the Service Properties through the SQL Server Configuration Manager, the tooltip and the Online Help is a bit contradictory concerning this path. Nevertheless I figured out where to change it in the registry but instead I did a full reinstall of SQL Server 2005.|||

You don't want to change this path. If you do the SQL Server won't start. The "Binary Path" which is listed is the path to the actualy exe file which is the SQL Server service. The only way to change this would be to uninstall and reinstall the SQL Server.

What is the end result that you are trying to get?

|||If you look closely and the tooltip for that path and the same explanation in the help when you hit F1 then you will notice that it is not really clear what this path is for. This path is not the binary path because I installed SQL Server in C:\Program Files\... and during the installation I changed the location for the databases to D:\Databases. The path I am talking about was set to D:\Databases but afterwards I had to move the data files to E:\Databases so in SQL Server I changed the database location to E:\Databases but for some reason the in the SQL Server it still pointed to D:\Databases which in the meantime no longer existed. I finally had to reinstall SQL Server anyway because the binaries had to be in the D:\Program Files\ and not on the C-drive.

How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

|||I am referring to the path that you see in the Service Properties through the SQL Server Configuration Manager, the tooltip and the Online Help is a bit contradictory concerning this path. Nevertheless I figured out where to change it in the registry but instead I did a full reinstall of SQL Server 2005.|||

You don't want to change this path. If you do the SQL Server won't start. The "Binary Path" which is listed is the path to the actualy exe file which is the SQL Server service. The only way to change this would be to uninstall and reinstall the SQL Server.

What is the end result that you are trying to get?

|||If you look closely and the tooltip for that path and the same explanation in the help when you hit F1 then you will notice that it is not really clear what this path is for. This path is not the binary path because I installed SQL Server in C:\Program Files\... and during the installation I changed the location for the databases to D:\Databases. The path I am talking about was set to D:\Databases but afterwards I had to move the data files to E:\Databases so in SQL Server I changed the database location to E:\Databases but for some reason the in the SQL Server it still pointed to D:\Databases which in the meantime no longer existed. I finally had to reinstall SQL Server anyway because the binaries had to be in the D:\Program Files\ and not on the C-drive.

How to change a string

Hi,

I have a string like (Mike,David,John...). How can I change this string into a proper syntax of

('Mike','David','John'...) so that I can use it in an "IN" statement some where else.

Select * from TableName where Customer_Name In ('Mike','david','john',...)

Any function? Any Idea?

Thanks

An easier method might be to create a parameter called CustomerName and then use the following code:

Select * from TableName where Customer_Name IN(@.CustomerName)

|||

won't work, he needs to put the single quote on them...

Search for a user on this forum called Lisa Nicholls. she posted a solution for someone with a similiar issue, She even posted the code to use.

|||

With my example he would not have to worry about the quotes as the SQL operation addresses internally using the parameter. I have several of my reports built this way which allows me to avoid the quote issue.

|||

Thanks every one for the feed back. I have created the below function and it work fine.

CreateFUNCTION [dbo].[fn_StringWithQuotes] (@.InputString Varchar(8000))

RETURNSnvarchar(4000)

AS

BEGIN

DECLARE @.IDTable TABLE(Item Varchar(255))

DECLARE @.item VARCHAR(255)

WHILE(DATALENGTH(@.InputString)> 0)ANDRIGHT(SUBSTRING(@.InputString,1,CHARINDEX(',',@.InputString)),1)=','

BEGIN

SET @.item =SUBSTRING(@.InputString,1,(CHARINDEX(',', @.InputString)-1))

INSERTINTO @.IDTable(Item)VALUES(@.Item)

SET @.InputString =SUBSTRING(@.InputString,(CHARINDEX(',', @.InputString)+1),DATALENGTH(@.InputString))

END

INSERTINTO @.IDTable(Item)VALUES(@.InputString)

DECLARE @.NAME VARCHAR(255)

DECLARE @.NEW_NAME VARCHAR(255)

SET @.NEW_NAME=''

SELECT @.NEW_NAME = @.NEW_NAME +''','''+A.ITEM FROM(SELECT ITEM FROM @.IDTable)A

SET @.NEW_NAME=RIGHT(@.NEW_NAME,LEN(@.NEW_NAME)-2)+''''

RETURN @.NEW_NAME

END