Differences

This shows you the differences between two versions of the page.

--- mssql [2012/09/26 14:02]
mantis
+++ mssql [2012/09/26 14:02] (current)
mantis [Date conversion]
@@ Line 1: / Line 1: @@
+===== Cheat sheet for MSSQL 2008 =====
+Get table names from database
+<code sql>
+select table_name from information_schema.tables
+</code>
+Show table structure
+<code sql>
+exec sp_columns <tablename>
+</code>
+Limit output to x lines
+<code sql>
+Select top <x> * from <tablename
+</code>
+===== Dates and times =====
+==== Date conversion ====
+The magic number in the convert function determines the datestyle: 120 == ODBC canonical.
+More about converting [[http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx][here]]
+Select events that are active at a certain time:
+<code sql>
+SELECT event, starttime, endtime
+FROM table
+WHERE  convert(datetime,'2012-08-04 12:00:00',120) BETWEEN starttime and endtime
+</code>
+===== Python and mssql =====
+http://code.google.com/p/pymssql/wiki/PymssqlExamples
+===== Caveats =====
+Error message: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier."
+can be resolved by adding **tds_version = 8.0** to your odbc.ini
+(the Why is nicely summarised e.g. by Ubuntitis http://www.ubuntitis.com/?p=64)

Small heaps of code