Linking Oracle view to SQL Server express

SQL Server Express

Index ‹ SQL Server
- Archive
  - Office
  - Windows Forms
  - VC++
  - Architecture
  - IE
  - SharePoint
  - VB
  - Smart Device
  - ISV
  - VFP
  - SQL Server
  - Visual Studio
  - Visual C#
  - Windows Live
  - Windows Vista
  - VS Express
  - Dotnet
  - Game
  - VS 2008

- Previous
  - 1
    - How to backup CE database Tables and restore to newer database? I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download. When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end. Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time Tag: Linking Oracle view to SQL Server express
  - 2
    - Adding CRLF to nvarchar(max) column text A SqlServer 2005 db has a table with an nvarchar(max) column containing text with paragraph format. When displayed in a Windows form textbox each paragraph exhibits CRLF. When the table is opened in Management Studio the paragraph text is separated with two boxes at each CRLF. I would like to insert an additional CRLF (or whatever is required) so that when viewed in a textbox each paragraph has a blank line separating it from the next paragraph. Much easier to read!!! Can this be done If so, how Thanks in advance for any help you can provide. Tag: Linking Oracle view to SQL Server express
  - 3
    - EncryptSensitiveWithUserKey - question Hi, I have a question about the EncryptSensitiveWithUserKey package option. Currently, this is the option on all my packages (which I then later save to SQL Server using SQL Server storage for encryption). My question is this: When I log onto the machine where I'm developing the packages I use a common developer login. So my understanding is that if anyone else logs on using the same login as mine, they can see and run the packages in BIDS, yes But if someone logs onto the machine under a different login, they can also see my packages in BIDS, but they *shouldn't* be able to RUN them, correct That's because the passwords are encrypted with MY user key, and all the connection managers that use passwords should fail, right Can someone please confirm this. Thanks Tag: Linking Oracle view to SQL Server express
  - 4
    - @@FETCH_STATUS Reset Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @@Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong declare @entityid int , @datafrom nvarchar ( 255 ), @datato nvarchar ( 255 ), @updatedate datetime declare statustime1 cursor local scroll static for select audit . entityid , audit . datafrom , audit . datato , audit . updatedate from audit , orders where audit . entityid = orders . orderid and audit . entitytypeid = 4 order by orders . orderid , audit . updatedate desc open statustime1 While @@fetch_status = 0 Begin fetch next from statustime1 into @entityid , @datafrom , @datato , @updatedate print @entityid end close statustime1 deallocate statustime1 go Tag: Linking Oracle view to SQL Server express
  - 5
    - Object reference not set to instance of the object while trying to sync. Hi, I am trying to configure web syncronization using the wizard for a merge replication. However when I am trying to add users (in authentication mode), I get an exception "Object reference not set to instance of the object ". Please let me know if you have any inputs on this. It will be highly appreciated.. Tag: Linking Oracle view to SQL Server express
  - 6
    - OLE-Db connection in Transformation Script Component Hello, Using the following documentation as a guide: http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx I instantiated a new script component into an existing Data Flow in my SSIS project. In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project. My Connection Manager is of the type oOLEDB. I then opened up the script designer and added the following lines of code where it said "Add your code here" Dim myConnManager As IDTSConnectionManager90 = _ Me .Connections.ECFconnection Dim dbConn As OleDb.OleDbConnection = _ CType (myConnManager.AcquireConnection( Nothing ), OleDb.OleDbConnection) When I test run the project I get the following error and the new script component is red: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. I know the database connection works since I am using it in a component that executes before this new script component. I am stuck...Any suggestions Tag: Linking Oracle view to SQL Server express
  - 7
    - Installing Client Tools I recently installed an evaluation copy of SQL Server (Enterprise x64 edition). What's the proper way to load the management and analytical tools onto client machines There are 2 separate problems: 1. The 64 bit program only comes with 64 bit tools. (Should I just download the 32 bit evaluation copy Will the tools expire in 180 days on all the client machines ) 2. What are the licensing rules Surely I am allowed to load client tools (but not the server itself) onto client devices. Is that right Thanks! Tag: Linking Oracle view to SQL Server express
  - 8
    - Comparing Large Tables I've successfully created SSIS packages where I compare two tables in different databases on different servers. However, this is good enough to compare hundreds of thousands of records quickly. The process becomes a huge performance problem when trying to compare table differences when I'm looking at tables that each contain tens of millions of records. One database is on a SQL 2005 box and the other DB is SQL 7.0 so the lookup component fails for this type of SQL Server. I've been implementing merge joins and conditional components to do my standard table comparisons. Is there another way to implement this process or maybe partition it somehow to take pieces of the table at a time and compare them I'm open to ideas. Tag: Linking Oracle view to SQL Server express
  - 9
    - How to delete rows in tables... I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database. Im using SQL 2000 I have tried with the following: Delete from fsalesinvoiceline Join dsalesinvoiceheader on Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and Fsalesinvoiceline.company= dsalesinvoiceheader.company Where dsalesinvoiceheader.billtocustomerno=ĄŻINDTAST DEBITORNUMMERĄŻ Go Delete from dsalesinvoiceheader Where dsalesinvoiceheader.billtocustomerno=ĄŻINDTAST DEBITORNUMMERĄŻ I get the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN' What am I doing wrong /Soren D. Jensen Tag: Linking Oracle view to SQL Server express
  - 10
    - Maximum height for table How do I set the maximum vertical size of a table I would like to set it based on total vertical size in inches or a maximum number of rows returned. Thanks for any help. Brad Tag: Linking Oracle view to SQL Server express
  - 11
    - any *simple* external activation examples available? BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.) TIA Tag: Linking Oracle view to SQL Server express
  - 12
    - My SSIS problems There are things I just don't get about MS programming: 1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04" 2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging. 3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do) I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated. Thanks, Tag: Linking Oracle view to SQL Server express
  - 13
    - Filtering Problems I am stumped on what is probably a simple glitch. I have an SSRS 2005 server report based on a stored procedure dataset, which accepts a single parameter. The parameter value originates from a session variable when the user opens the aspx page containing the reportviewer control (Visual Web Developer 2005). I've configured this parameter as a hidden parameter in the RDL design. In this configuration it functions as intended in both the development environment and when deployed. Next, I tried to add user prompts to implement report server filtering (in addition to the database filtering in the source SPROC). I added the parameters, set up the data sources for the prompt values, set up the filter expressions, did everything that I can find to implement this process. When I preview the report in the development environment, it works as intended. When I deploy it and attempt to run it from my http://localhost environment, though, it fails. There's no error message or anything...I don't even get the "Report Running" icon with no rows returned...what I get is prompts which function correctly (showing the right values and letting me select one), but when I click "Run Report" I just get an immediate screen refresh with the prompts reset to the default "<Select a value>" setting. And no report. I've obviously missed some setting somewhere, for it to work in dev but not when deployed. Any suggestions will be GREATLY appreciated. Tag: Linking Oracle view to SQL Server express
  - 14
    - Data from Derived Table I am looking to run two different queries. the first one returns about 6000 numbers. i.e 123454, 15432, 2343545 etc My second query i want to use the results from the first query as a where clause i.e where Number in ( '123454' , '15432' , '2343545' ) Clearly i dont want to type out 6000 numbers in a query. How do i use the data generated in the first query as the where criterea in the second query. Tag: Linking Oracle view to SQL Server express
  - 15
    - Cannot load Data Flow tasks When I copy over an SSIS package I have been developing from my laptop to my desktop with Windows File Sharing (shared folders) across a home network, the moved package fails to load properly. I can see the Control Flow tasks but not the Data Flow tasks - they simply disappear! I have update the Connection Managers to point to the new machine, and tested them (OK). Its the same as this issue posted here And Here Creating an empty package and clicking to create a new data flow cause this error: TITLE: Microsoft Visual Studio ------------------------------ Failed to create the task. ------------------------------ ADDITIONAL INFORMATION: The designer could not be initialized. Microsoft.DataTransformationServices.Design) Seems the only way is to uninstall and reinstall Client Components. How do I uninstall and reinstall Client Components when I try to do that, SQL Server Setup tells me "None of the selected features can be installed or upgraded. Setup cannot procees since there is no effective change being made to the machine...." I had selected "Workstation components, Books online and Development tools". This is already installed - so how I can reinstall it if I get this message above which does not let me proceed. Tag: Linking Oracle view to SQL Server express
  - 16
    - SQL 2005, OPENQUERY and security context switching Hi, I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine: EXECUTE AS LOGIN = 'link_user' SELECT * FROM OPENQUERY ( linked_server , 'SELECT * FROM ... ) But when I use the exact same statements in a stored procedure I get the folllowing error: Msg 7416, Level 16, State 1, Procedure linktest2, Line 5 Access to the remote server is denied because no login-mapping exists. I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures! Is there another workaround then to create all users in the linked server security Rgds Bertrand Tag: Linking Oracle view to SQL Server express
- Next
  - 1
    - Query problem involving DateTime column Hi all, I am quite surprise by getting wrong resultset from a simple query like: select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008' In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null simply because there is no such date as 2008 yet in the DB. Help from anyone is needed. Thanks in advance. Sha. Tag: Linking Oracle view to SQL Server express
  - 2
    - Pivot fields into a single column I have a row that looks like this Year 2006 2007 2008 2009.....etc. I want a select statement that will get me this Year 2006 2007 2008 2009 etc. Tag: Linking Oracle view to SQL Server express
  - 3
    - Access project connecting to SQL 2005 Most of our users are using MS Access project to work with our data in SQL Server 2005. The problem we have is that in order for them to access the tables they need to be given db_datareader and db_datawriter permission on the database. Our goal is not to give access to the tables themselves but to views. Therefore, we've created several views and placed them into a particular schema. Now we want to give access to that schema, but when we assign select, delete, insert, and update to that schema user in ms access project aren't able to view these tables. When we give them db_datareader permission they are able to see the views but in parentencies does not show the correct schema; therefore, when you try to open that view it errors out saying it doesn't exist. Also since they now have db_datareader they are also able to access the other view and tables in the database. What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only. Tag: Linking Oracle view to SQL Server express
  - 4
    - Filters - Report Builder I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres Last Name Contains ------------------ First Name Contains ------------------ When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria. Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL), Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic Thanks. Tag: Linking Oracle view to SQL Server express
  - 5
    - Connection Managers Area not coming up When I brought up the BI DEV studio first tiem the Connection Managers area showed up, but after I closed it by mistake from next time on wards it is not coming up. Can some one from this form help me how to get the Connection Managers area(pane) back Thanks in advance. Regards Mamidi Tag: Linking Oracle view to SQL Server express
  - 6
    - How to make this query... Hi all. I have tried making this query for a while and i didn't manage to. I know it is something related to JOIN, but i didn't manage to make it work :\ I have two tables. Table1- db_stockprices Table2- db_stockSymbols db_stockprices is getting updated every day with stock daily prices. db_stockprices looks like: ID, SymbolID, Price,Date 1 1 33.21 1/1/2007 2 1 33.14 1/2/2007 3 1 34.21 1/3/2007 4 2 11.42 1/1/2007 5 2 11.73 1/2/2007 6 3 18.32 1/1/2007 7 3 19.11 1/2/2007 8 3 18.41 1/3/2007 9 4 52.44 1/1/2007 and so on for all the stocks. ID is Primary Key SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information. What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID. Example: If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted. Is this possible within 1 SQL Query I would really appreciate a good query example for this example.. Tag: Linking Oracle view to SQL Server express
  - 7
    - What is the best book to start from scratch? Hi - I have tried several times to get started with SQL Server (I think I downloaded SQL Server 7 2005), but everytime I do there are so incredibly many steps to get started that I end up not going any further. This has been going on for longer than I wish to admit - but it is a very long time. Isn't there some straightforward set of instructions that I can use to get started What I want to do is to put some financial data in and then do some analysis of the data. Doug Tag: Linking Oracle view to SQL Server express
  - 8
    - Mirroring on separete domains We have 2 servers each having a domain (i.e. 2 domains) running Windows Server 2003 Standard Edition Service Pack 1 We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error The error produced from the mirroring part is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error : 1418) Thank you. Tag: Linking Oracle view to SQL Server express
  - 9
    - Trigger with access to remote server Hello, i have two server connected to internet S1 and S2 (connection between both is only internet too). I need write trigger on S1 which send update or another command to S2. Is it possible, and how (without using .NET methods only by using Transact SQL) Thnx, jakub. Tag: Linking Oracle view to SQL Server express
  - 10
    - problems at beginning of set up Before I get into the project that I actually need working I'm trying to get through an example or two provided by microsoft. I'm using the Flights sample from http://msdn2.microsoft.com/en-us/library/ms160889.aspx I've downloaded the files and follow the instructions but quickly ran into trouble. I select the configuration file, enter the needed information, check the 'enable instance after it is created' box, and click okay. This takes a couple minutes but finishes without errors. I then try the register step. I enter my login information for the computer I'm using and use windows authentication. After click okay here's the error I get: Registering Instance - FlightInstance - Updating registry entries (Success) - Installing Windows Service (Error) Messages Required information could not be retrieved from the registry. This usually indicates a problem with setup, configuration, or security. (Microsoft.SqlServer.NotificationServices) For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=ErrorReadingRegistryParameters&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Required information could not be retrieved from the registry. This usually indicates a problem with setup, configuration, or security. (Microsoft.SqlServer.NotificationServices) For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=ErrorReadingRegistryParameters&LinkId=20476 - Installing performance counters (Stopped) I'm running Microsoft SQL Server Management and here's the info from Help->About Microsoft SQL Server Management Studio 9.00.1399.00 Microsoft Analysis Services Client Tools 2005.090.1399.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600 Any clues Tag: Linking Oracle view to SQL Server express
  - 11
    - Trigger? Hello Team i want to make a trigger to delete the orderdate when inserting the record ( if the orderdate is 1/1/1900) Thanks lot Tag: Linking Oracle view to SQL Server express
  - 12
    - Report connection fails with DNS name Hi, I am facing problem while setting connection string in Report Service project. Problem is when I specify DNS name instead of machine name as the Server in the connection string. When I specify DNS name in the connection string it gives error that "A connection cannot be made.Ensure that the server is running" on click of "Test Connection" Also I am not able to view reports directly through IE. I.e. http://<DNS_NAME>/Reports . When I access Reports, it prompts me to enter username/password. Even after specifying valid username, it shows me the prompt again n again. Is there some known issue with Reporting Service or with IIS setting not able to connect to a DNS name I did not face with any issue when I specified DNS name in the connection string of Analysis Service project. Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service We also verified that: 1: Ping with both machine name and DNS name returns same result. 2: nslookup with both machine name and DNS name resolves to same IP. 3: Tried putting the DNS name in the hosts file. Eagerly awaiting some positive reply. Note: I am working with SQL Server 2005 Standard Edition. Tag: Linking Oracle view to SQL Server express
  - 13
    - Creating Stored Procedure in SQLServer2005 I have been using SQL Server 2000 and creating Stored Procedure. I need these Stored Procedure to create Reports in Crystal Reports. In SQL Server 2000: it is very simple to create a stored procedure: Go to Enterprise Manager->Databases-> Choose a database-> stored procedure->New-> Simply Add to the following syntax: CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS GO My problem is that it is More Complex to use Stored Procedure In SQL Server 2005: the stored procedure is under Databases->choose a database->programmability->create new stored procedure: But how can I use it: I need your help It is more complex than the Stored Procedure in SQL Server 2000 Thank you Your help is highly appreciated Tag: Linking Oracle view to SQL Server express
  - 14
    - Secure a single SQL Server database + Make float fields encrypted Hi all, I am developing an application for a big office which uses SQL Server 2000. Apart from my database, on that server, there are two databases by other companies. The administrator also has access to server but the client only wants him to backup the database. I have two questions: 1) First of all (if it is possible) I would like to protect my own database from the other companies. I don't want them to: see the data in the tables (around 20 tables) make changes to the stored procedures (more than 100 stored procedures) be able to backup the database 2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden. I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming. Thanks for your patience reading this! Would really appreciate some help on any of these Nicolas Tag: Linking Oracle view to SQL Server express
  - 15
    - Stored procedure assembly Error Hello there, I am having trouble with the following and appreciate any help.Thanks in advanced. I have created an assembly to delete records in exchange (which works fine when testing it in debug mode). I then create the assembly in SQL server 2005(tried both unsafe and external access btw), and then i create a stored procedure calling the assembly. Now my error occurs when i try to execute this stored procedure in SQL Server. I get the following... Msg 6522, Level 16, State 1, Procedure sp_DeleteExchApp, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_DeleteExchApp': System.Security.SecurityException: System.Security.Permissions.SecurityPermission System.Security.SecurityException: at App_Delete.eSay.AppointmentDelete.DeleteAppointment(Guid GUID) . (1 row(s) affected) Now when i have looked around the web, i have come accross people saying that it is to do with the assembly trying to access something it does not have permission for. But i cant figure out what that is or why it is throwing an error. The code for the assembly is as follows: Public Shared Sub DeleteAppointment(ByVal GUID As System.Guid) Dim CalendarURL As String Dim ItemURL As String Dim Rs As New ADODB.Recordset Dim Rec As New ADODB.Record Dim Conn As New ADODB.Connection Dim iAppt As New Appointment Dim Target As String Dim strCRMGUID As String Dim strGUID As String Dim sSQL As String Dim Check As String strCRMGUID = GUID.ToString 'Using the Exchange OLE DB provider CalendarURL = "file://./backofficestorage/esay-solutions.co.uk/public folders/Office Diary" 'Open a recordset for the items in the calendar folder Rec.Open(CalendarURL) Rs.ActiveConnection = Rec.ActiveConnection sSQL = "SELECT ""DAV:href"", " & _ " ""urn:schemas:calendar:location"", " & _ " ""urn:schemas:calendar:dtstart"", " & _ " ""urn:schemas:calendar:dtend"", " & _ " ""urn:schemas:httpmail:textdescription"" " & _ "from scope('shallow traversal of """ & CalendarURL & """')" Rs.Open(sSQL, Rec.ActiveConnection, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockOptimistic, 1) 'Enumerate the recordset, checking each item's location If Rs.RecordCount > 0 Then Rs.MoveFirst() Do Until Rs.EOF 'get the location of each item Check = Rs.Fields(CdoHTTPMail.cdoTextDescription).Value strGUID = StripGUID(Check) 'test for desired location If (strGUID = strCRMGUID) Then Rs.Delete() End If Rs.MoveNext() Loop End If End Sub Private Shared Function StripGUID(ByVal i_strAppDescription As String) As String 'Function to strip the GUID from the message field Dim r As Integer Dim SearchChar As String Dim testPos As Integer Dim GUID As String SearchChar = "**|" testPos = InStr(i_strAppDescription, SearchChar) testPos = testPos + 3 If testPos > 0 Then GUID = Mid(i_strAppDescription, testPos) End If StripGUID = GUID End Function End Class End Namespace Thanks again for any help. Tag: Linking Oracle view to SQL Server express
  - 16
    - Referencing a conditional column in the WHERE clause -- Possible? I have two tables which I'm joining in a query. In the join, I'm creating a column whose value is conditional (see columns Actual_Latitude and Actual_Longitude below). Is it possible to reference the created column in the WHERE clause Consider the following query: Tag: Linking Oracle view to SQL Server express

Stara

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Re: Linking Oracle view to SQL Server express

Jens K. Suessmeyer

Which driver did you use Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

Re: Linking Oracle view to SQL Server express

Stara

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example

Thanks,

Stara

Re: Linking Oracle view to SQL Server express

Mike Wachal - MSFT

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike