Sybase server linked in SSMS using Concatenate in openquery
concatenation,sybase,ssms,openquery
realized the columns needed to be converted to same data type... added conversions and good ole fashioned + worked.
Using IIF function in OPENQUERY
sql-server,dynamic-sql,linked-server,iif,openquery
I think the version of SQL Server you are using is under 2012 and IIF is not supported for earlier versions. You can use CASE instead. SELECT Sum(CASE Left(matcode,1) WHEN 'R' THEN 1 ELSE 0 END) AS Cases FROM cases WHERE cases.date_opened = '20150511' Edit Based on Zerubbabel comment, he...
sp_procoption is not executing strored procedure with OPENQUERY
sql-server,stored-procedures,openquery
This is just simple. The startup procedure is triggered before the connection to the linked servers is established. Therefore you cannot use a linked server in stored procedure which is used as a startup procedure. One suggestion could be to generate a SQL Server Agent Job which will be generated...
UPDATE local MSSQL table from Identical MySQL Linked Server Table Where Clause
mysql,sql-server,linked-server,openquery
Something like this should work. You can also use synonyms, if the .... get a bit much: ;with cte as (SELECT * from OPENQUERY(LinkedServer, 'SELECT * FROM employees') ) UPDATE MSSQLDB.dbo.employer SET employer.employeescol1 = cte.employeescol1 FROM MSSQLDB.dbo.employer inner join cte on cte.idemployees = employer.idemployee ...
SQL Server OpenQuery - Passing parameter result from main SELECT query to sub query
You can't send variables into OpenQuery context in this way. Try to filter the query "outside": DECLARE @UserID int SELECT @UserID = UserID FROM dbo.Users As TEST WHERE (RecordID = 123456) (SELECT t.displayName FROM OpenQuery ( ADSI, 'SELECT displayName, extensionattribute5 FROM ''LDAP://DC=company,DC=local'' WHERE objectCategory=''user'' ') as t WHERE [email protected] )...
Remote query with variable using MSDASQL
tsql,sql-server-2008-r2,linked-server,openquery,msdasql
with help from Oleksandr Kucher I managed to do what I want with the below code (I added char(39) to the query string to force the quotes): declare @EarliestDate varchar(8), @SQL NVARCHAR(1000), @sDate varchar(8) SET @EarliestDate= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4)) + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '01'...
Escape single quote in openquery using dynamic query
sql-server,dynamic-sql,linked-server,openquery
You need single quotes around your variables since you are trying to make them string literals. But also complicating it is the fact that you are trying to create a SQL statement in a string that includes another SQL statement in a string. So you need to make your line...
SQL Server 2012 - Insert into linked server table using openquery
Try this INSERT INTO OPENQUERY([Remoteserver] ,'SELECT subdirectory, depth, [file] FROM [RemoteDB].[dbo].[files]') EXEC master.sys.xp_dirtree '\\fileserver\DBBackup', 1, 1; OR INSERT INTO OPENQUERY([Remoteserver] ,'SELECT subdirectory,depth, [file] FROM [RemoteDB].[dbo].[files]') select * from OPENQUERY([another_server_name], 'master.sys.xp_dirtree ''\\fileserver\DBBackup\temp'', 1, 1'); But in general you do not need to use OPENQUERY at all if Fileserver and Remoteserver are...
sql open query on active directory with multiple where clause
More than one WHERE clause? Or you just want to put the filters together with "AND"? WHERE physicaldeliveryofficename= "*" AND objectCategory="person" ...