Archive for the ‘Databases’

  • 0
    Percentage of Work completed – SQL Server
    Today, I was taking a manual backup of a database through a scheduled Job. Normally this backup job takes about 1 hour to complete, but today it was still executing for almost 3 hours. I started to look up Activity monitor, dbcc commands for any lock against the database, but of no avail. After searching on internet i came across a DMV(Dynamic Management View) provided...
    by Nauman at August 24th, 2010 at 09:08 am
  • 0
    Get indexes on all tables – SQL Server
    An important ingredient in database designing is creating, maintaining and rebuilding indexes after some specified intervals. One main  aspect of database tuning is to apply indexes. Index must exist in databases otherwise their would be a lot of performance issues. While searching across internet, i came across a script that will list all the tables in my database and...
    by Nauman at August 24th, 2010 at 09:08 am
  • 0
    Migrate from MySQL to SQL Server
    Migrate from MySQL to SQL Server with ease. Microsoft has launched a small utility to migrate a database from MySQL to SQL Server. Name of the utility is SQL Server Migration Assistant for MySQL. More details can be found here
    by Nauman at August 23rd, 2010 at 08:08 am
  • 0
    Remove a Stored Procedure in Production Environment
    How to remove a Stored Procedure in SQL Server production environment? How can we be so sure that is not being used any more? You need to know every single path from where this procedure is initiating before you can actually remove it from production. Following are some ways to determine the usage of a procedure in SQL Server. Set up a trace and filter through SQL Server...
    by Nauman at August 23rd, 2010 at 08:08 am
  • 0
    Retrieve permissions on SQL Server database
      SQL Server permissions can be granted to on an object level (schema, database, database objects). In newer versions of SQL Server permissions can be given to user at a more granular level. To retrieve all the permissions granted to a specific user SQL server has a function named: fn_my_permission.  This function is only accessible to system administrator. e.g following...
    by Nauman at August 22nd, 2010 at 10:08 am
  • 0
    How to Improve I/O performance for SQL Server
      If underline I/O architecture system is working properly then every time SQL Server do some transaction (Read/Write) it can do it without waiting. But if load on the system is up, then SQL transaction have to wait for their turns for processing. This can significantly reduce SQL Server performance. There is a much greater issue when you are not in charge of the underlying...
    by Nauman at August 22nd, 2010 at 06:08 am
  • 0
    Import CSV file in SQL Server
    Importing a CSV(Comma Separated File) in SQL server is a very common task, and most of us use the Import /Export feature of SQL server to do this. Import/Export feature has SSIS (SQL Server Integration Services) as it backbone and works great with most of the Databases, file types etc. But today we are going to import a CSV file in SQL server through simple SQL queries....
    by Nauman at May 27th, 2010 at 10:05 pm
  • 0
    0x8002801D 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 msxml dll files and now...
    by Nauman at February 14th, 2010 at 09:02 am
  • 1
    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’), (‘Computer’), (‘Printer’), (‘Printer’), (‘Printer’), (‘Scanner’), (‘Scanner’), (‘Scanner’), (‘Scanner’), (‘Camera’), (‘Flash...
    by Nauman at February 14th, 2010 at 09:02 am
  • 0
    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 || ' HOURS...
    by Nauman at November 18th, 2009 at 09:11 am
  • 0
    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...
    by Nauman at November 5th, 2009 at 06:11 am
  • 0
    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 stored procedure and then through parsing execute insertion statements. generate...
    by Nauman at November 5th, 2009 at 06:11 am
  • 1
    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 work this out. There are many custom tools available...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 begin execute immediate...
    by Nauman at November 5th, 2009 at 12:11 am
  • 1
    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 oracle_username,object_id,session_id from v$locked_object; ORACLE_USERNAME...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 a custom function: CREATE [OR REPLACE] FUNCTION function_name [...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 function: LENGTH(TRANSLATE(...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 you implement this in SQL Server? There is an ISNUMERIC function that checks whether a...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    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 came in each week: SELECT COUNT(*), WEEK(mydate) FROM mytable GROUP BY WEEK(mydate); The...
    by Nauman at November 3rd, 2009 at 07:11 am
  • 0
    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 applications. Especially if you want to extract some non-obvious...
    by Nauman at November 3rd, 2009 at 07:11 am
Page 1 of 212