SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  06:32:57  Show Profile  Reply with Quote
hi spirit,

i am executing the sql statement in C# and not in the sql level. is the syntax same for both C# and sql ?

shybi
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 07/27/2006 :  06:36:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
i'd think so.

Build the string in c# (don't run it) and copy it to QA.
Then tell us what the error is.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  06:51:59  Show Profile  Reply with Quote
i execute the query in QA

INSERT INTO UserDetail
([FirstName], [LastName])
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' F : \\ Employee.xls ';Extended Properties=\"Excel 8.0;HDR=YES;\"';
SELECT [FirstName], [LastName]
FROM [Employee1$])


its showing the synatx error :

Line 1: Incorrect Syntax near 'F:'.

is it the problem with the provider???
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 07/27/2006 :  07:48:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
According to madhivanan's first post in this thread
it should be like this:

INSERT INTO UserDetail([FirstName], [LastName])
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 
		'Database=F:\Employee.xls;Excel 8.0;HDR=YES;"',
		'SELECT [FirstName], [LastName] FROM [Employee1$]')




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/27/2006 :  08:48:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>can you tell me the syntax for OPENROWSET

Did you read the first page of this topic? If not, read it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

davidreed
Starting Member

USA
7 Posts

Posted - 07/27/2006 :  09:00:28  Show Profile  Reply with Quote
problem is with the two backslash characters..

change F:\\ to F:
David Reed
David Reed Consulting
White Plains, Maryland
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/31/2006 :  03:01:03  Show Profile  Reply with Quote
hi everyone,

i am still getting an error like when i execute the following sql statement in query

INSERT INTO Northwind.dbo.UserDetail
([FirstName], [LastName])
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0;Database=F:Employee.xsl;Extended Properties=\"Excel 8.0;HDR=YES;\"',
'SELECT [FirstName], [LastName]
FROM [Employee1$]')

error is : Line 2: Incorrect syntax near ')'.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/31/2006 :  03:20:08  Show Profile  Reply with Quote
after some modification and when i execute the query in QA

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Database=F:Employee.xsl;Excel 8.0;HDR=YES',
'SELECT [FirstName], [LastName]
FROM [Employee1$]')

error: OLE DB error trace[Non-interface error:Provider not registered.].
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/31/2006 :  04:34:52  Show Profile  Reply with Quote
what does the error mean?

error: OLE DB error trace[Non-interface error:Provider not registered.].
Could not locate registry entry for OLE DB provider 'provider=Microsoft.Jet.OLEDB.4.0'.
Deferred prepare could not be completed.

what should i do to rectify the error?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 07/31/2006 :  08:19:58  Show Profile  Visit spirit1's Homepage  Reply with Quote
other than try reinstalling the oledb driver nothing else comes to mind...



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/31/2006 :  12:25:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>'Database=F:Employee.xsl;Excel 8.0;HDR=YES',

Also, Check whether the file name is correct

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jronneba
Starting Member

USA
2 Posts

Posted - 07/31/2006 :  13:34:22  Show Profile  Reply with Quote
This thread helped me out a great deal and as I’m a fond believer in Google karma, I thought I’d post my solution. I needed to create an export from SQL to Excel which would be properly formatted in Excel and each export would have it’s own unique filename.

First I created a temp table which temporarily stored all of the data that was needed. Then I created a Template Excel file (ProductTemplate.xls) with 2 tabs – 1 is an exact match to the temp table with the proper column headings and the 2nd tab is the properly formatted layout. Then I called the following Stored Procedure. @File_Name is a unique name with a time/date stamp which is also how I identified the records in the temp table – in my case it was FNameLName_ProductName_DateTime. You can see the SP copies the Template Excel file with the new unique filename > then does an insert for openrowset where the records equal @File_Name > then copies the new file to a different server. The tab with the formatted layout has formulas to pull the values from the ‘data’ tab then I have a macro that runs to Copy/Paste Special as values. I’m definitely not claiming that this is the best solution but it works for me and will hopefully help someone out along the way.

CREATE procedure sp_tblProducttmp_ExportCopy
(
@File_Name as varchar(100)
)
as
begin
set nocount on

declare @cmd varchar(1000),
@cmd2 varchar(1000),
@fn varchar(500),
@fn2 varchar(500),
@provider varchar(100),
@ExcelString varchar(100),
@sql varchar(200),
@dest varchar(500)

select @fn = 'D:\Templates\' + @File_Name + '.xls'
select @cmd = 'Copy D:\Templates\ProductTemplate.xls ' + @fn
exec MASTER..XP_CMDSHELL @cmd, NO_OUTPUT
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fn
set @sql = 'select * from tblProducttmp where UniqueID =''' + @File_Name + ''' order by Type'
exec('insert into OPENROWSET(''' + @provider + ''',''' + @ExcelString + ''',''Select * from [tblProducttmp $]'')
'+@sql+'')
select @fn2 = 'D:\Templates\' + @File_Name + '.xls '
select @dest = '\\ServerName\FinalDestination\' + @File_Name + '.xls '
select @cmd2 = 'Move ' + @fn2 + @dest
exec MASTER..XP_CMDSHELL @cmd2, NO_OUTPUT
set nocount off
end
GO

I had difficulty figuring out how to get it to copy directly to a different server so my work around was to create it locally and then move the file to the other server. Due to my deadline, I had to put it in place with this workaround but I’ll hopefully resolve the issue at some point.

If you have any questions, I’ll do my best to answer them.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  00:51:09  Show Profile  Reply with Quote
hi all,

when i try to execute the query in the query analyzer

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Database=F:Employee.xsl;Excel 8.0;HDR=YES',
'SELECT [FirstName], [LastName]
FROM [Employee1$]') Rowset_1

its showing the error :

OLE DB error trace[Non-interface error:Provider not registered.].
Could not locate registry entry for OLE DB provider 'provider=Microsoft.Jet.OLEDB.4.0'.
Deferred prepare could not be completed.

spirit1 has adviced to reinstall the oledb driver. is it possible ti reinstall the driver.it actually comes with the OS, if possible, how can i do it????

madhivanan, as you told i had check the filename and 'Database=F:Employee.xsl;Excel 8.0;HDR=YES', . but i am still getting the error.

what does this means could not locate registry entry for OLE DB provider ???

help needed urgently..
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/01/2006 :  02:15:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 
    'SELECT * FROM [Sheet1$]')
Now most error desriptions are output. When you have resolved the error, switch back to Jet again.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/01/2006 02:21:17
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  02:54:33  Show Profile  Reply with Quote
when i use the MSDASQL like

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DataBase=F:Employee.xls;Excel 8.0;HDR=YES',
'SELECT FirstName,LastName FROM [Employee1$]')

its giving error like

ADO error:[OLE/DB provider returned message: Operation wa canceled.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ

Initialize returned 0x80040e4e
Could not initialize data source object of OLE DB provider "MSDASQL'.
Deferred prepare could not be completed

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/01/2006 :  03:15:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by shybi

when i use the MSDASQL like

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DataBase=F:Employee.xls;Excel 8.0;HDR=YES',
'SELECT FirstName,LastName FROM [Employee1$]')

There is no proper path to the file.
INSERT INTO UserDetail
SELECT     *
FROM         OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DataBase=F:Employee.xls;Excel 8.0;HDR=YES', 
                      'SELECT FirstName,LastName FROM [Employee1$]')
Proper code would be
INSERT INTO UserDetail
SELECT     *
FROM         OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=F:\Employee.xls', 
                      'SELECT FirstName,LastName FROM [Employee1$]')

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/01/2006 03:19:17
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  03:16:15  Show Profile  Reply with Quote
how can i register the OLE DB provider in the registry editor?? in the registry editor i ddidnt find any provider for the OLE DB? how can i register it in the editor?
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  03:27:17  Show Profile  Reply with Quote
when i use this code

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=F:\Employee.xls',
'SELECT FirstName,LastName FROM [Employee1$]')

its giving the error:

ADO error: [OLE/DB provider returned message: [Microsft][ODBC Excel Driver] The Microsoft Jet databse engine could not find the object 'Employee1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLD/DB Provider 'MSDASQL']ColumnsInf

GetColumnsInfo returned 0x80004005: ].
OLE db provider 'MSDASQL' reported an error.
Deferred prepare could not be completed.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  03:37:58  Show Profile  Reply with Quote
IF i use Database instead of DBQ,i am getting another error

ADO error:[OLE/DB provider returned message: Operation wa canceled.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ

Initialize returned 0x80040e4e
Could not initialize data source object of OLE DB provider "MSDASQL'.
Deferred prepare could not be completed

so what should i use either DBQ or Database?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/01/2006 :  03:49:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Reinstall the MDAC package from http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000