;with d(date) as ( select cast(’10/01/2012′ as datetime) union all select date+1 from d where date < ’10/15/2012′ ) select t.ID, d.date CDate, isnull(t.val, 0) val from d left join temp t on t.CDate = d.date order by d.date OPTION (MAXRECURSION 0) — use this if your dates are >99 days apart You need to … Read more
N.B. – this question and answer relate to the 2000 version of SQL Server. In later versions, the restriction on INSERT INTO @table_variable … EXEC … were lifted and so it doesn’t apply for those later versions. You’ll have to switch to a temp table: CREATE TABLE #tmp (code varchar(50), mount money) DECLARE @q nvarchar(4000) … Read more
You can’t pass in a function call as an argument to your stored procedure. Instead use an intermediate variable: DECLARE @tmp DATETIME SET @tmp = GETDATE() EXEC DisplayDate @tmp;
Here is my own answer to this question (Please also see the update at the end): No, there isn’t such unary operator defined on the String expressions. It is possible that this is a bug. Explanation: The given statement is valid and it generates the below result: (No column name) —————- ABCDEF (1 row(s) affected) … Read more
Enable the options Allow Remote Clients Allow Outbound On Security tab of Local DTC Properties in Component Services. Go To Run, type comexp.msc. Double click “Console Root”. Double click “Component Services. Double click “Computers”. Double click “My Computer”. Double click “Distributed Transaction Coordinator”. Right click “Local DTC” under “Distributed Transaction Coordinator”, and click properties. Click … Read more
You’ll need your recursion to also build something that can be sorted by at the end: declare @t TABLE ( [CHILD] [int] NOT NULL, [PARENT] [int] NOT NULL ) insert @t values ( 0, -1), — I added this ( 1, 2 ), ( 2, 0 ), ( 3, 1 ), ( 4, 2 ), … Read more
You don’t have an equivalent function for that, but you can still simulate (make useof CROSS APPLY and FOR XML PATH(”)). example, USERID ADDRESSLINE1 ========================== 1 First Street 1 Second Street 2 32th Street 2 24th Street 2 25th Street will result USERID ADDRESSLIST ============================ 1 First Street, Second Street 2 32th Street, 24th Street, … Read more
SET @SQL = ‘SELECT * FROM ‘ + @table EXEC (@SQL) — parentheses are required
See this excellent article here or this blog post for a long explanation how to do it. Basically, you need to use a SqlDataReader and specify SequentialAccess to it when you create it – then you can read (or write) the BLOB from the database in chunks of whatever size is best for you. Basically … Read more
There are situations where the redundancy doesn’t hold. For example, say ColumnC was a huuge field, but you’d sometimes have to retrieve it quickly. Your index 1 would not require a key lookup for: select ColumnC from YourTable where ColumnnA = 12 On the other hand index 2 is much smaller, so it can be … Read more