rp_sqlredirect: Implementation

1> exec servername...rp_sqlredirect
2>              @dbname    ,
3>              @sqlstmt   ,
4>              @srcserver ,
5>              @srcuserid ,
6>              @srcpasswd ,
7>              @dstserver ,
8>              @dstuserid ,
9>              @dstpasswd ,
10>             @dsttable 

servername

The name of the Open Server. Originally this was intended to be generic and applied to each different server. So far this has not been a necessity, and hence we only have one open server known as open_acs_dds.

@dbname

The name of the database from which you are selecting data.

@sqlstmt

The SQL statement you wish to be executed in @db. For example, ``select * from table'' would select the entire contents of table. Note that the SQL statement should be enclosed in single quotes (') for most applications.

@srcserver

The name of the source dataserver. Continuing with our example, this might be acs_dds.

@srcuserid

A valid username on @srcserver.

@srcpasswd

The password corresponding to @srcuserid.

@dstserver

The name of the destination dataserver. For example, sybase1.

@dstuserid

A valid username on @dstserver.

@dstpasswd

The password corresponding to @dstuserid.

@dsttable

The name of the table on @dstserver where the result set of @sqlstmt will be placed. The table will be created automatically and if there is already a table with that name, it will be dropped and recreated. Note that if you specify an existing table, it will be dropped! This table name should always be full qualified. For example, use 'fred..foobar' instead of just foobar. As with @sqlstmt, this name is generally placed inside of single quotes (') for most applications.

1. Login to @srcserver as @srcuserid.
2. Login to @dstserver as @dstuserid.
3. Execute @sqlstmt in @dbname on @srcserver.
4. Examine result set and create a @dsttable on @dstserver.
5. Initiate bulk copy in on @dstserver using result set from @srcserver.
6. Logout of @srcserver and @dstserver.