Archive
SQL DBA – Collation Conflict in SQL Server
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Just few days back I came across this error when I tried to join two tables from 2 different databases. Didn’t get any clue for a few minutes so I googled up this error (Thanks google baba).
Collation is MS SQL Server is used for specifying the ordering of characters when you create or alter a table or create a domain. Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. SQL Server uses them implicitly in SQL statements. To use them explicitly you need to override the default collation or the collation you specified when creating or altering the table or creating the domain. Collation can be applied for char, varchar, text, nchar, nvarchar, and ntext data types.
For example:
SELECT T1.EmployeeName, T2.DeptName FROM ServerA.dbo.EmpTab T1 JOIN ServerB.dbo.DeptTab T2 ON T1.DeptCode = T2.DeptCode
There could be a possibility that both the servers use different Collations. If yes then you would get an error similar to then one I mentioned at the top of this topic. What you should do in this case?
1. You can alter the default Collation of either of the table columns/fields, but this could have a ripple effect for other tables currently in use with the altered table.
2. Use COLLATE DATABASE_DEFAULT keyword while matching the columns/fields, like:
SELECT T1.EmployeeName, T2.DeptName FROM ServerA.dbo.EmpTab T1 JOIN ServerB.dbo.DeptTab T2 ON T1.DeptCode COLLATE DATABASE_DEFAULT = T2.DeptCode COLLATE DATABASE_DEFAULT
More Info:
Collation Types: http://msdn.microsoft.com/en-us/library/ms144250%28SQL.90%29.aspx
Alter Table: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx
Alter Database: http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx
Other: http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Collations3.html
C++ Program to implement File Handling With Class Objects – Q32
Q32. Program to implement File Handling With Class Objects:
Define a class to represent a bank account. Include the following members:
i) Depositor Name
ii) Account Number
iii) Balance Amount
Member Function
i) To Assign Initial values(opening balance Rs. 1000 by default)
ii) To deposit an amount
iii) To withdraw an amount(if possible)
iv) To display the current balance
Write a file based program to store the records of at least ten accounts in “ACCOUNT_DETAIL” data file and perform the required manipulations- DEPOSIT, WITHDRAW & BAL_ENQUIRY using the file on a given account. The changes after each deposit and withdrawal should be updated in the given file.
… from College notes (BCA/MCA assignments):
#include <fstream.h> #include <stdlib.h> #include <iomanip.h> #include <conio.h> const int MAX = 100; enum bool{_false, _true}; class Cbank{ private: int accNo; char name[20]; float bal; public: Cbank() : accNo(100){} void getAcc(int ); bool accPresent(int ); void saveBal(int, float ); void dispAcc() const; void dispAll() const; }; void Cbank :: getAcc(int i){ accNo += i; cout<<"\n Enter Name: "; cin>>name; cout<<"\n Enter Amount for opening Acc.: "; cin>>bal; } bool Cbank :: accPresent(int _acnt){ if(accNo == _acnt) return _true; else return _false; } void Cbank :: saveBal(int type, float _amt){ if(type == 2){ // Deposit. bal += _amt; cout<<"\n Balance Updated."; } else if(type == 3){ // Withdrawl. if(bal >= _amt){ bal -= _amt; cout<<"\n Balance Updated."; } else cout<<"\n Not Enough Funds on Account, SORRY !!"; } } void Cbank :: dispAcc() const{ cout<<"\n Account Information:-"; cout<<"\n ~~~~~~~~~~~~~~~~~~~"; cout<<"\n Account No.: "<<accNo; cout<<"\n Holders Name: "<<name; cout<<"\n Balance: "<<bal; } void Cbank :: dispAll() const{ cout<<endl<<setw(8)<<accNo<<setw(20)<<name<<setw(20)<<bal; } void main(){ int ch, i, _tot = 0; int _acc; float _amt; char nx; bool found; Cbank *Obank = new(Cbank[MAX]); fstream fstr; fstr.open("Bank.txt", ios::in | ios::nocreate); if(fstr){ while(fstr){ fstr.read((char *) &Obank[_tot], sizeof(Obank[_tot])); _tot++; } _tot -= 2; } fstr.close(); // Data Retrieved form Disk. while(1){ clrscr(); cout<<"\n BANK AUTOMATION"; cout<<"\n ~~~~~~~~~~~~~~~"; cout<<"\n 1 -> Create Account."; cout<<"\n 2 -> Deposit Money."; cout<<"\n 3 -> Withdrawl Money."; cout<<"\n 4 -> Balance Inqury."; cout<<"\n 5 -> Display Accounts."; cout<<"\n 6 -> Exit."; cout<<"\n\n Enter your choice: "; cin>>ch; clrscr(); switch(ch){ case 1: // Create Acc. while(_tot < MAX){ cout<<"\n Account No.: "<<_tot+1; Obank[_tot].getAcc(_tot); cout<<"\n Want to add more (y/n): "; cin>>nx; _tot++; if(nx == 'n') break; } break; case 2: // Deposit Money. found = _false; cout<<"\n Enter Account No: "; cin>>_acc; for(i=0; i<=_tot; i++){ if(Obank[i].accPresent(_acc)){ found = _true; break; } } if(!found){ cout<<"\n Account Not Found."; break; } else{ cout<<"\n Enter Amount to Deposit: "; cin>>_amt; Obank[i].saveBal(ch, _amt); } break; case 3: // Withdrawl Money. found = _false; cout<<"\n Enter Account No: "; cin>>_acc; for(i=0; i<=_tot; i++){ if(Obank[i].accPresent(_acc)){ found = _true; break; } } if(!found){ cout<<"\n Account Not Found."; break; } else{ cout<<"\n Enter Amount to Withdrawl:"; cin>>_amt; Obank[i].saveBal(ch, _amt); } break; case 4: // Balance Inquiry. found = _false; cout<<"\n Enter Account No: "; cin>>_acc; for(i=0; i<=_tot; i++){ if(Obank[i].accPresent(_acc)){ found = _true; break; } } if(!found){ cout<<"\n Account Not Found."; break; } else{ Obank[i].dispAcc(); } break; case 5: // Display Acc. cout<<"\n Account Information:-"; cout<<"\n ~~~~~~~~~~~~~~~~~~~"; cout<<"\n ACC No."<<setw(20) <<"NAME"<<setw(20)<<"BALANCE"; cout<<"\n`````````````````````````````````` ``````````````````````````````````"; for(i=0; i<_tot; i++) Obank[i].dispAll(); break; case 6: // Exit; fstr.open("Bank.txt", ios::out); for(i=0; i<=_tot; i++) fstr.write((char *) &Obank[i], sizeof(Obank[i])); fstr.close(); // Data Saved to Disk. delete []Obank; exit(1); default: cout<<"\n Enter Appropriate Choice."; } // end of switch. getch(); } // end of while. } // end of main.
Output:
BANK AUTOMATION
1 -> Create Account.
2 -> Deposit Money.
3 -> Withdrawl Money.
4 -> Balance Inqury.
5 -> Display Accounts.
6 -> Exit.
Enter your choice:1
Account No.: 1
Enter Name: Manoj
Enter Amount for opening Acc.: 1000
Want to add more (y/n): y
Account No.: 2
Enter Name: Nitin
Enter Amount for opening Acc.: 2000
Want to add more (y/n): n
Account Information:-
ACC No. NAME BALANCE ``````````````````````````````````````````````````````````````````` 100 Manoj 1000 101 Nitin 2000