Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
manikandan
Starting Member
35 Posts |
 Posted - 2002-12-18 : 00:37:13
|
| Hi i've to extreact data from sql 2000 to my excel. These are the steps i followed.i created a odbc datasource for SQL Server. I selected SQL authentication.then i went to excel and clicked on data --> GetExternalData.i select the datasource which i have created. But my problem is that excel tries to use NT authentication to connect to SQL Server through the ODBC instead of SQL Authentication (which i've specified in the ODBC)....essentially it tries to override the preferences that are used in ODBC. If i use Test Database Connectivity in ODBC driver, it works well and it uses SQL authentication only.As excel is triying to use NT authentication, a lot of users are not able to access the sql server.CheersManikandan |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-18 : 07:06:07
|
| Have you tried DTS? Is this a one-time extraction or a repeatable process? How is the SQL data exposed (query? proc? view? table?)?Jay White{0} |
 |
|
|
sherrer
64 Posts |
Posted - 2002-12-18 : 11:01:30
|
| That doesn't make much since. If you have chosen SQL Server authentication in your ODBC connection then that is what Excel should use. I tested it using an ODBC connection with SQL Server Authentication, and it popped up a log in screen at that point asking for the password for the userid I had used. It also gave the choice to use a trusted connection (NT authentication). I am using Excel 8.0 and SQL Server 7.0.I think you need to answer Jay's questions, as there may be a much better way of doing what needs to be done... we need more information on what you are trying to accomplish. DTS is an extreamly useful tool to import and export data and can also be controled using the object library from other sources like VBA in Excel. |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2002-12-18 : 12:27:02
|
| I usually run SQL in Result in Grid view.And then copy and paste it into the Excel spredsheet. quick and easy way. |
|
|
|
|
|
|
extracting data from sqlserveer to excel