SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Database names
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/26/2006 :  07:40:45  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Jason writes "We currently have a management application that allows users to create a database, using a user specified database name.

I need to determine whether the name they have entered is a valid name (ie. it meets the criteria as a valid SQL Server identifier name, as per the Online books documentation.[See Create Database syntax]).

Is there a function within SQL Server to determine this? I have searched high and low, and there doesn't seem to be any references to this sort of check that I can find."

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/26/2006 :  07:53:10  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
list of database attached on the server

Select * From Master..Sysdatabases

Chirag
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 09/26/2006 :  10:35:28  Show Profile  Reply with Quote
quote:
"We currently have a management application that allows users to create a database, using a user specified database name."


I am SO SORRY to hear that.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/26/2006 :  11:06:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
That sounds really a bad design. Why do you want create database for each user?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/26/2006 :  11:19:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sounds like a web-hotel solution.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ws5926
Yak Posting Veteran

USA
88 Posts

Posted - 09/26/2006 :  11:31:18  Show Profile  Reply with Quote
"We currently have a management application that allows users to create a database, using a user specified database name."

Gotta ask - WHY???

Live to Throw
Throw to Live
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/26/2006 :  11:44:59  Show Profile  Reply with Quote
I don't think there are any restrictions..just wrap the crete statement with []


CREATE DATABASE [Why would any #1 ever think to do this database!!!]


Which actually works



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/26/2006 :  11:53:07  Show Profile  Reply with Quote
who cares why Jason wants to do this. There are many scenarios where a self-service environment like this would be useful.

Anyway, to answer the question - There is no built-in function for you to use that will do this. However, you can certainly build a function that will parse the proposed name the end-user has submitted and reject ones that don't meet your criteria.

If the name passes this check, then you can simply pass the name to the proc you are using that creates your new databases.



-ec
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/26/2006 :  12:09:09  Show Profile  Reply with Quote
You would just have to handle the error

DECLARE @error int
CREATE DATABASE [Why would any #1 ever think to do this database!!!!#$%^*&(*()_]
SET @error = @@ERROR
IF @error <> 0
PRINT 'Handle Error'
ELSE
PRINT 'No Error'




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/26/2006 :  13:41:44  Show Profile  Reply with Quote
Why don't you invent your own standard? A name starting with Alpha and containing only AlphaNumeric and "_" is safe for any sort of name, provided it is not a reserve word.

I think this would be preferable to allowing "anything" because a name containing weird characters, although "valid", might cause something that is not expecting the weird characters to fail. Likewise ridiculously long names are OK, but make life difficult trying to choose a database in the drop down list in QA and so on.

I would go for 30 characters max, [A-Z0-9_] and forced upper case.

IF @strProposedName LIKE '%[^A-Z0-9_]%'
BEGIN
    SELECT [ERROR] = 'Database name is invalid'
END

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000