OPENROWSET from excel 2010 and 2007


I have the below versions in my stystem:

Database Version:  SQL Developer Edition (64-bit)
Windows Version: Windows 7 Enterprise (64-Bit)
Excle:  Micorsoft Excel 2010 or 2007(64-Bit)


Since all the versions are 64 Bit. So we need to install  Microsoft.ACE.OLEDB.12.0 from “AccessDatabaseEngine_x64”. instead of 'Microsoft.Jet.OLEDB.4.0'. We can download this from the below link

http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

After downloading and running the setup. Run the below queries step by step.

sp_configure 'show advanced options',1
 GO
 RECONFIGURE
 GO
 sp_configure 'Ad Hoc Distributed Queries',1
 GO
 RECONFIGURE;
 -----Run the below query after running above.
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
SELECT * FROM
OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\rmamillapalli\Desktop\Ramesh\Shanthi.xls;HDR=YES;IMEX=1','SELECT * FROM [WrongThirdPartySupplier$]')
Above given path is my local path and Header columns in the excel should be in the first

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more