rename table & column name in PL-SQL & T-SQL

In T-SQL :

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME '[OldTableName]' , '[NewTableName]'

In PL-SQL :

The script for renaming any object (table, sp etc) :

Alter table Stu_Table rename to Stu_Table_10)

Thursday, November 19, 2009

SQL Server Registration Tool

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP
• Microsoft .NET Framework
• Microsoft Internet Information Services (IIS)
• Microsoft SQL Server

Modify the Web.config File of Your Application

To implement ASP.NET SQL Server mode session state management, you must modify the element of your application's Web.config file as follows:

1. Set the mode attribute of the element to SQLServer to indicate that session state is stored in SQL Server.
2. Set the sqlConnectionString attribute to specify the connection string for SQL Server. For example:

sqlConnectionString="data source=MySQLServer;user id=;password="

Note  The user, , must have permissions to perform this operation on the database.

The modified  element should appear as follows:


            mode="SQLServer"
            sqlConnectionString="data source=127.0.0.1;user id=;password="
            cookieless="false" 
            timeout="20" 
    />

Note Ensure that you use the correct case when you specify the  element and the associated attribute values. This code is case sensitive.

Troubleshooting

• If entries in the ASPStateTempSessions table are not removed after the related sessions expire, make sure that the SQL Server agent is running. You can implement this functionality through stored procedures that are scheduled through jobs in SQL Server. The SQL Server agent manages these jobs.
• When you use the default InstallSqlState.sql and UninstallSqlState.sql script files to configure ASP.NET SQL Server mode session state management, note that these files add the ASPStateTempSessions and the ASPStateTempApplications tables to the tempdb database in SQL Server by default. Furthermore, if you restart SQL Server, you lose the session state data that was stored in the ASPStateTempSessions and the ASPStateTempApplications tables.For additional information about how to run alternative scripts to configure persistent SQL Server session state management so that the session data is not lost when you restart the serve

Monday, April 13, 2009

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)

The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database.

You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server installation, and installing or removing the database elements for the membership, role management, profile, Web Parts personalization, and health monitoring features. (Setting session state and SQL cache dependency are not covered by the wizard.) You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove, using the options listed in the table below

Aspnet_regsql.exe 

SQL Connection Options
Option Description

-? Prints Aspnet_regsql.exe Help text in the command window.
-W Runs the tool in wizard mode. This is the default if no command-line arguments are specified.

-C Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information.

-S Specifies the name of the computer running SQL Server where the database will be installed, or is already installed.

-U The SQL Server user ID to log in with. This option also requires the password (-P) option. This option is not necessary if you are authenticating using Windows credentials (-E).

-P The SQL Server password to log in with. This option also requires the user ID (-U) option. This option is not necessary if you are authenticating using Windows credentials (-E).

-E Authenticates using the Windows credentials of the currently logged-on user.

-sqlexportlonly Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed.

Application Services Options

Adds support for one or more ASP.NET application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:

-A all|m|r|p|c|w

all - All services, including common tables and stored procedures shared by the services

m - Membership

r - Role Manager

p - Profile

c - Web Parts Personalization

Removes support for one or more application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:

-R all|m|r|p|c|w

m - Membership
r - Role Manager
p - Profile
c - Web Parts Personalization
w - Web Events

-Q Runs the tool in quiet mode and does not confirm before removing an application service.
-d Specifies the name of the database to create or modify for use with application services. If the database is not specified, the default database name of "aspnetdb" is used.

Note

The database elements that are installed in the application services database will always be owned by the SQL Server database owner account (dbo). You do not need to be a system administrator for the computer running SQL Server to install the application services database. To install the application services database, a SQL Server login must be permitted to the db_ddladmin and dd_securityadmin roles for the SQL Server database.

SQL Cache Dependency Options (for Microsoft SQL Server 7.0, Microsoft SQL Server 2000 and SQL Server 2005)


Option Description

-d Specifies the name of the database to use with SQL cache dependency. The database can optionally be specified using the connection string option, -C.

-ed Enables a database for SQL cache dependency.
-dd Disables a database for SQL cache dependency.
-et Enables a table for SQL cache dependency. The -t option must also be included in the parameter string.
-dt Disables a table for SQL cache dependency. The -t option must also be included in the parameter string.
-t Specifies the table name to enable or disable for use with SQL cache dependency. This option must be used with the -et or -dt options.
-lt Lists all tables enabled for SQL cache dependency.

Session State Options

Option Description
-d Specifies the name of the database to store session state. This option must be used if -sstype is set to c

-ssadd Adds support for SQL Server mode session state.
-ssremove Removes support for SQL Server mode session state.

Specifies the type of session state to us1:

-sstype t|p|c

t - Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.

p - Persisted. Both session state data and stored procedures are stored in the SQL Server ASPState database.

c - Custom. Both session state data and stored procedures are stored in a custom database. The database name must be specified using the -d option.