Creating an Identity column to a SELECT statement

Hi all,
I am pretty much going insane. I have tried all sorts of things and gotten
nowhere, and I'm fairly sure there exists a simple solution to my problem.
If you help me, I will be eternally in your debt.
Here is the scenario. I have a stored procedure which conains a fairly heavy
UNION query, which I'm not going to repeat here. The data comes from all
sorts of places, and the data that comes back has no unique record
identifier. I want to add one, that is, effectively add a IDENTITY column to
the query result.
I have put the data from the UNION query into a table variable, called
@.myResults (I could put it into a temp table #myResult instead, if you
care). This kind of makes my problem simpler to see, but be aware that the
source tables have no unique identifier I can use. The column "myID" I have
just made up with zero value, in case it can be used.
SELECT myID, Color FROM @.myResults -- Simplified example, this is
what I get
0 Blue
0 Red
0 Green
I want to add a column or update the myID column so that it looks like this,
counting each row
-- This is what I want. How?!?!
1 Blue
2 Red
3 Green
Simple, eh? That's what I thought.
I have tried this --
SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
work
and
SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has number
3 on each row
what I want is something like this --
SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt as
far as I can tell
As a general thing, I'm not sure how to add an identity column to a table
that already has data in it. That's kind of what I am trying to do, but to a
select statement result.
I have even considered looping through each record in a cursor and manually
updating the int. Seems like a lot of work, and this stored procedure is
going to get hit a lot and needs to be fairly fast.
Mostly I'm just burning up because I *know* there is a simple answer to
this - I just can't see it!
Thanks in advance,
SaulLook at the IDENTITY function in Books Online; you basically want to do
something like:
SELECT IdentColumn = IDENTITY(int, 1,1),
OTHERColumns
INTO TargetTable --must be a table or temp table
FROM @.myResults
HTH
Stu|||I suggest you to insert data into a #TempTable like
Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From TableNam
e
-- That should generate record numbers for you
HTH
Ed
"Saul" wrote:

> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly hea
vy
> UNION query, which I'm not going to repeat here. The data comes from all
> sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
to
> the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I hav
e
> just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like thi
s,
> counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has numbe
r
> 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt a
s
> far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
a
> select statement result.
> I have even considered looping through each record in a cursor and manuall
y
> updating the int. Seems like a lot of work, and this stored procedure is
> going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>
>|||Guys,
Thank you for your responses!!
Yes, what you suggested works, and works quite well. What I guess I don't
like about it is that this means creating a temp table to solve the problem.
But it does work, so I'm not complaining! So, thanks again!!
On the way down to lunch, I thought of another solution though, which I like
better and also works. Here's the idea - when I intially declare the table
variable (@.myResults) I define an indentity column there. Then, when I do
the insert into.., I don't insert into that ID column, and it takes care of
creating the identity. What I preffer about this solution is that the
identity is built the first time when the data is being inserted.
eg
declare @.myResults table (my_ID int identity(1,1) , colour varchar(20))
insert into @.myResults
SELECT colour
FROM table1
UNION
SELECT colour
FROM table2
Of course, my real world query is vastly more complicated, but it's for
illustration purposes.
- Saul
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:7088713E-1340-4D8D-9328-C8F0DEAF251B@.microsoft.com...
>I suggest you to insert data into a #TempTable like
> Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From
> TableName
> -- That should generate record numbers for you
> HTH
> Ed
>
> "Saul" wrote:
>|||There is no "simple" way of doing this, and there may not every be. The
issue here is that you need to something to order the data on. If you have
some unique value, and you want to add a sequence number, you can do
something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
To do a non-sortable order, you will need to build the data first (don't
expect the order you see from a select to always be the order of the
results. There are no guarantees with row order in a relational database.)
(note, in 2005 there will be an easier way to do this, but the same
limitations do exist.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Saul" <sbryan@.nsw.counterpoint.com.au> wrote in message
news:4355a839$0$1360$c30e37c6@.ken-reader.news.telstra.net...
> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly
> heavy UNION query, which I'm not going to repeat here. The data comes from
> all sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
> to the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I
> have just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like
> this, counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has
> number 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt
> as far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
> a select statement result.
> I have even considered looping through each record in a cursor and
> manually updating the int. Seems like a lot of work, and this stored
> procedure is going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

Creating a new database beside 1 that currently exists

I am developing an application that will use msde or SQL sevrer depending on
if they are in the office or workign on the road. If they are on the road
they currently have MSDE installed by a different program. I also want to
use the msde service to run my database.
is it possible to run two databases on the same PC?
if it is do I need to know the sa password for msde? if i do is there a way
around it, I doubt that the company whos program runs on msde would want me
knowing the sa password
I have my database scripted in 3 sql files and I know that I will have to
use oSQL to execute them
If any one can point me how to do it or provide sample code I would be very
grateful
cheers
Comments inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
>I am developing an application that will use msde or SQL sevrer depending
>on
> if they are in the office or workign on the road. If they are on the road
> they currently have MSDE installed by a different program. I also want to
> use the msde service to run my database.
> is it possible to run two databases on the same PC?
Yeah, you are only stuck in to one instance.
> if it is do I need to know the sa password for msde? if i do is there a
> way
> around it, I doubt that the company whos program runs on msde would want
> me
> knowing the sa password
if you got windows auth. activated you can easily log on to the msde
with an administrive account, they are usally members of the system
administrator group, if they disabled it, you can reenable that via:
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
auf "1" (Windows Auth)
http://www.microsoft.com/sql/tXechin...tion/MaXy3.asp

> I have my database scripted in 3 sql files and I know that I will have to
> use oSQL to execute them
> If any one can point me how to do it or provide sample code I would be
> very
> grateful
You can call external scripts via the switch -i
OSQL -iC:\Test.sql

> cheers
|||thanks for the reply
Am I right in assuming that I just re-run the msde2000 setup but name an
instance
eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
TARGETDIR="C:\Program files\KaisenDB\"
then I can play around with it using my SA password and its totally seperate
but just using the sql service
sorry if i sound dumb but I dont have much experience with administering
msde, just running from the bits i picked up
"Jens Sü?meyer" wrote:

> Comments inline.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
> Yeah, you are only stuck in to one instance.
> if you got windows auth. activated you can easily log on to the msde
> with an administrive account, they are usally members of the system
> administrator group, if they disabled it, you can reenable that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\L oginMode
> auf "1" (Windows Auth)
> http://www.microsoft.com/sql/tXechi...ion/MaXy3.asp
>
> You can call external scripts via the switch -i
> OSQL -iC:\Test.sql
>
>
|||hi Jens,
Jens Smeyer wrote:
> if you got windows auth. activated you can easily log on to the
> msde with an administrive account, they are usally members of the
> system administrator group, if they disabled it, you can reenable
> that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
> auf "1" (Windows Auth)
Windows authentication can not be disabled even in the value of "0" is
reported as SQLDMOSecurity_Normal (Allow SQL Server Authentication only)..
anyway, you'd require administrative WinNT privileges to modify that
registry settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Steven,
steven scaife wrote:
> thanks for the reply
> Am I right in assuming that I just re-run the msde2000 setup but name
> an instance
> eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
> TARGETDIR="C:\Program files\KaisenDB\"
> then I can play around with it using my SA password and its totally
> seperate but just using the sql service
MSDE installs by default disable standard SQL Server connection and only
allowing trusted ones...
to modify this behaviour at install time you have to provide the
SECURITYMODE=SQL paramenter to the setup.exe boostrap installer... or,
after install, as already discussed, you can modifiy a registry key..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Create views only if database exists

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,
DaveHow 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, 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|||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, 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
>|||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|||Hi Ray,
Can you please elaborate, not sure what you mean.
Dave
"rb" wrote:
> 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|||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|||Hi Dave,
You can build a dynamic sql statement and then execute it. I hope this
example helps:
IF EXISTS(Select 1 from information_schema.schemata Where catalog_name ='Test2')
BEGIN
DECLARE @.sql nvarchar(200)
Set @.sql = 'CREATE VIEW dbo.vwt1 as Select * from test2.dbo.sysobjects'
EXEC(@.sql)
Set @.sql = 'CREATE VIEW dbo.vwt2 as Select * from test2.dbo.sysindexes'
EXEC(@.sql)
END
"Dave" wrote:
> Hi Ray,
> Can you please elaborate, not sure what you mean.
> Dave
> "rb" wrote:
> > 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

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