Is there anyway I can run a weekly automated report for all the carts on our audiowall or the SQL command to query the autotrack DB to export to a excel/txt file to give all cart numbers artist title info, either way would work.  This would benefit our presenters when creating their playlists when working from home when having access to their OneDrive's.

I know I can do this manually in Autotrack but wanted this to be automated and current as new tracks are added on a weekly basis.


Many thanks
Shane

HI Shane,

Off the top of my head I can't think of a way of doing it directly with a command line, but yes a SQL query would work.

You'd probably want something like the below, if you can load this into Excel or something then you could format thecolumns nicely and script it to run possibly using VBA macros (though I'll be honest and say I don't know how to do that part!)

SELECT   SongTitles.ItemTitle,Songs.HDReference, Artists.ArtistName, Songs.Year, Songs.LastEditedDate, Songs.ChartPeak, SongCategories.Description, Songs.Extro,
         Songs.OriginallyAddedDate, Songs.DisplayBy, Songs.DisplayTitle
FROM    Songs INNER JOIN
         SongTitles ON Songs.TitleNumber = SongTitles.TitleNumber LEFT OUTER JOIN
         SongCategories ON Songs.ItemNumber = SongCategories.ItemNumber LEFT OUTER JOIN
         SongTypes ON Songs.ItemNumber = SongTypes.ItemNumber LEFT OUTER JOIN
         Artists ON Songs.ArtistNumber1 = Artists.ArtistNumber


------------
Peter Jarrett, Technical Director
Broadcast Radio Ltd.

Bill Bailey: No win, no fee, no basis in reality. Just a room above a minicab office in Acton and a steady stream of greedy simpletons whose delusion is only matched by their clumsiness

Thanks for letting me know, I should be able to schedule this via SQLCMD.

Got it running in Task Manager to export to CSV for all Myriad songs in the AutoTrack Database on a weekly basis.  I'm not a good at scripting but it does the job.  This uses Powershell with Invoke-SQLDCMD

Remove-Item "H:\Myriad.csv"
(Deletes the previous file)
Start-Sleep -m 10000
(Waits 10 seconds)
$DBServer = "localhost\your instance name"
$database = "Your Database name"
$DBScriptFile = "As provided in this post.sql"
$OutputFile = "H:\Myriad.csv"
Invoke-Sqlcmd -InputFile $DBScriptFile -Serverinstance $DBServer -Database "$database" | Export-Csv -Path $OutputFile -NoTypeInformation


This script is then scheduled in Task Manager.