Category SqlServer

export select query result in to pipe delimiter Textfile

Hello Friends, I have a table that I need to export to a pipe delimited .txt file.please follow the below step to do that things. Steps 1: [https://amitpatriwala.wordpress.com/2010/04/02/save-select-query-result-into-textfile/] Enabled the xp_cmdshell By default disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure To enable xp_cmdshell using […]

Save Select query result into Textfile

Here I am explaining how to export query results into text file. Please follow below steps to archive this thing. Step 1: Enabled the xp_cmdshell By default disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure To enable xp_cmdshell using sp_configure, use below query : EXEC […]

HierarchyId Data Type

Here I Explained the New Data Type in Sqlserver 2008. What is HierarchIyd Data Type?  HierarchyId data type is a variable length System Data Type. What is the use of HierarchyId Data Type? Represent position in a hierarchy manner. HierarchyId does not automatically represent a tree, it is depend on how to application generate and […]

Sql Server 2008 Feature – Part1

Here I am explaining Sql Server 2008 Key Features. 1) Initializing variable when you declare declare @val as int =0 declare @currentdate as datetime = getdate() print @val print @currentdate 2) Compound assignment operators –operator like +=,-=,/=,*/,%= declare @val as int = 0 set @val += 10; print @val 3) Add multiple row in single […]

Get Parameter List from StoreProcedure or Function in SqlServer 2005

–get all storeprocedure and function SELECT * FROM INFORMATION_SCHEMA.PARAMETERS –pass storeprocedure name or function name –get the relavent columns ParameterName,DataType,Length,ParameterType SELECT Parameter_name as ParameterName,Data_type as DataType,coalesce(Character_Maximum_Length,0) as Length,Parameter_Mode as ParameterType FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME=‘sp_InsertOrder’

Get Primary key – Foreign key relations table in sql server 2005

Why it is required? The main purpose of this is finding related constraint table from database. Check below Query. –get table list with constraint(primary and foreign key) select * from information_schema.constraint_column_usage –get table list with foreign key constraint select * from information_schema.referential_constraints –get the relation select tblAll.table_name as PrimaryTableName, tblAll.column_name as PrimaryTableColumn, tblFK.table_name as ForeignKeyTable, […]

The media set has 2 media families but only 1 are provided

I got this error when restore database from .bak file. why this error happen? when you backup the database at that time you have not delete the existing path means you start a back up behalf of the previous one means your backup is divided in to more parts. steps : 1)  you select all […]

Regular Expression in Sqlserver 2005

why Regular Expression Required for sqlserver 2005? The basic need Of Regular Expression is avoiding hard work for searching or pattern maching in database. Example if i want to search valid email address from users table then what will be senario for that? i have thousand of newly registered user daily. then how to i […]

FullText Search In SqlServer 2005

why fulltext search used? Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server Database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language. The […]

use case in where clause in sqlserver 2005

steps: –declare temp table declare@tbl as table(id int,username varchar(50),Age int) –insert data into temp table insert into @tbl values (1,‘Amit’,10)  insert into @tbl   insert into @tbl values (1,‘Bhavin’,10)  insert into @tbl   insert into @tbl values (1,‘Chirag’,10)  insert into @tbl   insert into @tbl values (1,‘Dhaval’,10)  insert into @tbl   insert into @tbl values […]