Transferring SQL Server DB
Apr 27, 2004
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.
Why is this happening and how can I resolve it?
Thanks in advance
Phil
View 6 Replies
ADVERTISEMENT
Apr 17, 2007
Hi Mohan, Would u pls help me in working with the SSIS,
I just wanted to know from where to start,
for e.g...
!.how to Transfer DBF of 3 mb of *.dbf to sqlserver.
2.pls mention step by step.
waiting for ur reply,
Regards,
Vishwanath
View 1 Replies
View Related
Jan 6, 2005
Hello,
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.
Thanks for any help anyone can provide.
View 1 Replies
View Related
Nov 13, 2000
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.
View 1 Replies
View Related
Aug 27, 1999
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?
View 2 Replies
View Related
May 6, 2008
(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?
View 5 Replies
View Related
Jan 21, 2008
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
View 3 Replies
View Related
Jul 31, 2007
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?
Thanks,
[RU]
View 2 Replies
View Related
Jul 2, 2007
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 !!!!
View 11 Replies
View Related
Jun 15, 2007
Hi!
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.
Thanks in advance.
Dan
View 17 Replies
View Related
Dec 20, 2007
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
View 5 Replies
View Related
Feb 5, 2008
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.
View 2 Replies
View Related
Apr 13, 2006
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.
View 2 Replies
View Related
Nov 13, 2000
Good morning one and all,
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?
TIA
Gurmi
View 1 Replies
View Related
Dec 9, 1999
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.
Any suggestions ?
View 1 Replies
View Related
Feb 17, 2012
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
declare @columns varchar(8000), @sql varchar(8000), @sql1 varchar(8000),@data_file varchar(100)
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
View 9 Replies
View Related
Mar 24, 2015
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.
View 0 Replies
View Related
Feb 4, 2007
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.
Thanks.
--sean
View 3 Replies
View Related
Aug 12, 2015
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.Â
View 4 Replies
View Related
Mar 25, 2015
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.
View 3 Replies
View Related
Sep 19, 2007
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
Cheers,
Biplab
View 5 Replies
View Related
Jun 27, 2007
Hi,
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.)
View 10 Replies
View Related
Mar 21, 2007
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
View 5 Replies
View Related
Mar 23, 2001
Hi !
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 ?
Please advise. Thanks !
View 3 Replies
View Related
Nov 22, 2000
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.
View 5 Replies
View Related
Jan 7, 2001
I have to transfer a database to a new machine. I am planning on doing a backup and restore. My question is, will the logins and roles be transferred along with the database?
thanks
View 6 Replies
View Related
Dec 6, 1999
This is the situation: I had a SQL 6.5 server with some databases. I still have access to the .dat files, however, SQL 6.5 will not start anymore. Is it possible to copy the .dat files to the new sql 7 server and somehow get them imported into the new SQL 7 server? Any help would be greatly appreciated.
Thanks
Charl
(please also copy my email address if possible when you reply)
View 1 Replies
View Related
Oct 19, 2005
Hi Everyone,
Have anyway come across any easy method of transfers DTS packages across two servers which are existing on two different networks.
I haven't been able to use the method mentioned in article "DTS How to...Transfer Packages between Servers.." since the two servers I'm trying to move TO and move FROM are on two different networks which aren't visible to each other for my to register from the same box.
Appreciate if someone to point me to a means of doing this or any articles that explain how to do this..
thank you
View 9 Replies
View Related
Apr 6, 2001
Hi,
We have a SQL 6.5 database that was expanded larger than was really necessary. I have created a smaller size database and am wondering what the easiest way would be transfer ALL of the data and objects from the existing database into the new one.
Is SQL transfer manager reliable? Are there any better ways?
Thanks,
Sundar.
View 1 Replies
View Related
Sep 8, 1998
While doing a database transfer between servers (using transfer manager), it does not copy all of my stored procedures. Any ideas?
View 1 Replies
View Related
Oct 16, 2001
How do i transfer DTS packages from SQL 2K server to another? Since the no.of packages is high i don't want to edit them individually and do a 'Save As'.
Thanks
View 2 Replies
View Related
Aug 1, 2003
hope some one can help me
i have a couple of tables interlinked, and in one table it has a column named contact with such info in as S Smith, John Burn.
and i need to split this up, so it can go into a number of other columns in another table, such as initials, name surname.
the best ive come up with so far is, isnt very good.
thanx in anticipation
View 1 Replies
View Related
Aug 10, 2005
Hi! I am trying to import a file from excel into Enterprise Manager. I have found the wizard that does this, but I can't seem to find the table I want to import the file into. What am I doing wrong? Thanks for any help you can give.
Scott
View 14 Replies
View Related
