oldbridge
asked on
copy views to another database in Micorsoft SQL
When I tried to use DTS to copy views, instead of copying views, it created new tables based on the views and did not copy views.
There must be a code out there that can do the work.
Thansk.
There must be a code out there that can do the work.
Thansk.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I need to keep the same view names in the new database. We use excel to create reports that are linked to views. Each year database is indepedent. All we do is change year in excel to refer to new database. That is why the best option is to be able to copy all views to new database with same name.
the way that I suggest to you will do this for all views! just do it
but there is another way too:
create an SSIS package ( in business intelligence development studio - BIDS create a SSIS package) this is new version of old DTS pacakge .
in this package add a Transfer SQL Server Objects Task. in this task you can set source server, destination server and all objects you want to transfer, you can select all views for example
but there is another way too:
create an SSIS package ( in business intelligence development studio - BIDS create a SSIS package) this is new version of old DTS pacakge .
in this package add a Transfer SQL Server Objects Task. in this task you can set source server, destination server and all objects you want to transfer, you can select all views for example
The sp_helptext solution is perfectly suitable for all views, thats why is suggested it. Just put it in a curosr-loop (http://msdn.microsoft.com/en-us/library/ms180169.aspx) on all the views you want (http://msdn.microsoft.com/en-us/library/ms181381.aspx) .
Output to a file and you have your 'initial view creation script' that you can review and store (source controle?) before executing.
A bit more complex is getting it executed immediate, but I strongly advise not to do this. How do you ever know what you have done?
(and yes I try to let you gain knowledge, not to be cheap labor. So I won't try to type a possible sollution I don't have in a copy-paste format at the moment)
Output to a file and you have your 'initial view creation script' that you can review and store (source controle?) before executing.
A bit more complex is getting it executed immediate, but I strongly advise not to do this. How do you ever know what you have done?
(and yes I try to let you gain knowledge, not to be cheap labor. So I won't try to type a possible sollution I don't have in a copy-paste format at the moment)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
P.S. DTS is overkill for such a simple task and besides the tool is deprecated in SQL Server 2005 in favor of SSIS.
3 suitable sollutions posted (2 by reza_rad, one by me but reza_rad was first).
And 'acperkins' asked why on a first 'not suitable' comment, but author didn't respond.
And 'acperkins' asked why on a first 'not suitable' comment, but author didn't respond.
ASKER