Excel Connecting to SQL Server Database - Need your Help

[Rajesh G]

I am trying to use OLEDB provider for SQL Server to connect, but its failing.
Is it possible to connect SQL Server like this..?

But when I tried with ODBC, its working.

Look into this code:
try

{

m_objExcel = new Excel.Application();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

// Create a QueryTable that starts at cell A1.

m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

m_objRange = m_objSheet.get_Range("A1", m_objOpt);

m_objQryTables = m_objSheet.QueryTables;

m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provider=SQLOLEDB;Data Source=xxxxxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees");

m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;

m_objQryTable.Refresh(false);

// Save the workbook and quit Excel.

m_objBook.SaveAs(m_strSampleFolder + "Book7.xls", m_objOpt, m_objOpt,

m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,

m_objOpt, m_objOpt, m_objOpt);

m_objBook.Close(false, m_objOpt, m_objOpt);

m_objExcel.Quit();

}

catch(Exception ee)

{

throw ee;

}


Any help greatly appreciated.

 

[Bob Phillips]

Rajesh,

Is this Net? If so I don't have that to test, but I know that SQL Server has an OLEDB data provider, so it does work. What error do you get?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


I am trying to use OLEDB provider for SQL Server to connect, but its failing.
Is it possible to connect SQL Server like this..?

But when I tried with ODBC, its working.

Look into this code:
try

{

m_objExcel = new Excel.Application();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

// Create a QueryTable that starts at cell A1.

m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

m_objRange = m_objSheet.get_Range("A1", m_objOpt);

m_objQryTables = m_objSheet.QueryTables;

m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provider=SQLOLEDB;Data Source=xxxxxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees");

m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;

m_objQryTable.Refresh(false);

// Save the workbook and quit Excel.

m_objBook.SaveAs(m_strSampleFolder + "Book7.xls", m_objOpt, m_objOpt,

m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,

m_objOpt, m_objOpt, m_objOpt);

m_objBook.Close(false, m_objOpt, m_objOpt);

m_objExcel.Quit();

}

catch(Exception ee)

{

throw ee;

}


Any help greatly appreciated.

 

[Rajesh G]

Bob

Its in .Net

All I am struggling with is this line

m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provider=SQLOLEDB;Data Source=xxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees")

Using QueryTable Object, I want to embed the DataSource and Query into the Excel Sheet

I would like to use OLEDB provider, that is the difference. With ODBC its working fine.

 

[Rajesh G]

I figured a way to connect using OLEDB

Exact syntax .
OLEDB;Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=sa;Data Source=xxx;Initial Catalog=xxx;