Changing connection credentials

Hi,
I would like to change the credentials that are used at run time by the
reportserver service to connect to the SQL Server instance.
Can anybody please help me?
Regards!!Cookie =EB=FA=E1:
> Hi,
> I would like to change the credentials that are used at run time by the
> reportserver service to connect to the SQL Server instance.
> Can anybody please help me?
> Regards!!
http://support.microsoft.com/?id=3D306158
Impersonate a Specific User in Code
To impersonate a specific user only when you run a particular section
of code, use the following code:
<%@. Page Language=3D"C#"%>
<%@. Import Namespace =3D "System.Web" %>
<%@. Import Namespace =3D "System.Web.Security" %>
<%@. Import Namespace =3D "System.Security.Principal" %>
<%@. Import Namespace =3D "System.Runtime.InteropServices" %>
<script runat=3Dserver>
public const int LOGON32_LOGON_INTERACTIVE =3D 2;
public const int LOGON32_PROVIDER_DEFAULT =3D 0;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll")]
public static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet=3DCharSet.Auto)]
public static extern bool CloseHandle(IntPtr handle);
public void Page_Load(Object s, EventArgs e)
{
if(impersonateValidUser("username", "domain", "password"))
{
//Insert your code that runs under the security context of a specific
user here.
undoImpersonation();
}
else
{
//Your impersonation failed. Therefore, include a fail-safe mechanism
here.
}
}
private bool impersonateValidUser(String userName, String domain,
String password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token =3D IntPtr.Zero;
IntPtr tokenDuplicate =3D IntPtr.Zero;
if(RevertToSelf())
{
if(LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) !=3D 0)
{
if(DuplicateToken(token, 2, ref tokenDuplicate) !=3D 0)
{
tempWindowsIdentity =3D new WindowsIdentity(tokenDuplicate);
impersonationContext =3D tempWindowsIdentity.Impersonate();
if (impersonationContext !=3D null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if(token!=3D IntPtr.Zero)
CloseHandle(token);
if(tokenDuplicate!=3DIntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
</script>

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoonOn the clientside, you would just run Client Network Utility (cliconfg.exe).
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegroups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac...ds/default.aspx)
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegroups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>|||oj:
Thank you.
JCVoon

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoonOn the clientside, you would just run Client Network Utility (cliconfg.exe).
--
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegroups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac/downloads/default.aspx)
--
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegroups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>|||oj:
Thank you.
JCVoon

change the TCP/IP port SQL Server listens on

Hi:
To change the TCP/IP port SQL Server listens on:
1) Launch the Server network utility on the server
2) Select the instance you want to configure from the drop-down
3) Select TCP/IP from Enabled protocols
4) Click Properties
5) Change the default port to a different port
6) Restart SQL Server service
And for client side what should we do ? Normally i didn't install
client access library on the client site, so the client network utility
will not be in the client machine right ?
Please help
Thanks
JCVoon
On the clientside, you would just run Client Network Utility (cliconfg.exe).
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143613530.062172.268920@.v46g2000cwv.googlegr oups.com...
> Hi:
> To change the TCP/IP port SQL Server listens on:
> 1) Launch the Server network utility on the server
> 2) Select the instance you want to configure from the drop-down
> 3) Select TCP/IP from Enabled protocols
> 4) Click Properties
> 5) Change the default port to a different port
> 6) Restart SQL Server service
>
> And for client side what should we do ? Normally i didn't install
> client access library on the client site, so the client network utility
> will not be in the client machine right ?
> Please help
> Thanks
> JCVoon
>
|||oj:
Is the Client Network Utility (cliconfg.exe) exists in default windows
installation ?
Thanks
JCvoon
|||Cliconfg.exe is installed part of the MDAC
(http://msdn.microsoft.com/data/mdac/...s/default.aspx)
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143679737.916913.97130@.t31g2000cwb.googlegro ups.com...
> oj:
> Is the Client Network Utility (cliconfg.exe) exists in default windows
> installation ?
> Thanks
> JCvoon
>
|||oj:
Thank you.
JCVoon

CHANGE THE PROVIDERS TO USE SQL SERVER INSTEAD OF SQL EXPRESS

In step one of this Blog ,what's this mean

Step 1: Create or obtain a blank SQL database instance

and how do i go about doing it

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

That means to get a SQL database which does not have the schema (tables, stored procedures, functions, etc.) for membership, so that you can useaspnet_regsql.exe to create the required schema.|||

I'm a newbie so can you explain what u mean by getting a schema

Can u explain for example where do i start to make the config in VWD or using one of the SQL DATABASE, and if so which one. My provider is running SQL 2000

If you can break it down like the first step to do . My objective is to run a asp .net page

But i cannot figure out how to get in to run on the server But it works on my local machine. but when i upload it... i get the error message 40 about remote connection ,and i was told because i have to config VWD2005 to run on SQL 2000

Thanks for your help

|||

Hi bigmike40, let's discuss it in this post:

http://forums.asp.net/thread/1362712.aspx

|||

Ok

Change SQL Server Authentication Programmatically

Hi,

Does anyone here know whether it is possible to change the
authentication method of a SQL server instance programmatically, rather
than going through enterprise manager.

I am using SQL-DMO (under C#) for some other things, but the
documentation is so bad that I can't find out whether what I want to do
can be done. I have also tried googling but came up empty handed.

I hope that someone can help me.

Cheers
JonoHi

At a guess try SetSecurityMode under the SQLServer/IntegratedSecurity Object

John
"Jono Price" <jonathan_daivd_price@.hotmail.com> wrote in message
news:4298443f$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono|||John Bell wrote:
> Hi
> At a guess try SetSecurityMode under the SQLServer/IntegratedSecurity Object
> John

Thank you so much. I couldn't believe that it wasn't possible, but I
also couldn't find that.

Thanks again.

Jono

Change SQL Server Authentication method programmatically

Hi,
Does anyone here know whether it is possible to change the
authentication method of a SQL server instance programmatically, rather
than going through enterprise manager.
I am using SQL-DMO (under C#) for some other things, but the
documentation is so bad that I can't find out whether what I want to do
can be done. I have also tried googling but came up empty handed.
I hope that someone can help me.
Cheers
JonoThe only thing I know about, though I am not quite familar with SQL-DMO is
to change the regkey:, using the XP xp_regwrite
For registry changing try this regkey:
Default instance:
" HKEY_LOCAL_MACHINE\Software\M_icrosoft\M
SSqlserver\MSSqlServ_er\LoginMode"
to 2 for mixed-mode or 1 for integrated.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jono Price" <jonathan_daivd_price@.hotmail.com> schrieb im Newsbeitrag
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono|||"Jono Price" schrieb:
> Hi,
> Does anyone here know whether it is possible to change the
> authentication method of a SQL server instance programmatically, rather
> than going through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the
> documentation is so bad that I can't find out whether what I want to do
> can be done. I have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono
This is a VB solution (with SQLDMO). You'll have to translate this to C#
yourself.
On Error GoTo ErrSec
Dim srv As SQLDMO.SQLServer2
Set srv = New SQLDMO.SQLServer2
' integrated security login
srv.LoginSecure = True
' or standard security login
srv.LoginSecure = False
srv.Login = "sa"
srv.Password = "MyPassword"
srv.Connect "MyServer"
' switch to integrated
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated
' or switch to mixed mode
srv.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
' restart server: shutdown looses the connection and generates an error.
' we catch that error in the error section and restart the server.
srv.Shutdown
Exit Function
ErrSec:
srv.Disconnect
srv.Start (True) ' true reconnects automatically|||See SQLServer->ServerLoginMode()
_7s6h.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_7s6h.asp
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Jono Price" <jonathan_daivd_price@.hotmail.com> wrote in message
news:42984478$0$542$ed2619ec@.ptn-nntp-reader03.plus.net...
> Hi,
> Does anyone here know whether it is possible to change the authentication
> method of a SQL server instance programmatically, rather than going
> through enterprise manager.
> I am using SQL-DMO (under C#) for some other things, but the documentation
> is so bad that I can't find out whether what I want to do can be done. I
> have also tried googling but came up empty handed.
> I hope that someone can help me.
> Cheers
> Jono

Change SQL Port doesnt change

I am trying to change the port of an instance via the SSCM. Afther changing
the TCP/IP protocol -> ALL IP -> Blank/1433 and restart the service it goes
back to previous configuration.
Any ideas why? I want all my named instances to be using 1433.
The Default port is 1433 so U need not have to configure for 1433
"ipconfig2" wrote:

> I am trying to change the port of an instance via the SSCM. Afther changing
> the TCP/IP protocol -> ALL IP -> Blank/1433 and restart the service it goes
> back to previous configuration.
> Any ideas why? I want all my named instances to be using 1433.
>
|||For some reason, SQL Server does not like assigning named instances to port
1433. Even though a clustered instance lives in its own virtual server and
should not have a problem with 1433, I have run into problems trying to get
it to work. I would suggest choosing another port and assigning all your
instances to that port.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
news:9EFC71C2-CAD3-45CE-910E-A831ACCB8562@.microsoft.com...
>I am trying to change the port of an instance via the SSCM. Afther changing
> the TCP/IP protocol -> ALL IP -> Blank/1433 and restart the service it
> goes
> back to previous configuration.
> Any ideas why? I want all my named instances to be using 1433.
>
|||As I thought. But there is nothing in documentation that says that it's not
possible.
I have a x64 cluster where this works great and a x64wow cluster which is
troublesome.
I hoped MS would comment on this but I guess I have to file a support case.
To Khwaja - please read the manual. When installing clustered named
instances the port number is randomly assigned.
Thanks for replies.
Rune
"Geoff N. Hiten" wrote:

> For some reason, SQL Server does not like assigning named instances to port
> 1433. Even though a clustered instance lives in its own virtual server and
> should not have a problem with 1433, I have run into problems trying to get
> it to work. I would suggest choosing another port and assigning all your
> instances to that port.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
> news:9EFC71C2-CAD3-45CE-910E-A831ACCB8562@.microsoft.com...
>
|||By default, the Default instance is assigned TCP 1433, Named Instances use
Dynamic Ports; however, this same manual states that the best practice when
supporting connectivity through firewalls is to declare a static port
assignment, and then proceeds to note how to do this through the SSCM. So,
I am not sure which "manual" you are referring to.
Anthony Thomas

"ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
news:4778F614-5C49-43C3-BD45-A9E24EE1535E@.microsoft.com...
> As I thought. But there is nothing in documentation that says that it's
not
> possible.
> I have a x64 cluster where this works great and a x64wow cluster which is
> troublesome.
> I hoped MS would comment on this but I guess I have to file a support
case.[vbcol=seagreen]
> To Khwaja - please read the manual. When installing clustered named
> instances the port number is randomly assigned.
> Thanks for replies.
> Rune
> "Geoff N. Hiten" wrote:
port[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
your[vbcol=seagreen]
changing[vbcol=seagreen]
|||Hi Anthony
I cannot set the static port - it goes back to the dynamically set port when
I have changed it. The manual is BOL and Help file of SSCM. I have another
cluster where this is no problem so I wondered if there is a bug somewhere in
the software. I am using version 2153.
I want all my instances to use port 1433 since there are applications that
have hardcoded port 1433 as database port.
Regards
Rune
"Anthony Thomas" wrote:

> By default, the Default instance is assigned TCP 1433, Named Instances use
> Dynamic Ports; however, this same manual states that the best practice when
> supporting connectivity through firewalls is to declare a static port
> assignment, and then proceeds to note how to do this through the SSCM. So,
> I am not sure which "manual" you are referring to.
>
> Anthony Thomas
>
> --
> "ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
> news:4778F614-5C49-43C3-BD45-A9E24EE1535E@.microsoft.com...
> not
> case.
> port
> and
> get
> your
> changing
>
>
|||All instances in your enterprise or all instances on a multi-instanced
cluster? The former is achievable, the latter is impossible: each instance
on a single cluster must "listen" on a unique set of ports, one or more, as
the case may be, but unique from any other cohosted instance. These ports
can be static or dynamic per instance.
Now, Geoff has stated that he has had difficulty running named instance on
TCP 1433; however, I guarantee you that you can run on static ports. I
currently run multiple named instances running on TCP 11291, 11292, 11293,
etc. correlating the LSD with the named instance id. So, I know it works.
I've never tried to run a named instance under the default port, reserving
that for default (unnamed) instances.
I doubt your applications hard code ports. If they do, then you don't want
them running in your enterprise.
More likely, the other side hasn't a clue on how to configure named
instances or non-standard ports. Obviously, they have never worked with any
other DBMSes like Oracle or DB2, which all run, and make recommendations to
run, under non-standard ports, as a security measure.
SERVER = "MyServer\NamedInstanceN" is equivalent to SERVER =
"MyServer,PortN" where the former relies on dynamic discovery (UDP 1134)
while the latter is explicit.
I do not like embedding configuration parameters within code: SERVER =
"MyServerIPAddress,PortN" would work as well but defeats the purpose of
abstracting the name resolution.
You would be better off configuring your clients using SQL Server
client-side aliases (akin to OS host files) than to bury dynamic properties
within static code.
Sincerely,
Anthony Thomas

"ipconfig2" <ipconfig2@.discussions.microsoft.com> wrote in message
news:4F279F5A-E540-49D2-92C4-37E171D19719@.microsoft.com...
> Hi Anthony
> I cannot set the static port - it goes back to the dynamically set port
when
> I have changed it. The manual is BOL and Help file of SSCM. I have another
> cluster where this is no problem so I wondered if there is a bug somewhere
in[vbcol=seagreen]
> the software. I am using version 2153.
> I want all my instances to use port 1433 since there are applications that
> have hardcoded port 1433 as database port.
> Regards
> Rune
> "Anthony Thomas" wrote:
use[vbcol=seagreen]
when[vbcol=seagreen]
So,[vbcol=seagreen]
it's[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
server[vbcol=seagreen]
to[vbcol=seagreen]
service it[vbcol=seagreen]

Change SQL Instance name to default(local) instrance

I found some information, but, it didn't work.
Now, i open the enterprise manager, and My SQL Server name is
DENNIS\DENNISDB(windows NT), but i want to change this name to default
name(local), when i installed sql, i changed the instance name, i want
default name.
I used this method.
EXEC sp_dropserver 'DENNISDB'
go
EXEC sp_addserver 'DENNIS', 'local'
go
and i restart SQL Server Service and,
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
result : DENNIS\DENNIDB
select @.@.SERVERNAME
result : DENNIS
But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I want
"(local)" instance
Help me plase..Hi
As far as I know you cannot change/switch a default instance with a named
one. Re-install SQL Server only.
"DennisLee" <ddongdog@.haja.or.kr.korea> wrote in message
news:ONofqOsMGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I found some information, but, it didn't work.
> Now, i open the enterprise manager, and My SQL Server name is
> DENNIS\DENNISDB(windows NT), but i want to change this name to default
> name(local), when i installed sql, i changed the instance name, i want
> default name.
> I used this method.
>
> EXEC sp_dropserver 'DENNISDB'
> go
> EXEC sp_addserver 'DENNIS', 'local'
> go
> and i restart SQL Server Service and,
> SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
> result : DENNIS\DENNIDB
> select @.@.SERVERNAME
> result : DENNIS
> But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I
> want
> "(local)" instance
> Help me plase..
>
>

Change Recovery model through T-SQL

I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.
You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>
|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>

Change Recovery model through T-SQL

I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRI
NT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:

> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>

Change Recovery model through T-SQL

I have an instance of SQL Server 2000, with 400 databases.
Now I want to change the recovery model of each of them from simple to
full. How can I do it in T-SQL?
If not T-SQL, what is the easiest way to change all of them?
Thank you.You can generate the commands required this way:
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT
''GO'''
Just use results in text in Query Analyzer, and copy the output to the top
pane.
Of course you will have to prune out the system DBs.
You can also generate the commands by using a select from
master.dbo.sysdatabase or INFORMATION_SCHEMA.SCHEMATA.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Hi,
I think you can use the alter database command to set the recovery option.
Example,
alter database <dbname>
set recovery simple
Hope this helps.
Dee
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||sp_MSforeachdb 'alter database ? set recovery full'
"Jluo" wrote:
> I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Of course you should immediately do full backups, and begin log
backups...( the log will still truncate until you have done the first full
backup.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>|||Another variation :-)
declare @.sql nvarchar(4000)
set @.sql ='SELECT ''ALTER DATABASE ''+QUOTENAME([name])+'''+
' SET RECOVERY SIMPLE'' FROM sysdatabases where dbid>4'
exec master.dbo.xp_execresultset @.sql, 'master'
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jluo" <jluost1@.yahoo.com> wrote in message
news:423B47D4.4080509@.yahoo.com...
>I have an instance of SQL Server 2000, with 400 databases.
> Now I want to change the recovery model of each of them from simple to
> full. How can I do it in T-SQL?
> If not T-SQL, what is the easiest way to change all of them?
> Thank you.
>

Change of Computer Name

Hi,
I'll like to change the computer name but my sql server 7 instance is tied
to the computer name. May I know how to resolve this without re-installation ?
TIA !
Desmond
Desmond
See sp_addserver and sp_dropserver in the BOL as well as visit at this web
site
http://vyaskn.tripod.com/moving_sql_server.htm
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without
re-installation ?
> TIA !
> Desmond
|||http://www.karaszi.com/SQLServer/inf...erver_name.asp
Note that you have to adapt the SQL code that handles your jobs (the code is to SQL Server 2000)...
Or use the KB article strategy instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installation ?
> TIA !
> Desmond
|||Hi
This is SQL 2000 but I think it may have been the same in SQL 7.
http://msdn.microsoft.com/library/de...stall_5r8f.asp
John
"Desmond" wrote:

> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installation ?
> TIA !
> Desmond

Change of Computer Name

Hi,
I'll like to change the computer name but my sql server 7 instance is tied
to the computer name. May I know how to resolve this without re-installation
?
TIA !
DesmondDesmond
See sp_addserver and sp_dropserver in the BOL as well as visit at this web
site
http://vyaskn.tripod.com/moving_sql_server.htm
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without
re-installation ?
> TIA !
> Desmond|||http://www.karaszi.com/SQLServer/in...server_name.asp
Note that you have to adapt the SQL code that handles your jobs (the code is
to SQL Server 2000)...
Or use the KB article strategy instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installati
on ?
> TIA !
> Desmond|||Hi
This is SQL 2000 but I think it may have been the same in SQL 7.
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
John
"Desmond" wrote:

> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installati
on ?
> TIA !
> Desmond

Change of Computer Name

Hi,
I'll like to change the computer name but my sql server 7 instance is tied
to the computer name. May I know how to resolve this without re-installation ?
TIA !
DesmondDesmond
See sp_addserver and sp_dropserver in the BOL as well as visit at this web
site
http://vyaskn.tripod.com/moving_sql_server.htm
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without
re-installation ?
> TIA !
> Desmond|||http://www.karaszi.com/SQLServer/info_change_server_name.asp
Note that you have to adapt the SQL code that handles your jobs (the code is to SQL Server 2000)...
Or use the KB article strategy instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:314C7E9C-FFC8-4651-B085-B1BD74A84B3B@.microsoft.com...
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installation ?
> TIA !
> Desmond|||Hi
This is SQL 2000 but I think it may have been the same in SQL 7.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"Desmond" wrote:
> Hi,
> I'll like to change the computer name but my sql server 7 instance is tied
> to the computer name. May I know how to resolve this without re-installation ?
> TIA !
> Desmond

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RT
Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT
|||Sorry to use an existing post but when I try to start a new thread, nothing
happends.
I just reinstalled SQL Server 2000. During the install. I used the default
Server name of GATEWAY_COMPUTE. Now when I try to start SQL Server, the
Server name comes up as GATEWAY_COMPUTE, services is SQL Server. When I
click start I receive error message ' The service did not start due to a
login failure'.
Can someone tell me how to correct this?

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RTYou cannot change instance name, nor from a default to a named instance (or
other way around). One
option is to install 2005 again, as a named instance (after removing 2000).
Then copy the databases
(backup/restore, detach/attach, for instance). But there are extra steps han
dling the data inside
the system databases, though...
Or, handle this on the client machines:
Either change the connection string.
Or, create an alias for the named instance, with only the machine name as th
e alias name.
(Basically, rename it seen from the client's perspective.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005 th
e
> default instance. Can anyone describe the steps need to do this. We want t
o
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/pr...5/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RTYou cannot change instance name, nor from a default to a named instance (or other way around). One
option is to install 2005 again, as a named instance (after removing 2000). Then copy the databases
(backup/restore, detach/attach, for instance). But there are extra steps handling the data inside
the system databases, though...
Or, handle this on the client machines:
Either change the connection string.
Or, create an alias for the named instance, with only the machine name as the alias name.
(Basically, rename it seen from the client's perspective.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
> default instance. Can anyone describe the steps need to do this. We want to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Sorry to use an existing post but when I try to start a new thread, nothing
happends.
I just reinstalled SQL Server 2000. During the install. I used the default
Server name of GATEWAY_COMPUTE. Now when I try to start SQL Server, the
Server name comes up as GATEWAY_COMPUTE, services is SQL Server. When I
click start I receive error message ' The service did not start due to a
login failure'.
Can someone tell me how to correct this?