New answers tagged

0

If you want to do efficient queries and have real time access to your records, perhaps you want to stay away from compression or references to external disk storage... ugh... Here are some directives I was able to find, you might also find them useful: Construct an index for each field that requires range queries. Use a B+ tree to implement the index. A B+ ...


3

Different performance of the same query typically indicates different execution plans. The database context generally won't yield different plans (and performance) but there are some cases where it could. The default collation of the current database context determines the collation of literals and parameters. Below is an example that shows a slightly ...


1

DB1 will be used as the default/current database for that connection string unless a USE statement changes the current database, and will show up in some of the DMVs, as well as sp_who and sp_who2.


5

They are part of a mixed extent, the diagram just didn't draw a grey box behind them, probably more to keep it from being too busy as much as anything. Data File : Page Again, I think this is just meant to simplify and not make the diagram too busy, as well as not give any sort of suggestion that pages will necessarily be linked in a certain order. Surely ...


3

This issue may actually be related to a very old bug with SQL Server, but not directly caused by SQL Server itself. I found this MSDN thread which describes the exact same behavior Please note the strangeness of this: RAISERROR won't compile (connection time out), even if it is in a comment! This was the accepted answer It turns out that the ...


1

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably going to have to build your own ETL logic, perhaps in one of the following ways: A linked server and stored procedures using OPENQUERY() or OPENROWSET(), An SSIS ...


1

The easiest way is to add a user to the db_ddladmin role. -- Older method EXEC sp_addrolemember 'db_ddladmin','username'; -- New method ALTER ROLE db_ddladmin ADD MEMBER username; This will give the user the ability to create/modify/drop any object in any schema. Unfortunately that will include objects you probably don't want them to change (tables for ...


1

Yes you can deploy that scenario to EC2 just as you would if it was VMWare environment. However with AWS I would highly suggest you follow the guidelines on how to properly set this up from Amazon. AWS provides a comprehensive set of services and tools for deploying Microsoft Windows workloads on its reliable and secure cloud infrastructure, such as ...


3

The problem arises because the aliases defined in the SELECT list are not visible by the same level WHERE clause - and the reason behind this is the order of logical execution of queries (which is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY, omitting some other clauses). You can bypass this problem with several methods: ...


1

there are lots of different ways to monitor for deadlocks in SQL Server. You can use Extended Events, Performance Monitor (to measure rate of deadlocks), Profiler (as a legacy alternative to XE) or even deadlocks to the SQL Error Log through enabling Trace Flag 1204 (details of nodes involved in the deadlock) and Trace Flag 1222 (deadlock information in ...


0

If this is Mysql, instead of "+" use concat().So the line will be SET @Where = concat(@Where , 'CLASS LIKE \%' , @SearchText);


-2

According to me you can write case for name. you can also use and operator with age in if clause, but that would be longer process if names are more.


0

As implied by Anti-weakpasswords's answer, if your machine really has been pwned, and the replacement is recurrent, it may be happening in an Agent job, or a Windows scheduled task!


0

For testing purposes: Add the account you successfully created the objects with to the SQL Agent restart the Agent Re-run your job Let me know you findings please.


2

The direct cause of the "The context transaction which was active before entering user defined routine, trigger or aggregate "..." has been ended inside of it, which is not allowed." error is: You are calling the XML_SCHEMA_NAMESPACE() built-in function, and sometimes it fails due to not finding the specified XML Schema Collection. As per the info in the ...


0

You will want to ensure that you are connecting to the right instance. Right now it's saying it's not able to find that instance/port #. open a powershell terminal and execute: get-service *sql* This will return the instance name (after the MSSQL$) if it is running on the local host. At that point try to connect to that instance, if you don't see it, ...


0

You should include the primary replica as the last entry in the Read_Only_Routing_List so that it will be used when the secondaries are unavailable. See Steve's answer for how to enable ApplicationIntent=ReadOnly on the primary and include it in the routing list.


0

Yes, it is necessary to drop and recreate the linked server. This takes just moments, so the hit is very small. Likewise, there is no reason to believe that SQL Server would try to read the internal contents of a different relational database.


0

This query get the first Approve and first Not Approve: WITH list AS ( SELECT ea.TaskId, e.EmployeeName, ea.IsApprove , n1 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove DESC, ea.Id) , n0 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove, ea.Id) FROM @Employee_Approval ea INNER JOIN @Employee e ON ...


2

Use a Derived Table/CTE to get a single ID for "approved"/"not approved" and LEFT JOIN it two times to Employees: SELECT TaskID ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID FROM "Employee Approval" GROUP BY TaskID


0

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora. Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows: 1. Right click on the linked server. 2. Choose Script Linked Server as. 3. Choose New Query Editor ...


0

There are so many possibilities, but here are a few things to check: that the user is a member of the ADB group in the Active Directory. that ADB exists as a server principal (a login). Example: SELECT * FROM sys.server_principals WHERE [name] LIKE '%ADB'; that this login has a user in the database in question: SELECT * FROM ...


0

Please check in [Database Properties-> Permissions Tab] and check if there are any denies on Create Table option. Also on the other roles like db_public .


6

As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual: Filtered indexes do not allow the IGNORE_DUP_KEY option. There is a workaround, however, if you can afford a small change to the table's structure. With the help of a uniquifier column you can create a unique index ...


2

According to MSDN the IGNORE_DUP_KEY option doe's not apply on filtered indexes


2

The server has already been scanned with several tools and all the main password (administrator, sa of SQL Server) has been changed. If the SQL Server service account user is an administrator on the local machine, then you need to build a new machine from the ground up to replace it using long (14 character plus) cryptographically random passwords, and ...


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


-2

http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx just to link sQL servers why would you need MSDTC ? unless the sql servers are using the a distributed transaction in the tsql


5

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


7

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


2

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


2

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


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


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


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

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


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


0

From the output you posted I can see MEMORYCLERK_SQLQERESERVATIONS 39874 (MB) The value is 38G which is huge. Why is SQL Server operations(sortand hash) requesting so much memory What is SQLRESERVATIONS SQL Server memory allocated during query execution for Sort and Hash operations. Do you really think 38 G is what the sort and hash operations ...


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


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


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

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

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


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


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


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


7

I'm working on the assumption that the table in question is a fact table, not a dimension table with a huge composite key: Just to fix the performance issue in the short term, I would add all of these key columns as the table's clustered index, which means you won't have to INCLUDE a lot of measures and stuff, like the suggested index does. Also, make the ...


2

As of January 29th, Microsoft SQL Server supports TLS 1.2 for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and major client drivers like Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server and ADO.NET (SqlClient). Blog post about the release: ...


2

You should look at Red Gate's tool set. They have a SQL unit testing tool called SQL Test. It might help you with a more comprehensive approach.



Top 50 recent answers are included