Openrow set to sp_crosstab stored procedure-- Plea

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)

Openrow set to sp_crosstab stored procedure-- Plea

Author

Topic

rjolly1971
Starting Member

3 Posts

Posted - 2008-12-01 : 12:38:16

Hello everyone.

This is my first time posting and I am hoping to get some assistance as I normally try to research the forums before I post. However it appears that I have run out of options so I will try to be as descriptive as possible. I am trying to run an openrowset command for a stored procedure call sp_crosstab on my local machine. As a reference the sp_crosstab, comes from the following article. Just so that you understand why I am doing this, although this sp solution is excellent one cannot execute stored procedures within a view. Therefore I am trying to use an openrowset function as an alternative solution.

Ultimately I am trying to create a faux-view of a crosstab showing hours over time dynamically by project, function, and skillset.

To do this, I am dong an openrowset back to the same localhost and database. Every time I try to execute my statement unfortunately, I receive the following errors and for the life of me I don't understand why:

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

What really confuses me is the fact that it is saying that the linked server is "(null)". Why????

I am currently using MS Server 2005 Developers edition as I am trying to do some proof of concept before I move on the next phase. I have also enabled 'Ad hoc remote queries' via the Surface Area Configuration. Additionally for security I am also using 'SQL Server and Windows Authentication Mode'. The execution statement is as follows:

Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'SERVER=localhost; Database=MCX; Trusted_Connection=Yes;', 'SET FMTONLY OFF;Execute sp_crosstab ''SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM Program_Requirements_Hours_View'',NULL, NULL, ''PERIOD'', ''HOURS'', ''SUM''')

Now I have tried different configurations of this statement. I have changed 'localhost' to the actual name of my machine and I have even changed the connection string to specify the userID and Pwd. When I do this I receive the exact same error. Additionally I have even tried adding the following before the statement based on previous forum postings:

USE Master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'SERVER=BLULAB813708; Database=MCX; Trusted_Connection=Yes;', 'SET FMTONLY OFF;Execute sp_crosstab ''SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM Program_Requirements_Hours_View'',NULL, NULL, ''PERIOD'', ''HOURS'', ''SUM''')

Again a no go with the following error:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)".

If anyone could help me on this problem, I would greatly appreciate it. Again, I've tried to be as descriptive as possible so I hope that I am providing the key information.

Thanks.

Shamil
Starting Member

5 Posts

Posted - 2008-12-01 : 14:10:51

Using my example for description connection string:
Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'Server=BLULAB81370;Trusted_Connection=yes', 'SET FMTONLY OFF;Execute sp_crosstab ''SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM MCX.dbo.Program_Requirements_Hours_View'',NULL, NULL, ''PERIOD'', ''HOURS'', ''SUM''')
Hope this helps.

rjolly1971
Starting Member

3 Posts

Posted - 2008-12-01 : 14:32:42

quote:
Originally posted by Shamil

Using my example for description connection string:
Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'Server=BLULAB81370;Trusted_Connection=yes', 'SET FMTONLY OFF;Execute sp_crosstab ''SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM MCX.dbo.Program_Requirements_Hours_View'',NULL, NULL, ''PERIOD'', ''HOURS'', ''SUM''')
Hope this helps.

Shamil,

Thank you for the reply. I made modifications as you recommended with:

Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'SERVER=BLULAB813708; Trusted_Connection=Yes', 'SET FMTONLY OFF;Execute sp_crosstab "SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM MCX.dbo.Program_Requirements_Hours_View",NULL, NULL, "PERIOD", "HOURS", "SUM"')

and I am still receiving the following:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".
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)".

Any additional ideas? Is there anything else besides Adhoc Remote queries that I need to turn on? I am thinking that there is some small item that I am missing here.

Thanks again.

Shamil
Starting Member

5 Posts

Posted - 2008-12-01 : 14:47:06

do it like this:
Select * into #tbltemp FROM
OPENROWSET('SQLNCLI', 'SERVER=BLULAB813708; Trusted_Connection=Yes', 'SET FMTONLY OFF;Execute MCX.dbo.sp_crosstab "SELECT Program_Code, FunctionCode, SkillCode, Hours, Period
FROM MCX.dbo.Program_Requirements_Hours_View",NULL, NULL, "PERIOD", "HOURS", "SUM"')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources