You can use

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])

and use dynamic sql to substitute the workbook name

but beware that when SQL reads Excel files is uses the first few...

\\AS-B-CLI-000\c:\temp\test2.csv is an invalid UNC name

you need to replace the c: with a share name

eg \\AS-B-CLI-000\mysharename\temp\test2.csv

but SQL will require the neccessary rights to the share

You could use c$, which...

...old Narcissus...

Less of the old

It'll be more like Sycophant

A clone of myself

We'd get on so well and get the work done in a 1/4 of the time

SELECT c.[Date], c.Unit, c.Status

FROM [Table] c

INNER JOIN (SELECT a.[Date],a.Unit

FROM [Table] a

INNER JOIN [Table] b

ON b.Unit = a.Unit

AND b.Status = 'F'

AND b.[Date] >= a.[Date]...

Is there any duplicate data, ie does your READER table have multiple entries for multiple door entry

Make sure there is nothing pending on the connection, ie open recordset or use a separate connection

SELECT d.personname, d.[datetime], d.sys_nr, d.door_nr, r.[Door Reader]

FROM [DATEBOOK] d

INNER JOIN [READERS] r

ON r.sys_nr = d.sys_nr

AND r.door_nr = d.door_nr

should give you

personname  datetime  sys_nr  door_nr  Door Reader

myself     ...

NULLIF(cell,'NULL')

Out of curiosity, what does the table's DDL look like and how do you wish to update the table, eg update query, EM

Use a view as Jeff decribes to strip out cr/lf and format data

use -c and -t parameters instead of format file

exec master..xp_cmdshell 'bcp "SELECT top 10000 * FROM myServer.DB.dbo.myView WHERE...

You could do it this way

WHERE @Check LIKE '''%' + CustomerID + '%'''

Either protect each divide using a CASE staement

or

see SET ARITHIGNORE in BOL (Books Online)

or

SELECT j.job_id

FROM [Jobs] j

INNER JOIN [Activities] a

  ON a.job_id = j.job_id

GROUP BY j.job_id

HAVING COUNT(*) = 1

AND SUM(activity_type_id % 2) = 1

p.s. providing activity_type_id...

Duplicate post, see

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302527

Duplicate post, see

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302527