My Rational Rights

In one of my previous posts I wrote about “ How to Create a Comma Delimited List ”. Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values. There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab. For example here is our Sample Table - Id AllNames 1 A,B,C 2 A,B 3 X,Y,Z And here is the expected output - Id Names 1 A 1 B 1 C 2 A 2 B 3 X 3 Y 3 Z Create Sample Data : -- Create Table for Sample Data CREATE TABLE Test ( ID INT , AllNames VARCHAR (100...

1. Create a text file. 2. Change a file extension as UDL 3. Now Double Click the file and Test connection string. 4. close 5. Open a file with notepad 6. We can have connection string

BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_LINE() AS ErrorLine; END CATCH;

DECLARE @Mth smallint SET @Mth = 11 SELECT DateName (mm, DATEADD (mm,@Mth,-1)) as [MonthName] SELECT Number + 1 as [MonthNumber], DateName (mm, DATEADD (mm,Number,0)) as [MonthName] FROM master..spt_values WHERE Type = 'P' and Number

SELECT GETDATE() AS [CURRENT_DATE], CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [DATE_PART], CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [TIME_PART]

Today I got an email asking how to delete duplicate records in a table? This is very common and very interesting question. The perfect answer to this question depends on the following points: 1) Volume of the data 2) Downtime of the system 3) Dependency on the table 4) Restrictions on writing the code. I am sure every database developer deals with “Delete Duplicate Records” issue at least once in a lifetime. There are so many ways we can eliminate the duplicate data from a table. In this article I would like to explain various ways to delete the duplicate records. --Create Demo Table CREATE TABLE #Employee ( EMP_ID INT, FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30) ) --Insert Dummy Data INSERT INTO #Employee Values(1,'Stefan','Vachev'); INSERT INTO #Employee Values(2,'Michael','Allen'); INSERT INTO #Employee Values(3,'Glenn','Jhonson'); INSERT INTO #Employee Values(4,'Ray','Muran'); INSERT INTO #Employee Values(1,'...

To configure the Database mail by creating email profile. 1. Login as admin in sql 2. Under Management , right click the Database mail and Click Configure the database mail , 3. select setup the database mail by performing following actions options 4. Create a profile and give the authentication and user name and password 5. Otherwise select the username and password 6. Complete the all the steps To make a public profile 1. Login as admin in sql 2. Under Management , right click the Database mail and Click Configure the database mail , 1. Go to Database mail 2. Right Click Click on Configure Database mail. 3. wizard will click next 4.select Manage Profile security 5. Click Next 6. Beside Profile name by default there will be no , Please make it yes 7. Finish

SET IDENTITY_INSERT YourTableName ON INSERT INTO YourTableName ( CustId , FirstName , LastName ) VALUES ( 18 , 'Paul' , 'Adams' ) GO SET IDENTITY_INSERT YourTableName OFF But we must use insert into table_name(columns) values(values) Otherwise it willnot work

If you happen to forget your SQL Server password for 'sa' account, then here's a simple query to help you reset it: GO ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] GO USE [master] GO ALTER LOGIN [sa] WITH PASSWORD=N'MyNewPassword' MUST_CHANGE GO In Case you remember your Old Password and want to change the 'sa' password, use this query: ALTER LOGIN [sa] WITH PASSWORD = N'MyNewPassword' OLD_PASSWORD = 'MyOldPassword'; GO

select @@trancount SELECT dateadd(qq,4,getdate()) select DATEDIFF(DD,GETDATE(),dateadd(qq,4,getdate())) How to query a string contains %? Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'

Generate Insert Statements For a SQL Server Table For demonstration purposes, I am using the Culture table of the AdventureWorks database. USE AdventureWorks GO DECLARE @Columns VARCHAR ( max ); SET @Columns = '[CultureID], [Name], [ModifiedDate]' DECLARE @Table VARCHAR ( max ); SET @Table = 'Production.Culture' DECLARE @SQL VARCHAR ( max ) SET @SQL = 'DECLARE @S VARCHAR(MAX) SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Columns + ')'') + CHAR(13) + CHAR(10) + ''SELECT '' + ' + REPLACE ( REPLACE ( REPLACE (@Columns, ',' , ' + '', '' + ' ), '[' , ''''''''' + CAST(' ), ']' , ' AS VARCHAR(max)) + ''''''''' ) + ' FROM ' + @Table + ' PRINT @S' EXEC (@SQL) OUTPUT

ALTER PROCEDURE [dbo].[snproc_find_text_in_sp] @StringToSearch varchar(MAX) AS BEGIN SET @StringToSearch = '%' +@StringToSearch + '%' SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch ORDER BY SO.Name END

--Please refer to following update query to recently executed T-SQL query on database. --USE Master SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC

view plain copy to clipboard print ? State (Tablename) ----- City (Columnname) ----- Chennai Madurai Coimbatore declare @retstr varchar (8000) select Top 5 @retstr = COALESCE (@retstr + ';' , '' ) + City from State print @retstr

DECLARE @SEARCHSTRING VARCHAR (255) SELECT @SEARCHSTRING = 'MyTable' SELECT DISTINCT sysobjects .name FROM sysobjects , syscomments WHERE sysobjects .id = syscomments .id -- look for stored procs only AND sysobjects .type = 'P' AND sysobjects .category = 0 -- what you are looking for, what you're looking in AND CHARINDEX (@SEARCHSTRING, syscomments . text )>0

select mi . SKU , mi. CasesPerPallet , cp . CasesPerPallet as SandrasCasesPerPallet from ii_masteritem mi join OPENDATASOURCE ( 'Microsoft.Jet.OLEDB.4.0' , 'Data Source=d:\data\cspallet.xls;Extended Properties=Excel 8.0' )... Sheet1$ cp on cp . SKU = mi . SKU where mi.CasePerPallet cp . CasesPerPallet

select datename(month,GETDATE()) select datename(dw,GETDATE()) select datepart(dw,GETDATE())

var myResults=(from emp in db.employees order by emp.emp_name where emp.emp_name.startwith("a") select emp).Take(10) http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Create procedure dbo.SearchEmployee ( @Employeename varchar(20), @EmployeeDesig varchar(20), @EmployeeLocation varchar(20), @EmployeeInterest varchar(20) ) as begin set nocount on set @EmployeeName = '%' + @EmployeeName + '%' set @EmployeeDesig = '%' + @EmployeeDesig + '%' set @EmployeeLocation = '%' + @EmployeeLocation + '%' set @EmployeeInterest = '%' + @EmployeeInterest + '%' SELECT EmployeeName from EmployeeMaster (nolock) WHERE (nullif(@EmployeeName, '') is null or EmployeeName like @EmployeeName) and (nullif(@EmployeeDesig, '') is null or EmployeeDesignation like @EmployeeDesig) and (nullif(@EmployeeLocation, '') is null or EmployeeLocation like @EmployeeLocation) and (nullif(@EmployeeInterest, '') is null or EmployeeInterest like @EmployeeInterest) set nocount off end go seaching on name and interests exec SearchEmployee 'shriram', null, null, 'SQL' searching on intere...

Create Proc Sp_helpDate @mark1 int=10,@mark2 int=10,@mark3 int=10,@mark4 int=10 AS BEGIN SELECT @mark1+@mark2+@mark3+@mark4 END EXEC Sp_helpDate @mark3=70