October 16, 2018 at 6:03 am
I need to run the report (generally last month but it can vary) against 15-20 databases on monthly basis and need to save/export a file with a delimiter of | (aka pipe) and with headers”. How this can be implemented using Powershell or maybe using some script/job?
Note; We can't enable xp_cmdshell due to some security reason.
Script:
SELECT * FROM Summary with(nolock)WHERE DATEPART(m, CreatedDateTime) = DATEPART(m, DATEADD(m, -1, getdate()))AND DATEPART(yyyy, CreatedDateTime) = DATEPART(yyyy, DATEADD(m, -1, getdate()))order by CreatedDateTime
October 16, 2018 at 1:22 pm
Use SSIS and schedule a job to run it.
October 16, 2018 at 1:39 pm
You'll also get better performance from your query using a date range instead of a function on a column. Try something like this to query the data for last month:
SELECT *
FROM Summary
WHERE CreatedDateTime >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
AND CreatedDateTime < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
ORDER BY CreatedDateTime;
Hope this helps.
October 16, 2018 at 1:54 pm
Joe Torre - Tuesday, October 16, 2018 1:22 PMUse SSIS and schedule a job to run it.
Thanks for your response. But, how can i schdule through poweshell? Here is the sample script for 1 db. I have 10-15 different db's with the same tbale name. So, i will put together in one batch file as shows below and i just need to change createddatetime between '20180101' and '20180901.
How can i dynamically change script through poweshell?
sqlcmd -S testserver -d dynamodb 1 -Q "set nocount on; select 'SwitchID', 'ContactID', 'ContactType', 'ContactTypeString', 'CreatedDateTime', 'CreatedReason', 'CreatedReasonString', 'CreatedContactGroupID', 'CreatedAddressID', 'Duration', 'ReleasedReason', 'ReleasedReasonString', 'ReleasedDateTime', 'OriginatorAddress', 'OriginatorName', 'ReceivingAddress', 'RedirectAddress', 'NumTimesInWorkflow', 'TimeInWorkflow', 'NumTimesRouted', 'TimeInRouting', 'NumTimesInPAQ', 'TimeInPAQ', 'NumTimesOnOutbound', 'TimeOnOutbound', 'NumTimesHandledByAgent', 'TimeHandledByAgent', 'NumTimesQueued', 'NumTimesReturned', 'OriginalQueueID', 'OriginalQueueName', 'NumTimesHandledFromQueue', 'TotalTimeQueuedHandled', 'NumTimesAbandonedFromQueue', 'TotalTimeQueuedAbandoned', 'NumTimesRemovedFromQueue', 'TotalTimeQueuedRemoved', 'NumTimesSetUserData', 'NumTimesActionCompleted', 'OriginalHandledQueueID', 'OriginalHandledQueueName', 'OriginalHandlingAgentID', 'OriginalHandlingAgentName', 'OriginalHandlingAgentSkillScore', 'OriginalOutboundContactGroupID', 'OriginalOutboundAddressID', 'OriginalOutboundNumber', 'OriginalRoutedAddressID', 'OriginalRoutedResult', 'OriginalRoutedResultString', 'OriginalRoutedReason', 'OriginalRoutedReasonString', 'OriginalRoutedDestination', 'OriginalSetUserData', 'LastSetUserData', 'OriginalLoggedActionWfID', 'OriginalLoggedActionPageID', 'OriginalLoggedActionActionID', 'OriginalLoggedActionDuration', 'OriginalLoggedActionName', 'OriginalLoggedActionData', 'OriginalLoggedActionResult', 'LastLoggedActionWfID', 'LastLoggedActionPageID', 'LastLoggedActionActionID', 'LastLoggedActionDuration', 'LastLoggedActionName', 'LastLoggedActionData', 'LastLoggedActionResult'; select * from Summary with (NOLOCK) where createddatetime between '20180101' and '20180901'" -h-1 -W -w 999 -s "|" -o s:\Idynambodb.txt
October 17, 2018 at 1:37 pm
EasyBoy - Tuesday, October 16, 2018 1:54 PMJoe Torre - Tuesday, October 16, 2018 1:22 PMUse SSIS and schedule a job to run it.Thanks for your response. But, how can i schdule through poweshell? Here is the sample script for 1 db. I have 10-15 different db's with the same tbale name. So, i will put together in one batch file as shows below and i just need to change createddatetime between '20180101' and '20180901.
How can i dynamically change script through poweshell?
You would likely do it differently if you wanted to use Powershell. You can iterate through the databases pretty easily in Powershell instead of dynamically changing a sqlcmd script to run against each database. Seems to be a bit on the ugly side to just have this batch file and dynamically changing it with Powershell.
An option with Powershell if you wanted to go that route is to create a table for your results and just dump the results from each database into that table. Iterating through the databases on the server is just a matter of getting all of the database names from sys.databases. A couple of them I have to do something similar would just be along the lines of: Import-Module SQLSERVER
# $databases is the databases from your instance
$databases = invoke-sqlcmd -ServerInstance YourInstance -Query "select name from sys.databases"
foreach ($database in $databases)
{
#Your query for each database
#insert into Database.schema.tablename select....
}
Sue
October 17, 2018 at 2:03 pm
Sue_H - Wednesday, October 17, 2018 1:37 PMEasyBoy - Tuesday, October 16, 2018 1:54 PMJoe Torre - Tuesday, October 16, 2018 1:22 PMUse SSIS and schedule a job to run it.Thanks for your response. But, how can i schdule through poweshell? Here is the sample script for 1 db. I have 10-15 different db's with the same tbale name. So, i will put together in one batch file as shows below and i just need to change createddatetime between '20180101' and '20180901.
How can i dynamically change script through poweshell?You would likely do it differently if you wanted to use Powershell. You can iterate through the databases pretty easily in Powershell instead of dynamically changing a sqlcmd script to run against each database. Seems to be a bit on the ugly side to just have this batch file and dynamically changing it with Powershell.
An option with Powershell if you wanted to go that route is to create a table for your results and just dump the results from each database into that table. Iterating through the databases on the server is just a matter of getting all of the database names from sys.databases. A couple of them I have to do something similar would just be along the lines of:Import-Module SQLSERVER# $databases is the databases from your instance
$databases = invoke-sqlcmd -ServerInstance YourInstance -Query "select name from sys.databases"foreach ($database in $databases)
{
#Your query for each database
#insert into Database.schema.tablename select....
}Sue
But. how do i save the result from each db with separate .txt file?
Please review the code again and let me know for any correction.
i.e db1.txt,db2.txt
Import-Module SQLSERVER
# $databases is the databases from your instance
$databases = invoke-sqlcmd -ServerInstance YourInstance -Query "select name from sys.databases"
foreach ($database in $databases)
{
#SELECT *
FROM Summary
WHERE CreatedDateTime >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
AND CreatedDateTime < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
ORDER BY CreatedDateTime;
#insert into Database.schema.tablename select....
}
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply