Help in replacing the deprecated SP_MAKEWEBTASK

Question

question

DaniSQL asked Jul 14, '10

Help in replacing the deprecated SP_MAKEWEBTASK

Hi All, I have Excute SQL task that excutes the below query as part of bigger SQL Server 2005 SSIS package: EXEC SP_MAKEWEBTASK 'C:\test.xls', @QUERY = 'SELECT * FROM [ServerA].[DB1].dbo.table1 where order_no in (select order_no from [ServerA].[DB1].dbo.order_detail where subsystem = ''INV'' and product_code like (''CPI%IND'') and fulfill_date sql-server-2008 sql-server-2005 ssis t-sql deprecated

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden commented · Nov 30, 2013 at 07:00 AM

@DaniSql I know this is an old post but you said "I am thinking to use BCP but I am not happy about enabling xpcmdshell." My question to you is "Why Not"? Who is going to use it? ONLY those with "SA" privs can use it unless someone were foolish enough to grant individuals the privs to use it directly. Turn it on and leave it on. Then do those things that should have been done to protect the server. Turning xp_CmdShell off offers zero protection against a hacker that gets in with "SA" privs and still can't be used by one that gets in with less than "SA" privs.

0 ·

Jeff Moden commented · Dec 02, 2013 at 07:36 AM

I'll also add that although it's a really good answer, the "accepted" answer uses OPENROWSET, which requires the same privs as xp_CmdShell... "SA". You can do just as much damage with OPENROWSET as you can with xp_CmdShell. With a tweak to the registry (which you can easily do with xp_RegWrite and I certainly won't demonstrate), you can even invoke CMD with it.

0 ·

Answer 1 · 2010-07-14T15:04:58Z

Oleg answered Jul 14, '10

This is pretty straightforward with T-SQL. For example, lets say I have Excel file with 3 columns: FullName, Nationality, AccountNumber. The worksheet name is Customer\_Account\_Details and the file name is Sample.xls located in C:\\Useless\\Temp folder. -- select all data from the Excel file described above -- (path is relative to the server): select * from openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Useless\Temp\sample.xls', [Customer_Account_Details$] ); -- This shows results: FullName Nationality AccountNumber --------------- ----------- ---------------------- Bozo the Clown USA 1 John Doe USA 2 Someone Else UK 3 -- Lets insert one record: insert into openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Useless\Temp\sample.xls', 'select * from [Customer_Account_Details$]' ) select 'Oleg', 'USA', 4; -- Now lets select data again by running the first select -- this will result in: FullName Nationality AccountNumber --------------- ----------- ---------------------- Bozo the Clown USA 1 John Doe USA 2 Someone Else UK 3 Oleg USA 4 For sanity check you can open Excel file and see that the new record is there. Please note that the worksheet name followed by the dollar sign is what makes the "table name" valid, i.e. worksheet named **Sheet 1** will result in the "table name" **[Sheet 1$]**. If you have multiple worksheets in the file then it is like having multiple tables in the database, you can join the sheets if they are related, update data in one sheet based on the results of such join etc. One thing I would like to add: If the original Excel file is already full of records then it does not really make much sense to bring them over while inserting new record(s). Therefore, it might make sense to include the predicate in the third parameter of the openrowset used in the insert statement to ensure that no data is actually returned, just the column definitions, i.e. insert staments can be restated like this: insert into openrowset ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Useless\Temp\sample.xls', 'select * from [Customer_Account_Details$] where 1 = 2' ) select 'Oleg', 'USA', 4; That **where 1 = 2** will ensure that no needless traveling takes place. Oleg

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark commented · Jul 14, 2010 at 03:10 PM

Thanks Oleg, I'd like to try OPENROWSET.

0 ·

DaniSQL commented · Jul 15, 2010 at 07:29 AM

Thanks Oleg. I was aware of openrowset for excel but i wasn't sure about the implementation, i will try it later.

0 ·

DaniSQL commented · Jul 16, 2010 at 08:50 AM

Oleg: this is a handy trick but unfortunately I couldn't make it work for me. I need to create the excel file and know the columns beforehand too to use openrowset which is not applicable in my case. The reason I didn't use SSIS in the first place is the output of the query varies from time to time and even though i can create the table first and there is no way to know how many rows it the query returns. So far BCP is the only way:-)

0 ·

Oleg commented · Jul 16, 2010 at 09:21 AM

@DaniSQL You can still try to combine Pavel's suggestion and my script. Suppose you need to insert some data of arbitrary shape into Excel. So, query the shape of the data (dummy SQL to return just the column headers but not the data, i.e. select ... where 1 = 2). You can then use CLR proc or sp_OA... if CLR is not enabled to create a blank Excel File with just the column headers. Once this is done, use openrowset to insert your data. With CLR, things are much easier though because you don't even have to use anything Office specific. For example, if you have select 'FirstName' + char(9) + 'LastName' + char(9) etc then you can use System.IO from CLR proc to simply insert one line into a brand new flat file which you happen to name with .xls extension. From then on, you can use plain T-SQL to insert data into aforementioned file.

0 ·

DaniSQL commented · Jul 16, 2010 at 11:53 AM

Thanks Oleg!!

0 ·

Show more comments

Answer 2 · 2010-07-14T14:04:01Z

Kev Riley answered Jul 14, '10

I'm by no means certain about this, but how's about trying Powershell. I know you can create xls with Powershell, and you can sure as heck query SQL, so why not..... Dang I wish I knew more about Powershell now!!!

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Jul 15, 2010 at 04:52 AM

No time like the present to start. I'd suggest Don Jones book, PowerShell, TFM, Version 2.0.

2 ·

DaniSQL commented · Jul 15, 2010 at 07:24 AM

@Grant...I will get Don Jones book and Read TFM :-) BTW I was looking his recent article "PowerShell IS NOT HARD. Read this. I'll prove it." and I am encouraged to dive into POSH.

2 ·

DaniSQL commented · Jul 15, 2010 at 07:21 AM

@Kev: I dont know PowerShell either, but it should be easy from what I hear about the power of POSH

0 ·

Answer 3 · 2010-07-15T23:37:51Z

Pavel Pawlowski answered Jul 15, '10

+1 to Oleg, but Oleg solution requires, that the excel file exists. If you need create a new excel file and output queries into it using pure T-SQL, you can use OLE Objects using the `sp_OA....` stored procedures. You can find a complete example how to generate an excel file using the OLE objects here [ http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx][1] The example uses OLE ADO to create an excel file and sheets inside it. Then it creates a linked server for data output. Instead of creating a linked server you can use the OPENROWSET as Oleg stated here. [1]: http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Jul 16, 2010 at 08:01 AM

@Pavel Pawlowski +1 Thank you for pointing out the flaw in my answer. I was sure that this was inevitable, as it is obvious that T-SQL solution does indeed require the file to already exist and its shape to be known to T-SQL (so insert works as expected). Addition of the file creation part (either with sp_OA... or perhaps with CLR proc if CLR is enabled) makes T-SQL approach complete.

0 ·

DaniSQL commented · Jul 16, 2010 at 08:55 AM

@Pavel, Thanks for the link to the script. its a very nice script but the script assumes I know what columns I want beforehand when i create the table; which I dont. See my comment to Oleg above.

0 ·

Pavel Pawlowski commented · Jul 17, 2010 at 01:50 AM

@DaniSQL, this is not a problem, you know what columns are in the table. The script for creating table in the example is constructed dynamically and there is no problem to put the columns from the system table sys.columns. `SELECT STUFF((select ', ' + C.name from sys.columns C where object_id = OBJECT_ID('dbo.Table_1') FOR XML PATH('')), 1,1, '')` to list columns with data types included for create script join the sys.types table `SELECT STUFF((select ', ' + C.name + ' ' + T.name from sys.columns C inner join sys.types T ON C.system_type_id = T.system_type_id where object_id = OBJECT_ID('dbo.Table_1') FOR XML PATH('')), 1,1, '')` You only substitute the SQL types by the excel types for the creation script.

0 ·

Answer 4 · 2011-12-13T04:24:14Z

Lonely Rogue answered Dec 13, '11

@Oleg Your INSERT INTO ROWSET has worked fine, with only caution that the XL file along with the table structure has to be defined before we execute this query. Thanks. -- In 'thoughts'... Lonely Rogue.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2013-01-31T05:43:19Z

moghthalkushal answered Jan 31, '13

Any ideas how to align the text ? , when you export to XL , it is automatically right alligned , i want it to be left aligned , how can it be done , from the query itself

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Jan 31, 2013 at 08:59 AM

How text appears in Excel is handled by Excel. You can't control that from the query. You can probably set Excel properties if you use Powershell - however I would suggest asking that as a separate question here rather than tagging on to this thread.

1 ·

Answer 6 · 2013-11-21T09:10:04Z

anandsinghh answered Nov 21, '13

But i am using for createing HTML file with help of TPL file. USE pubs GO EXECUTE sp_makewebtask @outputfile = 'C:\WEB\ MULTIPLE.HTM', @query = 'SELECT title, price FROM titles SELECT au_lname, au_fname FROM authors SELECT pub_id, pub_name FROM publishers SELECT au_lname, au_fname FROM authors', @templatefile = 'C:\WEB\MULTIPLE.TPL', @dbname = 'pubs', @rowcnt = 5, @whentype = 9 GO

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 7 · 2013-11-21T09:10:33Z

anandsinghh answered Nov 21, '13

Any Alternate code for below query, because sp_makewebtask is not available in sql 2008 Pl help USE pubs GO EXECUTE sp_makewebtask @outputfile = 'C:\WEB\ MULTIPLE.HTM', @query = 'SELECT title, price FROM titles SELECT au_lname, au_fname FROM authors SELECT pub_id, pub_name FROM publishers SELECT au_lname, au_fname FROM authors', @templatefile = 'C:\WEB\MULTIPLE.TPL', @dbname = 'pubs', @rowcnt = 5, @whentype = 9 GO

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question