Either using cfquery or cfstoredproc.
_____
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 22, 2004 10:34 AM
To: CF-Talk
Subject: Re: sql question
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]
