Null Usage Examples WHERE DeletedDate IS NULL
All posts by
WAITFOR
Use to block execution. While executing a WAITFOR statement the transaction is running and no other requests can run under the same transaction. Bear in mind that if the server is busy the thread may not be immediately scheduled. —-Delay for 20 seconds WAITFOR DELAY ‘000:00:20′ SELECT ’20 Second Delay’ GO —-Delay till 7 AM […]
General Insert Statements
Insert New Row (Record) Into A Table “INSERT INTO SomeTableName (SomeColumnName1, SomeColumnName2, SomeColumnName3) VALUES (” + Convert::ToString(ColumnValue1) + “, ” + Convert::ToString(ColumnValue2) + “, \'” + ColumnValue3 + “\’)”; In this example ColumnValue1 and ColumnValue2 are numbers and column value 3 is a string
General Delete Statements
Delete A Row (record) In A Table “DELETE FROM SomeTableName WHERE SomeColumnNameToMatch=” + Convert::ToString(ColumnValueToMatch)
General Update Statements
Updating A Column Value In A Table “UPDATE SomeTableName SET SomeColumnName=” + Convert::ToString(NewValue) + ” WHERE SomeColumnName=” + Convert::ToString(IdValueOfRecordToAlter) Assumes MyNewValue is numeric, otherwise surround with ‘ and ‘ Update 2 Column Values “UPDATE SomeTableName SET SomeColumnName1=” + Convert::ToString(NewValue) + “, SomeColumnName2=\'” + NewString + “\’ WHERE SomeColumnName=” + Convert::ToString(IdValueOfRecordToAlter);
Specifying Values In Statements
Numeric Values Simply use the value as is: “WHERE SomeFieldName=4” Text Values Surround the value in single qutoes: “WHERE SomeFieldName=\’This Is The Value\'” When you need to include single quotes in the string (e.g. ‘St Mary’s’) use a doudle single quote. E.g. in VC++: NewString = NewString->Replace(“\'”, “\’\'”); //Replace all ‘ with ” (needed if […]
Sub Queries
Matches A Value Returned From A Sub Query For Instance On Another Table “WHERE MyColumnName IN (SELECT SomeColumnNameToReturn FROM tblSomeTableName WHERE SomeColumnName=2)”
Backing Up SQL Server
Backing Up In Management Studio Rightclck the database > Tasks > Backup Backup type ‘Full’ Destination Backup to: Disk Add button Do the backup SQL Server Agent is a windows service in SQL server that runs scheduled jobs such as auto backup
Troubleshooting SQL Server
Connecting via network Use SQL authentication Server name: [pcnetworkname]\SQLEXPRESS,portnumber for example a working string used in the ‘Server name’ box: MY-PC-NAME\sqlexpress,1051 Check the log of the target PC’s SQL server to confirm the PC name and the port it is listening on SQL Server Log You can check the log a PC’s SQL Server: SQL […]
SQL Server Express Edition
Running SQL Server Management Studio on Win 7 On a Win 7 PC the local SQL server will often not be automatically offered and using (localhost) or browsing and finding the SQL server for the local PC caused a “Cannot connect to..” error. The solution is to use this in the server name box when […]