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 [...]
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 [...]
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 [...]
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 [...]
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: • [...]
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)
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 [...]
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
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 [...]
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 [...]
