T-SQL XML update with XML fragment of another linked table

I am looking for a way to update (.modify('insert into..') to be specific) an XML column in a table with xml fragments from another table linked by a foreign key. For example, my table structure looks like below (simplified): Fields 1 to 5 in each ta

SQL Server: order of linked lines (routes)

I have a table with routes and I need to give them actual orders. The data looks as follows: route | rstart | rfinish | rfrom | rto | rorder -------------------------------------------------- route1| A | E | A | B | 0 route1| A | E | B | E | 0 route2

Convert numeric (contains special characters) to silver

Given declare @text varchar(4) ='3677.98*' , @text2 varchar(4) ='1245367.98%' I want the output as follows: 3,677.98* and 1,245,367.98% I tried below: select convert(varchar,cast(3677.98 as money),1) --output as 3,677.98 If I try select convert(varch

how to convert the date to a format `mm / dd / yyyy '

I'm having a sql table with date column named CREATED_TS which holds the dates in different format eg. as shown below Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Feb 20 2012 12:00AM 11/29/12 8:20:53 PM Nov 16 2011

Excel to SQL Server by using SSIS

I am loading data from Excel Source to SQL Server destination. And it is working fine. The problem is the empty rows are also inserting into the database. Can you help please. ThanksCheck the step 7 of article he has used conditional splits you can u

Identity insertion on the linked server fails

I want to use a stored procedure to copy a table from my test database to a linked server with the same ID's / Identity but I can't get it to work.. I've set the IDENTITY_INSERT to ON but it still complains about the ID column. Here's my procedure: C

The FORMAT function does not work in SQL Server 2008 R2

DECLARE @d DATETIME = '01/01/2011'; SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result; I am using above code in SQL Server 2008 R2 but it is encountering an error: 'FORMAT' is not a recognized built-in function name. How can I use FORMAT function?FORMA

Suppression with GROUP BY

How do I change this SELECT so it is the criteria used for deleting? SELECT COUNT(empid), empid, status, deptid from tableA GROUP BY empid, status, deptid HAVING status is null and deptid = 5 What do you want to delete? If simply records with NULL st

SQL Server: Largest N per extended group

My table looks like: A B C D 1 1 1 1 1 1 3 2 1 1 0 4 1 1 2 1 1 2 1 0 1 2 0 2 1 2 4 5 2 1 5 3 My goal is to, for each pair of A and B, output the value in D that corresponds to MIN(C), and the value in D that corresponds to MAX(C). The output should b

SQL Server returns the unexpected week number

I have some orders in a table and the last order date of 2011 is 20th Dec. I'm using a sql command to calculate the number of orders in a given week: SELECT CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) AS week, COUNT(1) as orde

Find all tables with a given foreign key

I have a table A that has primary key Id. I want to find all tables that contain this key as foreign. Number of tables is about 100, so its rather impossible to use generated database diagram. Is it any way to to query database schema objects to find

How to get the hierarchy level of this query

I am trying to get the hierarchy level of this query. Here is the CTE recursive query WITH CategoryRec AS ( SELECT Id, Parentid, Name FROM dbo.Category UNION ALL SELECT cr.Id, c.Parentid, cr.Name FROM CategoryRec AS cr INNER JOIN dbo.Category AS c ON

Make CakePHP work with SQL Server 2008

I can call SQL server 2008 from PHP with Microsoft Drivers for PHP for SQL Server ( http://www.microsoft.com/download/en/details.aspx?id=20098) But as Sqlsvr driver class is needed to use CakePHP with SQL server 2008, I got the driver file from follo

Upgrade SQL Server 2008 Standard to Developer?

I have installed SQL Server 2008 Standard edition and I want to upgrade it to Developer version. I installed the Standard version for my MSDN subscription. I have tried to upgrade from the Standard ISO and Developer ISO and it does not give you an op

How to rename a database without losing data?

HEllo All, I am in a problem in my sql database.I have a DB in sql 2005.I just want to rename that DB and want it in sql 2008 without losing its data.For examble,i have a mdf and a ldf file of a DB named "Hello" in sql 2005 with a 1000 data,i wa

Program not connecting to the new database

I moved a VS Solution file from an old PC to a new PC. When I opened the solution in VS2010, I found that the database connections in database explorer was not present. Now I want the project to connect to a SQL Server database which is local to the