Transact SQL :: How To Calculate Size Of Files That Have UNC Paths For

Mar 27, 2014

I 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


ADVERTISEMENT

Disaster Recovery: Calculate DB Size Based On .dmp Files

Feb 7, 2000

I 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

View 1 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Transact SQL :: How To Rollover Trace Files Once It Reaches Max File Size

Aug 18, 2015

I am trying to trace few events using profiler Via T SQL like below,  the below scripts works fine, but once the file reaches 1GB , it is not rolling over to another file and start trace again.I tried using URL...once it reaches max file size provided between the time interval..

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2015-08-18  16:00:00.000'------Stop Time 4 PM 
set @maxfilesize = 1024  --1GB max File size 

[code]...

View 2 Replies View Related

Table Paths And .udl Files

May 6, 2007

Hi:

I am trying to set up a TEST ENIVORNMENT for a reservation software package. I need this setup so that I can run various scheduling scenarios in order to optimize operations. Below I have been given instructions from the software vendor on how to set up my SQL database. However, I am a little confused on what to do for a couple of the steps. They are as follows:

***In SQL server 2000 enterprise manager on the test laptop, change the 2 path settings in the table SGCONFIG. The should be changed to C:StratagenAdept5Server. ** YOU WILL NEED TO DO THIS STEP EVERYTIME YOU RESTORE A BACKUP ADEPT5_CLASTRAN.BAK FROM THE PRODUCTION TO THE TEST ENVIRONMENT

QUESTION: How do you change the path settings on a table?


***Open the .udl files in the apps folder and the server apps folder and check to see that they are pointing to the test laptop server, not the production server.

QUESTION: What are the .udl files and how do you check to see if they are pointing at the test laptop server only?

Thanks sincerely for your help. I am trying to meet a deadline for a meeting tomorrow. Therefore, I am desperate. Please send me email. rtanner@clastran.com.


Ron



View 3 Replies View Related

Table Paths And .udl Files

May 6, 2007

Hi:

I am trying to set up a TEST ENIVORNMENT for a reservation software package. I need this setup so that I can run various scheduling scenarios in order to optimize operations. Below I have been given instructions from the software vendor on how to set up my SQL database. However, I am a little confused on what to do for a couple of the steps. They are as follows:

***In SQL server 2000 enterprise manager on the test laptop, change the 2 path settings in the table SGCONFIG. The should be changed to C:StratagenAdept5Server. ** YOU WILL NEED TO DO THIS STEP EVERYTIME YOU RESTORE A BACKUP ADEPT5_CLASTRAN.BAK FROM THE PRODUCTION TO THE TEST ENVIRONMENT

QUESTION: How do you change the path settings on a table?


***Open the .udl files in the apps folder and the server apps folder and check to see that they are pointing to the test laptop server, not the production server.

QUESTION: What are the .udl files and how do you check to see if they are pointing at the test laptop server only?

Thanks sincerely for your help. I am trying to meet a deadline for a meeting tomorrow. Therefore, I am desperate. Please send me email. rtanner@clastran.com.


Ron


View 1 Replies View Related

Transact SQL :: Make Tracks Or Paths Of Parent Has Many Child

Nov 19, 2015

I have a  tblActivity table :

ActivityID ActivityName
-----------------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
10 A10

and related it in tblParentActivity table with parent child relationship as:

ParentActivityID ChildActivityID
-------------------------------------
1 2
1 3
2 4
2 5
3 6
4 7
5 7
6 8
7 9
8 9
9 10

I want to write a SQL query which will make it in tracks depend on how many childActivity in one parentActivity like

ActivityID TrackGroup
-------------------------
1 1
2 1
4 1
7 1
9 1
10 1

[code]....

View 8 Replies View Related

How To Calculate Database Size

Nov 6, 1999

How to calculate database size ?

When u setup e-commerce site for selling product then
how to calculate datbase size ?

Navnit

View 1 Replies View Related

Calculate Table Size

Oct 10, 2001

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

View 1 Replies View Related

How Can I Calculate Table Size ?

Jan 29, 2008

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 ?

View 3 Replies View Related

How To Calculate Average Row Size Of A Record.

Apr 22, 2008

 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 Related

Calculate The Size Of A Database And Backup It.

Feb 13, 2004

Hi 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.

View 5 Replies View Related

Data Files Size And Log Files Size

Sep 22, 2004

Hi!
I'm using SQL Server 2000!
How to know then Data files size and Log files size by Store Procedures or Sql query?
Thanks!

View 1 Replies View Related

How To Calculate The Size Of A VARBINARY(max) Field Or Variable

Jan 7, 2008



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

View 3 Replies View Related

How To Calculate The Space (size In Bytes) Used By A BLOB Column In A Row

Dec 3, 2007

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.

View 3 Replies View Related

SQL 2012 :: Check Columns Before Process And Calculate Size In Byte In SSIS

Apr 10, 2014

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.

View 0 Replies View Related

SQL 2012 :: Calculate TempDB Size For Read Committed Snapshot Enabled

Apr 14, 2014

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

View 4 Replies View Related

Transact SQL :: Calculate Events On Each Day

Jun 17, 2015

Need calculating the count of events on each day. I have an event log table as shown below. 

DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime )
INSERT INTO @EventLog
SELECT '100','Inplace','01/05/2015 05:00' UNION ALL
SELECT '100','Removed','03/05/2015 10:00' UNION ALL
SELECT '100','Inplace','05/05/2015 10:41' UNION ALL

[Code] ....

I need an output to look like on each day how many events were in place.

E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 . 

event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month. 

So I need to count of all each event_id in place for each day in may. 

How to calculate the missing dates to ...

View 7 Replies View Related

Transact SQL :: How To Calculate A Ratio

Oct 28, 2015

How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.

SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK")
FROM ruleResults;

View 2 Replies View Related

SQL Server 2012 :: Calculate Number Of Groups And Group Size With Multiple Criteria

Jun 15, 2015

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.

View 9 Replies View Related

Transact SQL :: How To Calculate A Running Stock Value

Oct 8, 2015

I have the following store procedure :

SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,

[code]....

What I am trying to get is a RunningStock level column which is able to display stock level as describe below :

If ItemStatus value is 0, that means that the item has been taken out from stock.

So based on that the first row running Stock level is calclulated as

(ProductQuantity * ItemUnitWeight)-ItemQuantity=9...

For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?

View 6 Replies View Related

Transact SQL :: Calculate Total Between Two Columns?

Sep 16, 2015

I have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.

CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);

And i have some data:

INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)

I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):

SinGroup C1 C2

y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50

Z 50 0

And also result like this (c1-c2 and grop by each row,the result show under the column 1):

SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0

View 5 Replies View Related

Transact SQL :: Query To Calculate In Array Like Excel

Nov 4, 2015

I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.

I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.

SELECT
'0' As RN
,memrepay.lnr
,'Repayment' AS Type
,SYSDATETIME() AS NOW
,SUM(memrepay.mprinc) As PrincPaid
,SUM(memrepay.mint) As IntPaid

[Code] .....

How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?

View 2 Replies View Related

Transact SQL :: Calculate Rolling Year Sales?

Aug 19, 2015

I have 2 tables as follows:

table A-NewParts
PartNumber
Description
LaunchDate
a123
product a
4/1/2009

[code].....

I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.

PartNumber
Description
LaunchDate
Yr1 Sales
Total Yr1 Sales
Yr2 Sales
Total Yr2 Sales
a123

[code].....

View 3 Replies View Related

Transact SQL :: How To Calculate Number Of Employees In Each Month

Jun 17, 2015

I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.

AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31

If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .

View 2 Replies View Related

Transact SQL :: Efficient Way To Calculate Unique Values?

Jul 14, 2015

I have a data set as -

ID       Set Date          DB Date
100     Null                 07/01/15
100     07/05/15         07/02/15
100     07/10/15        07/08/15

I want to able to get 2 unique dates

1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.

2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.

The table has such records for lot of different ID's.

View 29 Replies View Related

Transact SQL :: Calculate Average Based On A Scenario

Oct 30, 2015

Col1                      Col2       Col3          Col4
54763.00              21           0              0             
--Row1
59574.00              23           0              0             
--Row2
64085.00              20           0              0             
--Row3
0.0               0.00      0           0              0             
--Row4

I'm trying to calculate Average of Col1 of above table based on below scenario:

CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3

ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4

View 7 Replies View Related

Transact SQL :: How To Calculate Estimated Completion Time Of A Job

Nov 4, 2015

How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.For example)...Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.

In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...

View 7 Replies View Related

Transact SQL :: Calculate Data Based On Position

Jun 4, 2015

I have a table "PrintArea" with below table structure.

The Details column have " " separated data.

CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)

This is how the data lookis like.

INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579

[Code] ...

I want to calculate totalAreaPrinted based on below formula from Details column.

View 2 Replies View Related

Transact SQL :: Calculate Minutes In Stored Procedure Using Datediff

May 9, 2015

I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user  only if he makes the 3 incorrect entries within 30 minutes.

I created a field named "FirstEntryTime" of type datetime  that saves the date of the first incorrect entry.  I tried to make an if statement:

if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30)
Begin 
 Update myTable set ...
 End

View 6 Replies View Related

Transact SQL :: Calculate Total Working Time Against Attendance ID?

May 16, 2015

I have a table like below

i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)

ex : 

AttID TotalTime
1 08:02:00
2 07:45:00

View 7 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??

select  building_number as SchoolID,building_name as Campus,   count( distinct( CASE  WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed   from   Sales sl join Buildings b on  sl.Building_Num =b.Building_number join students2 s on  s.Student_Number= sl.Student_Num   join Sale_Items SI on   si.UID = sl.UID   where  CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and   '2015-05-07'      and VoidReview <> 'v' and  SI.INum = '1'    group by  building_number,building_name order by building_number,Building_Name;

View 8 Replies View Related

Transact SQL :: Calculate Column Purchasing Rate Per Customer For Product And Region

Oct 22, 2015

I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.

createtableProduct2
(
[product] [varchar](255),
[Region] [varchar](15),
[Subregion] [varchar](25),

[Code] ....

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved