SQL Server Management Console Express and Linked S

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Management Console Express and Linked S

Author

Topic

kitfox69
Starting Member

13 Posts

Posted - 2008-05-17 : 09:01:34

Ok so I managed finally to get an Excel Spreadsheet to connect as a linked server.

When I right click it , go to run scripts and click on create to i can see the tab of script below:

/****** Object: LinkedServer [EXCELLINK] Script Date: 05/17/2008 08:37:04 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'EXCELLINK', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'c:\PRICINGFINAL2.xls', @provstr=N'Excel 8.0'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCELLINK',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELLINK', @optname=N'use remote collation', @optvalue=N'true'

Now I am not sure exactly how to go about querying to pull the data into a table.

The table I want to set will have three columns:

CREATE TABLE new_price (
item_id CHAR(12),
item_lst_lnd_cost DECIMAL(9,2),
item_prc_2 DECIMAL(9,2))

the columns in the EXCELLINK linked server spreasdsheet are named the same as the columns above. Once the table is created how do I use the linked server to import the data?

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-17 : 10:41:36

You can also use export/import wizard for these.

check this out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources