Connecting to another SQL Server

I think I have done my homework and research on this. I'm in a company
Intranet and through Enterprise Manager, I have added the server I want to
access (FES89TXNCEV501) in my server group. I can access the that server and
open a view on that server (read only).
I want to write a query (see below) on another server (MY Server) and access
FES89TXNCEV501 to pull data to insert into My Server. I get an error telling
me the server doesn't exist or access denied. I tried;
EXEC sp_addlinkedserver
'FES89TXNCEV501',
N'SQL Server'
And my query is
SELECT TOP 1000 dbo.vwNiceEvaluationHeaders.dtDateCreated AS DateCreated
,
dbo.vwNiceUserInfo.vcLoginName AS Assoc_CorpID,
dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.
vwNiceUserInfo.vcFirstName AS Associate, dbo.vwNiceEvaluationHeaders.fltScor
e
AS Score,
vwNiceUserInfo_1.vcLastName AS Evaluator,
vwNiceUserInfo_1.vcLoginName AS Eval_CorpID,
dbo.vwNiceFormDefinitionHeaders.vcFormName AS FormName
FROM OPENDATASOURCE ('SQLOLEDB',
'Data Source=FES89TXNCEV501;User
ID=bubba;Password=whatever;' ).Nice_Cls.dbo.vwNiceFormDefinitionHeaders
Rowset_1 INNER JOIN
dbo.vwNiceEvaluationHeaders ON dbo.
vwNiceFormDefinitionHeaders.iFormID = dbo.vwNiceEvaluationHeaders.iFormID
INNER JOIN
dbo.vwNiceUserInfo ON dbo.vwNiceEvaluationHeaders.
iAgentUserID = dbo.vwNiceUserInfo.iUserID INNER JOIN
dbo.vwNiceUserInfo vwNiceUserInfo_1 ON dbo.
vwNiceEvaluationHeaders.iEvaluatorUserID = vwNiceUserInfo_1.iUserID
WHERE (dbo.vwNiceEvaluationHeaders.dtDateCreated >= CONVERT(DATETIME,
'2006-08-07 00:00:00', 102))
GROUP BY dbo.vwNiceUserInfo.vcLoginName, dbo.vwNiceEvaluationHeaders.fltScor
e,
dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.vwNiceUserInfo.vcFirstName,
dbo.vwNiceEvaluationHeaders.dtDateCreated,
vwNiceUserInfo_1.vcLastName, vwNiceUserInfo_1.vcLoginName,
dbo.vwNiceFormDefinitionHeaders.vcFormName
ANy help is greatly appreciated!!!
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...tivity/200608/1I'm not sure if it is causing your problem, but I don't think that you use
OPENDATASOURCE on a linked server .
You might try removing the linked server (using sp_dropserver), and be sure
that that is not 'gumming' up the works.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Chamark via droptable.com" <u21870@.uwe> wrote in message
news:646ddd01cfdb8@.uwe...
>I think I have done my homework and research on this. I'm in a company
> Intranet and through Enterprise Manager, I have added the server I want to
> access (FES89TXNCEV501) in my server group. I can access the that server
> and
> open a view on that server (read only).
> I want to write a query (see below) on another server (MY Server) and
> access
> FES89TXNCEV501 to pull data to insert into My Server. I get an error
> telling
> me the server doesn't exist or access denied. I tried;
> EXEC sp_addlinkedserver
> 'FES89TXNCEV501',
> N'SQL Server'
> And my query is
>
> SELECT TOP 1000 dbo.vwNiceEvaluationHeaders.dtDateCreated AS
> DateCreated,
> dbo.vwNiceUserInfo.vcLoginName AS Assoc_CorpID,
> dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.
> vwNiceUserInfo.vcFirstName AS Associate,
> dbo.vwNiceEvaluationHeaders.fltScore
> AS Score,
> vwNiceUserInfo_1.vcLastName AS Evaluator,
> vwNiceUserInfo_1.vcLoginName AS Eval_CorpID,
> dbo.vwNiceFormDefinitionHeaders.vcFormName AS
> FormName
> FROM OPENDATASOURCE ('SQLOLEDB',
> 'Data Source=FES89TXNCEV501;User
> ID=bubba;Password=whatever;' ).Nice_Cls.dbo.vwNiceFormDefinitionHeaders
> Rowset_1 INNER JOIN
> dbo.vwNiceEvaluationHeaders ON dbo.
> vwNiceFormDefinitionHeaders.iFormID = dbo.vwNiceEvaluationHeaders.iFormID
> INNER JOIN
> dbo.vwNiceUserInfo ON dbo.vwNiceEvaluationHeaders.
> iAgentUserID = dbo.vwNiceUserInfo.iUserID INNER JOIN
> dbo.vwNiceUserInfo vwNiceUserInfo_1 ON dbo.
> vwNiceEvaluationHeaders.iEvaluatorUserID = vwNiceUserInfo_1.iUserID
> WHERE (dbo.vwNiceEvaluationHeaders.dtDateCreated >= CONVERT(DATETIME,
> '2006-08-07 00:00:00', 102))
> GROUP BY dbo.vwNiceUserInfo.vcLoginName,
> dbo.vwNiceEvaluationHeaders.fltScore,
> dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.vwNiceUserInfo.vcFirstName,
> dbo.vwNiceEvaluationHeaders.dtDateCreated,
> vwNiceUserInfo_1.vcLastName, vwNiceUserInfo_1.vcLoginName,
> dbo.vwNiceFormDefinitionHeaders.vcFormName
> ANy help is greatly appreciated!!!
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...tivity/200608/1
>|||Having a linked server and using Opendatasource seems a bit
contradictory. Opendatasource is used for ad hoc access when
you aren't going to use linked servers - just need a one
time connect type of thing. However, just having the linked
server defined shouldn't cause a problem as the API just
uses the connection info you provide in the Opendatasource
command and doesn't really have any knowledge of what linked
servers you have.
If you are sure you want to use Opendatasource, Have you
tried just a simple one table select using Opendatasource as
a test?
-Sue
On Mon, 07 Aug 2006 20:54:04 GMT, "Chamark via
droptable.com" <u21870@.uwe> wrote:

>I think I have done my homework and research on this. I'm in a company
>Intranet and through Enterprise Manager, I have added the server I want to
>access (FES89TXNCEV501) in my server group. I can access the that server an
d
>open a view on that server (read only).
>I want to write a query (see below) on another server (MY Server) and acces
s
>FES89TXNCEV501 to pull data to insert into My Server. I get an error tellin
g
>me the server doesn't exist or access denied. I tried;
>EXEC sp_addlinkedserver
> 'FES89TXNCEV501',
> N'SQL Server'
>And my query is
>
>SELECT TOP 1000 dbo.vwNiceEvaluationHeaders.dtDateCreated AS DateCreate
d,
>dbo.vwNiceUserInfo.vcLoginName AS Assoc_CorpID,
> dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.
>vwNiceUserInfo.vcFirstName AS Associate, dbo.vwNiceEvaluationHeaders.fltSco
re
>AS Score,
> vwNiceUserInfo_1.vcLastName AS Evaluator,
>vwNiceUserInfo_1.vcLoginName AS Eval_CorpID,
> dbo.vwNiceFormDefinitionHeaders.vcFormName AS FormNam
e
>FROM OPENDATASOURCE ('SQLOLEDB',
> 'Data Source=FES89TXNCEV501;User
>ID=bubba;Password=whatever;' ).Nice_Cls.dbo.vwNiceFormDefinitionHeaders
>Rowset_1 INNER JOIN
> dbo.vwNiceEvaluationHeaders ON dbo.
>vwNiceFormDefinitionHeaders.iFormID = dbo.vwNiceEvaluationHeaders.iFormID
>INNER JOIN
> dbo.vwNiceUserInfo ON dbo.vwNiceEvaluationHeaders.
>iAgentUserID = dbo.vwNiceUserInfo.iUserID INNER JOIN
> dbo.vwNiceUserInfo vwNiceUserInfo_1 ON dbo.
>vwNiceEvaluationHeaders.iEvaluatorUserID = vwNiceUserInfo_1.iUserID
>WHERE (dbo.vwNiceEvaluationHeaders.dtDateCreated >= CONVERT(DATETIME,
>'2006-08-07 00:00:00', 102))
>GROUP BY dbo.vwNiceUserInfo.vcLoginName, dbo.vwNiceEvaluationHeaders.fltSco
re,
>dbo.vwNiceUserInfo.vcLastName + ' ' + dbo.vwNiceUserInfo.vcFirstName,
> dbo.vwNiceEvaluationHeaders.dtDateCreated,
>vwNiceUserInfo_1.vcLastName, vwNiceUserInfo_1.vcLoginName,
> dbo.vwNiceFormDefinitionHeaders.vcFormName
>ANy help is greatly appreciated!!!|||If you have a linked server the from for the select is ...
FROM LinkedServerName..TableName
"Sue Hoegemeier" wrote:

> Having a linked server and using Opendatasource seems a bit
> contradictory. Opendatasource is used for ad hoc access when
> you aren't going to use linked servers - just need a one
> time connect type of thing. However, just having the linked
> server defined shouldn't cause a problem as the API just
> uses the connection info you provide in the Opendatasource
> command and doesn't really have any knowledge of what linked
> servers you have.
> If you are sure you want to use Opendatasource, Have you
> tried just a simple one table select using Opendatasource as
> a test?
> -Sue
> On Mon, 07 Aug 2006 20:54:04 GMT, "Chamark via
> droptable.com" <u21870@.uwe> wrote:
>
>|||Oops.. one more dot
FROM LinkedServerName...TableName
"Chris" wrote:
[vbcol=seagreen]
> If you have a linked server the from for the select is ...
> FROM LinkedServerName..TableName
> "Sue Hoegemeier" wrote:
>