SQL Server: OPENQUERY in easy words (with example)

Question

I am pretty new to SQL Server, and I have stumbled upon this right here:

BEGIN TRANSACTION loadTTAllocations

    INSERT INTO @ttaAllocs
        SELECT 
            company_code AS CompanyId,
            job_number AS JobNo,
            ...
            is_blocked AS IsBlocked
        FROM 
            OPENQUERY([SRV_TimeTac], 
                       'SELECT DISTINCT 
                            job.individual_value_1 AS job_number,
                            job.individual_value_3 AS company_code,
                            ...
                            now() AS queryTimeStamp
                        FROM 
                            pm_altran.pm_tasks_subprojects AS taskCode
                        INNER JOIN 
                            pm_altran.pm_tasks_subprojects AS job
                                 ON job.id = taskCode.mother_id 
                                 AND job.is_done = 0
                                 AND NOT job.is_blocked
                        INNER JOIN 
                            pm_node_to_user AS n2u
                                 ON n2u.node_id = taskCode.id
                                 AND n2u.access = 1
                                 AND n2u.is_todo = 1
                        LEFT JOIN 
                            altran_pm_user_user_settings AS u
                                 ON u.administrators_id = n2u.user_id
                        WHERE 
                            taskCode.object_type = ''task''
                            AND taskCode.is_paid_non_working = 0
                            AND taskCode.id > 50');

    SET @rowCount = @@ROWCOUNT

    SET @eventDetails = 'End loadTTAllocations: ' + CAST(@rowCount as VARCHAR(10)) + ' rows affected';

    COMMIT TRANSACTION loadTTAllocations

The issue is the OPENQUERY.

What I understand from the docs is basically, that it is a query just on another server. In this case the SRV_TimeTac?

So in this example, we are querying on another server called "SRV_TimeTac" and returning results to be loaded finally into the temp table called @ttaAllocs.

Is this correct?

Thank you for your help.

score 8 · Accepted Answer · answered Jul 02 '19 at 10:55

OPENQUERY (Transact-SQL):

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Arguments

linked_server
Is an identifier representing the name of the linked server.

' query '
Is the query string executed in the linked server. The maximum length of the string is 8 KB.

So, to answer your question, yes, that is what your query is doing. The value of linked_server in your case is SRV_TimeTac and the value of ' query ' is your long literal string. The value of ' query ' is run on the linked server and the result set returned to the server you ran OPENQUERY on. That result set is then inserted into the variable @ttaAllocs.

You interestingly then COMMIT the transaction you started before the INSERT, which seems odd considering you only inserted the value into a variable and didn't effect any persisted objects. The Table Variable may well have ended up being written to disc, rather than stored in Memory, if it got large enough but then you'd very likely be committing the transaction to tempdb.

SQL Server: OPENQUERY in easy words (with example)

1 Answers1