| Author |
 Topic  |
|
cengaver
Starting Member
30 Posts |
 Posted - 07/08/2009 : 09:03:54
|
hi
please, can you help me?
i need sql program. it should data transfer from sql to excel after finishing transfer the program will send mail and at the end of program excel will be deleted.
|
 |
|
|
vishalg
Starting Member
29 Posts |
Posted - 07/08/2009 : 23:37:43
|
Hello,
I tried doing this:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Users\vinchenzo\Desktop\wards\Wards\Sales\Wards_Sales_2005.xls;HDR=YES',
'SELECT * FROM [2005 Sales$$]')
but m getting the following error
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Can someone plz help? Thanks in advance.
-Vishal |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/09/2009 : 05:17:07
|
quote:
Originally posted by cengaver
hi
please, can you help me?
i need sql program. it should data transfer from sql to excel after finishing transfer the program will send mail and at the end of program excel will be deleted.
1 Goto first page of this thread and refer the code
2 To send mail, refer http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html
3 To delete a file, use
exec master..xp_cmdshell 'del file_path'
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/09/2009 : 05:18:38
|
quote:
Originally posted by vishalg
Hello,
I tried doing this:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Users\vinchenzo\Desktop\wards\Wards\Sales\Wards_Sales_2005.xls;HDR=YES',
'SELECT * FROM [2005 Sales$$]')
but m getting the following error
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Can someone plz help? Thanks in advance.
-Vishal
Refer point 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/12/common-migration-issues-version-2000-to-2005.aspx
Madhivanan
Failing to plan is Planning to fail |
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/09/2009 : 08:35:07
|
thank you very very much.
i am obliged to you |
|
|
|
vishalg
Starting Member
29 Posts |
Posted - 07/09/2009 : 15:38:15
|
Thanks a lot. It worked :)
But i have another question. I am trying to load different excel files into the same table. The files are almost similar but not the same. For example, one file has all the columns, another file is missing last column, another file is missing the headers, etc etc..
how can i use the following code for all the files:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
I know this cannot work if the excel files are totally different, but I want to know can this or modification of this code can work for the above given cases???
Thank you :)
-Vishal |
|
|
|
stephenbaer
Yak Posting Veteran
USA
71 Posts |
Posted - 07/10/2009 : 00:20:10
|
Hi madhivanan,
I know this is an old thread...but (always a but), I am trying to use the proc_generate_excel_with_columns to create file that we send out every week. I created the proc, but whenever I execute it, I get some wierd dstuff in the results pane:
First this:
Password:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
Then this:
The system cannot find the file specified.
NULL
Then this:
Could Not Find N:\MSSQL\Temp\data_file.xls
NULL
I execute it like this:
USE [DimonDale]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[proc_generate_excel_with_columns]
@db_name = N'Dimondale',
@table_name = N'dbo.PopReport',
@file_name = N'N:\MSSQL\Temp\testing.xls'
SELECT 'Return Value' = @return_value
GO
It does create a file called testing.xls in the folder specified, but it is empty except for a sheet also named testing. The table is actually a view, but that wouldn't matter, would it?
UPDATE: I have had some success with the insert into OPENROWSET code, but I'm a little stumped by the fact that "OPENROWSET does not accept variables for its arguments" (http://msdn.microsoft.com/en-us/library/ms190312.aspx). Is there a way around this? Can I build an @SQL string and use EXEC (@SQL) for the insert into OPENROWSET?[
For instance, this works great:
------
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=N:\MSSQL\Temp\poprpt.xls;',
'SELECT * FROM [DACF2$]') select Lastname as 'Last Name', FirstName as 'First Name', PDJ, DateofBirth as DOB, Age, Gender,Ethnicity, DateOfPlacement As 'start date', DateofTermination as 'exit date', TermReason as 'exit reason' from dimondale.dbo.popreport where FacilityAbbrev = 'dacf2'
-----------
(The column aliases are necessary because the xls file is from an external source.)I can send different DACF #'s to different sheets by repeating the code, too. Like I said, it works great. I copy the file using
-----------
DECLARE @sql AS varchar(8000), @Filename AS varchar(255),
CopyBlank AS varchar(255), @TempPath AS varchar(255)
Select @TempPath = 'N:\MSSQL\Temp\'
Select @Filename = CAST(datepart(yy, getdate()) AS varchar(4)) + '-' + CAST(datepart(m, getdate()) AS varchar(2)) + '-' + CAST(datepart(d, getdate()) AS varchar(2))+ '_F-B_PopRpt.xls'
Select @CopyBlank = ' ' + @TempPath + 'Blank_PopRpt.xls ' + @TempPath + @filename
Select @SQL = 'xp_cmdshell '+ '''''copy + @CopyBlank'''''
set @sql= 'exec xp_cmdshell ''copy '+@CopyBlank+'''' + ', No_Output'
EXEC (@SQL)
so the target file for the open rowsource changes... OH! Wait! DOH! I can copy to poprpt.xls, insert the data, then copy again using the dates! Thanks for intending to help! LOL 
----------------
-Stephen |
Edited by - stephenbaer on 07/11/2009 16:32:22 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/10/2009 : 02:08:53
|
quote:
Originally posted by cengaver
thank you very very much.
i am obliged to you
You are welcome 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/10/2009 : 02:12:26
|
quote:
Originally posted by vishalg
Thanks a lot. It worked :)
But i have another question. I am trying to load different excel files into the same table. The files are almost similar but not the same. For example, one file has all the columns, another file is missing last column, another file is missing the headers, etc etc..
how can i use the following code for all the files:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
I know this cannot work if the excel files are totally different, but I want to know can this or modification of this code can work for the above given cases???
Thank you :)
-Vishal
You may need to use different queries to export data from different excel files. If there is no header, specify HDR=NO. If there are more/less columns than the table, explicitely names the columns like below
Insert into SQLServerTable(col1,col2,...colN)
Select col1,col2,...colN FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
Madhivanan
Failing to plan is Planning to fail |
|
|
|
stephenbaer
Yak Posting Veteran
USA
71 Posts |
Posted - 07/11/2009 : 18:08:01
|
Here is the final procedure in reference to my earlier question. Works great!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
USE MYDATABASE
/* copy the original (prepared with sheetnames and column names) .xls to a dummy file */
EXEC xp_cmdshell 'copy N:\MSSQL\Temp\Blank_PopRpt.xls N:\MSSQL\Temp\poprpt.xls', No_Output
go
/* insert DACF2 data onto DACF2 sheet */
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=N:\MSSQL\Temp\poprpt.xls;',
'SELECT * FROM [DACF2$]') select Lastname as 'Last Name', FirstName as 'First Name', PDJ, DateofBirth as DOB, Age, Gender,Ethnicity, DateOfPlacement As 'start date', DateofTermination as 'exit date', TermReason as 'exit reason' from mydatabase.dbo.popreport where FacilityAbbrev = 'dacf2'
/* insert DACF3 data onto DACF3 sheet */
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=N:\MSSQL\Temp\poprpt.xls;',
'SELECT * FROM [DACF3$]') select Lastname as 'Last Name', FirstName as 'First Name',PDJ, DateofBirth as DOB, Age, Gender, Ethnicity, DateOfPlacement As 'start date', DateofTermination as 'exit date', TermReason as 'exit reason' from mydatabase.dbo.popreport where FacilityAbbrev = 'dacf3'
/* insert DACF4 data onto DACF4 sheet */
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=N:\MSSQL\Temp\poprpt.xls;',
'SELECT * FROM [DACF4$]') select Lastname as 'Last Name', FirstName as 'First Name', PDJ, DateofBirth as DOB, Age, Gender, Ethnicity, DateOfPlacement As 'start date', DateofTermination as 'exit date', TermReason as 'exit reason' from mydatabase.dbo.popreport where FacilityAbbrev = 'dacf4'
/* Copy the dummy file to a file with the date in the name */
DECLARE @sql AS varchar(8000), @Filename AS varchar(1000), @CopyFilled AS varchar(500), @TempPath AS varchar(500),
@SavePath AS varchar(500), @SendMail AS varchar(500)
Select @TempPath = 'N:\MSSQL\Temp\'
Select @Filename = CAST(datepart(yy, getdate()) AS varchar(4)) + '-' + CAST(datepart(m, getdate()) AS varchar(2)) + '-' + CAST(datepart(dd, getdate()) AS varchar(2))+ '_F-B_PopRpt.xls'
Select @CopyFilled = ' ' + @TempPath + 'PopRpt.xls ' + @TempPath + @filename
Select @sql= 'exec xp_cmdshell ''copy '+@CopyFilled+'''' + ', No_Output'
EXEC (@SQL)
/* Attach the file to an email and send (the sp_send_dbmail won't accept variables in the arguments, so you have to build it with some double quotes first, then exec the whole string*/
Select @SendMail = 'EXEC msdb.dbo.sp_send_dbmail
@recipients= ''somebody@SomeDomain.dom'', @subject = ''Auto-Generated Population report'',
@body = ''Population report attached'', @body_format = ''HTML'',
@file_attachments = ''N:\MSSQL\Temp\' + @filename +''''
EXEC (@Sendmail)
/*Cleanup*/
EXEC xp_cmdshell 'del N:\MSSQL\Temp\PopRpt.xls', no_output
go
----------------
-Stephen |
Edited by - stephenbaer on 07/13/2009 00:00:49 |
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 02:16:59
|
ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\raporlar.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * FROM personal
DECLARE @body VARCHAR(1024)
SET @body = 'dosyalar '+
CONVERT(VARCHAR, GETDATE())
declare @rc int
exec @rc = omerDB.dbo.xp_smtp_sendmail
@recipients=N'ivnbnn@gmail.com',
@message = N'dosyalar' ,
@subject = N'dosyalar',
@attachment = N'c:\raporlar.xls'
exec omerDB..xp_cmdshell 'del file_path'
END
this program is giving error. but i couldnt see it.
is it true this program
|
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 07:00:35
|
quote:
Originally posted by cengaver
ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\raporlar.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * FROM personal
DECLARE @body VARCHAR(1024)
SET @body = 'dosyalar '+
CONVERT(VARCHAR, GETDATE())
declare @rc int
exec @rc = omerDB.dbo.xp_smtp_sendmail
@recipients=N'ivnbnn@gmail.com',
@message = N'dosyalar' ,
@subject = N'dosyalar',
@attachment = N'c:\raporlar.xls'
exec omerDB..xp_cmdshell 'del file_path'
END
this program is giving error. but i couldnt see it.
is it true this program
the error is Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
I added link server but the error still continue
please help me
|
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 07:09:55
|
by the way i am using sql server 2008..
should it be a reason of the error.
have a nice day all |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/13/2009 : 09:12:44
|
quote:
Originally posted by cengaver
by the way i am using sql server 2008..
should it be a reason of the error.
have a nice day all
Refer this
http://support.microsoft.com/kb/327489
Madhivanan
Failing to plan is Planning to fail |
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 09:36:24
|
i had looked that link but there is not providers option in sql server 2008.. i dont know maybe my mistake i couldnt see it altough i looked it for 2 hours.
if you know please show me |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/13/2009 : 09:41:52
|
quote:
Originally posted by cengaver
i had looked that link but there is not providers option in sql server 2008.. i dont know maybe my mistake i couldnt see it altough i looked it for 2 hours.
if you know please show me
Goto the bottom part of the article that specifies about the change to be done thru Registry
Madhivanan
Failing to plan is Planning to fail |
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 10:01:57
|
i did what the website say but unfortunately it is same
i will cry :(
i couldnt do it for 3 hours......
i want to destroyed the combuter |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/13/2009 : 10:22:42
|
quote:
Originally posted by cengaver
i did what the website say but unfortunately it is same
i will cry :(
i couldnt do it for 3 hours......
i want to destroyed the combuter
Can you tell me step-by-step what you did?
There may be a chance to find where goes wrong
Madhivanan
Failing to plan is Planning to fail |
|
|
|
cengaver
Starting Member
30 Posts |
Posted - 07/13/2009 : 10:31:45
|
| i wrote the codes and it gave error that is in surfacearea configuration adhoc... sohuld be true then i did like that after that this error showed what i said previous message then i went new link server and configure it and providers in sqloledb provider checked disallowadhoc... but the error still continue.. :( |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/13/2009 : 10:52:05
|
Did you try this?
Start Registry Editor.
Locate, and then click the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL
On the Edit menu, click Add Value, and then add this registry value:
Value name: DisallowAdHocAccess
Data type: REG_DWORD
Radix: Hex
Value data: 0
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Topic |
|
|
|
Export to Excel
