InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:41:22 GMT20http://www.sqlservercentral.com/Forums/Topic1440927-1042-1.aspxHi All,I'm trying to call an Oracle procedure from an SSIS OLE DB source data flow task and have a record set returned so i can write it to a flat file destination.When i try to preview it, i get the error: "No disconnected record set is available for the specified SQL statement."Any ideas? thanks, ScottBelow is the statement i'm using to cal it and the procedure..DECLARETYPE Cursor_Type_2 IS REF CURSOR;precordset Cursor_Type_2;BEGIN avmindev.freedomex.freedomexport( pdate=>TO_DATE('2012-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), pstore_number=>471, precordset=>precordset); :PRECORDSET := PRECORDSET;END;PROCEDURE FreedomExport (pDate IN DATE, pStore_Number IN NUMBER, pRecordSet OUT SYS_REFCURSOR) AS BEGIN OPEN pRecordSet FOR SELECT SUBSTR(f2.quickid,1,4) as StudentID, SUBSTR(t.ticketid,1,7) as InvoiceNum, 'I' as TicketType, SUBSTR(s.quickid,1,5) as ServiceID, TO_CHAR(sysdate,'YYMMDD-HH24MISS') as BatchID, ' ' as Grade, 'XXXXXXXXXXXXXXXX' as Filler FROM TicketData t LEFT JOIN Service s ON t.ItemID = s.ID AND t.Store_Number = s.Store_Number LEFT JOIN Staff f2 on t.staffid2 = f2.id WHERE t.Status=1 AND t."Date" = pDate AND t.datatype = 5 AND t.store_number = pStore_Number AND NOT f2.ID is Null; END; /* FreedomExport */Wed, 10 Apr 2013 10:19:56 GMTsfranzenhttp://www.sqlservercentral.com/Forums/Topic1106110-1042-1.aspxHi all, We get the below error when using order by clause in the select query using the linked server to Oracle table (LS..USER2.UNIT3)[code]SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO, FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T WHERE U.ABO like 'LE%' AND U.CREATE_DT = '18/01/2009' AND U.ABO = L.ABOAND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP ORDER BY U.ABO, U.LED_IDError:OLE DB provider "ORAOLEDB.ORACLE" for linked server "ls" returned message "ORA-01403: no data found".Msg 7346, Level 16, State 2, Line 1Cannot get the data of the row from the OLE DB provider "ORAOLEDB.ORACLE" for linked server "ls". [/code] But the query is successful if we comment out the order by clause & also if we remove the U.CREATE_DT condition in the where clause.. I'm not sure what we are missing.. Please help.ThanksTue, 10 May 2011 07:38:01 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1435405-1042-1.aspxHi Team,Am new to Oracle, i need a triggeri have a table named Stud: Columns : ID, Stud_Name, Another table named : LabColumns : ID, Stud_ Namei want to trigger, when ever any insert or update or delete on Stud, it need to check the Id, Stud_Name in Lab table, if available, then get the ID and udpate status ='Y' where TABLE = "APP" Table : APPColumns : ID, StatusPlease help me....Tue, 26 Mar 2013 05:49:21 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1357764-1042-1.aspxSQL Server 2005 on Windows 7 box. I have the Oracle 11g client installed. I also have an ODBC connection to the Oracle DB and that works fine - used it in Access with no issue. Also can see linked Oracle tables in my SQL Server. I have tried my query with several types of linked tables including MSDASQL - the error I get from this query is (where X is my linked server name):SELECT * FROM OPENQUERY(X, 'SELECT * FROM TABLE')OLE DB provider "MSDASQL" for linked server "X" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".OLE DB provider "MSDASQL" for linked server "X" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".Msg 7303, Level 16, State 1, Line 3Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "X".I have tried selecting and deselecting "Allow in process" and every other solution I can find.PLEASE HELP - I AM BANGING MY HEAD ON THE DESK!Tue, 11 Sep 2012 20:13:49 GMTscott.bondhttp://www.sqlservercentral.com/Forums/Topic1419124-1042-1.aspxHi friends, We are troubleshooting performance on a process that is taking too long. I ran a trace using sql server profiler and it looks like a select query is causing the slowness.. Below select is a join to Oracle remote table using linked server.[code]SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' ) OR (remtab.rfield2 = ''value'' )) AND remtab.rfield3 = localtab.rfield3'[/code]execution plan:select cost: 0 %MErge join (inner join) Cost: 6 %Compute Scalar Cost:0 %Remote Query Cost: 85 %Index Scan [localdb].[dbo].[localtab].[rfield3.idx] Cost: 10 % (non clustered) I analyzed the query in tuning advisor. There are no recommendations for this sql.. not sure if it is because it's remote table. Below are few lines with the highest duration from the sql server profiler trace when the process ran. There are about 25000 records in both the local and remote tables. Process runs this select query in a loop with application logic and other sqls inside the loop. Other sql's seem to execute faster with less duration in the profiler trace. Every occurrence of this select query has high duration time. Select query has bind variables in the process code..[code]TextData:declare @p1 intset @p1=1602323declare @p3 intset @p3=1declare @p4 intset @p4=1declare @p5 intset @p5=1exec sp_cursoropen @p1 output,N'SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab WHERE ((remtab.rfield1 = ''value'' OR remtab.rfield1 = ''value'' ) OR (remtab.rfield2 = ''value'' )) AND remtab.rfield3 = localtab.rfield3'',@p3 output,@p4 output,@p5 outputselect @p1, @p3, @p4, @p5Duration: 382CPU: 0Reads:49Writes:0TextData:exec sp_cursorfetch 1602323,2,1,1Duration:302CPU:15Reads:7Writes:0TextData:exec sp_cursor 1602323,40,1Duration:290CPU:0Reads:7Writes:0TextData:exec sp_cursorclose 1602323Duration:0[/code] Please help..THanks a lotTue, 12 Feb 2013 11:07:08 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1419147-1042-1.aspxHi friends, I have created another thread regarding a performance problem on a select statement to oracle table...But would like to know if the below Select query can be used as open query and if it would make the sql run faster..[code]SELECT localtab.field1 FROM LINKEDSERVER..REMOTEUSER.TABLE remtab, localtab WHERE ((remtab.rfield1 = 'value' OR remtab.rfield1 = 'value' ) OR (remtab.rfield2 = 'value' )) AND remtab.rfield3 = localtab.rfield3[/code] Is it possible to run the above SQL using open query? Any help is much appreciated..Thanks so muchTue, 12 Feb 2013 12:24:50 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1417163-1042-1.aspxJust wanted to post this in case someone else feels my pain.I've been working with Oracle and SSIS recently, I wrote a delete statement within Developer that worked perfectly, pasted the SQL into an SSIS Execute SQL Task and againit parsed successfully however once I ran the package it failed with the above error!After multiple re-writes of the query trying to figure out the Oracle syntax for this delete statement I finally found the following[url]http://thefirstsql.com/2012/09/28/ora-00936/[/url]So credit where its due this saved me hours more work and debugging, apparently you cannot use -- to comment your scripts in SSIS when using Oracle I hope this helps someone elseAndyThu, 07 Feb 2013 09:12:19 GMTAndy Hyslophttp://www.sqlservercentral.com/Forums/Topic1411925-1042-1.aspxI am having a problem connecting an oracle client to an oracle database. I am also having a problem connecting an access database to oracle using an ODBC. I have been working on this for several days and I keep getting the same error: ora-12203: TNS: unable to connect to destination.I have only one tnsnames.ora and one sqlnet.ora files on the system, I double checked. I have modified the path variable to have both the oracle home and the net80/admin in it.The oracle client is oracle8, I know it is old and outdated, but that is what I have to work with here.I have not used oracle for the past 10 years, can anyone supply some assistance here.Am I missing something?Any help would be appreciatedtacomaFri, 25 Jan 2013 13:33:41 GMTlewis.rayhttp://www.sqlservercentral.com/Forums/Topic1408655-1042-1.aspxHi guys, recently i need to start to look after some of the linked server query . i got a instance of SQL 2005 and linked over to oracle server. i can run the pl-sql on the server without a problem (the query returns 200000 rows) on sql plus* in my local machines (the result come back within 25 sec) , when i ran the same query through linked server, it has no responses for about 25 minutes and sometimes it came back the error messages . here are the followings : OLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" "ORA-03113: end-of-file on communication channelOLE DB provider "OraOLEDB.Oracle" for linked server "REB_REPSER" returned message "ORA-12592: TNS:bad packet".but if i run a query which return less than 3000 rows, it brings the result back without a problem . i have look into different options on linked server setting (and i tried to modify the timeout) without any luck. anyone came across this error before ? and how do you solve it ? or where should i start ?please let me know if you require any more information ? i really appreciated with all your help many thanks Thu, 17 Jan 2013 16:03:01 GMTrayan98http://www.sqlservercentral.com/Forums/Topic530170-1042-1.aspxI have set up a linked server to Oracle 9i from SQL server 2005. This works fine when working with queries in SQL Server Management Studio, but I am after getting this working with Excel. I have set up an ODBC connection to SQL Server, then try querying the linked server but get an "invalid object name" when using Microsoft Query. I would assume if the query works in SQL Server studio it would also work in MS Query (therefore excel reports).ThanksDannyTue, 08 Jul 2008 10:02:50 GMTDannygrhttp://www.sqlservercentral.com/Forums/Topic1402059-1042-1.aspxI am trying to extract just the time portion of the datetime field. i am creating a view in MYSql which will then be queried via an openquery statement form SQL Server and it needs to be a CHAR field.SELECT substr(`calldate`,12,8) AS 'TIME'FROM `asteriskcdrdb`.`cdr`;the calldate field is defined as datetime in MYSql. i need just the time portion and to save it as a CHAR filed for SQL Sever. as you can see I am clueless.Thank you calldate field: in table2013-01-02 15:10:41Wed, 02 Jan 2013 13:19:28 GMTTryingToLearnhttp://www.sqlservercentral.com/Forums/Topic1401170-1042-1.aspxi am querying a MySql view for data, but need to join to a sql table for three additional fields.SET IDENTITY_INSERT CALLSNEW ONINSERT INTO [cas].[dbo].[CALLSNEW] ([calls_key] ,[ext])SELECT * FROM OPENQUERY(PHONEDB,'SELECT calls_key, ext FROM calls')Where calltype IN ( 'from-internal', 'ext-local', 'ext-queues', 'ext-grou')but i need to join to a linked SQL server as well to get a few more fields. I could use suggestions on how to do that.SELECT dept = dbo.EMP.dept, fname = dbo.EMP.fname, lname = dbo.EMP.lname, empnum = dbo.EMP.empnum FROM linkedserver.dbo.EMP WHERE dbo.EMP.ext = ext AND dbo.EMP.active = 1thanks for your time.Sat, 29 Dec 2012 08:22:20 GMTTryingToLearnhttp://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspxI need to create a sql database which works the same way in oracle....Basically, I have to mimic a database from Oracle to SQL server.I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.Thanks,SueTons.Wed, 21 Nov 2012 12:28:14 GMTSQLCrazyCertifiedhttp://www.sqlservercentral.com/Forums/Topic1379646-1042-1.aspxHello Team,How to corelate between oracle dba concepts and sqlserver dba concepts Please provide books or any linksThanks in advanceThu, 01 Nov 2012 00:56:04 GMTRK2012http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxHello all,I am experiencing an odd problem with my systems and hope someone here can shed some light on my issues. So, here are the details...Environment:SQL Server 2005 Std Ed. SP4 x64One production cluster, one test cluster and one single server dev instance all on Windows 2003 Server, x64, all VMWare Guests.Oracle Client 10.2.0 x64All three instances use the Oracle client to connect via Linked Server to the same Oracle database.Problem:Everything seems to work properly using the linked server until we start to get Error 7302 - "Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked Server "" ". There doesn't seem to be anything that precedes this issue. One day it works, the next it doesn't.Troubleshooting:I get the error message if I try to execute a query, or if I right-click the linked server and choose Test.The provider oraOLEDB.Oracle provider still shows in the list and Allow In Process is checked.No changes to TNSNames.ora or SQLnet.ora. TNSPing is successful using the name I am connecting to.I can connect from the instance using SQLPlus and the same login the linked server uses.Creating a new linked server with the same information fails with the same error message.Appears to affect all Oracle Linked servers.Current Solution:Restart SQL. Every time this happens, I have to restart the SQL Server services. After restart everything works properly for a long period of time until the problem recurs.Of course, this is not an optimal solution as it requires some downtime and customers notice, but so far it is the only way I have found to get the link working again.Further Troubleshooting:I have been poking around in master to see what catalogs might be helpful and have found sys.dm_os_loaded_modules. If search this table while I am experiencing the issue, there are no entries for Oracle (company = 'Oracle Corporation'). If I search after I restart the database services I see entries for Oracle, some 34 of them.My query[code="sql"]select * from sys.dm_os_loaded_modules where company = 'Oracle Corporation'order by description[/code]Sample results[quote]base_address file_version product_version debug patched prerelease private_build special_build language company description name0x000000000B440000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle CORE Library C:\oracle\product\10.2.0\client_1\bin\oracore10.dll0x000000000B6B0000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle CORE UTS PT Library C:\oracle\product\10.2.0\client_1\bin\orauts.dll0x000000000C410000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle Internet Directory Client Library C:\oracle\product\10.2.0\client_1\bin\oraldapclnt10.dll0x000000000C230000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle Net ORAN10 DLL C:\oracle\product\10.2.0\client_1\bin\oran10.dll[/quote]So, what gives? Is there a known time where modules that have been loaded into SQL process space are pushed out? Does this happen if the system is experiencing heavy memory presssure? Is it possible to reload these modules without restarting SQL?Thanks!Dan BWed, 24 Oct 2012 14:17:16 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic741714-1042-1.aspxHi,I am a sql server developer/DBA. I am interested to learn Oracle from begining. Can anybody pls guide on this path.Thanks,VjThu, 25 Jun 2009 05:38:41 GMTvijay1327-891581http://www.sqlservercentral.com/Forums/Topic1359754-1042-1.aspxHi All,I have a requirement where i need to Trigger the SQL Server Job, once the Data load which at Oracle side is completed.Please need help to understand the Steps involved to make this.Fri, 14 Sep 2012 20:19:44 GMTsudhirnunehttp://www.sqlservercentral.com/Forums/Topic1348133-1042-1.aspxI have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without installing the Oracle Client on my users' machines. So I created a SQL Server 2008 R2 view that queries the Oracle view through a Linked Server. I can run the view in Mgt. Studio. It looks good....I can see the text fields as well as the numeric fields. When I connect to this SQL Server view with the Pivot Table, the numbers disappear.....all zeros! The text fields all look good. I tried to connect with the regular SQL Server Native driver as well as with ODBC and Microsoft Query with the same result. If I connect the Pivot Table directly to Oracle, there is no issue. For whatever reason, this scenario is not yielding results in a Google search. I thought I would check here to see if anyone has seen this before.Thank you for your help!Tue, 21 Aug 2012 16:45:06 GMTJesseDWhttp://www.sqlservercentral.com/Forums/Topic1340616-1042-1.aspxHi friends, We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We have a linked server from SQL Server database to Oracle.. Can you please advice most efficient ways to do this? Thanks muchMon, 06 Aug 2012 07:51:22 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1347176-1042-1.aspxHello Oracle Forum:I've got my first linked server established from SQL Server 2008 SSMS to Oracle.What is the proper syntax for this simple query with a date expression in the where clause?What is the proper date delimiter?Is there a conversion necessary?SELECT TOP (5) SURGERY_DATEFROM OPENQUERY(CLARITY, 'SELECT * FROM OR_LOG WHERE SURGERY_DATE = ('8/1/2012')' AS aMany thanks ahead of time.PhunhogMon, 20 Aug 2012 07:46:17 GMTPhunhoghttp://www.sqlservercentral.com/Forums/Topic1317695-1042-1.aspxHi all,I've got an issue with SQL Server 2005 Replication with Oracle.[b]Issue Summary[/b]:When the replication is stopped and the tnsnames.ora file is modified to make the connection points to another Oracle database, the replication returns error when I try to start it again.[b]Environment Description[/b]:I've successfully created a transactional replication from SQL Server 2005 (distributer) for a single table to Oracle server ( named as db1)(substriber). When I do the following, an error is raised:1. SQL Server replication is stopped2. the table is copied from Oracle db1 to another Oracle server db2 (the same schema name and password in both dbs)3. hostname in tnsnames.ora file is changed to point to db2 server4. the Server link in SQL Server tested and succeeded5. when the replication is started, it returns error... the error is trying to re-initialize the table and claims the table is already there.[b]My questions are: [/b] - Why SQL Server 2005 wants to reinitialize in the scenario described above? - Is there a way to make SQL Server proceed with the synchronization without initialization?By the way, if I set the hostname back to db1, the replication proceeds with synchronization without any problem.Thanks in advance,Mon, 18 Jun 2012 20:30:32 GMTahmed_b72http://www.sqlservercentral.com/Forums/Topic1317784-1042-1.aspxI have a large table having millions of rows. There is one datetime column which captures the date and time a row is inserted to the table. Now, I want to copy the table's data into another server's table with same structure. Here the client wants me to migrate the data in a page by page manner (say 100 rows at a time). This table does not have any identity or unique key. I am thinking of using the row_number () function on the datetime column. But I have one doubt... say the trasaction fails somewhere in between... say at row number 115... it is written like the process will start again from 101 and not from 1. But is there any guarantee that the row_numbers will be same considering the datetime field can have duplicate values? Regards,SnigdhaTue, 19 Jun 2012 02:48:38 GMTsnigdhandreamhttp://www.sqlservercentral.com/Forums/Topic1304827-1042-1.aspxHi All,I am Facing Currently with one Key Issue.I have a Package which Imports the Data from Oracle to SQL Server DB Tables.Package Runs Well without any issue in Integration Services as "Run Package".But on Running it using the JOB, the Data Flow fails saying the "cannot convert between unicode and non-unicode string data types".But this Does not occur on Running the Package from Integration Services or BIDS.Please need pointer to get this issue resolved.Thanks,Sudhir NuneWed, 23 May 2012 04:38:12 GMTsudhirnunehttp://www.sqlservercentral.com/Forums/Topic742973-1042-1.aspxI've developed an Integration Services package using the Microsoft (obsolete) OLE-DB provider to move data between Oracle 10g (on Unix) and SS05 SP3. During my initial development the Control Flow Tasks will execute successfully in BIDS. After saving the package with ProtectionLevel of "EncryptSensitiveWithUserKey" and sleeping 8 hours, when I return and open the package I can not execute a task which connects to Oracle in BIDS. The Oracle connection manager will test successfully, but running it in BIDS gives me the famous error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.I installed the Oracle 11g ODAC components including OLEDB provider, but it will not allow automatic translation of Oracle unicode to BIDS Visual Studio data format. so I've dropped it.I have built my package (ProtectionLevel changed to "DontSaveSensitive"), deployed it with a configuration file that includes the Oracle connection information, and it works from SQL Server Agent just fine. TransactionOption doesn't seem to do much. This is not moving much data and does not need explicit transactions. What parameter am I not setting that prevents BIDS from testing tasks that connect to Oracle?Can anyone recommend a supported, useable OLEDB provider for Oracle that works with SSIS?Thank you for any assistance.Fri, 26 Jun 2009 14:42:31 GMTAlan Spillerthttp://www.sqlservercentral.com/Forums/Topic1298249-1042-1.aspxHi All, I am having problem with pulling data from Linked Server that I have created using "Oracle Oledb Provider". I am able to created Linked server and tested the connection successfully. But I am unable select the data from table when running SELECT statement. I have used both four part naming and OPENQUERY method. I am getting same error in both cases. Here is the error I am getting. Msg 7399, Level 16, State 1, Line 1The OLE DB provider "OraOLEDB.Oracle" for linked server "IMPRX_D" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "IMPRX_D". Environment: SQL Server 2005 SP3 32-BitWindows 2008 R2 64 Bit Oracle 11g Client 64 bit Please let me know if I missed to provide any details. ThanksMadhu PradeepThu, 10 May 2012 16:16:43 GMTl.madhupradeephttp://www.sqlservercentral.com/Forums/Topic1296599-1042-1.aspxI see that there are a ton of these issues already posted, but I can't find one that fits my situation.I have no to the oracle server settings so please eliminate those solutions. I've successfully created a linked server to the Oracle server. But can only access it using my own windows credentials. Even though I've set it up so that the all logins are mode using the same security context, where I've hard coded the connection creds.I created an SQL account called ETL, and when I connect as that account and try to query the linked server, I get the following error.[i]Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "AUTO2K".[/i]But when running the same query under my windows credentials it runs fine. What I'm trying to do is get a SQL Agent job to run a query that hits the linked server, but SQL Agent is setup to run as NT AUTHORITY\SYSTEM. So I set the step to run as user ETL, but get the error above. When I change to run as my windows credentials, I get the following error.[i]DOMAIN\cdoise. Access to the remote server is denied because the current security context is not trusted.[/i]This is extremely frustrating, any advice would be greatly appreciated. Thanks!Tue, 08 May 2012 10:07:35 GMTBad_Karmahttp://www.sqlservercentral.com/Forums/Topic1247217-1042-1.aspxWhich one is faster delete/truncate? Why?-------------------------------------------------------------------[b][url='http://www.starconfig.com.au/']Sydney Web Design Companies[/url][/b][b][url='http://www.starconfig.com.au/']Cheap Web Design Sydney[/url][/b]Mon, 06 Feb 2012 04:56:48 GMTfastformation01http://www.sqlservercentral.com/Forums/Topic761703-1042-1.aspxThe scientific application for which I'm the developer uses a SQL Server database to hold data and results.The data and results for a single 'catchment' are stored in a single database.So any one user can have many databases (one per catchment). And a single server instance can hold the databases for several users.Each database has the same set of tables, stored procedures etc.This works well - users can easily create and drop their databases and not affect other people's databases.BUTI now need to implement the application using ORACLE and the same concepts just don't seem to apply.You seem to be encouraged to have just one database instance and therefore one database per server.So how do I achieve the multiple databases concept? At first, I thought that the 'schema' would be the key. In other words each 'database' would be in a separate schema.This seems to have the following snags(a) One-to-one correspondence between user and schema.(b) The schema contains tables and views but not stored procedures. So stored procedures must handle 'variable' schema.[b]Any suggestions?[/b][i]I'm asking this question here rather than an ORACLE-style forum because I have more hope of an expert, helpful, good-humoured reply here.[/i]Wed, 29 Jul 2009 09:34:13 GMTMartin Willshttp://www.sqlservercentral.com/Forums/Topic1244943-1042-1.aspxOLE DB provider "OraOLEDB.Oracle" for linked server "ORAFINNONE" returned message "New transaction cannot enlist in the specified transaction coordinator. ".Msg 7391, Level 16, State 2, Line 1The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORAFINNONE" was unable to begin a distributed transaction.Wed, 01 Feb 2012 03:31:07 GMTgajananhatkarhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxHi,We have a very time sensitive pressing requirements to be addressed immediately.We need to read the date time column from sql server into oracle 10g.We have a dblink established between the two servers and have tried to use sql server and sql native client 10 driver to read the dates from sql into oracle.This we are able to do very successfully.The issue is in reading microseconds stored in sql server.The microsecond part comes in as 00000 from sqlserver to oracle.These microseconds are needed to identlfy the unique records.For instance SQL SERVER Oracle source_id source id01/01/2012 6:30:35:456 01/01/2012 6:30:35:000000.Any insight or help will be much appreciated.We have no control over sql server Db as this is remote third party DB.We just have select access to their tables to bring into our own oracle DB.Tue, 10 Apr 2012 12:52:54 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1272364-1042-1.aspxplease convert below code of sql server to sql plusso that it doesnt generate syntax error.CREATE PROC SearchAllTables( @SearchStr nvarchar(100))ASBEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results ENDSun, 25 Mar 2012 11:19:40 GMTmurarka.ankita2007http://www.sqlservercentral.com/Forums/Topic1269375-1042-1.aspxHi friends,I am trying to create a Linked server to Oracle in a Win 2008 (64bit) server using SQL Server 2005(64 bit).This server is configured to Replication and Clustering too.I installed 64 bit Oracle Client,64 bit ODAC and 32 bit Oracle client.But none of this helped.I dont see the oraoledb.oracle driver in the Linked servers providers list.I am able to connect the Oracle client with the Oracle DB.Please advice what could be the reason.I even tried using the Microsoft driver for ODBC.But I get the following errorTITLE: Microsoft SQL Server Management Studio------------------------------"The linked server has been created but failed a connection test. Do you want to keep the linked server?"------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER".OLE DB provider "MSDASQL" for linked server "SERVER" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".OLE DB provider "MSDASQL" for linked server "SERVER" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)Tue, 20 Mar 2012 01:26:52 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic1264785-1042-1.aspxHi friends, I'm trying to create linked server from SQL Server 2005 SP3 on Windows 2003 64-bit to Oracle database 64-bit. Steps are below:Installed InstantClient Oracle 11gR2 (11.2.0.1.0) for Microsoft Windows (x64) to C:\Oracle\product\11.2.0\client_1 folder on the SQL Server - database server.Installed ODAC (11.2.0.2.1) ODAC112021Xcopy_x64 for Windows x64 by executing install.bat all C:\Oracle\product\11.2.0\client_1 odacChecked the Allow inprocess of OraOLEDB.Oracle provider in SSMS.Created ODBC connection to the Oracle database - ORADB. Connection successful via ODBC and sqlplus.Executed sp_addlinkedserver @server = 'TEST_LINK', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'ORADB'I have executed sp_addlinkedsrvlogin @rmtsrvname = 'TEST_LINK', @useself = 'FALSE', @rmtuser = 'orauser', @rmtpassword = 'pwd'The orauser is the oracle username and pwd is the password for the oracle user orauser. When I click Test Connection for the linked server TEST_LINK in SSMS, it just hangs.. When I try to run a query to ORADB , 'select * from TEST_LINK..ORAUSER.TABLE1 ' - it runs forever and eventually SSMS hangs. Please help.. Thanks Sat, 10 Mar 2012 13:38:16 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1259108-1042-1.aspxHi all -I am in the process of migrating from oracle 8.1.0.6 to sql server. I am trying to use the SSMA (version 2, I tried 4 and 5 and they are not compatible with that version of oracle) utility, but am running into an issue with the license key. I can't get one. I go through the instructions on the popup, but it does nothing after it takes me to the website. Anyone have any ideas in how to go about getting the license file?Cheers!DanTue, 28 Feb 2012 12:45:40 GMTrobert.baird 2778http://www.sqlservercentral.com/Forums/Topic635800-1042-1.aspxI have a 64-bit SQL Server 2005 on Windows 2003 Server Standard Edition server. I have the Oracle 11g client installed, the 64 bit version. I also have an ODBC connection to the Oracle DB and that works. I have a Data Link (UDL) to access the Oracle table and that works. I also have the correct user name and password. The TNSNames.ora is good. The linked server is created successfully, and I can see where you can select Tables and Views in the Catalogs. So, everything looks good. However, when I select to view the tables, I get the following error: The OLE DB Provider "OraOLEDB.Oracle" for linked server "PBLA" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "PBLA". (MS SQL Server Error 7399)Any suggestions?Thank you!Tue, 13 Jan 2009 13:32:26 GMTpbyrumhttp://www.sqlservercentral.com/Forums/Topic1253909-1042-1.aspxHello,I am actually setting up a wizard function on an oracle database (this wasn´t part of the job description:-)). But I am just working with a kind of Wizard interface, so I am not coding anything.The wizard will filter out specific keywords of database content and applies specific rules to this content. Anyway, many words in the content contain special letters, and the wizard rules won´t consider these words, which has a very negative impact on the Wizard quality.I got the advice to replace special letters in the query with a variable that stands for any character. Example (the keyword should be "coopération"):(coop[!e]ration) -> where [!e] would stand for any letter expect e.The rule itself does not work, but I am asking myself (and now you) if such `character variables´ could be an option and how they work. In an ideal case, the variable would even replace a character row, because the special characters are possibly coded into several characters.Thanks,PeterFri, 17 Feb 2012 08:14:46 GMTbatistuta789http://www.sqlservercentral.com/Forums/Topic1205542-1042-1.aspxWe have a large amount of jobs that take data from our Oracle database, transform it, and then import it into or SQL Server databases. We recently changed our Oracle, source, database from WIN1252 to UTF-8. This has broken our SSIS packages and producing multiple errors with ..."cannot convert between unicode and non-unicode"...Changing the source back to WIN1252 is not an option.I believe we may have found a work around by adding a data conversion task within the package. But, there are over 500 hundred packages and mapping each column within each package will be VERY time consuming. ([url=http://msdn.microsoft.com/en-us/library/aa337316%28v=SQL.90%29.aspx]http://msdn.microsoft.com/en-us/library/aa337316%28v=SQL.90%29.aspx[/url])Has anyone tried this work around before? Have you had any issues? I am worried about invalid characters, truncation, and the effects on performance. I was looking into updating all the destination tables to only have unicode columns (nvarchar, nchar, etc) to see if I could do a direct import if the columns were large enough. But, from what I read, translations will not match.We are running SQL Server 2005 SP2.Thank you for your time!Mon, 14 Nov 2011 13:52:09 GMTschep021http://www.sqlservercentral.com/Forums/Topic1232659-1042-1.aspxHi friends, I need to drop and recreate check constraints in a few databases and would need to script this so it can be run over different databases at customer's end. Below is the sql I need to run to drop check constraintsSELECT 'ALTER TABLE ' + QuoteName(OBJECT_NAME(so.parent_obj)) + CHAR(10) + ' DROP CONSTRAINT ' + QuoteName(CONSTRAINT_NAME) FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc INNER JOIN sys.sysobjects so ON cc.CONSTRAINT_NAME = so.[name] In Oracle, I can spool the results of the above sql to a file and run the file to drop the constraints.. Is there a way to do the same in SQL Server? Please give me your thoughtsThank youMon, 09 Jan 2012 10:45:37 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic1221074-1042-1.aspxHello,In order to access and run pl/sql procedure from an SSRS report:1) Which format should the pl/sql procedure return the data in?2) How do I read the data returned from SSRS?I haven't found any resources on the internet. Please give references if you know of anything that will guideThanks and Regards,PraveenaTue, 13 Dec 2011 11:11:10 GMTvenus.pvrhttp://www.sqlservercentral.com/Forums/Topic1212966-1042-1.aspxstring str3 = "SELECT a.siebleloginid, a.employee, MAX (a.tierlevel) AS tier FROM"+ "((SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee,"+ "DECODE (e.siebleloginid, m.siebleloginid, 2, DECODE (e.username, z.usernamesup, 1, 0) )"+ "AS tierlevel FROM emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e,"+ "emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP' AND"+ "z.usernameman = m.username AND e.section = z.section AND e.employmentstatus = 'Active')"+ "UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee, "+ "DECODE (e.siebleloginid, m.siebleloginid, 1, 0 ) AS tierlevel FROM "+ "emadministration.tblemployeeinfo m, emadministration.tblemployeeinfo e,"+ "emadministration.tblzlistsectsup z WHERE m.siebleloginid ='OLEMSUP'"+ "AND (z.usernamesup = m.username OR (z.usernametempsup = m.username "+ "AND z.tempsupenddate >= TRUNC (SYSDATE - 0.25)))"+ "AND e.section = z.section AND e.employmentstatus = 'Active') "+ "UNION (SELECT e.siebleloginid, e.lastname || ', ' || e.firstname AS employee,"+ "DECODE (zm.usernameman, NULL, DECODE (zs.usernamesup, NULL, 0, 1),2) AS tierlevel"+ "FROM emadministration.tblemployeeinfo e, emadministration.tblzlistsectsup zm,"+ "emadministration.tblzlistsectsup zs WHERE e.siebleloginid ='OLEMSUP' AND"+ "e.employmentstatus = 'Active'AND e.username = zm.usernameman(+) AND"+ "e.username = zs.usernamesup(+))) a GROUP BY a.siebleloginid, a.employee"+ "ORDER BY tier DESC, employee";Tue, 29 Nov 2011 01:00:46 GMTswainsarada