Yikes.
Try this, search and replace * with the @
IF OBJECT_ID('sp_displayoaerrorinfo ') IS NOT NULL
DROP PROCEDURE sp_displayoaerrorinfo
GO
CREATE PROCEDURE sp_displayoaerrorinfo
*ErrObject INTEGER,
*RetCode INTEGER
AS
BEGIN
SET NOCOUNT ON
DECLARE *cDescription VARCHAR(255)
EXECUTE sp_OAGetErrorInfo *ErrObject, *RetCode OUTPUT, *cDescription OUTPUT
SELECT *cDescription
END
GO
IF OBJECT_ID('sp_oaScript') IS NOT NULL
DROP PROCEDURE sp_oaScript
GO
CREATE PROCEDURE sp_oaScript
*ServerNameArg SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE *RetCode INTEGER
DECLARE *ErrObject INTEGER
DECLARE *SQLServer INTEGER
-- Create server object.
EXECUTE *RetCode = sp_OACreate 'SQLDMO.SQLServer', *SQLServer OUT
EXECUTE *RetCode = sp_OASetProperty *SQLServer, 'LoginTimeout', 10
EXECUTE *RetCode = sp_OASetProperty *SQLServer, 'LoginSecure', True
EXECUTE *RetCode = sp_OASetProperty *SQLServer, 'AutoReConnect', False
EXECUTE *RetCode = sp_OAMethod *SQLServer, 'Connect', NULL, *ServerNameArg
IF **ERROR|*RetCode <> 0
BEGIN
SELECT *ErrObject = *SQLServer
EXECUTE sp_displayoaerrorinfo *ErrObject, *RetCode
END
END
GO
EXEC sp_oaScript
-----Original Message-----
From: Davis, Ralph [mailto:Ralph.[Email address protected]
Sent: Wednesday, June 29, 2005 2:42 PM
To: Paul Schlieper
Subject: RE: Remote Servers
This is hard to follow with all the stuff that was stripped out. I haven't
tried Ole automation because it's generally unsupported by MS even though it
works most of the time.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322884
However, I'll consider this going forward.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Paul Schlieper [mailto:Paul.[Email address protected]
Sent: Wednesday, June 29, 2005 12:53 PM
To: Davis, Ralph
Subject: RE: Remote Servers
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
You can use this to check if a connection to a remote server is possible.
The key is the LoginTimeout parm: currently set to 10 (seconds), if no
connection is made after that time, [Email address protected] <> 0, and you
can trap that.
IF OBJECT_ID('sp_displayoaerrorinfo ') IS NOT NULL
DROP PROCEDURE sp_displayoaerrorinfo
GO
CREATE PROCEDURE sp_displayoaerrorinfo
[Email address protected] INTEGER,
[Email address protected] INTEGER
AS
BEGIN
SET NOCOUNT ON
DECLARE [Email address protected] VARCHAR(255)
EXECUTE sp_OAGetErrorInfo [Email address protected] [Email address
protected] OUTPUT, [Email address protected] OUTPUT
SELECT [Email address protected]
END
GO
IF OBJECT_ID('sp_oaScript') IS NOT NULL
DROP PROCEDURE sp_oaScript
GO
CREATE PROCEDURE sp_oaScript
[Email address protected] SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE [Email address protected] INTEGER
DECLARE [Email address protected] INTEGER
DECLARE [Email address protected] INTEGER
-- Create server object.
EXECUTE [Email address protected] = sp_OACreate 'SQLDMO.SQLServer',
[Email address protected] OUT
EXECUTE [Email address protected] = sp_OASetProperty [Email address
protected] 'LoginTimeout', 10
EXECUTE [Email address protected] = sp_OASetProperty [Email address
protected] 'LoginSecure', True
EXECUTE [Email address protected] = sp_OASetProperty [Email address
protected] 'AutoReConnect', False
EXECUTE [Email address protected] = sp_OAMethod [Email address
protected] 'Connect', NULL, [Email address protected]
IF [Email address protected] <> 0
BEGIN
SELECT [Email address protected] = [Email address protected]
EXECUTE sp_displayoaerrorinfo [Email address protected]
[Email address protected]
END
END
GO
EXEC sp_oaScript
-----Original Message-----
From: Davis Ralph
[Email address protected]
Sent: Wednesday, June 29, 2005 1:21 PM
To: LazyDBA Discussion
Subject: RE: Remote Servers
When using a linkserver it MUST be available or the SP will fail. You can't
put it in an "IF" statement either. When the proc starts it allocates all
resources. If one isn't available it won't even start up so you cannot code
around this in this proc. Same is true when you compile a proc with a
linkserver call in it. If the link isn't available it won't compile.
This is the only solution MS had for this when I came across it. The way
to get around this is to put the linkserver call in a sub-proc. Then
xp_cmdshell out and run an ODBC ping against the server the link is pointing
to. If it comes back successful I EXEC the sub-proc with the linkserver
call in it, else I skip that one. It's a little awkward but it works
because the actual linkserver call isn't spawned until I know the link is
up.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Michael Florkiewicz
[mailto:mssqldba-ezmlmshield-x88085401.[Email address protected]
Sent: Wednesday, June 29, 2005 11:41 AM
To: LazyDBA Discussion
Subject: Remote Servers
Trying to figure this out
CREATE Procedure sp_advname_2_alabama
(
[Email address protected] varchar(300) =NULL,
[Email address protected] varchar(300),
[Email address protected] varchar(50),
[Email address protected] varchar(50)
)
WITH ENCRYPTION
AS
DECLARE [Email address protected] varchar(3000)
set [Email address protected] = 'Select * from
openquery(washingtonco,''alenv3.dbo.sp_stdnamesearch '''''
[Email address protected] + ''''',''''' + [Email address protected] + ''''',
'''''+
[Email address protected] +''''', '''''+ [Email address protected]
+''''''')'
set [Email address protected] = [Email address protected] + 'UNION ALL '
set [Email address protected] = [Email address protected] + 'Select * from
openquery(saraland,''alenv3.dbo.sp_stdnamesearch ''''' [Email address
protected]
+ ''''',''''' + [Email address protected] + ''''', '''''+ [Email address
protected] +''''',
'''''+ [Email address protected] +''''''')'
set [Email address protected] = [Email address protected] + 'UNION ALL '
set [Email address protected] = [Email address protected] + 'Select * from
openquery(satsuma,''alenv3.dbo.sp_stdnamesearch ''''' [Email address
protected]
+ ''''',''''' + [Email address protected] + ''''', '''''+ [Email address
protected] +''''',
'''''+ [Email address protected] +''''''')'
set [Email address protected] = [Email address protected] + 'ORDER BY
plastname, pfirstname'
[Email address protected]
The Above is my current Procedure which work 100% when all of the remote
servers are online,
Fails when any one of the remote servers goes offline.
How can I make the sproc check the connection to each remote server and
only return data from the servers that respond, skipping the offline
servers.
Thanks in advance for any advice
Michael Florkiewicz
Coastal Security Solutions, Inc.
http://www.coastalsecuritysolutions.com
Provider of Remote Backup Services
Get your free trial at
http://www.cssibackup.com
> -----Original Message-----
> From: Javed
> [mailto:mssqldba-ezmlmshield-x43592642.[Email address protected]
> Sent: Monday, April 11, 2005 4:24 AM
> To: LazyDBA Discussion
> Subject: RE: SQL Mail
>
>
>
> Hi,
>
> How safe it is to use 'xpsmtp80.dll'. Any one have tried it???
>
> Regards,
>
> Javed Iqbal
>
>
> -----Original Message-----
> From: Hugh du Toit
> [mailto:mssqldba-ezmlmshield-x53960956.[Email address protected]
> Sent: Monday, April 11, 2005 1:57 PM
> To: LazyDBA Discussion
> Subject: RE: SQL Mail
>
>
> Do not use SQL mail.
>
> Rather use something like http://www.sqldev.net/xp/xpsmtp.htm or
> http://www.databasejournal.com/features/mssql/article.php/3489111.
>
> With these procedures you send out mail by connecting to a
> mail server on
> your network, and not by installing outlook on your SQL
> server (required by
> SQL mail)
>
> -----Original Message-----
> From: Sunil Seth
> [mailto:mssqldba-ezmlmshield-x68258993.[Email address
> protected]
> Sent: Saturday, April 09, 2005 00:39 AM
> To: LazyDBA Discussion
> Subject: Re: SQL Mail
>
> Hi all
>
> I am trying to set Sql mail to receive mails if my backup
> fails or succeeds.
> But when I right click on sql server agent and try to go to
> mail session my
> mail profile is disabled.. Why is that and what do I do to resolve it.
>
> Any help would be appreciated
>
> Thanks
>
> Sunil
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and
delete
the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and delete
the material from any computer.
MS Sql Server LazyDBA home page