I have database1 and database2. Most table names are the same between both dbs.
I would like to copy a column from db1 into db2 but since the data requirement on db2 is different i would like to change it before inserting.
Example:
Data from db1: <SomePage id="1" <template>This is a test </template>
When it saves to db2 it needs to be
<Page ident="1" <content>This is a test </content>
Is this possible at all?
If I want to copy the data from Table1 in Database A to Table2 in
Database B but Table1 column name is code , Table 2 column name is
vesselcode.
(Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ?
Do I need to write the SQL statment ? and Can I use Server Enterprise
Manager Tool?Thx a lot.
Hi Team,
We need to get the data from our third party CRM vendor. CRM vendor will provide us with .txt file on their folder each night and we need to get those data into our database. I know that SSIS should be able to do this for us. Is there any tutorial of how to do this job in SQL Server 2005? Thanks in advance.
Hi,
This requirement is for our development and test environment. We have a database server which we use for development hence many changes are done on the database objects on daily basis like tables def changes, view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server where they perform testing hence any internal test release would need the changes like tables def changes, view def changes, sp changes, new sp etc.
Since testing team already have their test data set in the test environment hence they want to keep their data but new test release should only provide with datbase object changes. The difficult part is, we have over 500 tables and about 1000 views and somewhere about 400 stored procs, so its not possible to manually perform the above task.
I dont do database admin job hence clueless as what would be the best way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
cheers!
d2
Hi all,
We have a website with a database connected to a SQL server. I need to copy that database without the data onto a CD so I can place the program on another server?
Any help would be greatly appreciated.
Hello all,
I'v got a database full with an administration. This database needs to be protected and can not be accessed from the internet. Now we are making an internetpage for the employees and i want to read from the database who's working here.
For the security i want to copy some database data from one database to another... Is that possible? With a script ore something?
Thanks i advance!
Willio
How do i make a new database file and then copy all the tables and data out of an old SQL file into it? I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file.
brandon jelinek
www.ccgspokane.com
I am working on a migration project.
Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.
However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.
An example table.
The following is the script created by SQL Management Studio if I right click and script the table as CREATE.
CREATE TABLE [dbo].[tbPRO_Package]( [PRO_PackageID] [int] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()), [CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()), [GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()), CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED ( [PRO_PackageID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Note that the field PRO_PackageID has an identity, and is a primary key.
Also note the constraints on several fields and the default values.
If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script
CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] ([PRO_PackageID] int NOT NULL,[PackageCode] varchar(8) NOT NULL,[LKU_VatRateCode] nchar(10),[CancellationCharge] decimal(18,2),[CancellationMultiplier] decimal(18,2),[CreatedDate] datetime NOT NULL,[CreatedBy] varchar(50) NOT NULL,[ModifiedDate] datetime NOT NULL,[ModifiedBy] varchar(50) NOT NULL,[GUID] uniqueidentifier NOT NULL)
We've lost the identity, the PK,the constraints and the defaults.
If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.
Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?
I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.
I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.
How would I take just one table from my test database and add it to my production database without retyping the design?
Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?
I'm looking to create a new version of a database but with completely reloaded data - so I want to retain the database structure but none of the data.
Is there an easy way (read: without a DBA on staff) to copy an existing database structure - preferably with views and stored procedures - and not take the data along for the ride?
Thanks in advance for suggestions...!
Hello everyone. i am using vwd 2005 express edition along with sqlexpress 2005. if i need to use database in my project. i click on database explorer and create a new table from the existing database. it is working very fine. But my problem is when ever i copy the project in my pen driveand try to use in another computer, the database doesnot gets copied when i copy my project folder. well i also tried to copy the tables from the database explorer but it does not happen.i tried to locate where does the database get stored in my computer.but i cant find where it resides..and even if i find which file to copy?i think there is log file and database file in some cases. can some body clarify me this scenario here.just tell me where can i find my database i m using in my project,so that i can copy it . and also whats the use of creating .mdf file in the project.?is this the better way of using database rather than creating using database explorer?confused?? thanks.jack..
View 10 Replies View RelatedHi there.
Here is my problem: I'm trying to copy a few tables from an ODBC database located on a PC on my LAN to a SQL Server on a remote server.
To do this I have created a DTS package on the SQL Server which runs smoothly, copying the data as it should, but only as long as it copies the data from the ODBC database on my own computer where I have Enterprise Manager installed. The moment I try to copy data from another PC in the network where Enterprise Manager is not used to execute the DTS package the task fails to run.
I'm quite at a loss here and would very much appreciate a notch in the right direction. Perhabs some source or book which I may read to solve the problem.
/Bakkegaard
Is there a way to copy the structure from one database to anotherwithout affecting the actual data?For example, I added new fields and stored procedures to a db in mydevelopment environment. There are a lot of them.I now want to make the same changes to my production environment db butdon't want to affect the data. Only the fields, stored procs &constraints and stuff pertaining to the structure of the db should bechanged.Does anyone know of a program that can do this or can this be done inMS SQL Enterprise Manager.Any help would be appreciated.7078895
View 2 Replies View Relatedi want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005.
can any one help
plz reply
tnx alot
I want to be able to reproduce my production execution plans on development with copying data.
Hi,
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
Thank you,
Aldo
I work on a copy of SQL Server Express on my desktop. After modifying and creating views and user defined functions, I would like to copy and paste them into the working database. Is there a method programmatically of doing this or must I copy and paste the t-sql language from the existing view to the new database--then save the new view on the working database?
View 6 Replies View RelatedHi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
View 1 Replies View RelatedHi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005
Hello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies View RelatedI have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
Hi,
View 6 Replies View RelatedHello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
View 1 Replies View RelatedOk, here is my dilemma
I have an application that has many sites. Each site has it's own database. The databases have common tables (ie the name and fields are the same) What I want to be able to do is when creating a new database, I want to be able to copy certain common table data from one database to another. I have run into an error because the table have an IDENTITY so this is not working
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
I also tried
USE ADMS2_RSCS
GO
SET IDENTITY_INSERT Containers ON
GO
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
GO
SET IDENTITY_INSERT Containers OFF
GO
I got an error saying that I need to have a column list. I am trying to use this for any tables, so my question is this..
Is there any way to get around using a column list or is there a way to dynamically create the column list?
Or, Is there a better way that I should be doing this?
Please keep in mind I am not a dba and everything I have learned about SQL is from my good pal Google :)
Thanks for any help
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedI am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.
DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full
regards
Phil
Hey,
I am trying to give a user permission to change the name of a database.
They are a member of the server role 'secuirtyadmin'
They are a member of the database role 'db_owner'
They have been granted ALTER permission on the database.
But every time I try and run the command
ALTER DATABASE <dbname> MODIFY NAME = <newdbname>
I get an error saying the database doesn't exist or you don't have permissions.
What am I missing?
Hello,
I have Test database with Log file property Automatically grow the file, option Unrestricted file growth, I wanted to alter it with Restrict file growth upto 200 MB. I'm getting an error that you cannot modify as file doesnot exist
But the file does exist. I cannot figure out what is giving the problem.
below is the sql statements
select name from sysfiles
go
Test_Data
Test_Log
USE master
go
ALTER DATABASE Test MODIFY FILE
( NAME='Test_Log',
MAXSIZE=200MB )
go
ERROR
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'Test_Log' does not exist.
Any help is appreciated.
Thanks
Sejal
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des