sp_delete_jobschedule SQL Server

I had to create a deployment script today which included modifications to an existing job. Ideally the normal process requires taking backup of the existing object and then implementing the modified object.

Could not find a built in function to rename the job, so my approach was to delete schedule of the existing job and create a new job with a different name (appended v2 i.e. version 2) with the same schedule.

sp_delete_jobschedule 
    @job_name = N'my existing job name', 
    @name = N'existing schedule name'

sp_xml_preparedocument and sp_xml_removedocument in SQL Server

Got a chance of using the 2 built in functions below:

sp_xml_preparedocument

sp_xml_removedocument

Implementation: Input to a the procedure is a text datatype which contains XML with account_id's and we just needed the list of account_id's in our procedure.

CREATE PROCEDURE dbo.get_account_info
(account_list text)

AS

BEGIN


DECLARE @ATempTable TABLE    
 (  
  [id] varchar(30) NULL  
 )  


 DECLARE @doc_out int 


 EXEC sp_xml_preparedocument @doc_out OUTPUT, @account_list    
  
 INSERT INTO @ATempTable ([row_id])  
 SELECT [id] FROM OPENXML (@doc_out, '/root/account', 1) WITH (id varchar(30))  
  
 EXEC sp_xml_removedocument @doc_out

SELECT @doc_out

END

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....