Office Web Component Strange error

[Bunty Singh]

I'm using Office Web Component for on the web page. Its works fine when I use
SP querying table directly as given below

create proc mySP
AS
SELECT * FROM myTable
GO

But if my SP uses temp tables as given below

create proc mySP
SELECT * INTO #Temp
FROM myTable

SELECT * FROM #Temp
GO

I get the error given below.


The query could not be processed:
o The data source you selected might be empty, might be linked with an
invalid link, or you may not have sufficient security permissions to access
the data.

Can someone help? Thanks in anticipation.

 

[rweide]

not an expert in anything, but the error message seems to give lots of clues
already? check DB table permission for the user, and make sure your temp
table does have something in it.

 

[Bunty Singh]

Thanks for your response. Well the user is db_owner on the database I'm
working on as well as the tempdb. but still I'm getting the same error.

If I execute the SP for the Query analyzer or from crystal report it works
fine. Only in Pivot Table or more precisely Data Source Control it give me
this error. The SP work with even ADODC control on the form.

 

[rweide]

not sure now. Check DS Control's classID:

V09 -- 0002E530-0000-0000-C000-000000000046
V10 -- 0002E553-0000-0000-C000-000000000046
V11 -- 0002E55B-0000-0000-C000-000000000046

check connection string of the control. You may be using a general
login/password, not yours nor your users', that login ID should have the
permission to these tables.

 

[Bunty Singh]

Thanks for your response.

I'm using V11 -- 0002E55B-0000-0000-C000-000000000046 and it work absolutely
fine if I don't use temp table in the procedure. This implies that login is
OK.

Only when I try to use temp table in my SP I get the said error. Even if I
assume that the login do not have sufficient rights for temp table it should
not even run with Query Analyzer for the same login. But it work fine! So the
solution for this problem does not seems to be so apparent.