18

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


12

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


12

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


10

For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters. select a.myVal, b.myVal 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 ...


8

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


7

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


7

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 reporting easier. 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 ...


7

Yes, more concise ways of pivoting exist for your scenario. Option 1 For instance, you could use the PIVOT operator: SELECT * FROM ( SELECT road_id, insp_year, condition FROM road_insp ) derived PIVOT ( MAX(condition) FOR insp_year IN ( 2009 AS condition_2009, 2010 AS condition_2010, ...


6

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),...


5

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


5

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


5

Yes sure.. you can use the subscription and delivery of Reporting Services to send a report. The available formats are: XML file with report data CSV (comma delimited) PDF MHTML (web archive) Microsoft Excel TIFF file Microsoft Word (source) For delivery via email look here.


4

I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried this command and it works. May be it helps someone else. sudo apt-get install libsqlite3-dev


4

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


4

You could use grouping and conditional aggregating, like this: SELECT id, MAX(CASE OtherVal WHEN 100 THEN MyVal END) AS MyVal1, MAX(CASE OtherVal WHEN 200 THEN MyVal END) AS MyVal2 FROM MyTable WHERE OtherVal IN (100, 200) GROUP BY id ;


4

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


4

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


4

DECLARE @yesterday SMALLDATETIME; SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE()))); SELECT ... 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 ...


4

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


4

Reporting Services is the generally preferred way of doing this. You can create the reports completely inside the web designer if you have an aversion to using Visual Studio, but really Visual Studio is the preferred method for creating reports. There is no need for any programming knowledge to make this work, just SQL. If cost is a concern, SQL Server ...


3

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


3

Per the article you provided, give this a shot for another potential workaround: On the Reporting Services server, modify the RecycleTime value of the rsreportserver.config file, and time this out so a 'recycle' at the app server level of RS occurs just before the start of the regular business day every day - like an hour or so before any production RS ...


3

This is a "known" issue/feature according to Microsoft. Descriptions, parameter defaults, subscriptions, etc. all fall under report "meta data" and are maintained separately on the Report Manager server. The only way to get these to change on the Report Manager is to either manipulate them manually or delete the report and upload anew. ...


3

You can use Power BI service and Power BI Mobile Apps to share reports with others as explained here. Take a look here at the Publish to Web feature in Power BI. Here you can see how easy is to create a report from SQL Azure data on Power BI. Hope this helps.


3

What do you suggest on above ? I need to keep primary SQL server replica as 2012 for now, and I need to make >async replica (SQL server 2016) to be available for read-only access There is nothing you can do, this is a known item: During a version upgrade, readable secondaries cannot be read after an upgrade of the readable secondary and before either ...


2

Parametrize your report and make sure that your query is parametrized too. You can test your parametrized report inside the report designer. Then use Kettle/PDI to feed the parameters to your report step, and once you produce the file, rename it or move it to the correct spot. Running several smaller reports also allows you to run multiple reports in ...


2

If I understand you properly - you want to have a second reporting instance and you are asking how you can keep that updated. It also appears you are on SQL Server 2005 so your options are limited more so than, say, if you were on SQL Server 2012 with Availability Groups. Your options are really going to be in the spirit of: Log Shipping And here you can ...


2

You need to install libraries: sudo apt-get install sqlite (on linux console, o the equivalent command for your distribution) also, if you want, you can install python because SQLite is a dependency of it.


2

Oops, I forgot to put the code I used, sorry about that. Here is the code that worked for me: SELECT v_FullCollectionMembership.Name as [Computername], v_Collection.Name as [Collections With Maintenance Windows], v_ServiceWindow.Description as [Maintenance Description], v_ServiceWindow.StartTime as [Maintenance Window Start Time], v_ServiceWindow....


2

http://colleenmorrow.com/2011/03/14/database-snapshots-part-2-offload-reporting/ may prove to be helpful for using synonyms to report on snapshots without disruption when generating new snapshots.


Only top voted, non community-wiki answers of a minimum length are eligible