Text field and linked servers

• sbauer

  SSCommitted

  Points: 1966

  More actions

  June 14, 2006 at 4:24 pm

  #115552

  One of my developers is having issues pulling over a text field from a linkedserver.

  Can this be done?

• sbauer

  SSCommitted

  Points: 1966

  More actions

  June 14, 2006 at 4:28 pm

  #643825

  Here's the error he gets when executing the query

  C:\projects\Dispatch_Partner.wsf (55, 6) Microsoft OLE DB Provider for SQL Server: Only text pointers are allowed

  in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

   

  There are two columns in the query that are text fields, you take them out the query works.

  Is there a work around to leave them in?

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  June 16, 2006 at 12:25 am

  #644152

  Where is the query? Did it just stop or has it ever worked? What is your linked server, SQL Server?

  DECLARE @server sysname

  SET @server = N'MyServer'

  IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @server)

  BEGIN

   EXEC sp_addlinkedserver @server, N'SQL Server'

   EXEC sp_addlinkedsrvlogin @server

  END

  GO

  INSERT INTO TestDoc

  SELECT TOP 3 *

  --INTO TestDoc -- toggle INSERT vs INTO for testing

  FROM MyServer.MyDB.dbo.Document

  --WHERE DocumentNo_PK IS NULL -- Comment line for INSERT

  SELECT DATALENGTH(Document) AS image_length FROM TestDoc

  /* results in

  image_length

  --------------

  302847

  9657

  14862

  (3 row(s) affected)

  */

  Andy