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!

We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to MDS 2016 on user's desktop machine so that same user can connect to MDS 2019 with excel 2019 and MDS2016 with excel 2016 simultaneously?.The reason we want to run parallelly is we doing a migration for UAT environment to MDS2019 and we don't want to restrict their access to MDS 2016 till the time they are on prod. We are open for any solution like techniques (if any) from desktop virtualization perspective or anything from SQL...Appreciate all the responses -Kev

I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL. The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to gather information into separate comparitive tables and charts based on the data in the Master worksheet. The master worksheet is a pivot table containing 90 columns and over 3000 rows. In the past, User's have tried to access this file at different times to edit it as it is on a network drive. Obviously, this not an ideal solution and I would like to set up this spreadsheet on a database. From my brief research so far, I have seen that phpMyAdmin is a popular solution to export an excel worksheet, saved as a .csv file to a MySQL database. The solution would need to leave the data very accessible to a handful of users who have good excel skills but almost no programming knowledge. I am wondering if the best solution is to move the entire spreadsheet to a database - ie. Convert the Master worksheet to a .csv file and export it using phpMyAdmin (Is it easy to set-up pivot tables again once this data is exported?), and then on MySQL, create the tables and charts that are currently on our other worksheets so that users can view and add/take from them using phpMyAdmin, or is there a better solution that would save me having to create the other 11 worksheets on MySQL? Or could the MySQL add-in for Excel be used in some way to generate a local copy of the data and other worksheets on everyone's local drive? My knowledge of databases is elementary to say the least, so any help at all would be greatly appreciated.

When using the ODBC data source, or the Microsoft Query Wizard to connect to a PostgreSQL database, I can see all tables; even though I only have permission to see several. The wizard even shows me the names of the table columns. Is there any way to prevent the tables that I don't have permission to read from being shown?

I want to export the result of a query to xlsx file format. I know we can export it to csv file, but I want to export it to xlsx file. Like the following: copy(select name_related from hr_employee limit 3) to '/tmp/ABC.xlsx' delimiter ',' xlsx header;

I have rental data from year 2019-2024 in an excel In that excel, the data consists of tenant name, monthly payment details from jan - dec each year I have done massaging the data I need to mapping the payment details and tenant details because both details are in different table Can you suggest what tools can I use so I can mapping the payment details with their tenants details Currently, tenant can pay multiple month in one receipt number, so I need cater for that duplicated receipt number also for my migration

I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel driver or excel itself on the machine in order to be able to import the data? The error is > Could not retrieve the table information for the connection manager > 'Excel Connection Manager'. Failed to connect to the source using the > connection manager. Thanks

Good day, I am facing two blocking points where I would need your insights please. I have downloaded all my sheets in power query and made the necessary transformations, including creating a common Column ID on each sheet based on a project number and company ID (=Project number (IFS)). The goal is to create a report (in the form of a PivotTable with tabular form to display/align the data) that will relate the different sheets based on this Project ID. To create the report, I would like to create a data model and use PowerPivot object. Below is the idea of the desired report design: . My first blocking point, once the data model is created and start using the power pivot, all the data that should be displayed in the different project number is put in line per project and duplicated in all the other project lines also. For example, 'Inspection Activities.' All the data in the PivotTable ends up in a single row in sequence and duplicates for each Project ID. Below is a screenshot of the issue. Did I miss a step? Or is this a limitation of the system?" My second blocking point is the below error. The sheet 'Nadia POs 2020-2023' lists a set of purchase orders by projects and companies. However, it contains replicated data in the 'Project ID (IFS)' column because for each project/company, we have multiple purchase orders. When I try to establish the relationship, I realize that it is blocking. Do you see a way to bypass this error to be able to relate it to the rest of the sheets?

I need to load a number of Excel files using SSIS package into a SQL Server table. I am able to successfully loop through and load my Excel files into my table I do not have any issue doing that, BUT as part of the task, I need to (validate)make sure, the files have a consistent format for all of received Excel files, things I need to check are: + Number of columns should be the same. + Data type for each column should be the same. + Certain columns should not have null/empty values. What do you suggest as the best way (simplicity and performance) to do this? Is using Script task / C# a good way? (the question is only about validating the Excel file format and columns data type)

As part of a web migration process, a vendor has asked for our database schema without the data records (for confidential purposes). They want a MS Excel file with each database table. I am completely new to working with databases. Is there an easy way to do this in SQL Server 2014 Management Studio?

I need to downgrade because I cannot use MySQL for Excel add-in. How can I do it? I use windows 8. There is no information on the MySQL's website.

I'm not very expert in SQL, i just want edit some tables on my website's phpmyadmin and it takes a lot of time.so I want to export the sql file and edit it on Microsoft excel or any similar tools, but i didn't find any solution to open the sql file in excel and didn't find any similar tools. Do you know any solution to open sql in excel or what tool i should to use for this purpose?

I tried to export synonyms table from my server database to excel using SMSS, I choose database, then I choose export, and manage source and dest, then I choose Excel File and I rename sheet 1 to name of synonyms table. The base table that the synonym points to is on another server and another database, but I just handle db server with table synonym, I just want to use the synonym to pull data across the linked server. when I execute, I got error about table x don't exist but I have synonyms :)m and I can choose it from export wizard, anyone know about how to export data from table of synonyms to excel file?

I've recently inherited a MSSQL database as our DBA has moved on to greener pastures. I'm not a DBA and have limited to moderate SQL knowledge but have had to absorb some of his duties and have an issue I am having some trouble navigating around. We have had an issue where users who access the database via Excel have a tendency to grind database operations to a halt and it impacts our ERP software. Our current method of resolution is to look at the activity monitor in SSMS and kill the offending Microsoft Office application. I'd like to have a scripted task that runs every few minutes to keep things moving and not have users flock to IT when the system goes down. I've found that the following query will allow me to pull the lock type and other useful data such as wait time but not the specific application found in the activity monitor. select * from sys.dm_exec_requests where wait_type = 'LCK_M_S' Ideally, this code would find the application Microsoft Office with the wait_type of LCK_M_S and kill the longest waiting task and repeat as needed throughout the day. Thank you in advance for your responses.

I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel: > The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'. These operations have previously worked for months without issue and to my knowledge nothing has changed. I'm looking for a little guidance on how to track down the source of the issue. This is a MS SQL Server 2008R2 instance with the current security settings: - Server Login assigned to an AD group for "Domain Users" to connect to the server. The users that are experiencing this issue are a part of this AD group. I have verified each individually. - Database role created with the AD group for "Domain Users" as a member of the role. Again, the users experiencing issues are a part of this AD group. This database role has securables (stored procedures) individually assigned so as to not grant access on all stored procedures using the following method: GRANT EXECUTE ON 'SPROC' TO 'DATABSE_ROLE'. The stored procedure that is throwing the error in Excel is listed in the securables section of the database role. These settings allow anyone to execute only explicitly assigned stored procedures - and this has been working flawlessly until recently. Does anyone have any ideas on what to check that could be causing the error message in question? Thanks! -Edit- Added permissions dump: name class class_desc permission_name state_desc dbo 0 DATABASE CONNECT GRANT DOMAIN\Domain Users 0 DATABASE CONNECT GRANT DOMAIN\USER1 0 DATABASE CONNECT GRANT DOMAIN\USER2 0 DATABASE CONNECT GRANT guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY guest 1 OBJECT_OR_COLUMN EXECUTE DENY IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Everyone 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Reader 1 OBJECT_OR_COLUMN EXECUTE GRANT IM_Reader 3 SCHEMA SELECT GRANT IM_Reader 3 SCHEMA SELECT GRANT NT AUTHORITY\SYSTEM 0 DATABASE CONNECT GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT public 1 OBJECT_OR_COLUMN EXECUTE GRANT ReportReader 0 DATABASE CONNECT GRANT SSRS_Reader 0 DATABASE CONNECT GRANT SSRS_Reader_S2 0 DATABASE CONNECT GRANT SSRS_Reader_S2 0 DATABASE EXECUTE GRANT

Just started my first internship and I could use some guidance on approaching the project given to me. Simply put, all that needs to be done is to make a database and then transfer the data from a excel spreadsheet and then to automate daily data entry via FTP to the new database. I'm comfortable with SQL and basic database tasks but I'm unsure of the process to create a new database for a small company. Unfortunately I don't have any DBA resources at said company and have no experience on how to manage a database at an organizational level. Considering that the DB will be taking in data automatically every day, I can't host it on my local machine but would rather host it with a cloud service or on-prem w/ remote access right? How would you approach this task?

Using the Save Results As option, shown below. CSV looks good! But when Excel opens/imports it: - Long strings are mangled as numbers (if the data is largely numeric) - Dates are not detected properly - My users beef about all this How do I get a query to a clean excel file (a real xls/xlsx) without dancing? (We like everything to actually in the SQL file itself... I wish we could do "SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS ...")

I work in a large retail business with a decent SQL Server environment. We have SQL Server replication, Availability Groups, Data warehouse, and some cloud services. In this beautiful environment, we use desktop applications with limited features. The business always needs some extra reports that the application support team delivers them late most of the time. As a senior DB Admin, they come back to me to try extracting the data form SQL Server to Excel files. As we grow larger and larger it has become almost like a daily task, I’ve managed to develop a C# tool to send most of those reports by mail to every one who needs them, but now it has become outdated and to be honest it takes a lot of time to be rewritten. Is there a tool that can run SQL query and add the results to Excel file and mail it to specific emails list ?

I have a SQL Server Reporting Services (SSRS) 2012 report that is populated via stored procedure (SP). The SP returns various fields, one of which is a BIGINT datatype. When I execute the SSRS report directly the value is properly displayed, but when I export the output to Excel, it gets rounded to the nearest thousandth. For instance, this is an example of what I'm seeing. The proper value: 480758225903628660 The value shown in the Excel File: 480758225903629000 It's rounding **8660** to **9000**. Truly frustrating, and [Microsoft's documentation](https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/exporting-to-microsoft-excel-report-builder-and-ssrs) mentions nothing about this behavior. I've also dug everywhere on the report that I can think of that may affect this, such as the Text Box Properties: ...the Placeholder Properties: ... and the Value of the field itself: I can "fix" it by converting the field to a string by wrapping the field value in a [CStr() or .ToString()](https://learn.microsoft.com/en-us/sql/reporting-services/report-design/data-types-in-expressions-report-builder-and-ssrs) function, but that also treats the output as a string and not a number. Is there a way to export this to Excel so that the rounding doesn't occur but still keeps this value numeric?

I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.