Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mpreissner
Starting Member
21 Posts |
 Posted - 2010-12-14 : 10:49:44
|
| Let me start by saying that all my SQL knowledge was gained by working with a NetIQ AppManager system, so I'm still a bit of a novice.I've written a query that extracts information from two tables, putting it into human-readable format. The query is now saved as a stored procedure called EventReport:select dateadd(ss, a.lastoccurtime, '1970-01-01 00:00:00')-.208333 as OccurTime, a.machinename, a.occurrence, a.eventid, a.kpname, a.eventmsg, b.agentmsgshort, b.agentmsglongfrom event a, eventdetail bwhere a.lastoccurtime between(select datediff(s, '1970-01-01 00:00:00', @windowstart)) and(select datediff(s, '1970-01-01 00:00:00', @windowend))and a.eventid=b.eventidorder by occurtimeDue to security concerns, our NOC technicians (who will be utilizing the above query) cannot have direct access to SQL via SMS. I'd like to create a .bat program that will allow them to set the @windowstart and @windowend variables (sets time period for record set) and pass those variables to the remote SQL server.Here's where I run into the problem...the AgentMsgLong field doesn't write to a .log or .txt or .csv file in a way that is easily importable into Excel. I can use an SSIS package to get things to look right, but I can't figure out how to pass the user-defined variables to the package. I'm thinking running dtexec from command line with the right switches should do the trick, but I can't figure out the correct syntax.In summary, I need to create a batch file that allows a user to set the @windowstart and @windowend variables, then remotely execute an SSIS package using those variables for the stored procedure the package calls in its query. I'd also like to direct the resulting .xls file to the user's desktop, but if I can't manage that, I can direct it to an FTP folder on the remote server and the users can retrieve it from there. |
|
|
|
|
|
|
|
Trouble with SSIS package...