I have a Stored Procedure that creates permanent tables in our database. Multiple users will run the SP; however, when I run the SP, the tables it creates are created on dbo, not my own schema; other users will have the same issue.
It seems a bit much to ask the user put their schema into an SP argument, so is there a command I can put into the SP that will ensure that all table creation will be done in the schema of the user who is running the SP? Sort of like:
USE [dbname] GO..but for schemas?
You could use dynamic SQL to make the CREATE TABLE commands dynamically include the schema of the user running the code.
Somewhat obscure, but you could have the code create the table in a dummy schema, then at the end dynamically alter the table owner to be the caller's name/schema. This would prevent a 'dbo.' version of the table from being created.
"You could use dynamic SQL to make the CREATE TABLE commands dynamically include the schema of the user running the code."
Yes, that is what I am trying to do. So, your comment gave me a clue as to where to go next with a google search and it turns out that I can assign a variable to the current user as:
set @user = user_name()
I can put this in dynamic sql and place the table location appropriately..
So, that worked, but now wanted to add an additional piece to the dynamic sql. I wanted to place a statement checking to see if the table is there (or 'not null' in the cases of the function I'm using), but my syntax appears incorrect. I am trying to place all of the code in the single exec command (i.e. exec (sql_cmd) - where sql_cmd hold the syntax)..
This is what I have:
'IF OBJECT_ID(''[' + @user + '].' + @curtblname + ''')' + ' IS NOT NULL DROP TABLE [' + @user + '].' + @curtblname +
'SELECT * INTO [' + @user + '].BK_' + @curtblname + '_' + @date + 'TASK' + @task_id +
' FROM ' + @curtblname
So written out non dynamically:
IF OBJECT_ID ('[schema\mark2].project') is not null drop table [schema\mark2].project
select into [schema\mark2].BK_Project_20171101_1000
from dim_project
I think I might be going wrong where I have to put the quotes into the table name in the OBJECT_ID arguments, I have 3 quotes in the dynamic part to take care of the schema and table name part of the above non dynamic part (i.e: OBJECT_ID ('[schema\mark2].project') ..
Can you see anything obvious I am missing syntax wise. Or perhaps do i have to do the OBJECT_ID check as a separate command exec from the part where the table is built?
That's probably a bit iffy ... presumably @user might be all sorts of characters. Suggest you use
QuoteName(@user)
instead which will, hopefully!, take care of any embedded characters.
That said, if you can force User Names to be A-Z and "_" that would certainly reduce the risk of having names that include spaces and rogue punctuation that mucks things up in future.
Personally I would use CREATE TABLE rather than SELECT ... INTO because there are limitations of SELECT INTO - constraints, indexes, etc. but some other things (sometimes unexpectedly!) like IDENTITY are preserved.
Maybe script the dim_project etc tables and store in a database table, and substitute the variable parts with {UserName} tags, or similar, and then use REPLACE on the Dynamic SQL and EXEC the resulting code.
The object you are checking exists for, and then pre-dropping, does NOT include the date part - is that the issue?
drop table [schema\mark2].project
select into [schema\mark2].BK_Project_20171101_1000
But the object name that you're looking for is not the same one you are creating. I'd suggest something like below, both to insure a consistent object name and to make the code that constructs the SQL statement easier to read and maintain.
DECLARE @object_name varchar(128)
SET @object_name = '[' + @user + '].[' + @curtblname + '_' + @date +
'TASK' + @task_id + ']' /*Edit: Added closing ']' */
SET @sql_cmd = 'IF OBJECT_ID(''$object_name$'') IS NOT NULL DROP TABLE $object_name$
SELECT * INTO $object_name$ FROM ' + @curtblname
SET @sql_cmd = REPLACE(@sql_cmd, '$object_name$', @object_name)
PRINT @sql_cmd
EXEC(@sql_cmd)
And I would not. I do not want to allow brackets in an object name, i.e., I want to get an error if someone put brackets in a table (or other object) name, I don't want to just gloss over it.
I'm ambivalent on that , although I agree that having all sorts or any characters in Table/Object Names in the database is a nightmare - but if that's the user name (not even sure it would be easy/possible to create - but assuming it is) then I would want to allow it. But that's probably because I know that changing a typing mistake in a User Name here is a nightmare - AD stuff already created, email accounts, all sorts ...
But ... assuming that some characters, such as Bracket, are not wanted, then I would want to test for that and present a useful error message to the user, I certainly would not want the application bailing-out with a SQL error - that would lead to a support query and time spent figuring out what had gone wrong. A "User name contains illegal characters" or similar data-validation-message presented to the user would be far more preferable to me in the hope that the User would be able to figure out a solution on their own, or at least we start with knowledge of the cause of the problem.
But it might be that our error handling in such cases (i.e. a runtime SQL error) is pants! and that you have a more slick solution? We would abort the application (web page in our case), display an error message so the user knows the APP is bust, offer the user a text box to type in some info, and then it gets escalated to Support. Even if the user does nothing an error which caused the APP to abort would be investigated.
We have a generic error catch at a high level, and that would be good enough for me here. i wouldn't spend too much time on such an obscure possibility. Just don't have time for such things where I'm at now.
However, If I were to go to the trouble to provide a more-specific error message, I would make it genuinely useful. A message like:
“User name contains illegal characters”
would likely just be frustrating to someone who would put a bracket in a name in the first place.
Add a function or stored proc to produce a more specific list of bad chars in the actual value:
"The user name provided, "%s", is not valid because it contains one or more of the these characters, "%s"'.
That's roughly how the message would look when it was added to sysmessages, with the %s's begin replaced with specific values at run time, of course.
ok, thanks for all your input. We have a small group that will use the SP, so there are no issues surrounding the use of the user_id and any treacherous characters..
And yes, I didn't make the names the same! I don't know why I didn't catch this at first. I think maybe its because I forgot!
So, here is the finished product (or the offending looping section) for reference, which I finally got to work after finagling with the apostrophes and getting the names to match!
BEGIN
SET @cursor = CURSOR FOR
SELECT TBL_NAME FROM #ProjectTbls ORDER BY TBL_NUM
--place table names into cursor to be used by dynamic sql below..
OPEN @cursor
FETCH NEXT FROM @cursor
INTO @curtblname
WHILE @@FETCH_STATUS = 0
BEGIN
--Make dynamic sql:
SET @sql_cmd =
--if table exists, delete it..
'IF OBJECT_ID(''[VR].[' + @user + '].BK_' + @curtblname + '_' + @date + '_TASK_' + @task_id +
''') IS NOT NULL DROP TABLE [VR].[' + @user + '].BK_' + @curtblname + '_' + @date + '_TASK_' + @task_id +
--create the table..
' SELECT * INTO [' + @user + '].BK_' + @curtblname + '_' + @date + '_TASK_' + @task_id +
' FROM ' + @curtblname
--run sql..
exec (@sql_cmd)
--print @sql_cmd
--get next table name..
FETCH NEXT FROM @cursor
INTO @curtblname
END;
CLOSE @cursor
DEALLOCATE @cursor
END;
Yes, I'm with you there, and also "If its worth a message at all make it a good, clear, one", I skated over that ....
Just by way of discussion:
For "Not expected to happen" errors (rather than "If happen terminate the application"), such as data import where the arriving-data size is, now, too wide - external source changed, noone bothered to tell us - we have a Record Validation Errors table; the messages are obtuse (no point spending time on it, as you said, we have gazillions of things that could trigger and store an error, all intended "never to happen") we store Table/Column/PKey, Source that raised the error, and a message. The user reviews those and if it happens that they understand it they can act on it, but if not there is plenty enough information for someone technical to solve it quickly.
Some of the errors become semi-permanent solutions - e.g. the User tidies up the source data and reimports - until some future release where a more formal handler is added.
