The script runs but gives me "no data to export" I see data in the $query, but seems like not in:
if ($items.Count -gt 0)
THanks.
# Clear the console window.
Clear-Host
# Database variables.
$server = "srv1"
$database = "ItemCost"
# SQL query to select all data in the person table.
$query = "SELECT * FROM [dbo].[item_dim] where itemid = '11-371517-582-3-02' ORDER BY [itemid]"
# Export path and file.
$exportPath = "C:\Demo\"
$exportXml = "Itemexport.xml"
# Check to see if the file path exists.
if (Test-Path $exportPath)
{
# Extract the data.
$items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop
try
{
# If data has been returned, do the export.
if ($items.Count -gt 0)
{
# Construct the full file path.
$fullFilePath = $exportPath + $exportXml
# Create the XML file.
$xmlWriter = New-Object System.XMl.XmlTextWriter($fullFilePath,$Null)
# Set the formatting for the document.
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$XmlWriter.IndentChar = "`t"
# Add the declaration for the document.
$xmlWriter.WriteStartDocument()
# Add the root element.
$xmlWriter.WriteStartElement('items')
# Process the rows of data.
foreach ($item in $items)
{
# Open the person element.
$xmlWriter.WriteStartElement('item')
# Add the person details.
$xmlWriter.WriteElementString('itemid', $item.itemid)
$xmlWriter.WriteElementString('itemdesc', $item.itemdesc)
$xmlWriter.WriteElementString('itempline', $item.itempline)
$xmlWriter.WriteElementString('itemplinedesc', $item.itemplinedesc)
$xmlWriter.WriteElementString('itemcategory', $item.itemcategory)
# Close the person element.
$xmlWriter.WriteEndElement()
}
# Close the root XML element.
$xmlWriter.WriteEndDocument()
# Flush the internal buffer.
$xmlWriter.Flush()
# Close the XML document.
$xmlWriter.Close()
# Today's date.
$today = Get-Date
# Construct the backup file name.
$exportBackupXml = $exportXml.Substring(0, $exportXml.Length-4) + "-" + `
[int]$today.DayOfWeek + "-" + `
$today.DayOfWeek.ToString().ToLower() + ".xml"
# Check if the backup file does not exist, or if it does, check that
# today's date is different from the last modified date.
if (-not (Test-Path ($exportPath + $exportBackupXml)) -or `
((Test-Path ($exportPath + $exportBackupXml)) -and `
((Get-Item ($exportPath + $exportBackupXml)).LastWriteTime.date -ne $today.Date)))
{
# Copy the XML export.
Copy-Item ($exportPath + $exportXml) `
-Destination ($exportPath + $exportBackupXml) -Force
}
# Message stating export successful.
Write-Host "Data export successful."
}
else
{
# Message stating no data to export.
Write-Host "There is no data to export."
}
}
catch
{
# Message stating export unsuccessful.
Write-Host "Data export unsuccessful."
}
}
else
{
# Message stating file path does not exist.
Write-Host "File path does not exist."
}
August 5, 2021 at 2:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 5, 2021 at 11:27 am
Tried that but seems like the query from SQL returns no records, but if I use that query in SSMS data returns.
?
Thanks.
August 5, 2021 at 11:32 am
have you tried adding a debug to it
$items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop
$items |out-file "c:\sql.log"
and also try
$items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop -As DataSet
$items |out-file "c:\sql.log"
August 5, 2021 at 12:23 pm
I have the one record captured in $items |out-file "c:\sql.log"
Debug:
$items.rows.Count
# If data has been returned, do the export.
if ($items.rows.Count -gt 0)
$items.rows.Count shows 0
Message:
0
There is no data to export.
Thanks.
August 5, 2021 at 12:29 pm
This was removed by the editor as SPAM
August 7, 2021 at 12:29 am
Anything else I can do for Debug...
August 9, 2021 at 3:06 pm
Does the code look correct?
THanks.
August 17, 2021 at 11:52 pm
anything else I can try to see why script not working?
THx
August 18, 2021 at 8:49 am
What do you get if you have Write-Host $items after the invoke-sql ? Also, change the "SELECT *" to use the actual columns that you need.
Another thing you could try is something like
foreach ($item in $items)
{ $item.<columnname> etc.}
..just to see if anything is actually getting put into the $items object.
So a debug script might look something like
$server = "YOUR SERVER"
$database = "YOUR DATABASE"
# SQL query to select all data in the person table.
$query = "SELECT !!!!COLUMN NAMES!!!! FROM [dbo].[item_dim] where itemid = '11-371517-582-3-02' ORDER BY [itemid]"
$items = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop
Write-Host $items
foreach ($item in $items)
{Write-Host $item.<column> ..... other columns
}
August 18, 2021 at 12:03 pm
This debug script is returning data as expected.
Thanks.
August 20, 2021 at 11:26 pm
Any other things I can try to figure out why script isn't working?
Thanks.
September 18, 2021 at 11:59 pm
any ideas why script not working?
Thx.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply