Creating SQLServer Jobs in Enterprise Manager

I would like to create a job in Enterprise manager that runs sp_who2
and write the output to a file. The filename would need to be appended
with a timestamp that makes the file unique. I can then write another
job that will cleanup the directory by deleting files that are older
than a specified date. This is a very simple process in unix platform.
I am hoping that it can be done in Windows but I don't know how?

Any help will be applicicated..You can send the output of a job step directly to a file (see the
Advanced tab), but there's no easy way to set the file name
dynamically. You might be able to use sp_update_jobstep to set it at
runtime, but then you would still have to do the cleanup part, which
would mean using xp_cmdshell.

Personally, I would use a script in a langauge like VBScript, Perl or
Python to do this:

1. Work out the correct filename
2. Call osql.exe to get the output into the file (see the -Q and -o
options)
3. Clean up any old files

You can then call the script from a job step - it's much easier to
manipulate files outside the database.

Simon

Creating Output in columns instead of Rows

Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

Right now my output would look like this:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date

(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

I have seen an example of PIVOT, but could not get that to work.

Any suggestions?

Thanks!select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling|||Thanks for the reply. Guess I should not have made my example so simple!

The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

So the "field" Yr-Mnth is not a table field, but created through the select statement.

The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.

In this example, the output would have 13 columns of demand data, 1 listed for each month.

As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
order by
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

(I don't have to use (+) in my table joins as there will always be a match)

Guess placing the data into columns instead of rows is not as simple as I had hoped!?!|||well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

Let me know Monday if you have not figured it out, I am leaving the office.|||If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 ....
where ...
group by col1, col2

Creating mailing label using database

Hi!

I have these tables:

DB1
MID
IIN
Title
FullName
Address
Apt
City
Province
PostalCode

DB2
MID
Lang
Version

How can I output a text file formatted like the one below?

(05) 01046 (F 05)
Mr. Sylvain Cote
123 Sesame Street
Apt 6
Charny, BC
L8T 5G6

where
(05)-MID
01046-IIN
(F 05) - LangConcatenate the various address components, separated by carriage return characters.|||Could you please post a sample code?

Thanks.|||select Title + ' ' + FullName + char(13)
+ Address + Char(13)
+ 'Apt ' + Apt + char(13)
+ City + ', ' + Province + char(13)
+ PostalCode + Char(13)
from YourTable

Creating Excel Output for Mac Excel 2004

I am experiencing issues with SQL Server Reporting Services 2000 when
rendering Excel (.xls) output on a Mac. The Mac is running Excel 2004 for
the Mac and appears to download the .xls file successfully, but just hangs
when attempting to open the file.
All other reporting services formats seem to render fine (html, pdf, .csv,
etc) though the HTML that gets rendered in Firefox or Safari doesn't look the
same nor nearly as nice as in IE, but it does render.
Are there service packs, hot fixes, or the like for either SSRS 2000 or
Excel 2004 for the Mac? Or is this just a case where that version of Excel
on the Mac isn't supported by SSRS 2000?
--
PaulHelo Paul,
Yes, this is a known issue in Reporting Services 2000 and Reporting
Services 2005.
For Reporting Services 2000, the MAC development group confirmed the
problem was in the .XLS file itself rendered from SQL Reporting Services.
Apparently there is certain information in the XLS file that is read by the
Mac version but not the PC version and it is this information that was
causing the file to not open or hang. The bug has been reported to the SQL
Reporting Services Product group, but I haven't been able to find a fix as
yet.
I'll include the VB code below which may helpful for you.
'Sample Program to automate process of opening and resaving XLS files
rendered by
Reporting Services and then email the file as an attachment to specified
address
locations
'This sample program was designed to run from the same location that the
XLS files
are rendered to
'Sample Command Line:
' c:>Project1.exe book1.xls
Private Sub Form_Load()
Me.Visible = False
pXLSFile = GetCommandLine()
ReSaveXLS (pXLSFile)
CreateEmail (pXLSFile)
Unload Me
End Sub
Private Sub ReSaveXLS(pXLSFile)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
'Start new instance of Excel using Automation:
Set xlApp = CreateObject("Excel.Application")
'Optional: Use if want to see Excel and let user close it:
xlApp.Visible = False
xlApp.UserControl = False
'Open the workbook that contains the data and macro:
Set xlBook = xlApp.Workbooks.Open(App.Path & "\" & pXLSFile)
'Save changes to the workbook:
'This line may need to be changed to call the SaveAs method.
xlBook.Save
'Quit Excel:
xlApp.Quit
'Release object variables:
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Private Sub CreateEmail(pXLSFile)
'from Q286431
' Send by connecting to port 25 of the SMTP server.
Dim iMsg
Dim iConf
Dim Flds
Dim strHTML
'Const cdoSendUsingPort = 25
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
With Flds
'.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =cdoSendUsingPort
'ToDo: Enter name or IP address of remote SMTP server.
'The value from below should be the same as what is located
RSReportServer.config between the <SMTPServer> tags
Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.unistudios.com" '<remote SMTP server>
Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
) =10
Update
End With
' Build HTML for message body.
strHTML = "&

---

"
strHTML = strHTML & ""
strHTML = strHTML & ""
strHTML = strHTML & " This is the test HTML message body"
strHTML = strHTML & ""
strHTML = strHTML & "

---

"
' Apply the settings to the message.
With iMsg
Set .Configuration = iConf
To = "matthofa@.microsoft.com" 'ToDo: Enter a valid email address.
From = "matthofa@.microsoft.com" 'ToDo: Enter a valid email address.
Subject = "This is a test CDOSYS message."
HTMLBody = strHTML
AddAttachment (App.Path & "\" & pXLSFile)
Send
End With
' Clean up variables.
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
End Sub
Function GetCommandLine(Optional MaxArgs)
'Declare variables.
Dim C, CmdLine, CmdLnLen, InArg, I, NumArgs
'See if MaxArgs was provided.
If IsMissing(MaxArgs) Then MaxArgs = 10
'Make array of the correct size.
ReDim ArgArray(MaxArgs)
NumArgs = 0: InArg = False
'Get command line arguments.
CmdLine = Command()
CmdLnLen = Len(CmdLine)
'Go thru command line one character
'at a time.
For I = 1 To CmdLnLen
C = Mid(CmdLine, I, 1)
'Test for space or tab.
If (C <> " " And C <> vbTab) Then
'Neither space nor tab.
'Test if already in argument.
If Not InArg Then
'New argument begins.
'Test for too many arguments.
If NumArgs = MaxArgs Then Exit For
NumArgs = NumArgs + 1
InArg = True
End If
'Concatenate character to current argument.
ArgArray(NumArgs) = ArgArray(NumArgs) & C
Else
'Found a space or tab.
'Set InArg flag to False.
InArg = False
End If
Next I
'Resize array just enough to hold arguments.
ReDim Preserve ArgArray(NumArgs)
'Return Array in Function name.
GetCommandLine = ArgArray(1)
End Function
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Creating Error Output for Custom Components

Hi,

I have a 2 custom components - source and destination.

I want to create an error output for each, to allow the users of my component to handle errors the way they choose.

I only found a property in IDTSOuptut90 named isErrorOut - a boolean property indicating whether this output is an error output or not.

Does anyone have additional documentation / articles / code samples regarding how to really populate the rows in the error output?

Thanks

You add the output in ProvideComponentProperties, as others, and that property is pretty much the key-

// Error Output
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = ErrorOutput;
outError.Description = "Error output for rows that caused an unexpected error.";
outError.SynchronousInputID = inp.ID;
outError.ExclusionGroup = 1;
outError.IsErrorOut = true;

In ProcessInput you would then direct rows to thje normal output or the error output.

int iError = ComponentMetaData.OutputCollection[ErrorOutput].ID;
buffer.DirectErrorRow(iError, 0, buffer.CurrentRow);

I don’t believe there are no samples in the recent batch on Microsoft.com/downloads/ and from memory I’m sure the Source sample that ships with the product includes error output usage.