The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
Is there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ? Thanks, Terry
Folks, what script must I use, as a part of CREATE TABLE, to automatically convert characters to UPPER case on insert? I wrote <CHECK (country = UPPER (country)> in the CREATE TABLE, which was wrong, because the values were still in the lower case. The sample script is:
CREATE TABLE address (street varchar(40), city varchar(20), state char (2), zip varchar (10), country varchar (20))
When a user types "Canada", I want the inserted value be "CANADA"
Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional. Thanks in advance for the help.
Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.
I have a problem. I need to rename all columns of a database to uppercase. Since SQL SERVER 2005 does not support changing system tables is there a smooth way to do this? Has anyone ideas for a script? point me to the right direction. I have found the stored procedure sp_rename which could be useful (or would it be better to alter the tables)... So any help would be appriciated very much...
Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.
There are some exceptions (email addresses for one). I don't mind going through each field and changing something.
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.
An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?
Do we have provision of separating sa and sso role in SQL server as we have in sybase? ( In such a case, sa shouldn't have any control on creating/modifying users/logins)
I'm kind of new to sql server (but experienced in Oracle) and I've got a couple of questions I wanted to bounce off you guys.
I'm implementing a SQL server cluster right now (2 node on Win2K3, shared EMC DASD for databases). We're at the very preliminary phase of this. I did an install and had my resource group set up with all of my disks on it. When prompted for the data file drive, I gave it one, but it put all the tlogs for the 'out of the box' database on that same drive as the data files (i.e. master, model, tempdb, etc.). The doc is a little vague in some of these areas (i.e. it says separate logs and data files on different disks, but then never actually tells you how to do that).
Now, I know how to specify the default paths for data and transaction logs for any NEW database I create and that's not a problem. However, my question is, how do I 'move' the tlogs from the databases created during the install? I've tried a detach, move tlog to separate physical drive and then reattach the db, but whenever I do this, SQL server wants to create a new tlog for the db on the same old drive as the datafile. I also can't delete the original tlog from a particular database even after I've created an additional tlog on another disk.
Any help is much appreciated. I'm more or less looking for the strategy any of you might take to set up this initial phase.
We have an environment/application where we have separate databases for different clients. Each database is pretty much a copy of the other - i.e. same tables, same stored procs, and a few reference tables whose data content is the same across all the dbs - except for that client's data in the user tables.
The issue we have is one of maintenance and growth. We wanted separated DBs so the amount of data in one would not result in performance issues for another client among other things (security and geographical separation for example). However, now that we are adding more clients the deployment team has to apply changes (schema chagnes, stored proc modifications) to each and every database. Does SQL (2005 SP2 in our case) support a concept of shared resources like schema, stored procs, user functions, and even a few tables but separate out the data for other tables? That way the schema, reference data changes have to done in one place...
I just want to verify my understanding of schemas...
Using separate schemas to segregate data is only a logical mechanism. Separate schemas do not physically segregate data onto different groups of pages or files. Partitioning is the mechanism for physical segregation of data.
I have a client who is in the market for a dedicated SQL box. It's a smaller company of 50 users. The primary task of this machine is a backend for Sharepoint 2007. At first I'm stuck using MSSQL 2000 for a few months until the budget can be approved to upgrade them to 2005.
I intend to spec this machine out for 2005, the question I have at the moment pertains to the HD configurations.
How would you recommend rigging that up?
Mirror for system, Mirror for the data store, mirror for transaction/temp files?
Put everything on a Raid-5 and forget about it?
I also have a question about Quad Core processors. How does MS sql's licensing work on that?
I guess I just don't get the reasoning behind the new SqlDataSource control. Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET? In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display? It doesn't make sense. Why would anyone do this? And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
I was wondering what more experienced DBAs have observed with regard to the capacity of a MSSQL DB. Is there an upper threshold of rows where performance becomes unacceptable? I have a fairly slow, but constant input rate of approximately 2,000 rows every 60 seconds or so (that is a little high, but I'm interested in worse case scenario here). That is up 172,800 rows a day. (I'm being overly pessimistic here.) We'd like to be able to keep all of this around as long as possible.
Or would a more heavy duty DB be in order for these sorts of data rates?
Hi expert, I would like to ask regarding the UPPER function in SQL Query. I was tryin' to create a scipt that will give me a result of all the names that are in UPPER case format, but when I tried to execute the script the result is not right, it also retrieves all the records that are in PROPER case.
SQL Script: SELECT id, name FROM table_1 WHERE UPPER(name) LIKE 'DAR%'
@DeptID nvarchar(10) ) As If Exists ( Select DeptName From Departments Where DeptID LIKE @DeptID ) Return 1 Else Return 0
Now I want to apply replace and upper functions to DeptID in database before saying "DeptID LIKE @DeptID".
for example the parameter is :"D&V" DeptID in database is:"d & v" //there are spaces
if I say DeptID LIKE @DeptID nothing is found because of character nonmatching So I have to apply replace & upper functions to the column DeptID in database
My SQL Server database is not case sensetive. How can I compare like cluase with search for capital and small letter? For example SELECT add1 from xcty_all where add1 like '%AL'%' I need only ................... 10 ltncewwod way AL 456 Ruio St. AL NOT
There may/may not be an upper limit for the number of rows in a table, but is there any performance-related limit?
I'm designing a database that stores results that have been acquired from a number of devices. Each device provides a set of data measurements every 10 minutes. Therefore each year a device will produce 52000 sets of results. If I design a table to store a row for each set of measurements from a device (PK is based on the timestamp and the deviceID), and if there are 100 devices recording for 5 years, there will be 52000x100x5 rows. Would I get a performance increase by separating this data into one table per year? Perhaps the year could be appended to the table name to identify the particular tables.
A secondary issue is some devices can also be configured to produce a different set of measurements every 10 seconds. In this case there will be hundreds of millions of rows over a 5 year period. Therefore I am considering bulking the results into an array for a 10 minute period, and storing this array as a blob each 10 minutes. Is this going to be faster or slower than having hundreds of millions of rows?
