March 7, 2008 at 11:50 am
Here is my statement and the results:
UPDATE [MYWORKBOOK]...[Cvt$]
SET [EID] = 37068788333
WHERE [SSN] = 014540999
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYWORKBOOK" returned message "Syntax error in UPDATE statement.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "UPDATE Cvt$ set `EID` = (3.706878833300000e+010) WHERE `SSN`=(1.454099900000000e+007)" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYWORKBOOK".
(I made a couple of edits to the above for privacy purposes, but no substantial changes.)
I can select the fields from the linked server (an Excel workbook, with a tab named "Cvt" with no problem, but when I try to make a change it fails.
A long search on Google found several warnings about using reserved words as column names, and being sure the workbook is not already open, but nothing that seems to fit here.
I expect I am doing something dumb -- can any of you smart folks see what it is?
(As always, thanks very much for your valuable help!)
March 10, 2008 at 12:54 pm
Just for further testing (and because it would be just like me to screw up the update query syntax), I did a select into a local table from the Excel workbook, and the update query (referencing the local table) worked as expected against the local table.
Does anyone here know the secret to update directly to a linked Excel table?
March 11, 2008 at 4:30 am
Hi Jim
It's likely that your problem is down to column data types - either the column you're updating or the column you're using as row identifier in Excel isn't being interpreted by the oledb driver as the column type you think it is. If I remember correctly, the driver determines the data type of an Excel 'column' based on the values in the first 8 cells below the header row.
Try first SELECTing from Excel like this...
SELECT [EID]
FROM [MYWORKBOOK]...[Cvt$]
WHERE [SSN] = 014540999
The leading zero in the SSN value looks ominous, at a guess this should be character data, try casting it as a CHAR or VARCHAR of appropriate length rather than allowing SQL Server to perform an implicit conversion.
Once you have the SELECT working, switch to your UPDATE and test / correct the column data type for EID. It may be necessary to force the column type in Excel by entering placeholder values and formatting.
Bear with it, this is a surprisingly useful technique: you can for instance update one spreadsheet file with data from another spreadsheet file (UPDATE...FROM...) without explicitly creating staging tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2008 at 5:33 am
Thanks Chris for your input!
A simple select worked fine. (The Excel cells are numeric, and the value is passed as a float, so the leading zero is not a problem.) But the UPDATE still failed.
I grasped for two more straws:
a. I changed the Provider String in my Linked Server Properties from Excel 5.0 to Excel 8.0 -- worked, but didn't change anything.
b.(The Winner) - My reference had been to the worksheet (Cvt$). I named my two column range and deleted/relinked the workbook and substituted the range name in the SELECT and UPDATE query -- then it all worked!
(I had wondered earlier why I didn't see my named range on my first link, but went with the worksheet name since it was shown. Turned out I had neglected to name the range I was using for my test.)
Now for some more testing, to be sure I can do an INSERT into the Excel range. (I read, in a 2004 MS article, that I can't DELETE, but I'll try that also.)
Being not easily satisfied , my next reservation is going to be that the link to the Excel workbook needs to be predefined. Do you think I could create a T-SQL procedure that will take, as arguments, a path to the requesting workbook and a range name, define the link procedurally, and then do something with the passed range. The intent would be to let any random workbook (opened by someone with permissions to the server and database) pass a table in to the server and get some results?
March 11, 2008 at 5:43 am
You're welcome, Jim. Interesting that it worked for a named range but not for a worksheet (tab). I've only used this with worksheets.
Here's a sproc which you can tailor to your requirements...any questions, just ask...
CREATE PROCEDURE [dbo].[CreateExcelLinkedServer]
@ServerName VARCHAR(30),
@TabName VARCHAR(30),
@xlsFileName VARCHAR(100)
AS
DECLARE @Retval int, @MsgString VARCHAR(400)
SET NOCOUNT ON
-- If the link already exists, then drop it first...
IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL
DROP TABLE #Worksheets1
CREATE TABLE #Worksheets1 (srvname varchar(20), providerstring VARCHAR(50))
INSERT INTO #Worksheets1
EXEC('SELECT srvname, providerstring FROM master.dbo.sysservers WHERE srvname = ''' + @ServerName + ''' AND providerstring = ''Excel 8.0;''')
IF (SELECT COUNT(srvname) FROM #Worksheets1 WHERE srvname = @ServerName) > 0
EXEC sp_dropserver @ServerName, 'droplogins'
DROP TABLE #Worksheets1
-- Attempt to create the link...
EXEC sp_addlinkedserver @ServerName,
@srvproduct = '',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = @xlsFileName,
@provstr = 'Excel 8.0;'
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
-- Validate the linked server by checking that the tab is available...
IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL
DROP TABLE #Worksheets
CREATE TABLE #Worksheets (TABLE_CAT varchar(20), TABLE_SCHEM VARCHAR(20), TABLE_NAME VARCHAR(20), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(20))
INSERT INTO #Worksheets
EXEC sp_tables_ex @ServerName
IF (SELECT COUNT(TABLE_NAME) FROM #Worksheets WHERE TABLE_NAME = @TabName) = 0
BEGIN
SET @MsgString = 'Tab [' + @TabName + '] or Excel file [' + @xlsFileName + '] not found, linked server [' + @ServerName + '] not created.'
SET @Retval = 1
END
ELSE
BEGIN
SET @MsgString = 'Linked server [' + @ServerName + '] created, with tab [' + @TabName + '].'
SET @Retval = 0
END
RAISERROR (@MsgString, 0, 1) WITH NOWAIT
RETURN @Retval
GO
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2008 at 6:19 am
Thanks!
At first glance it looks right on. (I'll have to study it a bit, since I'm a slow learner.)
That should be a big help!
March 11, 2008 at 7:01 am
Jim Russell (3/11/2008)
Thanks!At first glance it looks right on. (I'll have to study it a bit, since I'm a slow learner.)
That should be a big help!
heh heh I'm sure you mean thorough!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2008 at 7:25 am
Just to follow up my testing results...
Once the UPDATE worked, I tried:
1. INSERT - worked like a charm, and Excel even updated the range to include the inserted row.
2. DELETE - as advertised, it failed with:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYWORKBOOK" returned message "Deleting data in a linked table is not supported by this ISAM.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "DELETE FROM `CvtTable` WHERE `SSN`=(1.234567890000000e+008)" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SABRE2KRONOS".
March 11, 2008 at 7:34 am
You can't delete a row, but since you can blank out cells, for most purposes is doesn't matter.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2008 at 7:46 am
Good thought, thanks again, Chris!
For numeric cells, do you just update to NULLs?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply