Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

EXCEL SQL Server Connection Question

Posted on 2015-02-10
20
Medium Priority
?
88 Views
Last Modified: 2016-02-11
Hi,

I want to start with a blank Excel. Then I want to connect to a SQL Server such that the connection property
will look at this (see attached image file). How to do that? How to get the settings as shown in the image file?

Thanks.

JenniferSQL-Conn.png
0
Comment
Question by:jfz2004
20 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40601730
Check out this EE article and scroll down about 40% of the way to view instructions with screen shots.
0
 

Author Comment

by:jfz2004
ID: 40603277
Thank you so much for the article. I learned somthign from it and tested it on a blank Excel.
But what I wanted to achieve are:
In the connection properties dialog box,
1. there is nothing in the "connection file";
2. the Proder = SQL Server, not SQLOLEDB.

How to get these two things? The options in Excel's "From Other Sources" confuses me greatly.

Jennifer
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40605378
If you provide a connection string then you won't need to have a connection file. Connection file should be a .UDL file, I guess.
The provider is what you have installed for SQL Server connection. What do you have? MSSQL Native Client (SNAC)? .NET (ADO)? ODBC`? OLEDB?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jfz2004
ID: 40607770
Thanks. I have SQL Server and Excel.
0
 

Author Comment

by:jfz2004
ID: 40607773
Excel but no VBA code.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40607782
I mean drivers not applications. Which drivers do you have installed?
0
 

Author Comment

by:jfz2004
ID: 40607785
Drivers? How to check what drivers installed? I am new to this.
Thanks a lot.
Jennifer
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40607798
If you check in you connection string the first word is DRIVER and you have SQL Server written on it. You can verify the installed drivers in Windows menu Start / Control Panel / Administrative Tools / Data Sources (ODBC) / Drivers tab.
You should see I list now. What you have listed for SQL Server?
0
 

Author Comment

by:jfz2004
ID: 40607858
In the "Connection Properties" dialogue box, there is a Connection string box. In it,
I have "Provider=SQLOLEDB.1;;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=..."

Is my driver SQLOLEDB, then?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40607867
Yes, in that case you are using SQLOLEDB.
0
 

Author Comment

by:jfz2004
ID: 40607884
And on the "Data" ribbon, there are 5 choices on the list of "From Other Sources". I don't know which one I should choose:
1. From SQL Server
2. ...
3. ...
4. From Data Connection Wizard
5. From Microsoft Query

Thanks for any help.
0
 

Author Comment

by:jfz2004
ID: 40607888
My query will pull data from a large table in a SQL Server DB and there is a where clause as "systemdate = ?" and the "?" refers to a cell. I don't know which of the above I should use. Thanks.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40607904
What are your knowledge in SQL Server area?
And also do you know well the table that you want to connect to?
0
 

Author Comment

by:jfz2004
ID: 40607916
I have a little bit of SQL Server knowledge, I know select and where clause.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40608018
If you know T-SQL then it's good. Chose the first option (1. From SQL Server).
The '?' means that the user will be asked to provide a value. It this case a date to be used in the WHERE clause.
0
 

Author Comment

by:jfz2004
ID: 40608215
I just tried your suggestion. Thanks.

But it asks for a connection fil and I don't want to use connection file.
I jsut want to use a Sql Query freely style.
0
 

Author Comment

by:jfz2004
ID: 40608277
I have been reading and seearching on the internet for a few days and seems like there is no sample for this kind of operation. I know this can be done, but non of the tutorials teaches it.
0
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40609133
From your previous question, please see the screenshots I posted

Now that you have your  DSN, the steps that you said you followed in this post are correct:
1. In a blank Excel, I click "From Other Sources";
2. Then choose "From Microsoft Query"
3. In "Choose Data Source" box, I choose the right database;
4. Then choose the table and columns via MS Query.
5. And it worked. I see the EDIT button shown.

When you first create a query it may show that it is using a connection file, but as soon as you edit the properties of the query and OK it, you'll get a prompt to say that it will no longer match the connection file (as shown below).
Excel-Connection-ODC-to-ConnectionStringIf you then go into the properties of the query again, you'll see that it now shows 'Database query' with your connection string.
If you then edit the command text to include a WHERE clause
e.g. SELECT * from myTable where SystemDate=?
and OK the Connection Properties dialog again, it will prompt you for the parameters, including the options I showed the screenshots I referred to above (getting value from a cell and updating automatically when cell value changes).

To be clear, you can get to the connection manager looking the way you want it to be at least two routes:
1. Other Sources/Microsoft Query
2. Other Sources/From Data Connection Wizard/ODBC DSN
0
 

Author Comment

by:jfz2004
ID: 40614071
Thank you so much. I think this clears it. You rock!

Jennifer
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question