Re: SQL Script failing when using IF BEGIN END

From: Jeremiah Traxler (JeremiahTraxler_at_discussions.microsoft.com)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 09:59:01 -0700

Good observation. That's exactly what I am doing.

I am preparing the SQL updates for an application so that the current
version of our software is compatible with the current version of our
database.

We start with a default database (version 1.20.0022) and apply the latest
updates that we have completed(currently 1.20.9400, approx. 30 combined
scripts into one). Previously, all of the scripts would run as a really long
file through either OSQL or Query Analyzer without any SQL logic. I am
trying to add the version checking in both VB application and SQL scripting
so that if a user tries to run the SQL commands directly using Query Analyzer
or something, the probablity of it being destructive is lessend. Also, I am
now trying to at least make the scripts check to see that the database
version is the prior version(to prevent scripts that insert records into
existing tables from being duplicated, etc.) before each update. I am also
working on the Visual Basic application which will control the log file that
I am going to generate after running these scripts.

I am running into problems specifically with the CREATE VIEW. Am I not
allowed to use this inside of the BEGIN END?

This suggestion below is the best thing that I have so far, but is it not
possible to do the Create View inside of the IF logic?

IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID(<x>') AND
OBJECTPROPERTY(id, N'IsView') = 1) AND (select DBVersion from tablesysinfo)
= '1.20.2000'
 DROP VIEW <x>
GO
CREATE VIEW......

I am trying to 1. Check the version of the database for exactly the previous
version and 2. Run the update script which updates the database version only
if necessary. 3? It would be nice to not have extraneous errors if possible.

"Keith Kratochvil" wrote:

> It seems like you have an application that you are trying to maintain and
> you might be rolling out a new version of your app. This is a complicated
> task, therefore it might take lots of back and forth within the newsgroup.
>
> With that said, what happens if the DBVersion is not 1.20.2000? Will
> creating the view be a problem? It seems like creating new stuff (views,
> tables, stored procedures) should not matter.
>
> However, if the view (or table or stored procedure or....) already exist and
> you drop and recreate them without meaning to you are likely to have
> problems. It does not appear that you check for the object before trying to
> create it....perhaps that would be the better approach.
>
> Something like this
>
> IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID(<x>') AND
> OBJECTPROPERTY(id, N'IsView') = 1) AND (select DBVersion from tablesysinfo)
> = '1.20.2000'
> DROP VIEW <x>
> GO
> CREATE VIEW......
>
> If the DBVersion is not 1.20.2000 the view will not be dropped and the
> create will fail. This is probably what you want, so simply ignore the
> error message.
>
> You could also create a much more intelligent installer to call the
> appropriate scripts, but that would involve more coding and testing and I
> don't know if it is worth it to you.
>
> --
> Keith
>
>
> "Jeremiah Traxler" <JeremiahTraxler@discussions.microsoft.com> wrote in
> message news:1CB3B317-62E5-4B9D-9846-8A6FE102BBE6@microsoft.com...
> > I have tried removing the GO's from it to try it out, but more
> specifically
> > this is what I'm trying to do and the error is listed below:
> >
> > -----------------------------------------------
> >
> > IF (select DBVersion from tablesysinfo) = '1.20.2000'
> > BEGIN
> > -- Create view CUSTSITES_101:
> > CREATE VIEW dbo.CUSTSITES_101 AS SELECT c.TELEPHONE, c.COMPANYNAME,
> > c.WORKPHONE, c.EXTRAPHONE, c.SSN, c.BADDRESS1, c.BADDRESS2, c.BCITY,
> > c.BSTATE, c.BZIP, c.BCOUNTRY, c.DATEENTERED, c.CUSTTYPE, c.CREDITSCORE,
> > s.MONITORINGID, s.SITEPHONE, s.CUSTNUM, s.SITENUM, s.ADDRESS1, s.SITENAME,
> > s.ADDRESS2, s.CITY, s.STATE, s.ZIP, s.COUNTRY, s.DATESITEENTERED,
> > s.INSTALLSTATUS, s.CONTRACTSTATUS, s.SALESID, s.INSTALLERID, s.MRR,
> > s.SYSTYPEID, s.PASSWORD, s.SQFEET, s.WORKTYPE, s.STORIES, s.LOCATIONID,
> > s.PAYMENTFREQ, s.CONTRACTTERM, s.KEYMAP, c.LASTNAME, c.FIRSTNAME,
> > s.SITEFIRSTNAME, s.SITELASTNAME, s.ADDRESS1 AS ADLINE1, s.ADDRESS2 AS
> > ADLINE2, s.CITY + ', ' + s.STATE + ' ' + s.ZIP AS ADLINE3, s.BADDRESS1 AS
> > BADLINE1, s.BADDRESS2 AS BADLINE2, P.LISTVALUE AS SITESTATUS FROM
> > dbo.PRAM101000001 P INNER JOIN dbo.CUST101000004 s ON P.LISTTYPEID =
> > s.SITESTATUS RIGHT OUTER JOIN dbo.CUST101000001 c ON s.CUSTNUM = c.CUSTNUM
> > END
> >
> > ---------------------------------------------------
> >
> > Server: Msg 156, Level 15, State 1, Line 4
> > Incorrect syntax near the keyword 'VIEW'.
> >
> > ---------------------------------------------------
> >
> >
> > "Keith Kratochvil" wrote:
> >
> > > >>Maybe it's the limitation of using GO inside of IF BEGIN END
> conditions.
> > > I don't know anymore.<<
> > >
> > > Correct
> > > GO is a batch terminator. You cannot use it between BEGIN and END.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Jeremiah Traxler" <JeremiahTraxler@discussions.microsoft.com> wrote in
> > > message news:5FE94435-62C1-48E1-9CFB-012C43EA3C35@microsoft.com...
> > > > I don't know if this is the right place for this, so if it's not maybe
> > > > someone can point me in the right direction.
> > > >
> > > > I'm having trouble running an IF statement to control whether or not
> > > update
> > > > scripts get ran against my database. The major problem right now is
> the
> > > fact
> > > > that I can't seem to execute CREATE VIEW statements that were
> generated by
> > > > SQL Server.
> > > >
> > > > Example of what I'm trying to do(disregard the actual VIEW
> modification
> > > > because I have trimmed it up and changed it to conserve space here on
> this
> > > > post; it works without the IF BEGIN END):
> > > > -----------------------------------------
> > > >
> > > > USE MyDatabase
> > > > GO
> > > >
> > > > IF (select DBVersion from mysystable) = '1.38.0056'
> > > > BEGIN
> > > > SET QUOTED_IDENTIFIER ON
> > > > GO
> > > > SET ANSI_NULLS ON
> > > > GO
> > > >
> > > > ALTER VIEW dbo.VIEWDBINFO_101
> > > > AS
> > > > SELECT dbo.TBLK101000002.MEMO
> > > > FROM dbo.TBLT101000001 INNER JOIN
> > > > dbo.TBLK101000002 ON dbo.TBLT101000001.CUSTNUM =
> dbo.TICK101000002.CUSTNUM
> > > > GO
> > > > SET QUOTED_IDENTIFIER OFF
> > > > GO
> > > > SET ANSI_NULLS ON
> > > > GO
> > > >
> > > >
> > > > --Update database versioning info
> > > > update mysystable set version='1.38.0056'
> > > > GO
> > > > END
> > > > -------------------------------------
> > > >
> > > > Maybe it's the limitation of using GO inside of IF BEGIN END
> conditions.
> > > I
> > > > don't know anymore.
> > > >
> > > > Any help would be wonderful.
> > >
> > >
>
>

Relevant Pages

  • Re: Access vs SQL
    ... > to SQL. ... Are you using the database primarily as a storage place, ... If you have many updates then you need to constantly compact it. ... No additional cost to your clients. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Dynamic database creation related.
    ... > I need to deploy the database of my winapp on client machine but do not ... my personal choice is to distribute sql DDL scripts to be run on the ... this will help you later when database structure modification must be ...
    (microsoft.public.sqlserver.msde)
  • Re: "Automated" updates..
    ... a SQL DB call is made every few seconds and it's causing ... new HTML file/s whenever the database updates, ... The software that updates the HTML will normally require far less ...
    (comp.lang.javascript)
  • Re: SQL SERVER 2005 Structural changes monitor with C#
    ... Does your process of communicating database changes ... dev team. ... scripts logged on my development database and execute them on a new ... So the key issue I am trying to figure out is how SQL Server Profiler ...
    (microsoft.public.dotnet.general)
  • Re: Last Date Stored Proc Updated???
    ... there really is a great way of working with your SQL code in just ... This database is then used by ... 'create' scripts under source control and just modify those. ...
    (comp.databases.ms-sqlserver)
Loading