Okay, so i've been working in foxpro for a while, but now we're moving on to bigger stronger faster. my question is, in foxpro, i used to be able to fix some name formatting issues with two commands:
replace all fname with subs(name,1,at(' ',name))
replace all lname with subs(name,(at(' ',name)+1),(30-at(' ',name)))
this basically locates the space in the name on each record, and replaces the first name with 0-space and the last with space-end of field
little cude, but it gets the job done. so basically i'm looking for the same solution but for t-sql, i tried something like:
update t1
set name=substring(name,1,at(' ',name))
from temp t1
with no luck, AT() is not an expression in sql, so what is out there that's like it?
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
I made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
I need to create a proc. which would send a warning via xp_sendmail when the database space gets within 10% of allocated space. I have written one which uses sp_spaceused however this proc requires DBCC udateusage to be run in order to get accurate results. This would work but i don't know how quickly the data returned by sp_spaceused becomes corrupt therefore how often DBCC should be run. I ran DBCC on our main production server and it took a considerable amount of time. Basically I am looking for another way to do this without using sp_spaceused. Any ideas
Does anyone have any knowledge on how to find out the log space left in a database? I've tried to use the DBCC sqlperf (logspace) and it gave me the numbers but I only need one of the numbers. How can I retrieve it? Someone please let me know if you have any info. Thanks a million.
The command sp_spaceused @updateusage = true returns following result. How do I have to calculate the single values that it gives me an equation: database size = data size plus reserved space minus etc. Somehow it is not clear to me how it is split up.
Thank you
mipo
database_namedatabase_sizeunallocated space ------------------- ----------------------------------- opms 3498.88 MB807.41 MB
I have a 12 GB database growing at 25 MB daily. The server will soon run out of space, 3 GB left. I cannot buy a new server or new RAID subsystems at once, and want to prevent the server being filled up fast.
What are the best ways? Can you share with me some of your thougts or comment on the following: 1) Archive old data in many tables across servers - Can SQL Server do it directly across server or we need to select into temp database and back it up to tape or using bcp out and in? 2) Put the >9Gb disk dump to another server. The daily disk dump takes up a lot of space. 3) Dump to the tape directly, saving 9 GB disk space of dump device. 4) ? ?
Which table in the master database has the information about free space available for a particular database? I am trying to find the information that is displayed under the general tab in EM when you look at the database properties.
I have a database with almost 3 tables each contains atleast 1 million Rows, and My hard disk space is 14G.
I want to insert another table some queries i have selected from different tables.
I got this Error:
(254118 row(s) affected)
(935885 row(s) affected)
Server: Msg 1105, Level 17, State 2, Procedure sp_ProcessRowData, Line 68 Could not allocate space for object 'Processed_Data' in database 'Mydb' because the 'PRIMARY' filegroup is full.
Hope this question is not too dumb for this forum.. but here goes.
I have a DB on SQL Server 2K which is the backend for our Great Plains System.
When I look at the properties of the Database from Enterprise Manager, It shows Size: 1370.57mb and space available: 0.00 The ZERO space available is what I'm concerned about. The DB is in FULL recovery mode with a nighly Full backup and transaction log backups throught the day. The DB is set to autogrow and the file growth is set at 20 percent.
My question is.. should I be worried about the ZERO space available and if so, how do I correct this??
Hi, Needs helps. I have a name filed in my table in sql. My name field combines first name middle name(initial) and last name. I need to combine the name into one string. Example if the name is John K Smith - I need to be JohnKSmith. I try to use replace function which I thought could be best, but is not working- may be I'm missing something. Please, I need your help!
Dear All My query is reagaing the SQL server space Imagine a situation where i have purchased server space of 50 kb for my sql server and at the time of hosting of my web site the size of the data base was 40 kb, once it is hosted and as the data of users and visitors get stored into the database,it gets expands and the size if the size of the database exceeds the the occupied space,will my database get stuck or..any other option to overcome this senario or Do i have to get more space periodically. Help me out Thank you
Hi, how does one normally insert a space in a statement like this: INSERT INTO table (column0, column1, column2) VALUES (getdate(), 'blah', CONVERT(VARCHAR(19), GETDATE(), 120) + 'blahblah')In column2 the output looks like ' 2007-10-08 20:19:08blahblah', but I want it to be like '2007-10-08 20:19:08 blahblah' (two spaces between date and text).Thanks,Chris
I made a table which is consist of three text data type and a datetime data type. I want to change from text data type into varchar datatype. My question is which datatype varchar or text is taking more space memories from your harddisk ?? Or do u have any suggestion ? Thank you very much
I'm using MSDE and I'd like to set a default value of a space (or spaces), and in design-view, it lets me enter a space. But it is interpreted as an empty-string "" which gets converted to Nulls when it is moved to another string field? I'm not sure why. Is there any way around this?
I have a sql server 2005 DB.When I define a column as a nvarchar(50) and fill it with one character (for approx 10000 rows), how much space is taken physically on the HD?Also, if I then redefine the column (as it only contains 1 char) to nvarchar(1), is the physical size on the HD reduced immediately or do I have to perform some special action?
Hi, I work on SQL Server 6.5, and I want to make a documentation about available space of my databases. So, I must know how many databases space used everyday ?
If somebody know how to get this available space or how to make this script. I hope you want to tell me about this. Thanks you so much for your cooperation.
What will the net result be if I limit the tempdb size, and it grows to it's limit? Will it crash my server? Will it shutdown SQL? I will most likely be moving it to another drive, but until then I can't have it consuming my entire drive like it did 2 days ago! Please Advise. - Rob
If I right-click on a database and choose Edit, on the Database Tab Enterprise Manager displays the Data Space Available to be 0.00, even though I know there is atleast 10GB spare !. Any ideas ? Thanks.
I have a SQL 7.0 database from which I have recently deleted a large amount of data. The allocated size of the DB is 107GB. The currently used amount is 104GB. The max size of the partition containing the database files is 108GB. I am estimating the amount of deleted data to be about 25GB. I have not seen any gain in the amount of space used. I have attempted to run an optimization job in hopes to return the space, but it fails when it reaches one of my large tables. The error message states "Could not allocate space for object 'xxxxx' in database 'DDD' because the 'PRIMARY' filegroup is full."
I am fairly new to SQL 7 admin and I am wondering if there's a way to reorganize the DB to gain back some free space without adding more disks.
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space. Should I allocate 1GB to the primary files and 1GB each to 3 secondary files? Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
The transaction log of the databse i am using has grown up to 7GB...(previously the setting was unrestricted file growth...now changed to restrict file growth to 7 GB approx.) now this 7GB space is not needed....i would like to reduce the size to around 2 GB...how can i achieve this?i observer that on the properties i can only increase the size and not decrease it...also i am using transactional replication..this server is the publisher to four subscribers..
