SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Dynamically Created Stored Procs??
Okay. Scenario:
We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.
From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.
This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.
So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?
Or if not, is there some round-about yet effective way of doing this?
Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).
When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.
If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?
The structure of the where statement can cause the execution plan to be changed.
So my question again is why you wouldn't want a dynamically created stored proc for this.
Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.
Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.
n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes
Every new request becomes a log of the report in a table.
The get a list and select what they want... the table stores the proc name which gets executed
You'll come to a point when you've created almost all variations.
And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long
You need to build a process to denormalize the data so they pop...
I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.
Don't look now...but your Oracle is showing...
:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.
And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...
ever wait MINUTES for a web based report?
We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.
From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.
This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.
So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?
Or if not, is there some round-about yet effective way of doing this?
Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).
When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.
If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?
The structure of the where statement can cause the execution plan to be changed.
So my question again is why you wouldn't want a dynamically created stored proc for this.
Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.
Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.
n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes
Every new request becomes a log of the report in a table.
The get a list and select what they want... the table stores the proc name which gets executed
You'll come to a point when you've created almost all variations.
And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long
You need to build a process to denormalize the data so they pop...
I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.
Don't look now...but your Oracle is showing...
:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.
And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...
ever wait MINUTES for a web based report?
Friday, March 9, 2012
Dynamically change the Image Location
I've hunted all over for the answer to this without success...
I'm trying to display a company logo at the top of the report, but read from
the database whether the logo is left justified, right justified, or
centered. I figured I would just adjust the Left property of the image to
get it left/right/centered, but I can't find any way to modify the Location
of the image other than to set it at design time.
I found one suggestion to stretch the image control and use padding to
"center" the image, the problem however is that I also need to dynamically
locate the company address depending on the location of the logo. <sigh>
As usual when I can't find a solution, I'm getting that ominous
"it's-right-in-your-face" feeling...
Thanks,
JLSTom, I am running into the same issues. I want to dynamically change the
<Top> position of an image depending on a value passed to a Function. I am
not having any luck either. Just wondering if you found a work around?
Thanks, Phill
"Tom Rocco" wrote:
> I've hunted all over for the answer to this without success...
> I'm trying to display a company logo at the top of the report, but read from
> the database whether the logo is left justified, right justified, or
> centered. I figured I would just adjust the Left property of the image to
> get it left/right/centered, but I can't find any way to modify the Location
> of the image other than to set it at design time.
> I found one suggestion to stretch the image control and use padding to
> "center" the image, the problem however is that I also need to dynamically
> locate the company address depending on the location of the logo. <sigh>
> As usual when I can't find a solution, I'm getting that ominous
> "it's-right-in-your-face" feeling...
> Thanks,
> JLS
>
>
I'm trying to display a company logo at the top of the report, but read from
the database whether the logo is left justified, right justified, or
centered. I figured I would just adjust the Left property of the image to
get it left/right/centered, but I can't find any way to modify the Location
of the image other than to set it at design time.
I found one suggestion to stretch the image control and use padding to
"center" the image, the problem however is that I also need to dynamically
locate the company address depending on the location of the logo. <sigh>
As usual when I can't find a solution, I'm getting that ominous
"it's-right-in-your-face" feeling...
Thanks,
JLSTom, I am running into the same issues. I want to dynamically change the
<Top> position of an image depending on a value passed to a Function. I am
not having any luck either. Just wondering if you found a work around?
Thanks, Phill
"Tom Rocco" wrote:
> I've hunted all over for the answer to this without success...
> I'm trying to display a company logo at the top of the report, but read from
> the database whether the logo is left justified, right justified, or
> centered. I figured I would just adjust the Left property of the image to
> get it left/right/centered, but I can't find any way to modify the Location
> of the image other than to set it at design time.
> I found one suggestion to stretch the image control and use padding to
> "center" the image, the problem however is that I also need to dynamically
> locate the company address depending on the location of the logo. <sigh>
> As usual when I can't find a solution, I'm getting that ominous
> "it's-right-in-your-face" feeling...
> Thanks,
> JLS
>
>
Dynamically change datasource
Is it possible to dynamically change the datasource in a report? I have a database for each company and I don't want to have to replicate each report for each datasource.
Thanks,
Duncan
Moved to Reporting Services.|||Hi Duncan,
If your databases are identical you can define them to the report server as linked servers, and build your sql statements for report datasets as dynamic sql statements.
Pass the linked server name as a parameter and use that parameter value in the dynamic sql.
Set data source as :
"Exec " & Parameters!LinkedServer.Value & "." & Parameters!DBName.Value & ".dbo.SampleStoredProcedureName " & Parameters!SampleParameterName.Value
|||Also check this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16395&SiteID=1
-- Robert
Wednesday, February 15, 2012
Dynamic SQL in procedure compiles but does not run
The reason why is because we offer several "lines of coverage" One company may use our app for Workers' Comp and has it's own set of tables. They may use it for Liability that too has it's own set of tables and they may have both. And by using only one usually means that is th eonly set they have.
We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
Well, it is parsing all statements regardless of the instructions from the parameters. Anyone know of any tricks to get around this?I'm goint to try removing the IF statements for the lines of coverage and just do something like:
case @.lob when 'WC' then 'WC' else 'GL' end + 'CCLM1'
I suspect this will work just fine.|||I found the cause. I had hard coded the wrong line of coverage in an area of the procedure so that explains why it was looking for the opposite tables! I'm still going to do the case statement above. Since I've got it in one proc I should cut down what was four queries to two!|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"|||[QUOTE=DBA-ONE]We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
QUOTE]
But you ignore the fact that the optimizer will recompile your Dynamic SQL
** every **
time it executes, so you are trading writing four efficient procs running well for one extremely inefficient proc that runs poorly.:(|||That is actually fine. Because these reports are not run every second of the day so the overhead on the server is much more favorable then having to change several procedures and statements. I'd much rather update one procedure, one file and have to put that one file in a single location. See, we support Oracle and SQL Server so for each procedure I need one for comp, one liability, and one each of those for SQL and Oracle. Now do you see why I'm willing to give up a little on the server end to make the maintinence easier? When we roll something out it can be an enourmous pain having to put all these files in different places.|||By the way, I test the old version to the new. While the optimizer may have more work to do there was no difference to speak of in the time to execute. Not bad for my sloppy procedure huh?|||Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.|||Clients may execute these once a day. Usually at month end.|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"
At least it doesn't happen too often!
We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
Well, it is parsing all statements regardless of the instructions from the parameters. Anyone know of any tricks to get around this?I'm goint to try removing the IF statements for the lines of coverage and just do something like:
case @.lob when 'WC' then 'WC' else 'GL' end + 'CCLM1'
I suspect this will work just fine.|||I found the cause. I had hard coded the wrong line of coverage in an area of the procedure so that explains why it was looking for the opposite tables! I'm still going to do the case statement above. Since I've got it in one proc I should cut down what was four queries to two!|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"|||[QUOTE=DBA-ONE]We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
QUOTE]
But you ignore the fact that the optimizer will recompile your Dynamic SQL
** every **
time it executes, so you are trading writing four efficient procs running well for one extremely inefficient proc that runs poorly.:(|||That is actually fine. Because these reports are not run every second of the day so the overhead on the server is much more favorable then having to change several procedures and statements. I'd much rather update one procedure, one file and have to put that one file in a single location. See, we support Oracle and SQL Server so for each procedure I need one for comp, one liability, and one each of those for SQL and Oracle. Now do you see why I'm willing to give up a little on the server end to make the maintinence easier? When we roll something out it can be an enourmous pain having to put all these files in different places.|||By the way, I test the old version to the new. While the optimizer may have more work to do there was no difference to speak of in the time to execute. Not bad for my sloppy procedure huh?|||Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.|||Clients may execute these once a day. Usually at month end.|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"
At least it doesn't happen too often!
Subscribe to:
Posts (Atom)