Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.
HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.

My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?

Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.

Any advice would be greatly appreciated.

Nick.(nickwilson.nick@.gmail.com) writes:
> I have several stored procedures, created in a development environment,
> that I need to move to a 'QA' environment, and then in turn, to various
> production environments.
> When I move these stored procedures, I would like to encrypt them,
> using the 'WITH ENCRYPTION' clause.
> My question is, how do I copy these stored procedures from development
> to their target SQL server environment in an encrypted state?

The answer is that you don't. I think SQL 7 had a function for this, but
it was discontinued with SQL 2000.

> Up until now, we have been moving them by generating an SQL script and
> then executing that script on the target server. I have tried this
> using a script with 'WITH ENCRYPTION' specified within it, but it
> doesn't appear to work when I try and execute that script on the target
> server.

What "doesn't appear to work" mean?

You would have to write a tool that reads the script and shifts in
WITH ENCRYPTION before AS. (Assuming that the number of procedures are
too many to make manual editing an option.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
--
http://www.sqlerver2005.de
--

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
--

Copy rows across to another db

Hello all,

first of all please forgive me for asking what might turn out to be a very stupid question. I am new to SQL.

Here is my problem. I have to identical databases, one used for testing (db_test), the other for production (db_prod). What I want to do is to grab a row from a table in db_test and insert it into the relative table into db_prod.

Now, I know to use INSERT INTO, however I having problems specifiying which database to select from and which to insert to. I have tried specifying the full path ([db_test].[dbo].[Table1]) but it does not work. I can only get statements to work if is include the "USE [db_test]" line at the top, but I can't then access the other db.

Any suggestions?It is surprising that the cross database INSERT does not work. Perhaps you could post the query so I could see is there is an error?