tsql,merge,identity,openquery

An explicit value for the identity column in table 'Database1.dbo.Table' can only be specified when a column list is used and IDENTITY_INSERT is ON. You have no column list...

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.

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 ...

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...

SQL Server 2012 - Insert into linked server table using openquery

sql-server,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 Server OpenQuery - Passing parameter result from main SELECT query to sub query

sql-server,openquery

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] )...

sql open query on active directory with multiple where clause

mysql,ldap,adsi,openquery

More than one WHERE clause? Or you just want to put the filters together with "AND"? WHERE physicaldeliveryofficename= "*" AND objectCategory="person" ...

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...

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...