Cursors in MS T-SQL should be avoided unless absolutely necessary as they are extremely slow and inefficent.

Using one of the methods mentioned in previous posts is a better and more...

If you're using SQL 2008 then its simple

Select CONVERT(DATETIME,CONVERT(DATE,GETDATE()),112)

This will automatically strip off the Time part of the date.

PS : The 112 is option, and an old habit of mine.

Jeff

A unique contraint would elimate the chance that there are 2 or more records with the same EmpId.

However it could also be a circular reference in the Employee,Supervisor chain,...

Look at using the Binary_checksum() function in SQL for all columns but the Business Key and meta data columns.

So

Update

Destination

Set

Col1 = Source.Col1

:::::

From

...

Look at using the Binary_checksum() function in SQL for all columns but the Business Key and meta data columns.

So

Update

Destination

Set

Col1 = Source.Col1

:::::

From

DestTable...

Nice Article Jeff, and having spent a lot of time recently working with them its good to see alternative solutions.

One common problem is people come across is when they...

To use the anology of the car from a previous post, would you expect to go onto the internet and expect someone t be able to teach you how to...

aravind-305595 (3/8/2011)

---

Thanks All, The solutions are working fine. Thanks for your inputs.

Do we have an option incase if we are not scheduling them in SQL job ? I mean if...

Can I check that you have SSMS R2 installed on your local machine (version 10.50.1600.1 or greater), though I dont know why that would stop you connecting via Excel 2010,...

Glad to help, I did find the problem interesting hence the quick response. 🙂

For SQL Server 2005 you need to do a little more work,

SET @stmt = N'DECLARE @AllocPages int;

SELECT @AllocPages=sum(total_pages) FROM '

+ QUOTENAME(@dbname) + '.sys.allocation_units;

INSERT #AllocTable (Database_id,Database_Name,AllocPages)

values...

The problem isnt in the CTE its in the Dynamic SQL thats within the cursor, where you have SUB Select in the Insert statement.

Is there a reason that you haven't migrated the cubes to SSAS 2008 as that would be a better solution.

I'm not sure that the .Net supports calls to the older...

I cant see anything wrong, the only issue may be the DB_NAME function, out of curisoity have you tried substituting it with the at.Database_Name reference from the Temp Table?

Personally I only resort to table variables during proof's and avoid them in production code, thanks to a previous boss who was a bit of a zealot on the matter....