Replication :: Powershell Not Scripting All Options Specified

Oct 2, 2015

I would get from scripting the replication out from SQL Server itself. However, there are a couple of things that are not showing up in the script,Adding the option to IncludeUninstallDistributor or UninstallDistributor doesn't appear to add any code to actually do that. i.e. the below code is never added.

/****** Uninstalling the server as a Distributor. Script Date: 10/1/2015 4:34:13 PM ******/
use master
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO
Also, adding the reverse for the create script generation, IncludeInstallDistributor or InstallDistributor, does not add the below code.
use master
exec sp_adddistributor @distributor = N'xxxxxx', @password = N''
GO

The purpose of the script is simply to script out all drop/create scripts for all publications and subscriptions, run the drop script, restore over the databases using backups of non-replicated databases, run the create replication script, and have it re-sync.

param ([Parameter(Position=0, Mandatory=$true)] [ValidateNotNullOrEmpty()] [string] $Publisher,
[Parameter(Position=1, Mandatory=$true)] [ValidateNotNullOrEmpty()] [string] $Directory,
[Parameter(Position=2, Mandatory=$false)] [switch] $Create = $true,
[Parameter(Position=3, Mandatory=$false)] [switch] $Drop)

[code]....

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Scripting Options Alter Element?

Aug 11, 2015

Is there a way to (automatically) remove/disable the first statements like SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON which are generated by modify sp via mms 2014 interface?

--
SET ANSI_NULLS ON
--
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_SendMail] @test INT = 0
AS
begin
--blabla
end

View 1 Replies View Related

Automated Replication Scripting

Feb 22, 2007

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

View 5 Replies View Related

Sql Express Options With Replication ?

Aug 9, 2007

I am considering using a local database for a desktop application, instead of relying on remote connections. This will require replication I suppose to sync up table changes etc. What are my options when using Sql express on the client end and Sql server (standard edition) ?

After looking at this
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Now, Im wondering how do I get local changes merged back into the primary database ?

View 1 Replies View Related

Looking For 2000 &&> 2005 Replication Options.

Aug 8, 2007

I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.

I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).

I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.

UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard.
UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.

View 3 Replies View Related

Data Scripting Tool ( NOT DATABASE Scripting)

Mar 16, 2004

Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....

if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.

Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..

Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.

by to all

View 1 Replies View Related

SQL 2012 :: Sorting In Powershell

Aug 20, 2015

i am using powershell to get the output..I am planning implement sorting to my output.below is the format I am writing my objects to html file.I want sort the object of lastrunoutocome field.

foreach($item in $sqlServer.JobServer.Jobs)
{
Write-Host $sqlserver $item.name $item.OwnerLoginName $item.IsEnabled $item.LastRunDate $item.LastRunOutcome
writeDiskInfo $ServiceFileName $sqlServer $item.name $item.OwnerLoginName
}

View 2 Replies View Related

Powershell Sql Server Performance

Mar 31, 2008

Hi,
I am trying to get performance data using powershell. When I run the command "get-wmiobject -list" I get classes like
Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks. However when I run the command

get-wmiobject -query "select * from Win32_PerfRawData_MSSQLSQLEXPRESS1_MSSQLSQLEXPRESS1Locks" powershell gives me an error. Why is this happening? any help in this regard is appreciated.

Regards
Sourashis

View 2 Replies View Related

SQL Server 2014 :: Using Powershell Commands Within Scripts

Mar 15, 2015

I would like to use powershell commands within my TSQL scripts. how to do that?

The reason is that up until now I have been using SQLCMD and XP_CMDSHELL to rename files before importing them into a database. Powershell is a better tool for this job.

View 9 Replies View Related

SQL 2012 :: How To Insert Powershell Output Into A Table

Mar 20, 2015

I'd like to know how to get windows events script below into a SQL Server Table.

Get-WinEvent -LogName application -MaxEvents 200 | where {$_.LevelDisplayName -eq "Error"}

View 1 Replies View Related

SQL Server 2008 :: Create A Table Using Powershell

Apr 28, 2015

I'm trying to create a table using powershell..following is my syntax

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=.sqlexpress; Initial Catalog=testdata1; Integrated Security=SSPI")
$conn.Open()
$db= $srv.Databases.Item("TestData1")
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "companytable")
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"CompanyName", [Microsoft.SqlServer.Management.Smo.DataType]::NChar(50))

[code]...

View 1 Replies View Related

SQL 2012 :: Named Pipes Error From PowerShell

Jun 15, 2015

1) I can connect to SQL Server 2012 named instance from SSMS.

I can launch Power shell in any of the supported data contexts, for example from the named instance.

However when I try to run a query in Power Shell, I get the named pipes error.

2)I can also provide a connection string and connect to the SQL Server Instance form Windows PowerShell after launching SQLPS, the same as in the above. I can open the connection and verify its state, but trying to run a query results in the same error.

I also see that named pipes is enabled in the SQL Server Configuration Manager.

Some of this is the result of some ambiguity in the Windows User Account in Windows 8 computer. Some of this is documented in more detail here: [URL] .....

This link is not directly related to the above problem, but arises out of the ambiguity in the Username retrieval: [URL] ....

View 0 Replies View Related

SQL 2012 :: Powershell Command To Install Specific KB

Jul 1, 2015

My system is windows 2012 core, no gui installed..I need to install missing kb through powershell. Need the powershell command to install specific KB

View 1 Replies View Related

Transact SQL :: Multiple Queries On Various Database Using Powershell

Oct 19, 2015

As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.

I am looking to automate the process, like a single click to execute all the .SQL files.

The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.

My design for this requirement is, say each .SQL file need to contain a template like

@DBName = 'your Database name'
@Executeon = 'When to execute'

In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.

Is it feasible ? Or any other alternate easier way to proceed.

View 3 Replies View Related

Transact SQL :: Move Files From One Location To Another Using Powershell

Jul 12, 2015

I have a job which copies .txt files 24 hours 7 days a week to c:TempSource

What I am planning to do is copy the files from one location to another say c:TempTarget

So I have written the Powell shell script and when i put that in the sql agent job i get below error ;

A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.

$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder } 

View 12 Replies View Related

SQL Server 2008 :: Restore Number Of Databases Using Powershell

Aug 15, 2012

I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....

The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.

View 5 Replies View Related

SQL Server 2008 :: SSMS Truncating PowerShell Output?

Jun 3, 2015

When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).

EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.

Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?

View 6 Replies View Related

SQL Server Admin 2014 :: PowerShell Security Error

Jul 16, 2015

I have a SQL Server 2014 instance running on a SQL Server 2008 R2 server. The server is not clustered, it is just a stand alone SQL Server. The syspolicy_ purge_history job fails every now and then with the error message: "A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access to the path 'PowerShell_CommandAnalysis_Lock' is denied. '. Process Exit Code -1. The step failed."

Google isn't bringing up much besides the whole"If this is a clustered server make sure you have the right server name in the command" answer, which isn't the case here. Some days this job fails and some days it succeeds. I have checked out task scheduler to see if there were any conflicts there, found nothing. Nothing in the event viewer either.

View 2 Replies View Related

SQL Server 2014 :: Error Using PowerShell Script For Restore Job

Sep 29, 2015

I'm trying to create a restore job for restore job in sql using PowerShell but I get the following error:

The job script encountered the following errors. These errors did not stop the script:

A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Set-location "filelocation"'.

Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'filelocation' because it does not exist.

' A job step received an error at line 3 in a PowerShell script. The corresponding line is 'get-childitem -Filter '*.trn' | Select FullName, LastWriteTime | sort LastWriteTime -Descending | ft -AutoSize'.

Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot call method. The provider does not support the use of filters. '. Process Exit Code -1. The step failed.

View 1 Replies View Related

Capture Messages Window Into Log File Using Powershell Program

Jun 25, 2014

As we know there are two output windows in sql server management studio like 'Results' and 'Messages', I want to capture that 'Messages' window into a log file using powershell program . like into a log file it should display like :

ex:-
(1 row(s) affected)
(1 row(s) affected)

View 5 Replies View Related

Transact SQL :: Update Query In Powershell From Excel Sheet

May 12, 2015

I am trying to update my SQL table using an update query in powershell from an excel sheet.

The query is as follows,

#building name from excelsheet
 $TCell=$reader.GetValue(8);
#update query is as,
 $CreateScript1= "UPDATE $DestinationTable SET $DestinationTable.Phone = '$SCell' WHERE RoomNumber = '$FCell' and buildingname
like ''$TCell'%'"

I want to use like operator in the query to compare building name from excel sheet with the building name in sql table. I am facing an error in the highlighted part. I am not sure if my query is right or wrong.

View 2 Replies View Related

Transact SQL :: Powershell For Pass Comma Separated List

Sep 8, 2015

I have a requirement, we need to pass comma separated list using powershell script.

How can we achieve the above scenario?

View 3 Replies View Related

Reporting Services :: Configuring SSRS 2014 Using Powershell

Jul 9, 2015

I am trying to automate the setup of SQL server 2014 Standard edition with SSRS. To avoid SSRS being setup using the default port 80 for the webservice and report manager url's, I've chosen to install SQL server with the setting RSINSTALLMODE="FilesOnlyMode" and then use powershell to do the final setup steps.To setup the ReportServer database, I use the "GenerateDatabaseCreationScript" method available in the "MSReportServer_ Configuration Setting" class.

This succeeds without problems however when I use my browser to connect to the web service or report manager url's, I get a "rsInvalid Report Server Database" and a "rsReportServerDatabaseUnavailable " error, respectively.Running the stored procedure named GetDbVersion in the ReportServer database returns "C.0.9.45". If I delete the ReportServer database and then recreate it database manually with the Report Services Configuration manager, the same stored procedure returns "162".

So it seems to me there's a problem with the GenerateDatabaseCreationScript but I can't find any information on this. How can I automate the setup of SSRS and ensure it does not use port 80? There are many manual solutions involving multi-step GUI solutions but the key for me is to automate this with powershell which doesn't appear to be possible.

View 3 Replies View Related

SQL Server 2008 :: Powershell Script To Move Backup Files

Feb 11, 2015

I am trying to create a job using power-shell script to move the backup files from one folder to another. I am using Ola Hallengren script for backups. Ola hallengren created a common backup folder with sub-folders for databases and even more sub folders for Full and Log backups. My goal is to move full backups, which are older than a month and save them in a different drive along with the same folder structure. I was able to move the first set of backups without any problem, but I can't move anymore files and keep getting this error even when I try to overwrite the previous file with the force statement:

Move-Item : Cannot create a file when that file already exists.

At line:5 char:9
+ Move-Item $i.FullName C:Test -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (C:BackupVALIDATIONgcommon:DirectoryInfo) [Move-Item], I
+ FullyQualifiedErrorId : MoveDirectoryItemIOError,Microsoft.PowerShell.Commands.MoveItemCommand

Here's the script that I used to move the first set of files:

foreach ($i in Get-ChildItem C:BackupVALIDATION)
{
if ($i.CreationTime -lt ($(Get-Date).AddMonths(-1)))
{
Move-Item $i.FullName C:Test
}
}

View 0 Replies View Related

SQL 2012 :: Powershell Script To Verify Showplan Enabled For User?

May 18, 2015

I am looking for a sample PowerShell script that allows me to verify that showplan is enabled for a user on a SQL Server 2012 instance. Haven't figured out how to code it.

View 2 Replies View Related

Integration Services :: Deploy SSIS Packages 2012 And Run Job Through Powershell

Jun 1, 2015

Any script  in ssis 2012 packages deployment  and create the jobs though power shell script.

View 3 Replies View Related

Reporting Services :: Powershell Script To Configure SSRS 2012?

Jun 12, 2015

We have 15-20 report server environments including Dev/SIT/UAT/PROD/DR etc. so instead of configuring it on each servers, we would like a script to use with parameters.

After installing the SSRS 2012 package on each server with given instance and service account, I would like to configure the SSRS through script instead on running the Configuration manager RSConfigTool.exe manually.

we already have created the database beforehand using the scripts provided by WMI. do we need db_owner permission on msdb and master database to configure SSRS?

View 2 Replies View Related

DB Engine :: Agent Jobs Status For Multiple Servers Using Powershell?

Oct 14, 2014

I am following website link:

[URL]

I require to gather status details about all the SQL Agent jobs in the environment on multiple SQL Servers.

I tried to edit the script using:

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$sqlServerName = 'localhostdeveloper'
 $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName)
 foreach($job in $sqlServer.JobServer.Jobs)
{
$job | select Name, OwnerLoginName, IsEnabled, LastRunDate, LastRunOutcome, DateCReated, DateLastModified
}

but SQL Agent jobs are not reflecting in the mail output...

View 11 Replies View Related

Integration Services :: Failure Run PowerShell Script SSIS Project

Aug 20, 2015

Add-Type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$ssisApplication = New-Object "Microsoft.SqlServer.Dts.Runtime.Application"
$ssisPackagePath = "c:users estdocumentsvisual studio 2012ProjectsSAP$SharePointSAP$SharePointPackage.dtsx"
$ssisPackage = $ssisApplication.LoadPackage($ssisPackagePath,$null)
$ssisPackage.Execute()

In case of command execution from project PowerShell SSIS, writes only Failure to what there can be a problem? From VS it is launched without problems.

View 2 Replies View Related

Transact SQL :: Server Is Cutting Off Result-output Running A Powershell Script

May 7, 2015

I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:

CREATE TABLE #DrvLetter (
iid int identity(1,1) primary key
,Laufwerk char(500),
)
INSERT INTO #DrvLetter
EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"'
select * from #DrvLetter
SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):
 
[code]....

View 2 Replies View Related

Integration Services :: SharePoint SSRS Subscription Execution From SSIS Using Powershell

Oct 26, 2015

We have been looking for a way to executing bursting of SSRS reports within SharePoint (integration mode) from a SSIS package after a successful load. We found this MSDN article which has a PowerShell script to "fire" a subscription.URL....One of the parameter needed is the SubscriptionID.

View 4 Replies View Related

Setup And Upgrade :: Use Powershell To Output Database Names On Old Server On Text File?

May 28, 2015

I've been asked to create a new SQL server and restore an old server's DBs to the new server.

I'd like to generate a list of the DB names using powershell to distribute to their creators, in case some of these DBs are no longer needed.

View 2 Replies View Related

Integration Services :: Execute Process Task / Passing In Variables From SSIS To Powershell Script

May 15, 2015

I am using SSIS 2012 to dynamically backup stored procedures on a list of Servers and Databases.Here are the steps in my package,

1. Execute SQL Task: Captures a result set (configured to save the data set in an Object variable) with all the Servers and Databases on which stored procedures exist.

2. For each loop that is configured to get each each row(server name @[User::Server_Name] and databases name @[User::DataBase_Name]) from the object variable (@[User::Connection_Strings])and pass it to a connection manager that has an expression for servername
and database name.

2a)  Within the for each loop, i have an execute process task that is configured as

 i) Executable:  C:WindowsSystem32WindowsPowerShellv1.0powershell.exe
 ii) Arguments:  Configured this to fetch value from an expression. The expression i am using is,'C:batch - CopyPowerShell Scripts to Backup Stored ProceduresScriptOutSPs.ps1' -$Server_Name "+ @[User::Server_Name]+ " -$Database_Name "+ @[User::DataBase_Name]
             
Note:  @[User::Server_Name] is the Servername from object variable and so is @[User::DataBase_Name] for database name . The execute task is to run a command line that triggers a powershell script with parameters. Here is the powershell script that i am using,

param([String]$Server_Name,[String]$Database_Name)
$Server = $Server_Name
$Database = $Database_Name
$savePath = "SalesDepartmentsData ScienceUsersSANDEEP PStoredProcedures_Backup"

[code]...

When i execute the script, by passing parameters from arguments, it executes successfully but nothing happens. Passing wrong arguments in the expression?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved