Directories in Oracle

To create a directory:
CREATE OR REPLACE DIRECTORY EXT_TABS AS 'C:\ext_tab_files';

Using REPLACE when creating objects is not a good idea as it can wipe out existing objects, so be careful.

To list all directories in the database:
SELECT * FROM all_directories;
OR
SELECT * FROM dba_directories;

Directory privileges can be viewed using the one of the queries below:
SELECT * FROM all_tab_privs WHERE table_name = 'EXT_TABS';

One thing to remember is directories are listed as tables, i was assuming they would be a object of their own.

This blog is for my reference, if you plan to use any information in here do research before blindly implementing it.

DISTINCT over Linked Server

Recently faced an issue where the destination table in a DB2 database had unique index and out insert from SQL Server using a linked server was failing. 

Even after using DISTINCT we received the same error:

OLE DB provider "MSDASQL" for linked server "SBLLS" returned message "[IBM][CLI Driver][DB2/XXXXX] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "46" constrains table "MYDB.S_DESTINATION_UPDATE" from having duplicate values for the index key.  SQLSTATE=23505

Then realized that DISTINCT clause hass no effect when inserting data over a linked server. Immediately we switched our approach to insert DISTINCT data into a temporary table and then execute the insert into DB2 from SQL Server.

Learned something new....

Multiple Identity columns in one table error

Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.

As the error message above suggests, only one identity column per table. Incase you need multiple sequences in the table then create table with one identity column, insert data and then update the remaining fields (that need to be a sequence) with the identity column values.
Google saved me...

Quick Tip #3

Always create a out file using the -o switch in you BCP command to a local drive where SQL Server instance is running. That way you can guarantee the log file getting created without any issues.

Example: You are trying to BCP out a file on a shared network drive and also writing the log file to the same location or a different shared network location where all logs for you applications exists. In case of network failure, directory write permission changes, unexpected outage the data file wont be created and even the log file. To create the log file it needs to be created on a local drive so all errors can be captured and required teams can be alerted of the failure.

BCP Out Error

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

The service account did not have permissions to write to the shared network directory. Also learned the UNC (Universal Naming Convention) is enough for SQL Server to identify the location to write the file to.

If you map the drive in Windows there is a chance that you still wont be able to write to the desired location. It needs to be mapped at SQL Server level and not Windows.

One more way to confirm your BCP Out job/script is working is it should run from command line if you have access to write to the shared network directory.

Google is my Friend....

Quick Tip #2

Important cursor system tables: master..syscursortables and master..syscursors

Can be used to check any open cursors in the database and other cursor related information.

Runtime Error: Arithmetic overflow error converting expression to data type int.

Actually the script i was testing did not have any computation nor any calculations (is there any difference?)

It was a simple cursor retriving data from temp table and updating a physical table in the database. I had declared variable to hold value from the cursor and were defined with INT datatype.

The cursor data was definitely not longer then 20 numeric characters, but i was still getting this error. Since i was in a hurry to complete the task at hand, i changed the data type to varchar(20) and proceeded.

Uncommitted transactions causing timeout error

The timeout period elapsed prior to completion of the operation or server is not responding

@@TRANCOUNT helped identify uncommitted transactions and the issue was resolved.

WHILE @@TRANCOUNT > 0
BEGIN
  COMMIT;
END;

Return code warning and truncation error in SQL Server

Msg 8152, Level 16, State 14, Procedure my_procedure, Line 56
String or binary data would be truncated.
The statement has been terminated.
The 'my_procedure' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Trying to figure out the reason for this error, i am able to modify the data and it procedure executes successfully. The issue is the column the procedure is updating is of length 30 characters and the data being updated is 17 characters (including a white space 'errorhere string'). As soon as i remove the white space and the 'string' it works like a charm.

Tried removing the white space but did not help either, still get the same error.

To resolve the NULL value being returned, just added return (0) and it was gone. I was under the impression that by default if no return is specified a 0 (numeral) is returned by the procedure.

Google is my friend....

Quick Tip #1

Converting a INT to VARCHAR trims all leading zeros

SELECT CONVERT(VARCHAR, 00012345)

Generating view definition in SQL Server

Make sure you have the privileges/access to the these commands.

1. sp_helptext [my_view_name]

2. Right click on the view in object explorer and generate a script

3. Run the query on information_schema.views table to see view definition

Hope this helps, i am also learning and Google is my friend :)

SYSOBJECTS.TYPE Distinct Values

use my_db
go

select object_id, type from susobjects;
go


Most Frequently Used      
P stands for Stored procedure
U stands for User table
F stands for Foreign Key constraint
PK stands for Primary Key constraint
V stands for View

Less Frequestly Used
C stands for Check constraint
UQ stands for Unique constraint
RF stands for Replication Filter stored procedure
TR stands for Trigger                       

Never Used
X stands for Extended stored procedure
S stands for System table
L stands for Log
D stands for Default constraint

(NOLOCK) vs. SET TRANSACTION ISOLATION LEVEL

As the title says, is there any difference between these 2 when it comes to procedures and/or SQL statements?

From what i have read and experienced, at a procedural level both of these are the same. But i would always prefer NOLOCK hint as i have gotten used to it so much that i always think something is not right with the code.

In SQL statements it has to do with caching (no personal experience here), read it from an online forum. Its better to use NOLOCK when running SQL statements.

Error when using a temp table and join with a physical table

Well i have never seen this exception before,  a good day to learn something new:



select * from dbo.[a]

create table tempdb.#b (temp_number varchar(11))

insert into tempdb.#b (temp_number)
select dnb_number from openquery ([XYZ], 'select dnb_duns_number from dnb_store_data')

create nonclustered index temp_number_idx on tempdb.[#b] ([temp_number])

select * from tempdb.[#b] a (nolock)
where not exists (
select [matched number] from dbo.[a] b (nolock)
where a.[temp_number] = b.[matched number])


(381618 row(s) affected)
Exception has been thrown by the target of an invocation.

(1 row(s) affected)

On searching found out it might be connection or network issue. Is that linked server [XYZ] causing this? Not sure, i get it when i try to execute the last select statement that uses NOT EXISTS

Error occurred during the pre-login handshake

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Update: Not getting this error anymore, might be a temporary glitch on the server or the network side.

Cannot insert the value NULL into column

In one of my projects i have been working on is in its final development phases and QA deployment has begun.

One of the tables with column name last_changed_by gets automatically populated with the session user name on insert or update. The column default value is set to: create default set_to_userid as suser_name()

This works as expected in DEV environment (Windows Server 2008 and SQL Server 2008 R2) and the field is populated with the user performing the DML (insert or update).

In QA (Windows Server 2000 and SQL Server 2000) does not work on the same table and returns error below when Oracle Fusion tries DML:

‘Cannot insert the value NULL into column 'last_changed_by', table 'mysecurity.dbo.security_app_users'

QA environment access is very strict that dev team cannot run any kind of test cases, so helpless we decided to pass suser_sname ()

Its still unclear whats causing the default value not to kick in on DML statement execution. Any input is appreciated....

MDM PDH Trillium and Purisma

My new assignment includes working on MDM (Master Data Management), PDH (Account Matching), Trillium (Address Standardization Tool) and Purisma Data Hub (PDH Development).

This is going to be a great learning experience. KT sessions have already started and my first task will adding new fields to PDH which interacts with SAP and Siebel for account management.

Account Matching: Match new and existing accounts based on default and user defined criteria and gives matching percentage to the user to select the appropriate account.

Will post some new topics soon....

Number of occurrences of space in a given string

I had used regular expression for counting number of occurrences of a character or string in SQL Oracle Database, but didnt know how to derive the same in SQL Server. 

Google search gave me the answer i was looking for:

declare @string varchar(50)
select @string = 'number of occurrences of space in a given string'
SELECT LEN(@string) - LEN(REPLACE(@string, ' ', '')) AS occurrence_count

Not sure what copyright policy the website has from where i got this piece of code, so not going to get into that....

Hope this helps.

SQL Server TEMPDB FULL

Got a new assignment to research why TEMPDB of a SQL Server 2008 64-bit is termination active user and application sessions.

DBA resource suspects a 120 table join is causing the TEMPDB to run out of disk space causing sessions  terminating unexpectedly.

Logged in the database for the first time, reviewed the procedure and tables and views involved. About 10 tables with significantly varying record counts which are joined by PK and other columns. Also the procedure runs in parallel (24 CPU's on the host machine).

Need to figure out the 120 table join, how why and where....

This is going to be a good learning opportunity.

Hopefully we will be able to resolve this TEMPDB issue and some good experience.

Update: Still no update, the users did not want the job running anymore so had to divert my attention towards other tasks.

One thing i suspect is there are 24 CPU's in that server and the job joins 2 tables, 2 views (3 tables in each view AFAIK). Can this be the reason for all those threads running in parallel? MAXDOP is not specified so may be?

SQL SERVER Object Property

SQL SERVER Object Property

Object properties that i have used frequently. Most of the times i have used object properties is to check whether the object exists or not. For example when deploying an existing procedure to production, the IsProcedure property is handy to check if the procedure exists in production or not. IsScalarFunction can be used to check existence of a function.

If it exists rename it with appropriate date time stamp (never use time delimiter i.e. 12:35:58 in an object name as it can cause problems if you are planning to use SQL Server Replication in future as these object names cannot be handled by the replication agent) and then you can successfully create a fresh copy of the modified procedure and you will have a backup copy as well.

Similarly try using all object properties in your spare time and think of ways  they can be used to make your life at work simple, you only need to be aware of what object properties exist and not remember the syntax (at least i don't) 

OBJECTPROPERTY ( id , property )

IsProcedure
IsScalarFunction
CnstIsClustKey
CnstIsColumn
CnstIsNonclustKey
ExecIsTriggerNotForRepl
IsCheckCnst
IsConstraint
IsForeignKey
IsIndexed
IsPrimaryKey
IsTable

GOOGLE: "sql server msdn objectproperty"

Update: For checking existance of non unique and non clustered indexes i did not find any ObjectProperty. 

USE [myDB]
GO


IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = 'idx_account_id' AND id = OBJECT_ID ('myDB.dbo.account'))
BEGIN
  CREATE NONCLUSTERED INDEX [ idx_account_id ] ON [dbo].[account] 
  (
  [UCN] ASC
  ) ON [PRIMARY]
END

The 2 ObjectPorperties below will not work for non unique and non clustered indexes.

Property name	Object type	Description and values returned
CnstIsClustKey	Constraint	PRIMARY KEY constraint with a clustered index. 1 = True 0 = False

CnstIsNonclustKey

Constraint

PRIMARY KEY or UNIQUE constraint with a nonclustered index. 1 = True 0 = False

Using APPLY

I had never heard or used this clause previously and just came across an example at work of how APPLY can be used to retrieve data from a join.

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. 

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. 

OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Often these operators can be used for retrieving SKU pricing, product SKU's, item lookups, etc. For example (got this as close as i can get without copy pasting from the actual script):

SELECT A.PRODUCT_SKU, A.PRODUCT_SKU_DESCRIPTION, B.SKU_LEVEL
FROM DBO.PRODUCT_SKUS A (NOLOCK)
INNER JOIN DBO.SKU_SALES B (NOLOCK)
ON A.PRODUCT_SKU = B.SKU
AND A.STATUS = 'A'
AND A.DISTI_CHANNEL = '007'
AND B.STATUS = 'A'
CROSS APPLY
(
SELECT TOP 1 FROM DBO.SKU_PRICE_ATTRIBUTE_LOOKUP C (NOLOCK)
INNER JOIN DBO.SKU_PRICE_12 D (NOLOCK)
ON C.SKU = D.SKU_ID
AND C.STATUS = 'A'
AND D.STATUS = 'A'
AND D.LAST_UPD_ROW_ID = '3RD-Q-12'
)

I used the same text as in the help section of MSDN, but this is something i would like to remember to use in future and need to spread the word about.

Using OPENROWSET

Using OPENROWSET

Easiet way to load data in a table in SQL Server for the purposes of data analysis, data load or anything you want to.

I know people will argue about using BCP, which is also a very good option. Using BCP you have good control over things like collation when importing data from different character sets or different international data sources and don't have to stick with Kanji.


INSERT dbo.xls_to_table
    (
    [ID],
    [COLUMN_1],
    [COLUMN_2],
    [COLUMN_3],
    [COLUMN_4]
    )
SELECT *
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR = YES; IMEX = 1; DATABASE = C:\temp.xls '
SELECT ' + CONVERT(nvarchar(255), @ID) + ',
    [COLUMN_1],
    [COLUMN_2],
    [COLUMN_3],
    [COLUMN_4]
FROM [xsl_sheetname$] )