InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:54:42 GMT20http://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxIf someone or something has the file open when your code runs, it will give such an error message. Bouncing the server seems to clear it because the file is on the server and bouncing it forces a disconnect.Fri, 30 Jan 2009 17:53:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxI'm sorry this is not a reply to your question...this is more a question to u guys since you guys are using the OPENROWSET and nobody has replied in the other group...I am trying to export data to excel file....this is the story...This sp works for couple of times after restarting the service and then it stops working.The reason for this sp is to run in a job every 8 hours....I did follow some instructions from MS...site and it did made sense since I saw that it is writing to this local dir.. C:\TEMPhttp://support.microsoft.com/kb/814398Could anyone provide me some new guide to this issue.....?This is the error I get when execute the sp:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Unspecified error]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].This is my code:DECLARE @channel AS VARCHAR(10)DECLARE @feed AS VARCHAR(10)DECLARE @OriginalFile AS VARCHAR(100) DECLARE @FilePathName AS VARCHAR(100)DECLARE @VBSFilePathName AS VARCHAR(100)DECLARE @Msg AS VARCHAR(250)DECLARE @ReturnStatus INTDECLARE @DateRpt AS VARCHAR(15)--Drop and Create temporal tables IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE ID = object_id(N'tempdb.dbo.#TmpSpeIngest')) BEGIN DROP TABLE #TmpSpeIngest END CREATE TABLE #TmpSpeIngest ( LogChannel NCHAR (15) NOT NULL, LogFeed NCHAR (20) NOT NULL, LogDate VARCHAR (50) NULL) --LogFiles Avaliable INSERT #TmpSpeIngest SELECT DISTINCT LogChannel, LogFeed, LogDate FROM tb_spe_ingest WHERE LogDate = @date ORDER BY LogChannel, LogFeedINSERT INTO OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Program Files\Microsoft SQL Server\MSSQL\REPORTS\ReporteCombinado.xls', 'SELECT * FROM [AvailableLogsForReport$]')SELECT * FROM #TmpSpeIngestAny help is greatly appreciated....Regards,JoseFri, 30 Jan 2009 15:28:03 GMTjluis_hdezghttp://www.sqlservercentral.com/Forums/Topic561539-65-1.aspx[quote][b]rg (9/8/2008)[/b][hr]Just wanted to follow-up to above: Currently suspecting network latency, as SQL test and Oracle prod servers are located in West Coast data center, whereas SQL prod server is located in East Coast data center,...[/quote]Heh... yeah, that might do it. We had a million row job that took 10 minutes to run locally... took almost 3 hours remotely. The fix was to do the job locally and only transfer the answer remotely. Even that still took a bit of time.Mon, 08 Sep 2008 19:11:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxJust wanted to follow-up to above: Currently suspecting network latency, as SQL test and Oracle prod servers are located in West Coast data center, whereas SQL prod server is located in East Coast data center, doing additional tests.Please confirm: From what I understand how OPENROWSET works, the query is processed on remote Oracle server and the WHERE clause processed on the remote server as well, only qualifying rows are sent back to SQL Server (vs. all records being sent to SQL server and WHERE clause being processed on local SQL server)? is this correct?Jeff: 1. Not relevant, as I'm testing against prod Oracle view only 2. Not relevant, as I'm testing against prod Oracle view only 3. Not using linked server, using OPENROWSET4. Already using a view, though I did confirm the view selects all records from underlying table of 70K rows, no index on LAST_MODIFIED_DATE--but would expect to see similar run-time from both SQL prod and SQL test if issue was remote Oracle prod view related. Also, shouldn't be an issue since using OPENROWSET, correct me if I'm wrong. I understand that we can add index and also modify view to include the WHERE clause (if it doesn't impact other code), but that is not the underlying issue at this point from what we can tell.thank you again for feedback...rgMon, 08 Sep 2008 10:59:20 GMTrg-722805http://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxDoes the Oracle table have the same number of rows in test and prod? Does the Oracle table in test and prod have the same indexes? When you use the linked servers, are both OLE DB or is one ODBC? And, why are you selecting against a whole Oracle table instead of an Oracle view that automatically limits the number of rows you want to return?Sat, 30 Aug 2008 16:11:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxI will check on the network route between Oracle server and SQL Server prod vs. test next week when I'm back at the office, as I questioned that too...thanks for reminder/suggestion...rgSat, 30 Aug 2008 14:24:39 GMTrg-722805http://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxthere's additional logic throughout sp, for example @openrowset is assigned to point to prod vs. test Oracle server depending on whether sp runs on SQL Server prod server vs. test server; I didn't include that since didn't think that was relevant to this post...I'm concentrating on why select takes so much longer to run against prod Oracle view when run from SQL Server prod server vs. SQL Server test server....is it memory pressure related? is it tempdb activity related? etc...I can certainly try without dynamic sql, just not sure that's the issue...thanks for your feedback... rgSat, 30 Aug 2008 14:16:54 GMTrg-722805http://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxIs the network route the same between the dev server and oracle server and the prod server and oracle server?Why are you using dynamic SQL?Fri, 29 Aug 2008 17:09:19 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic561539-65-1.aspxHello,We have a sp that runs 3 times each day in production, taking over 3 minutes each run. The sp is using OPENROWSET to select records from a remote Oracle view to populate local SQL Server table. After running profiler trace to identify where the bottleneck is, I reduced the code to bare minimum to test and noticed that just the SELECT against OPENROWSET is taking majority of time. Running same SELECT against the remote production Oracle view I get the following results: query finishes in 4 seconds on SQL Server test server, 3 minutes on SQL Server production server; both show 'Remote Scan' in the execution plan. Remote prod Oracle view is about 70K records and SELECT pulls last 30 days, which equates to about 2500 records. SQL Server test server is a clone of production SQL Server, though not clustered, and mostly idle compared to the busy production server.Win2003 SP2SQL Server 2000 Enterprise 32-bit SP4 build 8.00.218932GB RAM8 CPUsI've already done some research on OPENQUERY/Linked Servers vs. OPENROWSET performance, but I guess right now I'm questioning the difference in query run time between SQL Server test vs. production. Looking for some suggestions as to where to look next to try to troubleshoot and improve the sp performance. Any insight would be much appreciated. :)Please see sample code below...Thanks,rgsample of the code:DECLARE @qry nvarchar(4000)DECLARE @date varchar(255)DECLARE @openrowset varchar(255)..SET @openrowset = 'SELECT * FROM openrowset(''MSDAORA'',''xxxxxx'';''xxxxxx'';''xxxxxx'',''' + char(10)SET @qry = @openrowset +'SELECT ID,......FROM view_name where LAST_MODIFIED_DATE > to_timestamp('''''+@date+''''',''''YYYY-MM-DD HH24:MI:SS'''')'')'EXEC (@qry)Fri, 29 Aug 2008 15:16:46 GMTrg-722805