Don't forget that now that it is in the tempdb table you can script out a field descriptor or a CREATE table statement. Use that for a permanent import table elsewhere.

USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Now use Query Analyzer to script out the table in tempdb. Here's a sample script.
--
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO

Posted in: EXCEL , SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Easy linked server setup for a one hop query.

PC (A) to SERVER (B) will work.
PC (A) client tools to SERVER (B) and four-part queries from SERVER (B) connection to SERVER (C) will not work without some kind of delegation.

See "How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers" and this "Allow a computer to be trusted for delegation for specific services"

-- Add a linked server
EXEC sp_addlinkedserver 
@server = N'SQLDEV',
@srvproduct  = N'SQL Server'
GO
-- And some options
exec sp_serveroption 
@server = N'SQLDEV', 
@optname = N'data access', 
@optvalue = N'true' 
exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'SQLDEV'
,@useself = 'TRUE'
--     ,@locallogin = 'sa'    -- this doesn't seem to be required.
GO
EXEC sp_helpserver 'SQLDEV'
EXEC sp_helplinkedsrvlogin 'SQLDEV'
GO
-- This query will work from one hop. e.g. If you are querying directly from one local server to another.
-- You can't do this by Query Analyser on PC A, logging into server B, and running the 4 part query agains
-- Server C.
select * from sqldev.master.dbo.sysservers
GO
-- Some options
-- exec sp_serveroption N'SQLDEV', N'query timeout', 0 
-- exec sp_serveroption N'SQLDEV', N'collation name', N'null' 
-- exec sp_serveroption N'SQLDEV', N'use remote collation', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc', N'true' 
-- exec sp_serveroption N'SQLDEV', N'rpc out', N'true' 
-- exec sp_serveroption N'SQLDEV', N'data access', N'true' 
-- exec sp_serveroption N'SQLDEV', N'collation compatible', N'true' 
-- exec sp_helplinkedsrvlogin N'SQLDEV'
-- exec sp_addlinkedserver N'SQLDEV' 
-- Clean up our test
--  Exec Sp_dropserver 'SQLDEV', droplogins

Posted in: SQL  Tags:

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us

Sometimes this ERROR is just file system permissions. It is definatelya generic error and not reporting what is actually going on.

Test by trying to write the the directory e.g. from SQL

If you get a security message, change the permissions of the directory e.g. Tests, above, to RW.

Posted in: DOS , SQL  Tags: sql, xp_cmdshell, bcp

Actions: E-mail | Permalink | Comments (0) | Comment RSS | E-mail | Kick it! | DZone it! | del.icio.us