I've solved this by having a very simple calendar table - each year has one row per supported time zone, with the standard offset and the start datetime / end datetime of DST and its offset (if that time zone supports it). Then an inline, schema-bound, table-valued function that takes the source time (in UTC of course) and adds/subtracts the offset.
There's a few questions you need to ask first.
Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise.
Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not ...
Thank you for SQLfiddle and sample data! I wish more questions started this way.
If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join ...
For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters.
from MyTable a
join MyTable b on b.id = a.id
where a.OtherVal = 100
and b.Otherval = 200
For performance, ensure you have proper indexes. In this limited example, ideally you ...
In our shop at my previous job, we had a secondary set of servers where we tested our restores. For our busiest customers we would restore tonight's backup, mark it as read_only, and their reporting tomorrow would connect to that copy of the database for all reports from yesterday back. This offloaded about 90% of the reporting workload and doubled as a ...
(+) Reasons to create a physical table to store report data:
The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding ...
I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?
SQL Server Express cannot serve as a Publisher or Distributor.
SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details.
EDIT : To make my answer more ...
Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons:
Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance.
A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a ...
Since you have this requirement,
They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database
AlwaysON gets eliminated.
I would suggest you to setup Transactional replication.
Frequently (every 30mins) move the transaction data out of live database to another database
You can schedule the log ...
So what I am looking for is a way to store a large number of
activities that have almost no fields in common in a way that makes
Not enough rep to comment first, so here we go!
If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits are ...
Yes, more concise ways of pivoting exist for your scenario.
For instance, you could use the PIVOT operator:
FOR insp_year IN
2009 AS condition_2009,
2010 AS condition_2010,
I think what you're looking for is, "What are all the things I need to keep in mind when designing a data warehouse?"
There's a lot of good resources out there on it:
Well...my solution is as follows:
I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.
You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in text output view.
Convert your query into something like:
SELECT [Server] = CONVERT(VARCHAR(30), SERVERPROPERTY('Servername'))
, DatabaseName = CONVERT(VARCHAR(30), '''' + bs.database_name + '''')
, LastDatabaseBackupDate = CONVERT(VARCHAR(30),...
You can use expressions for this type of conditional work. To do this click on the image and select the properties tab in BIDS. Find the expression button (fx) and click it. Enter something like the below to get what you want:
=Iif(Fields!Gender.Value = "Female", "Female.jpg", "Male.jpg")
This is assuming that you have have set up the images to be ...
EAV is a nightmare for BI tools.
I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key.
However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the ...
I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.
This isn't necessarily true. Proper indexing can help mitigate blocking waits by giving queries adequate paths to data, and helping modification queries find the data they're going to be modifying.
I know that it is always best to perform ...
You can obtain most of the info from your screen shot from xp_msver. It goes back as far as SQL 2005. The other information might be in sys.configurations: http://msdn.microsoft.com/en-us/library/ms188345.aspx
Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place.
Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx
As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how ...
You could use grouping and conditional aggregating, like this:
MAX(CASE OtherVal WHEN 100 THEN MyVal END) AS MyVal1,
MAX(CASE OtherVal WHEN 200 THEN MyVal END) AS MyVal2
WHERE OtherVal IN (100, 200)
When I've been faced with this type of problem in the past, I have created a "numbers" table to help deal with the missing rows.
I created my numbers table specifically to deal with dates as so:
CREATE TABLE Dates
dDate DATETIME NOT NULL CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED
INSERT INTO Dates (dDate)
SELECT TOP(73049) DATEADD(d, -1, ...
If your SSIS package runs with the appropriate file system permissions, you can create a template Excel file with the desired formatting and copy that file to a new one (using a file system or script task) where the data will be exported. If the sheet will contain a variable number of cells that must be formatted, you can use conditional formatting in the ...
DECLARE @yesterday SMALLDATETIME;
SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE())));
WHERE X.stage = 'Approval'
AND X.T_ExitTime >= DATEADD(HOUR, 6, @yesterday)
AND X.T_ExitTime < DATEADD(HOUR, 17, @yesterday)
ORDER BY x.T_ExitTime;
Don't think you want BETWEEN for datetime ranges, unless you really want to ...
If you want something quick and easy, and can accept all column widths being the same, then try the -Y option of SQLCMD.exe:
C:\>SQLCMD -Y 3 -Q "SELECT name, name, name from sys.objects;"
nam nam nam
--- --- ---
sys sys sys
pla pla pla
spt spt spt
fai fai fai
MSr MSr MSr
sp_ sp_ sp_
OR, since the goal here is to email the output as a report, you ...
I would do something like this:
position int -- position of this row in the year,
-- e.g. 1 for first Monday of Oct,
-- 2 for 2nd Monday, etc.
primary key(period_start_on, period_duration)
check(position >= 1 and position < 40)
Then if you want ...
It will depend on the type of data being access and your company's security requirements/policy. In general you do not want users sharing an account, especially if that account has more than read permissions on your database. I would verify with your security folks or legal group if there are any specific requirements for managing access to data, or with ...
I'm not sure how you're going to be reporting this data back to said farmer, here's a quick way of doing what you're requesting:
declare @test table (
insert @test (sd,id,st) values ('05/25/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',1,'Shipment to Customer');
Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date. Although you could just write two queries and do a full outer join, I would guess you want ...
I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases.
REPLICATION : If the objects (tables) you are replicating are ...