How to change the instance name?

Hello DBA's

I have installed SQL Server 2005 on a machine with named instance, but later I noticed that I created it with the wrong name than desired. Now I need to change the instace name of the SQL Server that I have installed. How can I do that

Thanks

Satya

There is not a supported way to rename an instance. You need to uninstall and then reinstall.

Thanks,

Peter Saddow

|||

Thanks Peter

Satya

how to change the instance name back to default of SQL2k5

hi,

While installing SQL SERVER 2005, I had opted for providing custom name for the SQL Server and named it as for eg. as 'xyz' . Now I would prefer to change it back to default instance so that I can use server=localhost in my connection string of my ASP.NET page. With the custom instance name everytime I have to give
server="Machinename\xyz" which is annoying as I will have to change the connection strings in so many places for my exisiting ASP.NET page.

For e.g.

strConnection="server="Machinename\xyz";database=test;Integrated Security=SSPI;";

I tried using server=(local). It did not work...:(

Also on my another machine which has SQL2k5 installed with default instance I am able to use this string:
strConnection="server=localhost;database=test;Integrated Security=SSPI;";

while the same string I cannot use on the one in which I provided the instance name.

Guess uninstall is the only way.

anyone knows how can I change it back to default instance?

I'm not sure if there is a way to change an instance name. If you must do this, install another copy of SQL Server with the default instance name. Then backup your databases in the xyz instance. Restore them in the default instance. Then uninstall xyz instanace. As for the programs with changing connections strings in web apps, I would recommend pointing them to datasources instead of putting connections string in there. Then, if you modify your sql server configuration you simply have to change the datasouce and not all of your web.config files.|||

Thanks JonM. I know how to create datasources but I don;t know what needs to be put in the ASP.NET file or web.config files to establish the connection.

Suppose I have database called 'test' with DSN name as 'test' in the 'xyz' instance of SQL Server on my machine named 'Development'

How do I achieve this?

Thank you once again for your help.

|||

Basically you change your connection string to 'Datasource=xyx', and remove all of the other stuff, you may still have to specify a username and password if you didnt specify it in the datasouce.

sql

How to change the default "filter" on Executionlog?

In the ReportServer database there is a table named Executionlog, which
contains data on the reports executed. But at any given time, it only seems
to contain data for the last 2 months. How do I change this filter?Found it!
Open SQL Server Management Studio and connect to Reporting Services. Right
click on the server name in the Object Explorer and select Properties. Under
the "Logging" folder you can change days of history.
"Soren" wrote:
> In the ReportServer database there is a table named Executionlog, which
> contains data on the reports executed. But at any given time, it only seems
> to contain data for the last 2 months. How do I change this filter?

How to change the content?

Hi there !

I'm quite desperate as I can't find a quick method to partially change the content of a field.

I explain:
In a field named 'notes', PART of the content is:
<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>
This content is wrong.

It must be:
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>

How can I make the change with a SQL Query instead of manually make the change row by row (approximately 1500 rows contain the error!!!) considering the fact that abc@.domain.com is different in each of the rows?

Many thanks in advance for any help!

Best regards,

Gerardupdate daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'|||update daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'
Hi !

Thank you for replying. I appreciate.
But, as I'm quite old and learning, I want to really understand the syntax I'll write.

Some questions arise:
1 - Why have we "replace" repeated twice?
2 - The single and double quotes you wrote in your example doesn't correspond to those I put in my example.
3 - What will happen to the email addresses which are differents?

Be sure, professor, I certainly do not want to bother you! :-)

Best regards,

Gerard|||did you try it? what results did it give?|||Yes, I tried... on a copy of the table!
After two attempts, this worked fine:
UPDATE `table_name` SET column_name = REPLACE(column_name, '"', '') WHERE column_name LIKE '%href=\'mailto:"%';

I thank you for your valuable help.

Best.|||all you have done is remove the double quotes

in other words, you've changed this --<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>to this --<a href='mailto:abc@.domain.com'>abc@.domain.com</a>which is not what you asked

you asked for this --<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>|||YES you are right about the kind of quotes but the principle remained the same: remove the extra quotes just before and after the address, no matter if the quotes are single or double. I opted for the single quotes which are more "orthodox"! :-)|||either single or double can be used

see http://www.w3.org/TR/html4/intro/sgmltut.html#attributes

if you look at all the web sites out there (okay, just look at some, you don't really have to look at all of them) you will find that most of them use double quotes

also, the incidence of a single quote required inside a double-quoted string is more frequent than a double quote inside a single-quoted string

:)|||You are right.
I have read about attributes in the page you mentioned.
And I tried to follow your advice and to replace the single quotes with double quotes.

For example, I tried to replace where it is necessary
<a href='mailto:abc@.domain.com'>
with
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">
(Of course, this text isn't the only text contained in the field...)

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '', "") WHERE column_name LIKE '%mailto:\'%';
This query has no effect!

What's wrong?|||REPLACE(column_name, '''', '"')|||Still no effect !
And I perfectly know that more than a thousand rows must be affected...

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:\'%';|||you keep messing up in different places
WHERE column_name LIKE '%mailto:''%'|||Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';
...and nothing happens, right or wrong!|||what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:''%'and what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:"%'this should tell you why

:)|||Both queries return zero !!!!!

Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:
Autres adresses de courriel : <a href='mailto:jr.reverte@.laposte.fr'><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href='mailto:entsoa23@.fr.st'><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

and I want to change it to:
Autres adresses de courriel : <a href="http://links.10026.com/?link=mailto:jr.reverte@.laposte.fr"><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href="http://links.10026.com/?link=mailto:entsoa23@.fr.st"><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

This is driving me quite mad... :-) GRRRR !!!|||i believe you are not copying my exact code, but rather typing it in yourself, and making errors while typing

you must make very sure that you understand the difference between two single quotes in a row:''and a doublequote:"|||Obviously, yes. I know the difference.
And I copied the exact code you posted.

But I found one error.
It isn't
'%mailto:\'%'
but
'%\'mailto:%'
we are looking for.

The position of the quote was wrong.
And this change makes the query returning a number!

Using this change, I unsuccessfully (no row affected) tried to apply the modifying query this way (real names of table and column):
UPDATE `_annuairecopy` SET notes = REPLACE ( notes, '', "") WHERE notes LIKE 'href=%\'mailto:%\'%';
(please report to the REAL content of field I previously posted)|||please stop using those horrible backslashes!!!
UPDATE `_annuairecopy`
SET notes = REPLACE ( notes, '''', '"')
WHERE notes LIKE '%href=''mailto:%'|||Yes indeed, they are horrible.
But I was so tired to get error messages each time I used an even number of single quotes without a backslash!

The query has worked fine !!!
Could you imagine how happy I am?

I thank you for your patience and all the time you spent helping me.

Best from Gerard.

How to change MM/DD/YYYY format to DD/MM/YYYY format by using SQL query.

I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
Hello,
For mm/dd/yyyy
Select CONVERT(CHAR(10),columns_name,101) from Table_name
For DD/MM/YY
Select CONVERT(CHAR(10),columns_name,103) from Table_name
Thanks
Hari
<amripaul@.gmail.com> wrote in message
news:1181557806.349855.6210@.x35g2000prf.googlegrou ps.com...
>I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
>
|||Thanx for your help.

How to change Connectionstring in SQLCLR project?

Hi,

I have created a SQLCLR (database) using C#.. named SQLCLRtest

The Connection string is stored in SQLCLRtest.csproj file

here is code of csproj file

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<AssemblyOwner>
</AssemblyOwner>
</PropertyGroup>
<ProjectExtensions>
<VisualStudio>
<FlavorProperties GUID="{c252feb5-a946-4202-b1d4-9916a0590387}">
<DatabaseConnection Name="Data Source=DBserver;Initial Catalog=TestDB;Integrated Security=True" Provider="{91510608-8809-4020-8897-FBA057E22D54}" ConnectionString="01000000D08C9DDF0115D1118C7A00C04FC297EB01000000A2744997FFD51E459D0421E51E830EF30000000002000000000003660000A8000000100000005B59ACF96DA2A587CBFA27595B0F245E0000000004800000A000000010000000BB1D5F562AC3FE7F56F8E57C36E0E7A4B0000000CE828F399233A389D95E2D99B2CAA64DE5F5A19EF0CBB716D195DF60EE38B58B0C07674E2F80538C02ED27200C79A71B0F6F9177E598089CDA95B8DDEEF966A958C6EDE4E72CABBC39941FEED534E3384EF3A4B4A51704726BF5D43F2C3C9BD674885B9675FECD86E54498ED9E1957FCD7DCF0CE8ED99C8529FD9234EB4E760FDD6819E3E42A7771E0A5B18452C01C13976C0DDDF1B5B87D75F0490762C6A004AD093A3DF9210F7D03371D67E4901EB51400000005557E36590040C06F796463ABFEC165D2E60750" />
</FlavorProperties>
</VisualStudio>
</ProjectExtensions>
</Project>

Problem:

I have an sp which start an external process, It is working at my local machine but does not work at remote server.

FACTS:

Local machine has SQLExpress (SQL version) while remote machine has SQL Enterprise ..

SQL service is running under the System Account at both machines..

I have enabled the Sql server Service to interact with desptop, so that it can start a process in GUI mode.

I thin there is something in connectionstring which does not allow the application to connect to server with appropriate rights.

How I can chage this setting .. is it possible to write this info manually..

Please, comments

Thanks

You should be able to change the connection string from the database tab in project properties. When you click on browse, It will allow you to choose from different existing connections or add a new reference.

Thanks,

-Vineet

|||

Muna,

Did Vineet's post solve your problem? The reply is correct to my knowledge.

Derek