In our environment a database view is based on columns in a table from an
oracle 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 through
this process.
when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'
select <some fields> from star where nr_jahr_star = :Year
my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server
I've tried various combinations with the parameter settings also with no luck.
Hi, I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database. Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.
Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.
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.
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 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 compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Within a VS 2003 BI project, I can create a parmeterized query against an Oracle 7 database by using a ? for the parameter(s). Example: SELECT LTrim(RTrim(SWO_ISS.REF)) ACCT, CUS_LOC.NAME CUSTOMER, SWO_ISS.TRAN_DATE, SWO_ISS.ITEM, ITM_DESC.ITEM_DESCRIPTION, SWO_ISS.QTY, LSTPRC.AMT FROM MYDB.SWO_ISS, MYDB.CUS_LOC, MYDB.ITM_DESC, (SELECT LP.ITEM, LP.REVISION, LP.AMT FROM MYDB.LIST_PRC LP WHERE LP.EFFEND > SYSDATE) LSTPRC WHERE SWO_ISS.REF = CUS_LOC.CUS_LOC(+) AND SWO_ISS.COMP_ITEM = ITM_DESC.ITEM(+) AND SWO_ISS.COMP_ITEM = LSTPRC.ITEM(+) AND SWO_ISS.COMP_REV = LSTPRC.REVISION(+) AND SWO_ISS.REF = ? AND SWO_ISS.TRAN_DATE >= TO_DATE(?,'MM/DD/YYYY') AND SWO_ISS.TRAN_DATE <= TO_DATE(?,'MM/DD/YYYY')
Unfortunately, as soon as the dataset is saved or Layout view is selected the Field List disappears and all of the report objects lose their data binding. Worse still, if you click the Generic Query Designer button while working with one of these queries Visual Studio will lock up.
This worked at one time and now has me stumped. What may be the cause of the change?
I want to write this query in sql server. Please help me, it is very urgent :(
Any help will be appreciated.
SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT , valid_item.ITEM_NO,DESCPT,UNITS,CONTQ, CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und, nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV, Nz(QUANTITY,0) as quant_rev, round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL, AMT_PAID_ITEM,AMT_RET_ITEM FROM VALID_ITEM, cqe_item,CONTRACT_PC WHERE valid_item.db_contract = contract_pc.db_contract and valid_item.pc_code = contract_pc.pc_code and valid_item.db_contract = cqe_item.db_contract (+) and cqe_item.cqe_numb (+) = :EST_NO and valid_item.item_no = cqe_item.item_no (+) and valid_item.pc_code = cqe_item.pc_code (+) order by valid_item.item_no
I need the basic information about how the query is working in Oracle & SQl Server.
Sicne I am getting different values when I run the query. I have a table called 'Client_Master' in both the DB (ie., Oracle & SQL). I have inserted a record with the below values for Client_Master table. (both Oracle & SQl).
SELECT * FROM CLIENT_MASTER WHERE ID = '200' AND ((START_CODE <= '102' AND END_CODE >= '102') OR (START_CODE <= '200' AND END_CODE >= '200') OR (START_CODE >= '102' AND END_CODE < '200') OR (START_CODE = '102' AND END_CODE is null))
Oracle result : No rows return. SQl Server : one row returned.
Data type for start_code & end_code are 'NVARCHAR2 (40)'.
I would highly appreciate if anyone can explain why is this and any suggestion like how to handle this in both the database.
The subject of this query is the values which is entered by the user should not be overlapped. But I dont how to handle this if it is alphanumeric.
SELECT distinct whse_zone_id, bay_id From prod_geneology WHERE whse_zone_id in ('SH','CU','SG') START WITH unit_id = 'BL7B230811' CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = SUBSTR('85',1,2) AND TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND CSUM_PROPRD BETWEEN '200610' AND '200611' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE) FROM ACC_CUST_SUM as CSUM INNER JOIN ACC_CUST_ITEMS as CITM ON CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM ON CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND (INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
Hi Friends, Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :
select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;
Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.
However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.
please reply ASAP since i am working and need this query to fix a defect.
I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:
select 'AAA-'||OWNER AS SOURCE, table_name, column_name, SUBSTR(data_type,1,50) DATA_TYPE , SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH from all_tab_cols where owner='XXX' ORDER BY TABLE_NAME, COLUMN_ID
Here ARE the following errorS I get when running from SSIS:
[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".
[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.