When we create dynamic SQL code, we can add the parameters in two ways: Concatenate them into the dynamic code with + Use sp_executesql
In some situations a scheduled shrink of the log files is needed to release disk space. The script bellow can be scheduled in SQL Server Agent Job.
Let’s say that we have a SP with predefined parameters, i.e. @Parameter1 can hold values 1, 2 or 3; @Parameter2 can hold values of A, B, C or D and so on. This script creates all the possible combinations for the parameters of the SP and executes the SP as many times […]
Very often while exchanging data files with the clients, a useful information is stored in the file name. This can be a client name prefix, date, time, the period in the file and so on. While loading the data into the database, we need to extract the portions of the […]
Very often we make changes to the design of an underlying objects, used by views. After we do this, we need to refresh the views to apply the changes. In this example we will: 1. Create View 2. Change the design of an underlying object 3. Refresh all the views […]
Working with multi-value parameters in SSRS is always fun. We need to pick a good delimiter, add additional values like “ALL” or “BLANKS” and many others. In the following few lines I explain a logics to add a multi-value parameter to SSRS with additional values (all) to not apply the […]
In a lot of situations we may not have permissions to use SQL Server Agent for automations of our back-end processes. I built a run-around that uses a Windows Scheduled Task, SQLCMD and Stored Procedure to run task. Work flow: 1. Windows Scheduled Task calls a .bat file at specific […]
Data Warehouse is “a system used for reporting and data analysis”. In general it is built by dim (dimension) and fact tables (structure and data tables). Here I create a stored procedure to build Date Dimension. Now it builds only calendar data and will be expanded to include “fiscal” data […]