Hey I tried this but didnt help me...

pls find the attachement for execution plan.

thanks for the concern.

please check the atttachments for table structures.

Right now its taking more than 20min . each table has 3574385 records approx.

Hey I got a new error from your proc.

Msg 209, Level 16, State 1, Procedure xSynPortalToSolomon_ProgTEST, Line 117

Ambiguous column name 'programid'.

I checked at that column everything looks fina in my...

Thank you very much, you did most my work.

when i exec the proc i get this error.

OLE DB provider "SQLNCLI" for linked server "LS_PORTAL" returned message "The partner transaction manager...

Exactly.

Can you pls update my proc with your best performance ideas.

If you cant read attachement, look at this

CREATE TABLE #tempdays (

PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

days VARCHAR(255)

)

INSERT INTO #tempdays select SelectedDays from FAS_LaborCalendar

--select * from #tempdays

select pk,count(*) from (

SELECT lc_id pk,SUBSTRING(';'+SelectedDays+';',pk+1, CHARINDEX(';',';'+SelectedDays+';',pk+1)-pk-1) AS Value ...

Matt Miller (6/16/2008)

select pk, count(*)

from

(SELECT lc_id pk,

...

but for the function i can pass string a parameter, how can i pass my select query so tht I can iterate through each row.

I have a function where if i pass parameters (string,delimiter) it gives each row. something like

If Run this

select * from dbo.myfunction('1;2;3;4;11;10;9;8;7;14;15;16;17;18;25;24;23;22;21;28;30;29',';')

I get result as

1

2

3

4

11

10

9

8

7

14

15

16

17

18

25

24

23

22

21

28

30

29.

How Can i...

thanks matt

I am not getting about Tally table and value N from your query.

this is my table structure

CREATE TABLE [dbo].[FAS_LaborCalendar](

[LC_Id] [int] IDENTITY(1,1) NOT NULL,

[ProgramInstanceId] [int] NOT NULL,

[FiscalPeriod] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[HoursPerDay] [float] NOT NULL CONSTRAINT [DF_FAS_LaborCalendar_HoursPerDay] DEFAULT ((8)),

[NumberOfDays] [int] NOT NULL...

thanks guys..

i cant change the design. I just want the result as i need and doesnt mind if am using temp table or any long tsql query.

I am not sure...

any help?