In the first step of what will build up to include several temp tables and Stored Procs being called and then Unioned, I have the first baby steps (based on the answer from Philip Kelley here):
IF OBJECT_ID ( 'RockBottomAmalgamated', 'P' ) IS NOT NULL
DROP PROCEDURE RockBottomAmalgamated;
GO
CREATE PROC [dbo].[RockBottomAmalgamated]
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
AS
CREATE TABLE #BaseCraftworksTable
(
Unit varchar(25) not null
,ShortName varchar(50)
,ItemCode varchar(25) not null
,Description varchar(100) not null
,Price varchar(25)
,Variance varchar(25)
,VarianceAverage varchar(10)
,PriceWeek varchar(50)
,Week varchar(10) not null
)
INSERT INTO #BaseCraftworksTable (Unit, ShortName, ItemCode, Description, Price,
Variance, VarianceAverage, PriceWeek, Week)
EXECUTE sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks
@BegDate = @BegDate
,@EndDate = @EndDate
,@SortBy = @SortBy
SELECT * FROM #BaseCraftworksTable
...but calling this SP (RockBottomAmalgamated) returns nothing; yet calling "sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks" does return records. Why would that be, as so far this is simply stuffing some of the data returned from sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks into #BaseCraftworksTable, which is then being returning its contents?
UPDATE
On trying to use the "FROM OPENROWSET()" jazz with this code:
IF OBJECT_ID ( 'RockBottomAmalgamated', 'P' ) IS NOT NULL
DROP PROCEDURE RockBottomAmalgamated;
GO
CREATE PROC [dbo].[RockBottomAmalgamated]
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
AS
CREATE TABLE #BaseCraftworksTable
(
Unit varchar(25)
,ShortName varchar(50)
,ItemCode varchar(25)
,Description varchar(100)
,Price varchar(25)
,Variance varchar(25)
,VarianceAverage varchar(10)
,PriceWeek varchar(50)
,Week varchar(10)
)
SELECT * INTO #BaseCraftworksTable FROM OPENROWSET(
'SQLNCLI', 'Server=PLATYPUSQL42.za.ABCData;Trusted_Connection=yes;',
'EXEC sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks
@BegDate = @BegDate
,@EndDate = @EndDate
,@SortBy = @SortBy');
SELECT * FROM #BaseCraftworksTable
...I got an err msg about "BaseCraftworksTable" already existing. So I added this just before the "CREATE TABLE":
IF OBJECT_ID('#BaseCraftworksTable', 'U') IS NOT NULL
DROP TABLE #BaseCraftworksTable;
...but it didn't help.
So I commented out the CREATE TABLE portion; that did away with the previous error, but invoked these in its stead:
Error 53: Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
The connection value provided ("PLATYPUSQL42.za.ABCData") is exactly what I selected from the "Connection" dropdown in LINQPad (with "SQL" chosen from the Language dropdown); it works in LINQPad; what is the OPENROWSET call expecting as its second parameter?
UPDATE 2
I'm obviously not a SQL guru, but it surprised me that I could comment out my CREATE TABLE statement altogether, re-create the SP, run it, and there be no err msg about a nonexistent table. So I assume that if the table has not been defined, it is created automatically based on what's within the params in the INSERT INTO () clause. Makes sense/nice feature; but that being the case, what would be the advantage of explicitly defining the table?
NOTE: I still get no results, though; I tried that thinking maybe the problem was with how I was defining one or more of the fields in the table. But no, there's something other than that causing the problem[s]...
