New answers tagged

0

I cross-posted this to Stackoverflow and found a working answer, even if it meant having to type out the column names (which explains why my WHERE clause didn't work) SELECT ins.[name], ins.[created_at] FROM INSERTED ins JOIN DELETED del ON ins.id = del.id WHERE del.[name] <> ins.[name] OR del.[age] <> ins.[age]; ...


0

I am not sure that this is the best way (I would welcome any comments to tell me how it is not), but it's very simple (and quick if you have a small database): First, backup the database to a .bak file. Then, restore the database from the same .bak file, choosing the new .mdf and .ldf file locations under file options for the restore task. I wouldn't do it ...


2

The scheme could look like this. <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="R"> <xs:complexType> <xs:attribute name="C"> <xs:simpleType> <xs:restriction base="xs:string"> ...


2

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ? IMHO, NO - since you are on sql server 2012. Why ? SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is ...


4

You are performing an index rebuild on Standard edition. As documented here you would need Enterprise or Developer edition to see parallelism for this.


2

This query will give you a list of your table names and the field name into a temp table. IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData GO CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, SQLScript varchar(max)); DECLARE @FieldName varchar(20); SET @FieldName = 'COLUMN_NAME'; INSERT INTO #BaseData (...


1

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


3

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production. Setting that aside: There are a number of tools out there that claim to compare schema, and ...


1

This will return the name of the view(s) and the definition (the CREATE statement) for all views that your table is in. SELECT name, definition FROM sys.objects o JOIN sys.sql_modules m on m.object_id = o.object_id WHERE definition LIKE '%<Your table name>%'


0

USE test; GO CREATE TABLE LogonAudit ( AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID PRIMARY KEY CLUSTERED IDENTITY(1,1) , UserName NVARCHAR(255) , LogonDate DATETIME , spid INT NOT NULL ); GO GRANT INSERT ON Test.dbo.LogonAudit TO public; GO create ...


0

Assuming you have the .dtsx files, they are just XML so you could use a simple text search of the files, something like this http://stackoverflow.com/questions/8153750/how-to-search-a-string-in-multiple-files-and-return-the-names-of-files-in-powers


10

Having an open transaction by itself will have almost no consequence. A simple BEGIN TRANSACTION -- wait for a while, doing nothing -- wait a bit longer COMMIT will, at worst, hold a few bytes of status values. No big deal. Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be ...


4

Incomplete transaction may hold large number of locks and cause blocking When a transaction is not completed either because a query times out or because the batch is cancelled in the middle of a transaction without issuing a COMMIT or ROLLBACK statement to complete the transaction, the transaction is left open and all the locks acquired during ...


3

Your largest consequence will be blocking of the objects used in the transaction. Especially if you assume your users are inserting data, then that long running transaction could include SELECT statements on commonly used tables. Your users' update statements may not be able to get the necessary lock required to complete their updates or inserts. A ...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


1

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command. These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll ...


0

Yes, it's ok to use (READPAST, UPDLOCK) where inUse = 0. UPDLOCK hint will force SQL Server to use (U) locks instead of (S) locks and (U) locks are incompatible with each other, so the two threads will not be able to fetch the same row. READPAST hint will force SQL Server to skip the locked rows rather than being blocked. The batches are not "gone". ...


1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled back. The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will ...


2

You can get name of well-known sid https://support.microsoft.com/en-us/kb/243330 with powershell script $objSID = New-Object System.Security.Principal.SecurityIdentifier ("S-1-5-18") $objUser = $objSID.Translate( [System.Security.Principal.NTAccount]) $objUser.Value Is there any problems with giving MS Sql Server sysadmin role to 'NT AUTHORITY\SYSTEM' ...


0

I experienced very similar issue in my previous company. Database miroring was periodically getting suspended with the same error as you are getting. Mirroring never ever reconnected on its own, but usually I was able to resume it using: ALTER DATABASE <Database Name> SET PARTNER RESUME After a few weeks we figured out that even though there was ...


2

Installing service packs usually goes like this: detailed step by step guide It shouldn't affect databases, but you may want to test installing SP3 on the test server first. It will help you get an idea how long it takes (management will want to know that beforehand). It will also give you a chance to find out if any of your applications (or anything else) ...


3

To address the immediate question: Any reason why the convert function isn't working the way it should? The CONVERT function works with various types. What kind of conversion to apply is determined principally by the types specified by the first argument (explicitly) and the second argument (implicitly as the type of the expression). The presence of ...


1

You may want to consider using the query that will show you free space in the log and free space in the data file. If you add them up you'll get the similar value as the SSMS is showing. This is an excellent query which does just that: DECLARE @log_used DECIMAL(19,7); CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT); INSERT #x EXEC('DBCC ...


1

Unless you are using SSMS to just present the dates, your presentation layer should handle the part of presenting the date in whatever format you want to display. I normally use the date-format help to find out the style :-) SELECT [borrower#], -- use square brackets when you have special characters in the output column name location, ...


0

While there isn't any option that I am aware of to turn off this feature, a simple ctrl+h and then replace both [ and ] with an empty space will quickly remove the brackets from the generated t-sql scripts.


0

Both @cha and @alt have right clues - the error message is misleading and Application Pool Identity is involved (well, at least it was in my case). If your IIS app's application pool uses Application Pool Identity, the user account you need to add to SQL Server is IIS APPPOOL\your_app_pool_name. Note: don't use the Search... button in Login Properties to ...


14

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria. Suppose you have Table2 with the following content: | ...


2

Yes. You can write your own snippets code in a .snippet file and then import it into SSMS. To import the folder containing the .snippet file: Open SSMS Click on Tools ->Code Snippets Manager Click on Add and select the folder where your custom snippets are written. I believe this answers a part of your question. You can further follow the below links to ...


0

I had the same problem with SSMS 2016. I used procmon to identify that the last thing it did before a several-second delay was make a network connection to a Microsoft IP address. I then tried turning off the Customer Experience Program by setting registry key HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\130 value CustomerFeedback from 1 to 0. ...


1

SSMS has useful editing option, column editing. Hold ALT/SHIFT and select column with a mouse (left button pressed), a column selection appears. Type the text. Click anywhere to exit.


0

I found the issue after a long time. It was due to 1 million+ files in the \MSSQL\log folder. After setting up a job that cleared that folder. The failover after SP install worked fine. The solution was confirmed on both this 2012 cluster, and the 2008R2 cluster we had the same problem on


3

I understand this is an old thread, but it was the first hit when I searched "how to move mdw database" on Google. I had configured MDW (Data Collection) on a SQL Server 2008 R2 production server, and I hosted the MDW database in a QA instance. Later on I had to move the QA instance to a different machine, with a different name (migration and name change ...


0

Here are the steps that I followed to get a Windows 2008 R2 cluster hosting SQL Server to Windows 2012 R2. In this example we have a 2 node Active / Passive cluster running Windows Server 2008 R2 Enterprise hosting SQL Server 2012 SP2 with CU6, this will work with any version of SQL Server. Step 1: Document the following: Drive letter mapping to shared ...


4

Windows Update isn't offering you a Service Pack for SQL Server 2012 to apply to your SQL Server 2014 instance; there is something else on your machine that is triggering it. Could be dozens of things, but most commonly: The SQL Server 2014 instance isn't the only instance on your machine. You may have SQL Express installed (on its own, or as part of ...


5

You can capture this information with an Extended Events session: CREATE EVENT SESSION [Audit_Logon] ON SERVER ADD EVENT sqlserver.LOGIN ( SET collect_database_name = (1) ,collect_options_text = (0) ACTION( sqlserver.client_app_name ,sqlserver.client_hostname ,sqlserver.server_principal_name ) ) WITH ( ...


-4

You can Use ajax request for checking and in codeigniter has built in function Is_Unique[databse.table_name]


1

I don't have access to SQL Server so I googled the syntax for generated columns, but there's probably syntax errors in here anyhow. The idea is to use a generated column that has a unique value (BatchNumber) whenever BatchComplete is not null, and a non-unique number (BatchId) otherwise: CREATE TABLE BatchTable ( BatchNumber INT IDENTITY(1,1) PRIMARY KEY ,...


2

I would suggest using SQL Audit if you want to be sure. Take a look at this https://msdn.microsoft.com/en-us/library/cc280663.aspx You would want to audit the SUCCESSFUL_LOGIN_GROUP group. Mat


2

You can't actually install a service pack for a previous product (2012) onto the current product. I would guess you have SQL Express or some shared components/management studio etc which is what the SP applies to. The easiest way to find out, assuming you aren't inventorying your server any other way would be to run the SP from the GUI and see what it ...


4

Based on some code here I put together the following script: Note: It makes a couple of assumptions. 1) You are logging successful logins (and don't care about failed ones) 2) You are keeping enough log files to cover the last 90 days. CREATE TABLE #IOErrorLog ( ServerName nvarchar(256), UserName nvarchar(4000), LogDate datetime, ...


1

Possible duplicate of http://stackoverflow.com/questions/1905227/date-of-last-login-or-read-operation-on-a-sql-server-database This query should show the last login date/time and login_name. More information can be gathered by querying sys.dm_exec_sessions. select max (login_time)as last_login_time, login_name from sys.dm_exec_sessions group by login_name;


5

This issue is to do with corrupt/invalid statistics. A specific case where it can occur is mentioned in this connect item The Metadata of disabled Non-Clustered Indexes becomes invalid, if you change the keys of the Clustered Index, and this caused the failure that you experienced. With the workaround being to drop the disabled indexes. If the ...


0

For logins, only things that are like server audits. A SQL trace to capture logins. This is more intensive than using an audit though. An extended event session to capture the logins. You could cobble up a login trigger to record logins to a table, but this is probably the worst of them all. For expiries you can use Loginproperty('login_name', '...


1

I've had that problem for only on query: 1. Scheduled job was looking in query cache for particular query. 2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index XYZ. 3. If bad pattern is found, that query will be automatically deleted from query cache by it's plan-handle using DBCC FREEPROCCACHE


10

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to fully scan the narrow index. It seems that you were expecting a nested loops with seeks on the clustered index? The execution plan shows that the table #ToPurge ...


1

There is this KB article, which lists the required updates you need to install https://support.microsoft.com/en-us/kb/3135244 , which also lists which updates are needed on the client machines trying to connect to a TLS 1.2 enabled SQL Server. Also, read http://blogs.sqlsentry.com/aaronbertrand/tls-1-2-support-read-first/ , which lists known problems and ...


5

The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to ...


2

This is not an complete answer, will add more to the answer as I get further information from you. The output of errorlog had dbcc memorystatus dump and what was noticed was Process/System Counts Value(in Bytes) ---------------------------------------- ---------- Available Physical Memory 1217605632---1.1 G Available ...


0

Finally, subfolders were made possible in SSMS 2016. Cautious users: You can install SSMS 2016 and still keep using old one. See note [1] below. Once there, start using solutions instead of projects. Subfoldering works like this: Adding solution subfolder: Right-click solution item (tree root) and from menu select Add > New Solution Folder. Step 1 ...



Top 50 recent answers are included