Sql Server Tricks and Tips

  From Long Hour Thinking Today I got Some Ideas About Dynamic Searching.

When We have to search with Multiple Search Conditions like 

select * from Customer where 

Name='CustomerNm' and CustNo='CustNO' and

CustID='CustID' and IsActive=1

then we can use it like 

declare @SQL Nvarchar(4000);

set @SQL='select * from tbl_suspiciousaccounts where 1=1 ';

 if(CustomerNm is not null)

BGEIN

 set @SQL=@SQL +'  and  cutomername= '+ CustomerNM

END 

 if(CustomerID is not null)

BGEIN

 set @SQL=@SQL +'  and CustID= '+ CustomerID

END 

see that SMART Usage of 1=1 condition;

  Update Local Table Using OpenQuery

            update D
            set D.LongName=L.LONGNAME
            from  
            OPENQUERY(CBSLINKED,'SELECT LONGNAME,PRDACCTID,LBRCODE FROM D009022 ') L
            JOIN tbl_SuspiciousAccounts D
             ON L.PRDACCTID=D.PrdId and L.LBRCODE=D.LbrCd
             where D.LongName is null

Using Several UNION ALL

( select * from table1

group by ...

order by ...) 

UNION ALL 

( select * from table2

group by ...

order by ...) 

UNION ALL 

( select * from table3

group by ...

order by ...)

Note: Always use Bracket is Union All

Use  Union All When We have to append Result of several Tables with each others.

Using Scheduler RUN Stored Procedures

Create Following Command

C:\Users\BsgIt>sqlcmd -E -S "BSGIT-PC\SQLEXPRESS2008R2" -d "BIAMLKYC_22April" -Q
 "exec PrintMessage"

Then.....

Create the schedule

1. Open task scheduler from Start >> Administrative Tools >> Task Scheduler
2. Click “Create Basic Task” and enter a relevant name, e.g. Daily SQL Backup
3. Under “Triggers” select ‘Daily’ and then choose a time to run the backup.
   Try to choose a time when your database traffic is low, typically in the early hours of the morning. Also make sure that it doesn’t overlap with other scheduled tasks like Windows Updates that may reboot the server.
4. Under “Action” select ‘Start a program’ and browse to the script you created earlier

Note: Same Can be Use For Backup Databases.