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/13/2009 :  10:58:45  Show Profile  Reply with Quote
i did but it doesnt work
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/13/2009 :  11:03:37  Show Profile  Reply with Quote
can you put the codes on your computers and if you work correctly can you say me if it doesnt can you say me mistakes..
btw thanks for everything
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  02:54:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cengaver

can you put the codes on your computers and if you work correctly can you say me if it doesnt can you say me mistakes..
btw thanks for everything


It works for me.

Try to execute the code after executing this

EXEC SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
go
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE
go
EXEC SP_CONFIGURE 'show advanced options', 0
RECONFIGURE

Madhivanan

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

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  03:46:44  Show Profile  Reply with Quote
hi madhivanan
i tried every thing what you said and it still doesnt work
can you write yourself, please if you have time..
i think it doesnt take your much time for you
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  04:26:57  Show Profile  Reply with Quote
i tried you said and the error

Msg 102, Level 15, State 1, Procedure TEST, Line 25
Incorrect syntax near 'reconfigure'.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near 'END'.
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  04:44:34  Show Profile  Reply with Quote
ALTER PROCEDURE [dbo].[TEST]
AS

exec sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

BEGIN

SET NOCOUNT ON;
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

SET @server = 'ar-ge'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\raporlar.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog



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())

EXEC omerDB..xp_sendmail
@recipients='omer_yalcin1985@hotmail.com',
@message = 'dosyalar' ,
@subject = 'dosyalar',
@attachment = 'c:\raporlar.xls'

exec xp_cmdshell 'del C:\raporlar.xls'

--
END

this is my program amd the error is still same ::(
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  04:55:00  Show Profile  Reply with Quote
please help me i have to do it and i couldnt understand sql exactly
i really tried everything..
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  04:57:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Dont include it as part of a procedure. Execute it once

EXEC SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
go
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE
go
EXEC SP_CONFIGURE 'show advanced options', 0
RECONFIGURE

Now your procedure should be


ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN

SET NOCOUNT ON;
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

SET @server = 'ar-ge'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\raporlar.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog



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())

EXEC omerDB..xp_sendmail
@recipients='omer_yalcin1985@hotmail.com',
@message = 'dosyalar' ,
@subject = 'dosyalar',
@attachment = 'c:\raporlar.xls'

exec xp_cmdshell 'del C:\raporlar.xls'

END


Madhivanan

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

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  05:10:50  Show Profile  Reply with Quote
i tried it and the new error is
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  05:17:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
These two lines are informations on configuration settings
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Now try to alter the procedure alone and see what you get

Madhivanan

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

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  06:16:01  Show Profile  Reply with Quote
sorry, i couldnt understand
how will i alter the procedure ?
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  06:20:25  Show Profile  Reply with Quote
now the error is

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 7357, Level 16, State 2, Line 30
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  06:25:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cengaver

now the error is

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 7357, Level 16, State 2, Line 30
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.



Make sure the File exists in the Server's directory
Check the sheet name and it's spelling

Now run this

ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN

SET NOCOUNT ON;
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

SET @server = 'ar-ge'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\raporlar.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog



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())

EXEC omerDB..xp_sendmail
@recipients='omer_yalcin1985@hotmail.com',
@message = 'dosyalar' ,
@subject = 'dosyalar',
@attachment = 'c:\raporlar.xls'

exec xp_cmdshell 'del C:\raporlar.xls'

END


Madhivanan

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

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  07:43:32  Show Profile  Reply with Quote
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure TEST, Line 44
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


how can i solve this problem?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  08:32:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cengaver

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure TEST, Line 44
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


how can i solve this problem?


Where is the file 'c:\raporlar.xls' located?
It should be in Server's directory. Also check the sheet name and use it inside the procedure


Madhivanan

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

cengaver
Starting Member

30 Posts

Posted - 07/14/2009 :  10:50:44  Show Profile  Reply with Quote
how can look at the sheet name?
i dont where is it ?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/14/2009 :  11:21:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cengaver

how can look at the sheet name?
i dont where is it ?


You have referred the Excel file with the path 'c:\raporlar.xls' . Do you have access to view it?. If so, look at what the sheet name is and specify that name in the procedure

Madhivanan

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

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 07/14/2009 :  12:37:32  Show Profile  Reply with Quote
quote:
Originally posted by cengaver

how can look at the sheet name?
i dont where is it ?



The sheet name is on a tab on the bottom left of the excel file. By default, there will be three; Sheet1, Sheet2, and Sheet3. Just open Excel, and click file, open. In the filename box, type 'c:\raporlar.xls' without the quotes. Click open. If it opens, then the file exists, and you can see the sheetnames in little white tabs on the bottom left. note the name one the first one to the left. If the file doesn't open, you'll get an error. Hit escape. Click File, Save As. in the filename box type 'c:\raporlar.xls' without the quotes. Click save. Close excel. Run your procedure, and substitute the name of the first sheet follwed by $, for sheet1$, if it wasn't sheet1.

----------------
-Stephen
Go to Top of Page

cengaver
Starting Member

30 Posts

Posted - 07/15/2009 :  10:41:47  Show Profile  Reply with Quote
thank you very much for your helps

i understood that problem but errors not finish.
i couldnt solve and understand just this error
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

i configure linkserver but it still continue.. :(

actually i dont want to any more disturb you but i cant do it
and i believe that i can just solve it thanks to you.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 07/15/2009 :  11:20:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cengaver

thank you very much for your helps

i understood that problem but errors not finish.
i couldnt solve and understand just this error
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

i configure linkserver but it still continue.. :(

actually i dont want to any more disturb you but i cant do it
and i believe that i can just solve it thanks to you.


It means that the sheet does not have column names
Check if the sheet has column names in the first row

If you are not sure about the column names, try altering the procedure like below


ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN

SET NOCOUNT ON;
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

SET @server = 'ar-ge'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\raporlar.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog



INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\raporlar.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
SELECT * FROM personal

DECLARE @body VARCHAR(1024)
SET @body = 'dosyalar '+
CONVERT(VARCHAR, GETDATE())

EXEC omerDB..xp_sendmail
@recipients='omer_yalcin1985@hotmail.com',
@message = 'dosyalar' ,
@subject = 'dosyalar',
@attachment = 'c:\raporlar.xls'

exec xp_cmdshell 'del C:\raporlar.xls'

END


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