What is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.
View 3 RepliesI have a table (could have 1M or more rows in it) see structure below. I am looking to get the first and last date/times for each employee for each day. I also need the location GUID for the first read.
EmployeeGUID (uniqueidentifier datatype)
LocationGUID (uniqueidentifier datatype)
DateTime (DateTime datatype)
12345678-0000-0000-0000-000000000000
11111111-0000-0000-0000-000000000000
04/12/2014 07:00:01
[code]....
This would be the ideal output (I can do without the TotalTimeInHours):
EmployeeGUID (uniqueidentifier datatype)
LocationGUID (uniqueidentifier datatype)
FirstRead (DateTime datatype)
LastRead (DateTime datatype)
TotalTimeInHours
12345678-0000-0000-0000-000000000000
11111111-0000-0000-0000-000000000000
04/12/2014 07:00:01
04/12/2014 17:04:02
Total in hours between the first and last read.
44422222-0000-0000-0000-000000000000
22222222-0000-0000-0000-000000000000
04/14/2014 08:00:00
04/14/2014 14:00:03
44422222-0000-0000-0000-000000000000
33333333-0000-0000-0000-000000000000
04/15/2014 07:49:00
04/15/2014 09:00:01
I need the employees first and last reads for each date. They could have many entries per date or just 1.
Both using SSMS 2012 Enterprise on Windows 2012
I am able to import - export manually, how can you schedule this import export in a job? I want the table to drop and be rebuilt each time it runs. Since linking servers isn't recommended I want go with that approach.
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 RepliesI have the following 2 fields that are sourced from an Excel spreadsheet
DocNumber - a 10 digit number
PostingRow - a number between 1 and 999
I would like to produce a new column that is a concatenation of these two fields, but the PostingRow needs to be a 3 digit number eg. 1000256153-001 ....
I have a table as below.
Timestamp SourceIdChannelIdValue
2015-11-01 16:45:59.95311223,4923
2015-11-01 16:46:00.12712224,9357 *
2015-11-01 16:46:00.32713227,4183 *
2015-11-01 16:46:00.52714221,025 *
2015-11-01 13:17:14.17711223,0304 *
I only want the first value per channel in a new table ( the values marked with *)
I tried with sql below without succes.
set nocount on;
declare @today date;
set @today = CURRENT_TIMESTAMP;
Select * into new_table from (
select @today as 'Now', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth, Timestamp, SourceId,
[Code] ....
who has workflows created and ordered by CreateTimestamp . i need to count the number of workflows from top to point where there is either a success or failure workflow that occurs at the latest .
1.ban 137108351 has success workflow and prior to that workflow it has 2 workflows (exclude success and failure)
2.ban 104917284 has success workflow as latest (it still has failure but not considered because it is occurred earlier to success ) and prior to that workflow it has 2 workflows (exclude success and failure)
3.ban 107500674 has failure workflow and prior to that workflow it has 0 workflows (exclude success and failure)
below provided code for sample data as well
GO
/****** Object: Table [Temp].[deleteit] Script Date: 7/31/2015 3:04:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Temp].[deleteit](
[ban] [nvarchar](256) NULL,
[code]....
I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7.
DROP TABLE images
CREATE TABLE images
(
image_name varchar(500) null
,image_data varbinary(max) null
[Code] ....
how to set up the partitions.I have a transaction table with 50 million records that's very hard to query. it holds data for the last 4 years but the application only ever looks at the last 6 months so i believe this is and ideal candidate for partitioning.
Would it be better to
1) create a partition based on each year for all data so would have a 2015, 2014, 2013, 2012?
2) create 1 partition based on month for this years data then 3 based on year so would have jan,feb,march,april,may..., 2014, 2013, 2012
For 1) would you have to perform some maintenance at the turn of each year for accommodating the next years data. For 2) although this would give better performance as query's are mostly in the last 6 months wouldn't this have more maintenance to move month data to year partitions come the turn of the year and then create the next years months partitions.
I have a SSIS package in which i will download the files through FTP from main server to my local server. The file names will be like as follows:
''EYE0001_20150428_0805_INV.TXT''
''EYE0001_20150428_0805_SL.TXT''
''EYE0001_20150428_0805_SV.TXT''
''EYE0002_20150428_0805_INV.TXT''
''EYE0002_20150428_0805_SL.TXT''
''EYE0002_20150428_0805_SV.TXT''
After the download is over from the server to my local server.i will manually rename the files into like this as below,
''EYE0001_20150429_0805_INV.TXT''
''EYE0001_20150429_0805_SL.TXT''
''EYE0001_20150429_0805_SV.TXT''
''EYE0002_20150429_0805_INV.TXT''
''EYE0002_20150429_0805_SL.TXT''
''EYE0002_20150429_0805_SV.TXT''
SO i need to automate this files renaming process through ssis package.
I am trying to install SQL Server 2012 onto an already configured and validated windows failover cluster (server 2012) but the process is hanging after installing the setup files.
The last entry in the log is:
running discovery on remote machine
and I've left it hanging like this for 4 hours and nothing happens.
The environment is Server 2008 32-bit, SQL 2012 SP2 Standard 32-bit (11.00.5058), and Visual Studio 10 SP1 (10.0.40219.1).
I have a query that begins with MERGE <tablename> USING (SELECT blah, blah, blah While in SSMS, I can parse the query with no issues. I can execute the query and see the results I expect. But when I put the same query into an Execute SQL Task in SSIS, it won't parse. It gives me one of those very informative messages that I so love - Query failed to parse. Incorrect syntax near the keyword "MERGE".
I don't know if the 32-bit/64-bit thing has any bearing or not, but I have taken the exact same project folder, copied it to a 64-bit box, and it works fine in both SSMS and SSIS. That one is running Server 2012 R2, SQL 2012 SP2 Standard (11.00.5343), and Visual Studio 10 SP1 (10.0.40219.1).