It’s been a while when I wrote my last article. But thanks to all your comments, suggestions and support which motivates me to keep writing and today, I am starting a new column in my blog i.e. SSRS. :)
Problem Statement: Many times the DBAs, administrators and even the top managers would like to see and track the daily status of all the JOBs running on a server i.e. specifically the production servers. But not all the people have access to production servers, except the administrators and publishers. Hence, they are expecting some other way which not only gives information about the status of all the jobs but should be easy enough to understand it.
Solution: Why not to create a custom report to do the same job what “Job Activity Monitor” is doing. Even we can do better as per our requirement. Hence, I am going to show how to build a custom report in SSRS to display the status of all the jobs running under a given server.
Read more…
In big enterprise or organization, it is very common that they use GUIDs to uniquely identify each of Business Keys for any subject matter like EventIDs, RegistrationIds, or even the master data like Categories, hierarchies, etc. The reason behind using GUIDs is that a GUID is unique in nature across any platform, any server and at any point of time. The earlier versions of GUIDs contains encrypted MAC address with date component when it is got created. But in order to resolve individuals’ privacy issue, the algorithm got changed to more sophisticated form. Practically it’s a very huge number i.e. a 128 bit integer. We can store up to 2128 unique keys. So generating a same unique randomly twice is negligible.
The global representation of GUID is {00000000-0000-0000-0000-000000000000} and the digits on each place are dependent on the type algorithm used to generate the unique key. (format » {8-4-4-4-12}, total 36 characters including ‘-‘)
But SQL Server UniqueIdentifier data type stores GUID without including curly braces like 00000000-0000-0000-0000-000000000000. In this post, I will show how to import / export GUID columns from excel to SQL Server database by using SSIS.
Read more…
Many times upon executing any T-SQL queries, we like to know the performance of the query at millisecond level. But SSMS provides us information at second level as below:
SELECT *
FROM Purchasing.vVendor;
Read more…
Problem: Mr. Coach has Team A and Team B whom he wishes to play for different countries. Provided the given conditions satisfied:
- Team A and Team B both shouldn’t play in the same country at same time
- Team A and Team B should play at least once for each country.
- Number of matches played will be same as number of locations.
Now Mr. Coach needs little help in scheduling matches for each of his team along with the above constraints. Let’s check out the solution.
Read more…
One unbelievable tip for the day for T-SQL users. What a “GO” statement do in T-SQL.
By definition it is a batch separator. It will run a batch and commit it. But along with that it also accepts one parameter which tells the SQL engine basically how many times to do the job.
Read more…
Exploring each table in SSMS, just to check whether a particular table has IDENTITY property set or not i.e. presence of any IDENTITY column in the given table; might be a cumbersome task, especially when the number of tables in the given database is more than hundred. We need some shortcut to perform this type of tasks. Let’s discuss what identity property is and how to use some shortcuts i.e. custom procedures to get what we want.
Read more…
Although we have .Members and .Children for navigating through members of a particular dimension, but as a BI user, we need more flexibility in traversing through each level of the hierarchy. And For this, MDX has one more amazing function called DESCENDANTS, which not only provides listing of current level members but also has functionality to display Parent and Grand-Child members till the leaf level.
We will first discover the pattern of hierarchical data and the functionality of DESCENDANTS function over it.
Read more…
Although SQL Server has a big range of functionalities and is powerful to handle almost all type of data processing, but still there are some corners where it fails to do its job predominantly like pattern searching for text analysis, file operation, process calls, etc. or even when you want to perform some complex operations outside the world of database like custom mail operation, FTP operations on the basis of some dataset, uploading or downloading from internet or remote systems.
Read more…
Day by day the complexity of data has increased so much, resulting in demand of more complex analysis and reports. Days are gone when the analysts are happy with simple scorecards and dashboards with basic aggregation and computation and with some trend charts. But now they need more complex reports, more accuracy in results with better insights to lead in the information technology.
Read more…
Sp_MSForEachDB is a great procedure coming with MS SQL Server even though it is un-documented. It eases some of the complex operations that we mostly do by using CURSORS.
Read more…
Recent Comments