I have two clustered Microsoft SQL Servers (SQLA & SQLB) installed, and confirmed that both of the servers have an ODBC connector for a local PostgreSQL server. From that ODBC connection, I have a linked server created for use in some stored procedures that fails at least once a fortnight with this error message: > Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "POSTGRESP23" When troubleshooting the issues, the ODBC connector on both SQLA and SQLB tests successfully from the System DSN menu on the server; the error originates from the linked server. Currently, to fix this for convenience and lower downtime I am just deleting the linked server and remaking it, pointing it to the same ODBC object. However, this is not a sustainable process. Can anyone suggest where to look when troubleshooting? As I'm at a loss. **Additional Information** psqlODBC_X64 is installed on both machines already from https://odbc.postgresql.org/ System DSN settings: Linked Server settings: EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESP23', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'PSQLPROD' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESP23',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres',@rmtpassword='########' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'remote proc transaction promotion', @optvalue=N'true'

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is **production** server. Another is **reserve** server. Currently there is a transactional replication between two databases on these servers where **production** server is Publisher and Distributor and **reserve** server is Subscriber. On **production** server every night runs some tasks which inserts to (or updates) about of 5 tables one of them is about 6 Gb. We would like to move this tasks to **reserve** server and create on one another database with the same scheme. Then when tasks upload data to another database we need to synchronize one with **production** database on **production** server. **One moment :** For supporting actual data we must to include another database on **reserve** in replication as Subscriber from **production** server. As one way we consider linked server: 1. Truncate table on production (in one table there is FKs, so we can't) 2. Insert data from another database **Edit:** How about linked server and merge functionality? How do you think are there the better way?

Having issues selected data for some Oracle tables over the linked server from SQL Server: select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc; >Msg 7356, Level 16, State 1, Line 13 The OLE DB provider "OraOLEDB.Oracle" for linked server "RMTDB" supplied inconsistent metadata for a column. The column "CREATE_TIME" (compile-time ordinal 3) of object ""JOB1308"."HOST_INTERFACE"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time. Open query version of it works: select * from OPENQUERY( RMTDB, 'select type, count(*) from HOST_INTERFACE group by type order by 1 desc') result TYPE COUNT(*) 10 10450 8 6543 6 21 Anyone run into this?

I am trying to connect a PostgreSQL to SQL Server server as a linked server. On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN work correctly. Using SSMS I created a linked server that created successfully. When I test the connection it says successful and when I expand I can see all the tables from postgres. However when I try selecting from one of those tables it goves me the error: The OLE DB provider "MSDASQL" for linked server "ServerName" reported an error. The provider did not give any information about the error. I googled around but did not find anything that was helpful for me. Can someone please help me with this. Thank you

Linked Server SS 2012(SP4) to SS 2016(SP1) just stopped working. It times out with the error "Server is not found or not accessible". However I can connect and query the target server successfully with SSMS. How can SSMS work yet Linked Server cannot even "see" the target server?

I have a linked server setup on my source production server. I also have a secondary server which is the linked server. What I was looking to do is to execute a stroed proc and log connections to the linked server. However I seem to be having issues. > The object name 'MetricDB.Baseline.dbo.WhoIsActive' contains more than > the maximum number of prefixes. The maximum is 2. Metric DB is the destination server which is configured on the source server as a linked server. I been using Adam's sp which was described by Brent here: https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/ Due to space issues on my production DB I wanted to log it all to another server. Has anyone done this previously? or know if I'm missing something? Thanks

FILEPROPERTY (Transact-SQL) is not working with a linked server pointing from a on-Premise SQL 2017 to a Azure SQL Database. I see the following comment in books online: > Returns NULL for files that are not in the current database. But my linked server has @catalog pointing to a specific database. Here is my code and I get NULL for spaceUsedMB , freeSpaceMB, and spaceUsedMB columns. SELECT collectedAt = GetDate(), serverName =(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT @@SERVERNAME')), databaseName=(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT DB_NAME()')), fileName = LEFT(a.NAME, 64) , a.FILE_ID AS fileId, fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)), freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)), percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))), a.physical_name FROM [AzureDB_adventureworks].[adventureworks].sys.database_files a I was able to get by with following code but still curious to know why above code did not work. SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT GETDATE() AS collectedAT, @@SERVERNAME AS serverName, DB_NAME() AS databaseName, LEFT(a.name, 64) AS fileName, a.file_id AS fileId, a.size AS fileSizeMB, CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,''SpaceUsed'')/ 128.000, 2)) AS spaceUsedMB, CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,''SpaceUsed''))/ 128.000, 2)) AS freeSpaceMB, CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,''SpaceUsed''))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) as percentFree, a.physical_name AS physicalName from adventureworks.sys.database_files a' ) If I run the following code against Azure SQL Database I will get correct result for all columns. SELECT collectedAt = GetDate(), serverName = @@SERVERNAME, databaseName= DB_NAME(), fileName = LEFT(a.NAME, 64) , a.FILE_ID AS fileId, fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)), freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)), percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))), a.physical_name FROM sys.database_files a

A Tale As Old As Time... - I have a Linked Server setup to a 3rd party database which I have no ownership or access to otherwise. Lets call it ServerB. I have a stored procedure (SomeStoredProcedure) that selects from Linked Server ServerB. If I explicitly set the isolation level to SERIALIZABLE and then try to insert the results of SomeStoredProcedure into a local temp table, I get the following error: > OLE DB provider "MSOLEDBSQL" for linked server "ServerB" returned message "The parameter is incorrect.". > > Msg 7399, Level 16, State 1, Line 1 > > The OLE DB provider "MSOLEDBSQL" for linked server "ServerB" reported an error. One or more arguments were reported invalid by the provider. > > Msg 7391, Level 16, State 2, Line 1 > > The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "ServerB" was unable to begin a distributed transaction. If I just execute the procedure directly (without inserting the results into a local temp table) it works. If I don't use the SERIALIZABLE isolation level, it also works. (Other explicit isolation levels work as well.) I have tried disabling Enable Promotion of Distributed Transactions for RPC as mentioned in other answers: But no dice, same error: I understand that the query wants to promote to a distributed transaction for the above scenario since a Linked Server is involved (I assume enforcing SERIALIZABLE isolation is more involved across a remote server). But is it possible to prevent it from promoting to a distributed transaction under these circumstances? The same issue is reproducible using sp_executesql to select from the Linked Server as well. Repro code for example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DROP TABLE IF EXISTS #LocalTempTable;
CREATE TABLE #LocalTempTable (ID INT);

INSERT INTO #LocalTempTable (ID)
EXEC sp_executesql N'SELECT ID FROM ServerB.DatabaseName.SchemaName.SomeTable;';

*Reminder: I don't own this 3rd party server, and can't change any settings on it such as actually enabling the MSDTC.

I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records from an access database. Over the years, I have gotten it to work on Windows 2008 server, 2012 server, and Windows 10 (my development machine), though not without losing some hair. Now I am trying to port the app to an AWS EC2 instance with 2022 Server Datacenter edition, and can't make it work. I can run a linked server query in a SQL Server Management Studio window with no problem, so it works to that extent -- the linked server exists and works in principle. But if the same query is attempted from the web app, it fails with: > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[name of server]" returned message "Unspecified error". The error message seems very uninformative -- just a long-winded way of saying the query didn't work for unknown reasons. It's possible to query the SQL server directly from the web app, and to query the Access database directly from the web app using the same provider as used by the linked server. Only linked server queries fail. Because it works from my admin login via Management Studio but not from an IIS process, the problem could be 1) permissions; or 2) something about configuration of IIS or its application pools or the ability of the query to work from within an IIS process. Regarding permissions, I already made sure IUSR has full control of the folder where the linked server Access database sits. Based on other research, I tried firing up Dcomcnfg and giving IUSR full permissions for MSDAINITIALIZE (even though this was not necessary on my 2012 Server or Windows 10 machines). It didn't help. Regarding configuration, "Allow in process" is checked for the linked server provider, same as for my earlier machines, where linked server queries are successful from the web app.

Background - Main server is Microsoft SQL Server 2019 and linked server is PostgreSQL 15. Main reason for the linking is the duplication of records. All other DML operations are good from SQL Server to Postgres. Enable Promotion of Distributed Transaction is **False**. (EXEC master.dbo.sp_serveroption @server=..., @optname=N'remote proc transaction promotion', @optvalue=N'false') But all operations in SQL Server **trigger** throw error: >OLE DB provider "MSDASQL" for linked server "POSTGRESQL" was unable to begin a distributed transaction ...although promotion is set to *false*. Why does SQL Server continue to promote this transaction despite the option?

We have a linked PostgreSQL server and the remote login and password are parsed using the ODBC settings on the Windows server. Under the Security tab in Linked Server Properties within SQL, which connection setting should be used please? - Not be made - Be made without using a security context - Be made using the login's current security context - Be made using this security context If the last one, why would you want to pass the login credentials twice?

I have a requirement to create a new database on SQL Server from an existing Peoplesoft database, which is on oracle. I already have the script with me, and I want to know if the data can be extracted from the Peoplesoft database to the newly created SQL Server database using linked servers? This is the script: Create Database LHR_MIGRATION go Use LHR_MIGRATION go Create Table dbo.Z_MIGRATION_TABLE ( AMOUNT_TYPE nvarchar(255) NULL ,BUSINESS_UNIT nvarchar(255) NULL ,LEDGER nvarchar(255) NULL ,ACCOUNT nvarchar(255) NULL ,DESCR nvarchar(255) NULL ,DEPTID nvarchar(255) NULL ,DESCR_1 nvarchar(255) NULL ,PRODUCT nvarchar(255) NULL ,JOURNAL_DATE nvarchar(255) NULL ,JOURNAL_ID nvarchar(255) NULL ,CURRENCY_CD nvarchar(255) NULL ,MONETARY_AMOUNT float ,FOREIGN_CURRENCY nvarchar(255) NULL ,AFFILIATE nvarchar(255) NULL ,PROJECT_ID nvarchar(255) NULL ,LINE_DESCR nvarchar(255) NULL ,DESCR254 nvarchar(255) NULL ,DOC_TYPE nvarchar(255) NULL ,DOC_SEQ_NBR nvarchar(255) NULL ,JRNL_LINE_SOURCE nvarchar(255) NULL ,JRNL_LN_REF nvarchar(255) NULL ,JOURNAL_LINE float NULL ,STATISTIC_AMOUNT float ,[PRIMARY] int IDENTITY(1,1) NOT NULL ) on [PRIMARY] go Use /* insert your source database here*/ go Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE (AMOUNT_TYPE ,BUSINESS_UNIT ,LEDGER ,ACCOUNT ,DESCR ,DEPTID ,DESCR_1 ,PRODUCT ,JOURNAL_DATE ,JOURNAL_ID ,CURRENCY_CD ,MONETARY_AMOUNT ,FOREIGN_CURRENCY ,AFFILIATE ,PROJECT_ID ,LINE_DESCR ,DESCR254 ,DOC_TYPE ,DOC_SEQ_NBR ,JRNL_LINE_SOURCE ,JRNL_LN_REF ,JOURNAL_LINE ,STATISTIC_AMOUNT ) -- this script will return lines with MONETARY_AMOUNT and insert them into Z_MIGRATION_TABLE in the new LHR_MIGRATION database. -- Statistical amounts will be extracted separately SELECT 'MONETARY' 'AMOUNT_TYPE', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT, C.DESCR, A.DEPTID, E.DESCR, A.PRODUCT, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, A.JOURNAL_ID, A.CURRENCY_CD, A.MONETARY_AMOUNT, A.FOREIGN_CURRENCY, A.AFFILIATE, A.PROJECT_ID, A.LINE_DESCR, B.DESCR254, A.DOC_TYPE, A.DOC_SEQ_NBR, A.JRNL_LINE_SOURCE, A.JRNL_LN_REF, A.JOURNAL_LINE, A.STATISTIC_AMOUNT FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B, PS_GL_ACCOUNT_TBL C, XLATTABLE_VW D, PS_DEPT_TBL E WHERE ( A.BUSINESS_UNIT = A1.BUSINESS_UNIT AND A1.OPRID = 'BURAGOD' AND ( A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.JOURNAL_ID = B.JOURNAL_ID AND A.JOURNAL_DATE = B.JOURNAL_DATE AND A.UNPOST_SEQ = B.UNPOST_SEQ AND C.ACCOUNT = A.ACCOUNT AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.ACCOUNT = C_ED.ACCOUNT AND C_ED.EFFDT <= SYSDATE) AND C.SETID = 'GLOBE' AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= SYSDATE) AND D.FIELDNAME = 'JRNL_HDR_STATUS' AND D.FIELDVALUE = B.JRNL_HDR_STATUS AND E.DEPTID = A.DEPTID AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.DEPTID = E_ED.DEPTID AND E_ED.EFFDT <= SYSDATE) AND B.JRNL_HDR_STATUS IN ('P','U','T','N','V','D','E','Z') -- BUSINESS_UNIT to be merged with DEPTID to create the Hotel ID. Please add any missing ones the selection series below AND B.BUSINESS_UNIT+'-'+A.DEPTID in ('E0122-52588', 'E0123-52472', 'E0132-52473', 'E0132-52566', 'E0143-52474', 'E0143-54109', 'E0147-52475', 'E0147-54425', 'E0148-52476', 'E0149-52477', 'E0150-52471', 'E0150-52598', 'E0150-54105', 'E0150-54221', 'E0150-54380', 'E0150-54545', 'E0150-54547', 'E0150-54548', 'E0150-54750', 'E0150-54751', 'E0150-54752', 'E0150-54753', 'E0150-54759', 'E0150-54767', 'E0182-52468', 'E0182-52565', 'E0182-52599', 'E0182-52606', 'E0182-52607', 'E0182-52608', 'E0182-52609', 'E0182-52611', 'E0182-52612', 'E0182-52613', 'E0182-52634', 'E0182-53506', 'E0182-53512', 'E0182-53513', 'E0182-53514', 'E0182-53515', 'E0182-53520', 'E0182-53521', 'E0182-53522', 'E0182-53527', 'E0182-53528', 'E0182-53615', 'E0182-53616', 'E0182-53650', 'E0182-53691', 'E0182-53845', 'E0182-53847', 'E0182-53890', 'E0182-53936', 'E0182-54038', 'E0182-54054', 'E0182-54066', 'E0182-54072', 'E0182-54076', 'E0182-54080', 'E0182-54092', 'E0182-54096', 'E0182-54100', 'E0182-54104', 'E0182-54134', 'E0182-54140', 'E0182-54148', 'E0182-54166', 'E0182-54172', 'E0182-54242', 'E0182-54248', 'E0182-54286', 'E0182-54296', 'E0182-54297', 'E0182-54298', 'E0182-54301', 'E0182-54302', 'E0182-54316', 'E0182-54318', 'E0182-54322', 'E0182-54326', 'E0182-54350', 'E0182-54362', 'E0182-54364', 'E0182-54370', 'E0182-54376', 'E0182-54378', 'E0182-54381', 'E0182-54392', 'E0182-54422', 'E0182-54452', 'E0182-54470', 'E0182-54472', 'E0182-54494', 'E0182-54510', 'E0182-54540', 'E0182-54556', 'E0182-54590', 'E0182-54592', 'E0182-54610', 'E0182-54618', 'E0182-54644', 'E0182-54682', 'E0182-54724', 'E0182-54734', 'E0182-54741', 'E0182-54792', 'E0182-54824', 'E0182-54834', 'E0182-54870', 'E0182-54902', 'E0182-54946', 'E0183-52469', 'E0183-52600', 'E0183-54049', 'E0183-54133', 'E0183-54137', 'E0183-54283', 'E0183-54335', 'E0183-54379', 'E0183-54397', 'E0183-54529', 'E0183-54669', 'E0183-54727', 'E0183-54737', 'E0183-54784', 'E0184-52478', 'E0186-52479', 'E0186-53198', 'E0186-53199', 'E0190-52480', 'E0193-52555', 'E0221-52481', 'E0223-52482', 'E0229-52483', 'E0230-52484', 'E0230-52525', 'E0231-52485', 'E0231-52526', 'E0232-52486', 'E0232-52527', 'E0233-52487', 'E0233-52529', 'E0234-52488', 'E0234-52531', 'E0235-52489', 'E0235-52534', 'E0236-52490', 'E0236-52535', 'E0237-52491', 'E0237-52536', 'E0238-52492', 'E0238-52537', 'E0239-52493', 'E0239-52530', 'E0240-52494', 'E0240-52538', 'E0241-52495', 'E0241-52539', 'E0242-52496', 'E0242-52541', 'E0243-52497', 'E0243-52532', 'E0244-52498', 'E0244-52533', 'E0245-52499', 'E0245-52542', 'E0246-52500', 'E0246-52543', 'E0247-52503', 'E0247-52544', 'E0248-52504', 'E0248-52545', 'E0249-52505', 'E0249-52546', 'E0250-52507', 'E0250-52547', 'E0251-52508', 'E0251-52548', 'E0252-52509', 'E0252-52549', 'E0253-52510', 'E0253-52550', 'E0254-52511', 'E0254-52551', 'E0255-52512', 'E0255-52552', 'E0257-52513', 'E0257-52524', 'E0258-52514', 'E0258-52553', 'E0259-52515', 'E0259-52554', 'E0294-52516', 'E0360-52517', 'E0366-52518', 'E0366-54359', 'E0367-52519', 'E0367-54361', 'EMEAT-53643', 'EMEAT-53673', 'EMEAT-53724', 'EMEAT-58339') -- AND A.DEPTID='52469' --filter removed because it's merged with BUSINESS_UNIT to give the full Hotel ID in the filter directly above AND A.LEDGER = 'ACTUAL' AND B.JOURNAL_DATE < TO_DATE('2016-09-30','YYYY-MM-DD') ) ) UNION SELECT 'MONETARY' 'AMOUNT_TYPE', F.BUSINESS_UNIT, F.LEDGER, F.ACCOUNT, H.DESCR, F.DEPTID, I.DESCR, F.PRODUCT, TO_CHAR(F.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, F.JOURNAL_ID, F.CURRENCY_CD, F.MONETARY_AMOUNT, F.FOREIGN_CURRENCY, F.AFFILIATE, F.PROJECT_ID, F.LINE_DESCR, G.DESCR254, F.DOC_TYPE, F.DOC_SEQ_NBR, F.JRNL_LINE_SOURCE, F.JRNL_LN_REF, F.JOURNAL_LINE, F.STATISTIC_AMOUNT FROM PS_HH_JRNL_LN_ARC F, PS_HH_JRNL_HDR_ARC G, PS_GL_ACCOUNT_TBL H, PS_DEPT_TBL I WHERE ( F.BUSINESS_UNIT = G.BUSINESS_UNIT AND F.JOURNAL_ID = G.JOURNAL_ID AND F.JOURNAL_DATE = G.JOURNAL_DATE AND F.UNPOST_SEQ = G.UNPOST_SEQ AND H.ACCOUNT = F.ACCOUNT AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL H_ED WHERE H.SETID = H_ED.SETID AND H.ACCOUNT = H_ED.ACCOUNT AND H_ED.EFFDT <= SYSDATE) AND I.DEPTID = F.DEPTID AND I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_DEPT_TBL I_ED WHERE I.SETID = I_ED.SETID AND I.DEPTID = I_ED.DEPTID AND I_ED.EFFDT <= SYSDATE) AND H.SETID = 'GLOBE' AND G.JRNL_HDR_STATUS IN ('P','U') AND F.LEDGER = 'ACTUAL' -- BUSINESS_UNIT to be merged with DEPTID to create the Hotel ID. Please add any missing ones the selection series below AND G.BUSINESS_UNIT+'-'+F.DEPTID in ('E0122-52588', 'E0123-52472', 'E0132-52473', 'E0132-52566', 'E0143-52474', 'E0143-54109', 'E0147-52475', 'E0147-54425', 'E0148-52476', 'E0149-52477', 'E0150-52471', 'E0150-52598', 'E0150-54105', 'E0150-54221', 'E0150-54380', 'E0150-54545', 'E0150-54547', 'E0150-54548', 'E0150-54750', 'E0150-54751', 'E0150-54752', 'E0150-54753', 'E0150-54759', 'E0150-54767', 'E0182-52468', 'E0182-52565', 'E0182-52599', 'E0182-52606', 'E0182-52607', 'E0182-52608', 'E0182-52609', 'E0182-52611', 'E0182-52612', 'E0182-52613', 'E0182-52634', 'E0182-53506', 'E0182-53512', 'E0182-53513', 'E0182-53514', 'E0182-53515', 'E0182-53520', 'E0182-53521', 'E0182-53522', 'E0182-53527', 'E0182-53528', 'E0182-53615', 'E0182-53616', 'E0182-53650', 'E0182-53691', 'E0182-53845', 'E0182-53847', 'E0182-53890', 'E0182-53936', 'E0182-54038', 'E0182-54054', 'E0182-54066', 'E0182-54072', 'E0182-54076', 'E0182-54080', 'E0182-54092', 'E0182-54096', 'E0182-54100', 'E0182-54104', 'E0182-54134', 'E0182-54140', 'E0182-54148', 'E0182-54166', 'E0182-54172', 'E0182-54242', 'E0182-54248', 'E0182-54286', 'E0182-54296', 'E0182-54297', 'E0182-54298', 'E0182-54301', 'E0182-54302', 'E0182-54316', 'E0182-54318', 'E0182-54322', 'E0182-54326', 'E0182-54350', 'E0182-54362', 'E0182-54364', 'E0182-54370', 'E0182-54376', 'E0182-54378', 'E0182-54381', 'E0182-54392', 'E0182-54422', 'E0182-54452', 'E0182-54470', 'E0182-54472', 'E0182-54494', 'E0182-54510', 'E0182-54540', 'E0182-54556', 'E0182-54590', 'E0182-54592', 'E0182-54610', 'E0182-54618', 'E0182-54644', 'E0182-54682', 'E0182-54724', 'E0182-54734', 'E0182-54741', 'E0182-54792', 'E0182-54824', 'E0182-54834', 'E0182-54870', 'E0182-54902', 'E0182-54946', 'E0183-52469', 'E0183-52600', 'E0183-54049', 'E0183-54133', 'E0183-54137', 'E0183-54283', 'E0183-54335', 'E0183-54379', 'E0183-54397', 'E0183-54529', 'E0183-54669', 'E0183-54727', 'E0183-54737', 'E0183-54784', 'E0184-52478', 'E0186-52479', 'E0186-53198', 'E0186-53199', 'E0190-52480', 'E0193-52555', 'E0221-52481', 'E0223-52482', 'E0229-52483', 'E0230-52484', 'E0230-52525', 'E0231-52485', 'E0231-52526', 'E0232-52486', 'E0232-52527', 'E0233-52487', 'E0233-52529', 'E0234-52488', 'E0234-52531', 'E0235-52489', 'E0235-52534', 'E0236-52490', 'E0236-52535', 'E0237-52491', 'E0237-52536', 'E0238-52492', 'E0238-52537', 'E0239-52493', 'E0239-52530', 'E0240-52494', 'E0240-52538', 'E0241-52495', 'E0241-52539', 'E0242-52496', 'E0242-52541', 'E0243-52497', 'E0243-52532', 'E0244-52498', 'E0244-52533', 'E0245-52499', 'E0245-52542', 'E0246-52500', 'E0246-52543', 'E0247-52503', 'E0247-52544', 'E0248-52504', 'E0248-52545', 'E0249-52505', 'E0249-52546', 'E0250-52507', 'E0250-52547', 'E0251-52508', 'E0251-52548', 'E0252-52509', 'E0252-52549', 'E0253-52510', 'E0253-52550', 'E0254-52511', 'E0254-52551', 'E0255-52512', 'E0255-52552', 'E0257-52513', 'E0257-52524', 'E0258-52514', 'E0258-52553', 'E0259-52515', 'E0259-52554', 'E0294-52516', 'E0360-52517', 'E0366-52518', 'E0366-54359', 'E0367-52519', 'E0367-54361', 'EMEAT-53643', 'EMEAT-53673', 'EMEAT-53724', 'EMEAT-58339') -- AND F.DEPTID='52469' --filter removed because it's merged with BUSINESS_UNIT to give the full Hotel ID in the filter directly above AND G.JOURNAL_DATE < TO_DATE('2016-09-30','YYYY-MM-DD') ) go Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE (AMOUNT_TYPE ,BUSINESS_UNIT ,LEDGER ,ACCOUNT ,DESCR ,DEPTID ,DESCR_1 ,PRODUCT ,JOURNAL_DATE ,JOURNAL_ID ,CURRENCY_CD ,MONETARY_AMOUNT ,FOREIGN_CURRENCY ,AFFILIATE ,PROJECT_ID ,LINE_DESCR ,DESCR254 ,DOC_TYPE ,DOC_SEQ_NBR ,JRNL_LINE_SOURCE ,JRNL_LN_REF ,JOURNAL_LINE ,STATISTIC_AMOUNT ) -- this script will return lines with STATISTICAL_AMOUNT and insert them into Z_MIGRATION_TABLE in the new LHR_MIGRATION database. SELECT 'STATISTIC' 'AMOUNT_TYPE', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT, C.DESCR, A.DEPTID, E.DESCR, A.PRODUCT, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, A.JOURNAL_ID, A.CURRENCY_CD, A.MONETARY_AMOUNT, A.FOREIGN_CURRENCY, A.AFFILIATE, A.PROJECT_ID, A.LINE_DESCR, B.DESCR254, A.DOC_TYPE, A.DOC_SEQ_NBR, A.JRNL_LINE_SOURCE, A.JRNL_LN_REF, A.JOURNAL_LINE, A.STATISTIC_AMOUNT FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B, PS_GL_ACCOUNT_TBL C, XLATTABLE_VW D, PS_DEPT_TBL E WHERE ( A.BUSINESS_UNIT = A1.BUSINESS_UNIT AND A1.OPRID = 'BURAGOD' AND ( A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.JOURNAL_ID = B.JOURNAL_ID AND A.JOURNAL_DATE = B.JOURNAL_DATE AND A.UNPOST_SEQ = B.UNPOST_SEQ AND C.ACCOUNT = A.ACCOUNT AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.ACCOUNT = C_ED.ACCOUNT AND C_ED.EFFDT <= SYSDATE) AND C.SETID = 'GLOBE' AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= SYSDATE) AND D.FIELDNAME = 'JRNL_HDR_STATUS' AND D.FIELDVALUE = B.JRNL_HDR_STATUS AND E.DEPTID = A.DEPTID AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.DEPTID = E_ED.DEPTID AND E_ED.EFFDT <= SYSDATE) AND B.JRNL_HDR_STATUS IN ('P','U','T','N','V','D','E','Z') --AND B.BUSINESS_UNIT = 'E0132' --AND A.DEPTID='52566' AND B.BUSINESS_UNIT+'-'+A.DEPTID in ('E0122-52588', 'E0123-52472', 'E0132-52473', 'E0132-52566', 'E0143-52474', 'E0143-54109', 'E0147-52475', 'E0147-54425', 'E0148-52476', 'E0149-52477', 'E0150-52471', 'E0150-52598', 'E0150-54105', 'E0150-54221', 'E0150-54380', 'E0150-54545', 'E0150-54547', 'E0150-54548', 'E0150-54750', 'E0150-54751', 'E0150-54752', 'E0150-54753', 'E0150-54759', 'E0150-54767', 'E0182-52468', 'E0182-52565', 'E0182-52599', 'E0182-52606', 'E0182-52607', 'E0182-52608', 'E0182-52609', 'E0182-52611', 'E0182-52612', 'E0182-52613', 'E0182-52634', 'E0182-53506', 'E0182-53512', 'E0182-53513', 'E0182-53514', 'E0182-53515', 'E0182-53520', 'E0182-53521', 'E0182-53522', 'E0182-53527', 'E0182-53528', 'E0182-53615', 'E0182-53616', 'E0182-53650', 'E0182-53691', 'E0182-53845', 'E0182-53847', 'E0182-53890', 'E0182-53936', 'E0182-54038', 'E0182-54054', 'E0182-54066', 'E0182-54072', 'E0182-54076', 'E0182-54080', 'E0182-54092', 'E0182-54096', 'E0182-54100', 'E0182-54104', 'E0182-54134', 'E0182-54140', 'E0182-54148', 'E0182-54166', 'E0182-54172', 'E0182-54242', 'E0182-54248', 'E0182-54286', 'E0182-54296', 'E0182-54297', 'E0182-54298', 'E0182-54301', 'E0182-54302', 'E0182-54316', 'E0182-54318', 'E0182-54322', 'E0182-54326', 'E0182-54350', 'E0182-54362', 'E0182-54364', 'E0182-54370', 'E0182-54376', 'E0182-54378', 'E0182-54381', 'E0182-54392', 'E0182-54422', 'E0182-54452', 'E0182-54470', 'E0182-54472', 'E0182-54494', 'E0182-54510', 'E0182-54540', 'E0182-54556', 'E0182-54590', 'E0182-54592', 'E0182-54610', 'E0182-54618', 'E0182-54644', 'E0182-54682', 'E0182-54724', 'E0182-54734', 'E0182-54741', 'E0182-54792', 'E0182-54824', 'E0182-54834', 'E0182-54870', 'E0182-54902', 'E0182-54946', 'E0183-52469', 'E0183-52600', 'E0183-54049', 'E0183-54133', 'E0183-54137', 'E0183-54283', 'E0183-54335', 'E0183-54379', 'E0183-54397', 'E0183-54529', 'E0183-54669', 'E0183-54727', 'E0183-54737', 'E0183-54784', 'E0184-52478', 'E0186-52479', 'E0186-53198', 'E0186-53199', 'E0190-52480', 'E0193-52555', 'E0221-52481', 'E0223-52482', 'E0229-52483', 'E0230-52484', 'E0230-52525', 'E0231-52485', 'E0231-52526', 'E0232-52486', 'E0232-52527', 'E0233-52487', 'E0233-52529', 'E0234-52488', 'E0234-52531', 'E0235-52489', 'E0235-52534', 'E0236-52490', 'E0236-52535', 'E0237-52491', 'E0237-52536', 'E0238-52492', 'E0238-52537', 'E0239-52493', 'E0239-52530', 'E0240-52494', 'E0240-52538', 'E0241-52495', 'E0241-52539', 'E0242-52496', 'E0242-52541', 'E0243-52497', 'E0243-52532', 'E0244-52498', 'E0244-52533', 'E0245-52499', 'E0245-52542', 'E0246-52500', 'E0246-52543', 'E0247-52503', 'E0247-52544', 'E0248-52504', 'E0248-52545', 'E0249-52505', 'E0249-52546', 'E0250-52507', 'E0250-52547', 'E0251-52508', 'E0251-52548', 'E0252-52509', 'E0252-52549', 'E0253-52510', 'E0253-52550', 'E0254-52511', 'E0254-52551', 'E0255-52512', 'E0255-52552', 'E0257-52513', 'E0257-52524', 'E0258-52514', 'E0258-52553', 'E0259-52515', 'E0259-52554', 'E0294-52516', 'E0360-52517', 'E0366-52518', 'E0366-54359', 'E0367-52519', 'E0367-54361', 'EMEAT-53643', 'EMEAT-53673', 'EMEAT-53724', 'EMEAT-58339') AND A.LEDGER = 'ACTUAL' --AND B.JOURNAL_DATE BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-01-31','YYYY-MM-DD') AND A.ACCOUNT BETWEEN '900000' AND '999999' )) UNION SELECT 'STATISTIC' 'AMOUNT_TYPE', F.BUSINESS_UNIT, F.LEDGER, F.ACCOUNT, H.DESCR, F.DEPTID, I.DESCR, F.PRODUCT, TO_CHAR(F.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, F.JOURNAL_ID, F.CURRENCY_CD, F.MONETARY_AMOUNT, F.FOREIGN_CURRENCY, F.AFFILIATE, F.PROJECT_ID, F.LINE_DESCR, G.DESCR254, F.DOC_TYPE, F.DOC_SEQ_NBR, F.JRNL_LINE_SOURCE, F.JRNL_LN_REF, F.JOURNAL_LINE, F.STATISTIC_AMOUNT FROM PS_HH_JRNL_LN_ARC F, PS_HH_JRNL_HDR_ARC G, PS_GL_ACCOUNT_TBL H, PS_DEPT_TBL I WHERE ( F.BUSINESS_UNIT = G.BUSINESS_UNIT AND F.JOURNAL_ID = G.JOURNAL_ID AND F.JOURNAL_DATE = G.JOURNAL_DATE AND F.UNPOST_SEQ = G.UNPOST_SEQ AND H.ACCOUNT = F.ACCOUNT AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL H_ED WHERE H.SETID = H_ED.SETID AND H.ACCOUNT = H_ED.ACCOUNT AND H_ED.EFFDT <= SYSDATE) AND I.DEPTID = F.DEPTID AND I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_DEPT_TBL I_ED WHERE I.SETID = I_ED.SETID AND I.DEPTID = I_ED.DEPTID AND I_ED.EFFDT <= SYSDATE) AND H.SETID = 'GLOBE' AND G.JRNL_HDR_STATUS IN ('P','U') AND F.LEDGER = 'ACTUAL' --AND G.BUSINESS_UNIT = 'E0132' --AND F.DEPTID='52566' --AND G.JOURNAL_DATE BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-01-31','YYYY-MM-DD') AND B.BUSINESS_UNIT+'-'+A.DEPTID in ('E0122-52588', 'E0123-52472', 'E0132-52473', 'E0132-52566', 'E0143-52474', 'E0143-54109', 'E0147-52475', 'E0147-54425', 'E0148-52476', 'E0149-52477', 'E0150-52471', 'E0150-52598', 'E0150-54105', 'E0150-54221', 'E0150-54380', 'E0150-54545', 'E0150-54547', 'E0150-54548', 'E0150-54750', 'E0150-54751', 'E0150-54752', 'E0150-54753', 'E0150-54759', 'E0150-54767', 'E0182-52468', 'E0182-52565', 'E0182-52599', 'E0182-52606', 'E0182-52607', 'E0182-52608', 'E0182-52609', 'E0182-52611', 'E0182-52612', 'E0182-52613', 'E0182-52634', 'E0182-53506', 'E0182-53512', 'E0182-53513', 'E0182-53514', 'E0182-53515', 'E0182-53520', 'E0182-53521', 'E0182-53522', 'E0182-53527', 'E0182-53528', 'E0182-53615', 'E0182-53616', 'E0182-53650', 'E0182-53691', 'E0182-53845', 'E0182-53847', 'E0182-53890', 'E0182-53936', 'E0182-54038', 'E0182-54054', 'E0182-54066', 'E0182-54072', 'E0182-54076', 'E0182-54080', 'E0182-54092', 'E0182-54096', 'E0182-54100', 'E0182-54104', 'E0182-54134', 'E0182-54140', 'E0182-54148', 'E0182-54166', 'E0182-54172', 'E0182-54242', 'E0182-54248', 'E0182-54286', 'E0182-54296', 'E0182-54297', 'E0182-54298', 'E0182-54301', 'E0182-54302', 'E0182-54316', 'E0182-54318', 'E0182-54322', 'E0182-54326', 'E0182-54350', 'E0182-54362', 'E0182-54364', 'E0182-54370', 'E0182-54376', 'E0182-54378', 'E0182-54381', 'E0182-54392', 'E0182-54422', 'E0182-54452', 'E0182-54470', 'E0182-54472', 'E0182-54494', 'E0182-54510', 'E0182-54540', 'E0182-54556', 'E0182-54590', 'E0182-54592', 'E0182-54610', 'E0182-54618', 'E0182-54644', 'E0182-54682', 'E0182-54724', 'E0182-54734', 'E0182-54741', 'E0182-54792', 'E0182-54824', 'E0182-54834', 'E0182-54870', 'E0182-54902', 'E0182-54946', 'E0183-52469', 'E0183-52600', 'E0183-54049', 'E0183-54133', 'E0183-54137', 'E0183-54283', 'E0183-54335', 'E0183-54379', 'E0183-54397', 'E0183-54529', 'E0183-54669', 'E0183-54727', 'E0183-54737', 'E0183-54784', 'E0184-52478', 'E0186-52479', 'E0186-53198', 'E0186-53199', 'E0190-52480', 'E0193-52555', 'E0221-52481', 'E0223-52482', 'E0229-52483', 'E0230-52484', 'E0230-52525', 'E0231-52485', 'E0231-52526', 'E0232-52486', 'E0232-52527', 'E0233-52487', 'E0233-52529', 'E0234-52488', 'E0234-52531', 'E0235-52489', 'E0235-52534', 'E0236-52490', 'E0236-52535', 'E0237-52491', 'E0237-52536', 'E0238-52492', 'E0238-52537', 'E0239-52493', 'E0239-52530', 'E0240-52494', 'E0240-52538', 'E0241-52495', 'E0241-52539', 'E0242-52496', 'E0242-52541', 'E0243-52497', 'E0243-52532', 'E0244-52498', 'E0244-52533', 'E0245-52499', 'E0245-52542', 'E0246-52500', 'E0246-52543', 'E0247-52503', 'E0247-52544', 'E0248-52504', 'E0248-52545', 'E0249-52505', 'E0249-52546', 'E0250-52507', 'E0250-52547', 'E0251-52508', 'E0251-52548', 'E0252-52509', 'E0252-52549', 'E0253-52510', 'E0253-52550', 'E0254-52511', 'E0254-52551', 'E0255-52512', 'E0255-52552', 'E0257-52513', 'E0257-52524', 'E0258-52514', 'E0258-52553', 'E0259-52515', 'E0259-52554', 'E0294-52516', 'E0360-52517', 'E0366-52518', 'E0366-54359', 'E0367-52519', 'E0367-54361', 'EMEAT-53643', 'EMEAT-53673', 'EMEAT-53724', 'EMEAT-58339') AND F.ACCOUNT BETWEEN '900000' AND '999999') go

I tried to set up a linked server via ODBC connection in SQLServer. It seem to connect but showed all the caches as empty names in SSMS. Also I couldnt get a select query to work. Has anybody had any luck with doing this? If so.. How did you write the select statement?

I'm using SQL2022 I have a linked server to another SQL server, security is set with a defined username/password. My issue is that when logged into the server with a standard account I cannot see a list of the tables/views on my linked server within the object explorer. I can query the linked server just fine, but cannot expand the tables tree to see what tables are available. If I log into the server with my SA account, I can expand the tree and see all the tables and views on the linked server. What permission do I need to set on my standard login to be able to view the tables in the object explorer.

DECLARE @LINKEDSERVER VARCHAR(20)= 'LS_CMT'; DECLARE @ODS_DBNAME VARCHAR(20)= ''; SET @ODS_DBNAME = DB_NAME(); DECLARE @OCS_DBNAME VARCHAR(20)= ''; SET @OCS_DBNAME = 'CMT' + SUBSTRING(@ODS_DBNAME, CHARINDEX('_', @ODS_DBNAME), LEN(@ODS_DBNAME)) SELECT * FROM @ODS_DBNAME.[dbo].CLI_LOCATIONS

I have the task of migrating our databases from a Windows 2012 R2 server with SQL Server 11.0.7507 (2012) to a Windows 2019 server with SQL Server 16.0.1110.1 (2022). I am having issues with opening excel spreadsheets. The following works on the old server but not on the new server. select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Reports\ETAReport.xlsx;', 'SELECT * FROM [DataSheet$]'); I get the error > OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 8 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". I have tried everything here https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null and other similar threads. This is what I have done on the new server: - Installed the 64 bit of Microsoft Access Database Engine 2010 Redistributable (version 14.0.7015.1000 matches the current server). - EXEC sp_configure 'show advanced options', 1 - EXEC sp_configure 'ad hoc distributed queries', 1 - EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 - EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 - Given 'Everyone' rights to the folder, checked the file name. - Restarted the server multiple times. Many say Error 7303 is file open but it definitely is not open (still happens immediately after restarted server) and office is not installed on the server. The one thing I have noticed is that on the old server the SQL Instance user (NT Service\MSSQLSERVER) has a user folder in C:\Users\ and when I run the above query on the old server it writes to C:\Users\MSSQLSERVER\AppData\Local\Temp. The new server has NO user folder for the SQL user. Is SQL 2022 different or is that the missing bit? If so how do I create that folder? Thanks!

I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them. Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause. Is there any way we can speed this up or use a different approach? Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT 
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
   	 	 WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
		 WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
		 WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
		 WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
		 ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
		END AS col51 
  from source_server.table')

After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it? SELECT ISNULL(A.Col1,B.Col1) Col1 ,ISNULL(A.Col2,B.Col2) Col2 ,ISNULL(A.Col3,B.Col3) Col3 ...etc ,ISNULL(A.Col50,B.Col50) Col50 ,ISNULL(A.Col51,B.Col51) Col51 FROM Table1 A -- linked server table FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows OR (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes OR (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 A.Col50) --updates

I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against. I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this: DECLARE @database NVARCHAR(16) = 'ClientA'; DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database; EXECUTE sp_executesql @statement; // Run generic statements This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts. Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB? ----- EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.

as a follow up from this question below: Run a PowerShell script on a different server - from inside a SQL Server job there you can see that for me tu run a powershell script from inside a sql server job, I need to create a proxy. Both the powershell script and the proxy script are on that link. what the powershell script does is to delete old backup files on a remote server. the question that I still have is: for the whole thing to work on windows server 2012, I could find not other way than granting Administrator on the remote box to the Proxy account. as you can see on the picture below. another alternative was to create a powershell script that do both, backups and deletes, and create a sql server job with the following code: Is this a powershell limitation when running on a remote server? Are there other ways to work around it, without having to grant admin to the proxy account? PowerShell Basics: Managing Remote Servers & Sessions

I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) which is also SQL Server 2012. I can successfully run the stored procedure on it's own, but when the scheduled job runs, I get the following error: >Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "222.222.222.222". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.,00:00:00,16,7412,,,,0 I suspect this is a security issue, but cannot find the settings that will let me run this.