|
:D
不过 ，sp_remotesql的用法有 如下说法：
* Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
go
* Enable CIS
sp_configure "enable cis",1
go
* Finally, use sp_remotesql, sending the sql to the server defined in point
1.
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
go
----------------------------------------------------------------------------
/*
* CISXP_SETUP1.SQL
*
* Some server setups for CIS & XP. This will work only in ASE version
* 11.5 or later.
*
* Note that you should first change this script: do a global change of
* the string "YOUR_SERVER_NAME" to your actual SQL server name; ensure
* this is identical to the change in script CISXP_SETUP2.SQL.
*
* Note that there's a server shutdown at the end.
* After restarting the server, run script CISXP_SETUP2.SQL.
*
*
* Copyright note & Disclaimer :
* =============================
* This software is provided "as is" -- no warranty.
* This software is for demonstration purposes only. It may not work correctly
* and/or reliably in a production environment.
* You can use this software free of charge for your own professional,
* non-commercial purposes.
* You are not allowed to sell this software or use it for any commercial
* purpose. You may (re)distribute only unaltered copies of this software, which
* must include this copyright note.
*
* Please send any comments, bugs, suggestions etc. to the below email address.
*
* (c) 1999 Copyright Rob Verschoor
* Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.euronet.nl/~syp_rob
*-----------------------------------------------------------------------------
*/
use master
go
/*
* Check we're on ASE 11.5 at least
*/
if substring(@@version, 1, 26) != "Adaptive Server Enterprise"
begin
print "***"
print "***"
print "*** You can only use CIS and/or XP features on ASE version 11.5 or later."
print "*** These features do not exist in your current version of ASE, sorry..."
print "***"
print "***"
end
go
/*
* Fix bug in definition of @@servername on ASE 11.5
*
* (If you created your server with the default Sybase
* 'srvbuild' or 'srvbuildres' tools, this will have
* been set up wrong. For an alternative, check out
* the free tool "sybinit4ever" at
* http://www.euronet.nl/~syp_rob/si4evr.html)
*/
if exists( select * from master..sysservers
where srvname="local" )
exec sp_dropserver local
go
if not exists( select * from master..sysservers
where srvid=0 )
exec sp_addserver YOUR_SERVER_NAME, local
go
/*
* add XP servername if not there yet
* Note that the interfaces file should also contain an entry
* for this XP server
*/
if not exists( select * from master..sysservers
where srvname="YOUR_SERVER_NAME_XP" )
exec sp_addserver YOUR_SERVER_NAME_XP, null, YOUR_SERVER_NAME_XP
go
/*
* some config options to set up CIS & XP
*/
sp_configure "enable cis", 1
go
sp_configure "max cis remote connections", 5
go
/*
* now restart the server for these changes to take effect:
*/
shutdown
go
/*
* restart the server now, and run the script "CISXP_SETUP2.SQL"
*/
===============================================================================
/*
* CISXP_SETUP2.SQL
*
* Some server setups for CIS & XP. This will work only in ASE version
* 11.5 or later.
*
* First run script SETUP1.SQL and restart the server.
* Then, run this script.
*
* Note that you should first change this script: do a global change of
* the string "YOUR_SERVER_NAME" to your actual SQL server name; ensure
* this is identical to the change in script CISXP_SETUP1.SQL.
*
*
* Copyright note & Disclaimer :
* =============================
* This software is provided "as is" -- no warranty.
* This software is for demonstration purposes only. It may not work correctly
* and/or reliably in a production environment.
* You can use this software free of charge for your own professional,
* non-commercial purposes.
* You are not allowed to sell this software or use it for any commercial
* purpose. You may (re)distribute only unaltered copies of this software, which
* must include this copyright note.
*
* Please send any comments, bugs, suggestions etc. to the below email address.
*
* (c) 1999 Copyright Rob Verschoor
* Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.euronet.nl/~syp_rob
*-----------------------------------------------------------------------------
*/
sp_configure "xp_cmdshell context", 0
go
/*
* define a remote server which is actually pointing to yourself
*/
if not exists (select * from master.dbo.sysservers
where srvname = "YOUR_SERVER_NAME_MYSELF")
exec sp_addserver YOUR_SERVER_NAME_MYSELF, null, YOUR_SERVER_NAME
go
/*
* set up remote access authorisation
* There's various ways of doing this. The proper one would be to
* do "sp_addexternlogin YOUR_SERVER_NAME_MYSELF, sa, sa, <sa-password>".
* Easier would be "sp_addremotelogin YOUR_SERVER_NAME_MYSELF", but there's
* a built-in check that doesn't allow this for local servers. So either
* remove that check, or manually insert a row in master..sysremotelogins
* as happens below. In the below case, this will allows all logins to
* do remote access. If you want only a specific login (say 'zzz') to
* be enabled, insert the values (0,'zzz',suser_id('zzz'),0).
*/
sp_configure 'allow updates', 1
go
if not exists (select * from master.dbo.sysremotelogins
where remoteserverid = 0
and remoteusername = NULL
and suid = -1
and status = 0)
insert master.dbo.sysremotelogins values (0,null,-1,0)
go
sp_configure 'allow updates', 0
go
/*
* end
*/
============================================================================================
/*
* This script creates a procedure sp_exec_dynsql which will execute
* a string containg SQL statements.
* This makes use of a CIS-related trick, to access the server as if it
* were a remote server. This allows the use of dynamically
* generated SQL through sp_remotesql.
*
* The purpose of this procedure is to demonstrate some of the
* CIS capabilities.
*
* Installation:
* 1. First perform some server-level setups for CIS to work correctly.
* These setups are in two scripts that can be downloaded from
* http://www.euronet.nl/~syp_rob/cisxp_setup.html;
*
* 2. Next, run this script;
*
* 3. Finally, execute "sp_exec_dynsql <string-with-SQL-cmds>"
*
*
* Example:
* ========
* The below procedure 'myproc' will select the specified column from
* the specified table using "sp_exec_dynsql" :
*
* create procedure myproc
* @col_name varchar(32),
* @tab_name varchar(70)
* as
* begin
* declare @cmd varchar(255)
* select @cmd = "select " + @col_name + " from " + @tab_name
* exec sp_exec_dynsql @cmd
* end
*
* You can now do things like:
*
* 1> exec myproc "pub_name", "pubs2..publishers"
* 2> go
* pub_name
* ----------------------------------------
* New Age Books
* Binnet & Hardley
* Algodata Infosystems
*
* (3 rows affected)
*
*
* Copyright note & Disclaimer :
* =============================
* This software is provided "as is" -- no warranty.
* This software is for demonstration purposes only. It may not work correctly
* and/or reliably in a production environment.
* You can use this software free of charge for your own professional,
* non-commercial purposes.
* You are not allowed to sell this software or use it for any commercial
* purpose. You may (re)distribute only unaltered copies of this software, which
* must include this copyright note.
*
* Please send any comments, bugs, suggestions etc. to the below email address.
*
* (c) 1999 Copyright Rob Verschoor / Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.euronet.nl/~syp_rob
*-----------------------------------------------------------------------------
*/
use sybsystemprocs
go
/*
* create sp_exec_dynsql
*/
if object_id("sp_exec_dynsql") <> NULL
begin
drop procedure sp_exec_dynsql
end
go
create procedure sp_exec_dynsql
/* Copyright (c) 1999 Rob Verschoor/Sypron B.V. */
@p_cmd varchar(255) = "%"
as
begin
declare @remoteserver varchar(32)
/*
* suppress rubbish
*/
set nocount on
/*
* make sure this is 11.5 or later
*/
if substring(@@version, 1, 26) != "Adaptive Server Enterprise"
begin
print "This procedure only works for ASE version 11.5 or later."
return (-1)
end
/*
* check server name is defined
*/
if @@servername = NULL
begin
print "The @@servername must be defined."
print "Run ""sp_addserver SERVERNAME, local"", and restart the server"
return (-1)
end
/*
* Check a remote server is set up, pointing to the local server
* (this is the basic trick that makes this whole procedure work)
*/
select @remoteserver = srvname
from master..sysservers
where srvnetname = @@servername
and srvname != @@servername
if (@@rowcount = 0) or (@remoteserver = "")
begin
print "A remote server must be set up in master..sysservers,"
print "pointing to this local server."
print "First run the setup scripts CISXP_SETUP1.SQL & CISXP_SETUP2.SQL"
print "to fix this. These can be downloaded from"
print "http://www.euronet.nl/~syp_rob/cisxp_setup.html"
return (-1)
end
/*
* execute the specified command
*/
exec sp_remotesql @remoteserver, @p_cmd
end
go
grant execute on sp_exec_dynsql to public
go
/*
* end
*/
以上转自www.chinaunix.net |