http://www.cflib.org/udf.cfm?id=1127
On Wed, 22 Sep 2004 10:33:55 -0400, Tony Weeg <[EMAIL PROTECTED]> wrote:
> it cant be ommitted when creating a view and
> you do that from a single dsn in cfmx and you
> have to reference the database.
>
> CREATE VIEW must be first in the sql statement, unless its preceded by a "GO"
>
> heres what im doing...
>
> we are using a distributed partitioned view, and when
> we make table column changes we have to drop the
> view and create it again we want to make a cfmx routine
> that will destruct the views on all databases,
> add the new column to all the tables, then recreate the view
> all from 1 dsn.
>
> here is what works in sql:
>
> use dpvEven_2
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[view_dpvReports]') and OBJECTPROPERTY(id,
> N'IsView') = 1)
> drop view [dbo].[view_dpvReports]
> go
> USE dpvEven_2
> go
> CREATE VIEW view_dpvReports
> AS
> SELECT * FROM dpv1.dpvOdd_1.dbo.reports1
> UNION ALL
> SELECT * FROM dpv1.dpvOdd_3.dbo.reports3
> UNION ALL
> SELECT * FROM dpv1.dpvOdd_5.dbo.reports5
> UNION ALL
> SELECT * FROM dpv1.dpvOdd_7.dbo.reports7
> UNION ALL
> SELECT * FROM dpv1.dpvOdd_9.dbo.reports9
> UNION ALL
> SELECT * FROM dpv1.dpvOdd_11.dbo.reports11
> UNION ALL
> SELECT * FROM dpv2.dpvEven_2.dbo.reports2
> UNION ALL
> SELECT * FROM dpv2.dpvEven_4.dbo.reports4
> UNION ALL
> SELECT * FROM dpv2.dpvEven_6.dbo.reports6
> UNION ALL
> SELECT * FROM dpv2.dpvEven_8.dbo.reports8
> UNION ALL
> SELECT * FROM dpv2.dpvEven_10.dbo.reports10
> UNION ALL
> SELECT * FROM dpv2.dpvEven_12.dbo.reports12
> go
>
> but that doesnt work from cfmx in a cfquery tag.
>
> im thinking i just might use some stored procedures for this...
>
> unless anyone can think of another way.
>
> thanks!
>
>
>
> Tony Weeg
>
> macromedia certified cold fusion developer
> email: tonyweeg [at] gmail [dot] com
> blog: http://www.revolutionwebdesign.com/blog/
> cool tool: http://www.antiwrap.com________________________________
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
