My Rational Rights

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