December 1, 2008 at 9:42 am
Hello everyone.
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.
December 1, 2008 at 11:27 am
Personally, I'd suggest learning how to use CASE statements or PIVOT to do this instead of using that stored procedure. A good article is at:
http://www.sqlservercentral.com/articles/T-SQL/63681/
Trying out the stored procedure you referenced, I've found a PIVOT or CASE to work 4 times faster, and you won't have the overhead of an extra connection or temporary tables.
December 1, 2008 at 12:13 pm
Chris Harshman (12/1/2008)
Personally, I'd suggest learning how to use CASE statements or PIVOT to do this instead of using that stored procedure. A good article is at:http://www.sqlservercentral.com/articles/T-SQL/63681/
Trying out the stored procedure you referenced, I've found a PIVOT or CASE to work 4 times faster, and you won't have the overhead of an extra connection or temporary tables.
Chris,
I considered this option at first but the big problem with this route is that one needs to hard code what the columns are expected to be ahead of time. In a dynamic situation where time consistently moves forward and where I want to see the present month (Time 0) through the next 24 months (Time 0 + 24, how would you administrate this? Between constantly having to update the SQL statement or using the stored procedure, I really think that I would want to use the stored procedure. This has been the big problem with PIVOT and Case for that matter. Am I missing something here? When you did your Pivot or Case Statement did you have to address a moving column target such as time? If so, how did you deal with this problem?
Thanks
December 1, 2008 at 1:06 pm
OK, sorry for the confusion, I didn't realize you wouldn't know the desired pivoting values. I can think of two options offhand, either use ROW_NUMBER() to force the pivoting values into a list of known values (0 through 24), or you can use dynamic SQL to produce the list of values to pivot on at runtime:
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
December 1, 2008 at 2:11 pm
Chris,
After literally days (and I am not exaggerating unfortunately) of doing searches on the web, this blog has appeared to slip through the cracks. I will need to read more into this but you may have indeed given me another option. Thanks for the link and I will keep you updated on my progress.
Thanks,
Ray
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply