SQL: How to use “order by” with “select distinct”?
A query with SELECT DISTINCT can be rewritten using GROUP BY. So this [sql] SELECT DISTINCT city FROM HR.Employees WHERE country = N’USA’ AND region […]
Database
SQL Server: Error converting data type varchar to numeric.
Convert varchar to decimal error 1 Add another column with required dataType. [sql] ALTER TABLE [table name] ADD [column name] decimal(10,2) NULL [/sql] Import all […]
SQL Server: How to SELECT vertically/ horizontally?
name maths science english sex aaa 11 22 33 female bbb 44 55 66 female ccc 77 88 99 female Use UNPIVOT [sql] select u.Test2, […]
SQL Server: Job – How to find jobs calling a specific stored proc?
[sql]SELECT j.name FROM msdb.dbo.sysjobs AS j WHERE EXISTS ( SELECT 1 FROM msdb.dbo.sysjobsteps AS s WHERE s.job_id = j.job_id AND s.command LIKE ‘%store_proc_name%’ ); [/sql]
SQL Server: Debug – Why does query statement fail when there is only one record being added to reference table?
Because the query statement is too long, so the next added record may corrupt the query Because the reference table number is limited at 256 […]
SQL Server: Debug – Why can’t I find a stored procedure by SQL Profiler?
Because that stored procedure is executed inside a stored procedure. Stored procedure executed inside a stored procedure is not hit by SQL profiler (?) Because that […]
SQL Server: Debug – How to check temporary table created by INSERT INTO?
Get the table structure by [sql]exec tempdb..sp_help ‘#tempTableName'[/sql]
SQL Server: How to trace down database activities?
Create trigger to log activities Record actions happening on CREATE/ALTER/DELETE stored proc Create table to save actions happening [sql] CREATE TABLE dbo.ProcedureChanges ( EventDate DATETIME […]
SQL Server: How to use SQL Server Profile
How to trace which stored procedures are called Open “Trace Properties” window Select “Event Selection” tab Check “Show all events”, “Show all columns” tab Check […]