Here’s a quick way to get data off on of your SQL Server table and dump it to a CSV file. Only 4 steps:
1. Make sure you are in SQLPS console, or have the community SQLPS (or SQLPSv2) module loaded ( ie. Import-Module SQLPS ) in either your PowerShell Console or ISE.
2. Have you query ready, then save it into a PowerShell variable:
$sql = “SELECT * FROM [AdventureWorks].[Production].[Location]”
3. Next one-liner will build the PowerShell object and exported to a *CSV file: (Execute in localhost only. Use -ServerInstance with the -database parameter if is needed t0 execute query)
Next livemeeting, Wednesday July 13th, starting at 6:00PM (45min-1hr), I will be presenting about “Customizing your PowerShell Environment”. Here’s the link if you want to join me:
I’m excited to release this new version of SQLDevTools today Monday June 6th. Here’s list of the updates:
##- SQLDevTools Alpha 0.1.0 Released 06/06/2011:
##-
##- Functions fixed;
## 1. Get-TSQLTableRecordCount dual syntax issue not working properly.
## 2. Write-TSQLScriptIndex parameters ‘DropIndexOnly” and ‘CreateIndexOnly’ was mispelled.
## 3. Get-TSQLTableTriggers not work, add modify_date field as last_modified.
## – Updated two function to include rename of destination table: (need to all help)
## 1. Write-TSQLScriptDFConstraint – Help updated
## 2. Write-TSQLScriptInsertSelect – Help updated
## – Add two new functions to change destination table name in the PS Object: (Help included)
## 1. Rename-TSQLScriptedTableName
## 2. Rename-TSQLScriptedTableIndexes
## 3. Get-TSQLUserDefinedDataTypes
## 4. Get-TSQLXmlSchemaCollections
## 5. Get-TSQLUserDefinedFunctions
## 6. Write-TSQLScriptXmlSchemaCollections
## 7. Write-TSQLScriptUserDefinedFunctions
## 8. Write-TSQLScriptUserDefinedDataTypes
Next release -> will include Views, User Stored-Procedures, and Database triggers.
Basically, this completes the basic need to script out most object needed to rebuild a table, or series of tables. It takes into account “Identity”, Computed, and User Defined objects. Also, I included the ability to alter(rename) your scripted destination tablename, and index objects.
For testing these functions, I’ve use Microsoft “AdventureWorks” which is full of scripting surprises. Keep in mind, I’ve use a mix of both SMO and embed-TSQL that assist in generating the results.
Here’s a teaser sample using new function to script User Defined Data Types:
## Save PS Object to a file, and then view it:
[string] $ScriptToFile = Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName
$ScriptToFile | Out-File c:\Temp\SQLScript01.sql
ii c:\Temp\SQLScript01.sql
[/sourcecode]
Here’s a preview on what I’ve been doing for work. I create about 12 new functions to assist me in automating a task to copy table(s) from one database to another. Then, this process will also rebuild the data, indexes, and constraints from the scripts extracted during this process.
All these functions are using embedded T-SQL scripts to assist in the scripting of the SQL Table objects. Finally, I will be ending having a module to load all my functions when is needed.
Just recently, I updated two on the main functions I posted in the Microsoft Script Repository:
1. “Function Write-SQLCreateTable” – Updated – 05/25/2011: Added three new switch parameters so we can create individual scripts for:DropTableOnly, CreateTableOnly, and TurnOffidentity.
2. “Function Write-SQLInsertSelect” – Updated – 05/25/2011: Add ability to detect if there’s an Identilty Column so it can add the “Set Identity_Insert …” line. And, Add set-location go back to your “Home Drive” when the process completed.
These two updated functions require you have the SQLPS module or in won’t work. That’s why I include to do both Import and Remove of SQLPS module. Also, these functions will not script out Indexes, and Constraints.
In my new SQLDevTools Module, all functions will not import/remove the SQLPS module. It will included functions to script out Indexes, Check Constraints, Foreign Keys and even Triggers. Functions name are change slightly, and a test functions script is included to assist in building rebuilding a table in another database. More functions will be added to this module. I will make it available to the community through CodePlex when is ready.
Here’s a preview snapshot of my new functions:
List of SQLDEVTOOLS functions
Special Thanks to the following bloggers for their contributions:
If you couldn’t make it to TechEd 2011 this year then you just missed two great PowerShell Sessions. But, don’t worry, check out this link: (download and watch)
The first will script out your table(s) structure, adn the second one will build your “insert into… Select…” script so you can populate the data of the empty table(s). I included some documentation so is accessible using the get-help or help commands.
In the “Write-SQLCreateTable” function, I use some pre-defined scripting options but you may add/removed and only use the ones you need. Here’s the ones I use:
For now, you need to provide the table schema, and the name of the tables will rename the same. I will be updating this function to later allow to include a prefix to change the destination table name, and maybe include change the schema.
Keep in mind, both functions will display the results on screen. You can save the resutls to an PS Object and the use the “Invoke-SQLcmd” the “-query” parameter to execute the scripts.
I have to admit that having setup Office 365 was a breeze. Just make sure you have a reliable fast internet connection, and you are good to go. By the way… no servers equipment is needed.
and
Well, since I got Office 365 Beta setup in my computer I’ve been looking for information about how to use PowerShell. Now, thanks to one of my PowerShell MVP
college (Sean Kearney) mention about this information been well documented in
Office 365 Beta. I found it, and I couldn’t believe it was that SIMPLE!!! YES,
SIMPLE!!
My computer environment is very basic. I have no Exchange, Linc, or Sharepoint. So now I’m part of the cloud experience with Office 365, and my answer of using PowerShell was at my fingertip.
Search for "Using PowerShell"
If you want to try it, then check out the links below, and copy/paste the code. Make sure you meet all the requirements needed so you can experience using PowerShell with Office 365. By the way, It’s highly recommended by our PowerShell MVP’s colleges to set your Execution Policy to “ RemoteSigned” and not “unrestrincted”. Using “unrestricted” will still give you some security warning.
Office 365 – PowerShell – Resource Information Currently
available under: “Outlook Web App Help”
**You need to understand one thing, and quote this from the
help “… By default, all new user accounts are allowed to use Windows Remote Management (WinRM) to access the cloud-based organization with Windows PowerShell …”. But, the help documentation gives you the
code to prevent users to access remoting with Office 365. (read the help documentation)
By the way, there’s a recent change to the connection URI:
I love our PowerShell MVP Community. Here’s an observation from Aleksandar Nikolic: “… i think you should use generic Connectionuri(https://ps.outlook.com/powershell) and not specific server. you’ll be redirected … office365 will redirect you to closest, the least busy server …”. Thanks Alek.:)
This is totally true, and the only reason I included the change was to avoid the redirection warning message. So you can still use what’s documented:
We are pleased to announce the first South Florida IT Camp on Saturday, July 23rd 2011 from 8:00 am to 5:00 pm at Nova Southeastern in Fort Lauderdale.
Parking and admittance to this event is free. Lunch will provided. Please Register now. Let your friends and colleagues know about this event.
Join system administrators IT professionals and database professionals in addition to managers at all levels in Florida that work with Microsoft technologies for a “IT Camp Saturday”. IT Camps are a free, one day learning event for anyone seeking professional development. This event serves IT professionals and students with a focus on IT Pro related technologies. IT Camps offer a conference style learning environment free of charge to attendees and is open to presenters of
all backgrounds and expertise.
The call for speakers is open until 6/11. Topics can be anything applicable to System admins, and/or IT Pros including SharePoint, System Center, SQL Server, PowerShell, Active Directory, or Windows 2008 R2 and Windows 7.
To submit your session, please Click Here, or if you have questions? Comment or contact us.
This script hold a total of 4 functions to help setup your missing PowerShell User profiles which are not created when installing Window Management Framework, nor setup during then new Windows 7/2008R2 Operating Systems.
