Available Balance
Case study: Simple solution to tackle Collation linked error when executing some
October 4, 2016
0
programming-Case study- Simple solution to tackle Collation linked error when executing someImage: Pixabay.com

Many of the software Programs have installation or deployment routines, which inspite of many advancements and refinements in
the GUIs, are used on all sorts of systems.
Sometimes, when calling the routines to run in a batch mode, through Java or other means, we get this error:

Truncated Logs are shared below and I feel these should be well elabrated and used.

INFO   – MSSQL matched
 INFO   – Parsing [./sql/createTables.sql] :
          Connecting to [email protected]:micros
oft:sqlserver://localhost:1433 as isuser
2016-09-29 18:56:11,367 INFO   – MSSQL parser: Tables found: 31
2016-09-29 18:56:11,378 ERROR  – Fatal error, exitting with [1]
opuslab.util.serverinstaller.SetupException
        at opuslab.util.dbupdate.metadata.MSSQLMetaData.process(MSSQLMetaData.ja
va:52)
        at opuslab.util.dbupdate.DDLCompare.init(DDLCompare.java:67)
        at opuslab.util.dbupdate.ISDBUpdateProcessor.doCompare(ISDBUpdateProcess
or.java:32)
……………………
More will follow this :
at opuslab.util.serverinstaller.Installer.main(Installer.java:165)
To rectify java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Inv
alid object name ‘information_schema.columns’.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
To rectify the same, we can logon to the Sql Server Management studio and connect to the master Db and execute these commands:

ALTER DATABASE isdb_scanners SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE isdb_scanners COLLATE Latin1_General_CI_AS 
go
ALTER DATABASE isdb_scanners SET MULTI_USER
go

The last command is necessary to bring back the Sql Server to the normal: MULTI USER mode else otherwise it can cause problems.
Collation is a way to specify a broad based behavior such as for case sensitivity, width of characters, multi or single byte characters etc.

Source checked to find solution to the problem: =>
http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm
and rest based on own experience

Downgrade SQL Database 2012 to 2008 Or Earlier Versions
January 28, 2015
2
Sql-Server-Downgrade-Advisor

Problem:
Sometimes peoples have requirements; they need to transfer the database from newer versions to earlier versions due to client’s necessity. As I said, you have a database on SQL Server 2012, but your customers have 2008r2 or earlier versions. Migrating the database from older SQL server to the newer version is pretty much easy. All you need a complete backup and restore it to the latest version or release as SQL 2012 have backward compatibilities. However, Downgrading SQL Database 2012 to 2008 Or Earlier Versions is not that simple. In order to overcome, these problems follow the steps below.how to downgrade Sql Server database to lower versions

Solutions:
To the best of my information, I would recommend you two simple ways to complete this task.
• Generate Complete Scripts for both Schema and data
• Generate Script for Schema & Use Import / Export utility.

Some folks are thinking why am I suggesting two methods, what are we supposed to do? So, don’t worry. It depends on best practices and size of your database.

Generating Database Scripts:

This method is very useful and attractive for database developers to transferring their database structure or schema objects like tables, views, stored procedures, functions. However, some of you might know that you can transfer data as well. Let’s start.

SQL Server Generate Scripts

Generate Scripts In Sql Server for transferring database.

Login to your SQL Server Instance where you have hosted your database. Right click on your database that you want to transfer, go to tasks > generate scripts as you can see in the above picture.

Generate Scripts For Database Objects

Generate Scripts For Database Objects

Once you can create script, you will be redirected to this wizard. Then press ‘Next ‘ button to proceed further. There are four steps to complete this wizard.

 

Select Database object to script

Select Database object to script

On this screen, you will have the option to choose objects. By default, all objects of the entire database will be selected. Although you can select your desire objects such as tables, views and etc. if you do not have an idea then go with the default, like I did on this screen and ‘next ‘.

Specify How Scripts should be saved or published

Specify How Scripts should be saved or published In SQL Server

Next Screen is “set scripting options”. Now don’t go with next, this time you have to navigate to Advance options because we have to change some parameters that are necessary for transferring data from newer to lower versions. So, go to advanced and here have to the next screen.

Advance Scripting Versions

Select Script for server versions.

In advance screen, the first parameter, which you need to set, is ” Script for Server Version”, as you can see, choose whatever version which fulfils your requirements. In my case, I’ll select SQL Server 2008 R2.

Second parameter, which you need to select, is “Types of data to script”. There are three option:
• Data Only
• Schema and data
• Schema Only
Let me clarify all three types, benefits and drawbacks.

Data Only:

Data only is an excellent option given by SQL server. Quite attractive if you are moving your data from one place to another via SQL file. This method will extract all your data into .sql file command. The only drawback is that the .sql file becomes heavier with respect to the data. If you are moving small size database under couple of GBs then, it will be a good option to transfer. However, if you are dealing with more than this then, it will be chaos for you.

Schema and data:

Schema and data just mean you can script both schema and data in single .sql file. Condition applies as I said earlier if you are dealing with big data like more than couple of Gbs then it will take time and space to convert this database structure and data into .sql file.

Schema Only:

As describe Schema only. You can script all your database structure means tables, views, stored procedures, functions and etc. into SQL file.
I would recommend you to script schema and data only if you are dealing with the couple of Gbs. You can also script Schema only and data only into two different files . It will allow you to maintain your DDL and DML separately. As of now I will go with Schema and data then press OK.

Provide file name and path to save your customized database scripts. Click Next for final summary.

Provide file name and path to save your customized database scripts. Click Next for final summary.

Final Summary of Generate Scripts

Database has been extracted in SQL File.

After couple of next, you will be here at final stage. As you can see your entire database schema with structures have successfully extracted into SQL file. Now our half work has completed. We have exported the database with data. Now let start to execute this SQL file on SQL Server 2008 R2.