Import Excel Data into SQL Server with PowerShell
In this article we look at PowerShell code you can use to import Excel data into SQL Server.
Diana is currently working as a DBA and data centric applications developer for a small ISV company. Her 6 years of experience covers SQL 2000, SQL 2005 and SQL 2008. Besides working with SQL Server, she also does .NET development. Diane is constantly trying to improve her knowledge; therefore, she is involved as much as possible in the activity of the local PASS chapter.
- MSSQLTips Awards: Trendsetter (25+ tips) - 2020
Excel Data Import to SQL Server with PowerShell
In this article learn how to import selective data from an Excel worksheet into SQL Server using PowerShell.
Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE
In this tip we look at how to read an Excel file from within SQL Server using OpenRowSet and OpenDataSource along with the possible errors you may encounter and how to fix these issues.
An existing connection was forcibly closed by the remote host error
In this tip we look at how to troubleshoot and fix SQL Server Service Broker error receiving data 10054 existing connection was forcibly closed by the remote host.
Understand SQL Service Broker Communication Flow Between Databases
In this tip we look at how the SQL Server transaction log is used to transmit service broker messages between the source and target databases.
Steps to Restore Service Broker Enabled Database on New SQL Server Instance
In this tip we cover what you should know when restoring a service broker enabled database to another SQL Server instance.
Synchronizing data between SQL Server instances with Service Broker
In this tip learn how to use Service Broker to synchronize data.
SSIS Script Task to Connect to a Web Service
Learn how to build a SSIS script task to connect to a web service using Windows authentication.
SQL Server Agent Error Logging for Powershell Job Steps
Unfortunately the SQL Server Agent error logging can be deceiving for job steps when you run PowerShell scripts. In this tip I explain how I setup my SQL Agent jobs to get correct error messaging from my PowerShell scripts.
Improve PowerShell SMO Performance for SQL Server
Improve SMO performance when using PowerShell scripts by invoking server.SetDefaultInitFields.
Script and Alter SQL Server Database Objects with PowerShell to Correct Collation Issues
Recently one of our customers experienced an Infor ERPLN upgrade from version 10.3.1 (FP9) to version 10.4 (FP10) which went terribly wrong. The upgrade took place on a development server which has multiple versions of the software installed side by side
Error handling with try-catch-finally in PowerShell for SQL Server
PowerShell V2 introduces the try-catch-finally statements, similar to those you already use when you write .NET code. In this tip we look at how this can be used for your PowerShell scripts.
Introduction into handling errors in PowerShell for SQL Server tasks
PowerShell is becoming a great tool for managing SQL Server tasks, but like most tasks that are coded there is always the need for error handling to deal with the unknown. PowerShell has several options for handling and capturing error details and in thi
Iterating through SQL Servers and database objects with PowerShell
I've read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeac
Introduction to SQL Server CLR table valued functions
Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join
Options for cross database access within SQL Server
I need to create a stored procedure that queries data that are not in the current database. What are my options?
SQL Server sp_ prefix when and when not to use it
There have been many articles written about the issue with prefixing SQL Server stored procedures with sp_. In this tip we will take a look at some of these issues and also some differences between SQL 2000 and SQL 2005, 2008 and 2008 R2.
Introducing PowerShell V2 Remoting for SQL Server
PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.
Removing HTML tags from text data using PowerShell
In this tip I'll address the same problem as a previous tip using CLR, but to get the job done I'll use Powershell. Powershell will let me clean the string data and export the result to a .csv file in a single step. The tip I've mentioned before covers
Remove HTML tags from strings using the SQL Server CLR
There are situations when you may need to remove HTML tags from your character string data. As an example, consider having to submit a product data feed to a search engine like Google. The detailed product description is mandatory in this case. It is rec
.NET Bulk Insert into SQL Server
The SqlBulkCopy class is helpful if you need to load large amounts of data into your SQL Server database from within a .NET application. In this tip I walk through a simple console application which demonstrates the basic usage of this class and some opti
Using Extended Events to troubleshoot SQL Server issues
Learn hwo tu use SQL Server extended events (XEvents) to troubleshoot performance issues faster.
Converting UTC to local time with SQL Server CLR
Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to ano
Streaming rows of SQL Server data to a table valued parameter using a SQLDataReader
Before SQL Server 2008, passing multiple rows of data to a stored procedure was not a straightforward process. This involved additional steps such as creating an XML document from the data values and passing it to the procedure which may insert and update