Original post pretty sparse but here's a shot.

Start simple and work up, connect your people table to your upload table.

select *

FROM people p

RIGHT join upload1 u1 ON p.lastName = u1.lastName

...

OY! RTFPost...

only one table in a session can have the IDENTITY_INSERT property set to ON.

Running the set identity_insert in the sproc is residing in a different session.

IDENTITY_INSERT can only be on for one table at a time. You are turning it on but never turning it off.

CREATE PROCEDURE usp_IdentityTest

AS

CREATE TABLE #table1(ID INT IDENTITY(1,1))

CREATE TABLE #table2(ID...

This probably isn't the most efficient way but...

Create a date table (reasonable range)

weekdays (id, dayDate)

with only weekday dates.

FROM invc

inner join weekdays currDate on invc.mydate = currDate.dayDate --may need both at...

I had a similar issue between script execution and sproc execution. Noticed the difference in the execution plans (query execution plan and profiler w/xml). Tweaked a...

Back when I was having a similar problem with a query in production taking an inordinate amount of time for the web site but not from the SSMS. (still...

CREATE TABLE TableA

(RowID INT IDENTITY,

DocID int,

DocType varchar(15),

DocValue varchar(250)

)

INSERT INTO tableA (docID, docType, docValue)

SELECT 50,'FName','Mary' UNION ALL

SELECT 50,'LName','Jones' UNION ALL

SELECT 50,'EmpID','12345' UNION ALL

SELECT 62,'FName','John' UNION ALL

SELECT 62,'LName','Smith' UNION ALL

SELECT 71,'LName','Moore' UNION...

Couple small items

a) remove the interesting case statements with a.* and test tempdb size

b) is nullif(expr1, '') = null more efficient then expr1 = ''?

I was surprised that i could use # to alias a derived table. Looking into the execution times there was no difference (in time or plan). Further testing...

from BOL

'

Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.'

Give it a shot

usage

Declare @EntryValue varchar(MAX) -- with the actual...

I haven't test this idea but...

DECLARE @RequestIDs varchar(MAX)

luck

Daryl

hmm. set all the variables so nicely above and then didn't replace

....

SELECT DATEADD(dd, (@aWeek - 1) * 7 + @aFridayOffset, @aJan1)

The code below should do the trick.. TSQL below not the actual function...

DECLARE @argYearWeek VARCHAR(6), @aWeek INT, @aJan1 DATETIME, @aFridayOffset int

SET @argYearWeek = '200733'

SET @aWeek = CAST(RIGHT(@argYearWeek,2) AS INT)

SET...

Not sure if this would fit your needs. Not an elegant solution... But it shouldn't be too much of a stretch to have some string manipulation to turn the...

If you are using the SSMS...

right click [Databases] new database

in the new database wizard schroll to the right in the [Database files] area to the Path entry.