PDA

View Full Version : How can you prevent a SQL script from running at runtime



mumble
05-25-2005, 02:22 AM
I currently have a project where I have 5 SQL scripts, which will create a database and also create and grant access to users based on what the user enters. This seems to work ok.

But I have come to a point where I want one of the scripts to not be run if the user already exists in the server. What I would like to know is how can I choose not to have this script run.


The main part of this problem is that I am a novice to install shield and am not sure how to view the code, which makes this happen. At the moment I have 1 connection in the SQL scripts view, with the 5 scripts belonging to the same connection.

I then have created some properties in the Property manager. I then set these properties in the install script with MsiSetProperty. I then have these properties replace words in the install script in the TEXT REPLACE. I'm not sure how else to do this, I think there must be a better way?

The only Function I have showing in the installscrip is the OnSQLLogin. Could you please provide some guidance and help with this, as I am very confused as to what to look up. :confused:

Thank you
Craig :)

hidenori
05-26-2005, 01:31 AM
One thing you can do is to raise an user-defined error using the RAISERROR statement and handle it using the custom script error handling functionality of InstallShield. Add the following statement onto the top of your script that you don't want to run if the user already exists:

if exists (select * from dbo.sysusers where name = N'USERNAME' and uid < 16382)
RAISERROR 50001
GO

Then, in the Runtime tab of the script property, click the Custom... button in the Script Error Handling group and add a new entry with the following values:

Error Number: "50001", Behavior: "On Error, Goto Next Script", Project Wide: "No"

Hope this helps.

mumble
05-30-2005, 01:37 AM
Thank you this works great.
Cheers for the help

sanshr
02-23-2006, 08:17 PM
Hi:

I encounter runtime errors when the below sql script is executed in an installation created out of IS 11 - Prof Edtn.

if not exists (select * from employees where FirstName = 'Sam' and LastName = 'Intko')
RAISERROR 50001
GO


I encounter the same error, when I execute the code in SQL Query Analyzer. Can someone point out how to fix this issue.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '50001'.


Thank you.

============================
One thing you can do is to raise an user-defined error using the RAISERROR statement and handle it using the custom script error handling functionality of InstallShield. Add the following statement onto the top of your script that you don't want to run if the user already exists:

if exists (select * from dbo.sysusers where name = N'USERNAME' and uid < 16382)
RAISERROR 50001
GO

Then, in the Runtime tab of the script property, click the Custom... button in the Script Error Handling group and add a new entry with the following values:

Error Number: "50001", Behavior: "On Error, Goto Next Script", Project Wide: "No"

Hope this helps.
=============================================

wocket
08-07-2007, 05:17 PM
For those of you with MySQL,

RAISERROR is not supported by MySQL.

I'm looking around for a similar solution in mysql, but haven't found anything yet. If someone has a suggestion, please let me know.

Thanks

-w