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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-18 : 23:12:35
|
Jeff writes "My question regards a problem I am having using Access 2000 projects to execute a SQL stored procedure.
I often use temp tables to allow me to run queries on complex recordsets. My procedures run fine on the SQL server, but for some reason, Access doesn't like SP's that have more than one EXECUTE statement in them. It will run the SP, but no records are returned. Here is the code for my SP:
CREATE PROCEDURE proProfitBySalesRepSUB
@week1 datetime,
@week2 datetime,
@week3 datetime,
@week4 datetime,
@tblFinalSUB varchar(116) OUTPUT
AS
DECLARE @tblStopSUB varchar(116)
DECLARE @tblStop varchar(116)
DECLARE @tblRoute varchar(116)
DECLARE @tblProfit1 varchar(116)
DECLARE @tblProfit2 varchar(116)
DECLARE @strsql varchar(2048)
DECLARE @ext varchar(128)
DECLARE @curDate datetime
DECLARE @RunCode int
DECLARE @Range varchar(255)
SET @RunCode = 1
SET @curDate = {fn Now()}
WHILE @RunCode <3
BEGIN
/*Set Values*/
SET @ext = CAST(DATEPART(hh, @curDate) as varchar(2)) + CAST(DATEPART(n, @curDate) as varchar(2)) + CAST(DATEPART(s, @curDate) as varchar(2)) + @RunCode
IF @RunCode = 1
BEGIN
SET @Range = CAST(@week1 as varchar(12)) + ''' AND ''' + CAST(@week2 as varchar(12)) + ''''
END
ELSE
BEGIN
SET @Range = CAST(@week3 as varchar(12)) + ''' AND ''' + CAST(@week4 as varchar(12)) + ''''
END
/*Stop SUB*/
SET @tblStopSUB = '##tblStopSUB' + @ext SET @strsql = 'SELECT Salesman, CustomerCode, InvoiceDate, Route, SUM(Profit) AS StopProfit INTO ' + @tblStopSUB
SET @strsql = @strsql + ' FROM viwProfitBySalesRepSUB'
SET @strsql = @strsql + ' WHERE InvoiceDate Between ''' + @Range
SET @strsql = @strsql + ' GROUP BY Salesman, CustomerCode, InvoiceDate, Route'
EXECUTE (@strsql)
/*Stop*/
SET @tblStop = '##tblStop' + @ext
SET @strsql = 'SELECT Salesman, CustomerCode, SUM(StopProfit) / COUNT(InvoiceDate) AS StopProfitAvg INTO ' + @tblStop
SET @strsql = @strsql + ' FROM ' + @tblStopSUB + ' '
SET @strsql = @strsql + ' GROUP BY Salesman, CustomerCode'
EXECUTE (@strsql)
/*Route*/
SET @tblRoute = '##tblRoute' + @ext
SET @strsql = 'SELECT Salesman, DefaultSalesman, CustomerCode, DeliveryName, BillingName, SUM(Sales) AS TotalSales, SUM(Profit) AS GrossProfit'
SET @strsql = @strsql + ' INTO ' + @tblRoute
SET @strsql = @strsql + ' FROM viwProfitBySalesRepSUB'
SET @strsql = @strsql + ' WHERE InvoiceDate Between ''' + @Range
SET @strsql = @strsql + ' GROUP BY Salesman, CustomerCode, DeliveryName, BillingName, DefaultSalesman'
EXECUTE (@strsql)
/*Profit*/
IF @RunCode = 1
BEGIN
SET @tblProfit1 = '##tblProfitBySalesRep' + @ext
SET @strsql = 'SELECT Route.*, Stop.StopProfitAvg INTO ' + @tblProfit1
SET @strsql = @strsql + ' FROM ' + @tblRoute + ' Route INNER JOIN ' + @tblStop + ' Stop ON Route.Salesman = Stop.Salesman AND Route.CustomerCode = Stop.CustomerCode'
END
ELSE
BEGIN
SET @tblProfit2 = '##tblProfitBySalesRep' + @ext
SET @strsql = 'SELECT Route.*, Stop.StopProfitAvg INTO ' + @tblProfit2
SET @strsql = @strsql + ' FROM ' + @tblRoute + ' Route INNER JOIN ' + @tblStop + ' Stop ON Route.Salesman = Stop.Salesman AND Route.CustomerCode = Stop.CustomerCode'
END
EXECUTE(@strsql)
SET @RunCode = @RunCode + 1
END
etc........
I run this in SQL enterprise manager and get great results, but access won't take it. Wh |
|
|
|
|
|
|
|
Access 2000 and SQL Stored Procedures