replace the string in sql server query output

Welcome Forums General PowerShell Q&A replace the string in sql server query output

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 7 years ago.

  • Author
  • #6088

    Points: 0
    Rank: Member

    by vmusunoor at 2013-05-06 08:26:17


    i would like to replace the string in sql server output...I'm trying to pull database files information from a database...

    Here's my script...




    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    $SqlConnection.ConnectionString = "Server = $ClientName; Database = '$DESTINATIONDB'; Integrated Security = True"

    $SqlCmd.CommandText = "select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and = 'master'"

    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    foreach ($Row in $Dataset.Tables[0].Rows)

    $dev = $($Row[0])


    if you execute this ...with parameters as
    $clientname = sqlserver instance name
    $DestinationDB = database name ( ofcourse i have hard coded master database in the above sql query )

    You will get an output like below...

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Now, i would like get the output with my destination db name..for example
    if i give the parameters as
    $clientname = testserver
    $DestinationDB = Test

    output should be like

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf
    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testlog.ldf

    basically it must replace the master data and log file name.

    Please let me know your suggestions..

    Thanks in advance..

    by DonJ at 2013-05-06 12:00:56

    Use the -replace operator.

    $string -replace "this","that"

The topic ‘replace the string in sql server query output’ is closed to new replies.