Questions tagged [smo]

SMO stands for SQL Management Objects (and is technically called "Shared" Management Objecs by at least one installer).

Filter by
Sorted by
Tagged with
2
votes
0answers
23 views

How to set STATISTICS_INCREMENTAL option ON for an index in SQL Server using c#?

I need help with programmatically setting the STATISTICS_INCREMENTAL option ON for an index in a table in SQL Server (2016 SP2). I am using Microsoft.SqlServer.Smo, Version=13.0.0.0. I do not see any ...
1
vote
0answers
24 views

Add signature to stored procedure using PowerShell SMO

I want to add a signature to a stored procedure using PowerShell SMO. The equivalent command in TSQL is: ADD SIGNATURE TO [myprocedure] BY CERTIFICATE [mycertificate] with password = 'CertificatePW' ...
2
votes
0answers
61 views

SQLSMO Column Dependencies

I'm trying to help a teammate walk object dependencies in SQL Server. I've had some success using things like sys.sql_expression_dependencies (and other related DMV/DMFs) but it's falling short due to ...
2
votes
3answers
646 views

How can the System databases in SQL Server 2016 be moved using Powershell?

I'm trying to move the system databases using Powershell ie. without using any T-SQL. Using SMO : Install-Module SQL-SMO $smo = New-SMO -ServerName localhost $smo.databases["TempDB"]....
9
votes
2answers
1k views

SMO, SSMS are slow for management of SQL Server in Docker when connecting to localhost

TL;DR: When connecting to my SQL Server Docker container via a name that resolves to the IPv6 loopback (::1), SMO calls are really slow. When using 127.0.0.1, they're fast. I am trying to learn how ...
3
votes
1answer
5k views

Missing assembly when running PowerShell script using SMO

I wrote a script which takes database name, restored DB name, backup source, data file location and log file location as parameters and performs database restore on SQL Server instance. Script works ...
7
votes
1answer
185 views

Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016. With earlier ...
3
votes
2answers
234 views

Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten?

I deploy multiple SQL Server jobs to a fleet of servers using a PowerShell script. I can create the job steps, success fail action, and even the output file. This all works on multiple servers. What I ...
7
votes
2answers
1k views

Why can't C# SMO see extended properties on a column but Powershell SMO can?

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended ...
1
vote
1answer
639 views

TSQL Solution to backup ALL databases on ALL instances of a server?

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
6
votes
2answers
946 views

Are cursors/While loops the only way to make administrative changes to multiple databases?

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in ...
3
votes
2answers
2k views

Handle expired SQL login's passwords in SQL Server

Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming? Many times during working hours I got this message from a production ...
4
votes
1answer
137 views

How long does it take for powershell to restore a SQL Backup?

I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name: $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)") $...
2
votes
1answer
392 views

SMO Copy database or remove all non-system database objects

I have c# SMO script which copy all database objects to existed empty database or to a new database. But this approach has two problems Existed database is not empty, manual intervention is needed. ...
1
vote
1answer
3k views

Trying to use SMO Transfer Object to transfer a database from one instance to another

The schema tables are owned by dbo and another schema name. The code below is not moving over any tables. Is there something else that I need to add to make it transfer the tables? There are no stored ...
2
votes
1answer
1k views

Script Table Definition in Powershell

I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*. I'm having problems adding the columns and the clustered column store index. Here's ...
13
votes
6answers
40k views

What is the most effective way to discover all running instances of SQL Server using PowerShell?

I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell ...
2
votes
1answer
379 views

Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service

I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the ...
3
votes
1answer
858 views

Why can't I script out CLR assemblies with SMO in Powershell?

I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most ...
3
votes
1answer
13k views

Cannot read property Collation.This property is not available on SQL Server 7.0

I'm not able to add administrator role in SQL Server 2008 R2. When I try to add new user it throws me the following error: Cannot read property Collation.This property is not available on SQL ...
3
votes
2answers
10k views

Using SQL Server SMO with a Port number

In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient....
1
vote
1answer
582 views

Why SMO's Transfer tries to login into linked server when scripting?

Consider this code sample: var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword"; var serverConnection = ...
0
votes
1answer
3k views

What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#?

How can I do the following with C#: Completely backup an existing database. Partially backup an existing database. Completely restore an existing database from a backup file. Partially restore an ...
2
votes
2answers
5k views

Shrinking log files with SMO

I know I can use the following t-SQL to shrink a log file: BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn'; DBCC ShrinkFile([db1_log], 0); How do I do the same with SMO? I tried: $server = ...
6
votes
1answer
980 views

Why am I missing some output?

Archiving to avoid ID Exhaustion We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
5
votes
2answers
1k views

SQL Server SMO and PowerShell formatting

I have probably been looking at this for too long to figure this out... What is the easiest way I could get this exported to a CSV format? Would it be better to dump this to a table in a database ...
4
votes
1answer
2k views

SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output ...
5
votes
2answers
2k views

Can I Run Two Full Backups on SQL Server 2005 at the Same Time?

Running SQL Server Express 2005 here. We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET). We have separate processes ...
6
votes
4answers
6k views

Script Contents of SQL Server Database From a Batch File

I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio ...
6
votes
1answer
847 views

SQL Server SMO Method “Discover()”

I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instance of SQL Server. I found a script ...
0
votes
2answers
631 views

Is command BACKUP asynchronous operation

I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location. Is it possible that multiple backup operations are running at the same time? I have ...