SQL Linked Server issue - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Linked Server issue

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-17 : 03:31:50

Hello All,
I have the following SP

ALTER PROCEDURE [dbo].[cpucalc]
@amapid int,
@metric_name varchar(max),
@wsStart datetime,
@wsEnd datetime
--@adj_val float output,
--@measuredval float output

as
DECLARE @startDate DateTime;
Declare @endDate DateTime;
declare @total_instance int;
declare @outliers int;
declare @solsubsetlibid int;
declare @adj_val float;
begin
exec sp_addlinkedserver [monitor1\whatsup]

SET @startDate = CONVERT(varchar(10), @wsStart, 101);
SET @endDate = CONVERT(varchar(10), @wsEnd, 101);
--set @sql_str =

SELECT @total_instance = COUNT(*)
FROM [monitor1\whatsup].whatsup.dbo.perfmonresultsbypolltime AS wug INNER JOIN
metric_by_compname ON wug.monitorname = metric_by_compname.metric_Name
WHERE (wug.dpolldate >= @startdate) AND (wug.dpolldate <= @enddate)
GROUP BY wug.monitorname, metric_by_compname.Actionmap_ID
HAVING (wug.monitorname = @metric_name) AND (metric_by_compname.Actionmap_ID = @amapid)

SELECT @outliers = COUNT(*)
FROM [monitor1\whatsup].whatsup.dbo.perfmonresultsbypolltime AS wug INNER JOIN
metric_by_compname ON wug.monitorname = metric_by_compname.metric_Name
WHERE (wug.dpolldate >= @startdate) AND (wug.dpolldate <= @enddate)and (nvalue_min < llimit OR
nvalue_max > ulimit)
GROUP BY wug.monitorname, metric_by_compname.Actionmap_ID
HAVING (wug.monitorname = @metric_name) AND (metric_by_compname.Actionmap_ID = @amapid)

set @adj_val = (@outliers*100)/@total_instance

--set @measuredval = @outliers
--print @total_instance

UPDATE Metrics_Table
SET Period_Start = @startdate, Period_End = @enddate, metric_outputval = @outliers,
actual_perf =
CASE WHEN (perf_weight is not null) THEN (@outliers*100)/@total_instance END,
actual_rel =
CASE WHEN (rel_weight is not null) THEN (@outliers*100)/@total_instance END,
actual_sec =
CASE WHEN (sec_weight is not null) THEN (@outliers*100)/@total_instance END

WHERE (metric_Name = @metric_name) AND (amapid = @amapid)

SELECT @solsubsetlibid = Solsubset_libID
FROM Metrics_Table
WHERE (metric_Name = @metric_name) AND (amapid = @amapid)

exec update_subset @amapid,@solsubsetlibid

END

This works fine when I run from the Management Studio, but when I call this from my .net app, I get the following error

The OLE DB provider "SQLNCLI" for linked server "monitor1\whatsup" does not contain the table ""whatsup"."dbo"."perfmonresultsbypolltime"". The table either does not exist or the current user does not have permissions on that table.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The OLE DB provider "SQLNCLI" for linked server "monitor1\whatsup" does not contain the table ""whatsup"."dbo"."perfmonresultsbypolltime"". The table either does not exist or the current user does not have permissions on that table.

Can anyone help please, I need to fix this ASAP.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-17 : 07:42:13

sorry to state the obvious....but does the table exist AND do you have access to it?

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-17 : 10:29:41

Yes the table exists and I have access to it. The question is does the user running the app has the access to it. Here is what I have found, the server where the main SQL database exists has a aspnet user account, but the server to which I am linking to does not have a aspnet user account. I am thinking this account is created when asp is installed on the server. Now should I install asp on the server where the database exists, so an aspnet user account is created.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-17 : 11:23:38

users should never ahve direct access to tables.
they shopuld have access to esecute SP's via a role...and the SP's have access to the database.

have a look at the account which is used by the "connectedto" server to process the request from the "linked server"....does this account (and it is possibly a generic account) have access to the SP or the database/table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources