Hot answers tagged

7

If you want the interestingData and timestamp from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then: ;WITH x AS ( SELECT ID, interestingData, [timestamp], [count] = COUNT(1) OVER (PARTITION BY ID), rn = ...


5

Here's a stab at an algorithm. It's not perfect, and depending on how much time you want to spend refining it, there are probably some further small gains to be made. Let's assume you have a table of tasks to be performed by four queues. You know the amount of work associated with performing each task, and you want all four queues to get an almost equal ...


4

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation: There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with ...


4

According to the error message, your database contains Enterprise Edition features in the table Country. First of all, check the following on the original (old) server: SELECT SERVERPROPERTY('Edition'); That should tell you if you're running on Standard, Enterprise or Developer Edition. Developer Edition supports all of the Enterprise features (with ...


3

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there. So, to solve this issue and restore your database to point-in-time use T-SQL commands: Restore your last full backup RESTORE DATABASE ...


3

SQL Injection is hard to track from SQL Server side. Instead of looking at sql server, you should look at your web server IIS logs. Use Log Parser to parse your IIS Logs to track down the source of sql injection. e.g. logparser.exe -i:iisw3c -o:Datagrid -rtp:100 “select date, time, c-ip, cs-uri-stem, cs-uri-query, time-taken, sc-status from ...


3

I was not able to reproduce your error (although I only tested with one table in the mix). An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility. Set up I created a simplistic table --- a key, a value and an empty field that will be populated, via ...


2

For production instances it is recommended to create separate partitions for the following elements of SQL Server: data files transaction log files tempdb files Some people also suggest to install SQL Server on a dedicated partition instead of the C: drive but I think it is an overkill and does not bring any benefit. The installation itself cannot grow ...


2

Based on your sample, I believe you only need a GROUP BY ID with an HAVING clause on MIN([interestingData]) > 300. Query: SELECT ID , interestingData = MIN([interestingData]) , timestamp = MIN([timestamp]) , count = COUNT([ID]) FROM Table1 GROUP BY ID HAVING MIN([interestingData]) > 300; SQL Fiddle with sample data and query. Output: ID ...


2

To make this happen, you need to give the computer domain account on which you have SQL Server installed rights to write to the share [Folder] on [ComputerName]. Say you have a computer SRV-SQLSERVER, and SQL services you run with NT Service*, but in some situation you can't create a domain account for them. But you need to store backups on other domain ...


2

You need to ensure you are running the query in the context of the correct database. Since system views such as sys.objects are context sensitive, they only return rows related to the current database. For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect. ...


2

My idea for this is ,first, to filter only the records with zoneID 35 or 38 that have have status = OUT (this mean the start of the trip) Then base on this, give me the next record(most near the the record as datetime)that have different zoneID and different status (Ex:If I have 35 and OUT , give me the next 38 and IN.) And I added a additional ...


2

The technical answer To my knowledge, there's no problem in using the same user for all "client" machines. The same user account can connect to a SQL Server as many times as the licensing of that SQL Server allows. The security perspective However, you may want to review the security aspects of this setup - do all the remote machines have the same access ...


2

For those who are interested in getting more details about contained databases, I can recommend them to read this article http://www.sqlshack.com/contained-databases-in-sql-server/ The article pinpoints main advantages/disadvantages of using contained databases. Disadvantages Partially contained databases cannot use features like replication, change data ...


1

Change your DayOfTheMonth column to TINYINT. Any downstream services, such as Excel or SSAS, will then sort your numbers as numbers. There is one circumstances where I'd consider using a text data type for the day of the month. Most dimension tables, including Dates, will have a couple special records to represent things like "not available," "not ...


1

There isn't a way to do this currently, as finding the "latest" widget date will require an aggregate (grouping), ranking function, subquery, or apply; none of which the indexed view will allow in its definition. However - is there a particular reason you want to have an indexed view? Do you have appropriate indexing on the underlying Widget table? I'm ...


1

The following will show you if the triggers are enabled. If they are, they will fire, assuming the DML meets the trigger requirements. You should run this while the SSIS package is running. SELECT TableName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name) , TriggerName = t.name , t.is_disabled FROM sys.triggers t INNER JOIN sys.objects o ON ...


1

Which SP of SQL Server 2012 did you use? This is a known issue with SQL Server 2012 SP2. The information for "Restore to:" is empty, which was supposed to show the timelines. To work around this issue: Use SQL 2014 SSMS Use T-SQL


1

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL. A few points to get you started on the performance of your query: Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain. tblMetaData ...



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