Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

Question

I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink

Oracle throws error :

select * from "test_table"@dbLink
Query Execution started at Thu Mar 08 17:57:50 IST 2012
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DBLINK
ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], []`

On further analysis I realized this happens only for tables which have TEXTIMAGE_ON [Primary] (Specifying that text, ntext, and image columns are stored on PRIMARY filegroup)

Then I went over the create command for this table which is

CREATE TABLE [dbo].[test_table](
[ADJ_ID] [numeric](19, 6) NULL,
[ADJ_TYPE] [numeric](19, 6) NULL,
[PK_HASH] [nvarchar](255) NULL,
[WH_HASH] [nvarchar](255) NULL,
[ADJ_REVISION] [numeric](19, 6) NULL,
[LAST_MODIFIED_BY] [nvarchar](255) NULL,
[LAST_MODIFIED_DATE] [datetime] NULL,
[COMMENTFIELD] [nvarchar](2000) NULL,
[DATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The same error is not thrown for tables which do not have filegroup specified as PRIMARY or if I have the same table without clause TEXTIMAGE_ON [PRIMARY]

Can any one suggest a solution to overcome this error?

Thanks

The trace content is

` 1.exec sp_unprepare 1

2.SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM "test_table" "A1"

3.declare @p1 int set @p1=1 exec sp_prepare @p1 output,NULL,N' SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM "test_table" "A1"',1 select @p1`

Thanks.

Answer 1

Filegroup is completely irrelevant: a table doesn't know where it is and internal storage allocation or placement won't affect a query.

If you have a single MDF you most likely only have one PRIMARY (or default) filegroup anyway

image datatype is deprecated too: use varbinary(max).

Have you run SQL Profiler to see what Oracle is sending to SQL Server? Is there a call actually being made?

Edit: why does the 2nd trace use a table called "P44730584C4264287A49ABEAF_ADJ"

• Egalitarian: I have added the contents of trace in my question
• Egalitarian: Do you have any inputs ?
• Egalitarian: :Sorry, That table's actual name is P44730584C4264287A49ABEAF_ADJ, I just made it test_table for readability.
• Egalitarian: Any suggestions ??
• gbn: @Egalitarian: no, no idea.