[Solved] Parallels Desktop: Interface vmnet1 is not present. This should be created at boot time.
I recently modified my Parallels DHCP settings because I'm really particular about the internal subnets I work with in the lab (192.168 is the ugliest subnet ever). Once I modified the file at /Library/Preferences/VMware\ Fusion/networking (OS X) my Parallels Desktop erred out and said
Interface vmnet1 is not present. This should be created at boot time.
Someone on this thread suggested a reinstall but that only worked as a temporary fix. Turns out, when you modify the networking file, you also need to restart your vmnet-cli service. You can do this with the following commands:
/Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --stop
/Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --config
/Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --start
/Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --status
I ended up just throwing the stop and start commands into a script and running it each time I modified my settings.
SQL Server Discovery Tools and Scripts
Each time I start a new contract, the first thing I do is search for all SQL Servers across any network/subnet to which I have access. While the documentation I get is usually paltry or non-existent. When it does exist, it's usually only for the production SQL Servers. Sometimes, most production SQL Servers aren't even documented; one of my clients only had 20 documented servers, and I found over 60 (including SQL Express instances, of course.)
So here I am again, starting a new contract and I'm out looking for SQL Discovery Tools. Ultimately, here are the 5 I ended up finding and using, in no particular order:
Idera SQL Discovery is a really great tool within the Idera SQL Toolbox. That one's good and , which has been in beta for years.
A new tool I discovered this time around is the Microsoft Assessment and Planning Toolkit. Free (as in beer), of course.
Next is a quick lil script that grabs all SQL Servers in Active Directory that contain the word "SQL"
import-module activedirectory
get-adcomputer -filter "Name -like '*SQL*'" |select Name
Next is a more thorough script written by Colin Smith which grabs a list of servers within a text file and probes their services. I created the list with the script above, though I replaced "*SQL*" with "*Server*"
#####################################################################################
## Author: Colin Smith
## Script: Get_Intstance_names.ps1
## Purpose: This script will read in a file of hotnames that has been
## Provided of servers with SQL Server running on them. This
## script will then look at the services on that host to find
## the instance name if the instance is named. If the instance
## is a default instance the script will also report that.
#####################################################################################
$Servers = get-content "C:\servers.txt"
echo "Server, Instance" >> "C:\sqltab.txt"
foreach ($server in $servers)
{
$instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
if (!$?)
{
echo "Failure to connect on $server" >> "C:\failures.txt"
echo "Failure to connect on $server"
}
else
{
$instancenames = @()
foreach ($name in $instances)
{
if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
{
$instancenames += $name.name
}
}
foreach ($iname in $instancenames)
{
echo "$server, $iname" >> "C:\sqltab.txt"
echo "$server, $iname"
}
}
}
Next up, I'd like to investigate the SQL Server Active Directory Helper service and see what that's all about.. then get all these SQL Servers registered.
A Possible Infinite Recompile was Detected – SQL Server Bug with Linked Servers
I recently assisted in migrating a very large system from SQL Server 2005 SP3 to SQL Server 2008 R2 SP1. The actual migration of the database went off without a hitch, but we soon encountered an odd error with the application querying a view over a linked server.
The application was web-based and began throwing an error trying to load a view. After some investigation, we discovered that the view was going across a linked server. Every time the error was encountered, the Windows Event Viewer displayed the following error:
A possible infinite recompile was detected for SQLHANDLE 0x020000004840002608194C0756C4E44307B50A91623589FD, PlanHandle 0x060012004840002640616630050000000000000000000000, starting offset 32, ending offset -1. The last recompile reason was 6.
I took a look at the SQL Text using the supplied handle and matched it to what the web page was calling. The recompile reason of type 6 turned out to be a problem with remote rows changing on the linked server. We attempted different things to fix the issue, and thought maybe a maintenance run would assist. In the end, restarting the main SQL Server instance (where the linked server was configured) resolved the issue.
The system ran great for a week, but then we encountered the same issue the next weekend. After engaging Microsoft, we were pointed to a KB article about a bug with linked servers and synonyms across SQL Versions.
The article can be read here.
Cumulative updates are available for different versions of SQL Server to resolve the issue. However, for those of you who cannot just take down a system to apply the patch, there is a temporary fix.
exec sp_refreshview <view name>
On the main server that is executing the query across the linked server, the views pulling data over the linked server connection must be refreshed AFTER re-indexing or other schema-changing maintenance on the linked server.
We currently have a weekly job in place to refresh our relevant views until we can schedule the CU updates. I only wish I had been able to find more information on this bug when I was experiencing it. It would have saved us 8 hours in the middle of the night trying to figure it out.
Using Bookpedia, SQLite, Book+ and PowerShell to Organize Your e-Book Collection
This setup is so unlikely, I doubt anyone on the 'net will be looking for this solution but: if you're an eBook loving Windows developer who uses a Macbook Pro for your primary workstation, this may appeal to you.
My Macbook Pro runs Windows 7 within Parallels. It's fast and slick and I <3 it. My eBook reader of choice is the Book+ iPad App. I recently switched from GoodReader to Book+ because of its support for Smart Folders. And I back this all up to SugarSync which I picked for the price, privacy policy and Book+ integration.
My eBook organizer is Bookpedia. If you're unfamiliar with this (MacOS only) program, it's basically a really advanced eBook library/organizer. What I love most about it is that it easily populates your eBook's metadata using various web services - including Amazon! - then stores it all to a SQLite database.
My eBook collection looked nice enough in Bookpedia: Initially, I dragged ebooks onto the dock to import, named them properly and populated their metadata using Amazon. The problem was that my files were all over the place and practically unsyncable with SugarSync and ultimately, Book+. Look how awesome:
I'm only a perfectionist when it comes to some things, and ebook organization is one of them. I want my eBooks' naming convention to be as obvious as possible: Amazon's full book title - Publisher - ISBN. I finallly accomplished this using a number of tools and a few hours of coding. Here's my setup:
- Windows 7 in Parallels with Z: mapped to my Home directory on my Mac.
- Amazon developer account
- eBooks that are scattered all over my home directory but organized nicely in BookPedia using Amazon.com data
- SQLite Studio for Windows
- Desired single directory destination: ~/Books in Mac or Z:\Books in Windows
First thing you wanna do is make a backup of your Bookpedia database.
cp ~/Library/Application\ Support/Bookpedia/Database.bookpd ~/Library/Application\ Support/Bookpedia/backup.bookpdNext, load it up into SQLite. Databases -> Add Database -> Z:\Library\Application Support\Bookpedia\backup.bookpd.
CREATE TABLE export (
title TEXT,
filename TEXT,
pubdate TEXT,
publisher TEXT,
isbn TEXT,
asin TEXT
);
insert into export
select a.ztitle, b.zurl, strftime('%Y',date(zreleasedate,'unixepoch'))+31 as pubdate,
a.zpublisher, a.zisbn, a.zasin
from zentry a join zlink b on a.z_pk = b.zentry
--where b.zurl not like '%YOUR MAC DESTINATION DIR%'
This script creates a new table because that's the only way SQLiteStudio exports data into tsv (tab separated values, which I generally prefer to CSVs) format. Once the table is created, I use a right click to export the it to C:\bookpedia.tsv, making sure to check the "column names as first row" option and ensuring the output is ascii encoded.
Now that we've got our TSV file, we'll run it against the PowerShell script below. Copy this code and save it as whatever.ps1
# winbasedir = Parallels mapped drive
$winbasedir = "Z:\"
$windestination = "Z:\books\"
$macusername = "chrissylemaire"
$csvfile = "C:\newstructure.csv"
$macdir = "file:///Users/$macusername/"
# import csv file that contains two columns, directory and filename
$csv = Import-Csv "c:\bookpedia.tsv" -Delimiter "`t"
$newcsv = @()
foreach ($item in $csv)
{
if ($item.filename) {
$source = $item.filename.Replace($macdir,$winbasedir)
$source = $source.Replace("/","\")
$extension = $source.substring($source.length - 4, 4)
$title = $item.title
# Set the file's new name. In my case, I wanted Book Title - Year - Publisher - ISBN
if ($item.pubdate) { $title = $title + " - " + $item.pubdate}
if ($item.publisher) { $title = $title + " - " + $item.publisher}
if ($item.isbn) { $title = $title + " - " + $item.isbn }
# No clue why GetInvalidFilenameChars doesn't do this right...
# Copy-Item will not allow the characters below
$newname = $title.Replace(":"," -")
$newname = $newname.Replace(" "," ")
$newname = $newname.Replace("\","-")
$newname = $newname.Replace("/","-")
$newname = $newname.Replace("?","") # ascii encoding changes weird chars to "?"
$newname = $newname.Replace("[","")
$newname = $newname.Replace("]","")
$newname = $newname.Replace("*","-")
$target = $windestination+$newname+$extension
if ((test-path($source)) -and !(test-path($target)))
{
Copy-Item -path $source -destination $target -whatif
# If the copy succeeded, add it to a CSV file that
# Bookpedia will use to import the new clean structure
if (test-path($target))
{
$newline = New-Object System.Object
$mactarget = $target.Replace($windestination,$macdir+"Books/")
$mactarget = $mactarget.Replace("\","/")
Add-Member -inputobject $newline -name link -value $mactarget -MemberType NoteProperty
Add-Member -inputobject $newline -name title -value $item.title.Replace("?","") -MemberType NoteProperty
Add-Member -inputobject $newline -name isbn -value $item.isbn -MemberType NoteProperty
Add-Member -inputobject $newline -name asin -value $item.asin -MemberType NoteProperty
$newcsv += $newline
}
}
}
}
$newcsv | export-csv $csvfile -noType -Force
mv $csvfile $winbasedir -ForceOne thing to note: I did write this script so that it can be run multiple times. So each time you've got a batch of books that need to be cleaned up, you can just run it again without destroying your library.
So once the script has finished running, the fruits of its labor will look something beautiful like this:
Now that we've got the files copied (I don't recommend moving them, just in case something happens) and the new csv import file has been created, we'll open it in Bookpedia. First, though, I made another backup of this Bookpedia database then deleted my library. Now it's time to import the "clean" collection. Bookpedia -> File -> Import Collection.
The columns will automatically map properly because the script and Bookpedia are awesome like that.
Now Bookpedia has enough information to populate the metadata. As I said before, I chose the Amazon option for this.
So now I use SugarSync to sync up that Books directory and then use Book+ to keep that directory in sync on my iPad. What's super great about Book+, too, are the Smart Folders. Say SugarSync syncs a new PowerShell book, it will automatically appear in my PowerShell Smart Folder. Whaaaat!
Anyone wanna rewrite this in AppleScript for me?
SharePoint/Excel 2010: Relative Hyperlinks and Local Documents
Relatively linking to local documents is much simpler you'd expect. Just save the Excel file and the documents in the same directory and =HYPERLINK(filename,filename). No need for "file://" or any fancy hyperlinking. This method works anywhere -- your local drive, a CD-ROM drive, USB stick, or a networked drive.
If you'd like to add files to a subfolder, the syntax is =HYPERLINK("foldername\" & Name,Name) I've seen a few people around the 'net who also needed to download documents in SharePoint and have them indexed in Excel. Here's how you do it:
SharePoint
Go to the Document Library
Download the documents to your local drive using Explorer View.
Once that's done, select the View and Folder you'd like to export
Ribbon Library Tools -> Library -> Current View, Change to CDR
Don't click any files or folders -> Export to Excel
Open
Excel
Click "Enable" if given a security prompt
Now you're in Excel
Click Data -> Connections -> Highlight owssvr -> Remove
Rename the workbook to Index
Delete the Path and Item Type Columns
Select Column A -> Right Click -> Remove Hyperlinks
Select Column A2 -> Right Click -> Insert Table Columns to the Left
Name that column FileName
Click the "Name" Column to highlight the whole thing
Click "Formulas" in the Ribbon -> Define Name -> It should say Name, Scope is Workbook, Refers to is autofilled: =Index!$B:$B
Highlight all of the empty cells on the side of the Filenames (A2 on down)
Go to the Function section
Paste: =HYPERLINK(Name,Name)
Save to all columns by clicking CTRL-ENTER
Save the Excel file to the same directory where you initially copied your SharePoint directories.
*BONUS: Highlight the Name column and hide it.
Install Project Server 2010 on Windows 7
Google is kind of hiding this French guy's webpage (at least in America) so I thought I'd point it out. You can install Project Server 2010 and few other Office App Servers on Windowes 7 by making two changes: modify the setup\config.xml and adding a faux ServerManagerCMD.exe to your System32 directory.
To compile your own ServerManagerCMD.exe in C#, use the following code:
namespace faux.ServerManagerCmdEmul
{
class Program
{
static void Main(string[] args)
{
System.Environment.ExitCode = 1003;
}
}
}
Or just download the copy I compiled. Using this, I successfully installed Project Server 2010 and I'm running it now. Something odd did happen, however. After installing Project Server 2010 w/SP1, I ran the SharePoint Configuration tool and my SecureStore broke; I kept getting 503 Server Unavailable. Ain't nobody got time for that.
Upon seeing "An exception occurred when trying to issue security token: Could not connect to http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc/actas. TCP error code 10061: No connection could be made because the target machine actively refused it." in Event Viewer, I tried a couple things then just decided to rerun the SharePoint Configuration Tool. Annnnd that worked. Time to start Projecting 
SharePoint 2010 Task List Validation: Comparing Two Columns, Status and % Complete
Did you know that SharePoint supports list validation in addition to column validation? I've seen the option 100 times before but it never clicked before I actually needed to use it. In this case, I needed to make sure that anyone setting a task item Status column to Completed" was also filling setting the % Complete column to 100%. Or, alternatively, if they were setting the % Complete column to 100%, they also set the task Status to Completed.
Need to do the same? Here you geaux:
=OR(IF(AND(Status<>"Completed",[% Complete]<100%),TRUE,FALSE),IF(AND(Status="Completed",[% Complete]=100%),TRUE,FALSE))
Note that this simple formula doesn't care about the percentages of any other status. If you'd like that functionality, check out the solution on this TechNet post.
[Client-side Workaround] SharePoint 2010 Excel Services cannot Automatically Refresh Data when using SharePoint Lists as a Data Source
So far as I can tell, Excel Services is not capable of automatic data refreshes when using SharePoint Lists as a data source. If you're looking for this functionality, you have two choices: PowerPivot (best) or scheduled client-side data refreshing (ghetto).
In order for Excel Services to communicate with other data sources to do data refreshes without your intervention, it relies on SharePoint's SSO Service, the Secure Store. There are three different methods of connecting - Unattended Service Account, Embedded Connections, & External Data Connections - and I tried all three of them in various configurations but kept running into Access Denied errors. I even tried some of that voodoo magic we were taught growing up and it didn't even help.
I finally stopped trying after reaching a dead end on the Excel Services data refresh flowchart then further confirming said dead end within the ULS which informed me that there was an "Unauthorized attempt to access session by user <username>. Session belonged to user <username of user that started the session> (Event ID: 2011)." Seems that SharePoint does not like Excel Services asking to see its lists.
To confirm, I looked up the error in the SharePoint Technical Reference and it said
Excel Services Application maintains individual user sessions. Sessions maintain state related to workbook calculations, parameters that a user sets, interactions that a user has with a live workbook, and data returned from a data source as a result of a data refresh operation. Sessions are maintained per user per workbook, and can contain private data and information. Sessions are available only to the user that started the session. The issue in this case is that a user who did not start a session attempted to access data from that session.
(Emphasis mine)
FFFFFINNNNEEE, Microsoft. Until we get PowerPivot implemented, I'll just make PowerShell do my dirty work. The script below, which you can schedule, assumes the following:
- your workstation will be on most of the time
- you have Excel 2010 Professional installed
- you can use Windows Explorer view of libraries
- versioning is turned on and limited to a certain number of versions
(this makes 24 versions a day) - that you want EVERY Excel file in a library to be refreshed
- you have write permissions to all excel files
- you have access to do so and the files are checked in
So, it goes to the document library, searches recursively for all Excel files, opens them up, checks them out, opens them, refreshes the data connections, saves the file then checks them in with a new version. K, here goes:
For Document Libraries that require checking out files prior to editing them:
# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"
# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application
# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3
$excelfiles = get-childitem $library -recurse -include "*.xls*"
foreach ($file in $excelfiles)
{
$workbookpath = $file.fullname
if ($excel.workbooks.canCheckOut($workbookpath)) {
# open the worksheet and check it out
$excelworkbook = $excel.workbooks.Open($workbookpath)
$excelworkbook = $excel.workbooks.CheckOut($workbookpath)
# Don't ask cuz I don't know (yet). You have to open it again.
$excelworkbook = $excel.workbooks.Open($workbookpath)
# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()
# Save and Check it in
$excelworkbook.Save()
$excelworkbook.CheckInWithVersion()
}
}
$excel.quit()
And this code is for libraries that do not require check-out:
# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"
# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application
# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3
$excelfiles = get-childitem $library -recurse -include "*.xls*"
foreach ($file in $excelfiles)
{
$workbookpath = $file.fullname
# open the worksheet
$excelworkbook = $excel.workbooks.Open($workbookpath)
# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()
# Save and Close
$excelworkbook.Save()
$excelworkbook.Close()
}
$excel.quit()Wanna schedule this script hourly? schtasks /create /tn RefreshData /tr "powershell -noninteractive -nologo -command C:\scripts\refresh.ps1" /sc HOURLY. Don't forget to limit Versions in your library as this script creates 24 versions of each workbook per day.
Also, this script is 10x's faster on Windows 7 if you disable the use of the Web Proxy Autodiscovery Protocol (WPAD). IE -> Tools -> Internet Options -> Connections -> LAN Settings -> Uncheck Automatically Detect Settings.
And finally, if you find that your files are opening Read-only, make sure all instances of Excel are closed, then delete your Microsoft Office cache in %userprofile%\local settings\temporary internet files\content.mso\.
SQL Server 2012 PowerPivot Services: Service Account Keeps Getting Locked Out
Okay, I've had to call Helpdesk an embarrassing 8 times today because my PowerPivot upgrade is going horribly wrong and locking out my AD account that, admittedly, I use as a "service" account. I'm really left with no choice -- I'm only granted one account on this network. Restrictive networks are so restrictive 
For dev machines, I like to let PowerPivot setup my farm. Today, I was upgrading my SQL Server 2008 R2 instance to SQL Server 2012 and ultimately decided to just wipe out my entire 2008 instance and setup a new farm. Everything went well until the Validation area where it would say that my password isn't valid. Here's the workaround that worked for me:
- Running the validation once (which always resulted in a lockout)
- Unlocking the account
- Entering my Service Account Password
- Making the farm pass phrase the same thing
- Validating once more. If it fails...
- Type in the account password again but *click elsewhere* before hitting Validate so that the password box pretends it has additional characters.
- Hit validate, party.
When xp_delete_file doesn’t work as expected…
Whether you use a Maintenance Cleanup Task as part of a SQL 2005 or 2008 Maintenance Plan, or script out your own cleanup t-SQL, you are probably using the xp_delete_file extended procedure. For quite a while, I've occasionally come across old TLog or backup files that were missed. Without questioning it, I would clean them up manually.
Recently I had to disable my 3rd party compression utility due to issues and noticed that all of the SQL instances using that compression utility stopped cleaning up their TLog files. I decided I had to investigate why this was happening. My original assumption with xp_delete_file was that it went to a directory that you specify, looked for the file extension and deleted any matches older than the date. But there is a missing component here.
Let's look at the syntax for xp_delete_file in my particular case...
EXECUTE master.dbo.xp_delete_file
0, -- Either a 0 (Backup File) or 1 (Maintenance Plan Log File)
N'', -- Location of Files. I use a remote SAN storage location.
N'TRN', -- Extension of file to clean up.
N'2012-03-28T09:38:06', -- Date threshold to delete files older than...
1 -- 0 (Current Directory) or 1 (Recursively through subdirectories)
The secret to this procedure is the first argument, the specification of the type of file you are deleting. You may wonder why it would care what kind of file it is, as long as you can match up the extension, but the procedure is actually reading the header of the files to determine that they are indeed backups.
This is why my backups stopped cleaning up after I disabled my compression software. The native ability of SQL Server could not read the header of my TLog files, and therefore could not verify that they are backup files. So it did not delete them. The issues I had occasionally with missed files were with corrupted backup files, usually aborted during writing for one reason or another.
In order to get around the issue, I tried telling the procedure that the files were just logs, but that was not successful in cleaning up my backups either. So the decision has to be made on if this is acceptable behavior, or if I want to go through the trouble of crafting a new method of deletion for all of my SQL installs. At this point, I think I can live with it, even though forcing a deletion would be handy.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Chrissy LeMaire
Tweet to @cl
Brandon Abshire
Categories
- Oracle
- OS X & iDevices
- PowerShell
- Security
- SharePoint
- SQL Server
- VBScript
- Virtualization
- Windows
- Wordpress
