RE: running MSSQL scripts
What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: running MSSQL scripts
Robert, Why not just run it inside a cfquery tag - since you have already shot your security in the foot with xp_cmdshell (lol). -Mark -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Friday, October 01, 2004 7:50 AM To: CF-Talk Subject: RE: running MSSQL scripts What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: running MSSQL scripts
You do not mention CF, but since you are posting to CF-TALK I assume it comes into the process somewhere :-) If you frame your question around how it fits into CF this list will be able to help you right away! Apart from CFQUERY making your script a stored dbproc may be an option - this can then be called from CF with CFSTOREDPROC. Best Regards Mike - Original Message - From: Richard Meredith-Hardy [EMAIL PROTECTED] Date: Fri, 01 Oct 2004 12:59:30 +0100 Subject: running MSSQL scripts To: CF-Talk [EMAIL PROTECTED] I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: running MSSQL scripts
I'd suggest using cffile to read the text from the .sql file and then plop that text into a cfquery set. John -Original Message- From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: Friday, October 01, 2004 8:00 AM To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: running MSSQL scripts
Why? This is for DB maintenance tasks.If you make an alteration to a table, moving a field position for example, then Enterprise Manager can sometimes make quite a long script with transactions, temporary tables and lots of GO statements. Yes, I suppose I could split it up into lots of separate CFQUERYs, surrounding the lot with CFTRANSACTION, but it seems easier to me and much less error prone to just be able to run the script itself. Robertson-Ravo, Neil (RX) wrote: What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: running MSSQL scripts
From CF5, I said, it says, [below] Michael Traher wrote: You do not mention CF, but since you are posting to CF-TALK I assume it comes into the process somewhere :-) If you frame your question around how it fits into CF this list will be able to help you right away! Apart from CFQUERY making your script a stored dbproc may be an option - this can then be called from CF with CFSTOREDPROC. Best Regards Mike - Original Message - From: Richard Meredith-Hardy [EMAIL PROTECTED] Date: Fri, 01 Oct 2004 12:59:30 +0100 Subject: running MSSQL scripts To: CF-Talk [EMAIL PROTECTED] I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: running MSSQL scripts
Don't you have access to the SQL Server? If so use an SP.NOTE: GO is not a required statement.Also, don't get EM to script, it's crapola. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 14:41 To: CF-Talk Subject: Re: running MSSQL scripts Why? This is for DB maintenance tasks.If you make an alteration to a table, moving a field position for example, then Enterprise Manager can sometimes make quite a long script with transactions, temporary tables and lots of GO statements. Yes, I suppose I could split it up into lots of separate CFQUERYs, surrounding the lot with CFTRANSACTION, but it seems easier to me and much less error prone to just be able to run the script itself. Robertson-Ravo, Neil (RX) wrote: What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: running MSSQL scripts
This is a dev intranet setup at a remote location, I probably won't have direct access to the DB with EM. CFQUERY doesn't like EM script at all. ODBC, I imagine. A sp is exactly what I am trying to do. However, if you pass the actual script in as a var from CF5 then you run into unicode and/or ntext problems (I think) which is why it occurred to me just pass it a script path name, sp gets the actual text from the file and then runs it.How to do it though. EM may be but it seems to work, and the scripts seem to be repeatable in EM or query analyser, and it's easy to generate them. What's the alternative? Robertson-Ravo, Neil (RX) wrote: Don't you have access to the SQL Server? If so use an SP.NOTE: GO is not a required statement.Also, don't get EM to script, it's crapola. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 14:41 To: CF-Talk Subject: Re: running MSSQL scripts Why? This is for DB maintenance tasks.If you make an alteration to a table, moving a field position for example, then Enterprise Manager can sometimes make quite a long script with transactions, temporary tables and lots of GO statements. Yes, I suppose I could split it up into lots of separate CFQUERYs, surrounding the lot with CFTRANSACTION, but it seems easier to me and much less error prone to just be able to run the script itself. Robertson-Ravo, Neil (RX) wrote: What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: running MSSQL scripts
Well, post what you have now (off list if you like) _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 15:25 To: CF-Talk Subject: Re: running MSSQL scripts This is a dev intranet setup at a remote location, I probably won't have direct access to the DB with EM. CFQUERY doesn't like EM script at all. ODBC, I imagine. A sp is exactly what I am trying to do. However, if you pass the actual script in as a var from CF5 then you run into unicode and/or ntext problems (I think) which is why it occurred to me just pass it a script path name, sp gets the actual text from the file and then runs it.How to do it though. EM may be but it seems to work, and the scripts seem to be repeatable in EM or query analyser, and it's easy to generate them. What's the alternative? Robertson-Ravo, Neil (RX) wrote: Don't you have access to the SQL Server? If so use an SP.NOTE: GO is not a required statement.Also, don't get EM to script, it's crapola. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 14:41 To: CF-Talk Subject: Re: running MSSQL scripts Why? This is for DB maintenance tasks.If you make an alteration to a table, moving a field position for example, then Enterprise Manager can sometimes make quite a long script with transactions, temporary tables and lots of GO statements. Yes, I suppose I could split it up into lots of separate CFQUERYs, surrounding the lot with CFTRANSACTION, but it seems easier to me and much less error prone to just be able to run the script itself. Robertson-Ravo, Neil (RX) wrote: What does the script do?You should be able to just use cfquery unless it's using some non-supported SQL. _ From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED] Sent: 01 October 2004 13:00 To: CF-Talk Subject: running MSSQL scripts I'm struggling here What I would like to do is load a MSSQL script created in Query Analyser or Enterprise manager onto the server and then run it from CF5. making and loading the .sql file is easy.Running it is another matter. I've tried creating a sp which runs xp_cmdshell @stwhere @st is a complete osql string.This works but being a 2 stage process it seems to be difficult to get any meaningful feedback if there is a problem in the script. sp_executesql seems to be a better option but how to read the .sql file? This must be a common thing people need to do? -- Regards; Richard Meredith-Hardy - r[dot]mh[at]flymicro[dot]com Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
