replace the string in sql server query output

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 4 months ago.

  • Author
    Posts
  • #6088

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

    Hi,

    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...

    [code2=powershell]param
    (

    $ClientName,
    $DESTINATIONDB

    )

    $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
    d.name = 'master'"

    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null
    foreach ($Row in $Dataset.Tables[0].Rows)
    {

    $dev = $($Row[0])
    $dev

    }[/code2]

    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"

You must be logged in to reply to this topic.