Access temp table within Table value function

hi,

I am trying to access a temperory table within a table values function, but when i try to do i get an error saying unable to access.

This is wat i am trying to do. here is the sample code. I am creating a table value functiona and trying to insert data from a temp table. if this is not possible, there should be an alternative. creation of this function is important as the same is used in SSIS to build a package.

Please help me ....

Create function SiteAddress_func()

returns @.Data_For_Site_Address_Table table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(10),

CreatedBy varchar(20)

)

As

Begin

insert into @.Data_For_Site_Address_Table

select * from #NON_Matching_Records_From_Site_Address

return

end

rgds,

Meher Krishna.V

Meher:

One of the rules for function creation is that a function is not allowed to access temp tables. Please examine books online about some of the constraints that are applied to functions.

Dave

|||

hi Dave,

your right.... But there should be an alternative to this ? I cannot use an SProc here, because they are not schema bound.

rgds,

Meher

|||

Meher:

You might need to resort to transorming your temp table to a "string array / string table" and passing that to the function. Also, since your function does not include schema binding why are you worried about stored procedures not being schema bound? How are you planning on using this function?

You can find an article here that discusses array implementation here:

http://www.sommarskog.se/arrays-in-sql.html

Dave

|||

I need to use this function in creating an SSIS package. Here its important that whatever i use should be schema bound. Only then i can map the columns. If i use a stored procedure with temp tables, my purpose will be defeated as it not schema bound.

rgds,

Meher

|||

Instead of using the Temp table you can use Physical table..

UDF never allows to access the temp tables..Need more info about what exactly your function try to do.. So we can suggest similar solution with Physical table...

|||

Here is the problem in detail.

I am extracting the required data from three physical tables. After extracting i will be doing furthur manipulations like running a cursor to remove the duplicates and store the final output data in a temp table.Then i will be using this query in an SSIS package in BIDS(Business intelligent development studio) to transform data from SQL server to an MDB file at regular basis using jobs and schedulers.

The constraint is that i cannot use temp tables directly in the SSIS package because it doesn't return a schema. The same is with sproc. I need it to return a schema so that i can map the fields from an input source namely SQL to a destination which is the MDB file. So, instead of using sproc i am trying to using a tvf(Table Value Function) which is capable of returning a schema. But even this failed as i cannot use a temp table in a function.

Hence creating physical tables for this purpose alone is a bit problem as the data porting is to be deployed on a live server.

please let me know wat to do furthur.......

rgds,

Meher