Hi,

I recently upgraded my dev machine to SQL 2005. It's the only machine on
our network that's running SQL 2005; everything else is on SQL 2000. It's
been a bit of a struggle getting used to the new management console, but
mostly liveable. However, now I'm getting totally frustrated by something
that seems like it should be incredibly simple.

I need to import a spreadsheet into a database that resides on one of our
SQL 2000 servers. I've done this a million times in SQL2K - go to the table
list for that database, right-mouse click on the table list, select Import
Data and go from there. Maybe I was just lazy, because I've never really
bothered learning the bcp syntax - it hasn't been a big deal. So now, of
course, I try to do the same thing and that option isn't there. And the
more I dig, the more it starts to look as if I HAVE to use bcp, or, God help
me, SSIS inside a Visual Studio project, which looks like shooting a mouse
with an elephant gun.

So, is there an easy way to import a spreadsheet to a SQL2K (or SQL 2005)
database? I have tried bcp (have to have an existing table created that you
import into; not difficult, but not as simple as before), OPENDATASOURCE
(tells me I have an invalid table name), and looked at SSIS. FYI: These
Aren't Easy. And, in case I'm stuck w/ the OPENDATASOURCE method, here's
the code I was using:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\WebsiteApproval.xls";
User ID=Admin;Password=;Extended properties=Excel
8.0')...WebsiteApproval

It seems to open the spreadsheet correctly, but it doesn't recognize the
TableName (the last item). The only sheet is named WebsiteApproval, and
there are no named ranges.

Please, could somebody give me some good news!

Thanks in advance, and I'm sorry for venting,

Jim

In SSMS, right-click the DB, Tasks, Import Data...
This will invoke the wizard, which builds a SSIS package for you in much the
same way that the wizard in SQL 2000 built a DTS package.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Jim" <floopdog (AT) bodiddy (DOT) com> wrote

That certainly fits the criteria of "easy". Thank you very much.

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

You can also call the import/export wizard from SQL management studio by
right-clicking the database...tasks....import/export. You can execute
immediately or save the package (and edit with VS2005 aka elephant gun)

JL

"Jim" <floopdog (AT) bodiddy (DOT) com> wrote