Database, SQL (Structured Query Language)

Comments Off on select XML data from parent child tables sql server 2008

select XML data from parent child tables sql server 2008

Recently I was working on to create XML menu from SQL table. We have two tables one table is NavigationSystem and second table is Navigation Item. Following was required to show Parent Child relation ship.   <Navigation> <NavigationSystem> <systemid>1</systemid> <Title>Top Menu</Title> <Items> <Item> <systemid>1</systemid> <ItemId>1</ItemId> <label>Home</label> <Url>http://www.isolutionteam.co.uk</Url> </Item> <Item> <systemid>1</systemid> <ItemId>2</ItemId> <label>Database</label> <Url>http://www.isolutionteam.co.uk/Database</Url> </Item> </Items […]

Database, SQL (Structured Query Language)

Comments Off on Delete duplicate records SQL

Delete duplicate records SQL

How to delete duplicate Towns from Town Table  Using SQL Script where Primary key is unique but Town are duplicate. SQL Server and MY SQL DELETE Town1 FROM  Town Town1 INNER JOIN Town Town2 ON Town2 .TownName=Town1.TownName WHERE Town1.TownID >Town2.TownID Above SQL Script will delete Duplicate Towns from a Twon table where Town ID  is […]

Database, SQL (Structured Query Language)

Comments Off on Find occurrence of object in a database using sql script

Find occurrence of object in a database using sql script

Following is very useful script which will give you occurrence of object or will tell you where you have used the object like field name is being used in stored procedures, functions and views. SELECT so.name, so.TYPE, sc.TEXT FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id WHERE  sc.TEXT LIKE ‘%[Field Name,Stored procedure […]

Database, SQL (Structured Query Language)

Comments Off on SQL error handling using Try Catch with sql server 2008

SQL error handling using Try Catch with sql server 2008

Following is code snipest which tells how to handle errors in SQL , T-SQL using Try Catch block. The code is self explanatory. This works excellent with Insert, Delete and Update SQL commands. Tested with Microsoft SQL server 2008. USE [databasname] BEGIN TRY BEGIN TRANSACTION Script code here; COMMIT TRANSACTION END TRY BEGIN CATCH IF […]

SQL (Structured Query Language)

Comments Off on Assigning and Modifying Database Values in Place

Assigning and Modifying Database Values in Place

Assigning and Modifying Database Values “in Place” SQL Server 2008 introduces new compound assignment operators beyond the standard equality (=) operator that allow you to both assign and modify the outgoing data value. These operators are similar to what you would see in the C and Java languages. New assignment operators include the following: • […]

Database, SQL (Structured Query Language)

Comments Off on Case Sensitive comparison of data in Select query

Case Sensitive comparison of data in Select query

You can forcefully do the case sensitive comparison of data in sql serer by using COLLATE Latin1_General_CS_AS in a select query Select * from  Table1 inner join Table2 Table2.ID=Table1.ID where (Table2.WebsiteUrl  COLLATE Latin1_General_CS_AS)<>(Table2.websiteurl COLLATE Latin1_General_CS_AS)

Database, SQL (Structured Query Language)

Comments Off on How to export delimited (tab, space, comman) file from query analyzer

How to export delimited (tab, space, comman) file from query analyzer

How to export delimited (tab, space, comman) file from query analyzer Visual Management Studio (2005) In the  Query menu choose Query options 1.  From Results Choose Text 2. Choose out format from drop down list 3.  Click OK. 4. In the query menu from  Result to choose Result to file 5.  Run your query it […]

SQL (Structured Query Language)

Comments Off on How to get only date from data field SQl server t- sql

How to get only date from data field SQl server t- sql

Following line gives you the short date  from long date time field. ie. From a date time field of Sql server field if you need to extract only date use the the following method. Replace GetDate() with your date field name. Cast(Floor(Cast(Getdate() as float)) as datetime

Database, SQL (Structured Query Language)

Comments Off on Accessing Different Database on Different SQL Servers

Accessing Different Database on Different SQL Servers

Accessing Different Database on Different SQL Servers I was trying to Access two databases on two different servers. I was usring four part name to SQlServer “[sqlserver02].MyDatabase.dbo.aspnet_membership” but it was not letting me do that and was showing following error message. Msg 7202, Level 11, State 2, Line 1 Could not find server ‘sqlserver02’ in […]

SQL (Structured Query Language)

Comments Off on Two methods to update data from one table to another table using sql and TSQl

Two methods to update data from one table to another table using sql and TSQl

Two methods to updated data from one table to another table using sql and TSQL, T1=Table1 T2=Table2 F1=Field1 F2=Field2 F3=Filed3 F4=Field4 Method one, Update date using subquery from one table to another table update T2 set T2.F1=isnull(T1.F1,”), T2.F2= T1.F2, T2.F3= T1.F3 from T1 inner join T2 on T2.F2= T1.F4 where T2.F2<> T1.F2 and T2.F3<> T1.F3 […]