Mssql Howto

MS SQL Server How-To’s

1 Prepared by : Ramesh Raj

Contents 1) How to display Year as YYYY instead of YY........................................................... 3 2) How to Build a Comma Delimited String from Table values........................... 3 3) How to write values to a Text file ........................................................................... 4 4) How to add a User Defined Error Message ............................................................... 4 5) How to get the Rowcount as well as Error at the same time ...................... 5 6) How to start SQLServer in Single User Mode ........................................................ 6 7) How to start SQL Server in Minimal Configuration Mode ............................... 7 8) How to get Output parameter value from Dynamic execution of a Query8 9) How to move Database/Transaction Log files ........................................................ 8 10) How to Rename a Database ............................................................................................... 9 11) How to move Master Database ...................................................................................... 10 12) How to Rename a Server .................................................................................................. 10 13) How to detect & rectify Orphaned Users in a Database ............................. 11 14) How to use a Stored procedure as a Derived Table ...................................... 11 15) How to Generate Serial Numbers in a Query ...................................................... 12 16) How to Rename a Table .................................................................................................... 12 17) How to Rename a Table Column .................................................................................... 13 18) How to Rename a Primary Key ...................................................................................... 13 19) How to Rename a Foreign Key ...................................................................................... 13 20) How to Rename a Default................................................................................................ 14

2 Prepared by : Ramesh Raj

1) How to display Year as YYYY instead of YY Enterprise Manager adopts the User's regional settings for the date format. By default, the regional setting for the short date format is m/d/yy. To display it in m/d/yyyy format, follow the following steps: On the taskbar, click the Start button, point to Settings and then select Control Panel. In the Control Panel dialog box double-click Regional Settings. In the Regional Settings Properties dialog box, select the Date tab. Next, change the value for the Short Date Style from M/d/yy to M/d/yyyy.

2) How to Build a Comma Delimited String from Table values Sometimes you might want to return your records in a single comma delimited string. For example, if you have records like :Mango Banana Peach Grapes Strawberry Instead of returning it as a recordset containing these 5 records, you might want to return them as 'Mango, Banana, Peach, Grapes, Strawberry'. Here's how you can accomplish this. -- Create a temporary table & insert dummy records Create Table #Fruits (Fruit Varchar(25)) Insert #Fruits (Fruit) values('Mango') Insert #Fruits (Fruit) values('Banana') Insert #Fruits (Fruit) values('Peach') Insert #Fruits (Fruit) values('Grapes') Insert #Fruits (Fruit) values('Strawberry') -- Build comma delimited string Declare @Fruits Varchar(200) Set @Fruits = '' 3 Prepared by : Ramesh Raj

Update #Fruits Set @Fruits = @Fruits + Fruit + ',' Set @Fruits = Substring(@Fruits,1,len(@Fruits)-1) -- to remove extra comma at the end print @Fruits

3) How to write values to a Text file To write a result of a SELECT query in a text file :master..xp_cmdshell 'osql -SMyServer -Umyuser -Pmypwd -Q"select * from products" -dNorthwind -w"1000" oc:\MyText.txt' To write a string (like comments, etc.) you can use this code :declare @cmd varchar(1000) select @cmd = 'echo These are the Products in the database>> "c:\MyText.txt"' exec master..xp_cmdshell @cmd

4) How to add a User Defined Error Message Use Master Go EXEC sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = N'Failed to insert Customer Transaction into %s table', @lang = 'us_english' Go You can raise this message from your code like this :INSERT Customers (CustomerName) VALUES ('Rick Nelson') IF @@Error <> 0 RAISERROR 4 Prepared by : Ramesh Raj

(50001,16,1, 'Customers')

5) How to get the Rowcount as well as Error at the same time Normally when we run a Query, we check if the query was executed successfully or not before commiting it. DECLARE @CustomerId SET @CustomerId = 1

Int

Begin Transaction Update Orders Set OrderDate = GetDate() Where CustomerId = @CustomerId IF @@Error <> 0 Begin Raiserror('Failed to update Orders',16,1) Rollback Transaction End Commit Transaction But what if you also want to get the number of records which were effected by this query? If you do a @@Rowcount after your Error checking statement then you are going to get 0 as the value of @@Recordcount would have been reset. And if you place @@Recordcount before the error-checking statement then your @@Error would get reset. So what's the solution to get both of them at the same time ? Save both the values in a local variable and then check that local variable. Here's how it can be done. DECLARE @Rcount DECLARE @ErrNum DECLARE @CustomerId

Int Int Int

SET @Rcount = 0 SET @ErrNum = 0 5 Prepared by : Ramesh Raj

SET @CustomerId = 1

Begin Transaction Update Orders Set OrderDate = GetDate() Where CustomerId = @CustomerId SELECT @Rcount = @@RowCount, @ErrNum = @@Error -- check if there was an error in Updating the records IF @ErrNum <> 0 Begin Raiserror('Failed to update Orders',16,1) Rollback Transaction End -- check if some records were updated or not IF @Rcount = 0 Begin Raiserror('No records for CustomerId %d',16,1, @CustomerId) Rollback Transaction End Commit Transaction

6) How to start SQLServer in Single User Mode At times you might want to start SQL server in a single user mode to perform some maintenance work on the server or to change server configurations or to recover a damaged database. You can accomplish this in any of the three ways given below :-

a) From Command Prompt :» sqlservr -m 6 Prepared by : Ramesh Raj

b) From Startup Options :» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager. » Under the 'General' tab, click on 'Startup Parameters'. » Enter a value of -m in the Parameter. c) From Registry :» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Pa rameters. » Add new string value. » Specify the 'Name' as SQLArg(n) & 'Data' as -m. Where n is the argument number in the list of arguments. Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior.

7) How to start SQL Server in Minimal Configuration Mode Sometimes a bad configuration value can prevent SQL Server from starting. Then it won't even let you connect to SQL Server using Enterprise Manager and correct the configuration values. The only option is to start SQL Server in a minimum configuration mode and then correct the configuration values and restart the SQL Server in normal mode. Here's how you can start the SQL Server in a minimal configuration mode :-

a) From Command Prompt :» sqlservr -f b) From Startup Options :» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager. » Under the 'General' tab, click on 'Startup Parameters'. » Enter a value of -f in the Parameter. c) From Registry :» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Pa rameters. » Add new string value. » Specify the 'Name' as SQLArg(n) & 'Data' as -f. Where n is the argument number in the list of arguments. Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior. 7 Prepared by : Ramesh Raj

8) How to get Output parameter value from Dynamic execution of a Query Sometimes you have to execute a Query dynamically using Exec(@Sql). This method works fine as long as you don't want any output values from the @Sql query. But there's another method (sp_Executesql) that allows you to execute queries dynamically as well as get their output values.

The syntax :EXECUTE sp_executesql @SQLString, @ParmDefinition, @parm=@parmIN, @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT Example :DECLARE @IntVariable INT DECLARE @SQLString nVARCHAR(500) DECLARE @ParmDefinition nVARCHAR(500) DECLARE @Lastlname nVARCHAR(30) SET @SQLString = 'SELECT @LastlnameOUT = max(lname) FROM pubs.dbo.employee WHERE job_lvl = @level' SET @ParmDefinition = '@level tinyint, @LastlnameOUT varchar(30) OUTPUT' SET @IntVariable = 35 EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @LastlnameOUT=@Lastlname OUTPUT SELECT @Lastlname Note: sp_Executesql is a Extended Stored Procedure.

9) How to move Database/Transaction Log files You can move Database & Transaction log files to a different location in 2 ways.

a) sp_detach_db & then sp_attach_db » Make sure that no user is using the database. 8 Prepared by : Ramesh Raj

» Exec sp_detach_db northwind » Move the Data & log files to a different location » EXEC sp_attach_db 'Northwind', 'c:\mssql7\northwnd.mdf', 'c:\mssql7\northwnd.ldf' b) BACKUP and RESTORE using WITH MOVE » Backup :Backup Database Northwind To Disk = 'C:\mssql7\backup\nwind.bak' Go » Restore :USE Master Go RESTORE Database northwind from DISK = 'c:\mssql7\backup\nwind.bak' WITH MOVE 'Northwind' TO 'c:\mssql7\Northwnd.mdf', MOVE 'Northwind_log' TO 'c:\mssql7\Northwnd.ldf' Go c) Can be used only for moving Tempdb files. » Use ALTER Database statement to specify a different Path for the filename. ALTER DATABASE Tempdb MODIFY FILE (NAME = Tempdev, FILENAME = 'c:\mssql7\tempdb.mdf') ALTER DATABASE Tempdb MODIFY FILE (NAME = Templog, FILENAME = 'c:\mssql7\templog.ldf') »

Restart SQL Server and delete the old files.

10) How to Rename a Database a) Using sp_rename » Make sure that no user is using the database. » Make the database in the single user mode. You can do this by using sp_dboption. sp_dboption 'Pubs', 'single user', true » sp_rename Pubs, Library, Database Note : For renaming the database, you can also use sp_renamedb. sp_renamedb Pubs, Library » Bring back the database in multiuser mode sp_dboption 'Library', 'single user', false b) Using Detach & Attach sp_detach_db @dbname = N'Pubs' 9 Prepared by : Ramesh Raj

sp_attach_db @dbname = N'Library', @filename1 = N'd:\programfiles\MSSQL7\data\Pubs_Data.MDF', @filename2 = N'd:\program files\MSSQL7\data\Pubs_log.ldf'

11) How to move Master Database Moving a Master database is different from moving a User database.

» Right-click the SQL Server in Enterprise Manager and click Properties on the shortcut menu. » Click the Startup Parameters button and you see the following entries: -dD:\MSSQL7\data\master.mdf -eD:\MSSQL7\log\ErrorLog -lD:\MSSQL7\data\mastlog.ldf -d is the fully qualified path for the master database data file. -e is the fully qualified path for the error log file. -l is the fully qualified path for the master database log file. » Remove these values and specify the new values. For example : -dE:\SQLDATA\master.mdf -lE:\SQLDATA\mastlog.ldf Note : You can specify a new path for the error log file as well. » Stop SQL Server. » Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata). » Restart SQL Server.

12) How to Rename a Server »

Rename the physical server.

»

Reboot the server.

10 Prepared by : Ramesh Raj

»

Run the SQL Server setup program. This will change the Registry entries. The setup will prompt you to "Upgrade" SQL Server. Choose "yes".

»

Reboot the server.

»

Connect to the new server name in Query analyzer.

»

Run this code: sp_dropserver

»

Run this code: sp_addserver , local

»

Add the entry for the new Server name in Enterprise manager and delete the entry for the Old server name.

13) How to detect & rectify Orphaned Users in a Database Logins are associated to users by the security identifiers (SIDs), which are stored in the Master database. When you restore a database to a different server, the SID may be different resulting in a mismatch between the Login-User association. These users, without a valid login association, are called 'Orphaned Users'.

Here's how you can Detect Orphaned Users :Use Northwind Go sp_change_users_login 'Report' To associate a Orphaned User with a Login :Use Northwind Go sp_change_users_login 'update_one', 'username', 'loginname'

14) How to use a Stored procedure as a Derived Table Here's a code sample which uses the recordset returned by the execution of a stored procedure as a derived table. 11 Prepared by : Ramesh Raj

SELECT a.pub_id, b.pub_name, a.title_id, a.price, a.pubdate FROM OPENROWSET('SQLOLEDB','servername'; 'username'; 'password', 'exec Pubs.dbo.reptq1') AS a inner join publishers b on a.pub_id = b.pub_id

15) How to Generate Serial Numbers in a Query create table #Fruits (Fruit Varchar(25)) INSERT #Fruits (Fruit) VALUES ('Mango') INSERT #Fruits (Fruit) VALUES ('Apple') INSERT #Fruits (Fruit) VALUES ('Banana') INSERT #Fruits (Fruit) VALUES ('Grapes') select Sno=count(*), a1.Fruit from #Fruits a1 INNER JOIN #Fruits a2 ON a1.Fruit >= a2.Fruit group by a1.Fruit order by [Sno]

16) How to Rename a Table Renaming table 'Sales' to 'Orders'. IF Exists (Select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Exec sp_rename 'Sales', 'Orders' IF @@Error <> 0 Raiserror('Failed to rename Table Sales to Orders',16,1) ELSE Print 'Table Sales Renamed to Orders' END 12 Prepared by : Ramesh Raj

ELSE Print 'Table Sales does not exist' GO

17) How to Rename a Table Column Renaming column 'Qty' in 'Orders' table to 'Quantity'. IF (SELECT COLUMNPROPERTY( OBJECT_ID('Orders'),'Quantity','AllowsNull')) IS NULL BEGIN Exec sp_rename 'Orders.[Qty]', 'Quantity', 'COLUMN' IF @@Error <> 0 Raiserror('Failed to rename column Qty to Quantity',16,1) ELSE Print 'Column Qty Renamed to Quantity' END ELSE Print 'Column Quantity already exists' GO

18) How to Rename a Primary Key Renaming Primary Key 'UPKCL_sales' in 'Orders' Table to 'PK_Orders'. IF Exists (select * from sysobjects where id = object_id(N'[UPKCL_sales]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1) BEGIN Exec sp_rename 'UPKCL_sales', 'PK_Orders', 'OBJECT' IF @@Error <> 0 Raiserror('Failed to rename PK UPKCL_sales to PK_Orders',16,1) ELSE Print 'PK UPKCL_sales Renamed to PK_Orders' END ELSE Print 'PK UPKCL_sales does not exist' GO

19) How to Rename a Foreign Key Renaming Foreign Key 'FK__Titleauth__au_id__164452b1' in 'Titleauthor' Table to 'FK_Titleauthor_Author_auid'. 13 Prepared by : Ramesh Raj

IF Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Titleauth__au_id__164452b1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) BEGIN Exec sp_rename 'FK__Titleauth__au_id__164452b1', 'FK_Titleauthor_Author_auid', 'OBJECT' IF @@Error <> 0 Raiserror('Failed to rename FK FK__Titleauth__au_id__164452b1 to FK_Titleauthor_Author_auid',16,1) ELSE Print 'FK FK__Titleauth__au_id__164452b1 Renamed to FK_Titleauthor_Author_auid' END ELSE Print 'FK FK__Titleauth__au_id__164452b1 does not exist' GO

20) How to Rename a Default Renaming Default 'DF_Orders' to 'DF_Orders_Ord_Date'. (DF_Orders does not actually exist in the database.) IF Exists (select * from sysobjects where id = object_id(N'[dbo].[DF_Orders]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1) BEGIN Exec sp_rename 'DF_Orders', 'DF_Orders_Ord_Date', 'OBJECT' IF @@Error <> 0 Raiserror('Failed to rename Default DF_Orders to DF_Orders_Ord_Date',16,1) ELSE Print 'Default DF_Orders Renamed to DF_Orders_Ord_Date' END ELSE Print 'Default DF_Orders does not exist' GO Another advantage with this type of approach is that the script first checks to see if the Object exists or not before doing any operations on it. So that way, if necessary, you could run the script multiple times on the same server.

14 Prepared by : Ramesh Raj