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

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/02/2009 :  01:51:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by korssane

Sorry guys, I completely forgot to check the previous 18 pages in this topic which very instructive.
My idea basically is to build reports and charts. For this i need to have my sql table uptodates. Some of these tables data need to be imported from Excel Sheet.
i am a new in SQL and i was able the 1st time to use linked server to import data from Excel Sheet.
The thing i want to know what is the next step to have the data updated automatiocally from excel file to my SQL data table.
Thanks Guys.


1 You can schedule it as a job and run periodically
2 You can make a connection to SQL Server in EXCEL and do updatation. I dont know how to do this. You can post this as a new topic so that others who know can reply

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/02/2009 :  02:33:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by harlingtonthewizard

I have attempted to make some changes to procedure 5 as detailed within the first post by madhivanan. This is mainly a learning exercise for me to use and in this case and I am running into a little trouble. I am trying to add the machinename as a variable and hence have added the following towards the top of the file:

--Set Machine Name
Declare @MachineName nvarchar (100)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '/XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

If I set as follows it works:

set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S butterfingers\XTRALIS_CYCLOPS'''

but if changed to this it fails:

set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "'+@MachineName+'"'''

When run it continues to execute and never returns.


<<
When run:
EXEC Export_to_Excel 'VC', 'SiteDetails','c:\Temp\SiteDetails'

When run it continues to execute and never returns.
>>

Make sure you have permission to that specific folder

Madhivanan

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 03/02/2009 :  05:50:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is there a different connection string if sheet is protected, or complete file is protected?
I cannot get OPENROWSET to work for file-passwordprotected Excel files.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 03/02/2009 :  18:50:30  Show Profile  Reply with Quote
I have noticed that procedure 5 does not work if the file path has spaces in it. Do you know of a solution for this? I have tried adding [] around the file name but this gives an error? Note I removed _ from xpcmdshell so I can save this thread.

Msg 102, Level 15, State 1, Procedure Export_to_Excel, Line 27
Incorrect syntax near '@file_name'.



USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Export_to_Excel] Script Date: 03/03/2009 10:16:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Export_to_Excel]

@db_name nvarchar(max),
@table_name nvarchar(max),
@file_name nvarchar(255)

As

--Generate column names as a recordset
declare @columns nvarchar(max), @sql nvarchar(max), @data_file nvarchar(255)

--Set Storage Location
Declare @Location nvarchar (255)
Set @Location = (SELECT EntryValue + '\VCPRS\Exports\' FROM (SELECT EntryValue FROM VCSystemData WHERE EntryName = 'VCPDataRootPath') as t)

--Set Machine Name
Declare @MachineName nvarchar (255)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '\XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

-- Add Date, Time and .xls to file name
Set [@file_name] = [@file_name] + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'

Select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
From information_schema.columns
Where table_name=@table_name

Select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
Select @data_file=substring([@file_name],1,len([@file_name])-charindex('\',reverse([@file_name])))+'\data_file.xls'

--Generate column names in the passed EXCEL file
Set @sql='exec master..xpcmdshell ''bcp "select * from (select '+@columns+') as t" queryout "'+[@file_name]+'" -c -T -S "'+@MachineName+'"'''
Exec(@sql)

--Generate data in the dummy file
Set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "'+@MachineName+'"'''
Exec(@sql)

--Copy dummy file to passed EXCEL file
Set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> '+[@file_name]+''''
Exec(@sql)

--Delete dummy file
Set @sql= 'exec master..xpcmdshell ''del '+@data_file+''''
Exec(@sql)

RETURN
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36823 Posts

Posted - 03/02/2009 :  18:53:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Add double quotes to @file_name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/07/2009 :  03:07:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by harlingtonthewizard

I have noticed that procedure 5 does not work if the file path has spaces in it. Do you know of a solution for this? I have tried adding [] around the file name but this gives an error? Note I removed _ from xpcmdshell so I can save this thread.

Msg 102, Level 15, State 1, Procedure Export_to_Excel, Line 27
Incorrect syntax near '@file_name'.



USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Export_to_Excel] Script Date: 03/03/2009 10:16:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Export_to_Excel]

@db_name nvarchar(max),
@table_name nvarchar(max),
@file_name nvarchar(255)

As

--Generate column names as a recordset
declare @columns nvarchar(max), @sql nvarchar(max), @data_file nvarchar(255)

--Set Storage Location
Declare @Location nvarchar (255)
Set @Location = (SELECT EntryValue + '\VCPRS\Exports\' FROM (SELECT EntryValue FROM VCSystemData WHERE EntryName = 'VCPDataRootPath') as t)

--Set Machine Name
Declare @MachineName nvarchar (255)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '\XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

-- Add Date, Time and .xls to file name
Set [@file_name] = [@file_name] + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'

Select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
From information_schema.columns
Where table_name=@table_name

Select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
Select @data_file=substring([@file_name],1,len([@file_name])-charindex('\',reverse([@file_name])))+'\data_file.xls'

--Generate column names in the passed EXCEL file
Set @sql='exec master..xpcmdshell ''bcp "select * from (select '+@columns+') as t" queryout "'+[@file_name]+'" -c -T -S "'+@MachineName+'"'''
Exec(@sql)

--Generate data in the dummy file
Set @sql='exec master..xpcmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "'+@MachineName+'"'''
Exec(@sql)

--Copy dummy file to passed EXCEL file
Set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> '+[@file_name]+''''
Exec(@sql)

--Delete dummy file
Set @sql= 'exec master..xpcmdshell ''del '+@data_file+''''
Exec(@sql)

RETURN


Dont use [] around the filename.
The problem is the type command fails when any of the file name has space.
The solution is, as Tara specified,to wrap it inside double quotes.

So the line

Set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> '+[@file_name]+''''


should be

Set @sql= 'exec master..xpcmdshell ''type '+@data_file+' >> "'+@file_name+'"'''


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 03/07/2009 03:10:06
Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/11/2009 :  05:11:47  Show Profile  Reply with Quote
I am trying to export data from a table residing in the SQL server to an excel sheet in my local machine. Is this possible? Pls help.
The xp_fileexist constantly tells me file is not found, even though I have created the xlsx file.
Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/11/2009 :  05:18:15  Show Profile  Reply with Quote
I am trying to execute command for exporting data from SQL table to excel sheet (using commands given in this forum itself)
I continously get error "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I have seen the same thing being posted so many times. But nothing is working. I connecting to the server machine and trying to execute the query and the excel sheet resides in my local machine. Could this be a problem?
I cannot save the excel on the server system (C or D directories). Neither can I restart the server machine or anything of that sort)

Any suggestions would be very helpful

quote:
Originally posted by madhivanan

Is rept_name a table name?

Try

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\report.xls;', 'SELECT * FROM [Sheet1$]') select * from rept_name


Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/11/2009 :  05:31:57  Show Profile  Reply with Quote
How can I make the file exist in the server's directory??
The server is in some other machine. Pls help

quote:
Originally posted by madhivanan

Make sure the file exists in the server's directory

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/11/2009 :  06:20:10  Show Profile  Reply with Quote
Did u get solution for this? Coz I am facing same situation. Need to export data from server SQL machine to an Excel file on my local system
The fileexist command constantly tells me the file does not exist in the path specified :-(
quote:
Originally posted by dpdoug

madhivanan,

I'm assuming that this code:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

is exporting from a local SQLServer to an excel file on the same machine.

I have a web application where I need to do a backup of a table on a web server and dump the data into an excel file on the user's machine. How would I do that?

Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/11/2009 :  06:41:08  Show Profile  Reply with Quote
Can anyone tell me how to export SQL data to an excel on a remote machine. (The excel sheet is basically in my local system. I am connecting to the SQL server and trying to execute the query. It does not seem to recognize my excel)

This is the query I am trying to execute. The testing.xlsx is only local system, D drive

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/11/2009 :  07:13:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dona

Can anyone tell me how to export SQL data to an excel on a remote machine. (The excel sheet is basically in my local system. I am connecting to the SQL server and trying to execute the query. It does not seem to recognize my excel)

This is the query I am trying to execute. The testing.xlsx is only local system, D drive

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO



If the file exists in the local system, try

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\your_system_name\D$\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO

Make sure you need to change the part \\your_system_name\D$\testing.xlsx


Madhivanan

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

dona
Starting Member

11 Posts

Posted - 03/12/2009 :  01:18:03  Show Profile  Reply with Quote
Thanks a lot Madhivanan for your reply.

I am getting the same error that everyone is complaining about
"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."

I dont know if this has anything to do with file not being recognized. Anyway, I executed the below command to check if file exists. It keeps saying 'file not found'. (I have given path as specified by you - with my actual system name replacing My_System_Name. I even tried sharing testing.xls with the server machine)

DECLARE @exists int
DECLARE @cmd VARCHAR(1000)
DECLARE @File VARCHAR(200)
SET @File = '\\My_System_Name\D$\testing.xlsx'
EXEC master..xp_fileexist @File, @exists output
if @exists = 1
BEGIN
PRINT 'File is found'
END
ELSE
BEGIN
PRINT 'File is not found'
END



quote:
Originally posted by madhivanan

quote:
Originally posted by dona

Can anyone tell me how to export SQL data to an excel on a remote machine. (The excel sheet is basically in my local system. I am connecting to the SQL server and trying to execute the query. It does not seem to recognize my excel)

This is the query I am trying to execute. The testing.xlsx is only local system, D drive

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO



If the file exists in the local system, try

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\your_system_name\D$\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO

Make sure you need to change the part \\your_system_name\D$\testing.xlsx


Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/12/2009 :  02:06:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dona

Thanks a lot Madhivanan for your reply.

I am getting the same error that everyone is complaining about
"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."

I dont know if this has anything to do with file not being recognized. Anyway, I executed the below command to check if file exists. It keeps saying 'file not found'. (I have given path as specified by you - with my actual system name replacing My_System_Name. I even tried sharing testing.xls with the server machine)

DECLARE @exists int
DECLARE @cmd VARCHAR(1000)
DECLARE @File VARCHAR(200)
SET @File = '\\My_System_Name\D$\testing.xlsx'
EXEC master..xp_fileexist @File, @exists output
if @exists = 1
BEGIN
PRINT 'File is found'
END
ELSE
BEGIN
PRINT 'File is not found'
END



quote:
Originally posted by madhivanan

quote:
Originally posted by dona

Can anyone tell me how to export SQL data to an excel on a remote machine. (The excel sheet is basically in my local system. I am connecting to the SQL server and trying to execute the query. It does not seem to recognize my excel)

This is the query I am trying to execute. The testing.xlsx is only local system, D drive

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO



If the file exists in the local system, try

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\your_system_name\D$\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO

Make sure you need to change the part \\your_system_name\D$\testing.xlsx


Madhivanan

Failing to plan is Planning to fail





Try using D instead of D$ in the file path

Madhivanan

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

dona
Starting Member

11 Posts

Posted - 03/12/2009 :  04:41:04  Show Profile  Reply with Quote
I have seen this question posted a 100 times and I have been reading through all the solutions posted in this forum and all over the net. But nothing has so far worked for me!!!

Objective - Export data from SQL Server 2005 (Remote machine) to Excel (my local system) (Office 2007)

Scenario - SQL server 2007 is on some machine which I cannot directly go and work on
Excel - testing.xlsx is on my local machine.
I have created colums Emp_Id and Emp_Details in the excel sheet. There is Sheet1 without any space or anything - exactly Sheet1 in my excel.

Query used -
USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\My_System_Name\D$\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO

(Note: I have tried the above path as D instead of D$ also)

Error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


PLEASE HELP - Have been googling around for ages now and still at same point. Tried reading the Microsoft fixes. But cant understand those either. Moreover, no access to the Server machine directly !! :-(


quote:
Originally posted by madhivanan

quote:
Originally posted by dona

Thanks a lot Madhivanan for your reply.

I am getting the same error that everyone is complaining about
"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."

I dont know if this has anything to do with file not being recognized. Anyway, I executed the below command to check if file exists. It keeps saying 'file not found'. (I have given path as specified by you - with my actual system name replacing My_System_Name. I even tried sharing testing.xls with the server machine)

DECLARE @exists int
DECLARE @cmd VARCHAR(1000)
DECLARE @File VARCHAR(200)
SET @File = '\\My_System_Name\D$\testing.xlsx'
EXEC master..xp_fileexist @File, @exists output
if @exists = 1
BEGIN
PRINT 'File is found'
END
ELSE
BEGIN
PRINT 'File is not found'
END



quote:
Originally posted by madhivanan

quote:
Originally posted by dona

Can anyone tell me how to export SQL data to an excel on a remote machine. (The excel sheet is basically in my local system. I am connecting to the SQL server and trying to execute the query. It does not seem to recognize my excel)

This is the query I am trying to execute. The testing.xlsx is only local system, D drive

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO



If the file exists in the local system, try

USE [Client_Test];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\your_system_name\D$\testing.xlsx;',
'SELECT [Emp_Id], [Emp_Details] FROM [Sheet1$]')
SELECT Emp_Id, Emp_Details
FROM dbo.Emp_Ref
GO

Make sure you need to change the part \\your_system_name\D$\testing.xlsx


Madhivanan

Failing to plan is Planning to fail





Try using D instead of D$ in the file path

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 03/12/2009 :  04:56:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

What does these two return?

EXEC master..xp_cmdshell 'dir \\My_System_Name\D$\testing.xlsx;'
EXEC master..xp_cmdshell 'dir \\My_System_Name\D\testing.xlsx;'


Madhivanan

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

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 03/12/2009 :  20:56:36  Show Profile  Reply with Quote
Hi,

I am trying to setup a process for exporting and importing excel data to a SQL 2005 DB Tables. I have been successful with procedure 5 to export however I cannot get the import to work. I have been searching for answers and getting very confused.

I have set 'ad hoc queries' = 1

With this:

Select *
FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=C:\export\users_20090313_110544.xls', 'SELECT * FROM [users_20090313_110544$] ' )

I get:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] External table is not in the expected format.".
Msg 7303, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 21
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".'

With this:

Insert into UserDetails Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\export\users_20090313_110544.xls;HDR=YES',
'SELECT * FROM [users_20090313_110544$]')

I get:

Msg 7399, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 30
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Edited by - harlingtonthewizard on 03/25/2009 20:41:15
Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/13/2009 :  05:59:22  Show Profile  Reply with Quote
Both returns
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

I read somewhere that moving the TEMP folder (where the %temp% and %tmp% environment variables are set)in the SQL Server machine out of Documents And Settings folder (or any similar folder where we do not normally have write access to) would solve the problem.
However, there are so many TEMP folders in the Server machine, that I really cannot decide which one it is. (The Set t command does not work)

quote:
Originally posted by madhivanan


What does these two return?

EXEC master..xp_cmdshell 'dir \\My_System_Name\D$\testing.xlsx;'
EXEC master..xp_cmdshell 'dir \\My_System_Name\D\testing.xlsx;'


Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

matty
Posting Yak Master

161 Posts

Posted - 03/13/2009 :  07:39:48  Show Profile  Reply with Quote
If the file is open, close it and try. I had the same error, it worked when i closed the file.
Go to Top of Page

dona
Starting Member

11 Posts

Posted - 03/16/2009 :  00:55:19  Show Profile  Reply with Quote
File is not open. dont know what the problem could. even made the temp environment variables to point to a location where write access is there
quote:
Originally posted by matty

If the file is open, close it and try. I had the same error, it worked when i closed the file.

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.26 seconds. Powered By: Snitz Forums 2000