DQS Installer fails with error "Script process returned unexpected exit code: '1'."

Question

Hi,

I'm having a problem trying to install DQS when runnning DQSInstaller.exe for Sql Server 2012 RC0. It aborts reporting that the "Script process returned unexpected exit code: '1'"

By running each line separately from recreate_schema.bat I can see that the line that's failing is

    SQLCMD -b -a32767 -S"%DQS_DB_HOST%" -d"%DQS_DB%" -idb\populate_default_kbs.sql

and this is because there is no populate_default_kbs.sql in the db folder. Here's the output from the extraction

[15/02/2012 14:16:07] Extracting script: db\create_core_db_objects.sql
[15/02/2012 14:16:07] Extracting script: db\create_logic_db_objects.sql
[15/02/2012 14:16:07] Extracting script: db\create_transient_db_objects.sql
[15/02/2012 14:16:07] Extracting script: db\static_data.sql
[15/02/2012 14:16:07] Extracting script: db\drop_dq_databases.sql
[15/02/2012 14:16:07] Extracting script: helper\DeleteSchemaDs.sql
[15/02/2012 14:16:07] Extracting script: sql\drop_all_assemblies.sql
[15/02/2012 14:16:07] Extracting script: sql\create_databases.sql
[15/02/2012 14:16:07] Extracting script: sql\drop_databases.sql
[15/02/2012 14:16:07] Extracting script: sql\drop_all_tables.sql
[15/02/2012 14:16:07] Extracting script: sql\drop_database_properties.sql
[15/02/2012 14:16:07] Extracting script: sql\master_create.sql
[15/02/2012 14:16:07] Extracting script: sql\master_recreate.sql
[15/02/2012 14:16:07] Extracting script: sql\register_assemblies.sql
[15/02/2012 14:16:07] Extracting script: sql\register_assemblies_tsql.sql
[15/02/2012 14:16:07] Extracting script: sql\register_dq_assemblies.sql
[15/02/2012 14:16:07] Extracting script: sql\create_service_broker_objects.sql
[15/02/2012 14:16:07] Extracting script: sql\drop_service_broker_objects.sql
[15/02/2012 14:16:07] Extracting script: recreate_schema.bat
[15/02/2012 14:16:07] Extracting script: drop_databases.cmd
[15/02/2012 14:16:07] Extracting script: register_dlls.cmd
[15/02/2012 14:16:07] Extracting script: unregister_dlls.cmd
[15/02/2012 14:16:07] Extracting script: assembly_paths_retail.txt
[15/02/2012 14:16:07] Extracting script: DQS_Data.dqs
[15/02/2012 14:16:07] Extracting script: DefaultKbs.xml
[15/02/2012 14:16:07] Total scripts extracted: 25

So, I'm not sure why that file wasn't extracted and if it wasn't then whether or not it's needed.

When I try to launch the Data Quality Client I get the error "The SQL Server instance that you are trying to connect to does not include an installation of Data Quality Services. To finalize the installation of Data Quality Services please run the DQS installer script"

Any help would be much appreciated and thanks in advance,

Steve

Answer 1

Similar thread... http://social.msdn.microsoft.com/Forums/en-US/sqldataqualityservices/thread/d665822b-b672-40d1-8bdc-334a83333120

By chance do you have a case-sensitive or accent-sensitive collation?

I'll check it out this file further when I get to my server tomorrow.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 2

Hi Jason, yep similar thread but that's not failing for the same reason. I also notice that the other thread does include an extraction of the populate_default_kbs.sql which wasn't in my extraction.

The collation is not case-sensitive but it is accent sensitive, it's set to SQL_Latin1_General_CP1_CI_AS.

Thanks Jason,

Steve.

Answer 3

Can you find the DQS_installer log and let's take a close look... here is the location of the log output:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DQS_install.txt

I checked my RC0 and I confirm I also do not have this file in the %temp%<rand>\db\populate_default_kbs.sql

In my recreate_schema.bat it does mention that file (bug maybe). That file doesn't run on first execution as far as I can tell though, so it seems potentially benign. So that probably is not the cause of the original failure. I imagine this will get an error and end the batch early whenever it does run. 

When you tested running the script and noticed the missing file, did you give the exact switches for recreate_schema.bat that were shown in the DQS_Installer log output?

RecreateSchema.bat is used on secondary calls to DQSInstaller. You can delete DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA and run the DQSInstaller so it will not need to do recreate schema at all.

{

    SQLCMD -b -a32767 -S"%DQS_DB_HOST%" -d"%DQS_DB%" -idb\populate_default_kbs.sql
    if ERRORLEVEL 1 goto error

}

However even that doesn't cause the failure for me, so I'm not sure if the missing file is benign, or maybe it affects non-default collations perhaps.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 4

Hi Jason,

Thanks for your detailed reply. Yes, you're right that actually the non-existance of the file populate_default_kbs.sql is not the cause of the error because the path of the code that would cause it to run isn't being invoked based on the parameters being supplied to recreate_schema.bat.

I've deleted the DQS_MAIN, DQS_PROJECTS and DQS_STAGING_DATA databases and tried again. I still get the error as follows:

...

[21/02/2012 12:12:33] Action 'Load Installation Scripts' finished successfully.
[21/02/2012 12:12:33] Executing action: Create data quality schema.
[21/02/2012 12:12:33] Script: 'recreate_schema.bat WPRI1225 DQS SQL_Latin1_General_CP1_CI_AS'
...

[21/02/2012 12:12:36] ---[Reference Data Add provider schema]---
[21/02/2012 12:12:36] ---[Add Special characters for term normalization]---
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] (1 rows affected)
[21/02/2012 12:12:36]
[21/02/2012 12:12:36] Script process returned unexpected exit code: '1'.
[21/02/2012 12:12:36] Action 'Create data quality schema' finished with errors, aborting installation.

Running "recreate_schema.bat WPRI1225 DQS SQL_Latin1_General_CP1_CI_AS" in isolation doesn't show an error so I'm unsure where to look next. Do you have any ideas?

Thanks,

Steve.

Answer 5

Maybe this next sql script is failing. It checks that the 3 databases exist, and the certificates are present in them.

SQLCMD -b -a32767 -S"%DQS_DB_HOST%" -dmaster -isql\create_databases.sql

Normally it prints log output of the following messages next from that script.
{

PRINT 'Recreating DQS databases ($(DQS_DB), $(DQS_PROJECTS_DB), $(DQS_DATA_DB)) since they have wrong db owner or do not have the DQS Certificate'

or

PRINT 'Skipping creation of DQS databases ($(DQS_DB), $(DQS_PROJECTS_DB), $(DQS_DATA_DB)) - databases already exist.'

}

Can you please run SQL profiler with events All Errors & Warning Category +  SQL:Batch Starting, SQL:BatchCompleted, SQL:Stmt Starting, SQL:Stmt Completed, RPC:Starting, RPC:Completed, SP:StmtStarting, SP:StmtCompleted

You had success up to this below point. There are the nested calls leading up to what is shown in your log.

{

recreate_schema.bat.
--> SQLCMD -b -a32767 -S"%DQS_DB_HOST%" -d"%DQS_DB%" -isql\master_recreate.sql
--> --> :r sql\master_create.sql
--> --> --> :r db\static_data.sql
--> --> --> --> [static_data.sql]
PRINT '---[Add Special characters for term normalization]---'
GO
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('''' ,'')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (':' ,' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (',' ,' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('-' ,' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('.' ,' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('"' ,' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (';' ,' ')

}

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 6

Disregard the note about "SQLCMD -b -a32767 -S"%DQS_DB_HOST%" -dmaster -isql\create_databases.sql" it was incorrect.

1. SQL Profiler might show something we can't see in the log.

The usual events are

All Errors & Warning Category

SQL:Batch Starting, SQL:BatchCompleted, SQL:Stmt Starting, SQL:Stmt Completed,

RPC:Starting, RPC:Completed, SP:StmtStarting, SP:StmtCompleted

2. Reviewing the scripts again:

If db\static_data.sql is running fine, its hard to imagine what is failing because there isn't any work after that point before it writes "Action 'Create data quality schema' finished successfully." or "Action 'Create data quality schema' finished with errors, aborting installation."

Maybe there is a false positive being raised prior and it keeps running, but the errorlevel in the batch is not reset for a warning. Do you see any other warnins or error from above that segment in the DQS_Install.log besides the things like "(1 rows affected)"

The parent caller sql\master_create.sql also doesn't do any more work after that logged "Add special characters for term normalization" message your log shows, so theoretically the work was all done at that point in time.

{

-- Create core tables and configuration data
:r db\create_core_db_objects.sql
:r db\static_data.sql
GO

}

3. Maybe there is something going on with SQLCMD.exe itself. When the scripts are running, can you take a look at task manager. On the Processes tab,  go to View menu > select column. Add in the column "Command Line"

When it runs make sure its picking up the SQL Server 2012 SQLCMD.exe process from the 110\Tools\Binn folder, and not the SQLCMD from another instance or another location.

It should be something like this:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 7

Thanks again Jason,

1. In profiler I can see the following statements are executed:

INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('''' , '')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (':' , ' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (',' , ' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('-' , ' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('.' , ' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES ('"' , ' ')
INSERT INTO [A_SPECIAL_CHARACTER_RULE] ([FROM_CHARACTER], [TO_CHARACTER]) VALUES (';' , ' ')
go
exec sp_reset_connection
go
SELECT NAME FROM sys.databases WHERE NAME = 'DQS_MAIN' OR NAME = 'DQS_PROJECTS'
go
DROP DATABASE DQS_MAIN
go

And there is also an Exception logged just after the DROP DATABASE DQS_MAIN statement:

Invalid object name 'msdb.dbo.dbm_monitor_alerts'.

But perhaps that's not relevant because I guess it's rolling back at that point.

There are other errors further up in the profiler log but they are all along the lines of User Error Message "Changed database context to 'DQS_PROJECTS'" or "Missing Column Statistics" or "Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install" which presumably aren't the cause of the problem.

2. In the log file there is this entry:

[21/02/2012 12:12:33] Total scripts extracted: 25
[21/02/2012 12:12:33] Action 'Load Installation Scripts' finished successfully.
[21/02/2012 12:12:33] Executing action: Create data quality schema.
[21/02/2012 12:12:33] Script: 'recreate_schema.bat WPRI1225 DQS SQL_Latin1_General_CP1_CI_AS'
[21/02/2012 12:12:33] The system cannot find the path specified.
[21/02/2012 12:12:33] .  Trying to connect using Windows Authentication and db name...
[21/02/2012 12:12:33] Changed database context to 'master'.

If that is being reported from recreate_schema.bat then perhaps ERRORLEVEL is set then returning the false positive. Do you think that this is relevant? When you tried it did you also get that message?

3. When the script is running to create the databases and data unfortunately the Command Line isn't shown. However, I've searched for all instances of SqlCmd.exe and found only one which is the version from the folder C:\Program Files\Microsoft SQL Server\110\Tools\Binn dated 5 Nov 2011.

Thanks again,

Steve

Answer 8

1. Regarding profiler, I agree with your assessment about the Inserts and the ones you ignored seem benign.

2. I put my bet that its the error you bolded.

[21/02/2012 12:12:33] The system cannot find the path specified.

Maybe a false negative, but maybe indicative of something specific only on certain computers.

Process Monitor is one tool that can help us track down "path not found" kinds of errors. However, before we do that, we can do some simpler debugging now that you have it isolated to that one batch file.

On my machine, I don't see that system cannot find the path specified error ...

[11/28/2011 8:11:12 PM] Script: 'recreate_schema.bat SERVERNAME DQS SQL_Latin1_General_CP1_CI_AS'
[11/28/2011 8:11:13 PM] .  Trying to connect using Windows Authentication and db name...
[11/28/2011 8:11:14 PM] Changed database context to 'master'.
[11/28/2011 8:11:29 PM] Creating DQS databases (DQS_MAIN, DQS_PROJECTS, DQS_STAGING_DATA)...

Experiment 1:

Do you get that same "cannot find the path specified" error if you run the recreate_schema.bat with that same syntax:

      recreate_schema.bat WPRI1225 DQS SQL_Latin1_General_CP1_CI_AS

Experiment 2:

Are you comfortable to play around with the recreate_schema.bat and add some extra lines in there to display the error level ... put some of these between each code block.

@echo Errorlevel= %ERRORLEVEL%

The unfound path is likely happening at the top before the line: @echo .  Trying to connect using Windows Authentication and db name...

Then run it manually using the same step as Experiment 1 to see if we can find which part is logging the path not found error.

Thanks for your willingness to experiment and troubleshoot over the forum -Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 9

Hi Jason,

Unfortunately (?!) the bat file works without an error when run in isolation. And because dqsInstaller.exe uses an embedded resource to recreate the file each time it's run, it's not easy to echo the ERRORLEVEL. Perhaps the only way is to use process monitor to try to identify which file it looks for but can't find?

Alternatively, what about editing the resource in DQSInstaller to echo the error level?

Thanks again,

Steve.

Answer 10

Do you have the tools (Visual Studio, etc) to edit the embedded resource? You can try it if you do. I haven't tried before to know if its doable.

It could be something about finding SQLCMD.exe (is it in the environment path system variable?). From a command prompt do SET PATH and see if the string printed shows the folder C:\Program Files\Microsoft SQL Server\110\Tools\Binn. If not you can add that folder to the path and see if the error goes away.

Process Monitor may show us the not found. http://technet.microsoft.com/en-us/sysinternals/bb896645  There will be a high noise ratio, so its finding the needle in the haystack using that tool.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 11

Hi Jason, I've been unable to edit the bat file contained within the exe however with some crafty use of the Pause key I've been able to edit the file recreate_schema.bat before it runs and after it is unpacked.

I added the echo of the errorlevel as you suggested and could see that it was zero. So tried it all again but this time removed the @echo off from the top of the file and added "@echo exit label - no error" just before the ENDLOCAL statement at the end of the file. Here's the output:

[24/02/2012 16:10:36] C:\Users\curtis\AppData\Local\Temp\qupg2sk4.bde>if ERRORLEVEL 1 goto error
[24/02/2012 16:10:36]
[24/02/2012 16:10:36] C:\Users\curtis\AppData\Local\Temp\qupg2sk4.bde>if "nodev" == "dev" (
[24/02/2012 16:10:36] SQLCMD -b -a32767 -S"WPRI1225" -d"DQS_MAIN" -Q"CREATE SCHEMA [KnowledgeBase1]"
[24/02/2012 16:10:36]  if ERRORLEVEL 1 goto error
[24/02/2012 16:10:36]  SQLCMD -b -a32767 -S"WPRI1225" -d"DQS_MAIN" -idb\create_logic_db_objects.sql
[24/02/2012 16:10:36]  if ERRORLEVEL 1 goto error
[24/02/2012 16:10:36]  SQLCMD -b -a32767 -S"WPRI1225" -d"DQS_MAIN" -idb\populate_default_kbs.sql
[24/02/2012 16:10:36]  if ERRORLEVEL 1 goto error
[24/02/2012 16:10:36]  if "false" == "true" (
[24/02/2012 16:10:36] SQLCMD -b -a32767 -S"WPRI1225" -d"DQS_MAIN" -idb\create_transient_db_objects.sql
[24/02/2012 16:10:36]  if ERRORLEVEL 1 goto error
[24/02/2012 16:10:36] )
[24/02/2012 16:10:36] )
[24/02/2012 16:10:36]
[24/02/2012 16:10:36] C:\Users\curtis\AppData\Local\Temp\qupg2sk4.bde>goto exit
[24/02/2012 16:10:36] exit label - no error
[24/02/2012 16:10:36]
[24/02/2012 16:10:36] C:\Users\curtis\AppData\Local\Temp\qupg2sk4.bde>ENDLOCAL
[24/02/2012 16:10:36]
[24/02/2012 16:10:36] Script process returned unexpected exit code: '1'.
[24/02/2012 16:10:36] Action 'Create data quality schema' finished with errors,aborting installation.

So it looks to me as though the bat file is working perfectly however DQSInstaller.exe thinks that something's gone wrong. Possibly that something is the file that can't be found. I'll take a look in Process Monitor and see if I see which one it is.

Thanks, Steve

Answer 12

Wow that's a clever approach. You just click the pause key on your keyboard to pause it? I will use that technique then - good to know. No luck finding an error...

I'll see if I can check the DQSInstaller.exe source code to see if there is any "file" call just before this batch then.

Seems like process monitor is our last option short of debugging.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 13

Nope, unfortunately I've had no luck finding the error in process monitor. I did take a look at DQSInstaller.exe in ILSpy and I couldn't see any file call between the logging of "recreate_schema.bat SERVERNAME DQS SQL_Latin1_General_CP1_CI_AS" and the first line in the bat file. Except cmd.exe of course.

I even tried using ILSpy to write some similar C# to run the bat file and see what came back, but alas, it worked fine.

How would it be possible to debug DQSInstaller?

Thanks, Steve.

Answer 14

I've sort of got around the installation problem for the moment by running the steps that follow recreate_schema.bat. Using ILSpy I've seen and then run the actions for RegisterAssemblyAction, SetProductVersionPropertyAction and CreateMdsUsersAction. But I've not run LoadOutOfTheBoxDataAction, so maybe there will be a problem.

Jason, I'll be happy to help to continue diagnosing what's wrong with the install if you need me to though.

Steve.

Answer 15

I've looped in the developers to take a look for other ideas.

The caller code looks strictly at process.ExitCode from the child process:

      cmd.exe /C recreate_schema.bat WPRI1225 DQS SQL_Latin1_General_CP1_CI_AS

When the Exit code is non-zero it logs that message. "Script process returned unexpected exit code: '1'."

The only other thing I can imagine is that the WaitForExit() call is happening but the cmd is slow to actually exit, some small timing condition if the computer is very slow (VM?) or if there is some 3rd party monitoring for process exits (antivirus, spyware tools?

If you could see the cmd.exe command line in task manager, it may reveal something we haven't seen (any rogue switch or something) but I am doubtful of that. I remember you said you couldn't find the command line before. Did you click the button to show tasks from all users? I see they use an external cmd.exe process in the code, but maybe I'm wrong.

Process Monitor should show us the Thread Exit and Process Exit events, and in the exit of cmd.exe we should be able to verify the exit code. Put an include filter on ProcessName=DQSInstaller.exe and ProcessName=CMD.exe

Example of observing Process Exit in Process Monitor:

By the way, are you clicking the icon to launch the DQSInstaller, or are you using an elevated Admin command prompt?

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 16

Hi Jason, I've fixed it!

Using process monitor I was able to see that cmd.exe was exiting with an exit status of 1. So then I tried a bat file that didn't do anything and that also had the same error of having an exit status of 1. By changing the path to have nothing in it and running process monitor again and with a little trial and error, I could see that the file cmd.exe was trying to open was c:\temp\ansi32\x64\ansicon.exe. A quick search in the registry showed it at

HKCU\System\Microsoft\Command Processor\AutoRun Reg_SZ: "c:\temp\ansi32\x64\ansicon.exe" -p

and that file doesn't exist. I've removed the registry entry and now DQSInstaller.exe has completed successfully.

I'm not sure what put that entry there and I don't have that exe on my machine and I certainly didn't install it. It's all very strange.

Thanks so much for your help in diagnosing what was wrong, you've been a great help and that was a great tip about looking at the exit code using process monitor.

Thanks again,

Steve.

Answer 17

Wow I never heard of AutoRun Command Processor key before. That is a new one for me.

Glad you got it resolved, and glad it isn't a DQS bug after all :-) My faith is restored.

Seems like a useful freeware tool to color the command prompt, but when missing, if it affects the outcome of batch files!

http://www.softpedia.com/get/System/System-Miscellaneous/ANSICON.shtml

Good luck with DQS now! Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 18

Hola buenos días

Estoy instalando Data Quality y se me presenta el mismo error al final de la instalacion:

[15/05/2013 8:35:58] ERROR - An error occured, check message above
[15/05/2013 8:35:58] 
[15/05/2013 8:35:58] Script process returned unexpected exit code: '1'.
[15/05/2013 8:35:58] Action 'Register data quality assemblies and stored procedures' finished with errors, aborting installation.
[15/05/2013 8:35:58] Starting installation rollback...
[15/05/2013 8:36:00] Installation rollback completed successfully.

Estoy viendo que ya lo solucionaste, pero no me quedo muy claro como lo hiciste.... Sera que me puedes colaborar para continuar con la instalacion??.

Mil gracias.