* 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
*--------------------------------------------------