 SQL | | Structured Query Language | | | ANSI SQL Reference | | SQL has many dialects and flavors, but the core SQL specification is the ANSI standard. SQL dialects are supposed to extend this core, keeping compatible with it. |
| Joining Tables in SQL Queries (JOIN) | | About the usage of the JOIN statement and its variants to produce powerful queries from many "joined" tables (INNER JOIN, OUTER JOIN, LEFT/RIGHT JOIN) |
|
|
| Tips | | |
| SQL Server | | The Microsoft major DBMS | | | Documentation | | |
| Tools | | |
| Tips | | | | Change Owner of an Object in SQL Server 2000 | Use the native SQL Server stored procedure sp_changeobjectowner[object_name], [new_owner] |
| Change Owner of an Database in SQL Server 2000 | Use the native SQL Server stored procedure sp_changedbowner [database_name], [new_owner] |
| Grant user permissions for all stored procedures at once | In SQL Query Analyzer run:
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO [username]' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
Copy the resulting recordset and paste and run it in SQL Query Analizer (replace [username] with the proper SQL Server user name). |
| "Command text was not set for the command object" ADO error when calling a stored procedure in SQL Server | This error is returned by an ADO call to a SQL Server stored procedure running multiple transactions.
The reason seems to be that SQL Server by default returns the number of affected rows after running each transaction.
Add Set nocount on to the store procedure declarations to avoid this.
It is also healthy to add SET XACT_ABORT ON. This orders SQL Server to rollback automatically the whole transaction if any of them fails. |
|
|
|
|
| MySQL | | | | Set record creation date and time as default value for a field |
- Sets the field with current date/time in every
INSERT and UPDATE:
Declare the field of type TIMESTAMP and set default value property to NULL
- Only in the
INSERT: you must declare that explicity in the statement INSERT INTO table (date_created) values (now())
Attention: check the link!
|
| Get the ordinal for the rows in a MySql SELECT clause | You can get the row ordinal (position number of a row in a view) by using a variable in the SELECT statement. The trick is to initialize and increment it in the same clause:
select if((@row<=>NULL), @row:=1, @row:=@row+1 ) as Ordinal, field1, field2, ... from table1 ... order by field1
Note the use of the null-save comparisson symbol <=> (equivalent to = but that gives the same results even if null values are involved. |
|
|
| MS Access | | |
|
