Copying Views and Stored Procedures

Hello All,
I have two SQL databases which have the exact same tables - just different data. In database A there are only tables. In database B there are tables, stored procedures and views. I need to transfer the stored procedures and views from database B into database A. Is there a way to do this?
Thanks in advanceDTS or Script it. Your pick.|||Thank You.

I was playing around with it and I used a script. Thanks for your reply.|||What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?|||Originally posted by Brett Kaiser
What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?

I believe no entries would be inserted into sysdepends for the sproc corresponding to the view... which would lead to the view not showing up when you do a sp_depends on the sproc ...

copying databases to server with same name/IP

I need to copy databases with transactional replication to another box and the new server will be have the exact same name and IP address as the old server. I am planning on copying over complete backups of the master, msdb, model, distribution and userDB
databases to the new box and I was going to do a restore of each, then try to set up the replication again. Is there any way to keep the replication intact so I don't have to set it up again? Is there any particular order in which the databases have to b
e retored?
Try this order
master, msdb, distribution, publication databases. Restore the publication databases with the keep replication switch.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks for the response. I am not so hot with replication - just to clarify - by "publication databases" you mean the user databases right? The ones being replicated? Thanks again.
|||Yes
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Copying Data Problem with Export data tool

I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,

Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.It sounds as though the table that you want to copy the data into, has an identity column assigned to it. For the situation that you have described, there are two solutions available, each depends on how this secondary table will be used.

If the new table will be used in a transactional environment and it must maintain correct relationships with other tables, then you will need to maintain the same list of primary key values. To do this, we need to understand how your values for the primary key column is generated. I'm assuming a non-composite (single column) key for your system.

If it's an identity column, then you will want to maintain this property for future inserts directly into the new table, but you will also need to insert existing rows from the current table with the correct ID values. This situation is common and can be solved elegantly using the identity_insert option.

Using identity_insert, you can override the SQL Server automatic generation process of the column value for the identity column of table, and explicitly supply your own values. Once you have finished inserted these values, you can turn the identity_insert column off, to allow the column to behave normally and generate sequential identity values. You will have to research this particular aspect of behaviour to understand exactly how the identity column will respond after you turn identity_insert back off, and having inserted a random series of values. From experience a few years ago mind you, I don't believe there is any problem here and that SQL Server just resume the identity column counter by adding one to the maximum integer value in the column.

If on the other hand your table will be used in a more static context, for example bespoke data analysis, then I would suggest creating a table without the identity column. The corresponding column in the new table will have the same data type as the source table and will maintain a foreign key relationship back to the source column to ensure integrity throughout the lifetime of the table use in analysis.

Using this approach, without the identity column, you can copy the data using a simple multiple row insert operation. A example of this is below:

insert into destinationTable
columnA,
columnB,
columnC
select
columnA,
columnB,
columnC
from
sourceTable

It's important to remember that there are no restrictions, or very very few, that apply to a select statement when used as the source for a multiple row insert. Therefore, you should not hesitate to use any conditional constructs any other elements of the SQL language to ensure you insert only the data that you want. Often this feature is overlooked and people forget that the select statement need not be a simple one set query.

Regards,|||I think it is the identity insert option I need to use, I will give it a go in three days when I do the test transfer, then post on the outcome,

There is the Identity insert option on the Export data tool, Is this the option your talking about? or is there an option in the table properties?|||There is only one way to apply identity_insert, which is as a table option applicable only to the current session and for the duration of that session or until the option is explicitly turned off.

I would say that the option to enable identity_insert from within an ETL package is accomplished by the tool transparently issuing the option directly to SQL Server on your session's behalf. In this way, the ETL tool serves as just a GUI to execute SQL DDL and DML.

Nonetheless, you are correct in your thinking to investigate the identity_insert option. Just remember that column names must be specified when using this option, a requirement that often many people overlook and which can cause unnecessary frustration. This is one area where a tool similar to the one you describe can be helpful, in ensuring little compliance issues like this.

Regards,|||Got it worked a Treat, Use The Export Data tool in Management Studio, Not sure what I did differently to before, but I set the

Delete Rows in Destination table to true (even though it was empty)
And Enable Identity insert to true

Copied all the data as it was, missing all the PKs It had been including, and the PK count after the insert of the data starts at the last record it doesn't fill in the gaps, thanks for your help.

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:

> Hello,
> I need to copy from around 9 specific tables in one database (A) to another
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:

> Hello,
> I need to copy from around 9 specific tables in one database (A) to anothe
r
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax
.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

Copying data

Hello,
I need to copy from around 9 specific tables in one database (A) to another
database (B). Database B contains an exact image of those 9 tables.
Here is my requirement -
1) Altogether there could be around 4 million records across those tables.
2) The data would be moved from A to B every night.
3) Using SSIS is not an option as I would not be able to call this from Ax.
But I will
be able to call stored procs, statements etc.
Could anyone suggest me an optimum solution please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Sorry - my mistake.
Tables in database B is not an exact mirror image. Those 9 tables contain 2
new columns which is not there in Database A.
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Harish Mohanbabu" wrote:
> Hello,
> I need to copy from around 9 specific tables in one database (A) to another
> database (B). Database B contains an exact image of those 9 tables.
> Here is my requirement -
> 1) Altogether there could be around 4 million records across those tables.
> 2) The data would be moved from A to B every night.
> 3) Using SSIS is not an option as I would not be able to call this from Ax.
> But I will
> be able to call stored procs, statements etc.
> Could anyone suggest me an optimum solution please?
> Many thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

copying a database in the same SQLserver instance

Hi

Im using SQL server express and Management studio express, and i have a database attached called database1.

Now i would like to have an exact copy of this database, named database2.

I would only need the tables, and not the data in them, is there somehow i can do this?

There is no copy button in managment studio...

Create a new database called Database2, make Script of the database1 and open a query analyser window, change the database in the dropdown combo to datbase 2 (or run Use database2) and run the script

Steps to script database

(a) Right Click on DB

(b) Tasks Generate SQL Script and follow the instruction. You will get script without data.

Madhu

|||

Thank you for that..

I have no idea what it did with those scripts and such, but it made a perfect copy like i wanted..

Copying a cube but using a different data source

We have a cube that we've been using for a while. I need to make several more cubes with the exact same schema and roles and everything else...just with different data sources. The other data sources have the same schema as the database the original cube is using. I know that when you copy a cube and paste it, the data source is copied over too. And I've read that you can't change the data source of a cube.

My question is what is the easiest way to create the new cubes? Or do I have to create every new cube from scratch (pain in the butt)?

Thanks in advance for your advice.
After you made a copy of your project, create a new DataSource to point to your new relational DB. Open DSV in Xml Mode, change the tag <DataSourceID>...</DataSourceID> to point to your new data source ID.|||Thanks Ken. We're using AS2000. Is there a DSV equivalent?
|||Oh AS2000 is total different story then. There is no DSV concept in AS2000. You can try changing the connection string in the same data source rather than switching the data source on the cube.|||Yeah, I thought about that but we have another cube using the data source so that option is out. Looks like I will need to create the each new cube from scratch. : (
|||Go to every partition of this cube, edit it, and then change the data source. This should do a job.|||Thanks Sasha!

Copy to CD

Hi there,
Is there anyway to export an exact version of a database to CD so i can then go to another site and import the image of the database onto their server. Ive tried but cant find the best file format and also i keep getting problems such as auto increments d
ont get carried across in the table design. Hope someone can help me out.
JP
James,
if you mean from the point of view of replication, have a look at
"subscription databases, copying" in BOL. If this is a general query then
it's just backup and restore.
Regards,
Paul Ibison
|||is you are doing a subscription copy make sure your db is under 2Gb.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ez1re6lGEHA.1228@.TK2MSFTNGP11.phx.gbl...
> James,
> if you mean from the point of view of replication, have a look at
> "subscription databases, copying" in BOL. If this is a general query then
> it's just backup and restore.
> Regards,
> Paul Ibison
>