Dynamic Sort By in SQL Server

Sort by column is sent as an input to the stored procedure and the sort by field can be different during different web service calls.

Sorting based on different fields at runtime does not need you to generate SQL statements with sort field appended and then execute them dynamically. It can be done as follows:

use mydb
go


set ansi_nulls on
go
set quoted_identifier off
go


create procedure dbo.get_account_info
(@sort_by_column varchar(255))
as
begin

set nocount on

select account_id, name_id, address_id, start_date, end_date, first_name, middle_name, last_name
from dbo.accounts
where status = 'A'

order by
(case @sort_by_column
when 'last_name' then m.last_name
when 'first_name' then first_name
when 'account_id' then account_id
when 'end_date' then end_date
when 'start_date' then start_date
else account_id
end)

end

Hope this helps.

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.