How can i do the following:
Calculate the size of a varbinary(max) field or variable
Calculate the average of a varbinary(max) table column
I am using SQL 2005
Thanks for your posting
I have a SSIS package that opens an xml file, puts the contents into a string, then runs a stored procedure that dumps it into an xml column in a table. One of the xml files is huge. Putting the data into a ssis string causes an error. The length of the string variable is 58,231,886. The file will only get bigger.
How else can I get this data into a SQL Server XML field.
Hi,
I'm trying simple code:
declare @varbinary varbinary(8000)
set @varbinary = 2592
print len( @varbinary )
number length
2590 4 bytes
2591 4 bytes
2592 3 bytes ????
2593 4 bytes
All number in range 1 - 9000 has length 4 bytes except these numbers:
32,288,544,800,1056,1312,1568,1824,2080,2336,2592,2848,3104,3360,3616,3872,4128,4384,4640,4896,5152,5408,5664,5920,6176,6432,6688,6944,7200,7456,7712,7968,8480,8736,8992
These numbers has length 3 bytes !
Why?
I´m using varbinary variable to transport ID values and parsing in loop
set @cnt = len( @binEmpIDList ) / 4 -- 4 length
....
insert into @List( ID ) values( substring( @varbinary, @i * 4 + 1, 4 ) )
...
but this algorithm malfunctioning in enumerated numbers.
Please help, how correct this.
ID values in @varbinary too much. I cannot using convert varbinary to bigint and convert bigint to string.
Dalibor
I need to run a replace on a varbinary field but I am unsure how. I have tried mulitple different methods and to no avail. I am stumped.
I have attached a zip folder that contains a spreadsheet that shows what I am trying to do. In the first tab it shows the SQL command I use to call the field I want to change. In this case it's the Content field and I am calling it using the content_key. As you can tell the content is stored as varbinary. In the second sheet, It shows this varbinary being converted to varchar(max) and it shows what the actual text says.
The table that this information is stored is is called digitalassetcontent
I need to remove the
Code:
<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>
That is in front of the actual product description.
Somehow I need to run this command:
Code:
Update digitalassetcontent
set content = replace(CAST(content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')
Where CAST(content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' AND content_key = 'desc214974236480438500781058983745755010'
This is the error message I get when running this code: Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
On the varbinary field. How do I do this?
Hello,
I'm trying to insert a string expression into a varbinary field. I've tried it several ways, but the data does not seem to get inserted.
I map DT_STR field ("T") with varbinary field in destination table and the package executes properly, but when I see the data that it has been loaded I only see empty values (0x).
I have also tried other approaches, like converting to DT_BYTES during SSIS flow, but I always get the same result.
Any idea of how to achieve this?
hai guys,
i want to find out the length of the varbinary field ex: 0x000003752B226B3D0579
thanks
hari
Can a bitmap be added to a Varbinary field using the INSERT statement. If not what other method can I use to add a photo image (bmp or gif) to my Sql database?
View 4 Replies View RelatedI cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
How to calculate database size ?
When u setup e-commerce site for selling product then
how to calculate datbase size ?
Navnit
Hi all!
How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
Thanks,
ndba
this is the table i have created .
CREATE TABLE [dbo].[Table] (
[BCode] [varchar] (3) ,
[RefNo] [varchar] (12) ,
[RType] [varchar] (2) ,
[TheirRef] [varchar] (50) ,
[Amount] [money] NULL ,
[AccountNo] [varchar] (20) NULL ,
[EnteredDate] [datetime] NULL ,
[EnteredBy] [varchar] (6) ,
[IsTrue] [varchar] (1) ,
[RDate] [datetime] NULL ,
[Details] [varchar] (255) ,
[PostBy] [varchar] (6) ,
[SeqNo] [int] IDENTITY (1, 1) ,
[ThisCode] [varchar] (3) ,
[VDate] [datetime] NULL ,
[Id] [varchar] (20) ,
[Flag] [varchar] (1) ,
[TDate] [datetime] NULL ,
[Type] [char] (1) ,
[SerialNo] [int] NULL
) ON [PRIMARY]
GO
if i insert a single row in a table with values :
INSERT INTO [Table]
SELECT '100','1','DD',1000000,'999999000',
'2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502',
'101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'
EXECUTE sp_spaceused 'Table'
name | rows |reserved | data |index_size | unused
[Table] | 1 |16KB | 8KB | 8KB | 0KB
i couldnot understand
i) how the system calculate this 16 KB and 8 KB ?
ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ?
iii) how can i see the change in size of table by reducing the size of datatype ?
can anybody help me ?
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG
0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text.
2. Turn into a byte array and see if it is a jpg
3. Turn into a byte array and see if it is a pdf
4. Convert it to hex and then cast as varchar
5. etc....
I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table. Here is my table structureCREATE TABLE patient_procedure( proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY, patient_id int NULL, surgeon_name varchar(40) NOT NULL, proc_name varchar(20) , part_name varchar(30), wth_contrast int , wthout_contrast int , wth_wthout_contrast int, xray_part varchar(60), arth_area varchar(30), others varchar(30) , cpt varchar(20) , procedure_date smalldatetime NOT NULL, mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id) REFERENCES dbo.patient_information (Patient_id)) Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.Here the problem is when the user enters these two procedures information remaining fields will store the null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. Waiting for valuable suggestions.
View 2 Replies View RelatedHi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.
thanks.
I need to build a query to calculate the size of all tables in a database ...
View 5 Replies View RelatedI am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies View RelatedI have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
I know it won't be pretty.
`Ken
What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?
Thanks.
ok so i have this table:
Code:
Products_Sold
priceSold - money
itemsSold - int
An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81
to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)
But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?
in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100
but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200
and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300
but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.
I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye
I need find out the number of columns in flat file before i process that particular file.
I have file name in @filename variable and file path is @filepath variable.
But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And I am using for each loop container to read the file one by one and put the file name in @filename variable.
and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
My file structure is:
ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB)Â Â Â Â Â Â Â Â Â Â Â Â Â Â 279938496
Version Generation rate (KB/s)Â Â Â Â Â Â Â Â Â Â 53681040
Version Cleanup rate (KB/s)Â Â Â Â Â Â 53422320
Version Store Size (KB)Â Â Â Â Â 258720
Version Store unit count      22
Version Store unit creation                     774
Version Store unit truncation        752
im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars
Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)
i'm suppose to find the average length for them?
i'm suppose to use that to add up to my ROW SIZE
and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array
please help me asap before 2morrow night.
Thanks!
i have a test
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
Hi all,
I€™m trying to get a users age from their date of birth which is stored in my table, and although I can get the age I can€™t actually use it in a where clause as it keeps stating €˜invalid column name age€™. Below is a much reduced version of my code that shows my problem in a clearer way (the where clause is for example purposes as I don€™t intend to use it like this but it does show my problem):
SELECT DATEDIFF(YY, '8/5/1971', GETDATE()) - CASE WHEN (MONTH('8/5/1971') = MONTH(GETDATE()) AND DAY('8/5/1971') > DAY(GETDATE()) OR MONTH(GETDATE()) > MONTH('8/5/1971')) THEN 1 ELSE 0 END AS Age
WHERE Age = 35
Now this does get the persons correct age for the time of year and works fine until I include the WHERE clause, that is when I get the error. You can see that I have alias the returned column with the name Age but I keep getting the €˜invalid column name Age€™ error. What is wrong and how can I sort it out?
Now I did manage to resolve this problem using a temporary table (full actual code below) but I€™m certain my resolution can€™t be the best or easiest way to do this. Please help! Thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[sc_NewlyJoinedPeopleSearch]
@UserID uniqueidentifier,
@Days tinyint
AS
BEGIN
DECLARE @AgeFrom tinyint
DECLARE @AgeTo tinyint
DECLARE @DateToday DateTime
SET @AgeFrom = (SELECT AgeFrom FROM Users WHERE UserID = @UserID)
SET @AgeTo = (SELECT AgeTo FROM Users WHERE UserID = @UserID)
SET @DateToday = GETDATE()
SELECT UserID, DATEDIFF (YY, DateOfBirth, @DateToday) - CASE WHEN (MONTH(DateOfBirth)=MONTH(@DateToday) AND DAY(DateOfBirth) > DAY(@DateToday) OR MONTH (DateOfBirth) > MONTH (@DateToday)) THEN 1 ELSE 0 END AS UsersAge
INTO #Age
FROM Users
SELECT Users.UserID, UserName, AgeFrom, AgeTo
FROM Users JOIN #Age ON #Age.UserID = Users.UserID JOIN aspnet_Membership ON Users.UserID = aspnet_Membership.UserID
WHERE UsersAge > 30
ORDER BY CreateDate desc, Users.TimeStampUsers desc
DROP TABLE #Age
END
I want to get the number of days pending for a perticular column. I want the out put as a integer value.
This is what I used for my calculated field.
But when i write this day is underlined in red. It says unrecognized identifier.
=DATEDIFF(Day, GETDATE(),Fields!SubmittedDate.Value)
If I use "day", then it is not underlined but still it does not work.
When I use following query, I get no error message. but still no data in the column too.
=DATEDIFF("d", Fields!SubmittedDate.Value, "GETDATE()" )
Can anyone tell me why is that?
Thanks
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
Hi there,
if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.
Thanks very much,
Manny
What is the maximum size supported by XML datatype in SQL 2005.???
Thanks in Advance.
I have to send an email using a variable as message body... the variable gets its value from database varchar(8000). Due to limited size of string variable in SSIS the value gets truncated.
Any workaround?
Thanks
In SSIS, what is the maximum size of the String variable (one that you would define in the Variables dialog box)?
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