Click here to Skip to main content
15,919,749 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
go


INSERT INTO [sqlDb].[dbo].[pages]([page_title],[model_name],[page_num])

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=YES','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]')

i have been trying to make this import from excel to sql server work for some time now but with no success. it keeps giving me this error even though my database table column name is exactly the same . i have tried various tweaking but still its giving problem.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'page_title'.

i have express edition of sql server so the import export wizzard is not even working nor could i get the dts working .. so any headsup would be great thank you ...
Posted
Updated 7-Dec-11 8:55am
v2

 
Share this answer
 
Comments
mubarakahmad 7-Dec-11 21:05pm    
thankyou, i tried that but it didnt work aswell
RaviRanjanKr 8-Dec-11 7:21am    
Thank :)
Have you double checked that the table pages in your database contains a column named page_title? For example that the column name isn't Page_Title.
 
Share this answer
 
Comments
mubarakahmad 7-Dec-11 20:45pm    
yes , i did that the column name is the same.that is what makes it even more annoying :)
Wendelius 8-Dec-11 0:02am    
One thing you could try is to run the following. Still getting the same error?



INSERT INTO [sqlDb].[dbo].[pages]([page_title],[model_name],[page_num])
SELECT [Sub Group Name] AS [page_title],[Model] AS [model_name],[pagenum] AS [page_num]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=YES','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]')
Is it possible that you have the table [pages] in a different database?

Also Try,
INSERT INTO [dbo].[pages]([page_title],[model_name],[page_num])
 
Share this answer
 
Comments
mubarakahmad 7-Dec-11 20:47pm    
No the database is same i have checked that aswell.
Try SELECT only page_title. You must have a return value thru this. If this doesn't, check if page_title do really exists. :)

Regards,
Eduard
 
Share this answer
 
Comments
mubarakahmad 7-Dec-11 20:58pm    
well when i comment out this line SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=yes','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]') and run just this select 'page_title' as page_title,'model_name' as model_name,'page_num' as page_num FROM [sqlDb].[dbo].[pages] i can see the columns on execution... so my column names right its something to do with the openrowset line

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900