header1   header
header
header Register : : Login header
header
connector   connector
menuleft menuright
submenu   submenu
left
Query SQL for 2 tables then merge info into Get-QADUser csv
Last Post 30 Aug 2010 03:52 PM by Chad Miller. 1 Replies.
Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
MG2User is Offline
New Member
New Member
Posts:1
Avatar

--
30 Aug 2010 09:47 AM

    Hello,
    I have two issues. In SQL we have active and inactive customer numbers associated with a code. In AD we have only active customer numbers with address information. My goal is to export the customer number and address information from AD then somehow merge that data with the SQL code information for active stores only. ie: Get-qaduser customer number etc.. then, if the number exists in the sql database, get the code.
    1) How to query SQL for customer number and code to a text or csv file?
    2) Once I have the SQL text file, how can merge the data into my foreach statement block?

    Thank you,
    MG2

    Chad MillerUser is Offline
    Basic Member
    Basic Member
    Posts:160
    Avatar

    --
    30 Aug 2010 03:52 PM
    You may want to consider an alternate approach of importing the AD information into SQL Server. One way to do so:

    Create a table to hold AD data in SQL Server:
    Create table qaduser_fill
    (
    samid varchar(255),
    customerNum varchar(255)
    )
    #Get AD info:
    get-qaduser | select NTAccountName, customerNumber | export-csv -noTypeInfo ./qaduser.csv

    #Cleanup CSV file for import into SQL Server by removing double quotes
    (Get-Content C:\Users\Public\qaduser.csvv) | foreach {$_ -replace '"'} | Set-Content C:\Users\Public\qaduser.csv

    Define query to import data:
    $query = @"
    BULK INSERT mydatabaseName.dbo.qaduser_fill FROM 'C:\Users\Public\qaduser.csv'
    WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
    "@

    Execute query using Invoke-SqlCmd or Invoke-Sqlcmd2: http://poshcode.org/1791

    Invoke-SqlCmd2 -ServerInstance "WIN2K8R2\SQL2K8" -Database mydatabaseName-Query $query

    Use a standard SQL join to join the data:

    SELECT *
    FROM mydatabaseName.dbo.OrginalInfo o
    JOIN qaduser_fill q ON
    o.id = q.samid

    You could use Invoke-Sqlcmd or Invoke-Sqlcmd2 to call the query in PowerShell



    You are not authorized to post a reply.


    Active Forums 4.3
    right
    footer   footer
    footer Sponsored by Quest Software • SAPIEN Technologies • Compellent • Microsoft Windows Server 2008 R2 footer
    footer   footer