Transferring Data From Postgres 8.x Databases To SQL Server 2005
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
ADVERTISEMENT
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
Oct 22, 2007
Hi,
I have created a package where Iam transferring data from oracle to sql 2005 server and I am getting below error message.
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E10. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E10 Description: "ORA-01008: not all variables bound ".
The query which I got from oracle team has the following note
---- NOTE: Replacement Variable is required. &3 is the offset you wish to run (0 to -11).
The query is having a & sign in where clause.Part of the q is displayed below
select -------
where -----
and TMP.rel_mth_nbr = &3.
View 1 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
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
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
Feb 15, 2002
I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?
Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.
View 2 Replies
View Related
Jan 11, 2000
I tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.
The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.
How are the SQL Server logins transferred ?
Richard
View 2 Replies
View Related
Apr 1, 2008
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
Stephen
View 1 Replies
View Related
Apr 23, 2007
Hello! I'm new to these forums.
I have been trying to replicate from Sql Server to Postgres using Enterprise Manager and the Postgres ANSI ODBC driver version 8.02.02.
With enterprise manager, I was able to copy tables over when I used the option to "DROP existing table and re-create it," but columns of data types datetime and decimal were copied as varchar.
So I tried drop the tables manually in Postgres and then create them with the proper data types (timestamp without time zone, and numeric). I then tried changing the article option to "Keep the existing table unchanged" and I get the following error from the Distribution agent: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
So I then tried using the article snapshot option "Delete all data in the existing table" and I got the same error.
Anyone has any luck replicating in this fashion? Or should I maybe go about it differently?
View 17 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
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
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
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
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
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
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 18, 2004
Hi
Can anyone help me with this.. driving me nuts.
when setting up a linked server to Postgres 7.3.1 (specifics*) I can see the tables in Enterprise Manager but in Query Analyzer i get this error when doing the below queries
SELECT * FROM OPENQUERY(POSTGRES_1,'select * from dbo.TestTable')
OR
SELECT * FROM OPENQUERY(POSTGRES_1,'select * from TestTable')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select id,value_char from dbo.Test_Default_1'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=select id,value_char from dbo.Test_Default_1'].
View 6 Replies
View Related
May 13, 2008
We have a third party application that we will be upgrading.
The current version is using a MYSQL database, while the new version is using PostGresSQL.
We have an external database SQLServer 2005 that currently pulls data from the MySQL database into the SQL Server.
I now have to setup the same pull but from PostGresSQL to the SQL Server, how would I go about doing that?
Is there a driver I would need? Ports that would need to be opened?
Thanks
View 7 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 24, 2014
Looking to pass in the @targetDbName into the Open Query.
The target DB is PostGres and requires 2 single quotes around the dataset name.
I have tried many possible variations using the '+ @variableName +'
USE JonathanDB
declare @dqzDateVer int;
declare @targetDbName varchar(25);
select @targetDbName = DB_NAME();
select @dqzDateVer = dqz_date_ver FROM OPENQUERY(ofr_meta_db, 'select dqz_date_ver from ofr_registry.dataset_feed_state where dataset_name=''JonathanDB'' and state = ''Done'' order by row_iddesc limit 1');
print @dqzDateVer ;
print @targetDbName;
View 1 Replies
View Related
Jan 28, 2007
Trying to transfer databases from MSDE to SQL. When I use SQLEnterprise Manager - DTS - import or export, it works for the firstdatabase, but then fails for the others. I select the to and fromdatabases using DTS - Import - 'MS OLE DB Provider for SQL Server',then select 'Copy objects and data between SQL Servers'. It failswith the generic error message: 'Copy objects failed', nothingfurther. I tried to just copy data, and it doesn't work totally. Ontwo databases, it said that it copied everything, but when I go inunder Enterprise manager, some of the tables are incomplete, i.e. nodata, table shows up in the list, but if you try to look at it, itsays that it is missing or empty. Also, one table was not evencopied. In the other two cases, I get errors, does not copy alltables. I get errors, like insert fails, but the column it referencesfor the table does not even exist in that table. Any suggestionswould be appreciated.
View 2 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
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
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
Aug 14, 2005
Hi! I am importing data from an Excel file into SQL. I am getting the following error message: Error during Transformation 'DirectCopyXform' from Row number 32. Errors encountered so far in this task: 1
Destination does not allow NULL on column pair 1(source column 'F1'(DBTYPE_R8), destination column 'agentno' (DBTYPE_14))
Anyone know why this is and how to correct it?
Thanks for any help!
Scott
View 18 Replies
View Related
Nov 23, 2006
Can anyone give me some ideas on how can i transfer data from access database to sql 2005. It is bit complicated any help will be highly appreciated.
View 1 Replies
View Related
Dec 1, 2006
I am having immense difficulties trying to transfer tables, sprocs, udfs, etc, from a production SqlServer2000 server to a testing SqlServer2005 server.
Under SS2000 this was easy, using the DTS wizard. Apparently that's no longer an option.
I've tried the Transfer Database, Transfer Sql Server Objects tasks and the wizard in BIDS, and they all fail (for different reasons).
Help!!!!
- Mark
p.s. -- As a side question, why in the world did you guys get rid of the one workhorse tool in Enterprise Manager that made synchronizing databases easy?
View 5 Replies
View Related
Feb 27, 2007
- 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)
View 2 Replies
View Related
Apr 28, 2004
Hi,
This question may be a bit naive but I have some data that I would like to transfer from a SQL 7 database to a DB2 database on Linux. Does anyone know how I would go about doing this. I have in the past transferred data from SQL 7 to Oracle and SYBASE using DTS without any difficultly. I cannot remember exactly but presume they were on NT.
Anyone got any ideas/suggestions.
Many thanks
David
View 1 Replies
View Related
