Skip to main content

Posts

Showing posts from 2010

EXTRACT DATA FROM XLS AND XLSX FILE FORMAT

Dear All,
Todays post is about how to load data from excel sheet. This post is emphasized on following file formats:
1) xls (Excel Sheet 1997-2003) 2) xlsx (Excel Sheet 2007)
Both sheets require connection string to extract data from sheet.
Connection String for xls:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"
Connection String for xlsx:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"
Below is a small program for your understanding. The program is in ASP.NET.
protectedvoid Page_Load(object sender, EventArgs e) { string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString; // Create the connection object OleDbConnection oledbConn = newOleDbConnection(connString); // Open connection oledbConn.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = newOleDbCommand("SELECT * FROM [Sheet1$]", oledbConn); …

The credentials you provided for the SQL server Agent service are invalid. To continue, provide valid account and password for the SQL server Agent Se

The credentials you provided for the SQL server Agent service are invalid. To continue, provide valid account and password for the SQL server Agent Service.
Today, I have decided to install SQL SERVER 2008 on my PC. All was going fine until I came to server configuration page/tab. I was halted for half an hour to insert correct credential to move to the next page. If you are also getting the same trouble then just enter following data in Account name and password:
Account Name : "NT AUTHORITY\NETWORK SERVICE " Password : Blank
I hope this will resolve your issue.

FORMATTING NUMBERS IN SQL

To format numbers in sql queries in SQL SERVER 2005, use the following command
Select Convert(nvarchar,cast(123123323 as money),1)
This will convert the following number 123123323 to 123,123,323.00