with text_lex stored parametros linked expecting concatenate con sql parameters openquery

sql - text_lex - openquery stored procedure with parameters



incluidos los parĂ¡metros en OPENQUERY (11)

¿Cómo puedo usar un parámetro dentro de sql openquery, como por ejemplo:

SELECT * FROM OPENQUERY([NameOfLinkedSERVER], ''SELECT * FROM TABLENAME where field1=@someParameter'') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK


Combine SQL dinámico con OpenQuery. (Esto va a un servidor de Teradata)

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;


De la documentación de OPENQUERY dice que:

OPENQUERY no acepta variables para sus argumentos.

Vea este article para una solución alternativa.

ACTUALIZAR:

Como se sugirió, incluyo las recomendaciones del artículo a continuación.

Pase los valores básicos

Cuando se conoce la instrucción básica de Transact-SQL, pero debe pasar uno o más valores específicos, use un código que sea similar al siguiente ejemplo:

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)

Pase la consulta completa

Cuando tenga que pasar toda la consulta de Transact-SQL o el nombre del servidor vinculado (o ambos), use un código que sea similar al siguiente ejemplo:

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)

Utilice el procedimiento almacenado Sp_executesql

Para evitar las comillas de varias capas, use un código similar al siguiente ejemplo:

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


Descubrí una forma que me funciona. Sin embargo, requiere el uso de una tabla reutilizable a la que tenga acceso un servidor vinculado .

Creé una tabla y la llené con los valores que necesito, luego hago referencia a esa tabla a través de un servidor vinculado.

SELECT * FROM OPENQUERY(KHSSQLODSPRD,''SELECT * FROM ABC.dbo.CLAIM A WITH (NOLOCK) WHERE A.DOS >= (SELECT MAX(DATE) FROM KHSDASQL01.DA_MAIN.[dbo].[ALLFILENAMES]) '')


Desde la OPENQUERY :

OPENQUERY no acepta variables para sus argumentos

Fundamentalmente, esto significa que no puede emitir una consulta dinámica. Para lograr lo que intenta tu muestra, prueba esto:

SELECT * FROM OPENQUERY([NameOfLinkedSERVER], ''SELECT * FROM TABLENAME'') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK where T1.field1 = @someParameter

Claramente, si su tabla TABLENAME contiene una gran cantidad de datos, esto también afectará a la red y el rendimiento puede ser pobre. Por otro lado, para una pequeña cantidad de datos, esto funciona bien y evita los costos indirectos de construcción de sql dinámicos (inyección de sql, comillas de escape) que podría requerir un enfoque exec .


Ejemplo simple basado en el ejemplo anterior de @Tuan Zaidi que parecía el más fácil. No sabía que puedes hacer el filtro en el exterior de OPENQUERY ... ¡mucho más fácil!

Sin embargo, en mi caso, necesitaba rellenarlo en una variable, así que creé un Nivel de Sub Consulta adicional para devolver un solo valor.

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)


En el siguiente ejemplo estoy pasando un parámetro de departamento a un procedimiento almacenado (spIncreaseTotalsRpt) y al mismo tiempo estoy creando una tabla temporal todo desde una OPENQUERY. La tabla Temp debe ser una Temp global (##) para que pueda ser referenciada fuera de su integridad. Al usar exec sp_executesql puede pasar el parámetro de departamento.

Nota: tenga cuidado al usar sp_executeSQL. Además, es posible que su administrador no tenga esta opción disponible para usted.

Espero que esto ayude a alguien.

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

Resultados

Aumento del departamento Cnt 0 1 2 3 4 5 6 0.0000 1.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000


En realidad, encontramos una forma de hacer esto:

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

Esto asignará el resultado de la ejecución de OpenQuery, en la variable @Output.

Probamos para el procedimiento Store en MSSQL 2012, pero deberíamos trabajar con MSSQL 2008+.

Microsoft dice que sp_executesql (Transact-SQL): Se aplica a: SQL Server (SQL Server 2008 a versión actual), base de datos SQL de Windows Azure (versión inicial a versión actual). ( http://msdn.microsoft.com/en-us/library/ms188001.aspx )


Puede ejecutar una cadena con OPENQUERY una vez que la desarrolle. Si sigue esta ruta, piense en la seguridad y tenga cuidado de no concatenar el texto ingresado por el usuario en su SQL.

DECLARE @Sql VARCHAR(8000) SET @Sql = ''SELECT * FROM Tbl WHERE Field1 < ''''someVal'''' AND Field2 IN ''+ @valueList SET @Sql = ''SELECT * FROM OPENQUERY(SVRNAME, '''''' + REPLACE(@Sql, '''''''', '''''''''''') + '''''')'' EXEC(@Sql)


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.


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


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]