jusher
Posts: 195
Joined: Thu Dec 22, 2011 12:00 pm

Problem with SQL Server script

Hi,

I have a script that runs fine stand-alone in SQL Server 2008 R2. However, when the same script is used in AI it givs the following error in the log: -
MSI (s) (A8:24) [14:19:04:480]: Invoking remote custom action. DLL: C:\Windows\Installer\MSIEB93.tmp, Entrypoint: OnSqlConfig
AI_SqlConfig: Connection: LBDO Server Script: Config
AI_SqlConfig: Connection: LBDO Server Script: Config
AI_SqlConfig: Connection: LBDO Server Script: Config
AI_SqlConfig: Connection: LBDO Server Script: Config
AI_SqlConfig: Connection: LBDO Server Script: Setup_1
AI_SqlConfig: Connection: LBDO Server Script: Setup_1
[SQLExecDirect] {SQL_ERROR}: {SQL_STATE: 42000} [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
CustomAction AI_SqlConfig returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)
Action ended 14:20:33: InstallExecute. Return value 3.
MSI (s) (A8:14) [14:20:33:401]: User policy value 'DisableRollback' is 0

Any suggestions on what may be causing this?

Regards,
John
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Problem with SQL Server script

Hello John,

We've received your e-mail regarding this issue and we'll start investigate this. Please give us some time to investigate this and we'll get back to you as soon as we have a conclusive answer.

Thank you for your patience and understanding.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
jusher
Posts: 195
Joined: Thu Dec 22, 2011 12:00 pm

Re: Problem with SQL Server script

ok no worries.

John
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Problem with SQL Server script

Hello John,

I've talked with our development team and this may happen (as it is written in the error message) due to a collation conflict. When establishing a connection with the target SQL server our SQL module use the default ODBC driver collation if your database doesn't already exist on target machine. If the database exists on target machine, then it should use your database collation. However, if the containing tables within your database have different collations, then you can get a collation conflict especially when executing string processing operation within your SQL script.

What you can do is to try to debug your SQL script and find what statement generates the above error. For example, you can break you script in multiple parts and try to run it within Advanced Installer. Then, you can try to set a collation accordingly for the related SQL statement. Also, you can try to set what collation to be used by your SQL script within the SQL connection string parameters (using a custom SQL connection in our "SQL Scripts" page).

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
jusher
Posts: 195
Joined: Thu Dec 22, 2011 12:00 pm

Re: Problem with SQL Server script

Hi Daniel,

This is exactly what I did and changed the variables to nvarchar(255) instead of varchar(255), which made the problem go away. This was on the line: -
SET @UPDATE1 ='Update configitems set _value=''ATODBC'' where _key=''NextDbSpecies'' Update configitems set _value=''' + ltrim(rtrim(@MASTERODBC)) + ''' where _key=''NextDbDSN'' Update configitems set _value='''+ ltrim(rtrim(@DBN)) +''' where _key=''NextDbDBN'''

It should default to database collation but does not see to work with varchar.

Regards,
John

Return to “Common Problems”