I just encountered a problem where I needed a job to run if the the same job ran ran over an hour ago. I stored the last run time in a cookie, poll it, and compare the last run time using SQL’s datediff() function. I check the number of hours since the job last ran thus:
datediff( hh, getdate(), last_run_time )
Then I wondered:”Does datediff() return 0 if the two datetime parameters have the same hour, but different dates? What if I get the hours diff between ‘06/11/2009 10:27:38′ and ‘06/17/2009 10:27:38′?” Whould would I see? Would I see 0? Or would I see the number of days * 24 + the actual hours different in the day?
It turns out the datediff() returns 168. This is good news.
It’s not ungrant.
It’s revoke, as in
revoke all from [user]
So many things I’ve learned in the past two weeks. Here’s one.
Suppose I’m running a stored proc, how can the stored proc know who’s calling it and from where is it being called from?
Ah- a trick.
In the stored procedure- just call:
To get the process id of the user process calling the stored procedure:
select @@spid
To get the hostname of the user who called the stored procedure:
select hostname from master.dbo.sysprocesses where spid=@@spid
A mystery error popped up today.
Server: Msg 4606, Level 16, State 1, Line 3
Granted or revoked privilege EXECUTE is not compatible with object.
I’ve never seen it before. It was on a GRANT EXECUTE command on an SQL function.
I googled- and found that EXECUTE permissions are not grantable for functions. For table- and there’s two types of functions- there’s two sets of allowable permissions:
Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Scalar functions return one single return value. Table-valued functions return a dataset.
Just to be safe, I just decided to do a GRANT ALL.
Learning something new everyday. Now reading Ken Henderson’s “The Guru’s Guide to SQL Server”. In all my years with TSQL, I never knew you could use the results of a stored proc as input into an Insert command. I’ve always used either only explicit values or selects as inputs to an insert.
Here’s an example:
CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname null, indid int, type char(4), resource char(15), mode char(10), status char(6))
INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)
EXEC dbo.sp_lock
SELECT * FROM #locks
DROP TABLE #locks
And it was only a few months ago that I started using functions in SQLServer. Here’s an example:
create function dbo.fn_grd_get_mtm_recid( @value_date datetime )
returns
@rec_id_results table
(
MAIN_BOOK varchar(10) not null,
COB_DATE datetime not null
)
as
begin
select MAIN_BOOK, COB_DATE from TestTable
return
end
Creating a primary key in SQLServer:
create table dbo.MyMainTable (
key int not null primary key,
data varchar(20) not null
)
Creating a foreign key in SQLServer:
create table dbo.MyDependentTable (
fkey int not null references MyMainTable( key ),
childdata varchar(20) not null
)
.Net assembly exposed as a COM component
Something interesting came up today. It’s possible to expose a .Net assembly as a COM accessible object by using the RegAsm tool. By default, always use RegAsm with the /codebase option- otherwise, at runtime, the client will choke on a cryptic ‘File Not Found’ error. RegAsm inserts the proper entries for the .Net assembly into the registry for COM-compliant tools to examine and invoke. Also, for runtime discovery tools like VBA and VB6, RegAsm can generate a typelib such that these tools can have at compile time access to the .Net assembly’s exposed methods.
Since COM is the invocation and communication layer, once the .Net assembly is registered as a COM object, it can be invoked just like any COM object.
Updating SQL Views
I didn’t know it’s possible to update a View- until today. Astounding! This means SQL Server breaks up the components of an update- down into the View’s constituent tables. That’s seriously cool.
I ran into a problem today where I had to pull table names out of a table and then perform delete/insert operations on those tables. The problem is- I’ve always used SQL against “known” static tables. How do I code SQL to operate on tables whose identities I only find out at runtime?
Turns out- T-SQL already has a solution for this. It’s the exec command. While it’s commonly known that exec is short for execute and is commonly used to kick off stored procs, what I didn’t know was that it also can take a string parameter containing any SQL operation to execute. Whoa. This dramatically opens up an entire world of ability.
Now I can generate the SQL at runtime and kick off the command all within a stored procedure. Notice the sample stored procedure below:
create procedure dbo.MDS_Backup @num_days_to_keep int
as
begin
declare @backup_date datetime
select @backup_date = getdate()
— loop through list of tables to backup
declare @table_name varchar(100),
@backup_table_name [...]
In all my experience with SQL, I’ve never had to use the CASE statement until now. It pretty much handles alternating scenerios on a per-row basis without having to resort to cursors- which as we all know is extremely slow in performance.
Here’s one example:
insert into #tmp (
ID, Name, AssetID, BuySell, NumShares, Price, Amount,
Date, Broker, Commission, Comment, SettleDate )
select t.ID,
a.Name,
a.AssetID,
t.BuySell,
t.NumShares,
t.Price,
t.NumShares * t.Price,
convert(varchar(12),t.Date,101),
t.Broker,
t.Commission,
case
[...]
I am absolutely floored by how much more productive Boost Spirit is over Lex/Yacc. There’s absolutely no comparison. Whereas I was still struggling with Lex/Yacc usage for over a week, I got a minimally functional Spirit parser working in a little under two hours.
I first heard of Boost Spirit back in the summer of 2005. But this is the first time I’m really driving it. It’s flabbergasting.
Here’s the sample test code.
#include "stdafx.h"</code>
using namespace boost::spirit;
using namespace std;
string col_name;
void do_date(char const *beg, char const *end )
{
col_name.assign( beg, (end – beg ));
}
int main(void)
{
int count_a = 0;
int count_b = 0;
vector column_name;
// look for keywords JOIN and ON
string input("JOIN DATE ON TCN");
// define allowed column name identifier
rule<> char_ident_start = alpha_p | ch_p('_') ;
rule<> char_ident_middle = alnum_p | ch_p('_') ;
rule<> ident = char_ident_start >> * char_ident_middle;
// define blanks
rule<> blanks_p = * blank_p;
// as_lower_d(["join"] is the same as as_lower_d[str_p("join")]
rule<> join_p = as_lower_d["join"] [increment_a(count_a) ];
rule<> [...]