How to exclude replication stored procedures from a script?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Programming » Powershell » How to exclude replication stored procedures...

Add to briefcase

18 posts, Page 2 of 2

««1 2

How to exclude replication stored procedures from a script?

Expand / Collapse

Author

Message

Posted Tuesday, June 26, 2012 7:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 6,724, Visits: 11,769

schleep (6/26/2012)

This will exclude all non-user objects. I assume they're probably not required anyway.

Get-ChildItem | where {$_.IsSystemObject -eq $false}

That brings up a good point. I have replication enabled in my DB and see the repl procs under System Stored Procedures in SSMS but when I run just Get-ChildItem (i.e. without any filtering) at the StoredProcedures prompt in PS none of the repl procs are returned, in fact no system procs are returned, I only see my user-defined stored procedures.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321247

Posted Tuesday, June 26, 2012 9:15 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 344, Visits: 602

$Srv = New-object Microsoft.SqlServer.Management.SMO.Server "Mufasa"
$Srv.Databases["Abb"].StoredProcedures | where {$_.issystemobject -eq $true} | select name

This returns 1300+ system procs -- including repls, and I don't have replication enabled.

P

Post #1321313

Posted Tuesday, June 26, 2012 9:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 6,724, Visits: 11,769

From the OP's code, modified for my machine for demo:

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

Set-Location SQLSERVER:\SQL\MyComputer\MyInstanceName\Databases\MyDataseName\StoredProcedures

Get-ChildItem | Select Name

I only receive a list of user-defined procs.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321353

Posted Tuesday, June 26, 2012 10:58 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 344, Visits: 602

Confirmed.

Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProcedures
dir | select name

does not return any system sprocs. I wonder why the repl sprocs would fall into the user category.

Weird...

P

Post #1321404

Posted Tuesday, June 26, 2012 11:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 6,724, Visits: 11,769

schleep (6/26/2012)

Confirmed.

Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProcedures
dir | select name

does not return any system sprocs. I wonder why the repl sprocs would fall into the user category.

Weird...

P

Indeed. This was quite an entertaining exercise nonetheless Tongue

Tongue

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321416

Posted Tuesday, June 26, 2012 11:12 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 344, Visits: 602

PS: Is your picture Robert Hays in Airplane?

Post #1321422

Posted Tuesday, June 26, 2012 11:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 6,724, Visits: 11,769

Yes, it is a picture of Robert Hays as Ted Striker. I am not sure if you can see the detail in the picture but it's from the scene when he's trying to land the plane and the sweat is pouring off him in buckets.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321440

Posted Tuesday, June 26, 2012 11:43 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 344, Visits: 602

I remember it well. Hilarious!

Post #1321441

« Prev Topic | Next Topic »

Add to briefcase

18 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse