My Rational Rights

RegisterStartupScript and RegisterClientScriptBlock http://chiragrdarji.blogspot.com/2008/07/registerstartupscript-and.html

alter PROC snproc_am_ytd_scoreboard_cp AS BEGIN DECLARE @no_of_days int SET @no_of_days = 24 DECLARE @yi int, @yj int,@mi int,@mj int,@month int,@noofyrs int DECLARE @yrstdate datetime, @yrenddate datetime,@monthstdate datetime, @monthenddate datetime,@CurrentDate datetime SET @month=datepart(mm,getdate()) ;set @noofyrs=-4; set @CurrentDate=getdate();--dateadd(mm,-4,getdate()); SET @yi=0; WHILE(@yi>@noofyrs) -- TO GET LAST FOUR YEARS BEGIN SELECT @yrstdate= '01/'+convert(varchar(10),'01')+'/'+convert(varchar(10),datepart(yyyy,dateadd(yyyy,@yi,@CurrentDate))); IF(@yi=0) SET @yrenddate=dateadd(dd,-1,dateadd(mm,1,convert(varchar(10),datepart(mm,@CurrentDate))+'/01/'+convert(varchar(10),datepart(yyyy,dateadd(yyyy,@yi,@CurrentDate))))); ELSE SET @yrenddate=dateadd(dd,-1,dateadd(yyyy,1,@yrstdate)); SET @yrstdate = dateadd(s,0,dateadd(mi,0,dateadd(hh,0,convert(varchar(10),@yr...

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

http://happi2share.blogspot.com/search/label/Database

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]

Use the below query to get the list of tables that depends on the input table. Select S.[name] as 'Dependent_Tables' From sys.objects S inner join sys.sysreferences R on S.object_id = R.rkeyid Where S.[type] = 'U' AND R.fkeyid = OBJECT_ID('Person.StateProvince') here, replace Person.StateProvince with your table name. http://www.dotnetvj.com

SQL Server 2005 stores your database information in two types of files: one or more database files and one or more transaction log files. As a database administrator (DBA), it is your duty to design and create databases to store user data and other objects. As part of your role as a database creator, you must decide how large to make these database files and what type of growth characteristics they should have, as well as their physical placement on your system. In this article, you will get to know where to create a database and then put the data and log files. First, you must decide where to put the data and log files. Guidelines to Use Data and log files should be on separate physical drives so that, in case of a disaster, you have a better chance of recovering all data. Transaction logs are best placed on a RAID-1 array because this has the fastest sequential write speed. Data files are best placed on a RAID-5 array because they have faster read speed than other RAID-arrays. If you...

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

Query Querying a full-text index is extremely fast and flexible. Because of the specialized index structure described above, it is very quick and easy to locate matches for particular search criteria. SELECT ProductModelId, ProductName FROM ProductModel WHERE CONTAINS(CatalogDescription, ' " aluminum alloy " ') USE Northwind; SELECT FT_TBL.CategoryName ,FT_TBL.Description ,KEY_TBL.RANK FROM dbo.Categories AS FT_TBL INNER JOIN FREETEXTTABLE(dbo.Categories, Description, 'sweetest candy bread and dry meat') AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY]; score value, because they are predicates in the Transact-SQL language. Rank values for full-text queries may be returned from CONTAINSTABLE and FREETEXTTABLE full-text query clauses. CONTAINSTABLE and FREETEXTTABLE behave like table-valued functions that return two columns: a key column (which can be used to join to the full-text indexed base table), and a rank column (which can be used ...

http://www.dbuggr.com/smallwei/top-10-absolutely-free-tools-microsoft-windows-software/

http://webforth.com/fuqee/ Just paste the question URL of experts-exchange.com in the above link and proceed for instant nirvana.

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

BCP command to export data to excel with column header Hi Friends, Here is a perfect running BCP command which exports table data into excel sheet with column headers. I spent almost 5 hrs to got it working, exporting data without column headers is a very easy job. but the main task was getting column headers also in the first row of excel sheet.. so here is the script.. enjoy NOTE: don't forget to replace RED elements with your local environment elements use database go if object_id('spExportData_n') is not null drop proc spExportData_n go create proc spExportData_n ( @dbName varchar(100) = ' database name ', @sql varchar(8000) = 'select col1,col2,col3....col4 from table ', @fullFileName varchar(100) = ' output file path ' ) as if @sql = '' or @fullFileName = '' begin select 0 as ReturnValue -- failure return end -- if DB isn't passed in set it to master select @dbName = 'use ' + @dbName + ';' if object_i...

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below: 1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd 2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package. 3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'. Here U go... Your job is successful. Any questions please let me know.

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

Sometimes the solution to a weird ‘looking’ problem lies in simple SQL Server functions! I was recently analyzing data of a SQL Server table with a varchar column that contained both numbers and alphabets. The client however now wanted to filter out the rows that contained only numbers in them. Here’s how the requirement was solved DECLARE @TT table ( ProductID int , CodeIdentification varchar ) -- Create Sample Data INSERT INTO @TT VALUES ( 101, 'A2' ) INSERT INTO @TT VALUES ( 203, '2' ); INSERT INTO @TT VALUES ( 305, '2' ); INSERT INTO @TT VALUES ( 403, '3' ); INSERT INTO @TT VALUES ( 553, 'B3' ); INSERT INTO @TT VALUES ( 634, '3' ); INSERT INTO @TT VALUES ( 744, '3' ); INSERT INTO @TT VALUES ( 838, '4' ); SELECT * FROM @TT WHERE IsNumeric (CodeIdentification) = 1 The IsNumeric function determines if the expression passed to it is valid, by returning 1; else it returns 0

You can find really helpful Microsoft .net (ASP.Net, VB.Net, c#.net and AJAX) books pdf at the following link... http://books-pdf.blogspot.com/search/label/ASP.Net http://books-pdf.blogspot.com http://microsoft-java-ebooks.blogspot.com http://freebooksandmagazines.blogspot.com http://www.ebooksboard.com/ http://muralikrishna542.blogspot.com

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

Good collection of .Net and Programming Resources Below is a main section, covering most of the programming languages http://www.oneqresources.com/tutorial.php Few other resources in sub-sections are: http://www.oneqresources.com/csharptutorials.php http://www.oneqresources.com/cpptutor.php http://www.oneqresources.com/dot-net-downloads.php

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

Method 1: DECLARE @rowsPerPage int , @pageNum int , @startRow int , @endRow int SET @rowsPerPage = 10 SET @pageNum = 3 SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1 SET @endRow = @startRow + @rowsPerPage -1 SELECT * FROM ( SELECT row_number() OVER ( ORDER BY id) as resultNum, id FROM myTable ) as numberResults WHERE resultNum BETWEEN @startRow AND @endRow Method2: SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging As ( Select Top (@rowsPerPage * @pageNum) ROW_NUMBER() OVER ( ORDER BY id) as resultNum, id FROM myTable ) select * from SQLPaging where resultNum > ((@pageNum - 1) * @rowsPerPage)

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

Recommended blog for C# 3.0 and functional programming I found the following blog post on programming.reddit.com: http://www.atrevido.net/blog/2007/09/05/C+30+And+LINQ+Misunderstandings.aspx I also recommend these blogs on functional programming in C# 3.0 by the same author: http://www.atrevido.net/blog/2007/08/12/Practical+Functional+C+Part+I.aspx http://www.atrevido.net/blog/2007/08/13/Practical+Functional+C+Part+II.aspx http://www.atrevido.net/blog/2007/08/16/Practical+Functional+C+Part+III+Loops+Are+Evil.aspx http://www.atrevido.net/blog/2007/08/29/Practical+Functional+C+Part+IV+Think+In+ResultSets.aspx This is really good stuff that any C# programmer can benefit from

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

Reading & Writing XML Data This post covers how to read and write XML data using XmlReader and XmlWriter classes provided by .NET framework. To get the Overview of XML support in .NET Framework pls read following post . Classes XmlReader & XmlWriter provide support for forward-only, read-only & write-only and non-cached way of reading & writing XML data. For reading the data, XmlReader provides the support for traversing through node, data, attributes, etc. XmlReaderSettings attached to XmlReader instance controls the behaviour of XmlReader like setting the validation type, setting event handler for validation failure etc. XmlSchemaSet can be used to specify the schema file to be used in case validation is of type Schema. For writing XML, XmlWriter provide the support for writing xml data to file in forward-only manner. XmlWriterSettings controls the behaviour of XmlWriter class like Indentation and Indentation characters to be used. Below sample demonst...

http://www.lanka.info/dictionary/EnglishToSinhala.jsp

http://free-ebooks-for-u.blogspot.com/ http://www.netbks.com/ http://knowfree.net http://booktraining.net http://vndownload.org http://www.free-ebook-download.net/ http://avaxhome.ws/

select top 3 * from tb_mytable order by newid() select top 3 * from tb_mytable order by Rand()

using System; using System.Collections.Generic; class Test { static void Main( string [] args ) { Test t = new Test(); int [] integerArray = {1,2,3,4,5,6}; char [] characterArray = { 'J', 'O', 'Y', 'D', 'I','P' }; double [] doubleArray = {0.1,0.2,0.3,0.4,0.5,0.6}; Console.WriteLine( "Displaying the contents of the integer array:--" ); t.Display(integerArray); Console.WriteLine( "Displaying the contents of the character array:--" ); t.Display(characterArray); Console.WriteLine( "Displaying the contents of the double array:--" ); t.Display(doubleArray); } public void Display ( GenericArray[] array ) { for ( int i = 0; i Console.WriteLine(array[i]); } }

CREATE FUNCTION dbo . ComputeNum ( @i int ) RETURNS int WITH SCHEMABINDING BEGIN RETURN @i * 2 + 50 END In SQL 2005 it improves the performance.

if(0='') print 'yes' else print 'no' Result is yes The result would be 'Yes'. "When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence" ( http://msdn.microsoft.com/en-us/library/ms190309.aspx books on line). This means that the text is implicitly converted to integer with a value of 0.

1. Disable the Debug Mode or Set Debug ="false" How it affect performance: By default this attribute is "true" when you create new application and is useful when you are developing the application. Debug = true means that pdb information to be inserted into file and this results a larger file size and it's performance issue. Before deployment you should set the following tag 2. Set trace enabled="false" How it affects performance: With help of tracing, we can track the application's and the sequences. Enabling tracing adds performance overhead and might expose private information, so it should be enabled only while an application is being actively analyzed. You can turn off tracing using - - - - -/> 3. While developing using Visual Studio.NET When you set Configurations Option as "debug" mode, it creates pdb file to store the debug information hence before deploying the application set it to the "Release" mod...

Dynamic binding Named and optional parameters COM specific interop features Co anVariance

* What's New in .NET 3.5 It is recommended to read first 'What's New in C# 3.0' ASP.NET Support for enabling existing ASP.NET 2.0 pages for AJAX Creation of ASMX & WCF based web services and consuming them from AJAX Library ASP.NET server side application services like authentication, roles management exposed as web services ASP.NET Merge Tool - a new tool for merging pre-compiled assemblies New ListView control which supports edit, insert, delete, sorting & paging ASP.NET integrated with core IIS 7.0 which makes ASP.NET services like authentication & caching available for other content types also. Microsoft AJAX Library to support AJAX based web development Base Classes & CLR Support for CSharp 3.0 including LINQ. HashSet : A high performance collection of type set. Time Zone Improvements : to develop applications which operate in multiple time zones Minor Improvements in Threading, Reflection and controlling the GC behavior Others Cl...

A Delegate Usage Example namespace MyFirstDelegate { //This delegate can point to any method, //taking two integers and returning an //integer. public delegate int MyDelegate ( int x, int y); //This class contains methods that MyDelegate will point to. public class MyClass { public static int Add( int x, int y) { return x + y; } public static int Multiply( int x, int y) { return x * y; } } class Program { static void Main( string [] args) { //Create an Instance of MyDelegate //that points to MyClass.Add(). MyDelegate del1 = new MyDelegate ( MyClass .Add); //Invoke Add() method using the delegate. int addResult = del1(5, 5); Console .WriteLine( "5 + 5 = {0}\n" , addResult); //Create an Instance of MyDelegate ...