Try this one – SET NOCOUNT ON; IF OBJECT_ID (N’tempdb.dbo.#temp’) IS NOT NULL DROP TABLE #temp CREATE TABLE #temp ( [COUNT] INT , DB VARCHAR(50) ) DECLARE @TableName NVARCHAR(50) SELECT @TableName=”[dbo].[CUSTOMERS]” DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT CHAR(13) + ‘SELECT ‘ + QUOTENAME(name, ””) + ‘, COUNT(1) FROM ‘ + QUOTENAME(name) + ‘.’ … Read more
I’m not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the … Read more
The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown. These predicates work with any comparable … Read more
First you need to set up database mail – if you haven’t done so, this question might help: Scripting setup of database mail Then you need a trigger: CREATE TRIGGER dbo.whatever ON dbo.wherever FOR INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100) BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients=”[email protected]”, @profile_name=”default”, … Read more
Since you’re using SQL Server 2008, you have the geography data type available, which is designed for exactly this kind of data: DECLARE @source geography = ‘POINT(0 51.5)’ DECLARE @target geography = ‘POINT(-3 56)’ SELECT @source.STDistance(@target) Gives ———————- 538404.100197555 (1 row(s) affected) Telling us it is about 538 km from (near) London to (near) Edinburgh. … Read more
See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example: DECLARE @textval NVARCHAR(30) SET @textval=”AB ABCDE # 123″ SELECT LEFT(SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000), PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000) + ‘X’) -1)
Problem: (Sql server 2014) This issue happens when assembly Microsoft.SqlServer.management.sdk.sfc version 12.0.0.0 not found by visual studio. Solution: just go to http://www.microsoft.com/en-us/download/details.aspx?id=42295 and download: ENU\x64\SharedManagementObjects.msi for X64 OS or ENU\x86\SharedManagementObjects.msi for X86 OS, then install it, and restart visual studio. PS: You may need install DB2OLEDBV5_x64.msi or DB2OLEDBV5_x86.msi too. Problem: (Sql server 2012) This issue … Read more
Looks like you have a query that is taking longer than it should. From your stack trace and your code you should be able to determine exactly what query that is. This type of timeout can have three causes; There’s a deadlock somewhere The database’s statistics and/or query plan cache are incorrect The query is … Read more