I am SO STUPID - it was NOCOUNT!

Thanks!

J. Moseley

CREATE PROCEDURE dbo.usp_ManDial_PD_Status_DMM_Rank

@sitevarchar(1),

@EmpLevel int

as

declare @location nvarchar(25)

Set @location = (case @site

when 'a' then 'Austin'

when 'l' then 'Louisville'

Else Null end)

begin

SELECT

RANK=IDENTITY(INT),

Site,

TL_EmpNo,

MGR_Name,

EmpNo,

Coll_Name,

EmpLevel,

DMM_pts

INTO #RANK

FROM dbo.tbl_ManDial_PD_Status_Report_Aggregate

WHERE 1=2 -- always false...

Great ideas for all kinds of ranking! I am sticking with Ken Henderson's solution using the temp table, but reading the Microsoft article gave me the idea to query...

I have not figured out a way to truncate the Excel “table” – DTS says it’s a no-no when you try it. This is what I do instead. ...

it's reference is already in the update statement, don't put it in the from clause.

J. Moseley

My guess is that the temp table solution would be the fastest but I'm not sure. Bet we have some experts out there that would know for sure! ...

BTW, I know where exists and nested subqueries can be a bit confusing! The really simple solution is to create a temp table from an aggregate query on tbl_OrderNotes...

Oops - I screwed up on the last one! Tested it with two orderid's and discovered that what you really need is where exists, like this:

select * from tbl_OrderNotes

go

--OrderIDNOTEIDType_CDtxt_notes

--189661829.09Test...

Don't you really just want the note associated with max noteid?

select * from tbl_OrderNotes

go

--OrderIDNOTEIDType_CDtxt_notes

--189661829.09Test Customer

--189661837.09Test Customer7

--189661845.09Test Customer2

--189661853.09Test Customer50

select * from tbl_Master

go

--order_idom_note

--18966

update tbl_Master

set om_note = o.txt_notes

from tbl_OrderNotes o

where

o.OrderID = tbl_Master.Order_id

and

o.type_cd...

take out the (8)

drop table test

go

create table Test(myfloat float)

go

exec sp_columns test returns the float you expected.

J. Moseley

Thanks, David! SMB, let us know how it goes.

J. Moseley

sp_executesql is faster and more feature laden than EXEC(). Use it when you are exectuting a dynamic string multiple times in succession where only the parameters change because query...

Have you tried Exec() function? It allows concatenation.

J. Moseley

It looks to me like your "bad" lines don't have enough commas, yes? I have written scrubbing routines before in an ActiveX script within a DTS package that use...

I cancelled the run and changed the source to select top 100 * from source table. That took almost a minute. Maybe there is just too much overhead....