Using MySql as Linked Server in Sql Server 2008

Using MySql as Linked Server in Sql Server 2008 Recently I was given an assignment to create some Reports using SSRS. They system were in use was osTicket (its an open source php, mysql) ticket management system. I decided to use SQL server linked server object.  By doing this I can easily query and write […]

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 […]

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 […]

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 […]

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 […]

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

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)

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 […]

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

SQL server connection time out

How to increase Connection time out in Sql server? Connection time out can be defined in SQL server in two ways. 1)  Connection timeout in Connection string property: which determines how long it should take to open a connection. It is affected by Network i.e. If network is slow. If a connection is opened the […]