October 8, 2012 at 3:51 am
We are trying to fetch Teradata tables in SQL server 2005. I installed OLEDB driver for Teradata on server hosting SQL server.
I created a linked server to Teradata. When I test the connection to this server, it indicates that test was successful.
But when I try to fetch the data using the following queries
SELECT * FROM TDPROD..MMM_PRD_STG_TBLS.tbl_aa_cltv_fact
SELECT *
FROM OPENQUERY(TDPROD, 'SELECT period_id FROM MMM_PRD_STG_TBLS.tbl_aa_cltv_fact')
===
I get the following error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "TDOLEDB.1" for linked server "TDPROD" reported an error. The provider ran out of memory.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "TDOLEDB.1" for linked server "TDPROD". The provider supports the interface, but returns a failure code when it is used.
Please advise on how we can resolve this issue.
Thanks & Regards,
David
October 8, 2012 at 7:56 am
x86 or x64?
Is using SSIS an option?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 8, 2012 at 8:23 am
we are using x86 and the reason we wanted to link the servers was to create stored procedures and create jobs that will run at night to update sql table with data from teradata.what suprise me is that i can read the teradata table with no errors when i use SSIS and SSIS.
October 8, 2012 at 9:45 am
dnonyane (10/8/2012)
we are using x86 and the reason we wanted to link the servers was to create stored procedures and create jobs that will run at night to update sql table with data from teradata.
Now that's what I call an anti-pattern 🙂
what suprise me is that i can read the teradata table with no errors when i use SSIS and SSIS.
This is why I asked. SSIS is a much safer option for you on an x86 platform. Linked Servers use non-buffered memory and on an x86 server this comes from the VAS Reservation (known as MemToLeave) which is a limited section of memory allocated for things like Linked Server memory and database backups.
Here is an article that explains it all in great detail. Understanding the VAS Reservation (aka MemToLeave) in SQL Server
What likely happened during your query is SQL Server ran out of memory while trying to pull the data from TeraData into its own memory. You would be much better off (on multiple levels) doing the data movement using SSIS.
Here is just an example of how it might work. This may not apply to your situation exactly but hopefully the intent will come across:
1. SSIS reads from SQL Server the "most recent date or ID" of the data
2. SSIS uses the "most recent date or ID" to get more current data from TeraData and loads into staging table on SQL Server
3. SSIS calls stored procedure on SQL Server to apply data in staging table to SQL Server tables
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 23, 2012 at 1:56 am
Hi again,im still strugling with the following initial problem.what is worse is that all peolple with admin account can run the script fine but when running with ur normal user logon account it returns the error in the post
November 23, 2012 at 5:53 am
The error you posted originally points to a VAS issue. The fact that only lesser-privilege users experience the issue could mean the Teradata driver is reporting misleading errors or that SQL Server is misinterpreting the error from the driver.
If it were me I would migrate the process to SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply