Archive

Archive for the ‘SQL Server’ Category

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

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

Restore SQL Server Database – disconnect all users

18 January, 2011 Leave a comment

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.

Categories: SQL Server

SQL Server SHRINKFILE not working

5 January, 2011 Leave a comment

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

Categories: SQL Server

SSIS Date conversion error going from Oracle to SQL Server

19 December, 2010 1 comment

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

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
Categories: SQL Server

SQL Server Excel and Soft Returns

29 July, 2010 Leave a comment

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

Categories: SQL Server

SQL Server 2008 R2 Nov CTP – Uninstall issues

5 May, 2010 4 comments

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_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

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

Categories: SQL Server, Uncategorized