Hyper-V Server virtual machine network connection failure – vm network issues
Hi,
This has been a problem that has haunted me for over 18 months now and finally I have a solution.
The Problem
I have been creating virtual machines and been getting inconsistent success with getting the virtual network card inside the Virtual machine to successfully connect to the network. It will start up with a yellow “Warning” icon showing and when you confirm the IP Addres settings it is an invalid 169.x.x.x address. I am attempting to use DHCP to assign the IP.
I this situtation I have tried the following:
- netsh int ip reset – followed by a reboot.
- stop the VM, remove the network card completely, start the VM and login, shutdown the VM, add the network card back, start the vm and login
- changed the MAC ADDRESS on the virtual card to ensure it is not conflicting on the network.
All of these have failed.
This has been really confusing as I have created 2 Virtual Machines at the same time, from the same sysprep’d .vhd image and one will succeed and one will fail!!!
The only thing that has worked is to assign a static IP address.
Solution
A friendly member of the infrastructure team where I am working fixed it in about 2 seconds.
Apparently due to the way the newtork as been configured we run a virtual Lan, its number so happens to be 100.
So in hyper-v server find this screen:
And change it to something like this:
This sovled my problem.
I don’t know why sometimes it was set correctly and other times not, but this definitely solved my problems.
Let me know if it works for you.
Setup App.Config during .msi install, msi re-installing
The Issue
I wrote a windows form application that deployed via an .msi installer.
The application has an App.Config file which contains a single configuration value. I wanted to prompt the user for this value during the installation wizard and write it to the application.exe.config file as part of the installation process.
I used the built in Setup project in Visual Studio 2010.
The solution
I could write it all here in my post but I have found one that was created earlier:
http://raquila.com/software/configure-app-config-application-settings-during-msi-install/
I essentially implemented the same solution but I wrote my .config file using a StringBuilder and deleted and re-wrote the.config file in the Post install custom action.
The other thing is that I created short cuts to the application by right-clicking on the primary output and chosing “Create Shortcut”, then I copied this to the desktop and users program menu locations.
The problem
I was installing this for “Everyone” and it would work fine for me, but when other people logged in and tried to launch the program the .msi would run and the application.exe.config file was being overwritten with the orginal version that was deployed with the .msi, which does not contain the users input.
The Fix
The problem is complicated and is related to the application short-cut.
It appears that by default the short-cuts created in the msi are “Advertised ShortCuts”.
According to microsoft when an advertised shortcut is activated “.. the installer verifies that all the components in the feature are installed before launching this file”.
This means that if you delete any of the files that were deployed to the installation location then the .msi will attempt to fix them and re-install the missing components, which in my case means that the application.exe.config is overwritten.
You can identify the “Advertised” short cut by looking at the “Properties” of a short-cut and the “Target” will be greyed out.
I found the post below that identifies an easy way to disable advertised shortcuts.
You need to set the “DISABLEADVTSHORTCUTS” property on the .msi. This can be done easily in the installer, I have my custom UI that is getting details from the user for the “Edit1Property” so in the Edit2Property I set these values and keep it invisible.
- Edit2Property: DISABLEADVTSHORTCUTS
- Edit2Value: 1
- Edit2Visible: false
This updates the property table in the .msi and now all your short-cuts will be normal standard shortcuts.
Cheers 🙂
Final Tip: Setting the shortcut icon
Another tip is to set the icon based on the icon embeded in your .exe.
- In the File System Editor, click on the ShortCut and view the properties
- Click on the icon property
- choose browse,
- change from .ico to .exe
- browse to the “Primary-Output for…” and choose OK.
It will show you the icons embeded in your .exe and this way you don’t need to deploy a seperate .ico file.
SQL Server 2008 R2 – Intellisense not working – Visual Studio 2010 SP 1
Hi,
Recently intellisense in the SQL Server management Studio stopped working.
It appears that it was caused by the installation of Visual Studio 2010 SP1.
The issue was raised here:
https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1
It was originally suggested that this could fixed by SQL Server 2008 R2 Cumulative Update #6, but this in turn lead to a crash in Visual Studio 2010 when ever you opened a .sql file in your database projects.
https://connect.microsoft.com/VisualStudio/feedback/details/653180/method-not-found-opening-files-in-database-project
It suggested that this issue has been fixed and will be released in SQL Server 2008 R2 Cumulative Update #7, which was due out in Mid April.
You can install Cumulative Update #6 and in most cases this will get intellisense working in SSMS but it will break Intellisense (on database projects) in VS2010.
So if you are like me, you are between a rock and a hard place, your choice of which environment you prefer to have working.
Lets hope that CU #7 fixes it.
I will update this post with my results after I have applied CU #7.
Cheers…
Update as @ 19th April
Today Microsoft has released Cumulative Update package 7 for SQL Server 2008 R2
After downloading and installing, preliminary results are that the problem is solved.
Intellisense in SSMS seems to be working and I no longer get the crashes in Visual Studio 2010 and Intellisense in Visual Studio 2010 database projects also seem to be working correctly. So, so far so good.
Bootstrap Manifest Generator (2008) – How To Guide v2
I wrote the original Boostrap Manifest Generator (BMG) blog post a few years ago.
It is one of the most heavily visited pages on my site so I thought I should revisit it, and given that the BMG is so buggy it could be useful to have some screen shots.
I will provide 2 concrete examples of how to use the BMG to create a bootstrapper pre-requisite check.
All this is being done on a Windows 7 32-bit (Virtual) machine, with visual studio 2010 installed.
I have also downloaded and installed the BMG2008 which can be downloaded from here
The application that I am going to bootstrap is a small test application called “GuidGenerator”, can you guess what it does?
I will be using a standard setup deployment project to create the installer.
Here goes..
Setup BMG 2008
- Download the BMG2008 from here
- Install the application and note the path that it is installed to as it does not create a short cut, (C:\Program Files\Microsoft\Bootstrapper Manifest Generator for VS2008), and create your own desktop shortcut
- Run the BMG
- Click “Tools :: Edit Machine Paths”
- You may get a message about the SDK not being installed etc etc. Get past the error message and you will need to set the path to your boot strap directory. Do a search on your file system for “bootstrapper” and you will find a folder somewhere like this (note the SDK path)
C:\Program Files\Microsoft SDKs\Windows\v7.0A\Bootstrapper
Enter that into the path and choose OK.You are now ready to create a bootstrapper
Note: This bootstrapper folder is important – read the tips and tricks at the end.
Example 1 – .Net Framework 4.0 Pre-requisite Check
- Download the DotNet framework full install from Microsoft here, and save to a location that you can find, mine is @ c:\Downloads\dotNetFx40_Full_x86_x64.exe
- Run the BMG
- Click “File New”
- Choose “Package Manifest”, choose OK
- Choose “Add Install File”, (button in the top left)
- Browse to the .Net Framework that you downloaded earlier and click OK
- Display Name: enter a name for the bootstrapper, this is the name of the pre-requisite check that will show up in visual studio. I will call mine “My .net Framework 4.0 Check”
- Click back on the “Package” option which is the root of the tree menu on the left and set the Product Name to DotNet4Check – this becomes the name of the folder that the package is created in.
Note: You have to do it in this order as if you set the package name before adding a file it crashes the BMG. - Click into the product code field and it will default with the “Product Name” just set, (otherwise this can throw a warning if left blank).
- System Checks
-
You want to setup a system check that fails if the condition is not met, or alternatively by-pass the install if a condition is met.
- Switch to the system checks tab
- Click on the registry check (2nd icon in the list)
- Property for Result: DotNet4RegCheck (note: this is the name of the check that is used later in the “Install Conditions” tab
- Enter the following registry key: HKLM\Software\Microsoft\NET Framework Setup\NDP\v4\Full
- Registry Value: Version
-
If it fails the check then it will prompt the user that they need to install the pre-requisite.For the .Net Framework I have chosen to do a simple registry check for the .Net Framework version.
- Install Conditions
- Switch to the install conditions tab. This is where we define when our installer should be run.
- Type: ByPassIf
- Property: DotNet4RegCheck – (This is the name entered in the “Property for Result” field in the system check
- Comparison: Ver >=
- Value: 4.0.30319
-
- Exit Codes
Set the following: (Note: I am cheating as I am reverse engineering the .Net bootstrapper that comes with VS2010 to get these exit codes-
0 – Success
3010 – Success with rebootLeave all the other options with their defaults
- Build: Now we can build the package, (button top right)
- Results, note that in my case it succeeded with no errors, and the path contains the folder name that I entered in the product name field.
- Copy: Go to the output directory and copy the “DotNet4Check” folder to “C:\Program Files\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages”
- Setup Project
- My project looks like this:
- Right Cick on the Setup Project and choose properites
- On the properties window Click the “Pre-Requisites” button.
- Deselect anything that is selected that you do not require and choose your check.
- In this case I am going to say – “Download prerequisites from the same location as my application”
- Build the setup solution in release mode.
- Copy the release directory somewhere to a machine missing the pre-requisites. The release directory should look something like this, because we chose to set the download from the same location.
- Run the installer and you will see this…
- My project looks like this:
Example 2 – .Net Framework 4.0 Pre-requisite Check – web download
In this example we want to supply a web download version of the installer so you don’t have to distribute the requisite files with your installer.
This will be identical to the previous example with these changes:
- After adding the file you get the Properties tab. Enter the URL from the tips below in the “HomeSite URL” setting.
I used – http://go.microsoft.com/fwlink/?linkid=182805 for the .Net 4.0 Framework. (remember you must right-click and choose paste). - When setting up the package pre-requisites choose to “Download Pre-requisites from the package vendors website”.
Repeat all the other steps and when you choose “install” from the bootstrap prompt you should get a message that it is downloading.
Tips and Tricks
- The “Bootstrapper” directory is your friend (on my machine “C:\Program Files\Microsoft SDKs\Windows\v7.0A\Bootstrapper”).
If you have access to a VS2010 installed machine and go to the bootstrap directory and into the Packages folder and you will find bootstrappers for 19 different things, including .Net 3.5/4.0 and Client installation versions.
I suspect if you took a copy of the packages folder back to your older VS2005/2008 environment you would get the pre-requisites. - If you don’t intend to setup exit conditions don’t switch to the “Exit Conditions tab”. if you do it will put an empty condition in that throws a warning at build time. The only way to get rid of it is to enter 0 / Success.
-
I suspect for things like .Net you want the user to be able to download the .exe directly. Here are the URL’s provided by the .Net bootstrappers installed with VS2010.
These are the small web installer based versions- .Net Framework 3.5 SP1: http://go.microsoft.com/fwlink/?linkid=118076 download
- .Net Framework 3.5 Client: http://go.microsoft.com/fwlink/?LinkId=119637 download
- .Net Framework 4.0: http://go.microsoft.com/fwlink/?linkid=182805 download
- .Net Framework 4.0 Client: http://go.microsoft.com/fwlink/?linkid=182804 download
- My web install failed because on my Install I happened to click into the “Value” field and it put the following “N/A for ValueExists and ValueNotExists”. This created an “unexpected error” on install.
I went into the product.xml file and set value=”” and it worked.
Reconnect all SQL logins after DB restore
Hi,
Below is a script that I use to reconnect sql logins after a database restore.
It reconnects all users to all databases.
Cheers
Use Master
GO
Set NoCount On
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblDatabases'))
Drop Table #tblDatabases
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#tblUsers'))
Drop Table #tblUsers
Declare @db nvarchar(max)
Declare @usr nvarchar(max)
Declare @sql nvarchar(Max)
Declare @msg nvarchar(200)
Create Table #tblDatabases
(
DBName nvarchar(4000)
)
Create Table #tblUsers
(
UserName nvarchar(4000)
)
Insert Into #tblDatabases
select name from master.dbo.sysdatabases where dbid > 4
Order By name
Select Top 1 @db = DBname from #tblDatabases
While @db is not null
Begin
set @msg = Char(13) + 'Processing ' + @db
RAISERROR(@msg, 0, 0) WITH NOWAIT
set @sql = N'
Use [' + @db + ']
Declare @usr nvarchar(max)
set @usr = null
Truncate Table #tblUsers;
Insert Into #tblUsers SELECT name from sysusers where altuid is null
and name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'');
Select Top 1 @usr = userName from #tblUsers;
While @usr is not null
Begin
print ''fixing user '' + @usr
exec sp_change_users_login ''auto_fix'', @usr
Delete from #tblUsers where username = @usr
Set @usr = null
Select Top 1 @usr = userName from #tblUsers
End'
--print @sql
exec(@sql)
-- main loop
Delete from #tblDatabases where DBName = @db;
Set @db = null
Select Top 1 @db = DBname from #tblDatabases
End
Drop Table #tblDatabases
Drop Table #tblUsers
Set NoCount Off
Restore SQL Server Database – disconnect all users
Hi,
Often when you try to restore a database there will be connections to the DB, which will stop the restore from succeeding.
What I do is use the wizard to configure the restore, and then click the script button, to script the restore to the clipboard, so that I can run it as a TSQL statement.
The trick is you want to disconnect all sessions, so you send the Database into single user mode which will disconnect all sessions. After the restore you then set the database back to normal.
The code would look like
Alter Database YOURDB Set SINGLE_USER with Rollback Immediate GO RESTORE DATABASE [YOURDB] FROM DISK = N'F:\SQLBackup\YOURDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO Alter Database YOURDB Set MULTI_USER GO
The important bits are the Alter Database statements either side of the restore database script.
SQL Server SHRINKFILE not working
As microsoft put in in SQL Server Books online, “A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation” (Note: all quotes are from SQL Server BOL)
The other day I blew away over 100GB of tables and issued the shrinkfile on both the Data and Log files and nothing happened.
Here is the reason…
If you use the SQL management tool and choose to shrink the data file releasing unused space you will get the following command executed:
DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO
When you read about “Truncate Only” it says
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
So depending on how the tables are allocated on disk inside the .mdf file it cannot release anything. Kind of like trying to partition a hard disk, you have to defrag the disk to move all the data to the front of the disk so you can make a big “contiguous” block at the end to repartition.
so really we want to essentially defrag our database file first, and then truncate, enter the “NOTRUNCATE” statement
DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO
Again SQL Server BOL says:
Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change.
Note: This is only applicable to the datafile, it does nothing on the log file
So what you really want to do is:
DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO
DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO
This will essentially defrag your .mdf file and then truncateonly will shrink down to the last allocated extent.
I may be wrong but I think that is what the ShrinkDatabase attempts to do and hence why it appears to take so long.
DBCC SHRINKDATABASE(N'YOURDBNAME')
GO
My recommendation:
- Try to shrink the file first and see what gets released.
- If its not good enough then try ShrinkDatabase.
Note: Shrinking my database file of 100GB took 41minutes on a 16 CPU box with 32GB of memory
Cheers
Windows Server 2008 Image thumbnails not showing
I noticed the other day that on one of my servers image thumbnails were not showing.
This is easy to fix, do the following:
- Click Start and Right-Click on “Computer” and choose “Properties”, (or if like me you like your “Computer” icon on your desktop then just Right-Click on the computer icon on your desktop).
- Under “Tasks” on the left hand list of options click “Advanced System Settings”
- On the “Advanced” tab, in the “Performance” group, click “Settings”
- On the “Visual Effects” tab find and tick the following option “Show thumbnails instead of icons”
- Click Ok
- Click Ok
You should now be able to browse your images as thumbnails again.
Sigh! Thanks for turning that off by default Microsoft!
SSIS Date conversion error going from Oracle to SQL Server
Hi,
The other day I had the task to move a large amount of data from Oracle to SQL Server.
I had chosen to import the data via SSIS, doing a direct copy from one table in oracle to one table in SQL Server.
Everything was fine until I one of my tables crashed, with a data conversion error.
The issue is that the lowest date that SQL Server will accept is “1-Jan-1753”. There was some bad data in Oracle dated year 1049, consequently SQL server did no know how to handle it. How was I going to handle it?
The solution is to use a transformation script. In your data flow task, add a transformation script between your source and destination. When you drop in on the form it pops up a dialogue, leave it default for transform.
On the inputs column choose the date column in question (for the example lets assume its called CreatedDate).
Make sure you change its “UsageType” in the grid from “Read” to “Read-Write”.
Go to the script tab and click “Design Script” enter something like the following code:
Note: This is in vb .net. I think 2008 R2 allows c#, but you should get the idea:
Try
Dim dt As DateTime = Row.CreatedDate()
Dim dtMin As DateTime = System.DateTime.Parse("1753-01-01 00:00:00")
Dim dtMax As DateTime = System.DateTime.Parse("9999-12-31 23:59:59")
If (Row.CreatedDate_IsNull = False) Then
If (dt < dtMin Or dt > dtMax) Then
If (dt < dtMin) Then
Row.CreatedDate() = dtMin
Else
Row.CreatedDate() = dtMax
End If
End If
End If
Catch ex As Exception
Row.CreatedDate_IsNull = True
End Try
This code will find any row that has a date that falls outside the bounds of what SQL server will accept and set it to the Max or Min date values.
Finally some general tips that I learned in attempting to move large amounts of data from Oracle to SQL.
1. Don’t use a linked server connection it is really slow.
2. The Microsoft OLEDB connector for Oracle is pretty fast.
3. When setting up the OLEDB Destination via “Table or View” make sure you choose the “Fast Load” option!! Why you would want it to run slow is beyond me but the “Table or View – fast load” is about 10 to 15 times faster.
Cheers
Dim dt As DateTime = Row.CreatedDate()
Dim dtMin As DateTime = System.DateTime.Parse(“1753-01-01 00:00:00”)
Dim dtMax As DateTime = System.DateTime.Parse(“9999-12-31 23:59:59”)
If (Row.CreatedDate_IsNull = False) Then
If (dt < dtMin Or dt > dtMax) Then
If (dt < dtMin) Then
Row.CreatedDate() = dtMin
Else
Row.CreatedDate() = dtMax
End If
End If
End If
Catch ex As Exception
Row.CreatedDate_IsNull = True
End Try
How to Create a linked server connection from SQL Server to Filemaker Pro 6.0
Ok,
This one may be a bit obscure but I had to do this for data conversion from a legacy system the other day and the FileMaker documentation was pretty light on, but I eventually got it to work.
The problem is that you want to extract data from a FileMaker Pro 6.0 database by creating a linked server connection from SQL Server.
Here are the steps with one Caveat. I was only able to make this work on a 32-bit machine. I had a 64-bit machine, and I could run the 32-bit odbc datasource tool and set-up and test the connection but I could not get my 64-bit version of Sql Management studio to find the 32-bit System DSN I had created, so I jumped over to a 32-bit virtual machine with SQL 2008 on it and everything worked more easily.
1. Install FileMaker Pro
Ensure that you have FileMaker pro installed on the box that you want to create the linked server connection on. This installs the 32-bit filemaker pro driver to allow you to create an ODBC data source.
2. Create on ODBC Datasource System DSN
- Go start :: administrative tools :: Data Source (ODBC)
- Switch to the System DSN tab
- Click Add
- Choose “FileMaker Pro”
- Click Finish
- On the General Tab give the connection a generic name, (can be anything), i used FMPro
- Tick “Use Remote Connection” and add the loop back IP address, 127.0.0.1
- Click OK.
3. Create a linked server connection
- Launch SQL Server Management Studio
- Expand to “Server Objects” :: “Linked Servers”
- Right Click “New Linked Server”
- In the provider choose “Microsoft OLE DB Provider for ODBC Drivers”
- In the following fields, enter “FMPRO”
- Linked Server
- Product Name
- Data Source
- Click OK.
4. Load FileMaker Database
The linked server connection only works via the local client, so for the linked server to work you have to have your Filemaker Pro database loaded and remotely accessible.
- Launch FileMaker Pro
- Open your database, this can be a FM database on a remote server over TCP/IP, connect up as you normally would to access the file.
- Choose File :: Sharing
- Set to Multiple and enable Local and remote connections.
Note: During my linked server sessions, I have sometimes lost the connection or get connection errors. I just close filemaker, and repeat the above steps and it all comes good again.
5. How to query FileMaker
What is the name of the FileMaker Table? select * from what?
Turns out the name of the filemaker database is the table name, so assume your db is called “fmdatabase” you would write the following (Note: I generally use “OpenQuery” statements to run my linked server queries):
select * from openQuery(FMPRO, 'Select * from fmdatabase')
but what if your filemaker database has a space in the name? like “fm database”
well the trick here is documented away deep in the FileMaker help file use `
this is the punctuation on the tilde (~) key.
so your query becomes
select * from openQuery(FMPRO, 'Select * from `fm database`')
Now that should all be clear.
Hope it helped.
Notes to self 