All Entries in the "Databases" Category
0×8002801D Library not Registered.. Cannot create any BI project.
An annoying error coming up while creating any Business Intelligence project from SQL studio. Error says that it cannot open a specific temp file. I have deleted all my BI settings but not good enough for creation of a BI project. Reparing SQL server installation also did not work also.
Cause: Some installation/uninstallation have corrupted the [...]
Removing Duplicates through SQL Query
Suppose we have a table in SQL Server, that has duplicates in it.
CREATE TABLE T1 ( ProductName varchar(50) )
INSERT INTO T1 VALUES (‘Computer’), [...]
Calculating difference of dates in Oracle
How to calculate difference between two dates in oracle, as subtraction of dates returns numeric value as difference. With some slight changes/enhancements we can track back the subtraction values to some readable date formats:
Here are some examples, these can also be amended for user convenience:
SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 [...]
ORA-38029 : object statistics are locked Recently
This error comes when analyzing tables in oracle. Basic issue in this error is when you import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:
select table_name, stattype_locked
from dba_tab_statistics
where owner = ‘MBS’ and stattype_locked is not null;
Then how to [...]
SqlBulkCopy – Bulk Insertion in SQL Server from .NET
Many a times there come a scenario when you have to insert large number of records in sql server. There are number of ways to implement this scenario:
through looping techniques to call insert statements for one record at a time.
Serialize the data in CSV or XML format and send them as a parameter to a [...]
Write a Custom SQL Scripter (Generating bulk insert, update statements)
There are always some static tables in our database that needs to always have some default values in them, for our custom application development. Usually a huge amount of data resides in these tables because they are slowly changing dimension fields. So when it is time to transfer this data the question arises how to [...]
Creating Table dynamically in Oracle Procedure
How can we create a table inside an Oracle procedure.
"CREATE TABLE tableName AS". We cannot execute this statement in procedures because upon creation of procedure it would give syntax error.
We can implement this with the help of EXECUTE IMMEDIATE command and then dynamically create table.
create or replace procedure Proc1 as [...]
Killing Locked Sessions in Oracle
Many a times user sessions are locked in some DML/DDL operation, than how to unlock those sessions because a new step or operation could not be done on the specific object that is locked.
Some really easy steps to unlock an object or kill a session in oracle are as follows:
To view locked sessions in oracle:
select [...]
Creating Custom Functions in Oracle
Here it is a simple procedure to create and call a custom function in Oracle. A custom function is a simple PL/SQL subprogram that is used to calculate a value. It creation syntax is almost same as of procedure except it has a RETURN clause that is used to return the computed value.
Syntax of creating [...]
How to check Numeric value in Oracle
Currently i could not find any suitable function for checking numeric value on some column. There are many workarounds to achieve this functionality, either cast the column value in Numeric and check, but this can create an exception if not properly coded.
Another workaround for this problem is by using the combination of LENGTH and TRANSLATE [...]
Validating string for numeric values in SQL Server
What to do when you have a large string and you have to validate whether this string only contain digits or not. A straight thing that comes in our mind is to use a looping technique to traverse all the characters in the string and validate each character to be a digit. But how could [...]
Find week start/end given week number
I have a table with some dated records. I wanted to do some weekly reports on this data. MySQL has a nifty function dubbed WEEK() which will return a week number. It allows you to break your data into week long intervals very easily. For example, the following query will tell me how many records [...]
MINUS query in MySQL
Mysql does not support MINUS operator which is not the end of the world. For most queries you can really care less if it’s implemented or not. My web app never needs to use it to function properly, which makes MySQL a perfect choice here. But, sometimes human beings are more demanding than simple php [...]
SQL Server permissions
Permissions on data are one of the most critical aspects of database administration. If you’re too strict as a database administrator then your users will not be able to do their jobs. If you’re not lenient, then data can be compromised or even leaked. It is a very fine balance to control. The ability to [...]
How To Calculate the Number of Week Days Between two Dates
If the start date and end date are both week days, then the total number of week days in between is simply:
(total difference in days) – (total difference in weeks) * 2
DateDiff(dd, @start, @end) – DateDiff(ww, @start, @end)*2
… since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., [...]
SQL Server Version
If you need to know the version you are currently running of SQL Server you can easily get it by running the following query.
SELECT ‘ SQL Server ‘ +
CONVERT ( varchar ( 100 ),SERVERPROPERTY( ‘ productversion ‘ )) + ‘ – ‘ +
CONVERT ( varchar ( 100 ),SERVERPROPERTY( ‘ productlevel ‘ )) + ‘ – [...]
Simple script to backup all SQL Server databases
Problem Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have [...]
Demand Paging (Custom Paging) in ASP.NET with SQL Server
Many a times we need to present bulk data to the user in our day to day application development. Loading all data at one shot is okay when we have few hundreads data but when it comes to thousands of thousands records, it hardly work. In scenario like this we need to [...]
Tracking object (Table, SP etc) changes in SQL Server 2005
DBA’s often need to track the changes being made to database objects such as tables, user defined functions and store procedures etc. Following article aims at providing the readers on how the changes being made to the database can be tracked with minimal effort. To get into details, i would first give preferrance to the [...]
Debugging Stored Procedures in SQL Serrver 2005
Pre-requisites
1. Find the .exe file under the directory, C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe or similar path where the SQL Server was installed. The file rdbgsetup.exe stands for ‘RemoteDeBuGsetup’. Look for the emphasis on letters. The filename reads rdbgsetup because, we are going to debug a stored procedures of a database available in some remote physical [...]
