Our backend database is Oracle 9i, and now I need to write an application for PDA, which is SQL Server Compact Edition powered. Here is my question: Is there any way I can sync data between PDA and backend database? Can Sql server compact edition do this? Any resource?
Thanks in advacne.
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
tia
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata
Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
Hi Forum, Ive been unsuccessfully trying to copy data from one table to another, very simple scenerio!
Table1 ID(PK), FirstName, Mobile, Date.
Table2 FirstName, Mobile.
The first question I have is should I fill Table2 at the insert stage OR should/do I update Table2 from Table1 as the users details are entered. ie create some kind of relationship.
Really appreciate good advice! cheers P
I have two db instances on different SQL Servers. One is my test db and the other the prod. db. Each has two tables that hold lookUp values for the entire application. I would like to keep these tables in sync. in both dbs. The prod. db would be the master. I was trying to design a trigger to insert, update, or delete the appropriate records in the test db as they were added or deleted from the prod db.
I'm getting too many prefixes errors on the script when trying to reference the table on the test db using four part naming convention.
Any ideas on how to resolve this issue? Do I need to set up a linked server to accomplish this functionality? If so, how do I setup security for a linked server?
Thanks in advance.
Oscar
I have a SQL 7 backup file that I restored onto a new server. All the objects within the database are not owned by dbo, instead they are owned by a db user. When I tried to login to the db it said that it wasn't a valid user. So I added the login to the system. Now when I login it says it is not a valid db user. I have attempted to sync the id's but don't know how. In 6.5 I could run an ad-hoc query to match the SUID's for the system and the database. In 7.0 I can not because the suid for the db is computed. Any ideas on how to sync the user ID's so that I can login to the server using the target db as my default and can query the table with prefacing the owner(I can get in if I give my login SA privelages but then I have to preface all the objects with the owners name)?
Thanks in advance for any help that you can offer.
hi,
we currently have a web server/db on-site and a similar backup machine across the country. we're using a pretty bad backup strategy. we zip up a backup file, thats done on the main server every night, then ftp over to the back up server then run a restore.
lately ive been having problems with the main server. i would like to have the machines sync'd up as close as possible.
ive searched the threads and noticed replication and log shipping to be the most practical, but im assuming most of the machines are on the same domain.
what would be my best bet for machines connected over the internet, no vpn?
also the db on the main machine get queried/inserts all day from 7am - 6:30pm
thoughts?
thx
I need to syncronize two separate databases. One is SQL Server the other is MySql. I don't have a lot of experience with MSSQL and need a little advice on how best (in terms of speed especially) to gather records that have been added since the last sync and any that have been modified. The table in question has datetime fields for both, the time each record was created (CreatedTime) and the time that the record was last modified (ModifiedTime). Sync will happen daily.
I have worked out a few ways I might do this with DATEADD or DATEDIFF, but my question is, what is the most effecient way? Currently I'm just looking for a way to get inserted and updated records from SQL Server to MySQL. I may have to do a two way sync of some sort later.
I need to make a sync between 3 or more SQL Servers. We have 3 Small Business server with sql server 2000. In all the 3 locations, branch and headquarters we've the same databate data.
For sync with 2 there's no problem. I need to make it with three and then at the end of the year with four.
Can anyone help me?
Cheers
João Almeida
how do i write a sync procedure that syncs both tables and ensures that Table_2 keeps all services that are listed in Table_1
how do i write a procedure for the sync between both tables, and put this procedure into the package
so basically there have to be two sync parts: First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes.
How would i write this sync procedure?
how do i write a sync procedure that syncs both tables and ensures that Table_2 keeps all services that are listed in Table_1
how do i write a procedure for the sync between both tables, and put this procedure into the package
so basically there have to be two sync parts: First is INSERT of what is not yet existing, second one is UPDATE on the appropriate attributes.
How would i write this sync procedure?
Hi all,
I have three tables and i want to bring all tables into sync.
My problem is that the difference in the amount of records is huge.
CORE -- 85659 This table doesnt have a PK
CRF -- 59895 This table has a composite key (MIN,MAX)
DS -- 58595 This table also has a the same composite key (MIN,MAX)
All the tables have their own UIDs. Additionally CRF has a column which stores the UID of CORE table and DS has a column which stores UID of the CRF table.
CORE --> CRF --> DS
UID --> UID,CORE_UID --> UID,CRF_UID
I have only 3 columns (UID, MIN, MAX) that can be used to sync all the tables. Can anybody help me...?
Thanks in advance....
I want to synchronize two databases. Is there a query or a tool to synchronize them?
View 3 Replies View RelatedHello,I've got 2 databases (both on MS-SQL Sever2k). One of them (remote)contains table which is often updated. For various reasons I'd like tomaintain a copy on local MSSQLServer. Is there any built-in mechanism whichallows to do such things?! I can do this manually, by examining the remotetable contents, and downloading new stuff (local table cannot be modified inany other way), but I guess that there is a smarter way. Any ideas?!ThanksPiotrek
View 1 Replies View RelatedI have tried to set up log shipping for one of our databases - firsttime. Using EM I was able to create the job and maintenance plansucessfully. I wasn't able to find any errors in any logs. In themonitor server, when I look at job history, everything is gettingcopied and loaded sucessfully, yet the STATUS in the log shippingmonitor states it is out of sync.How do I get this into sync? I have set the out of sync threshold to45 minutes. I have made changes in our production database and thosechanges are showing up in the standby database, so it all appears tobe working.Thanks.
View 1 Replies View Related
I have two databases data1(Express 2005) and data2( Enterprise 2005). Data1 is a subset of Data2. Now beginning from that state any changes to either database should be reflected on both of them.
Now what's my best solution to accomplish this considering one is the subset of other??
Thanks
What is the best solution to run a disconnected database on a tablet pc and then sync wirelessly to a SQl Server database?
Thanks
I set up log shipping,
after a while the log shipping pair in LogShip Monitor is out of sync.
what cause this & how to solve this problems.
thx
I have set up a database with log shipping to the backup server. I have actually set it up multiple times because it continues to get out of sync, and I do not know another way to get it back in sync (despite much effort to learn how). I have not found much documentation in BOL, nor in the on-line areas that I can find. I have not been able to discover why it keeps getting out of sync.
If there is anyone who may shed some light on this, I would be very greatful.
Particulars (both servers):
Windows ADV Server 2003
SQL 2000 (Enterprise Ed.)
Servers on a work group (not domain)
There is a separate database on the same servers that continues to have successful log shipping.
Thank you very much (in advance).
what is the work around for Out of Sync error?
What cause this problems ?
thx
I have (finally) successfully gotten through the maze and 'published' my database to a folder:
Physical path: I:SqlReplication
unc: WarrenassocSQLReplication
Since I'm running XP pro, I don't have the options of assigning specific read/write permissions to a folder.
However it appears the problem is with accessing the snapshot and the logon's there.
When I run the IIS Web Configure wizard I error out at the snapshot share permissions step (see report below)
Wizard Settings:
Click Finish to perform the following actions:
Modify a virtual directory immediately.
Copy the ISAPI dll 'sqlcesa30.dll" to 'I:SqlReplication" immediately.
Set NTFS permissions immediately.
The virtual directory 'SqlReplication' will be configured with the following options:
On server 'WARRENASSOC'.
Use 'SqlReplication' as the alias.
Use 'I:SqlReplication' as the physical path for the virtual directory.
Use 'Anonymous' as the authentication method(s).
Do not require secure channel.
Set user permissions to 'Execute'.
Clients can use 'http://WARRENASSOC/SqlReplication/sqlcesa30.dll' as the Internet URL.
'sqlcesa30.dll' will be copied from 'C:Program FilesMicrosoft SQL Server 2005 Mobile Editionserver' to 'I:SqlReplication'.
'WARRENASSOCEd Warren' will get the following NTFS permission:
'Read & Write' on physical path 'I:SqlReplication'.
'Read & Execute' on the ISAPI DLL 'I:SqlReplicationsqlcesa30.dll'.
'Read' on share physical path '\WarrenassocI$SqlReplication'.
'Read' on the share '\WarrenassocI$SqlReplication'.
- Modifying the virtual directory (Rolled back)
- Copying the ISAPI DLL (Rolled back)
- Setting the directory permissions (Rolled back)
- Setting the server agent permissions (Rolled back)
- Setting the snapshot directory permissions (Rolled back)
- Setting the snapshot share permissions (Error)
Messages
The operation completed successfully. (Exception from HRESULT: 0x80070000) (mscorlib)
- Modifying the Virtual Directory configuration file. (Stopped)
When I created the snapshot I used my personal logon (admin rights) at each step
I've tried all combinations of setting the permissions in the wizard from requiring a login and providing my personal login to no requirements and I get the same error.
I'm trying to set up to copy a database from my SQL Server 2005 --> SQl Server CE 31 RC1
to see it it meet my development needs.
I'm at a loss as to how to navigate the security maze!!
Thanks Edward Warren.
Hello everyone:
I wanted to ask a question about log shipping going out of sync. We have a remote server in the UK that is our production server, and we are trying to set up log shipping with another server in the US. Our Log shipping monitor goes "out-of-sync" after about 8-10 hours. We changed the US server to have the same time as the server in the UK, but there still seems to be an out of syn error after 8 hours.
We are using Windows server 2000 and SQL server 2000.
SQL server will restore logs then after a few hours it stops restoring and goes out of sync. Was just wondering if someone knew the possibilities of why.
Thank you.