Remove "The report is accessible at the following address:" text

I am sending emails to rpts via links. "The report is accessible at the
following address:" appears above the link. Is there a simple way to have
that text excluded from the email?
Thanks in advance
SPOThere is no way to remove this text. You would have to write your own
delivery extension.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:397BC3BE-E873-4193-83A4-25EF7B0FF772@.microsoft.com...
> I am sending emails to rpts via links. "The report is accessible at the
> following address:" appears above the link. Is there a simple way to have
> that text excluded from the email?
> Thanks in advance
> SPO

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@.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.developersdex.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.developersdex.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.
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 Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp