November 11, 2002 at 11:33 am
I am trying to call a nested SP from an ASP page. However, the SP fails everytime when I call it through the ASP page. It runs fine in QA. The Error is 208. Here is the SP:
CREATE PROCEDURE VO_ImportRates @file varchar(100) , @count varchar(20) OUTPUT, @rrcount varchar(20) OUTPUT AS
SET NOCOUNT ON
EXEC dbo.VO_Import 'vo..importrates','D:\BillingProject\ImportRates\',@file
INSERT INTO dbo.ratehistory (Country, Planname, Rate, EffectiveDate, Round1, Round2, FName)
SELECT Country, Planname, Rate, EffectiveDate, Round1, Round2, @file
FROM dbo.importrates
INSERT INTO dbo.ReRate (Country, Planname, EffectiveDate, FName)
SELECT Country, Planname, EffectiveDate, @file
FROM dbo.importrates
SELECT @count=count(*)
FROM dbo.ImportRates
SELECT @rrcount=count(*)
FROM dbo.ReRate
TRUNCATE TABLE dbo.ImportRates
GO
Any ideas?
November 11, 2002 at 11:47 am
Not sure if it works but try this.
CREATE PROCEDURE VO_ImportRates @file varchar(100) , @count varchar(20) OUTPUT, @rrcount varchar(20) OUTPUT AS
SET NOCOUNT ON
BEGIN
EXEC dbo.VO_Import 'vo..importrates','D:\BillingProject\ImportRates\',@file
END
BEGIN
INSERT INTO dbo.ratehistory (Country, Planname, Rate, EffectiveDate, Round1, Round2, FName)
SELECT Country, Planname, Rate, EffectiveDate, Round1, Round2, @file
FROM dbo.importrates
END
BEGIN
INSERT INTO dbo.ReRate (Country, Planname, EffectiveDate, FName)
SELECT Country, Planname, EffectiveDate, @file
FROM dbo.importrates
END
BEGIN
SELECT @count=count(*)
FROM dbo.ImportRates
END
BEGIN
SELECT @rrcount=count(*)
FROM dbo.ReRate
END
TRUNCATE TABLE dbo.ImportRates
GO
November 11, 2002 at 11:54 am
No, Same issue.
Here is the Stored Procedure which is nested:
CREATE PROCEDURE VO_Import @table varchar(50), @location varchar(100), @file varchar(50) AS
SET NOCOUNT ON
DECLARE @strSQL varchar(400)
SET @strSQL='BULK INSERT ' + ltrim(rtrim(@table)) + ' FROM ' + CHAR(39) + ltrim(rtrim(@location)) + ltrim(rtrim(@file)) + CHAR(39) + ' WITH(FIRSTROW=2,FIELDTERMINATOR=' + CHAR(39) + ',' + CHAR(39) + ', ROWTERMINATOR='+ CHAR(39) +'\n'+ CHAR(39) +')'
--'D:\BillingProject\rateamend_p\WTC_23-9-2002.csv'
EXEC(@strSQL)
GO
November 11, 2002 at 12:23 pm
Is it working fine if you are executing the stored procedure form Query analyser
November 11, 2002 at 12:27 pm
Yes, through QA it works perfectly. It seems to have some sort of permissions issue. That is what error 208 denotes. I have all of the tables prefixed with 'dbo'. I have run all sorts of Stored Procedures through the APS page. This is the first to give me any grief
November 11, 2002 at 4:05 pm
How are you logging into Query Analyser. This could be that if you are logging into using a domain user then your user account is being impersonated.
How is your asp site connecting?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 11, 2002 at 4:30 pm
Can you post the full message text and please post the asp code you use to call this (please alter any security items for safety)? Has to be the way you are calling the SP from the ASP application that is causing it.
November 12, 2002 at 7:57 am
The issue is solved. I was using somebody else's code(ASP upload software) in the ASP page and I hadn't noticed that they did not include the adovbs.inc file. It works perfectly now. Thanks for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply