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

Transact-SQL (2005)

SQL Linked Server issue

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-17 : 02:47:29

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-17 : 09:13:39

Dups:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112755

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources