replace the string in sql server query output

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"