Notes to self

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.

Update as of Nov 2011

As someone has pointed out the release of SQL Server 2008 R2 SP1 is supposed to fix this issue, (as it contains all the rolled up cumulative updates).

I personally have had mixed results with some machines working again and others having “Intellisense” working in SQL management Studio but remaining broken in Visual Studio 2010. My main work Development environment is still broken in this way and if I ever get it working I will udpate you on what I had to do, but for now I am living with only having intellisense in the SQL management studio.

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

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.

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:

1. Try to shrink the file first and see what gets released.
2. 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

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

Many moons before reporting services existed, I was involved in writing a report architecture which was based upon SQL server and MS Excel.

In short sql queries were executed and data dumped into excel and then formatted via embedded macros.

In Excel a Soft Return is done by pressing ‘Alt + Enter’. In some cases you may return a 1:Many relationship as a single string and want to delimit it with a carriage return between each element.

To get this to work in Excel you need to use the Ascii code for {LF} which is Char(10).

So that way your comma delimited string can appear with newlines, and turn on wrap in the column and the data formats nicely in excel.

Probably not useful for anyone else, but I needed to do this again today and do you think I could remember the Ascii code….

I was attempting to uninstall the SQL Server 2008 R2 Nov CTP and I found the SQL server 2008 R2 (x64 in my case) uninstaller and everything appeared to go to plan, but….

When it had finished uninstalling the Icon still appeared in my list of things to uninstall. So I attempted to uninstall again and chose everything that was there and uninstalled that. Again it told me success but the icon remained.

So next I thought I will just uninstall all the other 2008 R2 items in the list, starting with something like native client, and it told me:

Warning 26002: The following products on SQL Server 2008 Native Client
– SQL Server 2008 R2 Database Engine Shared
– SQL Server 2008 R2 Common Files

I understand dependencies, so I thought fine, I will just uninstall in reverse order of precedence until I could succesfully uninstall everything. The stupid thing was I could not find these items in the list of software installed on my computer.

Firstly note the name, is not Microsoft SQL Server … so check down your list of items to see if it is not just listed at the bottom of the page in the “S” section not in “M” where all the other Microsoft SQL Server entries are.. (grrrr <– angry @ microsoft sound).

So if you still cant find it do this, go to this registry key
HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionUninstall

Assume I was looking to unintall the "SQL Server 2008 R2 Database Engine Shared",

• Search for SQL Server 2008 keys in the uninstall registry.
• Keep searching until you find the Database engine shared install location
• Note the “Install source”, copy the path and access that path via Start :: Run
• Luckily I still had the install media on my machine, (it was in my downloads directory), and opening the path showed a directory with the .msi file. If you are not as lucky as me you will need to find the installation media and go to this relative path location
• Right click on the msi and choose uninstall

This threw a little error,which I ignored and it then proceeded to successfully uninstall the item

Repeat this process until you have successfully uninstalled all the items, that it is complaining about and then go back and start uninstalling from the uninstall UI those other SQL Server 2008 R2 files.

Cheers

VN:F [1.9.17_1161]
please wait...
Rating: 8.0/10 (4 votes cast)

Sometimes when attempting to uninstall SQL Server, “Setup Support Rules” performs a check and fails on “Restart Computer” with the following error;

“A computer restart is required. You must restart this computer before installing SQL Server”

So you restart your computer and try again, same error… Doh!

So now it turns out there is a bad registry entry, so if you have restarted your computer and there are actually no pending restart operations that you are aware of the do the following

1. Leave the Setup Support Rules UI open if you are already in there
2. Open Regedit
3. Go to the following registry key”HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager”
4. Delete the “PendingFileRenameOperations”
5. Click “Re-Run” in Setup Support Rules launch the uninstall again

So now you will have passed the restart computer check and you can continue and get rid of that pesky old version of SQL Server.

Below is a nice little script to output the various formats displayed by sql server when using convert(varchar, getdate(), n).

It just outputs the current date in the various formats so you can quickly find the one your after

Declare @date datetime
Declare @i int
set @date = getdate()
set @i = 1
<br>
while(@i &lt;= 31)
begin
&nbsp;&nbsp;if(@i not in (15, 16, 17, 18, 19, 22, 23, 24,  25, 28, 29))
&nbsp;&nbsp;begin<br>  
&nbsp;&nbsp;if @i &lt; 22
&nbsp;&nbsp;&nbsp;&nbsp;print  convert(varchar, @i) + &#39;: &#39; + convert(varchar, @date, @i)<br>    
&nbsp;&nbsp;&nbsp;&nbsp;print  convert(varchar, @i+100) + ': ' + convert(varchar, @date, @i+100)<br>  
&nbsp;&nbsp;end
&nbsp;&nbsp;set @i = @i + 1
end 

Hope it helps

If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors.

The following example is trying to divide last weeks total hours worked  

 

 

 

 

The following errors:

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
  0,
  Sum(Fields!LWTotal.Value)/Sum(Fields!LWWorkingDays.Value) 
 )

The Problem

Even though the number of days = 0 and it should evaluate to the true condition of 0, SSRS still evaluates the false result, and the false result still throws a divide by zero error because it is evaluating:
Sum(Fields!LWTotal.Value) / 0

The solution:
We need to try to make the false result not error when working days = 0.
The false result

Sum(Fields!LWTotal.Value) / Sum(Fields!LWWorkingDays.Value)

Needs to change to the following:

Sum(Fields!LWTotal.Value) / 
IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value))

Why does this work?
When working days = 0 the result is going to be 0. So we don’t really care what the false result is going to be we just don’t want it to error. So the new IIF statement on the denominator returns 1 when the working days are zero.

This evaluates to:

Sum(Fields!LWTotal.Value) / 1 

and this does not error.

Final Code Example

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
    0,
    Sum(Fields!LWTotal.Value) / 
    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) / 60
 )

In short by converting the zero to a 1, it stops the false result from erroring and allows the calculation to work correctly when it is evaluated.

That is a work around for a problem that should never of existed, good one Microsoft..