August 30, 2012 at 2:22 pm
Hi,
I have a SQL script with about 5500 lines, it does numerous things, but mainly, the job of the script is bringing the current table definition up to date, so it matches a "master" template. The commands in the script are created by doing a comparison with the source and target database. The script is supposed to be executed when need, ie. when the current table definition doesn't match the master. All of that I know how to handle. But I'm complete at loss, how to actually execute the script, without using dynamic SQL. The commands are things like the following:
ALTER TABLE [dbo].[PURCHLINEARCH] DROP CONSTRAINT [DF__PURCHLINE__ORIGL__69885181]
DROP INDEX [I_076ITEMLOCIDX] ON [dbo].[PURCHLINEARCH]
EXEC sp_rename N'[dbo].[TempLEDJOURNAME]',N'LEDJOURNAME', 'OBJECT'
And it goes on, including INSERT INTO ... FROM et cetera.
Anyone who got a great idea on how to execute this script, in some clever way? I've tried a procedure, but there's multiple problems with that, including the proc needs to be on the current database, and doesn't work with things like ALTER TABLE etc.
August 30, 2012 at 2:43 pm
Is your question about which apps submit tsql to sqlserver?
Or is your question about how to get rid of the need for dynamic sql?
August 30, 2012 at 3:20 pm
There is not going to be an app submitting the script, I need to figure out how to execute this script, on demand, without involving external components.
The script runs perfectly in Query Analyzer, but alas, it needs to be done programmatically on the SQL Server.
Using dynamic SQL poses other kinds of problems, one of them, is there seems to be a limit at 4000 characters pr. variable, and currently, the script is 221'000 character big. And well, it's ugly.
August 31, 2012 at 1:55 am
Just clarifying what you mean by 'external components' - I presume you are including SSIS and SMO routines?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 31, 2012 at 2:00 am
Which tool are you using to generate the script?
Why cant you use a command line SQLCMD to run this into the target server?
One other option is that you could look at this article about VSDBCMD,
http://msdn.microsoft.com/en-us/library/dd193283(v=vs.100).aspx
It may help, its built into VS2010 (Premium or Higher) and on VS2008 with DB projects, you can also download it as a free tool from MS.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 31, 2012 at 4:12 am
Phil Parkin (8/31/2012)
Just clarifying what you mean by 'external components' - I presume you are including SSIS and SMO routines?
I'm not sure if this includes SSIS/SMO, that depends on whether or not I can activate this from a ODBC/OLE connection?
August 31, 2012 at 4:19 am
Jason-299789 (8/31/2012)
Which tool are you using to generate the script?
I'm using DbDiff, works great.
Why cant you use a command line SQLCMD to run this into the target server?
Because the script have to run on a highly restricted environment, and it has to run inside an application which have to execute the script (via OLE/ODBC). Also this program have numerous restrictions, like the max length of a string is 999, so I'm aiming at having the SQL Server executing the script.
One other option is that you could look at this article about VSDBCMD,
http://msdn.microsoft.com/en-us/library/dd193283(v=vs.100).aspx
It may help, its built into VS2010 (Premium or Higher) and on VS2008 with DB projects, you can also download it as a free tool from MS.
Many thanks I'll look into it, but it looks like an external program, which won't work.
August 31, 2012 at 4:32 am
I didnt fully understand the requirements. VSDBCMD wont help in that case, but its a useful tool to know.
I take it the third party tool thats doing the deployment is so that theres a repeatable and automated process (Accenture use such a tool but I cant remember its name), however from memory it did accept whole Db scripts and execute them without issue, all you did was give it the filename that it needed to run and away it went.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply