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

cengaver
Starting Member

30 Posts

Posted - 07/08/2009 :  09:03:54  Show Profile  Reply with Quote
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.
Go to Top of Page

vishalg
Starting Member

29 Posts

Posted - 07/08/2009 :  23:37:43  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/09/2009 :  05:17:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/09/2009 :  05:18:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/09/2009 :  08:35:07  Show Profile  Reply with Quote
thank you very very much.
i am obliged to you
Go to Top of Page

vishalg
Starting Member

29 Posts

Posted - 07/09/2009 :  15:38:15  Show Profile  Reply with Quote
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
Go to Top of Page

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 07/10/2009 :  00:20:10  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/10/2009 :  02:08:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/10/2009 :  02:12:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 07/11/2009 :  18:08:01  Show Profile  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  02:16:59  Show Profile  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  07:00:35  Show Profile  Reply with Quote
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


Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  07:09:55  Show Profile  Reply with Quote
by the way i am using sql server 2008..
should it be a reason of the error.
have a nice day all
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/13/2009 :  09:12:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  09:36:24  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/13/2009 :  09:41:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  10:01:57  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/13/2009 :  10:22:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  10:31:45  Show Profile  Reply with Quote
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.. :(
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/13/2009 :  10:52:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
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