I’d always stick with the smallest data type I can to store this. SQLServer: BIT Oracle: NUMBER(1) (or BOOLEAN in PL/SQL) MySQL: TINYINT (iirc BOOLEAN maps to this automatically) Edit: Oracle’s BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.
Here are my stored procedure error-handling guidelines. Call each stored procedure using its fully qualified name to improve performance: that’s the server name, database name, schema (owner) name, and procedure name. In the script that creates each stored procedure, explicitly specify which roles are allowed to execute the procedure ,eg public or whatever. Use sysmessage, … Read more
I’m not completely sure, but I get the impression that this question is really about upsert, which is the following atomic operation: If the row exists in both the source and target, UPDATE the target; If the row only exists in the source, INSERT the row into the target; (Optionally) If the row exists in … Read more
like said before best would be to set datatype of the column to nvarchar(max), but if that’s not possible you can do the following using cast or convert: — create a test table create table test ( a text ) — insert test value insert into test (a) values (‘this is a text’) — the … Read more
SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID The above query is the accepted answer but it will not work for the following scenario. Let’s say we have to find the employees with the highest salary in each department for the below table. DeptID EmpName Salary Engg Sam 1000 Engg Smith 2000 Engg Tom 2000 … Read more
Try this out: procedure TForm2.Button1Click(Sender: TObject); begin try Connect; SQLMonitor1.SQLConnection := SQLConnection1; SQLMonitor1.Active := True; ExecuteQueries; SQLMonitor1.SaveToFile(‘D:\\Log.txt’); except on E: Exception do ShowMessage(‘Exception ocurred!: ‘ + E.Message); end; end; procedure TForm2.Connect; begin SQLConnection1 := TSQLConnection.Create(nil); SQLConnection1.ConnectionName := ‘odbcinterbaseconnection’; SQLConnection1.LoginPrompt := False; SQLConnection1.LoadParamsOnConnect := True; SQLConnection1.Connected := True; end; procedure TForm2.ExecuteQueries; var Query: String; begin try … Read more
If you know quantity of columns, but don’t know its names and types, you can use following trick: select NULL as C1, NULL as C2 where 1 = 0 — Returns empty table with predefined column names union all select * from Test — There should be exactly 2 columns, but names and data type … Read more
sysmail_faileditems will only get you the list of failed emails. If you need to see a list of successfull emails you need to use sysmail_mailitems. Use the below query to get details for all emails sent the same date: SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE()) And here is the complete query to … Read more
Here’s the fully automated restore T-SQL stored proc. Accepts three(3) parameters. Target Database Source Database Fully Qualified backup file name location (\\yourserver\yourshare\backupfile.bak or simply c:\backup.bak) CREATE PROC [dbo].[restoreDB] @p_strDBNameTo SYSNAME, @p_strDBNameFrom SYSNAME, @p_strFQNRestoreFileName VARCHAR(255) AS DECLARE @v_strDBFilename VARCHAR(100), @v_strDBLogFilename VARCHAR(100), @v_strDBDataFile VARCHAR(100), @v_strDBLogFile VARCHAR(100), @v_strExecSQL NVARCHAR(1000), @v_strExecSQL1 NVARCHAR(1000), @v_strMoveSQL NVARCHAR(4000), @v_strREPLACE NVARCHAR(50), @v_strTEMP NVARCHAR(1000), @v_strListSQL … Read more
use outer/cross apply: select * from Employee as E cross apply dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TC if you still have to filter by TC.EmployeeId = E.EmployeeId, you can do this with subquery: select * from Employee as E cross apply ( select TT.* from dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TT where TT.EmployeeId = E.EmployeeId … Read more