Short answer: You can’t. Thanks to those that provided workarounds, but it seems that SSMS itself can not be set to pause or stop on an error in the same way that Toad for SQL Server can.
That should work fine. The setting of NOCOUNT is irrelevant. This only affects the n rows affected information sent back to the client and has no effect on the workings of @@ROWCOUNT. Do you have any statements between the two that you have shown? @@ROWCOUNT is reset after every statement so you must retrieve the … Read more
The error means that the SQL INSERT statement is being executed, but the ROWCOUNT being returned by SQL Server after it runs is 0, not 1 as expected. There are several causes, from incorrect mappings, to UPDATE/INSERT triggers that have rowcount turned off. Your best beat is to profile the SQL statements and see what … Read more
From a purely querying standpoint, it makes no difference whether your key is descending or ascending if you want to pull N most recent or N oldest records: The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. … Read more
They have to be unique for the table or view they were created for. Here is a reference on Microsoft Learn that details this. FTA: index_name The name of the index. Index names must be unique within a table or view, but don’t have to be unique within a database. Index names must follow the … Read more
The most commonly used approach would be a recursive Common Table Expression (CTE) WITH Ancestors(Id, [Name], AncestorId) AS ( SELECT Id, [Name], Id.GetAncestor(1) FROM dbo.HierarchyTable WHERE Name=”Joe Blow” — or whatever you need to select that node UNION ALL SELECT ht.Id, ht.[Name], ht.Id.GetAncestor(1) FROM dbo.HierarchyTable ht INNER JOIN Ancestors a ON ht.Id = a.AncestorId ) … Read more
First, make sure you are profiling the performance properly. For example, run the query twice from ADO.NET and see if the second time is much faster than the first time. This removes the overhead of waiting for the app to compile and the debugging infrastructure to ramp up. Next, check the default settings in ADO.NET … Read more
I can say this from personal experience because I am dealing with this very problem as we speak. The place I am currently working as a contractor has this type of environment for their SQL Server development systems. I am trying to develop a fairly modest B.I. system on this environment and really struggling with … Read more
Use extended properties. For example to add an extended property to a table in the dbo schema you can use: EXEC sys.sp_addextendedproperty @name=N'<NameOfProp>’, @value=N'<Value>’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N'<Table>’ You can update them: EXEC sys.sp_updateextendedproperty @name=N’MS_Description’, @value=N’My Description’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’ ,@level1name=N'<YOUR TABLE NAME>’ You can read them like: SELECT * FROM fn_listextendedproperty (NULL, ‘schema’,’dbo’, ‘table’, ‘<yourtable>’, … Read more
open SSMS and connect to the database go to MANAGEMENT > MAINTENANCE PLAN > pick your backup plan. > right click and view history. or to MANAGEMENT > sql server logs. directory location : c:\Program Files\Microsoft SQL Server\MSSQL.xx\MSSQL\LOG