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 ;)

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