Accessing #temp table in a proc as a user with minimal rights.

I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:

> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>

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