Windows 10: Windows 10, SQLServer Express 2019, Excel 2016 import to SQLServer error...

Apologies. This is likely not the right Category. Tips to fix this appreciated.


Environment:

Workstation: 16GB RAM. 500MB SSD

Windows 10 Pro 64-bit

Microsoft Office 2010? 2016? Home/Student? Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016

SQLServer 2019 Express Edition Just installed 2 weeks ago.


Not available on workstation though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10:

Visual Studio

C# compiler

etc.


Task:

Import a USA-state-government-supplied .csv converted by me to an Excel spreadsheet into SQLServer.

The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQLServer table. No stored procedures. No constraints, 1 Primary Identity Key.

Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do *not* want to break the Excel spreadsheet into 500 pieces to load the entire dataset. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.


Error/roadblock steps to reproduce:

Within Microsoft SQL Server Management Studio,

I select the database.

I select Tasks > Import Data ...

I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path path to local spreadsheet file; Excel version -> Microsoft Excel 97-2003 or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016; First row has column names; Next

An error is displayed:

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.
ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. System.Data


How can I fix this? TIA



Additional info:


ODBC Data Source Administrator 64-bit Drivers tab has entries for

Microsoft Excel Driver 16.00.4513.1000 ACEODBC.DLL

SQL Server 10.00.18362.01 SQLSVRV32.DLL


ODBC Data Source Administrator 32-bit Drivers tab has entries for

Microsoft Excel Driver 10.00.18362.01 ODBCJT32.DLL

SQL Server 10.00.18362.01 SQLSVRV32.DLL


Finally -

I saw this: datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

I tried the steps for Option B, but they did not change my outcome.

:)

 

SQLServer 2016 AlwaysOn with SQL 2017 Secondary replica

Your question is outside the scope of this Community.

MSDN SQL Server Express

Msdn forums - SQL Server Express

TechNet SQL Server Express

Technet forums - SQL Server Express

TechNet SQL Server Forums.

Technet forums - SQL Server

"MSDN SQL Server Forums"

Msdn forums - SQL Server

TechNet Forums:

https://social.technet.microsoft.com/Forums/en-US/home

MSDN Forums:

https://social.msdn.microsoft.com/Forums/en-US/home

Cheers.

 

PowerShell SqlServer module only imports functions and aliases, but no cmdlets

I am trying to use the SqlServer PowerShell module, available on the PowerShell Gallery.

After installing, either to -Scope CurrentUser or in an admin session, doing Import-Module SqlServer only results in three commands being imported into the session:

This is in spite of the fact that doing (Get-Module -Name SqlServer -ListAvailable)[0].ExportedCommands.Values lists out all of the commands that are supposed to be included:

I tried installing an older version of the SqlServer module, as well as tried a different user account on my machine. Lots of reboots, new PS sessions, etc. Running Import-Module in verbose/debug mode doesn't throw any errors, it just imports only the function and aliases; none of the Cmdlets. The problem factor seems to be this particular Windows system, which is Windows 10 Pro 1803.

It is also worth noting that I'm only trying to use this module because the sqlps module that comes installed with SQL Server Management Studio stopped working on this machine. I don't know why that was the case; my guess was relating to a Windows or SSMS update, but in order to get to the point here where at least the SqlServer module installs without errors and warnings about clobbering, I removed that module from my $PSModulePath. Something else that broke seemingly at the same time was that my WSMan TrustedHosts list was reset (to blank), causing issues with PowerShell remoting that I had solved previously (this machine is not domain-joined).

How in the heck do I troubleshoot this?

 

INSTALL sqlserver 2008r2 in windows2008 server sp1 error: MOF syntax error

Your question is outside the scope of this Community.

Kindly repost your question in the TechNet SQL Server Forums.

Technet forums - SQL Server

"MSDN SQL Server Forums"

Msdn forums - SQL Server

TechNet Forums:

Technet forums

MSDN Forums:

Msdn forums

Cheers.