April 15, 2002 at 9:41 am
I have created a stored procedure as dbo which selects data into a temporary table then performs a select joined to the temporary table. I run the SP as user cmss_user which creates the temp table as owned by cmss_user but then fails on the following select in the SP. This works on a clients SQLServer but not on my development SQLServer. Can anyone help me?
April 15, 2002 at 9:42 am
We are both on SQL7 by the way.
April 15, 2002 at 9:55 am
April 15, 2002 at 10:09 am
The first bit works but the second bit doesn't:
SELECT LEFT(mas_firstname, 1) AS t_initial,
mas_lastname AS t_lastname,
adr_address_code AS t_postcode,
COUNT(mas_urn) t_count
INTO tmp_dedupe
FROM ncm_master
JOIN ncm_addresses
ON mas_urn = adr_mas_urn
WHERE mas_lastname IS NOT NULL
AND mas_lastname LIKE @Lastname
AND adr_address_code LIKE @Postcode
GROUP BY LEFT(mas_firstname, 1), mas_lastname, adr_address_code
DELETE FROM tmp_dedupe WHERE t_count = 1
April 16, 2002 at 4:40 am
Here is some clearer code. The stored procedure created as dbo and run as cmss_user fails on the second select with 'aarobtmp not found'. cmss_user has db_owner permissions.
CREATE PROCEDURE rob_tmp_1
AS
SELECT glt_urn,
glt_code,
glt_description
INTO aarobtmp
FROM ncm_general_list_type
SELECT * FROM aarobtmp
GO
April 16, 2002 at 6:01 am
No idea yet. Couple questions though. Why don't you use a true #temp table rather than creating a permanent table? And if you're just doing a select, why not just return the results directly rather than go through the intermediate step of creating the work table?
Andy
April 30, 2002 at 10:05 am
I tried something similar to this and it works. May be you are missing something minor. What is the error msg you get?
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 30, 2002 at 10:38 am
As Andy mentioned, try to use real temp table #aarobtmp and you probably will get thru.
There are some permissions issues regarding "select --into":
May be the following error message has some relevance in your case:
"Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply