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 6 3747
pb648174 (go****@webpaul.net) writes: 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.
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.
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...
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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?
pb648174 (go****@webpaul.net) writes: 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?
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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)
pb648174 (go****@webpaul.net) writes: Actually I specifically said "It returns no errors in query analyzer, but when profiler is run, it shows that "Error 208" is happening" - it
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?
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.
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.)
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,
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.
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.
Popular for crosstabs and that sort of thing is RAC,
see http://www.rac4sql.net.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Corrine |
last post by:
Hi,
I am creating a global temporary table that is session-specific. I
insert a BLOB into this table, and then select the BLOB from this
table into a ResultSet. The ResultSet sees this BLOB...
|
by: Ranga |
last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on
unix version of DB2, it gave the follwing error
db2 => create global temporary table temp ( OGI_SYS_NR char(8) )
DB21034E ...
|
by: gwaddell |
last post by:
I have a stored procedure that is loading data into a global temp table.
Here is all the code for that stored procedure.
CREATE PROCEDURE
AS
SET NOCOUNT ON
BEGIN
|
by: chettiar |
last post by:
I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerServiceTypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE...
|
by: pukivruki |
last post by:
hi,
I wish to create a temporary table who's name is dynamic based on the
argument.
ALTER PROCEDURE .
@PID1 VARCHAR(50),
@PID2 VARCHAR(50),
@TICKET VARCHAR(20)
| |
by: crazy_jutt |
last post by:
hi,
anyone knows if i can create index on global temp tables
if yes, will i use session schema for indexes also ?
if yes, can i create all kinds like unique, clustered, mdc etc indexes
in...
|
by: Rahul B |
last post by:
Hi,
I have very little knowledge about creating Procedures/functions in
DB2.
When i tried to create the test function like
CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT...
|
by: Lennart |
last post by:
Any thoughts on the following scenario anyone?
During a performance test I discovered that the application asked one
specific query extremely often. It turned out that this particular
query...
|
by: romV |
last post by:
Hi,
I have a procedure that creates the global temp table (##temp) inside a procedure which is populated with data when it is run for the first time. And when the procedure is executed second time,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |