including parameters in OPENQUERY

Question

How can I use a parameter inside sql openquery  such as    SELECT   FROM OPENQUERY  NameOfLinkedSERVER    SELECT   FROM TABLENAME where field1  someParameter   T1 INNER JOIN MYSQLSERVER DATABASE DBO TABLENAME T2 ON T1 PK   T2 PK

User · Answer

Simple example based off of  Tuan Zaidi s example above which seemed the easiest  Didn t know you can do the filter on the outside of OPENQUERY    so much easier   However in my case I needed to stuff it in a variable so I created an additional Sub Query Level to return a single value   SET  SFID    SELECT T Id FROM  SELECT Id   Contact ID SQL  c  FROM OPENQUERY  TR-SF-PROD    SELECT Id   Contact ID SQL  c FROM Contact   WHERE Contact ID SQL  c    ContactID  T

User · Answer

declare  p Id varchar 10  SET  p Id    40381   EXECUTE   BEGIN update TableName                 set     ColumnName1   null                          ColumnName2   null                          ColumnName3   null                          ColumnName4   null                  where   PERSONID       p Id     END    AT  linked Server Name

User · Answer

We can use execute method instead of openquery  Its code is much cleaner  I had to get linked server query result in a variable  I used following code   CREATE TABLE  selected store      code VARCHAR 250      id INT   declare  storeId as integer   25 insert into  selected store  id  code  execute  SELECT store id  code from quickstartproductionnew store where store id        storeId  at  MYSQL     declare  code as varchar 100  select  code   code from  selected store select  code drop table  selected store   Note       if your query doesn t work  please make sure remote proc transaction   promotion is set as false for your linked server connection    EXEC master dbo sp serveroption         server   N  linked server name            optname   N remote proc transaction promotion           optvalue   N false

User · Answer

From the MSDN page      OPENQUERY does not accept variables for its arguments   Fundamentally  this means you cannot issue a dynamic query  To achieve what your sample is attempting  try this   SELECT   FROM     OPENQUERY  NameOfLinkedSERVER    SELECT   FROM TABLENAME   T1     INNER JOIN     MYSQLSERVER DATABASE DBO TABLENAME T2 ON T1 PK   T2 PK  where    T1 field1    someParameter   Clearly if your TABLENAME table contains a large amount of data  this will go across the network too and performance might be poor  On the other hand  for a small amount of data  this works well and avoids the dynamic sql construction overheads  sql injection  escaping quotes  that an exec approach might require

User · Answer

DECLARE  guid varchar 36    select  guid  convert varchar 36   NEWID             The one caveat to this technique is that   ContextSpecificGlobal  Temp should ALWAYS have the exact same columns        So make up your global temp table name in the sproc you re using it in and only there      In this example I wanted to pass in the name of a global temporary table dynamically   I have 1 procedure dropping      off temporary data in whatever  TableSrc is and another procedure picking it up but we are dynamically passing      in the name of our pickup table as a parameter for OPENQUERY     IF   OBJECT ID  tempdb    ContextSpecificGlobal  Temp     U   IS NULL       EXEC   SELECT   INTO   ContextSpecificGlobal  Temp FROM OPENQUERY loopback    Select             guid        as tempid FROM      TableSrc          ELSE      EXEC   INSERT   ContextSpecificGlobal  Temp SELECT   FROM OPENQUERY loopback    Select             guid        as tempid FROM      TableSrc           --If this proc is run frequently we could run into race conditions  that s why we are adding a guid and only deleting --the data we added to   ContextSpecificGlobal  Temp SELECT   INTO  TableSrc FROM   ContextSpecificGlobal  Temp WHERE tempid    guid  BEGIN TRAN t1     IF   OBJECT ID  tempdb    ContextSpecificGlobal  Temp     U   IS NOT NULL        BEGIN         -- Here we wipe out our left overs if there if everyones done eating the data         IF  SELECT COUNT    FROM   ContextSpecificGlobal  Temp    0             DROP TABLE   ContextSpecificGlobal  Temp     END COMMIT TRAN t1  -- YEAH  Now I can use the data from my openquery without wrapping the whole        thing in a string

User · Answer

Actually  We found a way to do this   DECLARE  username varchar 50  SET  username    username  DECLARE  Output as numeric 18 4  DECLARE  OpenSelect As nvarchar 500  SET  OpenSelect     SELECT  Output   CAST  CAST pwdLastSet As bigint    864000000000  As numeric 18 4   FROM OpenQuery  ADSI   SELECT pwdLastSet                                 FROM      LDAP   domain net intra DC domain DC net DC intra                                     WHERE objectClass        User     AND sAMAccountName            username                                       AS tblADSI   EXEC sp executesql  OpenSelect  N  Output numeric 18 4  out    Output out SELECT  Output As Outputs   This will assign the result of the OpenQuery execution  in the variable  Output   We tested for Store procedure in MSSQL 2012  but should work with MSSQL 2008    Microsoft Says that sp executesql Transact-SQL   Applies to  SQL Server  SQL Server 2008 through current version   Windows Azure SQL Database  Initial release through current release    http   msdn microsoft com en-us library ms188001 aspx

User · Answer

Just try it this way  should work  easy  In your WHERE clause  after column name and equal to sign - add TWO single quotes  your search value and then THREE single quotes  Close the bracket  SELECT   FROM OPENQUERY  NameOfLinkedSERVER    SELECT   FROM TABLENAME where field1   your search value     T1 INNER JOIN MYSQLSERVER DATABASE DBO TABLENAME T2 ON T1 PK   T2 PK

User · Answer

Combine Dynamic SQL with OpenQuery   This goes to a Teradata server   DECLARE       dayOfWk    TINYINT   DATEPART DW  GETDATE          qSQL       NVARCHAR MAX         SET  qSQL     SELECT       FROM     OPENQUERY TERASERVER            SELECT DISTINCT             CASE                 WHEN     CAST  dayOfWk AS NCHAR 1         2                 THEN     Monday                     ELSE     Not Monday                 END                 EXEC sp executesql  qSQL

User · Answer

In the following example I m passing a department parameter to a stored procedure spIncreaseTotalsRpt  and  at the same time I m creating a temp table all from an OPENQUERY  The Temp table needs to be a global Temp      so it can be referenced outside it s intance   By using exec sp executesql you can pass the department parameter   Note  be careful when using sp executeSQL  Also your admin might not have this option available to you   Hope this helps someone      IF OBJECT ID  tempdb    Temp   IS NOT NULL   Then it exists       begin        DROP TABLE   Temp     end   Declare  Dept as nvarchar 20      47      declare  OPENQUERY  as nvarchar max  set  OPENQUERY    Select      Dept     AS Dept     into   Temp from openquery SQL AWSPROD01       declare  sql nvarchar max    openquery     SET FMTONLY OFF EXECUTE SalaryCompensation dbo spIncreaseTotalsRpts             Dept                 declare  parmdef nvarchar 25   DECLARE  param nvarchar 20    SET  parmdef   N  Dept varchar 20   -- select  sql -- Print  sql    parmdef     dept exec sp executesql  sql  parmdef   Dept   Select   from   Temp   Results    Dept    increase Cnt    0   1   2   3   4   5   6         0 0000  1 0000  0 0000  0 0000  0 0000  0 0000  0 0000  0 0000

User · Answer

From the OPENQUERY documentation it states that      OPENQUERY does not accept variables   for its arguments    See this article for a workaround   UPDATE   As suggested  I m including the recommendations from the article below   Pass Basic Values  When the basic Transact-SQL statement is known  but you have to pass in one or more specific values  use code that is similar to the following sample    DECLARE  TSQL varchar 8000    VAR char 2  SELECT   VAR    CA  SELECT   TSQL    SELECT   FROM OPENQUERY MyLinkedServer   SELECT   FROM pubs dbo authors WHERE state            VAR             EXEC   TSQL    Pass the Whole Query  When you have to pass in the whole Transact-SQL query or the name of the linked server  or both   use code that is similar to the following sample   DECLARE  OPENQUERY nvarchar 4000    TSQL nvarchar 4000    LinkedServer nvarchar 4000  SET  LinkedServer    MyLinkedServer  SET  OPENQUERY    SELECT   FROM OPENQUERY     LinkedServer         SET  TSQL    SELECT au lname  au id FROM pubs  authors      EXEC   OPENQUERY  TSQL     Use the Sp executesql Stored Procedure  To avoid the multi-layered quotes  use code that is similar to the following sample   DECLARE  VAR char 2  SELECT   VAR    CA  EXEC MyLinkedServer master dbo sp executesql N SELECT   FROM pubs dbo authors WHERE state    state   N  state char 2     VAR

User · Answer

SELECT field1 FROM OPENQUERY                       NameOfLinkedSERVER                        SELECT field1 FROM TABLENAME                               WHERE field1  someParameter T1                                   INNER JOIN MYSQLSERVER DATABASE DBO TABLENAME                                             T2 ON T1 PK   T2 PK

User · Answer

I figured out a way that works for me  It does require the use of a scratch table that a linked server has access to though   I created a table and populated it with the values I need then I reference that table through a linked server   SELECT    FROM OPENQUERY KHSSQLODSPRD  SELECT     FROM ABC dbo CLAIM A WITH  NOLOCK    WHERE A DOS  gt    SELECT MAX DATE  FROM KHSDASQL01 DA MAIN  dbo   ALLFILENAMES

User · Answer

You can execute a string with OPENQUERY once you build it up   If you go this route think about security and take care not to concatenate user-entered text into your SQL   DECLARE  Sql VARCHAR 8000  SET  Sql    SELECT   FROM Tbl WHERE Field1  lt    someVal   AND Field2 IN     valueList  SET  Sql    SELECT   FROM OPENQUERY SVRNAME        REPLACE  Sql                        EXEC  Sql

[sql] including parameters in OPENQUERY

Examples related to sql

Examples related to parameters

Examples related to openquery