ON9Blog-戇九博客

2007-11-08T17:41:00.000-08:00

Myfaces 1.1.5 with RichFaces 3.1.1
There is a compatibility problem between these libraries. The richfaces interference the component creation cycle and cached the creation process. The component will be re-created even the page is in first load, which fail the t:saveState.

2007-10-15T20:12:00.000-07:00

Installation location of JBoss 4.0.4
The installation location of the JBoss 4.0.4 must not contains a whitespace, else it'll throw MalFormedURLException during the EJB lookup

2007-10-14T19:52:00.000-07:00

Remote X Window using Cygwin

Download Cygwin and install it in any Win32 machine
In the Linux server, assume Gnome is the display manager, type "gdmsetup" and enable the XDMCP.
Open the bash shell in Cygwin, type "XWin -query "

2007-06-26T05:58:00.000-07:00

Handling Chinese Characters in JSF/JSP
The best way to handling chinese encoding problem is to install a JSP filter.

2007-02-23T00:07:00.000-08:00

JBoss AOP Part 2

The previous post was about using JBoss AOP with load time weaving. This feature
is great because there is no need for post compilation, the transformer will
weave the AOP code to the normal Java bytecode automatically without any
intervention. However, it failed in my project becoz there are many classes and
the transformer is always complain about out of memory. The JBoss forum said
this bug won't be fixed until the next two release. Since I'm die hard to try
the Jboss AOP. I took the precompile approach. The steps are listed as
followings

Add the following in ur ant build script for AOP precompilation in netbeans

<target name="-pre-dist">
<path id="javassist.classpath">
<pathelement path="/usr/local/share/download/jboss-aop_1.5.0.GA/lib-50/javassist.jar"/>
</path>
<path id="trove.classpath">
<pathelement path="/usr/local/share/download/jboss-aop_1.5.0.GA/lib-50/trove.jar"/>
</path>
<path id="concurrent.classpath">
<pathelement path="/usr/local/share/download/jboss-aop_1.5.0.GA/lib-50/concurrent.jar"/>
</path>
<path id="jboss.common.classpath">
<pathelement path="/usr/local/share/download/jboss-aop_1.5.0.GA/lib-50/jboss-common.jar"/> </path>
<path id="lib.classpath">
<path refid="javassist.classpath"/>
<path refid="trove.classpath"/>
<path refid="jboss.aop.classpath"/>
<path refid="jboss.common.classpath"/>
<path refid="concurrent.classpath"/>
</path>
<path id="jboss.aop.classpath">
<pathelement
path="/usr/local/share/download/jboss-aop_1.5.0.GA/lib-50/jboss-aop-jdk50.jar"/>
</path>
<path id="local.lib">
<fileset file="${build.web.dir.real}/WEB-INF/lib/*.jar">
</fileset>
</path>
<path id="classpath">
<path refid="lib.classpath"/>
<path refid="jboss.aop.classpath"/>
<path refid="local.lib"/>
</path>
<property name="aop.config.filepath"
value="${src.dir}/MFT-SIDS-war-aop.xml"></property>
<taskdef name="aopc15" classname="org.jboss.aop.ant.AopC"
classpathref="jboss.aop.classpath"/>
<aopc15 compiler classpathref="classpath" verbose="true"
optimized="false">
<classpath path="${build.classes.dir}"/>
<src path="${build.classes.dir}"/>
<include name="**/*.class"/>
<aoppath path="${aop.config.filepath}"></aoppath>
<aopclasspath path="${build.classes.dir}"/>
</aopc15>
<copy file="${aop.config.filepath}"
todir="/usr/local/share/app/jboss-4.0.4.GA/server/default/deploy">
</copy>
</target>
Assumed the development platform is JBoss 4.0.4 GA(Bundled with Netbeans). The client application needs to include the extra client file "jboss-aspect-jdk50-client.jar". In addition, it requires the file "jboss-aop-jdk50.jar" to compile the project. But DO NOT include it to the deployment package

2007-02-20T19:18:00.000-08:00

Startup parameter for JBoss clustering
For some reasons, the clustering doesn't work until start the run.sh with -b "{IPAddress} parameter. This cause a big problem since the Netbeans Jboss plugin doesn't allow me to insert any custom parameters. Finally I have to modify the "{Jboss}/server/default/deploy/cluster-service.xml" file and change the "NodeAddress" to a real IP. Then I can start the clustered JBoss in Netbeans

2007-02-20T04:47:00.000-08:00

JSF onValueChangeListener Bug
Myfaces seems to have a problem when there are two SelectOneMenu and each of them has a value change listener. When both of them have the "rendered" attribute is set, the second SelectOneMenu seems will never have the valueChangeListener fired. I fixed this problem by using the JSTL's c:if

2007-02-20T04:34:00.000-08:00

JSF & JSTL
I experienced something strange about JSF & JSTL. The test attribute of the JSTL's c:if seems always evaluated to false. Finally I have figured out that it is related to the web.xml file, I need to use the servlet 2.4's DTD and everything works fine. That is weird

2007-02-16T01:51:00.000-08:00

Configure JBoss AOP 1.5.3 FOR JBoss 4.0.4 GA

Download the JBoss AOP package
Copy all the files in the directory "{JBoss-AOP-1.5.3}/lib-50" into "{Jboss}/server/default/lib"
Remove the old "jboss-aop-deplopyer" from the "{Jboss}/server/default/deploy" if any
Copy the whole directory from "{JBoss-AOP-1.5.3}/jboss-40-install/jboss-aop-jdk50.deployer" to the directory "{Jboss}/server/default/deploy"
Remove the files "javassist.jar" and "common-softwarevaluehashmap.jar" from the "{Jboss}/server/default/deploy/jboss-aop-jdk50.deployer" directory.
Edit the "{Jboss}/server/default/deploy/jboss-aop-jdk50.deployer/META-INF/jboss-service.xml" file, set the "EnableLoadtimeWeaving" to true, in the "Exclude" attribute, add ",org.apache.,org.quartz.,java."

The following shows the file content
<?xml version="1.0" encoding="UTF-8"?>




<server>

<mbean
code="org.jboss.aop.deployment.AspectManagerServiceJDK5"
name="jboss.aop:service=AspectManager">
<attribute
name="EnableLoadtimeWeaving">true</attribute>

<attribute
name="SuppressTransformationErrors">true</attribute>
<attribute
name="Prune">true</attribute>
<attribute
name="Include">org.jboss.test, org.jboss.injbossaop</attribute>
<attribute
name="Exclude">org.jboss.,org.apache.,org.quartz.,java.</attribute>

<attribute
name="Ignore">*$$EnhancerByCGLIB$$*</attribute>
<attribute
name="Optimized">true</attribute><attribute
name="Verbose">false</attribute>
</mbean>
<mbean code="org.jboss.aop.deployment.AspectDeployer"
name="jboss.aop:service=AspectDeployer">
</mbean>
</server>
Edit the file "{Jboss}/server/bin/run.sh" , change the line JAVA_OPTS to the following
JAVA_OPTS="$JAVA_OPTS -Dprogram.name=$PROGNAME -javaagent:/usr/local/share/app/jboss-4.0.4.GA-test/server/default/lib/pluggable-instrumentor.jar"

2007-01-21T23:31:00.000-08:00

Update Myfaces in JBoss

I discovered some bugs in the setValueBinding method of the Myfaces that bundled with JBoss 4.0.4 GA. It will throw exception if I tried to set a value binding to the component attribute which is not "value".
Remove all the files in {JBOSS_PATH}\server\default\deploy\jbossweb-tomcat55.sar\jsf-libs
Copy all the myfaces files to {JBOSS_PATH}\server\default\deploy\jbossweb-tomcat55.sar\jsf-libs
Copy commons-collections.jar to {JBOSS_PATH}\server\default\lib

2007-01-21T23:16:00.000-08:00

Configure Oracle data source in JBOSS

Download and place the Oracle JDBC driver (ojdbc14.jar) inside the {JBOSS_PATH}\server\default\lib
Add the datasource XML file into the {JBOSS_PATH}\server\default\deploy folder, it should have the following content

<datasources>
<local-tx-datasource>
<jndi-name>MFT-OracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@192.168.3.122:1521:tcs</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>tcs</user-name>
<password>tcs</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<metadata><type-mapping>Oracle10g</type-mapping></metadata>
</local-tx-datasource>
</datasources>

2006-12-13T17:11:00.000-08:00

Netbeans 5.5+JBoss4.04GA+Oracle 10gR2+EJB3

Configure JBoss for Oracle
Download the JDBC driver and copy the file "ojdbc14.jar" into the "${JBOSS_HOME}/server/default/lib" folder
Create a data source file called "tx-oracleDS.xml" in the "${JBOSS_HOME}/server/default/deploy" folder
<datasources>
<local-tx-datasource>
<jndi-name>MFT-OracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@192.168.3.122:1521:tcs</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>tcs</user-name>
<password>tcs</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<metadata><type-mapping>Oracle10g</type-mapping></metadata>
</local-tx-datasource>
</datasources>
Following the instructions of the Netbeans 5.5 JBoss tutorial @
http://www.netbeans.org/kb/55/ejb30-jboss.html
- -User the java:/{DataSourceName} for the data source name in the persistance.xml
- -For JBoss the context lookup string for EJB is "EJBAppName/{EJBBeanName}/local"
- In Netbeans 5.5, Debug and Run the application by right clicking on the "Enterprise Application", but NOT THE Web application!!

2006-10-20T01:04:00.000-07:00

Jboss HTTP Clustering Part1 (Load balancing)

Download apache2.x and mod_jk 1.2.x. Compile and install.
Modify APACHE_HOME/conf/httpd.conf and add a single line at the end of the file:
```
# Include mod_jk's specific configuration file
Include conf/mod-jk.conf
```
Create a new file named APACHE_HOME/conf/mod-jk.conf:

# Specify the filename of the mod_jk lib
LoadModule jk_module modules/mod_jk.so

# Where to find workers.properties
JkWorkersFile conf/workers.properties

# Where to put jk logs
JkLogFile logs/mod_jk.log

# Set the jk log level [debug/error/info]
JkLogLevel info

# Select the log format
JkLogStampFormat "[%a %b %d %H:%M:%S %Y]"

# JkOptions indicates to send SSK KEY SIZE
JkOptions +ForwardKeySize +ForwardURICompat -ForwardDirectories

# JkRequestLogFormat
JkRequestLogFormat "%w %V %T"

# Mount your applications
JkMount /application/* loadbalancer

# You can use external file for mount points.
# It will be checked for updates each 60 seconds.
# The format of the file is: /url=worker
# /examples/*=loadbalancer
JkMountFile conf/uriworkermap.properties

# Add shared memory.
# This directive is present with 1.2.10 and
# later versions of mod_jk, and is needed for
# for load balancing to work properly
JkShmFile logs/jk.shm

# Add jkstatus for managing runtime data
<Location /jkstatus/>
JkMount status
Order deny,allow
Deny from all
Allow from 127.0.0.1
</Location>

The LoadModule directive must reference the mod_jk library you have downloaded in the previous section. You must indicate the exact same name with the "modules" file path prefi

Create a uriworkermap.properties file in the APACHE_HOME/conf directory.

# Simple worker configuration file

# Mount the Servlet context to the ajp13 worker
/jmx-console=loadbalancer
/jmx-console/*=loadbalancer
/web-console=loadbalancer
/web-console/*=loadbalancer

Create a file APACHE_HOME/conf/workers.properties

# Define list of workers that will be used
# for mapping requests
worker.list=loadbalancer,status

# Define Node1
# modify the host as your host IP or DNS name.
worker.node1.port=8009
worker.node1.host=node1.mydomain.com
worker.node1.type=ajp13
worker.node1.lbfactor=1
worker.node1.cachesize=10

# Define Node2
# modify the host as your host IP or DNS name.
worker.node2.port=8009
worker.node2.host= node2.mydomain.com
worker.node2.type=ajp13
worker.node2.lbfactor=1
worker.node2.cachesize=10

# Load-balancing behaviour
worker.loadbalancer.type=lb
worker.loadbalancer.balance_workers=node1,node2
worker.loadbalancer.sticky_session=1
#worker.list=loadbalancer

# Status worker for managing load balancer
worker.status.type=status

```
Configure JBoss
```
Edit the
JBOSS_HOME/server/all/deploy/jbossweb-tomcat50.sar/server.xml
file (replace /all with your own server name if necessary).
Locate the <Engine> element and add an attribute
jvmRoute:
```
<Engine name="jboss.web" defaultHost="localhost" jvmRoute="node1">
... ...
</Engine>
         
```
Edit the
JBOSS_HOME/server/all/deploy/jbossweb-tomcat50.sar/META-INF/jboss-service.xml
file (replace /all with your own server name). Locate the
<attribute> element with a name of UseJK, and
set its value to true:
```
<attribute name="UseJK">true</attribute>


The full document is located @
http://docs.jboss.org/jbossas/jboss4guide/r4/html/cluster.chapt.html
```

2006-10-20T00:03:00.000-07:00

Setup TCP/IP clustering with Linux's heartbeat

Download the libnet and heartbeat source code, install it in all the nodes
Libnet source code (v1.1.2.1)is available @ http://www.packetfactory.net/libnet/
Heartbeat source code(v2.0.0) is available @ http://www.linux-ha.org/
The source package will install heartbeat's binaries to /usr/local/lib/heartbeat and the config files to /usr/local/etc/ha.d
Configure heartbeat in all the nodes
Create the file "ha.cf" in the /usr/local/etc/ha.d folder and type the following content.

debugfile /var/log/ha-debug
logfile /var/log/ha-log
node wsn03-99
node tser-linux-dev1 # List our cluster members
keepalive 1 # Send one heartbeat each second
deadtime 3 # Declare nodes dead after 3 seconds
bcast eth0 # Broadcast heartbeats on eth0 and eth1 interfaces
ping 192.168.3.253 # Ping our router to monitor ethernet connectivity
auto_failback no # Don't fail back to paul automatically
respawn hacluster /usr/local/lib/heartbeat/ipfail # Failover on network failures

node wsn03-99 and node tser-linux-dev1 is the host name of the node that returned by the command "uname -n" ,the line ping 192.168.3.253 is the router address.

Create the file "/usr/local/etc/ha.d/haresources" and type the following content

tser-linux-dev1 192.168.3.150 httpd

tser-linux-dev1 is the preferred host, the IP 192.168.3.150 is the virtual IP address and must not be used by any machine. "httpd" is the script name of the service that needed to be clustered, it must match the file in /etc/init.d or /usr/local/etc/ha.d/resource.d/

Create the file "/usr/local/etc/ha.d/authkeys" and type the following content

auth 1
1 sha1 key

The "key" is the authorized key generated by some utility, for sake of simpliclity I just put some value. Apply chmod 600 to this file
Finally, make sure all the nodes has the same set of files and heartbeat is ready to run. To start heartbeat type "network heartbeat start"

Configure Apache for clustering
In the apache's http.conf file, includes the following directives

Listen {virtual-IP}:port-number

In addition, disable the startup service of apache in the
/etc/init.d because the heartbeat service will take control of it

2006-10-10T19:18:00.000-07:00

JBoss 4.04GA with EJB3 clustering in Linux

Install the JBoss 4.04 jar installer and choose "ejb3-cluster" as the installation profile
http://blog.matrix.org.cn/page/joeyta?entry=jboss_ejb3_helloworld_%E5%82%99%E5%BF%98%E8%A8%98
Disable IPv6
Place the following line in the /etc/modprobe.conf to turn off IPv6
```
alias net-pf-10 off
```
Set up multi-cast
Make sure the linux kernel is support multi-casting and add the following line in the startup script to bind a mulit-cast IP to the ethernet card

route add -net 224.0.0.0 netmask 240.0.0.0 dev eth0

Reference the section "Verify multicast works" of the following URL to verify your setup
http://www.spikesource.com/docs/cs_1.6.0-win32/doc/jboss/jboss_troubleshoot.html
Install the web console
The "ejb3-cluster" profile is missing the web console
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3971687
To manually install it, first install another copy of Jboss with "full" profile, then copy the following files mentioned in the xml tag to the "ejb3-cluster" profile
<fileset dir="@{jboss.install}"
targetdir="$INSTALL_PATH/server/$SERVER_CONFIG">
<include name="deploy/jsr77-service.xml"/>
</fileset>
<fileset dir="@{jboss.dist}/server"
targetdir="$INSTALL_PATH/server/$SERVER_CONFIG">
<include name="lib/jboss-jsr77.jar"/>
<include name="lib/jboss-management.jar"/>
</fileset>
<fileset dir="@{jboss.dist}/server"
targetdir="$INSTALL_PATH/server/$SERVER_CONFIG">
<include name="deploy/management/**"/>
<include name="deploy/monitoring-service.xml"/>
<include name="lib/jboss-monitoring.jar"/>
</fileset>
Install the web farm
The "ejb3-cluster" profile is also missing the web farm facility. After enabling the farm, you can deploy the EJB's jar file in a single node's farm folder and JBoss will automatically distribute it to all other nodes. To enable farm, first install a JBoss with "full" profiler in other location and copy the "farm" folder to the "{JBossHome}/server/default", also copy the "deploy.last" folder to the "{JBossHome}/server/default/deploy"
Startup the JBoss with -b parameter
Some Linux distro has some problem in IP address binding, therefore you should startup the JBoss using the "./run.sh -b {IPAddress}" command.
Verify the JBoss cluster is setup
After starting up all the JBoss servers in the cluster, each server should join the cluster and open the JMX console in one of the server to verify the cluster is running
"http://{IP}:8080/jmx-console/"

click on the link "jboss:service=DefaultParition", there should be a list of server IP address in the "CurrentView" row.
Test EJB3 clustering
Following the instruction in the following URL to setup a EJB3 project in eclipse
http://blog.matrix.org.cn/page/joeyta?entry=jboss_ejb3_helloworld_%E5%82%99%E5%BF%98%E8%A8%98
- The key step is to install JBoss IDE in your Eclipse and create a new EJB3 project.
- Change the target deployment directory from "{jboss.home}/server/default/deploy" to
  "{jboss.home}/server/default/farm"
- In the "jndi.properties" file, use the following line for the provider's url
  java.naming.provider.url={node1_IPAdd}:1100,{node2_IPAdd}:1100
  The HA JNDI port is 1100 instead of 1099
Reference URL
(Create EJB3 project)
http://blog.matrix.org.cn/page/joeyta?entry=jboss_ejb3_helloworld_%E5%82%99%E5%BF%98%E8%A8%98
(JBoss EJB3 tutorial)
http://docs.jboss.org/ejb3/app-server/tutorial/index.html

(Setup multi-cast)
http://www.spikesource.com/docs/cs_1.6.0-win32/doc/jboss/jboss_troubleshoot.html

(JBoss troubleshooting)
http://jboss.com/index.html?module=bb&op=viewtopic&t=66244&view=previous
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3956298
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=82345

2006-09-24T23:43:00.000-07:00

Install JavaEE5 samples
To run the example, you need to download the Java Blueprints Solutions Catalog for Java EE 5 bundle. To do this, follow these steps:

Go to the Java Blueprints site on java.net.
Click Documents & files in the right-hand navigation bar.
Select the bpcatalog-ee5(2) directory.
Download the bpcatalog-ee5-ea-0.6-installer.jar.
Run the installer according to the directions on the page from where you downloaded it.

Once you have checked out the code, do the following:

Obtain the Java EE 5 SDK, install it, and start the Sun Java System Application Server.
Add the Ant build tool included in the Application Server to your path. It's located in the Applications Server installation's lib/ant/bin directory.
Go to /bp-project/.
Open build.properties in a text editor.
Set the javaee.home property to the path of your Application Server installation.
Set the javaee.server.passwordfile property to the fully-qualified path to a file that contains your password. Follow the instructions in the build.properties file.
Save build.properties.
Go to /apps/webtier/bp-dynamic-text.
Run ant.
Run ant deploy.
Launch a browser and enter this URL:http://localhost:8080/bp-dynamic-text/

2006-08-03T02:35:00.000-07:00

Multi-Serial port and Linux
In Linux, only COM1-4 are configured by default. The setserial command is needed to configure the COM port that is >5.Here is an example usage
setserial /dev/ttySX port 0x04e8 irq 5 uart 16550A baud_base 115200
The man pages haven't mentioned about the "baud_base" is a compulsory parameter, but the command will fail without it. In addtion, even the COM port is intended to use < 115200 baud rate to coomuicate (like 9600). You still have to configure it as 115200.
A related command in Linux is minicom, it is used to configure serial port's param.

2006-06-25T20:11:00.000-07:00

Video conference using Java Media Framework(JMF) in Linux

Download the JMF for Linux package
open the jmstudio script, replace the line
"exec java JMStudio $*"
with the line
"exec java -Dawt.toolkit=sun.awt.motif.MToolkit JMStudio $*"
at the end of the file
Setup a host name for both machines(VERY IMPORTANT)
Open the jmstudio in the server side, choose "Capture" and then transmit. In the trasmit dialog box type the client IP address and port number
Open the jmstudio in the client side, choose "Open RTP Session", and then type in the client's IP address and port number
Done!

2006-06-25T19:57:00.000-07:00

Export chinese character to excel in web application
The only way to export the chinese character to excel is using UTF16 .I tried big-5 and UTF8 encoding but no luck, they both can only successfully export few number of characters. It fails when exporting an large chunk of character streams. The following shows the code fragement of exporting some chinese characters in asp.net

Response.Clear();
Response.Charset="";
Response.ContentType="application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition","attachment;filename=search.xls");

ArrayList content=new ArrayList();
content.Add((byte)0xff));
content.Add((byte)0xfe));

String text="大";
UnicodeEncoding encoding=new UnicodeEncoding();
content.AddRange((byte[])encoding.GetBytes(text));
byte output[]=content.ToArray(typeof(byte));
Response.OutputStream.Write(output,0,output.Length);

Response.End();

The byte 0xff and 0xfe is the header code that indicates the document is in UTF16 format. Therefore, all the text must be converted to 2byte UTF16 binary before export to the Response.OutputStream

2006-06-25T19:45:00.000-07:00

SQL 2000 Database migration
Besides transferring the database from the source to the target server. User accounts are also needed to be migrated as well. The following URL shows the correct way to migrate the users.
http://support.microsoft.com/kb/246133/
The most reliable way is to use the store proc approach

A complete resolution to transfer logins and passwords between different versions of SQL Server

To transfer logins and passwords between different versions of SQL Server, follow these steps:1. Run the following script on the source SQL Server. This script creates two stored procedures named sp_hexadecimal and sp_help_revlogin in your master database. Continue to step 2 when you finish creating the procedure.

Note The following procedure is dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server, and selecting directly from system tables is discouraged.

----- Begin Script, Create sp_help_revlogin procedure -----USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUTASDECLARE @charvalue varchar(256)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalueGOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @xstatus intDECLARE @binpwd varbinary (256)DECLARE @txtpwd sysnameDECLARE @tmpstr varchar (256)DECLARE @SID_varbinary varbinary(85)DECLARE @SID_string varchar(256)IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_nameOPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwdIF (@@fetch_status = -1)BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''PRINT 'DECLARE @pwd sysname'WHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstr EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null password EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd ENDCLOSE login_curs DEALLOCATE login_curs RETURN 0GO ----- End Script -----

After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:

EXEC master..sp_help_revlogin