I have a subset of data whose source of truth resides in an Oracle Database. Can I define a SQL Server View to retrieve this subset of data and make it available on the SQL Server side for say Reporting and SSRS? I think I also saw where they tend to prefer User-Defined functions as opposed to a view.
If I define the view using an OPENQUERY, I have noticed that it seems to run FOREVER on the SQL Server side and returns pretty timely if I run in Oracle SQL Developer. Is there anything I can tweak to improve this timeliness or lack there of? And the whole reason I'd like to create a view of this Oracle data is so that I can take advantage of all the views and functions that we have created on the SQL Server side.
I am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:
Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found". Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT * FROM Table WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example: ID - Name - State 1 - Jozef De Veuster - Mad 2 - Piet Husentruut - Not Happy 3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT * FROM PeopleStates WHERE --Some stuff-- AND CheckUserExclusions(SomeID) And CheckUserExclusions will translate to "NOT (ID = 1)"
I want to view all data that have a date of today, but my query is not returning them? Is it due to the actual data being a datetime and I am not accounting for time? How should I set this query up so that the data returns?
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
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 ?
hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard. but my requirement seems can't be done by DTS wizard. here are my requirement below. [move online Database to offline Database ] 1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..) 2. we will have only 2 kind of preserve time one is 6 months another is 12 months 3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data 4. We will have to reference the history data preserve time to delete data after finished data movement those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?
I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.
Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.
I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?
Sorry for the basic questions, and I really appreciate any help.
Hi,I have created a linked server to oracle, which works fine.But when I try and create a view joining the SQL table with the linked Oracle table, it only returns the primary key field in the Oracle table and nothing else.Anyone know why?ThanksN.B. I'm using SQL Server 2005 btw.
Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.
I have a number of scripts that run against an oracle server to retreive data to manipulate before populating a sqlserver database. I am connecting using an Odbc connection and using the DataReader Source to read from the Oracle table. It all seems to work ok.
When I have tried to extract data from an Oracle view in the same way it doesn't seem to work. The error message mentioned the PrimeOutput() method and error code 0xC02090F5. The view is very big so is it possible that the connection simply timed out?
I am wondering if there is such thing as primary key for a view? Assuming, that my view is based on a single table and it's just a subset of columns (some of them renamed) including PK of the table it's based on.
The reason for my question is this - we're using Reverse POCO generator which automatically generates C# Model class and Configuration file for our tables and views. For the view is lists all the columns as a key and therefore I obviously can not use normal way of updating that view. I posted that as an issue here [URL] .... but I am thinking there is no such thing as the "primary key" for a view.
The query used to generate the classes is extremely complex already but may be it can be modified to get the PK ?
SELECT [Extent1].[SchemaName], [Extent1].[Name] AS TableName, [Extent1].[TABLE_TYPE] AS TableType, [UnionAll1].[Ordinal], [UnionAll1].[Name] AS ColumnName, [UnionAll1].[IsNullable], [UnionAll1].[TypeName], ISNULL([UnionAll1].[MaxLength],0) AS MaxLength,
[Code] ......
I made a quick Google search and found this [URL] ....
It sounds as an interesting idea to try although I am not sure it will work with POCO Generator. But I'm going to try it now anyway.
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.
I have a view that takes 14 mins to return 3000 rows. At some points in the execution plan, there are over 4 million rows. The query for the View has a Distinct, a couple columns are populated by functions, there is a cross join, there is a join on a table with no keys or index, joins on non-key varchar columns and so forth. It looks a bit like this:
SELECT DISTINCT TOP 100 PERCENT N.Id, N.CountyName, N.OperationName, N_LO.Name, N_LO.LastName,N_LO.Address1, N_TO.Name, N_TO.LastName, dbo.fn_Sections(N.Shape, s.Points) AS Sections
[code]...
The DISTINCT is in there to get rid of the extra Bob rows, because Bob has many sizes. I need to keep the multiple sizes on Bob's row, but I don't want multiple rows of Bob. Is there a more efficient way to do that other than using DISTINCT?
Second, word on the street is that functions are best avoided if you are going for speed. I don't see a way to use "FOR XML PATH" outside the function, because I can't get it to work on just one column. So I am looking for a way to produce the multiple values on one row.
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this
Create Table [dbo].[BuildingInformation] ( ID INT IDENTITY NOT NULL, BuildingName varchar(50), RAName varchar(50)
[code]....
Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change. If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table
How to launch Oracle SQL Developer through "Run" window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS Select M.CUSTNMBR, M.ADRSCode, M.Contract_number, M.MaxWSCONTSQ, M.Equipment_id,
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups). The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:
