August 26, 2011 at 9:15 am
Re: Little Bobby Tables
I haven't heard that reference in a while. Thanks for the laugh...
BTW: The 'documented' capacity for SQL Server databases on one instance is listed as 32,767 but in reality it will be a bit less, depending on your hardware and resources. (Just in case your nifty stored proc gets caught in a loop or the end-user calling it has an itchy trigger finger...)
September 19, 2011 at 11:11 am
I am in need of a very similar situation and agree that hard-coding the database name into the procedure is a bit pointless. I would like to create such procedures but with dynamic database names. Would I be able to pass the database name into the procedures as a parameter and perform something like a "use" command or would I have to use dynamic SQL?
Shane.
P.S. I have several hundred statements to run when inserting the seed data into the new tables of the new database. Is dynamic SQL my only option?
[ Edited : Sorry for the premature post. I did not see there was a second page to this post outlining a dynamic SQL option. ]
September 19, 2011 at 11:14 am
You are in the same boat. If you are doing this totally in a stored proc you would either have to use dynamic sql or create clr stored proc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 19, 2011 at 11:16 am
sbentz 2061 (9/19/2011)
I am in need of a very similar situation and agree that hard-coding the database name into the procedure is a bit pointless. I would like to create such procedures but with dynamic database names. Would I be able to pass the database name into the procedures as a parameter and perform something like a "use" command or would I have to use dynamic SQL?Shane.
P.S. I have several hundred statements to run when inserting the seed data into the new tables of the new database. Is dynamic SQL my only option?
It depends on what precisely you're trying to do.
You can't use USE inside a stored procedure except in dynamic SQL, but there are other ways to tell a script what database to work in. It depends on what you need to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 11:25 am
We have the database creation and object creation scripts already taken care of. I simply need to figure out a way to efficiently create the metadata within the tables. Here is psuedo-code of what I am trying to accomplish. I realize this syntactically will not work:
Generic metadata creation script in a standard generic database:
create procedure [genericdatabase].dbo.[create_datafield_metadata]
@dbname sysname
as
begin
use @dbname
insert into datafield_table ( datafield_name, datatype_id, default_value)
values ( ‘COLUMN1’, 15, null )
insert into datafield_table ( datafield_name, datatype_id, default_value)
values ( ‘COLUMN2’, 64, null )
insert into datafield_table ( datafield_name, datatype_id, default_value)
values ( ‘COLUMN3’, 32, null )
etc….
end
Database-specific script in a newly-created database:
create procedure [newdatabase].dbo.[create_datafields]
as
begin
exec [genericdatabase].dbo.[create_datafield_metadata] 'newdatabase'
end
Each generic metadata script will have hundreds of INSERT statements and I am not looking forward to re-writing them all to turn each quote into a double-quote for the dynamic SQL to work. Any thoughts?
Shane.
September 19, 2011 at 11:31 am
Will the objects and the metadata be the same in all of these databases?
If so, save yourself significant time and work, and create one copy of it, then do a backup of that copy. You can use Restore to rename the database to whatever you want (so long as it complies with database name rules), and it's dead easy to script a restore statement with a dynamic database name in it.
That way, you have one step to do each time. Much easier, and it'll be easier to refactor as well, since you just do the work in the template database and then run a new backup.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 12:22 pm
Yeah, it would be nice if it were that easy.
Our environment is such that each database is a lego-creation of different applications. We evaluate or grade images of eyes to determine whether a drug is helping the person to see better or worse. Each new database does contain at least a base set of tables to record the metadata about the images. Then, depending on the type of evaluation or grading of the eyes, there may be different grading tables in the database, one table for the different types of gradings. Some databases might have just one grading table, others might have 4-5 depending on which type (or types) of gradings we need to do on the set of eyes in that database.
Perhaps we can do the backup/restore method with the core set of tables which hold the metadata, but we still need to add in the extra grading tables that would not necessarily be a part of every database creation.
Shane.
September 19, 2011 at 12:36 pm
is there some sort of logic which can be used to decide whether the grading tables belong or not, or is it a conscience decision by someone after the database is created?
would it hurt anything if the grading tables existed, even if they were not used?
Lowell
September 19, 2011 at 12:40 pm
Yes, I believe there is a place in each database which indicates which "grading modules" will be a part of that database.
It was discussed a long time ago about putting ALL the grading modules into a database even if they aren't used, but that is currently not the technique and investigation would need to be done to determine any inadvertent side-effects.
September 20, 2011 at 6:08 am
Alternatively, are there a finite number of variations on the grading tables? If so, make one backup for each common variation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 20, 2011 at 7:27 am
Just to clarify on the scope of stored procedures, as I am new to SQL*Server development --
When executing a stored procedure, the scope of that stored procedure is contained within the database that stored procedure resides. If calling on a stored procedure from a separate databse, the functions within that stored procedure can only be applied to the database in which it resides unless you explicitly use the 3- or 4-part naming convention.
Is that correct?
September 21, 2011 at 5:19 am
Hi GSquared,
Your comment is very much useful for me:-)
September 21, 2011 at 5:29 am
sbentz 2061 (9/20/2011)
Just to clarify on the scope of stored procedures, as I am new to SQL*Server development --When executing a stored procedure, the scope of that stored procedure is contained within the database that stored procedure resides. If calling on a stored procedure from a separate databse, the functions within that stored procedure can only be applied to the database in which it resides unless you explicitly use the 3- or 4-part naming convention.
Is that correct?
It's always applied to where you ask it on the code.
If you don't specify the DB, then it's going to be done "locally".
Pretty much only master and dmvs can change there own execution context to go against that rule.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply