syntax error get data from linked server

Question

Post reply

syntax error get data from linked server

Bruin

SSCrazy

Points: 2058

April 6, 2022 at 1:45 pm

Go to Answer

#4011630

This query runs but doesn't get any info. I think it's something in the syntax, but can't find the issue. If I query a table using Linked Server I can see the data I need.

DECLARE @SQL VARCHAR(MAX) = ''
;
 SELECT @SQL += REPLACE(REPLACE('
        RAISERROR("-----------------------------------",0,0) WITH NOWAIT;
        RAISERROR("Processing <<TableName>>",0,0) WITH NOWAIT;
 INSERT INTO BC_Data_Capture_Staging
        ([timestamp],equipid,speed)
 SELECT 
         [timestamp] = stamp_time
 ,equipid = SUBSTRING(REPLACE("<<TableName>>","line",""),2,128)
        ,speed
        
   FROM linksrv.DB.dbo.[<<TableName>>],
   LastDataCaptured
   where stamp_time > BC_Last_Capture_Ifacts3
  ORDER BY Stamp_time DESC
;'      ,'"','''')
        ,'<<TableName>>',obj.name)
   FROM sys.objects obj
  WHERE obj.name  LIKE 'F[0-9][0-9][0-9]%'
    AND type      = 'U'
    AND schema_id = 1
;
   
   EXEC(@SQL)
;

ratbak

Ten Centuries

Points: 1358
More actions
April 6, 2022 at 4:59 pm
Answer
#4011720

But your original query isn't querying sys.objects on the linked server, it's querying it on the server trying to use that linked server remotely.
If those tables you need in sys.objects are on the linked server and not the querying server where you're trying to insert, then you need to reference sys.objects on the linked server in your dynamic sql.
April 14, 2022 at 9:06 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Assuming dbo.BC_Data_Capture_Staging exists in the database in which you execute this statement:

Comment out or remove the INSERT lines so you are just selecting & debug:
1. print instead of executing
2. copy & paste the sql & see if it parses
3. If it parses, execute & see interpret what you get.
If that works, uncomment/reinsert the INSERT lines & repeat.

Bruin SSCrazy Points: 2058 More actions · Answer 2

I tried those steps, but the Print didn't give me any output.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

If print didn't return anything, that tells you you're getting a null when concatenating. Since you're only concatenating obj.name, that seems to imply that the following returns nothing in the server/database in which you are trying to execute that script. Does this return anything?

SELECT *
FROM sys.objects obj
WHERE obj.name LIKE 'F[0-9][0-9][0-9]%'
AND type = 'U'
AND schema_id = 1

Bruin SSCrazy Points: 2058 More actions · Answer 4

yes when i run on the linked server it returns the 90 tables i'm trying to query.

Bruin SSCrazy Points: 2058 More actions · Answer 5

Bruin

SSCrazy

Points: 2058

April 6, 2022 at 5:26 pm

#4011740

ahh good catch

Thanks!!