probably something like this (can't test at the moment)
<cfobject type="COM" name="SQLServer" class="SQLDMO.SQLServer"
action="CREATE" context="LOCAL">
<cfset SQLServer.Connect("SQLSERVER","(local)","")>
<cfset SQLDatabase = SQLServer.Databases()>
<CFLOOP COLLECTION="#SQLDatabase.Tables#" ITEM="tbl">
<cfif Not tbl.SystemObject>
<cfset Script = Script & tbl.Script(param)
</cfif>
</CFLOOP>
you'll have to find constant for the param value
Elliot
"Theo Galanakis" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> You are quite right it does extract the sp contents. Thankyou.
>
> Although just for my future reference I would still like to know how
> To call the SQLDMO.Table com dll from cold fusion.
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Elliot
Russo
> Sent: Thursday, 6 May 2004 1:13 PM
> To: CFAussie Mailing List
> Subject: [cfaussie] Re: calling SQLDMO Com experts...
>
> That is where sp_helptext comes in - get contents of proc unless compiled
> with encryption option
>
>
> "Theo Galanakis" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> >
> > Hi Elliot,
> > I'm familiar with sp_help and thankyou for the tip, I actually use
> > the comm Object to extract the entire table, stored procedure, view for
> > backup purposes formatted ready for a database rebuild, i.e simulating
the
> > enterprise manager Generate SQL Script... functionality..
> >
> > i.e sp_help produces the parameter inputs but not the entire stored
> > procedure scripted.
> >
> > The real question I guess is how do you effectively call the
SQLDMO.Table
> in
> > cold fusion... it works in vb code below.
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of Elliot
> Russo
> > Sent: Thursday, 6 May 2004 10:49 AM
> > To: CFAussie Mailing List
> > Subject: [cfaussie] Re: calling SQLDMO Com experts...
> >
> > Hi,
> >
> > you can get most of that stuff through straight sql against the schema
> stuff
> > (including the ddl of stored procs and views)
> >
> >
> > select * from sysobjects
> >
> > then sp_help
> >
> > eg for a table
> > <cfstoredproc procedure="sp_help" datasource="#attributes.datasource#"
> > returncode="Yes" dbname="#attributes.dbname#">
> > <cfprocparam type="In" cfsqltype="CF_SQL_CHAR"
value="#attributes.table#"
> > maxlength="100" null="No">
> > <cfprocresult name="qName" resultset="1">
> > <cfprocresult name="qParameters" resultset="2">
> > <cfprocresult name="qKeys" resultset="7">
> > </cfstoredproc>
> >
> > etc
> >
> > "Theo Galanakis" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> > >
> > > I have written a VB com dll to extract a database schema including all
> > > tables, stored procs, views etc.. I call this com object through
> > <cf_object>
> > > and it works as needed.
> > >
> > > However I have a query.... I have attempted to call the same methods
in
> > the
> > > SQLDMO class directly in cold fusion, i.e no need to write the VB dll
> > > directly to access the SQLDMO.
> > >
> > > Here is the Vb code which works fine...
> > >
> > > Public Function generateScript(server As String, database As String,
> > > username As String, password As String) As String
> > >
> > > Dim SQLServer As New SQLDMO.SQLServer
> > >
> > > ' SQL Server security
> > > If username = "(local)" Then
> > > SQLServer.LoginSecure = True
> > > SQLServer.Connect "(local)"
> > > Else
> > > SQLServer.Connect server, username, password
> > > End If
> > >
> > > Dim tbl As SQLDMO.Table
> > > Dim sp As SQLDMO.StoredProcedure
> > > Dim view As SQLDMO.view
> > >
> > > ' Get a reference to the selected database
> > > Set SQLDatabase = SQLServer.Databases(database)
> > >
> > > Dim param As SQLDMO_SCRIPT_TYPE
> > >
> > > Dim Script As String
> > >
> > > param = SQLDMOScript_Default Or SQLDMOScript_Indexes Or
> > > SQLDMOScript_Triggers Or SQLDMOScript_DatabasePermissions
> > >
> > > 'Generate all the tables
> > > For Each tbl In SQLDatabase.Tables
> > > If Not tbl.SystemObject Then
> > > Script = Script & tbl.Script(param)
> > > End If
> > > Next
> > >
> > > param = SQLDMOScript_Default
> > >
> > > 'Generate all the stored procedures
> > > For Each sp In SQLDatabase.StoredProcedures
> > > If Not sp.SystemObject Then
> > > Script = Script & sp.Script(param)
> > > End If
> > > Next
> > >
> > > param = SQLDMOScript_Default
> > >
> > > 'Generate all the Views
> > > For Each view In SQLDatabase.Views
> > > If Not view.SystemObject Then
> > > Script = Script & view.Script(param)
> > > End If
> > > Next
> > >
> > > 'Clean Up
> > > SQLServer.DisConnect
> > > Set SQLServer = Nothing
> > >
> > > generateScript = Script
> > > End Function
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > I wanted to replicate this in Cold Fusion, however I was not sure how
to
> > > defined something like this ... Dim tbl As SQLDMO.Table
> > >
> > >
> > > Here is my cold Fusion code so far ....
> > >
> > > <cfobject type="COM" name="SQLServer" class="SQLDMO.SQLServer"
> > > action="CREATE" context="LOCAL">
> > > <cfset SQLServer.Connect("SQLSERVER","(local)","")>
> > > <cfset SQLDatabase = SQLServer.Databases()>
> > >
> > > <cfobject type="COM" name="tbl" class="SQLDMO.Tables" action="CREATE"
> > > context="LOCAL">
> > >
> > > <cfloop index="i" from="1" to="#tbl.Count#">
> > > <cfdump var="#tbl.Script(4)#"><br>
> > > </cfloop>
> > >
> > >
> > > It throws an error when attempting to call the SQLDMO.Tables object...
> In
> > > the VB code it does not instantiate the SQLDMO.Tables as a new object
> but
> > > rather just assigns a variable.. Dim tbl As SQLDMO.Table
> > >
> > >
> > > Finally my question is how do you call the SQLDMO.Tables method, If
you
> > > cannot instantiate it?
> > >
> > > Ie is there a way to do this..
> > >
> > > Something like this... <cfparam name="tbl" type="SQLDMO.Table">
> > >
> > >
> > >
> > >
> > > This message may contain privileged or confidential information and is
> > > intended only for the individual named. If you are not the named
> addressee
> > > you should not disclose, disseminate, distribute or copy this e-mail.
If
> > you
> > > have received this e-mail by mistake please notify the sender
> immediately
> > by
> > > e-mail and delete this e-mail from your system. You should rely on
your
> > own
> > > virus checking programmes and procedures for checking any attachments.
> > > Please advise us if you wish your name and e-mail address to be
removed
> > from
> > > our database.
> > >
> > >
> > >
> >
> >
> >
> > ---
> > You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> > To unsubscribe send a blank email to
> > [EMAIL PROTECTED]
> >
> > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
> > http://www.mxdu.com/ + 24-25 February, 2004
> >
> > This message may contain privileged or confidential information and is
> > intended only for the individual named. If you are not the named
addressee
> > you should not disclose, disseminate, distribute or copy this e-mail. If
> you
> > have received this e-mail by mistake please notify the sender
immediately
> by
> > e-mail and delete this e-mail from your system. You should rely on your
> own
> > virus checking programmes and procedures for checking any attachments.
> > Please advise us if you wish your name and e-mail address to be removed
> from
> > our database.
> >
> >
> >
>
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
> http://www.mxdu.com/ + 24-25 February, 2004
>
> This message may contain privileged or confidential information and is
> intended only for the individual named. If you are not the named addressee
> you should not disclose, disseminate, distribute or copy this e-mail. If
you
> have received this e-mail by mistake please notify the sender immediately
by
> e-mail and delete this e-mail from your system. You should rely on your
own
> virus checking programmes and procedures for checking any attachments.
> Please advise us if you wish your name and e-mail address to be removed
from
> our database.
>
>
>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
