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 have an access database with my work that I am trying to insert into but I keep getting. > 'You cannot add or change a record because a related record is required in table 'Projects'.' I'm running this query: INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description) On this Structure: With this code in C# with an OleDb... commands and connections Which are working fine for other query's:

//the assign id is already known and is of type integer.
            string query = "SELECT Project_ID FROM Projects WHERE Project_Name = @project";
            OleDbConnection con = new OleDbConnection(con_string);
            OleDbCommand cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", projectComboBox.Text);
            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                project_id = Convert.ToInt16(reader);
                Console.WriteLine(project_id);
            }
                
            con.Close();

            Console.WriteLine("submit: " + project_id + " " + employee_id + " " + descriptionTextBox.Text + " " + monthCalendar1.SelectionStart.ToString("MM/DD/YYYY"));
            Console.WriteLine(monthCalendar1.SelectionStart);
            query = "INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)";
            con = new OleDbConnection(con_string);
            cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", project_id);
            cmd.Parameters.AddWithValue("@assign", employee_id);
            cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text.ToString());
            //cmd.Parameters.AddWithValue("@deadline", monthCalendar1.SelectionStart);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.Close();

I have tried looking at other examples of this problem and I don't understand why I'm getting this error. @project has a valid id number of the primary key for a Project, @assign has a valid employee id as well and @description is string of text. Thanks for any help.

I am trying to use Visual Studio 2022 to successfully load and build an SSIS package that targets SQL Server 2016. Documentation suggests that this is indeed supported. Per the online docs (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16) , I have used the visual studio installer to add in the SQL Server Data Tools. Then I have installed the SSIS extension. I am getting an error about OLEDB. More research suggests that I need the msoledbsql driver installed. However, the installer for that complains that I need a visual c++ redistributable. I downloaded that from here and installed it without issue: https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170 I have in fact installed that as shown in my add/remove programs list. However, the msoledbsql installer refuses to install. It still insists that I need to install the visual c++. Can anyone advise a way forward?

I'm using SQL Server 2016 (SP1) Standard Edition. I have a linked server that uses Windows Authentication and needs to because it connects to a non-SQL Server database. It uses an OLE database provider. I have a third-party application that needs to use this linked server, but its application connects using SQL Server Authentication. Is there a way to allow a SQL Server login to use this linked server?

When we run a SSIS package to import data from excel , we get an initial error:

The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode.

We were able to fix this by executing the package in 32 bit mode using the option in wizard

SELECT * 
INTO #temp13
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
                    'Data Source=XXXXXXXX;Extended Properties=Excel 8.0')...[Sheet1$];

We get the below error

The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" 
cannot be loaded in-process on a 64-bit SQL Server. [SQLSTATE 42000] (Error 7438).
The step failed.

Is there any option to run the Transaction SQL in 32 bit .

I am unable to select the option for Other data source when creating a linked server in SSMS, because the option is completely grayed out. I have installed Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server but nothing shows up under Server Objects > Linked Servers > Providers. !(https://i.sstatic.net/BeDsrIzu.png) The only errors I see in errorlog are about SSISScaleOutMaster160 failing to open the SSISDB database, but we don't have scale out enabled. This is all on an Enterprise Evaluation copy of SQL Server 2022, but I still have 48 days left on my evaluation. I would appreciate any guidance in making the OLE DB drivers work so I can create a linked server using them. Thank you.

I have added a trigger to perform a change tracking mechanism on a table that has a relatively high load. During testing, we encountered no issues but now as it is in production there are deadlocks happening from time to time. There is an old MS Access frontend accessing the database via OLEDB / ADODB that is not fault tolerant to the deadlock which leads to data integrity issues (and we cannot address this in short time as it is not our project). This is the deadlock graph:  The SQL statements (updates) on the left and right side of the deadlock graph are not within the trigger but of course triggering it. The deadlock occurs on the PK of the change tracking table. This is the trigger:

sql
ALTER TRIGGER [dbo].[TR_IND_ChangeTracking_OrderingItems_PickOrders]
ON [dbo].[OrderingItems]
FOR UPDATE, INSERT, DELETE
AS 
	DECLARE @InsertedOrderNumber int
	DECLARE @DeletedOrderNumber int

	SELECT @InsertedOrderNumber = OrderNumber FROM inserted
	SELECT @DeletedOrderNumber = OrderNumber FROM deleted

	-- Check if the order exists in IND_PickOrders, otherwise skip
	DECLARE @ExistsInPickOrders bit
	SET @ExistsInPickOrders = 0

	SELECT @ExistsInPickOrders = 1
		FROM [dbo].[IND_PickOrders]
		WHERE Auftragsnummer = @InsertedOrderNumber OR Auftragsnummer = @DeletedOrderNumber

	IF NOT @ExistsInPickOrders = 1
	BEGIN
		RETURN
	END

	DECLARE @Timestamp datetime
	DECLARE @ToUpdate bit
	DECLARE @State int

	SET @Timestamp = GETUTCDATE()

	-- Need to update an existing entity in queue?
	SELECT @ToUpdate = 1 
		FROM [dbo].[ChangeTracking_PickOrders] 
		WHERE OrderNumber = @InsertedOrderNumber OR OrderNumber = @DeletedOrderNumber

	-- There was an action happening, but no value has been inserted or deleted.
	-- I.e. trying to delete an entity that not exists.
	IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NULL
	BEGIN
		SET @State = 0
	END

	-- Entity has been inserted.
	IF (@InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NULL)
	BEGIN
		SET @State = 1
	END

	-- Entity has been updated.
	IF @InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NOT NULL
	BEGIN
		SET @State = 2
	END

	-- Entity has been deleted.
	IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NOT NULL
	BEGIN
		SET @State = 3
	END

	IF @State > 0
	BEGIN
		IF @ToUpdate = 1
		BEGIN
			-- Update the entity on change tracking table.
			UPDATE [dbo].[ChangeTracking_PickOrders]
				SET UpdateTimestamp = @Timestamp, State = @State
				WHERE OrderNumber = ISNULL(@InsertedOrderNumber, @DeletedOrderNumber)
		END
		ELSE
		BEGIN
			-- Insert if no entry exists.
			INSERT INTO [dbo].[ChangeTracking_PickOrders] (OrderNumber, UpdateTimestamp, State) 
				VALUES (ISNULL(@InsertedOrderNumber, @DeletedOrderNumber), @Timestamp, @State)
		END
	END

The trigger first checks if the order number exists in another table and if not, returns. Then I check if the order number exists in the change tracking table and insert or update the specific row in the change tracking table. The logic of the Access frontend iterates over a set of ordering items of an order, calculates some fields and updates the order row by row by using an ADORecordSet. I suspect the Access frontend to not wait until the update and the trigger have completed (releasing the locks) and update the next row from a different connection of the ConnectionPool (thus leading to a differnt server process?). As me not being a DBA, is there anything I may have overseen? Thank you for any tips in advance! Edit: The schema of [dbo].[ChangeTracking_PickOrders]

CREATE TABLE [dbo].[ChangeTracking_PickOrders](
	[OrderNumber] [int] NOT NULL,
	[State] [int] NOT NULL,
	[UpdateTimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_OrderNumber] PRIMARY KEY CLUSTERED 
(
	[OrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Today Azure Defender notified me that some OLE DB drivers are outdated on our two Azure VM's. However, I wasn't aware these are needed. Our VM's perform the following duties: - VM 1: Windows Server 2019 hosting SQL Server 2019, hosting databases for VM 2, - VM 2: Windows 2019 with IIS, multiple ASP.Net Framework 4.8 web sites all accessing VM 1 databases using exactly the same connection string format: Server=tcp:10.0.2.4,1433; Persist Security Info=False; Initial Catalog=[SiteXYZ-Db]; User ID=...; Password=...; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=True; Connection Timeout=60 Doing some initial searching I saw that web sites connecting to SQL via OLE DB would have Provider=sqloledb; in the connection string, which we don't see (although I am nervous that's a massive generalisation - perhaps ASP.Net uses this internally?). Therefore, is there any way to determine whether a driver is being used by other Windows' processes? The driver is installed on both VM's, and was installed on the date that the VM was commissioned (although I have no recollection of doing this manually and so might have been installed as a by-product of something else?).

Actually I have silly question. If I install below update during working hours then is it occurring any interrupt ? Also I have checked release notes. But I couldn't find anything. Security Updates for Microsoft SQL Server OLE DB Driver (June 2023)

I am working on a few applications which connect to an MS Access database backend (.mdb) to read/insert/update records. Everything is working fine, but I noticed that my db operations were quite slow. The backend is accessed by other users, but I still get the issue when querying a copy of the access file which no one else connects to. I managed to narrow this down so that I can now see the offending code is the line connection.Close() Called on an open OleDBConnection which has just executed some query, e.g: var con = new OleDbConnection(connectionString); con.Open(); var query = "SELECT * FROM subGRCReceived WHERE GRVNo=@grv"; var args = new DynamicParameters(); args.Add("@grv", grvNumber); // Using Dapper var pallets = (List)con.Query(query, args); con.Close(); // This is often taking between 7-10 seconds I can confirm that this is occurring when using using/con.Close()/con.Dispose(), and using or not using Dapper makes no difference. I did notice that this only seems to happen with web based prjojects (ASP MVC or WCF soap service) and not Console applications. The issue seems to be intermittent, but occurs frequently enough for it to be a pain for the user (especially when navigating to a page uses 2-3 db queries, as this can take as long as 20 seconds to load). The problem does not lie with the code itself, as I am able to host the same application on my laptop on the same network as the server and the speed is perfect (~200ms per request). See the specs of the 2 machines below: ### Laptop Details: - Processor : Intel Core i7-6700HQ CPU @ 2.60GHz - RAM : 16.0 GB - OS : Windows 10 x64 ### Server Details - Processor : Intel Xeon CPU E5-2603 v4 @ 1.70GHz (2 processors) - RAM : 32.0 GB - OS : Windows Server 2012 x64 ### Setup - 32 bit MS Access 2016 - 2016 Microsoft ACE OLE Engine - 64 it OS # What I have tried - Moved the database to same folder as application - Disabled antivirus (ESET) - Increased the MaxBufferSize key value from 0 in Access Connectivity Engine in the registry (does this need a restart?) - Measure the time it takes to run GC.Collect() before calling Close() to ensure it is not the garbage collector # Workarounds ### Threading I tried calling Close() from a new thread, which seemed to work after 1 request, but if I try accessing the application again I am getting unhandled win32 errors on the server (Even though I wrapped my thread and connection.Close() calls in try/catch. I suspect this might be failing because the thread might take 7 seconds to close the connection, but the IIS worker process gets terminated before that, so there may be some missing resources that Close() needs. It might be nice if I could get this working, but I understand that this i bad practise in MVC, and also does not actually solve the issue. ### Persistent Connection I could also just have 1 OleDBConnection and keep it open throughout the session. I did this with the WCF service (1 connection per request) and it works find, however I get the feeling that it wont work quite as well with ASP MVC, and after doing a bit of research it looks like this is not a good idea. _______________ I have been struggling with this for a week now and its driving me crazy, does anyone have any advice at all?

I have two servers running SQL Server 2000 and SQL Server 2005.
There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database. I have a linked server pointing from SQL Server 2005 database A to SQL Server 2000 database B. Here the scenario is I can able to access database SQL Server 2000 database B from SQL Server 2005. Two issues I face I am randomly getting the error: > OLE DB provider "SQLNCLI" for linked server "SQL2000" returned > message "Communication link failure". Msg 10054, Level 16, State 1, > Line 0 TCP Provider: An existing connection was forcibly closed by the > remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for > user 'NT AUTHORITY\ANONYMOUS LOGON'. How to resolve this error? Also, when the user want to access the database located in the same server what must be done? I mean accessing the database SQL Server 2005 database A and SQL Server 2005 database A i.e ignoring the linked server.

We've just upgraded a Server from SQL Server 2016 to 2019. We have linked servers connecting to an OLAP cube. When I look under providers for MSOLAP the label of the dialog box shows: > Microsoft OLE DB Provider for Analysis Services 14.0 If I look at a linked server The drop down for provider also says: > Microsoft OLE DB Provider for Analysis Services 14.0 When I look in: C:\Program Files\Microsoft Analysis Services\AS OLEDB I have 2 folders: - 130 - 140 If I look under 130 at the properties for MSOLAP130.dll it says it is version 13.0.5622.0 If I look under 140 at the properties for MSOLAP.dll it says it is version 15.1.65.24 Which DLL Is my linked server using? 13.xx or 15.xx?

We have a desktop application written in Visual Basic .NET. One our clients needs to use ODBC instead of OLEDB to connect to the SQL Server 2008 R2 Database. The company has several databases including Oracle, IBM DB2, and PostgreSQL. We can migrate the connections, but we need to know what is the better: OLEDB or ODBC when connecting to SQL Server 2008 R2?

I've been trying to establish a OLEDB Connection to an Informix Dynamic Server. (Version IBM Informix Dynamic Server Version 12.10.FC12WE) **Background Info:** A program, which will be used by my company, requires OLEDB and doesn't support ODBC. The colleague,who is currently tasked with managing the implementation, has a lot of other work piled up and so the implementation will be delayed. In the meantime, I've been tasked to figure out how to properly configure the Windows 10 Client PC (which will run the program) for the connection. I have very little experience with databases, so I've searched for about 2 weeks for answers in various documentations and forums. So far, I had mixed success. **What has already been done:** - The coledbp.sql- script has been run against the sysmaster table on the database - Informix Client SDK and Microsoft Data Access Components have been installed on the Client PC. - Using setnet32 the registry entries for host:port have been set. - The driver ifxoledbc has been registered. - ODBC-Datasources (both 64-Bit and 32-Bit) have been created and tested (they work fine). Various Tests ------------- For all my tests I've been using the Connection String: > "Provider=ifxoledbc;Data Source=mydatabase@myserver;User ID=myuser;Password=mypassword; First I tried to test the connection with an .udl-File. I select the IBM Informix OLE DB Provider and enter the data source and userdata. When I test the connection I get an Error like: > "Initialisation of the provider failed. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." From what I could find, there are many possible reasons for this error, and I don't know if can get a more specific error message somehow. With the Microsoft OLE DB Provider for ODBC Drivers and the ODBC-datasource for the Server the test connection worked. In Microsoft Excel (32-Bit) I also tried both the first mentioned Connection String and the Connection String, which is built when entering the infos in the .udl settings. Same Error with both cases. For my next test I used a .vbs - script, which was presented in the IBM Informix Developers Handbook: On Error Resume Next set conn=createobject("ADODB.Connection") conn.connectionstring = "Provider=ifxoledbc;DataSource=mydatabase@myserver;UserID=myuser;Password=mypassword;" conn.open If Err Then WScript.Echo "Error!! Open"+conn.Errors(0).Description Else WScript.Echo "Connected" conn.close end if This results once again results in the "multi-step"-Error. The last thing I tried was using the Rowset Viewer of MDAC. I used the exact same input as with the .udl - File. But to my surprise it managed to connect! (It returned the error "Unknown Interface", but still worked) It correctly read the entries I set with the setnet32-Tool. (I checked this by temporarily removing one of the Entries, which resulted in an additional error and no connection) **Now I have a few questions:** - Is there anything additional to configure on the Client PC? - How does the Viewer succeed, where every other test fails? - Is there any way to get a more accurate error code? - Are there major flaws in my tackling of the problem? I appreciate every suggestion anybody can give me.

I have SQL Server 2008R2 Standard Edition. I am making a Linked Server connection to a Listener of an AlwaysOn Availability Group. However, when I run a query from SSMS I intermittently get this error: OLE DB provider "SQLNCLI10" for linked server "Listener,4600" returned message "Unable to complete login process due to delay in opening server connection". Msg 7303, Level 16, State 1, Line 7 Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "Listener,4600". I thought it may be a driver issue but OLE DB looks to be installed. Any idea what I may be missing?

To configure linked server pointing to a SQL server, there are 4 possible options. 1. Server Type: SQL Server 2. Server Type: Other data source; Microsoft OLE DB Provider for SQL Server 3. Server Type: Other data source; SQL Server Native Client 11.0 4. Server Type: Other data source; Microsoft OLE DB Driver for SQL Server In this question I want to specifically ask when is the recommended to use either of the above in terms of **a) use case**; and **b) deprecated/recommended not to be used**.

I am developing an Excel / VBA - Application that connects to an Oracle database (v 11.2.0.4) instance. I need to query a customers table and search by name. Customers names will contain german umlaut chars (äöüÄÖÜ and ß). I issue sql queries like "... where c.custname = 'Müller'". The behaviour I am observing is that on my dev system all works well as well as on several users' systems. On few other systems, the application fails with "no customers found", my own error message if the name lookup mentioned before fails (which should not happen as it works at another system). Further investigation using the PL/SQL Developer tool revealed that on the affected systems the names for customers are returned without umlaut. "Müller" becomes "Muller". However, querying for "Muller" doesn't return anything - so "Müller" is really converted to "Mu[unprintable char]ller" - I guess. I am quite familiar with Microsoft SQL Server, Versions, Languages, Drivers... but for Oracle I have no experience at all. Can someone advice where / how to check what driver version is installed on a system, how it is configured (NLS-settings?) and how to make sure all systems are set up the same, in this aspect? Is there a way to totally clean a system of all oracle related drivers, files and registry settings to get a clear start and have only one, the most recent driver, available? --- Further information (to make things worse): * My VBA-macro uses a base class which is in use "forever" in my company. It connects through OLEDB and builds it's connection string using "Provider=msdaora.1" meaning it is using the Microsoft provided Oracle driver intended for Oracle around version 7 (?). * When using PL/SQL Developer to test, it takes a "real" oracle driver installed. For my system, there is a choice from "10.2.0/client_1", a "12.2.0/client" and "12.2.0/client_2" I am going to try and compare my Microsoft Oracle driver to the non-working systems, just to see what it is. I will try to upgrade the base class to stop using the obsolete Microsoft driver and use the recent oracle driver. However, manual queries using PL/SQL Developer showed the same basic problem... therefore I am not sure I will solve the issues. I'd appreciate any idea where to look, what to remove or install or any other idea!

I have a trigger on my SQL Server, located in server7. It inserts a data on another server (server5), after insert, as you see the code below: CREATETRIGGER [dbo].[trgSPTInsereDepartamento] ON [dbo].[tblDepartamento] AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into [Server5].alfabase.dbo.tblDepartamento (CodDepto,DescDepto,IncluiOutroDepto) Select ins.codDepartamento, ins.DescDepartamento, 0 From inserted ins where not exists(select 1 from [Server5].alfabase.dbo.tblDepartamento sptsta where sptsta.CodDepto = ins.CodDepartamento) END When i make an insert, the SQL shows me this: > OLE DB provider "SQLNCLI10" for linked server "server5" > returned message "No transaction is active.". > Msg 7391, Level 16, > State 2, Procedure trgSPTInsereDepartamento, Line 7 The operation > could not be performed because OLE DB provider "SQLNCLI10" for linked > server "server5" was unable to begin a distributed > transaction. Local DTC properties is all checked ( it allows remote users ). I've restarded the server and the DTC service. No Luck for me. I read a lot of posts but none of them helped me. Thanks for the help. > **update1:** Checked firewall. disabled it but still doesn't work. DTC is ok ( only server5 is on a cluster. ) I'm really out of ideas guys. Thank you very much. > **Update 2**: From server7 to server5, I have the problem above. To test, I made a trigger on a database from server4 to server5. and it worked. So, the problem is on server7. Server7 is not on the Cluster (server3,4,5 are clustered).

I am trying to set up a linked server from SQL Server Production to one of our Oracle instances but experiencing this error : The test connection in ODBC succeeds, but it fails on SQL Server side. I have checked the following things: 1. OLEDB provider is installed 2. OLEDB bit matches the bit of SQL 3. OLEDB is registered 4. Linked server properties is the last option to be able to login in using username and password 5. Oracle provider has Allow In process checked 6. Oracle ports are opened 7. The right version client is installed Are there anything else I should check further to resolve this error? Please provide me suggestions if you have experienced this before and what has worked for you. TIA

I installed an Oracle OraOLEDB.Oracle provider. Now I want to remove that provider from the list. How can I do that?