Global temp table permissions

I have a pivot table implementation, part of which is posted below. It
returns no errors in query analyzer, but when profiler is run, it shows
that "Error 208" is happening. I looked that up in BOL and it means
that an object doesn't exist. This block of code below works fine on my
local development machine, but not on our shared development server
until I go into the tempdb and make the user have the role db_owner.
Even wierder is that when I do a select * from ##pivot there is no
error, but if I specify the single column name (pivot) i.e. select
pivot from ##pivot, it takes the error...

Obviously this is a rights issue, but is there any way around this
other than making the user owner of tempdb??

declare @select varchar(8000), @PackageId int
set @PackageId = 10
set @select = '
select
Company = COALESCE(Users.Company, Contact.Company, ''''),
SubContractPackageVendor.Id, SubContractPackageVendor.isActive,
SubContractPackageVendor.isAwarded,
SubContractPackageVendor.UserOrContactType,
SubContractPackageVendor.UserOrContactId
FROM
SubContractPackageVendor
LEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =
SubContractPackage.Id

LEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =
Users.UserId
LEFT JOIN UserRoles ON UserOrContactType = ''User'' AND
UserRoles.UserId = Users.UserId AND UserRoles.ProjectId =
SubContractPackage.ProjectId
LEFT JOIN Role ON Role.RoleId = UserRoles.RoleId

LEFT JOIN Contact ON UserOrContactType = ''Contact'' AND
UserOrContactId = Contact.Id

LEFT JOIN SubContractLineItem ON
SubContractLineItem.RefType = ''Package'' AND
SubContractLineItem.RefId = SubContractPackageVendor.PackageId
LEFT JOIN SubContractLineItem as SubContractPackageVendorItem ON
SubContractPackageVendorItem.RefType = ''PackageVendor'' AND
SubContractPackageVendorItem.RefId = SubContractPackageVendor.Id AND
SubContractPackageVendorItem.RefSubId = SubContractLineItem.Id
Where
SubContractPackageVendor.PackageId = ' + CAST(@PackageId as varchar)
+ '
GROUP BY
SubContractPackageVendor.Id, SubContractPackageVendor.isActive,
SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,
SubContractPackageVendor.UserOrContactType,
SubContractPackageVendor.UserOrContactId'
--print @sql

declare @sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@FieldPrefix varchar(5),
@TotalFieldName varchar(50),
@PivotFieldFilter varchar(1000)

select
@sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,
@pivot ='SubContractLineItem.Category' ,
@table ='SubContractLineItem' ,
@FieldPrefix='~' ,
@TotalFieldName = 'Total' ,
@PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +
CAST(@PackageId as varchar)

set nocount on

DECLARE @sql varchar(8000), @delim varchar(1), @TotalSql varchar(8000)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + '
WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' +
@table + ' WHERE '
+ @pivot + ' Is Not Null ' + @PivotFieldFilter)

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char',
data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

select * from ##pivot

DROP TABLE ##pivot

re: Global temp table permissions

pb648174 (google@webpaul.net) writes:[color=blue]
> I have a pivot table implementation, part of which is posted below. It
> returns no errors in query analyzer, but when profiler is run, it shows
> that "Error 208" is happening.[/color]

Because of deferred name resolution, you see quite a lot of 208 in
Profiler which are "false alarms". When SQL Server first compiles your
batch, ##pivot does not exist which causes a 208 error which is then
suppressed. When you hit a statement where ##pivot is referred, the
batch is recompiled.
[color=blue]
> I looked that up in BOL and it means that an object doesn't exist. This
> block of code below works fine on my local development machine, but not
> on our shared development server until I go into the tempdb and make the
> user have the role db_owner. Even wierder is that when I do a select *
> from ##pivot there is no error, but if I specify the single column name
> (pivot) i.e. select pivot from ##pivot, it takes the error...[/color]

And that is a 208 and not a 207 ("Column does not exist"?) There could
be an old ##pivot which hanging around.

In any case, I'm not a friend of global temp tables, and I don't recommend
use of them. I don't know what your underlying problem is, but my favourite
is a spid-keyed table. That is, a permanent table, but which is intended
to hold any data only transitionary. The first column is spid, and in
the simplest form you use @@spid for the value.

Now, @@spid, does not work well, if you are to share the table between
processes. For this reason, we use negative values in these cases. You
could simply do:

BEGIN TRANSACTION
SELECT @nextksy = coalesce(MIN(spid), 0) - 1 FROM tbl (UPDLOCK)

INSERT tbl (spid, ...) VALUES (@nextkey, ...)

COMMIT TRANSACTION

Of course you should take measures to have old data removed from the
table once you are done with it. But that is really not any different
from the global temp table. Except, that is, you have more control
over it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Global temp table permissions

Why does giving the user running the process db_owner access solve the
problem? Is there some other way other than rewriting large blocks of
working code?

re: Global temp table permissions

pb648174 (google@webpaul.net) writes:[color=blue]
> Why does giving the user running the process db_owner access solve the
> problem? Is there some other way other than rewriting large blocks of
> working code?[/color]

Judging from your post, the code is not working in the general case, only
in a protected environment.

I don't know where db_owner comes into play. I played around a little
with a plain user and global temp tables, but I did not ran into any
problems.

Then again you have not specified what "not works on the shared development
server" means. Do you get error messages? Incorrect results?

Maybe the problem is access rights to the other tables, and not with
the global temp table?

Anyway, global temp tables, is in my opinion a poor solution, because
there is always a lot uigly what-if scenarios. Whence my recommentation
to change to something better.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Global temp table permissions

Actually I specifically said "It returns no errors in query analyzer,
but when profiler is run, it shows that "Error 208" is happening" - it
is only on one machine that it does this, and the fix for that machine
was to give the user running the command db_owner access to the tempdb
database.

The reason I am using the global temp table implementation for this is
because the below statement correctly (and dynamically) creates the
type of the column automagically. If there is a way to do that with the
method you suggested, or better yet, to find out why giving the tempdb
db_owner access fixes the 208 error and keep the existing working,
tested and released code, that would be great. Lastly, if there is a
better way to perform a pivot table in SQL Server(with working sample),
I am all ears - this was simply the best I found that actually works.

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + '
WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM
' +
@table + ' WHERE '
+ @pivot + ' Is Not Null ' + @PivotFieldFilter)

re: Global temp table permissions

pb648174 (google@webpaul.net) writes:[color=blue]
> Actually I specifically said "It returns no errors in query analyzer,
> but when profiler is run, it shows that "Error 208" is happening" - it[/color]

And as I explained to you, the 208 in Profiler is a false alarm.

So you don't get any error, but how then you do conclude that it doesn't
work? Or don't you really have any problem at all? Do you get incorrect
results, or what is your real problem?
[color=blue]
> is only on one machine that it does this, and the fix for that machine
> was to give the user running the command db_owner access to the tempdb
> database.[/color]

Or because when you reran the batch, the plan was in the cache, and the
208 did not happen behind the scenes.

db_owner could possibly have something to do with your reading
of INFORMATION_SCHEMA.COLUMNS, since that view is supposed to only
show things you have access to, so somesuch. Try using syscolumns
instead. (But here I am guessing very wildly of what you problem you
really have.)
[color=blue]
> The reason I am using the global temp table implementation for this is
> because the below statement correctly (and dynamically) creates the
> type of the column automagically. If there is a way to do that with the
> method you suggested, or better yet, to find out why giving the tempdb
> db_owner access fixes the 208 error and keep the existing working,[/color]

Again, the 208 you see in Profiler is not a problem!

Rather than using SELECT INTO, create the table with CREATE TABLE. Then
you can use a plain #temp table.
[color=blue]
> tested and released code, that would be great. Lastly, if there is a
> better way to perform a pivot table in SQL Server(with working sample),
> I am all ears - this was simply the best I found that actually works.[/color]

Popular for crosstabs and that sort of thing is RAC,
see http://www.rac4sql.net.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Global temp table permissions

Ok, that worked great, thanks.

I was thinking I couldn't used the #temp table since it was doing an
exec sql select and had already tried the @temp table style... I also
wish I would have seen that reference to tempdb in there, that was a
good educated guess. I pulled that pivot code from another site,
apparently it had some problems. It is now working well with a normal
#temp table (although I have to do some shenanigans to get the type of
the column correct, which that statement with the global temp table did
so easily.)

Thanks for the help!