Change Unc Path On Sql

I have a question on how to change a hyperlink path. I have an ADP that has our scanned files linked to it the path is on SQL server. The problems is that we are migrating sql server and the files to their own seperate server,
\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf

The server is called \\gcfsql\ Instead of gcfs01.
does anyone know how we can change the unc path to gcfsql without

\\gcfsQL\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES\5630.pdf

Any help would be greatly appreciatedNot sure I understand the question fully. Do you mean you have a bunch of UNC paths stored in a table and you want to update the server name in each path?

If so, you could do something like this:

update mytable set path = replace(path,'\\oldserver\','\\newserver\')|||See the thing is that we have about over 2000 employees who get an employee number and their file is saved in the old server as their number
and the only thing we need to change is the destination server the rest is fine but I dont want to mess anything up. Know what I mean, so I'm asking the Gurus :)|||Run Jezemine's replace statement as a select to see what it produces. Unless any of your employees have "\\" in their filenames, it should give you what you want.|||update EmployeeDocuments set EmployeeDocs = replace(\\gcfs01\database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED ACTIVE FILES,'\\gcfs01\','\\gcsql\')|||no, don't run that. it will update every record with the same value. certainly not what you want. you want this:

update EmployeeDocuments set EmployeeDocs = replace(EmployeeDocs,'\\gcfs01\','\\gcsql\')|||You realize this goes against Normalization. In a disaster recovery situation, you may be saddled with this breaking again - just when you least have time for it. Or you may want to create a test environment and wind up with someone updating the production employee records.

Things like "where we keep stuff" and "the servername we keep it on" should be in a central place and never hard coded into "each employee's record" - unless that can be different for different groups of employees.

A little late to fix, but something to file away in the brain for the next time this sort of design decision arises.|||Thats exactly what I did and it worked.

first I changed the ntext data type to nvchar(300)
and it worked .

Thank you guys worked like a charm|||its just that we are changing servers thats all and we wanted to change the path from the old server to the new server thats all.

Change SSIS File System Connection

Hi,

i've been asked to move our ssis project to another machine
and i've a lot of file system connection whice i must rename them to the new path in the other machine.
there is away to rename the file system connection dynamic or i must go and rename one by one ?
thanks.You'll have to edit them one by one, but the good news is that when you are editing each one, you can use expressions to dynamically create the file path. You can also use package configurations to allow you to set the "base path" via an environment variable, XML file, etc...

You may also be able to open the .dtsx file, though, and use Search/Replace to alter the file paths.|||

Ok thanks i will use Search/Replace to alter the file paths but after it i will have to enter each package and test connection so it will work.

there is away to bypass it?

thanks.

|||Why do you have to test it so that it will work? If the file format is the same, changing the directory shouldn't have any impact.|||

what i meant was that if i move my project to another machine i have to enter each package and there enter to each connection to sql server or odbc edit them,test connection and save the package.

there is any way to skip those levels?

|||

Best solution is to take your own time and make use of config files so that you dont have to bang your head on this in future.

Also i dont think you need to change the settings for ODBC and SQL connections unless and until you plan to execute the package on a different server.

Change of default path while creating New DB

Hi,
while creating new database and setting the Data and Log file by
default it shows the default path (based on SQL Server Installation) .
If we want to change the path we will be able to change. But while creating
Data and Log file i want the the default path it shows to some other default
path.
Please give me the solution as early as possible
Thanks,
HerbertHi
Enterprise Manager
Click on the Server
Right click
Select properties
Database Settings Tab
Change the values 2/3 of the way down the page "new database default
location".
From that point onwards, new databases created use those paths.
Regards
Mike
"Herbert" wrote:
> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creating
> Data and Log file i want the the default path it shows to some other default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert|||server propeties->database settings->def. data&log dir?
I think this settings stored somewhere in the registry.
"Herbert" wrote:
> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creating
> Data and Log file i want the the default path it shows to some other default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert|||Hi,
CHange the value for the below registry keys.
For a SQL 2000 default instance, the default folders are stored in the
registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\MSSQLServer\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\MSSQLServer\MSSQLServer\DefaultLog
For named Instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\Microsoft SQL Server\[Instance
Name]\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\Microsoft SQL Server\[Instance
Name]\MSSQLServer\DefaultLog
Enterprise Manager:-
These can be viewed/changed with Enterprise Manager (server properties -->
database settings). This will also update the registry.
Thanks
Hari
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:95A73C48-62BB-4264-B121-2D3FB8D4E4D7@.microsoft.com...
> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while
> creating
> Data and Log file i want the the default path it shows to some other
> default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert

Change of default path while creating New DB

Hi,
while creating new database and setting the Data and Log file by
default it shows the default path (based on SQL Server Installation) .
If we want to change the path we will be able to change. But while creating
Data and Log file i want the the default path it shows to some other default
path.
Please give me the solution as early as possible
Thanks,
Herbert
Hi
Enterprise Manager
Click on the Server
Right click
Select properties
Database Settings Tab
Change the values 2/3 of the way down the page "new database default
location".
From that point onwards, new databases created use those paths.
Regards
Mike
"Herbert" wrote:

> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creating
> Data and Log file i want the the default path it shows to some other default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert
|||server propeties->database settings->def. data&log dir?
I think this settings stored somewhere in the registry.
"Herbert" wrote:

> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creating
> Data and Log file i want the the default path it shows to some other default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert
|||Hi,
CHange the value for the below registry keys.
For a SQL 2000 default instance, the default folders are stored in the
registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\MSSQLServer \MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\MSSQLServer \MSSQLServer\DefaultLog
For named Instance:
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\Microsoft SQL Server\[Instance
Name]\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\Microsoft SQL Server\[Instance
Name]\MSSQLServer\DefaultLog
Enterprise Manager:-
These can be viewed/changed with Enterprise Manager (server properties -->
database settings). This will also update the registry.
Thanks
Hari
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:95A73C48-62BB-4264-B121-2D3FB8D4E4D7@.microsoft.com...
> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while
> creating
> Data and Log file i want the the default path it shows to some other
> default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert

Change of default path while creating New DB

Hi,
while creating new database and setting the Data and Log file by
default it shows the default path (based on SQL Server Installation) .
If we want to change the path we will be able to change. But while creating
Data and Log file i want the the default path it shows to some other defaul
t
path.
Please give me the solution as early as possible
Thanks,
HerbertHi
Enterprise Manager
Click on the Server
Right click
Select properties
Database Settings Tab
Change the values 2/3 of the way down the page "new database default
location".
From that point onwards, new databases created use those paths.
Regards
Mike
"Herbert" wrote:

> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creatin
g
> Data and Log file i want the the default path it shows to some other defa
ult
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert|||server propeties->database settings->def. data&log dir?
I think this settings stored somewhere in the registry.
"Herbert" wrote:

> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while creatin
g
> Data and Log file i want the the default path it shows to some other defa
ult
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert|||Hi,
CHange the value for the below registry keys.
For a SQL 2000 default instance, the default folders are stored in the
registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi_crosoft\M
SSQLServer\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Mi_crosoft\M
SSQLServer\MSSQLServer\DefaultLog
For named Instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi_crosoft\M
icrosoft SQL Server\[Instance
Name]\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Mi_crosoft\M
icrosoft SQL Server\[Instance
Name]\MSSQLServer\DefaultLog
Enterprise Manager:-
These can be viewed/changed with Enterprise Manager (server properties -->
database settings). This will also update the registry.
Thanks
Hari
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:95A73C48-62BB-4264-B121-2D3FB8D4E4D7@.microsoft.com...
> Hi,
> while creating new database and setting the Data and Log file by
> default it shows the default path (based on SQL Server Installation) .
> If we want to change the path we will be able to change. But while
> creating
> Data and Log file i want the the default path it shows to some other
> default
> path.
> Please give me the solution as early as possible
> Thanks,
> Herbert