How do I find the current size of each field in a table's column?
I have a table with a field for notes/memos. I need to see which ones are about to reach the size limit and what the current size is.
Does this make sense?
Thank you,
Karen
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
Hi All,
I am trying to find the nth business day in a month and in a quarter.
But I am not able to find any solution for this.
Could you please any one provide the scripts for me.
Thanks in advance...........
Hi,
I have created a DataSet that queries a table containing users names.
Code Block
SELECT * FROM myTbl
WHERE UserName = @Username
How do I automatically set @Username to the current user?
Thanks.
RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23
The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:
RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34
The code below is almost there but falls over on Recordno 8:
select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
[code]...
I have data similar to the below
CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
Hi,
Anyone got any ideas how to calculate the occupied row size of a table?
Hi All,
Do any of you know how I can find the size of a table in SQL server?
Cheers
Dave
Hii all,
I want to find the database , table size in MS SQL Server.
Is there is any query /procedures to find out the database and table size.
Thanks in advance .,
Regards.,
Sri
Our system guy wants me to find out just exactly how much room is being taken up by the SQL Express databases (I am using two). I am not sure how to do this. Is there any way to find the size, or, lacking that, how many records overall? We will probably need to move up to SQL 2005 soon, but he needs to figure out the memory requirements, etc.
Thanks,
Michael
If I have a table of 1,000,000 rows how I do find out what size this table is on disk?
And how do I find out the size of all tables on disc?
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
Hi,
is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...
The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...
Thanks,
In the "tblEmailGroupLink" table...
I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.
In the "tblEmailAddress" table...
The same "Emailid" field has a corresponding "EmailAddress" field.
What needed is all the email addresses found in the "EmailAdddress" field of these records.
I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.
Thanks,
Bill
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Any way around this?
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added? Thanks ahead for the help,Jason
View 4 Replies View RelatedI am trying to match records that are >= the current date. I have tried using:
SELECT DISTINCT name
FROM table
WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE())
ORDER BY name
but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?
After reading Dan Guzman's blog entry (http://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx) I started modifying some of my code to try it out and ran into a stumbling block. What size would you specific for a varchar(MAX) field?
Since a varchar max field can hold up to 2 billion chracters I really don't think I need to specify 2 billion as the size. Anyone have any ideas?
Hi,
I have MSDE installed on my computer and I'm using Web Data Administrator to manage my databses. The problem is that whenever I add a column with a length of more than 8000, I get the following error:
Length must be between 0 and 8000
If I create the column programmatically then i get this error:
The following error occured while executing the query:
Server: Msg 131, Level 15, State 2, Line 2
The size (8005) given to the column 'Article' exceeds the maximum allowed for any data type (8000).
I need several columns that can hold around
32,000 characters. What's the deal? Is this a limit with MSDE, or am I
missing something?
Thanks
Hi,
what happens when the autonumber field becomes bigger than MAX_INT?
If I get arithmetic overflow, how i can avoid this problem?
Thanks in advance
-december
I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.
The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor:
*The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037
*Field size too large
The table looks like this:
CREATE TABLE virtualdatalocations_waitingqueues (
dataid int ,
personid int ,
queueid int ,
CONSTRAINT FK_vw_dataid
FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE ,
CONSTRAINT FK_vw_personid
FOREIGN KEY(personid) REFERENCES persons(id),
CONSTRAINT FK_vw_queueid
FOREIGN KEY(queueid)REFERENCES waitingqueues(id)
);
It used to run fine in the past. I couldn't find any help on google or on forums.
Any help or comments are greatly appreciated.
Hi,
I have asked this question on 3 forums now and never get an answer, I don't know what is so hard about this question but I will try it here.
I am using SHA512 in C# to convert a password and its salt to hashed. I need to store the password hash and the salt hash in the database in two fields. I was told to use binary field to store the hash data and that the output of SHA512 would ALWAYS be the same no matter how long the password is.
I modified this hash example to use only SHA512 and to work with byte array instead of plain text.
All I need to know now is what size I need to make my binary field to hold this password that is hashed.
http://www.obviex.com/samples/Code.aspx?Source=HashCS&Title=Hashing%20Data&Lang=C%23
Say I have a password which is 30 characters max, and a salt which is 16 characters max. The password and the hash are stored in seperate fields in the same table. They are both hashed using SHA512 and are both being stored as byte arrays in C#, what size to I need to make the binary data type in order to hold the password, and to hold the salt.
Thanks!
Hi,I am using MSDE together with Enterprise Manager.I have a table with a field named description.This field will be filled by a web forms's textbox web control.The textbox's maxsize attribute is set to "3000" characters.What size do I have to adjust for my DB field description?Is the size of 3000 in Enterpise Manager equal to 3000 characters for the textbox?I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.
View 4 Replies View RelatedWe have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?
Thanks ahead of time
DMW
Edit:
We are running SQL Server 2000 and the db at the time was running in simple mood
Hi,
I have been running a merge publication & 4-5 subscriber all running perfectly good, but today I started getting this error below? I generated a new dynamic snapshot and re init the subscriber, but still the same. I synchronized the other subscribers and they are all running with no errors, even after a init. I.E. I would think this is data related, to this subscriber, but I have no further ideas how to track it down?
any ideas?
Regards
Gert Cloete
bcp "conCORD_ODS"."dbo"."MSmerge_contents" in "\OBSQL2005\ConcordReplData\MERCURYSQLEXPRESSOBSQL2005$DEV_CONCORD_ODS_CONCORD_ODSMSmerge_contents90_forall.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SMERCURYSQLEXPRESS -T -w
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options.
End of file reached, terminator missing or field data incomplete
Field size too large
The process could not bulk copy into table '"dbo"."MSmerge_contents"'.
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
I have a select statement that is being processed through oSql on Sql Server 2000. There are 2 fields in the select statement that are defined in the dateabase as nvarchar(1). When I perform my select statement, they show up in the output as 4 char fields. See dataset below for example.
493575545493575545003753404A 20070805000000002007080520070805 131307269009426800000000000000000000000
493575545493575545003753404A 00000000000000000000000020010410S 131307270009426800000000000000000000000
493575545493575545003753410A 20070805000000002007080520070805 131307271009426800000000000000000000000
How do I get rid of the extra spaces in the output? I have tried using ltrim(rtrim(fielde)) to no avail. Fieldg (the S) is a nullable field and is being processed using an isnull(filedg, ' ').
The general statement is:
Select fielda, fieldb, fieldc, ltrim(rtrim(fieldd)), fielde, fieldf, isnull(fieldg, ' '), filedh from mytable
The functioality can be replicated using:
Select 'a', 'b'
---- ----
a b
(1 row(s) affected)
Any Ideas?
Thanks in advance.
Aaron
Hi,
is it possible to change the appearence of input fields for parameters on the report server? My parameter is Multi-value with quite large amount of available values. On report server, user can (without scrolling) see only the first value. Parameter values are quite long, so user has to move alternally with both vertical and horizontal scrollbars to find the right value.
Thanks
Janca
Does the IDENTITY field type in SQL have a maximum size to it?
You know like int only goes so high up,
Hi all,
I tried to change the size of a field of my table using the query :-
ALTER TABLE test MODIFY id varchar(50)
Initially the size of id was set to 30 .Is there any other way or any error in my query.Please help me soon.
joshymraj
Can I build an expression that allows me to change the field size of a column or row in SSRS2005?
View 5 Replies View Related