question

DaniSQL avatar image
DaniSQL asked

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-2008sql-server-2005ssist-sqldeprecated
2 comments
10 |1200

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

Jeff Moden avatar image Jeff Moden commented ·
@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 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
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 Likes 0 ·
Oleg avatar image
Oleg answered
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 comments
10 |1200

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

Mark avatar image Mark commented ·
Thanks Oleg, I'd like to try OPENROWSET.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Oleg. I was aware of openrowset for excel but i wasn't sure about the implementation, i will try it later.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
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 Likes 0 ·
Oleg avatar image Oleg commented ·
@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 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Oleg!!
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
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 comments
10 |1200

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

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
No time like the present to start. I'd suggest Don Jones book, PowerShell, TFM, Version 2.0.
2 Likes 2 ·
DaniSQL avatar image DaniSQL commented ·
@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 Likes 2 ·
DaniSQL avatar image DaniSQL commented ·
@Kev: I dont know PowerShell either, but it should be easy from what I hear about the power of POSH
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
+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 comments
10 |1200

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

Oleg avatar image Oleg commented ·
@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 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@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 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
@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 Likes 0 ·
Lonely Rogue avatar image
Lonely Rogue answered
@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

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

moghthalkushal avatar image
moghthalkushal answered
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 comment
10 |1200

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

Kev Riley avatar image Kev Riley ♦♦ commented ·
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 Like 1 ·
anandsinghh avatar image
anandsinghh answered
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

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

anandsinghh avatar image
anandsinghh answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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