Category: Windows 10

PowerShell – SQL Server Management Studio v17 is Ready!

Posted on by admin

Yes! The SQL Server Management Studio  Version 17 is available for download for the purpose of managing the new SQL Server 2017, Azure SQL Databases and Azure SQL Data Warehouse. At the same time this version won’t prevent you from working with older SQL Server such as SQL Server 2000 and SQL Server 2005 but there will be some features that won’t work. Please know the limitations!

To download the new SQL Server Management Studio V17, click on the following link:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Now there are a few things, you should be aware. This version depends on Visual Studio 2015 with the update KB3165756 installed. I ended up crashing SSMS when I made the mistake of uninstalling Visual Studio 2015 to make some room for the Visual Studio Preview.

Here’s the SSMS error message that will popup and made my application useless.

But, no worries! In order to correct the issue, I did the following steps:

1. Uninstall SSMS
2. Restart machine (Windows 10 Insider Build 16179)
3. In my case, I installed the Visual Studio 2015 Shell only.
4. Search and installed the Visual Studio 2015 Update – KB3165756.
5. Restart machine.
6. Just to make sure, open Visual Studio 2015 and verify the update was installed.
7. Download SQL Server Management Studio V17: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
8. Install and restart system.
9. Open SSMS v17 and verify there are no errors.

Apparently, there are strong dependencies in SSMS with Visual Studio 2015.

Now, there are a few changes from previous SSMS Release Candidates in regards with PowerShell SQL Server commands. As the new PowerShell “SQLServer” module is no longer included with SSMS v17. But, you still get the SQLPS module.

The following PowerShell one-liners to check for the modules installed:

[sourcecode language=”powershell”]
gcm -Module SQLServer -CommandType Cmdlet | Select-object -first 10 name
gcm -Module SQLPS -CommandType Cmdlet | Select -first 10 name
[/sourcecode]

In order to installed the PowerShell “SQLServer” module, you’ll need to downloaded from “PowerShell Gallery”. Follow the instructions from the following link:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module

One requirement for using PowerShell “SQLServer” module is to have PowerShell Version 5.0 or greater.

[sourcecode language=”powershell”]
Install-module -Name SqlServer -Scope CurrentUser -AllowClobber
[/sourcecode]

Make sure that after you complete the installation, close and open another PowerShell session.

Now, you are ready to work with both SSMS v17 and the new PowerShell “SQLServer” module.

 

Drawback PowerShell Open Source Alpha18 on Bash on Windows 10

Posted on by admin

As we all know that PowerShell Open Source is a “work-in-progess” as it still in the alpha stage. So, previous version Alpha17 was a breakthru as it finally clear some bug in the Bash console. But, unfortunately it fell back with a different bug at the console level.

This is on the latest Windows 10 Insider build 16170.rs_prerelease.170331-1532.

It seem that when you’re typing to skips to the next line. And, sometimes I’ve seen the scroll up failing to display previous information.

The good thing is, everything work when using the Ubuntu Desktop under Windows X-Server. Also, the progress-bar issue got cleared and the Update-Help works.

Keep in mind, to use the “Update-Help -force“, you need to have admin privileges. So, use the following command:

sudo powershell

Patience is the key!

It will soon be fix. Remember to go to Github.com to post any PowerShell Open Source bugs: https://github.com/PowerShell/PowerShell

 

PowerShell, SQL Server, and Linux Oh My!

Posted on by admin

The South Florida Code Camp 2017 is ON! Come and join me in this event on Saturday, March 11th 2017 all day event.

Check out the event speaking agenda: http://www.fladotnet.com/codecamp/Agenda.aspx

I will be presenting the following 3 sessions:

1. 09:50am – 11:00am – Working with SQL Server for Linux Cross-platform:
I will be showing the latest build of SQL Server vNext for Linux. Everything you need to install and connect to SQL Server. Then, what tools are available to use from either Linux and Windows. Sample on querying for database information using Python and PowerShell between two environments. This will be a demo intensive session.

2. 11:10am – 12:20pm – Using Windows 10 Bash with PowerShell Open Source:
We’ll be taking a tour into Windows 10 Bash Linux subsystem. I’ll be sharing some tips on how to work with Bash, and the workaround to make PowerShell in side BASH. This is the perfect environment to start learning about Linux while you work in windows. We’ll be take advantage of how-to use cross-platform Open source product(s). All this and more from Windows 10.

3. 01:20pm – 02:30pm – Using PowerShell Open Source across multiple Platforms:
PowerShell is Open Source now! Come see how you could use PowerShell cross-platform between Windows and Linux sharing scripts to assist in admin task automation. I’ll be walking on how to make this work with existing tools. Also, interacting with SQL Server for Linux.

To register go to the following link: http://www.fladotnet.com/codecamp/

Can you run PowerShell in SQL Server Management Studio? YES!

Posted on by admin

Just to be clear!! You can run PowerShell in SQL Server Management Studio.

Not only you can run PowerShell, but you can create scheduled jobs in SQL Agent to run PowerShell scripts. This has been available since SQL Server 2008 (before R2). And, this is a lot better now, as each version are finally providing more “SQL PowerShell cmdlets” to manage your SQL Server in their *”SQLPS” PowerShell module.

*Note: SQLPS Module was introduce with SQL Server 2008.

Nowadays, Thanks to both Aaron Nelson, Christy LeMaire, and Rob Sewell who have contribute to the success of provide new enhancements to SQL Server PowerShell (SQLPS) cmdlets. Check out their tools:

DBA Tools “best practices and instance migration module” link: https://dbatools.io/
DBA Reports “free, fun” link: https://dbareports.io/

By the way, DBA Reports is owned by Rob Sewell – @sqldbawithabeard. Great Work!!

So, YES! You can run PowerShell from SQL Server Management Studio.

How to run PowerShell?

You can Right-Click on most of the SQL Server objects under “Object Explorer” and look for “Start PowerShell“.

This will open the PowerShell prompt and you are ready to start your adhoc scripting.

Keep in mind, on the latest version of SQL Server (< 2012), the SQL PowerShell module (SQLPS) is loaded and already available. This will create a SQL Server Drive connecting (in this case) to your local instance installation or whichever instance you’re connecting to.

Notice, in my case, the above image will open a PowerShell prompt and is using PowerShell version 5.1 which is part of my Windows 10. The same will be true on earlier OS version of PowerShell. The “Start PowerShell” will open the current PowerShell version installed on that machine.

Another thing to understand, although you already have a set of available cmdlets to manage your SQL Server, you can still expand and build more script with the use SMO (SQL Server Management Objects). So, the possibilities to build your own solutions are endless.

All SMO .NET assemblies are loaded into your system when installing SSMS.

About SQLPS been removed

To be clear! Documentation states that SQLPS “Utility” (sqlps.exe) will be removed in the future. But, the SQLPS PowerShell module will still be available. (See reference link)
https://technet.microsoft.com/en-us/library/cc280450(v=sql.130).aspx

This is why you rather use the normal PowerShell console and start using the SQLPS module. Keep in mind, that since PowerShell 3.0, all existing installed modules are automatically loaded and ready to use in your PowerShell session.

How do I get SQL PowerShell?

Simple! SQL PowerShell comes included when SQL Server Management Studio(SSMS) is installed. For sometime ago SSMS (SSMS 2012) has been available to install separately (free-of-charge). As a matter of fact, you could install three separate SQL Server Features components without the need of installing SSMS and start scripting against your SQL engine.

The following link shows both latest version of SSMS (16.5.3) and the preview SSMS for SQL Server vNext (RC 17) can be found here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Bonus – No need for SSMS GUI nor the SQL Engine

Sometimes there’s no need to install a SQL instance, nor SSMS GUI but only the necessary components installed in order to run and scheduled some SQL PowerShell scripts in Windows Server Task Scheduler. I had this scenario on a **server with no SQL engine but needed to run some scheduled SQL PowerShell scripts. Only 3 components are needed:

(Below content extracted from Microsoft link (Install section) : https://www.microsoft.com/en-us/download/details.aspx?id=52676 )

– Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016
The Microsoft Windows PowerShell Extensions for SQL Server includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

Filename: X86 and x64 Package (PowerShellTools.msi)

– Microsoft® SQL Server® 2016 Shared Management Objects
SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services.

Note: Microsoft SQL Server Management Objects requires – Microsoft SQL Server System CLR Types, that is available on this page.
Filename: X86 and x64 Package (SharedManagementObjects.msi)

– Microsoft® System CLR Types for Microsoft SQL Server® 2016
The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server.

Filename: X86 and x64 Package (SQLSysClrTypes.msi)

**Note: This can apply to desktop/laptop is you don’t want to install the whole SQL Server CD. As long as, you have remote connection to a SQL Server system, then you just start building scripts. You will save some disk space too.

IDERA Geek Synch Webinar – Wednesday February 22nd, 2017

Posted on by admin

Topic: Using PowerShell with Python and SQL Server

Description: Just as PowerShell is argued as the main technology for automation in Windows Datacenters Infrastructure, it is equally important in other non-Windows Operating Systems. According to Maximo Trinidad, Windows Administrators have an advantage working with PowerShell just as Linux Administrators have an advantage with Bash / PHP / Python.

Webinar starts at: 11:00AM – 12:00PM (CDT) / 12:00pm – 01:00pm (EST)

Register at: https://www.idera.com/events/geeksync

Florida PowerShell User Group Online Meeting – Thursday February 23rd, 2017

Posted on by admin

Topic:  Understanding Bash On Windows 10

Description: Come and learn how to setup and use Bash On Windows 10. Learn the tips and tricks to use PowerShell and Python together. At the same time, there are some differences in working with script files between Bash and Windows systems. We’ll be seen how to use efficiently use Windows apps with Python scripts on Bash. Lots of demo!

Meeting starts at: 6:30pm(EST)

Register at: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-february-2017-tickets-31689059831

Windows 10 Bash and PowerShell Redux

Posted on by admin

In my previous blog “http://www.maxtblog.com/2016/10/running-powershell-in-windows-10-bash-workaround/“, I was able to find a workaround to how work with PowerShell Open Source in Windows Bash subsystem.  But now, I found a better way using the Ubuntu Desktop component.

It’s a known fact, if you install PowerShell Open Source in Windows 10 Bash subsystem, that it won’t work correctly. As soon as start typing $PSVersionTable and press enter, the cursor goes to the top of the screen. And, you keep typing and it gets very ugly.

Now, what if I tell you, I found the way to run PowerShell Open Source without any of these issues. Just like running it like it was installed in a Linux environment. No issues with the cursor going crazy and able to page up and down.

The following are the steps required to run PowerShell Open Source.

Windows 10 Requirements

You *MUST* use the following Windows X Server application. Just proceed to Download and install VcXsrv Windows X Server:
https://sourceforge.net/projects/vcxsrv/?source=typ_redirect

Also, proceed to Download (to be install later) PowerShell Open Source. Go to the following Github link, and pick the Ubuntu 16.04 version: for Ubuntu 16.04:
https://github.com/PowerShell/PowerShell/releases/tag/v6.0.0-alpha.14

For best results, make sure to be on Windows 10 Insider Build 15007.rs_prerelease.170107-1846.

If previously installed, reset your Windows 10 Bash Subsystem installation, by executing to following commands on either DOS or PowerShell prompt:
1. Uninstall command: lxrun /uninstall /full
2. Clean Installation: lxrun /install

*Note: I’m assuming you’ve been recently checking out Bash in Windows 10. If not, go to “Control Panel | Programs and features” then go to “Turn Windows features on or off” and enable “Windows Subsystem for Linux (Beta)“. By the way, you must have “Developer mode” enabled under “Settings | Update and Security | For Developers” section.

Then, on the Windows menu, look for the “Bash on Ubuntu on Windows” icon and open the console. Make sure Bash is installed and working in Windows 10.

Bash Subsystem Requirements

Now, we need to start installing some additional components in our Bash environment.  Please notice I can use PowerShell console and open “Bash“.  Also, while installing these Linux packages, make sure to open any console “as Administrator“.

Then, in Bash prompt, use the Super User privilege command “sudo su” before installing these packages.

sudo su

I recommend to use the following two command for the purpose of getting both available updates and upgrades to Bash installed packages. You could use these command on a regular basis to keep you subsystem up-to-date.

sudo apt update

sudo apt upgrade

The following commands will install the Ubuntu Desktop and takes about 1hr to complete. Patience is a virtue!

sudo apt install ubuntu-desktop

In some cases, at the end of the installation you will either see no errors, or it will end with the following message: “E: Sub-process /usr/bin/dpkg returned an error code (1)“.

Now, the next package installation install the desktop setting manager application you will need to use later;

sudo apt install compizconfig-settings-manager

So after all is done, use the “exit” command to get out of the Super User session and get back to your user session.

exit

Installing PowerShell

Now, we are ready to install PowerShell Open Source. Remember, if you follow above Windows 10 Recommendation section, you have already downloaded PowerShell. This file will be located in your “..Username\Downloads” folder.  From the Bash prompt, you’ll probably be seen “username@SystemName:/mnt/c/Users/username$” when you open the console as a regular user (non-Admin privilege).

The following commands change folder location to where the PowerShell installation package was saved and the execute the package.

cd Downloads
sudo dpkg -i powershell_6.0.0-alpha.14-1ubuntu1.16.04.1_amd64.deb

This package installation should end without any errors. Don’t open PowerShell yet!

The Ubuntu Desktop

First, in Windows 10, we need to start our “VcXsrv Windows X Server” application by clicking the Windows X Server “Launch” icon. Pay close attention to the field labeled “Display number” should have the value ‘0‘ and pick the “One large window“. Then clicking “Next” to accept all default values.

This will open a blank Windows X Server display.

Now, open “Bash on Ubuntu on Windows” prompt, and at this point there’s no need to open “as Administrator”. Type the following commands to start the “Ubuntu Desktop” in Windows 10:

export DISPLAY=localhost:0

This command will allow the any gui application to interact with our Windows X Server service.

Then, run the command ‘ccsm‘ to configure the Ubuntu Desktop settings: (This is a one time setup)

ccsm

Click “OK” to continue making settings changes following the images below.

After updating the settings, click “Close”

To proceed to start the Ubuntu Desktop, just execute the following command:

compiz

You will notice there are some applications available to use.

Let’s concentrate of the ‘File’, ‘Text Editor’, and ‘Terminal’. You can manually execute some gui applications from the bash command prompt such as:
1. For File Browsing execute: nautilus or ‘sudo nautilus’ with elevaded security.
2. For Text Editor execute: gedit or ‘sudo gedit’ with elevated security.
3. For Internet browsing: firefox

Now, we are ready for PowerShell.

PowerShell in Windows 10 Bash

At the Ubuntu Desktop just right-click and pick “Open Terminal”. Then, type “powershell” and press enter.

Then execute the following powershell commands:

$psversiontable
dir

As you can see we, now PowerShell is working in Windows 10 Bash thank to Ubuntu Desktop. But, keep in mind, Ubuntu Desktop is not supported in Windows 10 Bash subsystem. At least, you can give it a test drive.

By the way, you can use your favorite PowerShell Editor

BONUS Section

Important Path to Remember, in case you’ll find where things are in Windows 10 Bash Subsystem.

Windows 10 BASH folders:
C:\Users\mtrinidad\AppData\Local\lxss
C:\Users\mtrinidad\AppData\Local\lxss\home\”Linux_UserName”
C:\Users\mtrinidad\AppData\Local\lxss\rootfs\usr\local\bin
C:\Users\mtrinidad\AppData\Local\lxss\home\”Linux_Username”\PowerShell

Bash file path to Windows folders:
/mnt/c
/mnt/c/Users/”Windows_UserName”/Downloads
/home/”Linux_UserName

Where PowerShell Open Source folder are located in Bash Subsystem:
/usr/local/bin/PowerShell

Additional Information:
Information about installing Ubuntu Desktop in Windows Bash:
— See link for info: https://github.com/Microsoft/BashOnWindows/issues/637

 

PowerShell Open Source Alpha14 and SQL Server for Linux CTP 1.1 are out!

Posted on by admin

This is Great! Microsoft keep delivering updates before Christmas just to keep us busy and excited.

To download the latest PowerShell Open Source just go to the link below:

https://github.com/PowerShell/PowerShell

Just remember to remove the previous version, and any existing folders as this will be resolved later.

To download the latest SQL Server vNext just check the following Microsoft blog post as the new CTP 1.1 includes version both Windows and Linux:

SQL Server next version Community Technology Preview 1.1 now available

And, don’t forget the check out the release notes as Microsoft SQL Server team has done an excellent job in providing documentation:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes

For those interested in how SQL Server became to Linux, check the following Microsoft Blog posts:

SQL Server on Linux: How? Introduction

http://arstechnica.com/information-technology/2016/12/how-an-old-drawbridge-helped-microsoft-bring-sql-server-to-linux/

Additional Tools in the Horizon

Just an FYI on some tools that recently caught my attention from Jet Brain:

1. PyCharm Community Edition (free) – Python Develoment IDE Tool available for both Windows and Linux.
2. DataGrip – SQL Developer IDE Tools for working with Databases (including SQL Server). Also available for both Windows and Linux.

And, of course, we can’t forget of Microsoft VS Code. This lightweight coding IDE works great in both Windows and Linux:

https://code.visualstudio.com/

 

Running PowerShell In Windows 10 BASH (workaround)

Posted on by admin

Yes! It’s possible. There’s a workaround make PowerShell to work in Bash for Windows. The only drawback, you still can’t do a Clear-Host (or clear or cls), at the PowerShell prompt.(yet)

(Updated 11/03/2016):  You can use Ctrl-L to clear the screen while using both ‘xterm’ and ‘screen’ while working at the console only.  At least is better than nothing!

poshbash_01_2016-10-02

The original behavior, and still is, the cursor goes to the top of the screen and it making hard to work.   This issues has been logged in Github PowerShell site:

But, let first install PowerShell for Bash on Windows.

Installing PowerShell

To identify which version of PowerShell to install, you’ll need to find what’s the Windows Bash version. Run the following Linux command:

username@hostname$ cat /etc/issue

poshbash_00b_2016-10-02

This command will show the Linux OS is Ubuntu Bash version is 14.04. Next is to  proceed to Github PowerShell and select to download the Ubuntu 14.04 version: https://github.com/PowerShell/PowerShell/releases/tag/v6.0.0-alpha.10

Look for and click on “powershell_6.0.0-alpha.10-1ubuntu1.14.04.1_amd64.deb” to initiate the download.

Please understand that this file type is *.deb and will only install on the correct Linux OS version. This file will be download to your Windows “Downloads” folder and you won’t be able to execute from Windows File explore.

poshbash_00c_2016-10-02

Now, confirm you downloaded and can see the file under Bash subsystem by doing the following Linux command:

$ ls /mnt/c/Users/mtrinidad/Downloads

poshbash_00d_2016-10-02

To installing the PowerShell version for Ubuntu 14.04, do the following commands:

$ cd /mnt/c/Users/mtrinidad/Downloads
$ sudo dpkg -i powershell_6.0.0-alpha.10-1ubuntu1.14.04.1_amd64.deb
$ sudo apt-get install -f

Now, you can run PowerShell from the Windows 10 BASH prompt. But, it won’t be pretty useful. Yet!

What’s the workaround PowerShell in Bash for Windows

Basically, there are two application you can try:
In my case, I didn’t have to installed these applications.  But, if you need to install either application, the use the following command lines and answer “y” to install:
$ sudo apt-get install screen 
$ sudo apt-get install xterm
Now, there is one more application that might be useful to install using the same command format (see above). Its the GUI editor “gedit”:
$ sudo apt-get install gedit
This is a very practical text editor that remind me of Notepad.  Keep in mind, I’m coming from a Windows Ecosystem.  Of course, Linux expert may go for Vim or Emacs editors.

Xserver for Windows 10

 Two of these applications: xterm and gedit that will need Xserver running in Windows 10.  I’m currently using “VcXsrv” and can be downloaded from: https://sourceforge.net/projects/vcxsrv/
After installation, double-click on the XLaunch icon and select how-to display the program.
poshbash_00e_2016-10-02
Then, go back to the Bash prompt and type the following command the set the Xserver popup window, follow by the application. In this example will be executing “gedit“.
$ export DISPLAY=localhost:0
$ gedit /mnt/c/Users/mtrinidad/Documents/test.txt
poshbash_00f_2016-10-02
In order to use application(s) in *Xserver, its only one application at the time per Bash console open. In other word, to run two applications, you’ll need to open two Bash console to run each individually.
*Note: You will notice  Warning messages after exiting the application which can be ignored.
poshbash_00h_2016-10-02
Now, we got all we need to start working with PowerShell in Bash for Windows.

PowerShell – Using xterm

Now, to use “xterm“, you’ll need run the Xserver application “VcXsrv” as previously explained. Follow the steps: (In my case, I’m opening PowerShell Console in Administrator mode)

  • Open PowerShell Console (in Administrator mode)
  • Execute Bash
  • Then, at the Bash prompt type: $ export DISPLAY=localhost:0
  • Follow by: xterm

This will open a popup window for xterm application. In the xterm prompt, run powershell and you can start working with PowerShell.

$ powershell

poshbash_00i_2016-10-02

But wait!! You’ll notice that there’s no scroll bar to page up or down. I found the following link that fix the issue and you can enable the scroll bar using the mouse.
http://beforewisdom.com/blog/tech/xterm-with-a-scrollbar/

This is where you use gedit text editor to add the code to make the scroll bar to work with the mouse.

poshbash_00j_2016-10-02

Now you can use PowerShell to xterm application.

PowerShell – Using screen application

This is another workaround to work with PowerShell by using the “screen” application without the need of using Xserver program.

Just open the PowerShell console (in Administrator mode) then type “screen” and press enter.

poshbash_00k_2016-10-02

Oops! This application will need to be executed as super user:

poshbash_00l_2016-10-02

Keep pressing enter to bypass the screen information prompt.

poshbash_00m_2016-10-02

You will ended up at a # prompt, then you can execute PowerShell:

# powershell

And start working with PowerShell. But, again you’ll notice, you won’t be able to scroll up and down (again).  There’s not scroll bar!

Here’s how to fix the “screen” scroll issue: press Ctrl-A and then ESC. Now, the scroll up and down feature will be enabled.

poshbash_00n_2016-10-02

And, at the same time, both the arrows up/down keys are working during the remaining of your session.

poshbash_00o_2016-10-02

Now, exiting the “screen” application, you will ended up type the “exit” command a few times. About 3 time to get to the starting point.

poshbash_00p_2016-10-02

There’s no excuse not to use PowerShell In Bash for Windows. I have to admit it was little painful to gather this information and work around. But I think is very useful.  This is Awesome!

 Useful Resources

Make sure to check the GitHub incident email threads, mention in the beginning of this blog, which leads to make this workaround possible: #933 and #988, Community contribution made this possible.

Here are some addition links that help me push through this workaround:

** Xterm
http://beforewisdom.com/blog/tech/xterm-with-a-scrollbar/

** screen
http://www.saltycrane.com/blog/2008/01/how-to-scroll-in-gnu-screen/
http://neophob.com/2007/04/gnu-screen-cheat-sheet/

** Finding Linux version:
http://www.lostsaloon.com/technology/how-to-check-and-find-your-linux-os-version/

 

PowerShell – SQL Server 2014 SMO TruncateData() Workaround

Posted on by admin

As I was still puzzle why the SMO *.TruncateData() was missing in SQL Server 2014, I needed to find a quick workaround to continue with my data migration.

Of course, here comes T-SQL scripting to the rescue:

[sourcecode language=”sql”]

Truncate Table databasename.schema.tablename
GO

[/sourcecode]

Basically, subtitute the SMO .TruncateData() with few lines of T-SQL statement, and have PowerShell run the code against SQL Server. The script is shown below:

[sourcecode language=”powershell”]
## —————————————– ##
## – Using SMO steps to work with tables:
$SQLServerInstanceName = ‘TSQLDSP01’; $global:SQLServerDatabasename = ‘devMaxText’;
$global:DbSchema = ‘dbo

[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
$SQLSrvObj = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$global:SQLServerDatabaseName].tables;

$global:cnt = 1;
foreach ($t in $tables.name)
{
$tsqry = @”
Truncate Table $($global:SQLServerDatabasename).$($global:DbSchema).$($t)
Go
“@;
#$SQLSrvObj.Databases[$SQLServerDatabaseName].ExecuteNonQuery($tsqry);
Write-Verbose “[$($global:cnt.ToString(“0000″))]Truncate Table dgSAP_Old.dbo.$($t) Process” -Verbose;
$global:cnt++;
};
[/sourcecode]

This *script will assist in the third-party application to load date back to the tables for our migration.

*Note: Just in case, I commented out the line having the .ExecuteNonQuery() method.