join SP dynamic resultset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join SP dynamic resultset

Author

Topic

ThePrisoner
Starting Member

18 Posts

Posted - 2007-06-29 : 09:02:58

hello

I am trying to get around SSRS 2005 limitations , only one dataset can be used in a table by getting all the fields I need into one table.

I use a stored procedure sp_crosstab (found on the web), to create a dynamic crosstab resulset.
I want to join the resultset of this SP to another resultset.
I can't use a temp table to store the reslts of the SP because the number of columns can vary
is there a solution or am I stuck ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-29 : 09:09:15

Check this thread: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85347[/url] for creating temp table from SP result without knowing columns in advance.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ThePrisoner
Starting Member

18 Posts

Posted - 2007-06-29 : 09:50:54

thanks

but I get error
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)".

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-29 : 09:59:09

What is your OPENROWSET() statement?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ThePrisoner
Starting Member

18 Posts

Posted - 2007-07-02 : 03:08:36

sorry I didn't see your reply
my sql statement is

Select * into #temp
from OPENROWSET
(
'SQLNCLI','Server=bruwpadewouters;Database=mydatabase;','dbo.usp_GetProductCrossTab'
)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-02 : 03:24:06

You are not providing any login credentials in your OPENROWSET() invocation. Either provide use name & password if using SQL authentication or use trusted authentication. See link I posted.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ThePrisoner
Starting Member

18 Posts

Posted - 2007-07-02 : 03:38:49

I solved it by adding SET FMTONLY OFF;

SELECT * into #tbltemp
FROM OPENROWSET('SQLNCLI', 'Server=bruwpadewouters;Trusted_Connection=yes;',
'SET FMTONLY OFF;EXEC master.dbo.sp_readerrorlog')

thanks for your help harsh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources