Hi everyone. I need to be able to query a database in Oracle and then
actually save the data in an MS SQL database, does anyone know what is
the best way to do this? Would it be best to do everything internal
(inside MS SQL) or external (with some script: javascript, vbscript,
perl)?
Thanks in advanced,
Mike
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 all. I'm not sure if this is the correct place for this question, so my apologies if it should be in another section.
I need to build a simple QA (quality assurance) DB for my company (a small physician group). I have the DB built in Access, but would much prefer to put it in SQL Server for reasons of stability, security, and access control.
What I would like to do is build a web interface to this DB so the users can just click an IE shortcut on their desktop, and be able to enter information via the browser like they would in a form in MS Access.
What would be the best way of going about building something like this? My background is as an analyst, so I'm unsure when it comes to actually building a front-end.
Sorry if this question is simplistic or if it has already been answered, and thank you in advance for any assistance you may lend.
how can i down load interfaceor fix the problem?
View 2 Replies View RelatedI am working on the project for Health Care Provider. They use HL7 interface to data sources. Any other interface is not an option. I need to address following scenarios for daily ETL jobs:
Pull analyses results for patients based on certain criteria to my database (SQL2005). Hundreds of results per day.
Verify list of patients against master list thru HL7 and update data elements that has changed. There are millions of patients
I am considering using WEB Services task in SSIS for scenario 1. Scenario 2 is more complicated because of the volume of data. I am considering using BizTalk for this one. Can BizTalk handle this scenario? Is there is more efficient way?
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
I have the following VB function. The stored proc that is being called
("GetList") inside the function does a simple SQL Select statement against
an SQL Server 7 database using OLE DB. I'd like to store the result of the
SQL Select statement in the "Rs" parameter to pass back to the calling
function. Whenever I call this function, I get an error message stating "No
Such Interface Supported". Does anyone know why I cannot do this?
Also, this function is being called from ASP code. That's why the "Rs" is
passed as a variant. It's created in the ASP code before this function is
called.
------------------------------------------
Function GetAddressDropDownList(Rs As Variant, CustomerId As Long)
Dim ProcComm
Dim Parameter
Set ProcComm = CreateObject("ADODB.Command")
ProcComm.CommandType = adCmdStoredProc
ProcComm.CommandText = "GetList"
Set Parameter = ProcComm.CreateParameter("CustomerId", adInteger,
adParamInput)
Parameter.Value = CustomerId
ProcComm.Parameters.Append Parameter
Set Parameter = Nothing
ProcComm.ActiveConnection = BuildConnectionString()
Rs.CursorLocation = adUseClient
Rs.Open ProcComm, , adOpenForwardOnly
Set Parameter = Nothing
Set ProcComm = Nothing
End Function
Is there an interface that I could use in SQL Server to create queries or tables instead of typing codes in query analyzer? Any help would be appreciated.
View 2 Replies View RelatedDear All,SQL-SERVER-DBAccess interface25 usersI want to know how the users are working with the interface:* What forms the use* What routines the run ( commandbuttons)* How long they use the interface* etc.I'll use a public function to add the information.Where should i store the information ? :* in a table in the SQL-DB: 1 table for all users on the server* a text-file for each user on the users computer.What solutions is the quickest?Thanks,Filip
View 1 Replies View RelatedDear All,I'm looking for a VBA-code to run a sql-sript from the Access.adp-interface.I want to send scripts so my client can add sql-server-objects from theinterface.Thanks,Filip
View 1 Replies View RelatedI was trying to burn cds when I got thisfda error message stating Interface not Registered what does this mean and can someone tell me how to fix it
-thanks Lequeta
Hi there,
we have several DWH Load processes running in SSIS, however I want to enable the enduser to have more control and information about the status of these processes. Therefore I plan to write a monitoring interface based on ASP.NET 2.0. However I hardly could find good docu or samples on what is available from within ASP.NET 2.0 to read out or control SSIS.
Anybody having me some good pointers to start with ?
Good morning,
Well... I'm implementing a clustering algorithm following the examples given on the Managed Plugin Algorithms tutorial. As the samples are in C# and I'm implementing it on C++, I'm facing some problems, but nothing really significant.
But I've some conceptual questions about the implementation:
1) It necessary to implement the IDMClusteringAlgorithm interface in order to implement the ClusterMembership() and the CaseLikelihood() methods, right ?
2) The changes needed to be done on the Navigation class have nothing to do specifically with clustering. These changes are only to set calculated statistics to be shown, and if I want, I can simply don't implement them. Is this correct ?
3) Is there any documentation on the parameters received by the ClusterMembership and CaseLikelihood methods ? What's its inputs and outputs ?
Any clues are wellcome.
Thanks a lot again.
Dear Friends,
I'm having a problem...
I need to export all the work that I have done in SSIS, but I dont know the option to do it... If I do a printscreen of the ETLs, I dont have all the layout of the ETL... Do you know how can I get it better to create my document?
thanks!
I downloaded SQL Server Express 5.0 and would like to know - must I open SQLServer through a studio like SQL Server Management Studio Express, or can it open on its own? Does it come with a GUI inside it?
I clicked Start/All Programs/Microsoft SQL Server 2005 and only submenues were displayed, not the server itself. The submenues were
Configuration Tools
SQL Server Management Studio Express.
Thanks.
I was just wondering why when i go to burn a cd it says Interface not registered. If somone could help i thank you.. cause i really want to burn cds again. thanks
View 3 Replies View Relatedneed 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
I did a little bit of looking in the forums for something like this, but I wasn't able to find anything. In essence, I am looking to create a web form in ASP.NET which will emulate the SQL Server Query Analyzer Tool... eventually what I'd like to see:
- A dropdown of different connection strings, to connect to different databases
-A multi-line textbox where a user could type in a query (be it a SELECT, CREATE, UPDATE, or multiple queries at once)
-A gridview object for results
-A multiline textbox to contain server output ("the command completed successfully", "xxx rows returned", "syntax at line 3 not recognized", etc)
I can do the first 3 without any difficulty, but I have absolutely no idea where to start with the server output. Anyone have any ideas? It does not need to be secure, as the only databases they'll have access to will be "test" databases and the pages will not be publicly hosted (LAN only)
Hi all
Does any one know abt the web interface for managing SQL SERVER
I would like to create database,create tables create users, modify, insert data etc.. over the web
i belive there are interfaces are available on the net
can any body tell me from where i can download it
Thanks in advance
Prasad
HELP!!! I've looked at a few, but none of them support insert and update. I hate to say it, but the Access inteface is better, but uses a different SQL than SQL Server. :(
Help,
mike
Hello everybody,
please advice: what is the fastest standard method of user interface access to SQL database? I am looking for fast display of one master record plus related dependent records, plus fast scrolling through master records with display of dependent records as fast as posible. Perhaps a standard problem with standard solution? At current state of matters, I am still much slower then with my old Access97 database.
thanks for any advice,
Otakar Kverka
Prague
In the SQL Object manager tools for sql server 4.21 there was a gui interface for running BCP where you could select tables and fields etc.
I haven't beem able to find abnything equivalent in sql 6.5 enterprise manager. Am I missing someting, or was this dropped?
Thanks.
Jerry Dunn
ViaHealth
jerry.dunn@viahealth.org
I am getting "writing conflict" or "Update or Cancel Update Without AddNew or Edit" errors even though there is nobody else updating the same record. I use Access Form as interface, the DBMS is MS SQL Server 2000 Standart Edition. I did not write any code for the form and its AllowEdits property is set to Yes.
Can anybody tell me what is the problem and how I can fix it?
Thanks... :
We are looking for a way to have sales data to flow from 1 database to another database on same SQL server. Can we build or have a link between 2 database? If yes, how to and how much effort will that be?
View 2 Replies View RelatedI don't want to use SSRS's GUI for parameter entry. It's quite minimal and not very easy to use for users and doesn't cope very well with large selection lists (E.g if you wanted to select 3 companies from a list of 60 000 to run a report for).
So I want to make my own parameter entry screen and then pass only the user parameter value selection on to the report parameters.
Can I do that? Are the parameters exposed?
I'd wanna do something like:
My_SSRS_Rep.param1=@MyValFromMyApp1
My_SSRS_Rep.param2=@MyValFromMyApp2
My_SSRS_Rep.Run/Refresh/View
Currently I just have a Windows form with a web browser control in it.
And I've set the url to http://myserver/reportserver?%2fDB+Report+Server%myRep&rs:Command=Render
So it is possible?
Or do I need to go about this some other way like using the SSRS Report Viewer or some other mechanism?
Answer:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88484
Report Viewer it is then!
UPDATE1:
Or maybe not...
When I do the following, the report does not refresh:
Dim param(0) As Microsoft.Reporting.WinForms.ReportParameter
param(0) = New Microsoft.Reporting.WinForms.ReportParameter("Test", a)
Me.ReportViewer1.ServerReport.SetParameters(param)
Me.ReportViewer1.ServerReport.Refresh()
Anyone know why?