Create table from results of >8000 char SQL Linked Server query

Question

I'm trying to create a table on SQL Server based on the results of Oracle SQL >8k chars. Is this possible without openquery which has that 8k limitation.

Here's what i'm trying to do but can't seem to get it to work:

SELECT * INTO #TMP001 EXEC ('select trunc(sysdate) curr_day from dual') AT LinkedServerN

Throws Err:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Any help would be appreciated.

Thank you,

Answer 1

Create the result table , even if it's temporary. After this use insert into instead of select * into .

Answer 2

Good day Dee,

OPENROWSET is not limited to 8000 chars. Please check this quote especially the part I bold:

"OPENROWSET(BULK...) assumes that, if not specified, the maximum length of SQLCHAR, SQLNCHAR or SQLBINARY data does not exceed 8000 bytes."

When you use OPENROWSET on data from SQL Server table using the provider SQLNCLI you can use it directly on tables with column type MAX.

When you use external file for the data then the solution is simply to use a format file which defines the length for the column to MAX.

Unfortunately, I am not sure how this work with Oracle since I did not tested it.

Here is a full example using SQL Server as source using a column MAX

use tempdb
GO

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO  

DROP TABLE IF EXISTS T
GO
CREATE TABLE T (MaxText NVARCHAR(MAX))
GO

TRUNCATE TABLE T;   
INSERT T(MaxText) VALUES ('Ronen')
GO
DECLARE @T NVARCHAR(MAX) = 
    CONVERT(NVARCHAR(MAX),'')
    + REPLICATE(N'Ronen', 1600) -- 8k
    + REPLICATE(N'Ronen', 1600) -- 8k   
    + REPLICATE(N'Ronen', 1600) -- 8k   
INSERT T(MaxText) VALUES (@T)
GO

SELECT DATALENGTH(MaxText) -- 24000
FROM tempdb.dbo.T
GO

SELECT DATALENGTH(MaxText) 
FROM OPENROWSET(
    'SQLNCLI',
    'Server=LectureVM\SQL2019;Trusted_Connection=yes;', 
    'SELECT MaxText FROM tempdb.dbo.T') AS a;
GO -- 24000 returned from the OPENROWSET

SELECT * INTO #TMP001 
FROM OPENROWSET(
    'SQLNCLI',
    'Server=LectureVM\SQL2019;Trusted_Connection=yes;', 
    'SELECT MaxText FROM tempdb.dbo.T') AS a;
-- (2 rows affected)
SELECT DATALENGTH(MaxText) FROM #TMP001
GO -- 24000

If this does not work in Oracle then:

1. You can use SINGLE_CLOB or SINGLE_NCLOB which will return the data as a single value, length MAX and next you can parse the data

2. Option 2 is to export to csv and as I explain above you can import using bcp or OPENROWSET using format file

3. In addition check this thread on MSDN about how tio use linked server