Create views only if database exists

How do you execute the script? OSQL etc will exit of you issue a RAISERROR w
ith a state of 127.
Another alternative is to do a REISERROR with a high enough severity level (
I believe 19 or higher
will do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:F4BBCA1B-0296-43C8-8E42-CCCAB69044CA@.microsoft.com...
> Hi, I have a script that I execute, which checks whether a database exists
, I
> wish to then only create the views if this DB exists. I have been attempti
ng
> to use return, like this:
> if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
> CATALOG_NAME='velocity')
> return
> and i then have my create views after this, but it still executes the rest
> of the script, (it seems to be due to the fact I have a GO commands after
> each view i then try to create, otherwise if I remove the GO's sql says
> 'CREATE VIEW' must be the first statement in a query batch. Anyone have an
y
> ideas?
> Thanks,
> DaveHi Tibor, thanks for the quick response.
I execute it as part of an installation (using Wise). Unfortunately at the
moment, it does raise an error as the script continues, so the person
installing the software sees an error. which is not really acceptable
"Tibor Karaszi" wrote:

> How do you execute the script? OSQL etc will exit of you issue a RAISERROR
with a state of 127.
> Another alternative is to do a REISERROR with a high enough severity level
(I believe 19 or higher
> will do).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <Dave@.discussions.microsoft.com> wrote in message
> news:F4BBCA1B-0296-43C8-8E42-CCCAB69044CA@.microsoft.com...
>|||Hi,
Use dynamic sql to build you sql statement to create your views.
Ray
"Dave" wrote:

> Hi, I have a script that I execute, which checks whether a database exists
, I
> wish to then only create the views if this DB exists. I have been attempti
ng
> to use return, like this:
> if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
> CATALOG_NAME='velocity')
> return
> and i then have my create views after this, but it still executes the rest
> of the script, (it seems to be due to the fact I have a GO commands after
> each view i then try to create, otherwise if I remove the GO's sql says
> 'CREATE VIEW' must be the first statement in a query batch. Anyone have an
y
> ideas?
> Thanks,
> Dave|||Hi Ray,
Can you please elaborate, not sure what you mean.
Dave
"rb" wrote:
[vbcol=seagreen]
> Hi,
> Use dynamic sql to build you sql statement to create your views.
> Ray
> "Dave" wrote:
>|||Hi, I have a script that I execute, which checks whether a database exists,
I
wish to then only create the views if this DB exists. I have been attempting
to use return, like this:
if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
CATALOG_NAME='velocity')
return
and i then have my create views after this, but it still executes the rest
of the script, (it seems to be due to the fact I have a GO commands after
each view i then try to create, otherwise if I remove the GO's sql says
'CREATE VIEW' must be the first statement in a query batch. Anyone have any
ideas?
Thanks,
Dave|||How do you execute the script? OSQL etc will exit of you issue a RAISERROR w
ith a state of 127.
Another alternative is to do a REISERROR with a high enough severity level (
I believe 19 or higher
will do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:F4BBCA1B-0296-43C8-8E42-CCCAB69044CA@.microsoft.com...
> Hi, I have a script that I execute, which checks whether a database exists
, I
> wish to then only create the views if this DB exists. I have been attempti
ng
> to use return, like this:
> if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
> CATALOG_NAME='velocity')
> return
> and i then have my create views after this, but it still executes the rest
> of the script, (it seems to be due to the fact I have a GO commands after
> each view i then try to create, otherwise if I remove the GO's sql says
> 'CREATE VIEW' must be the first statement in a query batch. Anyone have an
y
> ideas?
> Thanks,
> Dave|||Hi Tibor, thanks for the quick response.
I execute it as part of an installation (using Wise). Unfortunately at the
moment, it does raise an error as the script continues, so the person
installing the software sees an error. which is not really acceptable
"Tibor Karaszi" wrote:

> How do you execute the script? OSQL etc will exit of you issue a RAISERROR
with a state of 127.
> Another alternative is to do a REISERROR with a high enough severity level
(I believe 19 or higher
> will do).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <Dave@.discussions.microsoft.com> wrote in message
> news:F4BBCA1B-0296-43C8-8E42-CCCAB69044CA@.microsoft.com...
>|||Hi,
Use dynamic sql to build you sql statement to create your views.
Ray
"Dave" wrote:

> Hi, I have a script that I execute, which checks whether a database exists
, I
> wish to then only create the views if this DB exists. I have been attempti
ng
> to use return, like this:
> if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
> CATALOG_NAME='velocity')
> return
> and i then have my create views after this, but it still executes the rest
> of the script, (it seems to be due to the fact I have a GO commands after
> each view i then try to create, otherwise if I remove the GO's sql says
> 'CREATE VIEW' must be the first statement in a query batch. Anyone have an
y
> ideas?
> Thanks,
> Dave|||Hi Ray,
Can you please elaborate, not sure what you mean.
Dave
"rb" wrote:
[vbcol=seagreen]
> Hi,
> Use dynamic sql to build you sql statement to create your views.
> Ray
> "Dave" wrote:
>|||Don't use INFORMATION_SCHEMA.SCHEMATA to determine database existence or
enumerate databases. Although this view will provide a list of databases in
SQL 2000, that behavior doesn't conform to the ANSI standard. The
behavior was changed in SQL 2005 to list only schema in the current
database. To check for database existence, consider using IF
DB_ID('velocity') IS NOT NULL.
Regarding your original question, I can't help with Wise specifically but
most installers provide a way to conditionally execute installation tasks.
If you can't figure that out, you can execute a command file containing the
conditional code from the installer. For example (text may wrap):
REM Execute CreateViews.sql conditionally
@.OSQL -E -i CheckDatabase.sql
@.IF %ERRORLEVEL% == 0 OSQL -E -i CreateViews.sql
--CheckDatabase.sql
EXIT(SELECT CASE WHEN DB_ID('velocity') IS NULL THEN 1 ELSE 0 END)
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:F4BBCA1B-0296-43C8-8E42-CCCAB69044CA@.microsoft.com...
> Hi, I have a script that I execute, which checks whether a database
> exists, I
> wish to then only create the views if this DB exists. I have been
> attempting
> to use return, like this:
> if not exists(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE
> CATALOG_NAME='velocity')
> return
> and i then have my create views after this, but it still executes the rest
> of the script, (it seems to be due to the fact I have a GO commands after
> each view i then try to create, otherwise if I remove the GO's sql says
> 'CREATE VIEW' must be the first statement in a query batch. Anyone have
> any
> ideas?
> Thanks,
> Dave