Remote server Error...

Hi there. I'm getting the following error when trying to run a query on a
remote server I've set up:
Server 'SERVER_1' is not configured for DATA ACCESS.
I know this option is available for Linked Servers but I dont know why I'm
seeing this problem with a remote server. The Remote Server I've created has
RPC enabled ans is using a valid server name and login.
Any ideas?...Try executing:
exec sp_serveroption 'Server_1', 'data access', 'true'
-Sue
On Wed, 30 Mar 2005 09:49:03 -0800, len
<len@.discussions.microsoft.com> wrote:

>Hi there. I'm getting the following error when trying to run a query on a
>remote server I've set up:
>Server 'SERVER_1' is not configured for DATA ACCESS.
>I know this option is available for Linked Servers but I dont know why I'm
>seeing this problem with a remote server. The Remote Server I've created ha
s
>RPC enabled ans is using a valid server name and login.
>Any ideas?...

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote ForXML and XML Result

I created a sp_addlinkedserver to SQL 2005. I sent a
Remote For XML Request through a SP on SQL2005. However, I
now remember SQL Server does not send XML result back
because I found out what I got was a bunch
of "0x4409530068006900700070006500720049004400440B430 06F006
D00700..."
What options do I have?
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:977101c478b7$e183dc40$a601280a@.phx.gbl...
[snip]
> What options do I have?
Put some kind of client in between.
Bryant
|||Bryant is right - SQL 2000 compatible FOR XML (top level with no TYPE
option) does not work directly between linked servers with either SQL 2000
or SQL 2005.
However, XML data type or NVARCHAR(MAX) will be fine when sent from a SQL
2005 linked server.
So, try using
FOR XML ..., TYPE --results in XML
or wrap SELECT ...FOR XML... into another SELECT:
SELECT (SELECT ... FOR XML...) -- results in NVARCHAR(MAX)
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:etFf6gMeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> "C TO" <anonymous@.discussions.microsoft.com> wrote in message
> news:977101c478b7$e183dc40$a601280a@.phx.gbl...
> [snip]
> Put some kind of client in between.
> --
> Bryant

Remote Connection to Analysis Services using VPN

Given that MS Analysis Services does not support standard SQL Server
Authentication (thus requiring windows user accounts), is it possible for a
remote client PC, connected to a network using a VPN, to be authenticated on
an Analysis Server if it is not part of the host domain? If so, how can this
be achieved?
Is there a way of using the anonymous login account?
If not, is this not a strange inconsistency with SQL server, and are there
plans to address this with Yukon?
Many Thanks
Simon Kirk
I am running into the same problem. Currently we are kludging this by
creating a "pass-thru" user on the server with the same uid/pwd as the
client. Also, the user has to be an admin on the Analysis Server box. I'd
love to know if anyone has found a more direct way to remotely register an
Analysis Server.
Ken Collier
"Simon Kirk" wrote:

> Given that MS Analysis Services does not support standard SQL Server
> Authentication (thus requiring windows user accounts), is it possible for a
> remote client PC, connected to a network using a VPN, to be authenticated on
> an Analysis Server if it is not part of the host domain? If so, how can this
> be achieved?
> Is there a way of using the anonymous login account?
> If not, is this not a strange inconsistency with SQL server, and are there
> plans to address this with Yukon?
> Many Thanks
> Simon Kirk
>

Remote Connection to Analysis Services using VPN

Given that MS Analysis Services does not support standard SQL Server
Authentication (thus requiring windows user accounts), is it possible for a
remote client PC, connected to a network using a VPN, to be authenticated on
an Analysis Server if it is not part of the host domain? If so, how can this
be achieved?
Is there a way of using the anonymous login account?
If not, is this not a strange inconsistency with SQL server, and are there
plans to address this with Yukon?
Many Thanks
Simon KirkI am running into the same problem. Currently we are kludging this by
creating a "pass-thru" user on the server with the same uid/pwd as the
client. Also, the user has to be an admin on the Analysis Server box. I'd
love to know if anyone has found a more direct way to remotely register an
Analysis Server.
Ken Collier
"Simon Kirk" wrote:

> Given that MS Analysis Services does not support standard SQL Server
> Authentication (thus requiring windows user accounts), is it possible for
a
> remote client PC, connected to a network using a VPN, to be authenticated
on
> an Analysis Server if it is not part of the host domain? If so, how can th
is
> be achieved?
> Is there a way of using the anonymous login account?
> If not, is this not a strange inconsistency with SQL server, and are there
> plans to address this with Yukon?
> Many Thanks
> Simon Kirk
>