I have a table on one server that I would like to copy over to another server. I have done the full backup and restore, however that seems to overwrite the formatting on all of my existing tables. As there are some differences in the tables between servers, I would prefer to not have to do the full restore. I have been able to use the SQL export utility to copy a table from one database to another on the same server, but I was wondering how that can be done between different servers with different Windows logins.
I am currently woking on transfering the table contents in sql server in an csv file i have created a stored procedure which would do same but the prob i am facing is the data in the table is not clean it contains tab,newline etc so i had to clean the data i had applied the folowing procedure
set @columns='' --'@columns+''replace(replace(replace(''+column_name +'',Char(10),''''''''),Char(13),''''''''),Char(19) ,'''''''')''' --print @sql select @columns=@columns+'replace(replace(replace('+colum n_name+',Char(10),''''),Char(13),''''),Char(19),'' '') as '+column_name+ ', ' from
[code]...
the table_name contains around 300 columns with the column_name of min 20 characters .When i run the above query i get only few columns instead of all the columns so i tried to find the length which gives me the result as 4000.I had declared @columns as Varchar(8000) i dont know why this issue is coming up?? is there any other way i can clean the data an the transfer it into the file
- INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 - what i want is i INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL4, COL7 FROM TABLE1 Username (add one more value exp: Username)
Hi I'm new to SQL server. What I'm trying to do is to automate the import of data from an Excel spreadsheet to a table within SQL Server. The index of the table has a seed set to increment by 1 and I assume that this is the same as the Autonum datatype in Access. When I import data I miss out this field as it is autogenerated, but the DTS package fails as it is trying to put a NULL in to this field. Where am I going wrong....is there something that I should change on the DTS import?
I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer.
I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming) i need to use this query on access database
which will when run create 4 fileds ABC.mdb Sorce_Db_Id_Col
Attribute_Type
Attribute_Value
Query_Flag
And sorce database Indentity_col Att_typ Att_value Att_catg
I m very new in this stuff. so please please any one have any sample code for this then send me ! please i realy need it My email id is ripal.parikh@softwaresolutionsindia.com Thanks lot!!!!
If TABLE2 has a data in resource 01-05 that isn't in resource01-05 of TABLE1 then I want to added it to the next free slot where ref is the unique key.
Hi All, I've got a problem transferring objects between SQL Databases. The source database is an SQL 2000 dbase, and the destination is a local SQL2005 instance. When trying to transfer I get the error: 0x80040E37 (table) does not exist at the source - but it does.
I am guessing its a schema related issue, I've tried setting the copyschema setting to true, but no joy. The copyallschemas function doesn't work with an SQL 2000 box so its set to false.
I appreciate this may appear to a bit of a noob question but bear with me! ;)
I'm having a problem when I transfer my locally developed MS SQL server db to my hosts server.
I am logging into the host server and then importing the tables and data from my local machine. All the tables and data then get transferred alright but the ID colums lose the primary key and identity setting and any default values in the table structure also get lost.
This means I then have to go through each table on the host server an put things straight. A pain in the bum... I've tried creating the table structure using a SQL script which keeps the ID colum but not the default values.
I am looking at a way to transfer data from a SQL server into an AS/400. If possible can the data be continously transferred to the AS/400 or are there time constraints that will limit it to say once every two hours.
I have been asked to make it possible for our SQL 7 server to pull infromation from AS/400 and utilize it in web applications, and such. Is there any way of doing this? I have heard that you can export the data on the as400 to a DB2 file, and import it with SQL. Is this the only way of access info off of the 400?
(I don't know if this is the correct place for this topic, if not, then please move it)
I have to make a historisation script, and I need to transfer data from a database on one server to a database on another server.
I.e. from table T1 in database D1 on server S1 to table T2 in database D2 on server S2
I saw there is something like sp_addlinkedserver, but to be honest, I don't get the syntax correct.
Both servers are SQL2005 servers (but I will have to go from SQL2000 to SQL2005 in the future) and the migration script will be runnning at the source database.
Can you guys help me out on the syntax, or else provide other suggestions on doing this kind of transfer?
Hi,I have an asp.net (C#) project running perfectly on 2 machines running SQL Express.I need to transfer it to a machine running SQL Server 2005 (I don't believe it has SQL Express installed). I've setup the database on the new machine by right clicking 'Data Connections', Selecting my machine as the server name and attaching the database file from the other machine in the App_Data folder. My connection string is as follows: Data Source=My Machine Name;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Initial Catalog=ASPNETDB.mdf;Integrated Security=True It compiles fine, but when I try to access the profile object from C# (e.g. Profile.County), it fails with the following error: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"Line 19: public virtual string County {Line 20: get {Line 21: return ((string)(this.GetPropertyValue("County")));Line 22: }Line 23: set { N.B: This only seems to occur when I try to access Profile properties. I can access and update the tables I've created just fine, and I can access the user details tables created by Visual Studio just fine too (e.g. aspnet_Users).I understand the profile properties are stored in the database, am I missing a setting somewhere?Hope you can help.James Dimmock
Hi all, I am working at insurance company that using AS400 as it main server. All transaction data is already kept for about 10 years (or even more..). This data is growing larger from time to time, and after several upgrades (that cost a lot!), my supervisor has an idea to partly move the data from AS400 to SQL Server. (since the cost for upgrading sql server is cheaper than AS400).
So.. let say, we only want to kept data in AS400 from 4 years before until now (2004 – 2007), and the rest of data is kept in SQL Server.
So first, all transaction data from 1997 – 2003 is transferred to SQL and deleted in AS400. If user queried data and didn’t found the data in AS400, it will search the SQL, if data is found in SQL, then data is transferred back to AS400 and deleted in SQL.
I’m using SQL Server 2000 DTS (use HiT OLEDB) to transfer the data from AS400 to SQL and vice versa.
I wanna ask if anyone has done this before? What’s the difficulties by using this approach? (btw, I will implement the DTS using user control in vb.net)
Or anyone has a better solution to overcome this problem?
I am transferring DB2 data to SQL server 2005 in ssis. the connection i I am using is the IBM OLEDB driver for DB2 which uses the ODBC connection on the machine. The issue is that while transferring data i get errors reg unicode vs non unicode data. the db2 data is showing unicode data, but when i see the datatype in the db2 database it is a varchar. so when i transfer the data to a varchar in sql server 2005, it errors out....meesageis unicode vs non unicode data. Why is the data from db2 coming across as unicode string. Also the transfer works fine in SQL server 2000, why is is not working in sql server 2005. Please help !!!!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1. Both has SQL server 2005 running and has visual studio 2005 Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server. Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this. Thanks George
Hi,Ive been building a project on a local server and now need to transfer the files and database over to the live server. I have managed to transfer the files quite easily, however I am having a few problems transferring the database using Microsoft SQL Server Management Studio. When I try importing data to the live database, all the data and tables are brought across however some of the values for the fields are lost. For example all the fields which had a primary key no longer have one, and all the set default values for the fields are also lost. Ive been considering just going through every table in the database and re-entering the correct values for the fields, but this seems a bit time consuming and I'm also concerned about possible errors which could arise as there will be an inconsistency.Another method I tried was copying the SQL query across which created all the tables and the correct values for the fields. However when I tried to import the data an error was produced as you cannot import data into a table which has read only fields etc..Does anyone know a solution?Thanks for your time.
I have a web app and database created in sql server 2005 express. In addition to using the membership and role providers I have added custom tables and stored procedures to my db. When I try to export this with Sql Server Management Studio (not the express version) to my db on my shared host(sql server 2005) the custom tables and stored procedures are not transferred. How can this be accomplished? Thanks in advance.
I need to transfer a database (contining one table) containing over 35 million records from one server to another. I have two options at present : (a) Use DTS to do the transfer (b) Copy the mdf file across and sp_attach_db it
Does any1 have a better idea, or does any1 know which of the two methods will be the quickest?
How can I move data from an external source to SQL-server 6.5.
If you for example has an oracle database on a UNIX or VMS-system and want to move table data from a table in the oracle database to the SQL-server database.
There must be plenty of tools to do that via ODBC.
I have existing domain users with SQL logins and having different set of permissions, we are migrating to new domain, and we have new user ID's with new domain. Is there any way through query I can create an user and copy the permissions.
I have a requirement to pull data from several large tables residing in a Postgres 8.x (PG) database into our SQL Server 2005 warehouse every night. I have been doing this via data dumps to a file then bulk loading said file into SQL2005 but I am running into character encoding issues and newlines embedded in some of the strings which is causing the bulk loader some problems.
I would like to take advantage of the power of SSIS to move the data. I cannot find a current working OLEDB driver for PG and I cannot find an ODBC Data Flow Source in SSIS to use the ODBC driver that is available for PG so I am stuck.
If someone would be kind enough to shed some light on whether this is possible and if so where I could get the instructions on how to do it I would be most appreciative.
I am having an issue while transferring some tables from sql server database to oracle schema. The table has over 150 million records. I have created a view referencing that table and am transferring the view. I am using "Oracle Destination" data flow destination using Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services. I am repeatedly getting the following error:OCI error encountered. ORA-12571: TNS:packet writer failure The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.Â
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit 2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard 3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
Hi, I have to transfer data from JDEdwards source system to SQL Server 2005 using SSIS. Is it possible for me to connect to JDEdwards directly from ODBC Connection Manager provided by SSIS. If not then what is the way to implement this
I am using MS Access 2003 SP2 to maintain some data tables. I use SSIS to transfer them to SQL Server 2005, Enterprise Edition.
When I run the SSIS package from within Visual Studio 2005, the package runs without error.
When I try to run the same SSIS package by double-clicking on it in my File System (which invokes the Execute Package Utility, Version: 1.0) none of the tables get copied. Instead all I receive is a message for each table,
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).
The only data conversion I perform is double-byte characters to single-byte characters.
Bob Bojanic, MSFT, made a few suggestions about this in another thread -- but I have created this new thread to help focus on this specific issue. In particular, he asked if we have installed the complete SSIS support for SQL Server 2005, Enterprise Edition, and my network support and database support staff assure me that such complete SSIS support was installed.
Are others having this problem?
Dan
(I just took a look at some of the transformations in the Data Conversion task, and many of them are using an Output Alias identical to the Input Column name. Might that be causing the problem? I will try changing the Output Alias for some tables and see if they then transfer correctly. The "identical name" Output Alias values were created by the Migration Wizard for a DTS 2000 package.)
Hi I created some databases on my pc at work (using Visual Web Developer 2005 and Microsoft SQL Server Express), I now wish to move them onto my home machine, Can I do this by simply copying the .MDF files and then moving them into the App_Data folder of my new app? Do MDF files contain everything (schema, data, stored procedures etc) or am I missing something? Cheers, Adam
Currently I need to transfer existing database to another server. Unfortunately, after restoring all database file, user information is not available. It is stored in Master database.
I would like to know how can I transfer user login, database access, store procedures to another server ?
In Sql 6.5 there was an option to Transfer the db. I used this quite often to make a duplicate test db of my production db. Under 7.0 I haven't found it so easy to do this. DTS wizard hasn't worked well for me. I can get it to transfer tables but not all of the objects. Any ideas? Thanks.
