To disable foreign key constraints: DECLARE @sql nvarchar(max) = N”; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N’ALTER TABLE ‘ + obj + N’ NOCHECK CONSTRAINT ALL; ‘ FROM x; EXEC sys.sp_executesql @sql; To re-enable: DECLARE @sql nvarchar(max) = N”; ;WITH x AS … Read more
How about this? Assuming SQL Server 2008: SELECT CAST(StartDate as date) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) For pre-2008: SELECT DATEADD(day,datediff(day,0,StartDate),0) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) This results in : ForDate | OnHour | … Read more
CURRENT_TIMESTAMP is standard ANSI SQL, and so is theoretically one tiny little island of ‘don’t need to change’ amongst your thousands of SQL Server-specific lines of SQL if you ever need to move databases….
Not because it’s a good idea, but so that no one else has to figure out how to use the horrid sp_OAxxx stored procedures… Here’s an update to my ancient HTTP stored procedure to use both WinHttp and set that option. The Option property is an “indexed property” so calling it with sp_OASetProperty is wierd. … Read more
Well, “better” is subjective. There is some style here. But I’ll address your questions directly. Both perform the same Both are ANSI-compliant. The problem with the first example is that it is very easy to inadvertently derive the cross product (since it is easier to leave out join criteria) it also becomes difficult to debug … Read more
That is because null on either side of the addition operator will yield a result of null. You can use ISNULL(LiabilityPremium, 0) Example: ISNULL(l.LiabilityPremium,0) + ISNULL(h.LiabilityPremium,0) as LiabilityPremium or you can use COALESCE instead of ISNULL. COALESCE(l.LiabilityPremium,0) + COALESCE(h.LiabilityPremium,0) as LiabilityPremium Edit I am not sure if this is coincidence with this small data set … Read more
This has been documented well in MSDN , running through the steps similar to as @houseofsql mentioned Step1: Exclude column names in first row in excel connection, use sql command as data access mode Step2: Alias column names in output column as matching your destination, Select * from [Sheet1$A2:I] will select from second row Finally … Read more
I know that answer is accepted, but there is actually simplier way of doing it, if the only thing you need to do is select node value. Just use * as namespace name: SELECT MessageXml , MessageXml.value(‘(/*:Envelope/*:Body/*:FetchRequest/*:Contract/*:TransactionId)[1]’ , ‘varchar(max)’) FROM dbo.Message
The timeout is configurable via T-SQL with ‘user instance timeout’ option: sp_configure ‘show advanced options’, 1; RECONFIGURE; GO sp_configure ‘user instance timeout’, 5; GO The timeout is expressed in minutes and has a maximum value of 65535. I’m pretty sure you need to restart the instance after setting it. And don’t try setting it to … Read more
Another option would be as the return value for the stored procedure (I don’t suggest this though, as that’s usually best for error values). I’ve included it as both when it’s inserting a single row in cases where the stored procedure was being consumed by both other SQL procedures and a front-end which couldn’t work … Read more