March 14, 2012 at 9:17 am
{Resolved}
HI
The following query was working and then the IT maintance team changed the Client (upgraded the client) and now when i run this query i get the error meesage:
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL'
I am connecting to a remote Sybase Server and using the Linked Server with OPENQUERY below is the query
I have researched this error and it says try using set fmtonly off; but everytime i insert this statement i keep getting syntax errors.
Any Help Would Be Greatly Appreciated
<code>
PRINT 'Fetching data: ' + cast(getdate() as varchar(88))
DECLARE @filter as varchar(222), @sql as nvarchar(4000)
--SET @filter = char(39) + char(39) + Cast(DateAdd(m, -2, GetDate()) as varchar(88)) + char(39) + char(39)
SET @filter = char(39) + char(39) + Cast(DateAdd(mm, -2, convert(datetime,convert(varchar, getdate(),112))) as varchar(88)) + char(39) + char(39)
SET @sql = 'SELECT * INTO stg_dr0_project_afs_header FROM OPENQUERY ( afs_linked , ' + char(39) +
'SELECT Distinct h.* FROM afs.dbo.project_afs_header h INNER JOIN afs.dbo.submission_project s ON h.project_afs_header_id = s.project_afs_header_id
LEFT JOIN (SELECT project_afs_header_id FROM submission_project WHERE proj_name = ' + char(39) + char(39) + 'Combined Statement' + char(39) + char(39) + ') x ON h.project_afs_header_id = x.project_afs_header_id
WHERE x.project_afs_header_id IS NULL AND h.status_ref_id IN (0,1) AND convert(datetime,convert(varchar, h.date_received,112)) > ' + @filter + ' AND s.property_id > 0 ' + char(39) + ' )'
EXEC sp_executesql @sql
</code>
March 14, 2012 at 9:55 am
I formatted your query and now looks a bit more readable.
PRINT 'Fetching data: ' + cast(getdate() as varchar(88))
DECLARE @filter as varchar(222), @sql as nvarchar(4000)
SET @filter = char(39) + char(39) + Cast(DateAdd(mm, -2, convert(datetime,convert(varchar, getdate(),112))) as varchar(88)) + char(39) + char(39)
SET @sql = '
SELECT *
INTO stg_dr0_project_afs_header
FROM OPENQUERY(afs_linked, ' + char(39) + '
SELECT DISTINCT h.*
FROM afs.dbo.project_afs_header h
INNER JOIN afs.dbo.submission_project s
ON h.project_afs_header_id = s.project_afs_header_id
LEFT JOIN (
SELECT project_afs_header_id
FROM submission_project
WHERE proj_name = ' + char(39) + char(39) + ' Combined Statement ' + char(39) + char(39) + '
) x
ON h.project_afs_header_id = x.project_afs_header_id
WHERE x.project_afs_header_id IS NULL
AND h.status_ref_id IN (0,1)
AND convert(DATETIME, convert(VARCHAR, h.date_received, 112)) > ' + @filter + '
AND s.property_id > 0
' + char(39) + ' )'
EXEC sp_executesql @sql
Looks like you're selecting remote tables only: did you try using EXEC(...) AT ?
-- Gianluca Sartori
March 15, 2012 at 11:02 am
thanks, i had to click a property on the actual ODBC driver set up and that took care of the issue
March 15, 2012 at 11:22 am
Great!
Glad you solved your issue.
And, sorry if I have not been helpful enough.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply