I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?
I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
I am running SQL Server 2000.
I have about 300 tables scattered across 3 databases that I am trying to consolidate into a single database. There are other tables within these 3 databases that I don't wish to consolidate (so I don't want to copy the full database).
What are my options to move these tables to the consolidated database?
Are there options beyond, the pain and extensive run-time of copying from the DTS wizard into the new database (plus the indices won't copy will they?) or writing code that creates tables in the new database, populates them with data from the old tables and then deletes the old tables?
My understanding is sp_rename won't work with two separate databases --i.e., your destination db must be the same as the db of the object you are renaming. (if my understanding is wrong, that would seem to be the easiest way to accomplish this)
Thanks,
Is there any way to import a VistaDB database into SQL Server? From what I can tell, there is not...
Please advise.
Thanks,
MC
I have SQL Server 2005 on a DEV box and I have a linked server on that box.
It has several logins that I would like to move over to our production server when I re-create the linked server.
Now I can export the script to create that linked server, but when I do so it doesn't provide me with the user's login password.
Is there a way to export the password as part of the script so I can move the linked server AND all the users associated to it over to a new server?
I have an existing SQL 7 database with data and stored procedures. I would like to move it to another NT box that is more powerful...
What's the best method of migrating all the data and sp's from my existing server to a new one?
I'm looking forward to learning about SQL 7.
Thanks a lot!
Scott
hi all.....
iam trying to move or export database tables between two sql server 2000 databases..but i always lose the relations between the moved(exported) tables...
please tell me how to export or import tables between two sql 2000 server databases without losing relations...thx for ur time... :)
plz replay me quick guys...... :(
I have migrated databases and logins from SQL server 2008 R2->SQL Server 2014. Now I also want to migrate/copy the "linked servers". Do you have a step-by-step for this, so that i will copy everything that is necessary regarding logins etc ?
View 2 Replies View RelatedI'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.
For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).
Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).
Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).
Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.
So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Hi,
I want to know how to assign a normal user to take backup,Restore and attach and detach the database in sqlserver2000.
Please can anyone help me to find out which server role or database role will suites my requirements.
Regards
Md S.Hassan
Hello All,
I do have one large table, say "emp" having 80 columns. now as the requirement changes, i have to partition the "emp" table to 8 tables.
I want all of my existing data ["emp" table data] to be there in my new tables . i don't want to delete the existing data from "emp" table.
Cal any one please help me out to resolve this issue.
Thanks
Prashant Hirani
hi,
I have a access database. In this database there are 10 tables and this tables are related to each other.
I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
how can I do this?
thanks..
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I have to move all the tables in a database from one file group to another file group.All my tables have millions of records and the indexes are in correct file group but not the tables. How much time will it take to complete the whole process ?
View 13 Replies View RelatedI am trying to move data from 8 different tables that are dependent on each other through foreign key relationship.
Basically they have millions of rows in each table and they have data for the past 5 years. I want to move data for the past 120 days and move it to 8 new tables in the same database. So I created the new tables along with their relationships. Now I need to move in the order (parent table first).
The child table has 50million rows data to move
The intermediate tables have 10 mil 10mil 10mil and 40 mil 50mil and 20 mil rows to move
The parent table has 10 mil rows to move
if I choose to move this data through an SSIS package what is the best way? Or is there a better way to move this data faster?
I will be doing this move only once. After that I have maintenance purge jobs that will cleanup data on a daily basis.
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
View 8 Replies View Relatedhi,
I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.
I am using SQL Server Management Studio Express to connect remotely to the server.
I have 2 databases. Currently all data is stored in database1
I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server
I have already generated a script which creates the schema for the tables on
database2. The problem is moving the data. The web Host does not allow DTS
Here are the things they won't allow:
You can use all features of SQL 2000 (it is 2005) except for the following:
- DTS Packages
- Database Replication
- Mail Services
- XML Support
- Distributed Transactions
- Database Maintenance Plans
- Web Assistant
- Multiple Instances
I can't for the life of me figure out how to transfer the data.
I have tried copying and pasting the data and run into problems with Identity keys and constraints.
I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.
Bottom-line I can't import and export directly. And I desperately need to.
Thanks in advance!
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Currently we update database A everyday with tables from database B.
Database is India and has poor connection speed. What would be the best method of moving this data.
Currently it's about 100 tables and on average its about 900 mb.
Any input is greatly appreciated.
thanks,
I have two databases DB_1 and DB_2 on my server. Both of them have 25 identical tables.
Tables under DB_2 gets refreshed with new data daily and once it's ready with new data, those data should be copied to DB_1 tables (deleting any existing data and loading it with new data).
My question is there any other way other than using Import Data Wizard for moving the data between two databases which included deleting any existing data on DB_1 tables and loadit with new data.
Please let me know...Thanx in advance...
I have two databases located on the same server. I was able to script a identical table in the other database, but how do I transfer the table data there?
View 5 Replies View RelatedI am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?
I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB.
I was thinking using dynamic sql, cursors, and linked servers. Any ideas?
Jim
Hey everyone. I've linke an access database and I am able to query the tables like so:
SELECT *
FROM QFinity...tblEmployees
I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:
SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status
FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;
I get the error "Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo_evaluations'."
I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.
Thanks for any help!
Dave
Windows XP, Office XP
Hi,
I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :
table MOTHER (MOTHER_ID, MOTHER_NAME)
table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)
After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.
My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".
The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...
Is there a solution to chain two insertions with a foreign key constraint in a data flow?
Thanks
Regards,
Arnaud Gervais.
My sql database is in suspect mode, please help me with step by step how can i change the suspect status to normal status, please dont provid eme the links of other site and help me in your own language
iam getting following error:
TITLE: Microsoft SQL Server Management Studio------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ADDITIONAL INFORMATION:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=926&LinkId=20476
------------------------------BUTTONS:
OK------------------------------Thanks in advance
Hai,
I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB
' Set the properties to create the link.
Dim adoxPro As ADOX.Property
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
Hi there!
I am fairly new to SQL Server 7. I have a SQL server database on one machine (box A).
Now I installed a new box with SQL server on it (box B).
How do I move the database from box A to box B?
What are the steps involved? What is the easiest way to do it?
What files need to be copied over?
Thanks,
Helmut
What is the best and safest way to move all databases(almost 500GB total) from our existing server(2000) to the new server(2005).and What about the jobs and SSIS(DTS) Packages.Thanks
View 5 Replies View RelatedGuys,
We are currently moving our environment. I was told that we need to copy all the stuff over. More specifically, we want to move everything except the data, as our data is dynamic and will fill up in a few days by itself.
What is the best way to move everything over from one server instance to another?
My current approach is the following:
1. Create the file groups we have on our current server on the new server
2. Script out all databases with stored procedures, functions, views, priviliges, indexes ...
3. Script out all the jobs
4. Script out all the dts packages (or rather save each in a file)
5. Load all scripts into the new sql server
6. Re-create user accounts (can these be scripted out also and then loaded?)
Am I missing something or is there a wiser alternative?
Thanks a lot
Hello,
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
Thanks
Josh