SQL 2008R2 T-SQL need help with OPENROWSET insert into a Excel file

[DougP]

getting error on yellow hightlight below:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'

Alter Procedure sp_SOWExportToExcel
AS
Declare @File as nvarchar(300)
Declare @sqlString as nvarchar(max)
Declare @sqlString1 as nvarchar(max)
set @File= 'd:\apps\SOWTimeReportingExcelExports\SOWTimeReporting.xls;'
set @sqlString1 ='Select Vendor,ResourceLastName,ResourceFirstName,
	 ResourceType, OffshoreOnShore, [Year], 
	 [Month], [Day], SPMID, CostTracker, ADActivityCode as ADA,
	  HoursWorked as Hours, ApprovedBy, 
	  Convert(nvarchar(15),ApprovedDate,101) as ApprovedDate, 
	  SOWtr.[CostCenter] from dbo.SOWTimeReporting SOWtr 
	  Where [Month] in ( 1,  2,  3,  4) And [Year] = 2013
	   Order by [Month] DESC, ResourceLastName, ResourceFirstName, [Day]'
	   
	SELECT @sqlString = 'INSERT INTO OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File +', ' + '' + @sqlString1 + ''[highlight #FCE94F]')'[/highlight]
   EXEC (@sqlString)
	   
 -- EXEC sp_SOWExportToExcel

DougP

 

[MarkSweetland]

Missing some single quotes maybe?

@File + ''',''' + '' + @sqlString1

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

I think I know what's going on here....

When inserting into an open rowset, the general syntax is like this:

INSERT 
INTO    [!]OPENROWSET([/!]
          'Microsoft.ACE.OLEDB.12.0', 
          'Excel 12.0;Database=Excel File Here;', 
          [Sheet1$][!])[/!]
Select   Columns
From     table

Note that Sheet1$ is the name of the sheet you want to export the data to (followed by a $).

Try this:

Declare @File as nvarchar(300)
Declare @sqlString as nvarchar(max)
Declare @sqlString1 as nvarchar(max)
set @File= 'd:\apps\SOWTimeReportingExcelExports\SOWTimeReporting.xls;'
set @sqlString1 ='Select Vendor,ResourceLastName,ResourceFirstName,
	 ResourceType, OffshoreOnShore, [Year], 
	 [Month], [Day], SPMID, CostTracker, ADActivityCode as ADA,
	  HoursWorked as Hours, ApprovedBy, 
	  Convert(nvarchar(15),ApprovedDate,101) as ApprovedDate, 
	  SOWtr.[CostCenter] from dbo.SOWTimeReporting SOWtr 
	  Where [Month] in ( 1,  2,  3,  4) And [Year] = 2013
	   Order by [Month] DESC, ResourceLastName, ResourceFirstName, [Day]'
	   
	SELECT @sqlString = 'INSERT INTO OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File +''', [Sheet1$]) ' + @sqlString1
   Exec (@sqlString)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Well I guess that worked I am now getting an error, which I need to see if our SQL admin can get worked out or not.

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.

Thanks though.


DougP

 

[gmmastros]

The T-SQL to enable ad hoc distributed queries:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

I'm not 100% sure what permissions are needed, but if you have a DBA, then he/she will probably need to run this.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Got this from admin,

"The GPO will not let us set that long term.
Technically, we could configure the SQL Server but it will only be temporary – the GPO will get applied within a week"

so thanks though.
on to plan "B" using codeplex per the admins instructions...

http://excelpackage.codeplex.com/documentation

DougP

 

[DougP]

Can I automre this some how?
step 1: Run a query and it returns results in SSMS grid
, such as

Select Vendor,ResourceLastName,ResourceFirstName,
ResourceType, OffshoreOnShore, [Year],
[Month], [Day], SPMID, CostTracker, ADActivityCode as ADA,
HoursWorked as Hours, ApprovedBy,
Convert(nvarchar(15),ApprovedDate,101) as ApprovedDate,
SOWtr.[CostCenter] from dbo.SOWTimeReporting SOWtr
Where [Month] in ( 1, 2, 3, 4) And [Year] = 2013
Order by [Month] DESC, ResourceLastName, ResourceFirstName, [Day]'

step 2: save that to .CSV file somehow using T-SQL
step 3: email just created .CSV file to a user, where the email addr. just passed to the stored procedure.

run all of the above in a Stored Procedure.


DougP