0

I am moving a stored procedure from SQL Server 2005 to SS2008R2. I am using Openquery with date parameters. The 2008 Linked Server is configured with Oracle Provider for OLE DB.

The relevant code looks like this:

 DECLARE @beginDate DATETIME  
 DECLARE @beginDateStr  VARCHAR(11)
 DECLARE @sqlStr    VARCHAR(MAX)
 SET @beginDate     = GETDATE()
 SET @beginDateStr  = CONVERT(VARCHAR(10), @beginDate, 20)
 --Pass through query string (partial)
 SET @sqlStr        = '[date_field] >= ''''' + @beginDateStr + ''''' '

The relevant string resolves to this:

[date_field] >= ''15 Apr 2015''

The query returns the correct results when I execute in SSMS or VS/BIDS and also when deployed in a report .rdl to Report Server. There are many subscriptions associated with this report.

We are experiencing daily report server issues: blocks, increased wait times including preemptive_oledbops, orphaned queries, and failure to connect to data source. When I rolled back the issues subsided. I am trying to see if this is coincidence or related, and so I'd like to know if there is an issue with the date logic above.

Sorry this is vague, but I am having trouble pinpointing the Report Server issues and this is the only new factor. I am trying to eliminate it as the cause.

emreimer
  • 71
  • 1
  • 1
  • 7
  • does the stored procedure lock any tables through inserts/updates/deletes? What version of Oracle? What is the default date format for the Oracle server? – kevinskio Apr 20 '15 at 19:03
  • The larger query above follows this format: SELECT * INTO table1 FROM OPENQUERY(linkedSvr, 'SELECT a, b, c FROM sourcetable'). There are no locks (I don't think). Oracle version is 10g. Default date format is 'dd/mon/yyyy'. – emreimer Apr 20 '15 at 19:18

0 Answers0