2019-09-25 11:57
Using below two SQL queries we can retrieve who has what access on the database server.I have divided it into two-parts1. Role access [ sp_addsrvrolemember & sp_dropsrvrolemember ]2… Read More
Blog Directory > Software Blogs > Nothing is Bug Free software Blog >
Nothing Is Bug Free Blog
nobugfree.blogspot.com
Tags:
backup
database
left join sysdatabaseprincipals
collate sqllatingeneralcpcias
loginame medswadmin nameindb
history
login
spadduserexec spadduser loginame
dbname
server
Share reusable code, script, component. Reusable Database scripts. Different kinds of automation tools like SSIS, Batch
2019-09-24 10:51
Use below script to shrink T-LOG files. This script will shrink the log file which is larger than the value of the variable "@LOG_SIZE_LIMIT". You can change it to meet your requirement.To r… Read More
2019-09-24 10:39
Using the below script we can take all databases backup with one command!!You can check my other post BACKUP STATUS to know the current DB backup status. exec sp_msforeachdb 'use [?];IF ''?… Read More
2019-08-19 07:46
Always I like to use below SQL to find the backup history either DB backup or LOG backup. this is very handy and useful.By this SQL you can find the backup DB name, date-time when the… Read More
2019-04-17 19:57
Sometimes it's very important to know whether database or log backup or restore is running or not!in a word, if we need to check the stat/status of a database, we have to run below… Read More
2019-04-16 20:11
In our day to day work in SQL Server, we have to restore the database frequently. But the problem is the users which are also restored with the bak file and remain unmapped. In that cas… Read More
2019-04-11 21:51
As a DBA or Developer, it is very important to know how much access I have or a user has?I am working to transfer access from a server to another server. for what I just write below tSQL to… Read More
2019-03-28 12:12
Sometimes we need to read Dirty / Uncommitted records to confirm the changes made by other users.To read uncommitted records we have SET the Isolation level to read uncommitted. check below… Read More
2019-03-25 13:19
If we use Solarwinds DPA to monitor the DB health and performance and want to get notified about the data file space and long-running sql queries, then first of all we have to gran… Read More
2019-03-15 08:29
You can use below tSQL to grant any permission to a user.Just modify it as your requirements.Below script can be used to grant Read & Write access to all user databases...NOTE: you… Read More
2019-03-14 08:47
Sometimes SQL Server job history doesn't show that a job is running currently or not.For what we need to check this using SQL.Check my other post to "View Job History using tSQL, SQL Se… Read More
2019-03-13 14:03
We can view job history using SSMS very easily. But when the amount of log is too high in count, SSMS got timeout exception to view the job history.For what, I always use below SQL to c… Read More
2019-03-13 08:13
Using below tSQL we can check the database size,this sql will list all database (system & user)------------------------------SELECT dbs.name [DB name],CONVERT(VARCHAR,SUM(… Read More
2019-02-08 06:04
When you need to grant the user to access (read/manage) "SQL Server Agent", you can do it using either using SSMS or tSQL.There are three (3) fixed database roles available under "msdb" data… Read More
2019-02-06 11:22
First, check that the SQL Server service is installed in the machine/server bu using "services.msc".For that, press WindowsKey+R to get the Run dialog. Then type "Services.msc". Then find th… Read More
2019-02-01 10:24
If you get the error "The specified service does not exist as an installed service.", please dont be panic!This is a very common issue. Follow below steps to ride on it.1. Fir… Read More
2019-01-31 09:34
You can say this process as BULK Restore of .bak file.Using below script we can search and restore bak files; Just we need to follow some pattern.Make sure the .bak file names start with DB… Read More
2019-01-29 08:28
Control and Loop Statements in R.There are 2 types of Control/Conditional statements and 3 types of Loop available in R. see belowSee Also: Introduction to R/ Basic functions of RConditional… Read More
2019-01-25 13:55
To start programming in R, we have to understand the below topics first ...1. Variables/Objects with Data Types: like other programming languages, R also has Numeric (int & float)… Read More
2019-01-24 07:30
To use the benefit of partition, we have to create a partition scheme, partition function and then attached it to the Tabel/Index.See also - Add additional partition to an existing… Read More
2019-01-24 07:28
To add an additional partition to an existing partitioned table, we have to follow below steps...See Also - Create a Partition & use in Table/Index (Existing/Not)NOTE: it is not man… Read More
2018-11-16 12:52
By executing below SQL Query, you will get the list of connected users with the last running SQL Statement by each user...Who is connected ---------------------CREATE TABLE #WHO_TEST( [… Read More
2018-09-21 10:33
Watch GTV Live | Enjoy all Cricket match of bangladesh ;) Read More
2018-09-03 11:26
I have attached my SD Card as Internal Storage in my Samsung Galaxy J2 Prime without Root my phone, and its working fine fo...me.. please see below to perform the same in your phone.we need… Read More
2018-08-29 09:08
When our database size was too large, we need to compress or shrink data to reduce the disk/file/database size. I will provide some guidance to reduce the database/index size to the minimal… Read More
2018-08-16 09:00
To Import a CSV file (with header) to the SQL Server database, I will take an HTTP Listener to call the process then read the CSV file and insert to DB as BULK INSERT. Import CSV F… Read More
2018-08-02 14:13
When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.In my exampl… Read More
SSIS Task To Create Excel Report/ Export Data To Excel With Header Using SQL Statement, With Example
2018-08-01 11:29
When we plan to automate a Process/System; sometimes we need to generate an Excel report to check the process status or create QC report using Excel to deliver or for internal use. Also some… Read More
2018-07-06 09:31
When you plan to Mirroring Database, first you have to prepare the Secondary Database.Follow below steps to prepare the secondary database for mirroring...STEP 1: Take Backup of Princi… Read More
2018-07-04 06:40
First of all download the SQL Script, UnPivotTable.sql. Run it on your database, i.e. TEST_DB_UNPIVOT. A stored procedure, [dbo].[UnPivotTable], and a supporting function, [dbo].[SplitString… Read More
2018-07-04 06:38
This script is a parameterized stored procedure, [dbo].[DB_BACKUP_TO_DISK], to backup SQL Server Database (.BAK). Everything is to call this procedure with all parameters. See belo… Read More
2018-07-04 06:36
Sometimes we have to Pivot/De-normalize source/output data which arrives as UnPivot/Normalize.For that, I have created a dynamic procedure, which will create a new table with Pivoted data. A… Read More
2018-07-04 06:33
This post will describe the use of Assembly type DLL without installing it on GAC Always we have to install an assembly first then we can use the DLL file into C# code. But when we… Read More
2018-07-02 10:25
When we use SQL Server Locally/Home PC, it is not possible to get Domain User to practics Mirroring.Now I will describe how we can Mirror SQL Server Database, locally/Without Domain User, to… Read More
2018-06-28 08:18
Sometimes we fall in problem when need to mirror multiple databases by using different Windows-Authentication and Certificate.in this case, we have to create Endpoint using both authenticati… Read More
2018-03-30 08:08
Today, I will discuss how to Read an Excel File.Read from an Excel file: Follow the below steps 1. Create a connection, OleDbConnection, to read the excel2. Retrieve all… Read More
2018-03-28 07:07
Today I am sharing a Tool/exe to send email from anywhere instantly!always we so redundant task/ write code for the same like email sent, which is frequently used to automate a sys… Read More
2018-03-27 10:36
I will connect to SQL Server database and execute a procedure to get data.a procedure can provide data by Return or as a Select statement.I have discussed the full process step-by-step and a… Read More
2018-03-07 15:28
Today I am sharing a procedure by which we can easily create a dimension for a data warehouse. this is very much dynamic and you know dynamic things are not so easy BUT I have tried to creat… Read More
2018-03-07 13:21
We have to do many things to access FTP on our day-to-day works. sometimes we write the same thing many times for the same...Now, I am sharing my tool to upload/download/move/remove from/to… Read More
2017-11-29 09:51
Let, we have already created an SSIS custom task named HelloWorldTask. now we like to add an icon.see my post to create the HelloWorldTask... also you can download my ready solutio… Read More
2017-11-27 12:48
i assume we have a dll of our SSIS custom task. in my other post, i have already share "how to create a SSIS custom task very easily".assume our DLL name is : TestTask.dllnow follow the… Read More
2017-11-20 15:24
Here i am sharing the easiest way to create a SSIS custom task.. this a "Hello World" Task.by following this way can can create your own task very easily. also i have uploaded the solution f… Read More
2017-11-10 11:33
I am using below code/class to split and zip a large amount/size of files using JAVA.I have tested this code/class on below - number of uncompressed files : 116 - total size (uncom… Read More
2017-11-10 08:39
We have to do many things to access FTP on our day-to-day works. sometimes we write the same thing many times for the same...Now, I am sharing my tool to upload/download/move/remove from/to… Read More
2017-05-16 15:02
using below tool, you can backup a database, with all user defined objects, as a script and save it to the file.... without any third party/extra tool.NOTE: download the ZIP file "DBScriptGe… Read More
2017-02-10 13:04
This post will describe the use of Assembly type DLL without installing it on GAC Always we have to install an assembly first then we can use the DLL file into C# code. But when we… Read More
2017-02-03 17:42
Sometimes we have to Pivot/De-normalize source/output data which arrives as UnPivot/Normalize.For that, I have created a dynamic procedure, which will create a new table with Pivoted data. A… Read More
2017-02-02 11:21
NOTE: In 2016 version of SQL Server, a new function STRING_SPLIT (GivenString, Separator) has been implemented but for below 2016 version has no any string function like… Read More
2017-01-20 14:59
Sometimes we need to send task/operation status report as a well-formatted mail, known as HTML formatted mail. So that, we need to setup a DB mail environment to provide mail from DB end.Als… Read More
2016-02-04 15:31
This script is a parameterized stored procedure, [dbo].[DB_BACKUP_TO_DISK], to backup SQL Server Database (.BAK). Everything is to call this procedure with all parameters. See belo… Read More
2016-01-12 18:25
SQL server’s function doesn't support DML (Insert, Update & Delete)/EXEC () procedure. Shows “Invalid use of a side-effecting operator 'EXECUTE STRING' within a function&rdqu&hell…Read More
2016-01-12 08:07
master..xp_cmdshell used to execute windows command like cmd/PowerShell.Execute below commands under master database to enable xp_cmdshell extended procedure EXEC sp_configure 'show ad… Read More
2015-12-31 10:08
This script will help us to retrieve metadata/column information from a SQL Statement and/or a Table. Download this script, RETRIVE_METADATA_SCRIPT.sql.This script will provide the below&nbs&hell…Read More
2015-12-21 06:35
This script is a stored procedure to check duplicate record of a table or output of a SQL statement by considering all columns. Download the script file Duplicate Record Check.sql. Run it on… Read More
2015-12-21 06:32
Execute SSIS package from C# / SSIS script with configuration file and/or preset variable value and get all types of messages/events from that package like information, error, warning etc.Fo… Read More
2015-12-20 15:21
Actually there is no any built-in property or member to order variables and/or categories. By default system order variables as their position and for categories use alphabetical order. So w… Read More
2015-12-20 05:24
see also -- Got Error: Invalid object name 'master.dbo.spt_values'As usually we use below connection string to connect to SQL Server using OLEDB provider.Data Source=DBSERVER;Initial Ca… Read More
2015-12-11 20:01
Create a script task and try below code to configure html mail with a file as attachment and an embedded image/videothen send it. No required SMTP SSIS connection.Below is very much complete… Read More
2015-12-10 18:57
see also -- Blank "initial catalog" problem on OLEDB connection string of SQL Server DatabaseWhen you tried to view properties of a database in SQL Server, you got the error " Invalid o… Read More
2015-12-10 18:55
see also -- Java security to deploy ODK Aggregate at Google App Engine -- Revoke/Remove App-Specific Password from Google AccountIf y… Read More
2015-12-10 18:53
see also -- Google 2nd step verification to deploy ODK Aggregate -- Revoke/Remove App-Specific Password from Google AccountYou have t… Read More
2015-12-10 07:20
First of all download the SQL Script, UnPivotTable.sql. Run it on your database, i.e. TEST_DB_UNPIVOT. A stored procedure, [dbo].[UnPivotTable], and a supporting function, [dbo].[SplitString… Read More
2015-12-10 07:17
You have to use SQL Server relational operator UNPIVOT/CROSS APPLY to UnPivot data from Pivoted data. · For single measure its simp… Read More
2015-05-03 09:38
Now at this time of technology you can transfer your files from one system to other, i mean cross platform like iOS from/to Android and Desktop PC and all of this are vice versa.all of this… Read More
2015-04-26 14:30
If you use 2ne step authentication for you Google account, then you have to use an App specific password to deploy ODK Aggregate.Login to Google account. Go to “Account settings”… Read More
Subscribe to Nothing Is Bug Free
Get updates delivered right to your inbox!