Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)

Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

'Copy Website' function in VS 2005 updates or overwrites Database?

I have a sql 2005 express database uploaded to my website with important information in it.

Now, I had to make some table change and need to update the online database.

I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it.

Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it?

Thanks.

The system will over right as the moderfications will have been done in the local copy of the database... ie in the APP_Data.. (I am thinking that this is your case). If you only wanted to change the database schema and such one way would have been to create a database project and script out the changes as you were making them. Then you could have run the script against the old database.

Going forward you might have to copy down the database from the production system... Copy your web site project to the production system then import the old data into the new database structure on the server. Or you might be able to find some scripts to compare the databases and generate the scripts required to make the change in the production system.

|||Thanks Glenn!