This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- SQL Logging
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Sep 07, 2009
10:19 AM
SQL Logging
I am running some SQL scripts within my MSI using built in Installshield functionality.
If you run these scripts via SQL Management Studio, you get much more information than is being put into the MSI log. If I run the same scripts using sqlcmd or osql then I get the same results as the MSI log. However I noticed sqlcmd has an option of -m which i set to -m-1 and i got perfect messaging and logging that I need. But obviously I don't have access to how they are run. So I found that -m equates to using the setvar command at the top of the script and using it to set the variable SQLCMDERRORLEVEL to -1. Again Sqlcmd works great even without the -m. I thought I had it solved until I found that SQL management Studio and osql.exe complain that they cannot understand Sqlcmd variables. In the MSI log, I also get the same message so I've concluded that Installshield uses an api based on osql and so does SQL Management Studio. Osql.exe also has a -m option but no apparent corresponding script entry to do the same thing like sqlcmd did.
Has anybody got any suggestions on overriding the SQL log level within the MSI log to output informational messages? I would rather not switch away from using built-in functionality in favour of custom actions because I have already introduced massive amounts of complexity to this installation deploying reports to reporting services, iis packages and deploying and processing a SQL cube :S This will also help our database team resolve issues on my behalf, because I don't actually write these scripts.
If you run these scripts via SQL Management Studio, you get much more information than is being put into the MSI log. If I run the same scripts using sqlcmd or osql then I get the same results as the MSI log. However I noticed sqlcmd has an option of -m which i set to -m-1 and i got perfect messaging and logging that I need. But obviously I don't have access to how they are run. So I found that -m equates to using the setvar command at the top of the script and using it to set the variable SQLCMDERRORLEVEL to -1. Again Sqlcmd works great even without the -m. I thought I had it solved until I found that SQL management Studio and osql.exe complain that they cannot understand Sqlcmd variables. In the MSI log, I also get the same message so I've concluded that Installshield uses an api based on osql and so does SQL Management Studio. Osql.exe also has a -m option but no apparent corresponding script entry to do the same thing like sqlcmd did.
Has anybody got any suggestions on overriding the SQL log level within the MSI log to output informational messages? I would rather not switch away from using built-in functionality in favour of custom actions because I have already introduced massive amounts of complexity to this installation deploying reports to reporting services, iis packages and deploying and processing a SQL cube :S This will also help our database team resolve issues on my behalf, because I don't actually write these scripts.
(3) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Sep 07, 2009
11:59 PM
Hello, I am not sure how many SQL scripts you are running? If the number is small, what I have done in the past is use InstallScript LanuchApp and Wait to take complete control over the execution of the .sql script by launching a command prompt and executing a .cmd file with the name of the .sql file to execute and any necessary parameters. This way, I pipe the output of the .sql script to a .txt file and have a complete audit trail of how the .sql script executed.
By the way, osql was deprecated in SQL Server 2005 and you must use sqlcmd on SQL Server 2005 and better databases.
Hope this helps!
Tim
By the way, osql was deprecated in SQL Server 2005 and you must use sqlcmd on SQL Server 2005 and better databases.
Hope this helps!
Tim
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Sep 08, 2009
03:36 AM
Thanks for the reply but...
I'm running about 10 scripts but two large ones conditioned to run on install or upgrade which on one of our customers can take up to 13 hours to run at the moment, it's a massive installation.
Plus the complexity of the installation with dozens of custom actions for deploying reporting services reports, sql cube, processing cube, SSIS packages, SQL proxies, credentials... the list goes on and the complicated GUI to drive all this and keep it simple for the user is not good... So therefore if I can use as much out of the box functionality would be good. It seems silly to abandon the tables in favour of a custom action just for a log is not good. Especially seeing as it is logging to the MSI log for critical messages. I just want further info messages too because our db team need to know some of the information leading up to a critical message if there are problems
I guess what I'm hoping for is a line i can place in the script that will override any default logging level that isn't based on the SQLCMDERRORLEVEL variable or a way of telling the Installshield MSI to increase logging
Also I'm not launching OSQL i was just concluding that Installshield use an API based upon OSQL for their out of the box SQL functionality.
I tell you what... I could recommend a million and 1 improvements to Installshield's SQL functionality since taking up this project to support much more than just simple SQL scripts but to take into consideration credentials, SQL Agent jobs, proxies, SQL Analysis services, reporting services and integration services. So if you're listening Acresso 😄
I'm running about 10 scripts but two large ones conditioned to run on install or upgrade which on one of our customers can take up to 13 hours to run at the moment, it's a massive installation.
Plus the complexity of the installation with dozens of custom actions for deploying reporting services reports, sql cube, processing cube, SSIS packages, SQL proxies, credentials... the list goes on and the complicated GUI to drive all this and keep it simple for the user is not good... So therefore if I can use as much out of the box functionality would be good. It seems silly to abandon the tables in favour of a custom action just for a log is not good. Especially seeing as it is logging to the MSI log for critical messages. I just want further info messages too because our db team need to know some of the information leading up to a critical message if there are problems
I guess what I'm hoping for is a line i can place in the script that will override any default logging level that isn't based on the SQLCMDERRORLEVEL variable or a way of telling the Installshield MSI to increase logging
Also I'm not launching OSQL i was just concluding that Installshield use an API based upon OSQL for their out of the box SQL functionality.
I tell you what... I could recommend a million and 1 improvements to Installshield's SQL functionality since taking up this project to support much more than just simple SQL scripts but to take into consideration credentials, SQL Agent jobs, proxies, SQL Analysis services, reporting services and integration services. So if you're listening Acresso 😄
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Sep 08, 2009
02:51 PM
While I agree that improvements can typically always be made in any tool, I am not sure you can really expect or want some of the tasks that you mention to be handled by a Windows Installer tool. You may want to push back on your developers and ask them to write some of this logic for you. For example, in an installer that I wrote, we need to be able to do much more than just execute SQL scripts and this functionality is performed by writing a VB.NET application which utilizes SQL Server Management Objects. The installer simply launches the compiled VB.NET application and waits for its execution to end.
Just my two-cents worth!
Tim
Just my two-cents worth!
Tim
