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
