Converting a date/time to time_t using FxGqlC (or SQL)

The time_t is used in C++ to represent a date/time.  It is expressed in seconds since Januari 1st, 1970.

To get the current date/time as a time_t value, you can run this query in FxGqlC (or SQL):
select datediff(second, '1970-01-01', getutcdate())

You need to use getutcdate() because time_t defines the UTC time.

Or for any arbitrary date/time (in UTC):
select datediff(second, '1970-01-01', '2012-10-18 22:33') 
-- Returns 1350599580

The other way around is also easy: run this query to convert a time_t to a date/time
select dateadd(second, 1234567890, '1970-01-01')
-- Returns 13/02/2009 23:31:30

Get administrative rights on your own SQLExpress instance

When installing Microsoft SQL Server Express edition, only the user that is installing the database server software is added to the sysadmin role.  Other administrators of the host or domain don't have sysadmin rights on the server.

Being an (other) administrator, you can obtain the sysadmin role by running a cmd-script as indicated on this post.  After successful execution of this script, the current administrator user is added to the sysadmin role.  So you can log in using e.g. the SQL Server Management Database to create a new database.

Checking identity column overflow on all tables of a SQL Server database

The DMV (Dynamic Management View) query below can be used to get a list of all tables with an identity column, and the current value

SELECT o.name AS TableName,  c.name AS ColumnName,  t.name Datatype,  IDENT_CURRENT(o.name) CurrentID,  case t.xtype  when 48 then 127  when 52 then 32767  when 56 then 2147483647  when 127 then 9223372036854775807  else NULL  end MaximumID,  IDENT_CURRENT(o.name) * 100.0 /  case t.xtype  when 48 then 127  when 52 then 32767  when 56 then 2147483647  when 127 then 9223372036854775807  else NULL  end OccupationPrcFROM syscolumns c JOIN sysobjects o ON c.id = o.idJOIN systypes t ON c.xtype = t.xtypeWHERE  c.status & 0x80 = 0x80 -- 0x80 is an identity columnORDER BY 6 desc, 4 desc, 5

The query output contains these columns:

• TableName
• ColumnName: Column name of the identity column
• Datatype: Datatype of the identity column
• CurrentID: Current value of the identity column
• MaximumID: Maximum value of the identity column
• OccupationPrc: Percentage of IDs in use (100% * CurrentID / MaximumID).  When this column reaches 100, an overflow will happen.  The list is sorted descending on this column.

This query works for identity columns of datatypes tinyint, smallint, int and bigint.

Which database files are almost full?

The SQL Server query below can be used to get a list of all database files (data and transaction log), and their current size and the size that is currently in use.  The query uses the dynamic view (DMV) sys.databasefiles, which is present as from SQL Server version 2005.
The query is similar to DBCC SHOWFILESTATS, which also works on SQL Server version prior to 2005, but doesn't include the transaction log files.

-- Retrieve the occupation of all files of the current database

select file_name(file_id) FileName,

      fileproperty(file_name(file_id), 'SpaceUsed') UsedSize,

      size TotalSize,

      ceiling(fileproperty(file_name(file_id), 'SpaceUsed') * 100.0 / size) UsedPrc,

      physical_name FileNameOnDisk

from sys.database_files

--where type = 1 -- (for filtering TransactionLog)

Who is connected to my SQL Server database?

-- Who is connected to my SQL Server database?

-- (SQL Server 2005 and later)

select s.session_id, s.login_time, s.host_name, s.program_name, s.login_name

from sys.dm_exec_sessions s

inner joinsys.dm_exec_requests r onr.session_id =s.session_id

where r.database_id = db_id()