MS Excel friendlier to MS Access than to SQL 2005

Ever come across this error while trying to import data into SQL Server 2005 from Excel?

"Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

I came across this and googled with no luck. I realized I had faced this same situation some time earlier and my rescue was MS Access. It seems MS Excel is more friendlier to MS Access while MS SQL is less forgiving. If you run across this issue the simplest solution is to import the data in MS Access and then Import the data to MS SQL from MS Access. I agree this is an extra step but its better than spending time fixing the Excel import in MS SQL when you really don't want to be spending time fixing something which you may not want to use quite often.

Having said the above, there may be better ways of resolving this. If you know of any pls do leave a comment with the solution or link.

Happy Programming...

Backup all Databases on SQL Server

You work from 9 am to 6 pm and around 4 someone comes up to you and says "We got a problem. We need the backup of all 50 databases on our server and I want you to do that before you leave for the day." What is your reaction to that? And guess what, you are not even a Database Administrator.

All you know is this:

1. Open SQL Server Management Studio.
2. Connect to a Database Engine.
3. Right click on the database you want to back up.
4. Select Tasks.
5. Select Back Up.
6. Add a backup destination.
7. Specify the name of the backup.
8. Hit "OK".

Wow that was easy :)
But then you realise, you have to repeat this process for FIFTY databases so 50 times 8 Steps = 400 steps.. WOW.. now this is going to take quite a while. Say goodbye to your evening plans cause your boss just made you HIS plan :D

Then you think.. maybe someone can help me automate this. Thats when Paul Hayman comes to rescue. He has made this cool script which will backup all the databases on a SQL instance. This script saved me hours of work. No more clicking around.. just run the scrips. Following is the script, hope it helps:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in ('Master','tempdb','model')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)    
    set @DBFileName = datename(dw, getdate()) + ' - ' + 
                       replace(replace(@DBName,':','_'),'','_')

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:db backup' + 
        @DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' + 
        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

So there you are, just a single script to take care of all that and now you can go ahead with your evening plans ;)

Going crazy with "unable to start debugging on web server" in Visual Studio 2005?

So ever tried to setup visual studio 2005 on a new machine and all you want to do is just debug a application in IIS? I recently attemped doing this and got the friendly error message: unable to start debugging on web server. the web server is not configured correctly vs2005 Does not help does it? The message too generic and the thing(s) that caused it unknown So after googling for over 3 hrs and trying number of proposed solution, I came across a forum where Chandan Gowda had the right solution for it. Here is the post

1. iisreset -stop
2. Goto C:\Windows\Microsoft.NET\Framework\v2.0.50727
3. aspnet_regiis -ua
4. aspnet regiis -i
5. aspnet_regiis -ga machinename\aspnet
6. goto C:\Windows\Microsoft.NET\Framework\v1.1.4322
7. aspnet_regiis -i
8. iisreset -start

Solution:

• Uninstall all versions of .Net frameworks using aspnet_regiis -ua
• Re register the version of .Net framework using aspnet_regiis -i
• Add machine's aspnet user to the web operator group using aspnet_regiis -ga machinename\aspnet
• And you are done.

The Problem: I installed IIS after installing Visual Studio. I did run aspnet_regiis -i to re register .net but did not add the aspnet user to the web operator group.

Always Remember: Make sure IIS is installed before you install any version of Visual Studio Hope this information will save someones time and help reduce the frustration which I went through.

Lets learn from my mistakes

How many times have you spend hours to fix a bug (technical jargon) and you know that it is something small you have missed that's causing this to fail only to realise later that it was some small thing you missed. Well, now that you have the solution for the small error you made, can you remember it for a later time? say an year or maybe even few months. Its not always that you remember the solution you implemented the last time you faced such an issue. So here I am trying to help you learn from MY mistakes :)

I will keep adding more as and when I come across more issues

• 1
  IE behaves differently from Firefox when I put a rowspan
  Appaerntly IE disregards 'rowspan' as it expects the 's' in rowspan to be in upper case.
  So something that would work on both IE and Firefox is 'rowSpan'
• 2
  When I put html in blogger html editor, for some reason it puts huge amount of spaces/breaks in it
  Convert line breaks is set to 'Yes'
  Go to Settings >> Formatting and make sure 'Convert line breaks' is set to 'No'
• 3
  When I try to use xml.query in SQL for an attribute i keep getting the error "XQuery [query()]: Attribute may not appear outside of an element"
  There are certain limitations with accessing XML data type in SQL and the errors thrown are not really helpful in identifying what is the real issue. There are different ways to access value from a node and attribute.
  Use data() instead. I looked up on google and Matija Lah's post helped me resolve this. To access attributes use

  Select .query('data(@PubDate)').value('.','datetime') as PublishDate