May 4, 2011 at 12:58 am
Hi I have SP used to run report on intranet.
And rhis is the SP
USE [Report]
GO
/****** Object: StoredProcedure [dbo].[report_futura_price_overrides_china] Script Date: 05/04/2011 10:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[report_futura_price_overrides_china]
@BranchFrom varchar(5),
@BranchTo varchar(5),
@StartDate varchar(50),
@EndDate varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT
TABLE_REASON.BRANCH,
TABLE_REASON.DATE_,
TABLE_REASON.TIME_,
TABLE_REASON.RECEIPT,
TABLE_REASON.TILL,
TABLE_REF.REFERENCE,
TABLE_REF.QUANTITY,
CAST (cast(TABLE_REF.RT as integer) AS DECIMAL) / 100,
CAST (cast(TABLE_REF.DISCOUNT as integer) AS DECIMAL) / 100,
CAST (cast(TABLE_REF.SP as integer) AS DECIMAL) / 100,
TABLE_REASON.REASON_CODE,
TABLE_REASON.REASON_DESC,
TABLE_REF.PERSONNEL,
TABLE_REF.PER_NAME
FROM
(select
KAS_FILIALE AS BRANCH,
KAS_BERICHT as SOMETHING,
KAS_KASSE as TILL,
KAS_BONNR AS RECEIPT,
KAS_POSNR AS POSITION,
KAS_ZEIT as ID,
RIGHT (KAS_INFO,7) AS PRICE,
left(KAS_INFO,3) AS REASON_CODE,
PAG_TEXT AS REASON_DESC,
convert(varchar,(CONVERT(datetime, CAST( KAS_VK_DATUM AS CHAR(8)), 103)),103) AS DATE_,
case when len(KAS_ZEIT) = 5 then
left(right (KAS_ZEIT,6),1)+'.'+left(right (KAS_ZEIT,4),2)
else
left(right (KAS_ZEIT,6),2)+'.'+left(right (KAS_ZEIT,4),2) end AS TIME_
-- KAS_SATZART AS TRANS_NUM,
-- 'INFO - DISCOUNT' TRANS_TYPE
FROM
---APP06.FUTURA_CHINA.dbo.V_KASSTRNS
FUTURA_CHINA.dbo.V_KASSTRNS
--LEFT JOIN APP06.FUTURA_CHINA.dbo.PA_GRUND ON PAG_MANDANT = KAS_MANDANT
LEFT JOIN FUTURA_CHINA.dbo.PA_GRUND ON PAG_MANDANT = KAS_MANDANT
AND left(KAS_INFO,3) = PAG_NUMMER
WHERE 1=1
AND KAS_SATZART IN (17)
AND KAS_MANDANT IN (51)
AND KAS_BETRAG = 0
AND KAS_ANZAHL = 5
-- DATES ARE HERE
AND KAS_VK_DATUM between @StartDate and @EndDate
)
AS TABLE_REASON,
(select
KAS_RETOUR as RETURN_,
left ( cast ((KAS_BETRAG*1000 ) as integer) ,
len(cast ((KAS_BETRAG*1000 ) as integer)) - 1 )
as SP,
left ( cast(right(KAS_INFO,6)as integer) ,
len(cast(right(KAS_INFO,6)as integer)) - 1 )
as RT,
left ( cast ((KAS_BETRAG*1000 ) as integer) - cast(right(KAS_INFO,6)as integer) ,
len(cast ((KAS_BETRAG*1000 ) as integer) - cast(right(KAS_INFO,6)as integer)) - 1 ) as 'DISCOUNT',
KAS_FILIALE as BRANCH,
KAS_BERICHT as SOMETHING,
KAS_KASSE as TILL,
KAS_BONNR as RECEIPT,
KAS_POSNR as POSITION,
KAS_ZEIT as ID,
KAS_REFNUMMER as REFERENCE,
KAS_ANZAHL as QUANTITY,
PER_NUMMER as PERSONNEL,
PER_INDEX as PER_NAME,
PER_FILIALE as PER_BRANCH,
PER_RABATT_1 as PER_DISCOUNT,
PER_ISTRAB_WERT_1 as PESONNEL_NOT_SURE,
PER_ISTRAB_DATUM_1 as PESONNEL_NOT_SURE_2,
convert(varchar,(CONVERT(datetime, CAST( KAS_VK_DATUM AS CHAR(8)), 103)),103) AS DATE_,
case when len(KAS_ZEIT) = 5 then
left(right (KAS_ZEIT,6),1)+'.'+left(right (KAS_ZEIT,4),2)
else
left(right (KAS_ZEIT,6),2)+'.'+left(right (KAS_ZEIT,4),2) end AS TIME_
from
--APP06.FUTURA_CHINA.dbo.V_KASSTRNS
FUTURA_CHINA.dbo.V_KASSTRNS
---LEFT JOIN APP06.FUTURA_CHINA.dbo.V_PERSONAL ON PER_MANDANT = KAS_MANDANT
LEFT JOIN FUTURA_CHINA.dbo.V_PERSONAL ON PER_MANDANT = KAS_MANDANT
and left(KAS_INFO,6) = PER_NUMMER
where 1=1
AND KAS_SATZART IN (15)
AND KAS_MANDANT IN (51)
AND KAS_VK_DATUM between @StartDate and @EndDate
--AND KAS_FILIALE = 2305
--and cast ((KAS_BETRAG*1000 ) as integer) - cast(right(KAS_INFO,6)as integer) <> 0
and cast ((KAS_BETRAG*1000 ) as varchar) <> replace ((right(KAS_INFO,6)+'.00'), ' ','')
)
AS TABLE_REF
WHERE TABLE_REF.BRANCH = TABLE_REASON.BRANCH
and TABLE_REF.SOMETHING = TABLE_REASON.SOMETHING
and TABLE_REF.TILL = TABLE_REASON.TILL
and TABLE_REF.RECEIPT = TABLE_REASON.RECEIPT
and RETURN_ = 0
--and (REASON_CODE = ' 6' or REASON_CODE = ' 11')
and ( (TABLE_REASON.POSITION - TABLE_REF.POSITION = 1 )
OR
( TABLE_REASON.POSITION - TABLE_REF.POSITION <> 1 and TABLE_REASON.POSITION - TABLE_REF.POSITION = 2))
and TABLE_REASON.BRANCH between @BranchFrom and @BranchTo
END
Acutally we have report database and China database in server called sql02. Now recently moved the China database to different server called APP06. I have changed the APP06.FUTURA_CHINA to FUTURA_CHINA.
This Report databse has user as report and ihave created Report databse saved this query in new server APP06 and created this sp user as report and mapped FUTURA_CHINA databse to report user as db_datareader
and report databse to report user as db_owner.
When I run this report in intranet it throws an error
The OLE DB provider "SQLNCLI" for linked server "APP06" does not contain the table ""FUTURA_CHINA"."dbo"."V_KASSTRNS"". The table either does not exist or the current user does not have permissions on that table.
PLease guide me what I should be doing to fix this error.\I dont know how to map local user to user on linked server and also CREATE script for the current linked server and verify against the old one.
Please advise me. I am trying to fix this from 2 days and now am really ou of time.
Thank you Very much.
Thanks in advance.
May 4, 2011 at 1:28 pm
Let me start by asking a few questions
Does the table V_KASSTRNS exist in the FUTURA_CHINA database on the APP06 server?
It is prefaced with a V, does that mean it is a view?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 4, 2011 at 2:13 pm
I'm guessing toward the same direction as Stefan:
Assuming V_KASSTRNS and V_PERSONAL are views, both could have the hardcoded APP06 linked server referenced in the underlying query.
I'd check the definition of those views(?).
To find the one causing the issue, run
SELECT TOP 1 FROM V_KASSTRNS
and
SELECT TOP 1 FROM V_PERSONAL
and check which one fails.
May 4, 2011 at 4:35 pm
Hi
Yes they are in futura_China database.
May 4, 2011 at 4:48 pm
Does the sproc return any error when run from SSMS (Management Studio)?
If so, what tool do you use to "run (and design) the report in intranet"? Assuming it is SSRS (Reporting Services) there might be an issue with the connection string in the report itself pointing to a wrong data source.
Or it is "just" what the error message tells: the user has no permission to run this report. If possible, try to run the sproc from the SSRS in design mode using the same login as used on the intranet.
May 4, 2011 at 5:31 pm
kcheluvaraj 94771 (5/4/2011)
HiYes they are in futura_China database.
Are they views? If they're views it is possible that they are still pointing at tables on the old server. Look at the design and see what the structure is. Also, try to open the views directly and see what happens.
Is the stored procedure that you're running running on the APP06 server? Is it running in the futura_China database?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 4, 2011 at 6:12 pm
The next thing I'd try is to run it with a user mapped as database owner on futura_china. If that works, then the error is somewhere in your user permissions. If it still doesn't work, we'll have to keep looking elsewhere. If it does work, you can remove levels of permission until it stops working again and you'll know what level you need.
The problem I'm having here is why you're getting this error. You're not referencing APP06 from this stored procedure. You're running this on APP06, right? Have you tried changing it from a stored procedure to a regular query and running it that way?
You've run the ALTER PROCEDURE to make sure it has the APP06 lines commented out, right?
Sorry to be running over the simple things, but that's what I'd be checking if it werre on my machines.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply