Logic: The art of thinking and reasoning in strict accordance with the limitations and incapacities of the human misunderstanding. ~Ambrose Bierce

On a lighter note SQL

0
Wedding Queries........ ........ (SQL Style)
HUSBAND’S QUERY

CREATE PROCEDURE MyMarriage (
BrideGroom Male (25) ,
Bride Female(20) )
AS
BEGIN

SELECT Bride FROM  india_ Brides
WHERE FatherInLaw = 'Millionaire'
AND  Count(Car) > 20   AND  HouseStatus ='ThreeStoreyed'
AND BrideEduStatus IN (B.TECH ,BE ,Degree ,MCA ,MiBA) AND Having  Brothers= Null
AND Sisters =Null

SELECT Gold ,Cash,Car,BankBalance
FROM FatherInLaw
UPDATE MyBankAccout
SETMyBal = MyBal + FatherInLawBal

UPDATEMyLocker
SET MyLockerContents = MyLockerContents + FatherInLawGold

INSERT INTOMyCarShed VALUES('BMW')
END
GO

Then the wife writes the query below:

DROP HUSBAND;
Commit;

SQL Resultset in CSV format..

0
There might be a requirement very often to dump the query results straightaway into CSV file format or in other words into a .CSV file. Here is what we need to do to achieve the same.

1) In case we are using OSQL through JCL to call the stored proc, then we need to add a new parameter (-s) as "," or ";" to indicate the character separator. E.g. SET PARM5= -s %";"%
The other parameter (-o) needs to be modified to specify (.CSV) as output file format instead of (.TXT).

2) This is not all. The stored proc needs to be modified as well to accommodate the changes. Please refer to these different approaches taken.

SELECTED APPROACH:

a) SELECT 'col_1' + ',' + 'col_2' + ',' + ' '
    UNION ALL
    SELECT CONVERT (varchar, col_1) + ','
             + CONVERT (varchar, col_2) + ',' + ' '

ALTERNATIVE APPROACH:

a) SELECT 'col_1', ',' , 'col_2'
    UNION ALL
    SELECT CONVERT (varchar, col_1), ','
                , CONVERT (varchar, col_2).................cont.

b) SELECT  CONVERT( varchar,col_1) AS [col_1,]
                ,CONVERT( varchar,col_2) AS [col_2,]........cont.

NOTE:
Conversion to varchar is required to append the character separator.

ISSUES WITH ALTERNATIVE APPROACH:
1) Alignment problem with alphanumeric columns.
2) Space issues which could not be handled through generic sql trim functions.
3) Unoptimized file size.
4) Truncation of data in some cases if varchar size is not carefully specified.
5) Last column of unusual width.

UNSOLVED ISSUES:
1) The column header being present in the final CSV output file.
2) The date field losing its formatibility once converted to varchar and exported to excel.
Unresolved Issue # 1 can be resolved by the following approaches.

The SQL output contains 2 records, the Header name in the first line and the Line (-) in the second line. This can be removed by
1. We can create DTS package to export the data into Text file and call that DTS from the JCL script. This removes the header lines.
2. These lines can be skipped directly from the JCL script itself with the following code

We just need to provide the record length like [LRECL=357]

//SORTCPY EXEC PGM=SORT
//SORTIN DD DSN=&&UCMDFILE, DISP=OLD
//SORTOF01 DD DSN=&&UCMDSORT,
// DISP= (, PASS, DELETE),
// DCB= (RECFM=FB, LRECL=357),
// SPACE= (2048, (500,500), RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FILES=01, STARTREC=2




Orphaned Distributed Transaction SPID

0
Explanation:
This example is of an Orphaned Distributed Transaction SPID which specifically involves MSDTS, the Microsoft Distributed Transaction Coordinator. The is what happens when a transaction involves data which resides on more than one server (such as when a database record is replicated out to two or more servers where MSDTC needs to become involved) and the following happens:
a) A server drops off the network
b) There’s a power outage at the distributor server.


The Negative SPID (-2) in SQL Server
Phenomenon: May be you have been investigating a performace issue in SQL Server or exploring the error log file for sometime. But to add to your astonishment you notice that there is a negative SPID = -2 at the top of the chain and is the cause of all deadlocks.

What next?
Definitely you would like to wait (not sure how long?) or try to kill the process.

Here is what you have when you try any of them:
Wait: No use waiting as the process will not complete/refresh untill a server restart.
Kill: Yes. Ofcourse you can try to kill the process by executing KILL -2. Here is your prize:
Msg 6101, Level 16, State 1, Line 1Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

Anything else in Pandora's box?
No...?

Solution:
Try this.
SELECT req_transactionUOWFROM master..syslockinfo
WHERE req_spid = -2

This will return a 32 digit UOW number which looks like a GUID. Something like ‘BCD12078-0199-1212-B810-AB46A24F2498’
Run the KILL command with the above UOW.
KILL ‘BCD12078-0199-1212-B810-AB46A24F2498’
Now run sp_who2/sp_who. To your surprise its SPID -2 is GONE.

 

Andar Bahar...

0
Andar...
 Bahar...


Letter To Dean (Funny)

0

Forget...

0

Know Your Customer

0
A disappointed salesman of Coca Cola returns from his Middle East assignment. A friend asked, "Why weren't you successful with the Arabs?"

The salesman explained, "When I got posted in the Middle East, I was very confident that I would make a good sales pitch as Cola is virtually unknown there. But, I had a problem I didn't know to speak Arabic. So, I planned to convey the message through three posters...



First poster - A man lying in the hot desert sand...totally exhausted and fainting….

Second poster - man is drinking our Cola.

Third poster- Our man is now totally refreshed.

Then these posters were pasted all over the place "That should have worked," said the friend.
The salesman replied "I didn't realize that Arabs read from right to left"