Archive

Archive for March, 2011

SQL Server 2008 R2 – Intellisense not working – Visual Studio 2010 SP 1

18 March, 2011 9 comments

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.

Categories: SQL Server

Bootstrap Manifest Generator (2008) – How To Guide v2

3 March, 2011 6 comments

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

  1. Download the BMG2008 from here
  2. 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
  3. Run the BMG
  4. Click “Tools :: Edit Machine Paths”
  5. 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

  1. 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
  2. Run the BMG
  3. Click “File New”
  4. Choose “Package Manifest”, choose OK
  5. Choose “Add Install File”, (button in the top left)

  6. Browse to the .Net Framework that you downloaded earlier and click OK
  7. 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”
  8. 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.
  9. 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).
  10. 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.
      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.

    • 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
  11. 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
  12. 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 reboot

    Leave all the other options with their defaults

  13. Build: Now we can build the package, (button top right)
  14. 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.

  15. Copy: Go to the output directory and copy the “DotNet4Check” folder to “C:\Program Files\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages”
  16. Setup Project
    1. My project looks like this:

    2. Right Cick on the Setup Project and choose properites
    3. On the properties window Click the “Pre-Requisites” button.
    4. Deselect anything that is selected that you do not require and choose your check.

    5. In this case I am going to say – “Download prerequisites from the same location as my application”
    6. Build the setup solution in release mode.
    7. 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.

    8. Run the installer and you will see 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:

  1. 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).

  2. 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

  • 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.
Categories: c#, Code

Reconnect all SQL logins after DB restore

2 March, 2011 Leave a comment

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

Categories: SQL Server