Author Posts

June 6, 2014 at 12:12 pm

$date = Get-Date -Format MM.d.yyyy
$OutputDir = "C:\Reports\SQL"
$File = "Sql_" + "$date" + ".csv"
$OutputFile = Join-Path $OutputDir $file

if(!(Test-Path -Path $OutputDir))
{mkdir $OutputDir}
else {continue}

#SQl Data—————————————————————————————————————–
#Connection Strings
$Database = "db1"
$Server = "server11"

# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "DECLARE @PreviousDay Date, @toDay Date
SET @PreviousDay = GETDATE() – 1
SET @toDay = GETDATE()
PRINT @PreviousDay
PRINT @toDay

SELECT Phone1

FROM db1.dbo.Lead (NOLOCK)
WHERE ClientrelationshipId IN (4963,118, 4964, 5109)
AND CreatedDate >= @PreviousDay
AND CreatedDate < @toDay ORDER BY LeadId " $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $nRecs = $SqlAdapter.Fill($DataSet) $nRecs | Out-Null #Populate Hash Table $objTable = $DataSet.Tables[0] #Export Hash Table to CSV File $objTable |Export-CSV $OutputFile -NoTypeInformation #SQl Data—————————————————————————————————————– #ftp server upload——————————————————————————————————– $Dir= $OutputFile $ftp = "ftp://mysite.com/dir" $user = "user" $pass = "password" $webclient = New-Object System.Net.WebClient $webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) #list every sql server trace file foreach($item in (dir $Dir)){ "Uploading $item..." $uri = New-Object System.Uri($ftp+$item.Name) $webclient.UploadFile($uri, $item.FullName) } #ftp server upload——————————————————————————————————–

June 6, 2014 at 12:15 pm

When ran a portion at a time everything executes perfectly! when ran all at once i get nothing no error no out at all

June 6, 2014 at 12:15 pm

So... What's it do? Error messages? Different behavior? We can't easily run your script so understanding what's happening would be useful in helping you.

June 6, 2014 at 12:19 pm

nothing no errors nothing it just completes. when I high light portions and run in the ISE works fine. connects to sql grabs query and exports data to csv and uploads. when ran a scheduled task, or the whole script at once it just complete with no output at all. I would like to make this a scheduled job

June 6, 2014 at 12:21 pm

it just returns back to the prompt no erros

June 6, 2014 at 2:54 pm

That would be because you have


if(!(Test-Path -Path $OutputDir))
{mkdir $OutputDir}
else {continue}

Continue starts a new iteration of the current loop. An if statement is not a loop, so in your case, when you're running it as a script, continue exits the script.

What you want is to do away with the else { continue } part entirely. It's not necessary.

June 8, 2014 at 5:49 pm

Well Done Martin! your advise was correct and now the script runs!
I was using the continue the wrong way
Thank you