how do I import data in excel form SQL server?

[Mitch]

Hello All,

I've been trying to import data into excel from our SQL server. I have
the sql script yielding the results. The real problem is when I use the data
connectivity wizard, it only allows you to select one table from the database
in your SQL server. However, my script involves two tables with a join. Any
ideas would be helpful

Thanks,

Mitch

 

[Duke Carey]

Asuming you have rights on the Server, convert your 'script' (by which you
probably mean query?) into a view. If you don't have adequate rights, then
use MS Query to connect to SQL Server and use your query text there.

 

[Mitch]

Thanks Duke. Im really new to SQL. I have here a Server Management studio
and an SQL query analyzer. Which one should I use. Both could give me
results to grid. How do I change it into view? After this, what do I do to
connect the results to Excel(import)?

 

[Duke Carey]

Well, you're asking a lot.

You can use SQL Management Studio to convert your query into a view by using

Create View [viewname] as
and then paste in your query

then you execute that query and, if all goes well, you'll have a view. A
view is nothing more than a virtual table.

Alternatively, use your query in Excel. What version of Excel are you
using? In 2007, on the data tab, under From Other Sources you can find MS
Query. In 2003 it's under the Data menu somewhere. Use that to connect to
SQL and follow the prompts. Select one of your tables to get past the early
steps. Toward the end there is an option to edit the thing in MS Query. When
you get there, click on the SQL button and delete what is there, replacing it
with your query. Follow the steps to run the query and return the data to
your worksheet

 

[Duke Carey]

Here's a link, too, that might help. Go all the way to the bottom of the
page to read about MS Query

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

 

[Mitch]

Got an error message that read " changed database context to [table name
inside the data base]. What does this mean?

Well, you're asking a lot.

You can use SQL Management Studio to convert your query into a view by using

Create View [viewname] as
and then paste in your query

then you execute that query and, if all goes well, you'll have a view. A
view is nothing more than a virtual table.

Alternatively, use your query in Excel. What version of Excel are you
using? In 2007, on the data tab, under From Other Sources you can find MS
Query. In 2003 it's under the Data menu somewhere. Use that to connect to
SQL and follow the prompts. Select one of your tables to get past the early
steps. Toward the end there is an option to edit the thing in MS Query. When
you get there, click on the SQL button and delete what is there, replacing it
with your query. Follow the steps to run the query and return the data to
your worksheet

Thanks Duke. Im really new to SQL. I have here a Server Management studio
and an SQL query analyzer. Which one should I use. Both could give me
results to grid. How do I change it into view? After this, what do I do to
connect the results to Excel(import)?

 

[Mitch]

Duke,

am using Excel 2003. In the edit query window, do I erase everything? the
connection text etc, select SQL as command type and then paste my
script/query on teh bottom dialog box? I get an error like the context was
changed, or it indicates that the table I selected from the database is
invalid and such.

Well, you're asking a lot.

You can use SQL Management Studio to convert your query into a view by using

Create View [viewname] as
and then paste in your query

then you execute that query and, if all goes well, you'll have a view. A
view is nothing more than a virtual table.

Alternatively, use your query in Excel. What version of Excel are you
using? In 2007, on the data tab, under From Other Sources you can find MS
Query. In 2003 it's under the Data menu somewhere. Use that to connect to
SQL and follow the prompts. Select one of your tables to get past the early
steps. Toward the end there is an option to edit the thing in MS Query. When
you get there, click on the SQL button and delete what is there, replacing it
with your query. Follow the steps to run the query and return the data to
your worksheet

Thanks Duke. Im really new to SQL. I have here a Server Management studio
and an SQL query analyzer. Which one should I use. Both could give me
results to grid. How do I change it into view? After this, what do I do to
connect the results to Excel(import)?

 

[Duke Carey]

Use the link to Nick Hodge's site. Nearly at the bottom of that page he
shows you how to get t0 MS Query. You don't change any of the connection
info or any of the preliminary screens.

When you get to the screen that allows you to Edit in MS Query, be sure to
choose that option and click Finish. Now you'll be in the actual query
designer and that is where you click on the SQL button on the toolbar. In
the window that pops up you replace all THAT TEXT with your query text from
SQL Server Mgt Studio. Then follow the rest of Nick's instructions

Duke,

am using Excel 2003. In the edit query window, do I erase everything? the
connection text etc, select SQL as command type and then paste my
script/query on teh bottom dialog box? I get an error like the context was
changed, or it indicates that the table I selected from the database is
invalid and such.

Well, you're asking a lot.

You can use SQL Management Studio to convert your query into a view by using

Create View [viewname] as
and then paste in your query

then you execute that query and, if all goes well, you'll have a view. A
view is nothing more than a virtual table.

Alternatively, use your query in Excel. What version of Excel are you
using? In 2007, on the data tab, under From Other Sources you can find MS
Query. In 2003 it's under the Data menu somewhere. Use that to connect to
SQL and follow the prompts. Select one of your tables to get past the early
steps. Toward the end there is an option to edit the thing in MS Query. When
you get there, click on the SQL button and delete what is there, replacing it
with your query. Follow the steps to run the query and return the data to
your worksheet

Thanks Duke. Im really new to SQL. I have here a Server Management studio
and an SQL query analyzer. Which one should I use. Both could give me
results to grid. How do I change it into view? After this, what do I do to
connect the results to Excel(import)?

:

Asuming you have rights on the Server, convert your 'script' (by which you
probably mean query?) into a view. If you don't have adequate rights, then
use MS Query to connect to SQL Server and use your query text there.

:

Hello All,

I've been trying to import data into excel from our SQL server. I have
the sql script yielding the results. The real problem is when I use the data
connectivity wizard, it only allows you to select one table from the database
in your SQL server. However, my script involves two tables with a join. Any
ideas would be helpful

Thanks,

Mitch