SQL Server Performance Forum – Threads Archive
Passing variables to ALTER DATABASEA little new on the rules of T-SQL. Trying to pass a variable to the ALTER Database command: declare @newsize as char(3)
SET @newsize = ‘3MB’
ALTER DATABASE Northwind
(NAME = Northwnd,
SIZE = @newsize) Errors out. Is this possible? I have tried int and varchar and get the same error.
You would need to use dynamic SQL, where you prepare the whole script as a string, concatenating the variables, then executing the string.<br /><br />However, why would you want to use variables here? This sort of script is obviously intended to be executed once, so there’s absolutely no point in creating re-usable code. Just put the values where you’ve put the variables.<br /><br />In terms of T-SQL, you should be cautious with re-usable code. It may have its place, but don’t be surprised if you run into performance issues, or find you’ve painted yourself into a corner.<br /><br />If you’re coming from a programming background, you’re in for a lot of fun – unlearning recordset loops and stuff.<br /><br />Enjoy the ride![<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Actually this is not the entire script. I just pasted a segment where is was having an issue. The script will actually be run in a job every 4 hours to check the free space of a particular db. The autogrow function was causing disconnects from another vendors system. I am basiacally re-inventing the wheel and creating my own version of auto grow that executes if the amount of free space falls below a predefined level, say 500MB. It is really a failsafe if the DBA does not do his/her job in monitoring db growth. I was just looking at dynamic sql this morning right after posting this. I will try it out. Thanks
Why not run assign a size to the data file and monitor it on weekly basis and then increase the size in order to addup intermittent workaround. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
It is possible and one of the most frequently referenced article about that topic in general you can find here:<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html>http://www.sommarskog.se/dynamic_sql.html</a> Although it’s a quite long article, make sure you read closely through it. It’s a good read. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)