SQLServerCentral / Programming / SMO/RMO/DMO

Problems connecting to SQL Server 2012 using 2005 SMO

ken_england — Tue, 23 Apr 2013 23:37:43 GMT

Should 2005 SMO be able to access SQL 2012 please?We are currently performing a large migration to SQL Server 2012. It's been going well, but we have hit a problem testing an app. An app server is throwing an exception "This SQL Server version (11.0) is not supported". On further investigation an internal library (used potentially in a number of places) is found to be using Microsoft.SqlServer.ConnectionInfo 9.0.242.0.Testing seems to show that referencing a later library works. Is this expected behaviour that 2005 SMO library can't access SQL 2012? What are our options - the dev teams are nervous of installing a later version and changing this internal library to reference it (although tests have shown this works). Any simple ways around it? regards

C# => EnumJobHistory() not working on SQL-Server 2008 SP1 (10.00.2531) with smo-dll 11.0.0.0 and 10.0.0.0

christoph.meyer — Sat, 20 Apr 2013 12:40:36 GMT

Hi there,i am having problems enumerating the jobhistory for SQL-Server 2008 SP1 (10.00.2531).On SQL-Server 2008 R2 and 2012, everything works fine, and the function returns a DataTable-Object. Connected to SQL-Server 2008 SP1, the function call "hangs" and does not response anymore... till i kill the process manually.Any hints on that?

SMO.Scripter: Help Walking a DependencyCollection

zlthomps — Wed, 14 Nov 2012 13:37:41 GMT

Hi there I am working on automating the process of scripting creates for our SQL Views and object dependencies of the views (namely functions in this case). I don't care about table dependencies. I was able to easily script the objects not in dependency order, but I am struggling with walking the dependency tree in my code.Here is where I am so far. You can see in the last loop (blank) that I am struggling. Would someone be so kind as to point me in the appropriate direction?[code="plain"] //Trying to Connect to Source Database and Server Server SrcServerCon = new Server(SrcServer); SrcServerCon.ConnectionContext.LoginSecure = Convert.ToBoolean(isWindowsAuth); try { if (SrcServerCon.ConnectionContext.LoginSecure == false) { SrcServerCon.ConnectionContext.Login = SQLAuthLogin; SrcServerCon.ConnectionContext.Password = SQLAuthPword; } } catch (Exception sqlConnect) { using (System.IO.StreamWriter exAtvws = new System.IO.StreamWriter(Logger, true)) { exAtvws.WriteLine("SQLConnect - " + DateTime.Now.ToString() + "-" + sqlConnect.Message ); } Environment.Exit(1); } Scripter vwScripter = new Scripter(SrcServerCon); //Initiating Scripter Constructor vwScripter.Options = new ScriptingOptions(); vwScripter.Options.ScriptDrops = true; vwScripter.Options.IncludeIfNotExists = true; vwScripter.Options.SchemaQualify = true; vwScripter.Options.WithDependencies = true; vwScripter.Options.NoCollation = true; //Deleteing the File if it exists in the target location if (System.IO.File.Exists(ScriptOutputLoc)) { System.IO.File.Delete(ScriptOutputLoc); } try { Database SrcDbCOn = SrcServerCon.Databases[SrcDbName]; //Adding UDFs and Vws to Urn Collection UrnCollection udfobjs = new UrnCollection(); foreach (UserDefinedFunction udf in SrcDbCOn.UserDefinedFunctions) { if (!udf.IsSystemObject) { udfobjs.Add(udf.Urn); } } foreach (View views in SrcDbCOn.Views) { if (!views.IsSystemObject) { udfobjs.Add(views.Urn); } } //Creating Dependency Tree DependencyTree dtree = vwScripter.DiscoverDependencies(udfobjs, true); DependencyWalker dwalker = new DependencyWalker(); DependencyCollection dcollect = dwalker.WalkDependencies(dtree); using (System.IO.StreamWriter FxscriptsToFile = new System.IO.StreamWriter(ScriptOutputLoc, true)) { foreach (DependencyCollectionNode dcoln in dcollect) { }[/code]

SMO compatibility error

mwhite007 — Tue, 04 Sep 2012 17:33:52 GMT

Is anyone aware of any forward compatibility bug in the SMO library?We are having an issue with a .NET client using SMO to check for create database permission. It seems to have trouble reading the 2008 R2 library when communicating with SQL 2012.

Programming the Data Conversion task in SQL Server Integration Services 2012 in C#

DdG — Thu, 19 Apr 2012 01:41:25 GMT

I am trying to adapt C# code that configures the Data Conversion task from the code used in SQL 2005 and 2008 to 2012. Many things seem to have changed, and I am unable to figure out how to correctly assign the properties for the input->output path in the Conversion task. The following code worked for 2005 and 2008:[code="other"] IDTSPath90 path = this.mainPipe.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], convComponent.InputCollection[0]); IDTSInput90 input = convComponent.InputCollection[0]; IDTSVirtualInput90 virtualInput = input.GetVirtualInput(); //Binder start og converted sammen. VirtualInput på converted er output på start foreach (ModelColumnTransformation columnTransformation in transformation.Transfers) { foreach (IDTSVirtualInputColumn90 virtualColumn in virtualInput.VirtualInputColumnCollection) { if (string.Compare(virtualColumn.Name, columnTransformation.FromColumn.Name, true) == 0 || (columnTransformation.FromColumn.Preformatted && columnTransformation.FromColumn.Name.EndsWith(" as \"" + virtualColumn.Name + "\""))) { convInstance.SetUsageType(input.ID, virtualInput, virtualColumn.LineageID, DTSUsageType.UT_READONLY); IDTSOutputColumn90 outputColumn = convComponent.OutputCollection[0].OutputColumnCollection.New(); outputColumn.Name = virtualColumn.Name + " (Converted)"; outputColumn.SetDataTypeProperties(columnTransformation.ToColumn.DataType, columnTransformation.ToColumn.Length, columnTransformation.ToColumn.Precision, columnTransformation.ToColumn.Scale, 0); outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure; IDTSCustomProperty90 outputProp = outputColumn.CustomPropertyCollection.New(); outputProp.Name = "SourceInputColumnLineageID";//I expect this line to be the problem outputProp.Value = virtualColumn.LineageID;//I expect this line to be the problem outputProp = outputColumn.CustomPropertyCollection.New(); outputProp.Name = "FastParse"; outputProp.Value = false; break; } } }[/code]Apparently, the inner workings of the LineageID property has changed, and if I look at the SSIS format, the LineageID now needs to be a "logical" string instead of an integer ID. I have been unable to figure out where to obtain this ID in the objects and assign it in the transition from input to output.

SSIS Vs Linked Server with SQL Agent Jobs

pawana.paul — Fri, 11 May 2012 08:46:58 GMT

Dear Experts,Need advice in planning. There are tables which needs to be exported to / imported from databases on different SQL instances DAILY and also want to have exception handlers or notifications of the daily run and the status (especially on failure of perticular table). Please let me know whether to go with SSIS or create a linked server and schedule Job also let me know the advantages and disadvantages over both. At the max the below steps will be involved:1. Drop the existing tables at destination2. Create Tables at destination3. Insert Data at destination.

Detach database, copy to new location, attach database

brett-585458 — Thu, 10 May 2012 08:48:49 GMT

Hello,PowerShell newbie here. I'm trying to write a PS script that will detach a database, copy the data and log files to new location, and attach database using new file location. I'm stuck on getting the current physical file names. Here is the code I have so far:[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | out-null[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | out-null$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'BRETTW7'$database = $server.Databases['Test1'];$dblogfiles = $database.LogFiles.Item(0).Name;Write-Host 1: $dblogfiles ;$fileToRename = $database.FileGroups["PRIMARY"].Files[$dblogfiles];write-host 2: $database.FileGroups.get_Item(0).FilesWrite-Host 3: $fileToRename ;Write-Host 4: $initialpath;So -- how do I get the physical file name of the data and log files?Also, does anyone have any scripts or snippets that would help me?Thank you in advance.Brett

Minimum permissions to allow access to SMO.Server.Databases.Users list

geoffrey grierson — Wed, 30 Nov 2011 09:19:21 GMT

I have a Powershell script which I would like to run using less than sysadmin permissions.It creates a New-Object Microsoft.SqlServer.Management.Smo.ServerThen uses FOREACH-OBJECT in the Databases collectionThen uses FOREACH-OBJECT in the Users collection, grabs the name and the loginThis is ok for a sysadmin account, but for an ordinary user I get an exceptionThe following exception was thrown when trying to enumerate the collection: "An exception occurred while executing a Transact-SQL statement or batch.".At line:1 char:4+ $_. <<<< Users + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException + FullyQualifiedErrorId : ExceptionInGetEnumeratorPublic server role gives access to the list of databases, is there a server setting which will give me access to the users in a database?Sorry if the question is a bit garbled; it's tired and I'm getting late. Thanks for your help.

how to call excel (2007/2010) macro in SSIS Package (2005/2008)

pankaj.baluni — Wed, 23 Nov 2011 10:48:26 GMT

Hello,My situation : - Excel 2007 and 2010MSBI - 2005 and 2008 (EE/SE)Excel based macro has been written in above mentioned versions of excel.Script Task : - marco would copy and paste data from different sheets of 3 different excel files to one sheet to make it one consolidated data.Requirement : - Need to run those macros using SSIS on the excel file.Please send me step by step process to do it. Rgds,Pankaj

Using SMO for 2000 servers

m--S3qU3L — Sun, 27 Nov 2011 03:05:06 GMT

Hi Friends,I would like to know how can I take full advantage of SMO scripting on SQL Server 2000 too.I have a working script of SMO for 2005 servers. I know that DMO is the one for 2000, but I believe SMO has a backward compatibility as well?On the other hand, I tried importing SQLDMO dll into the SSIS script task to write separate code for 2000, but SSIS could not utilize its functionality.Thanks in advance :)

SQL Server Denali SMO calls with .NET framework 3.5

hemcreddy — Sat, 22 Oct 2011 01:59:07 GMT

Hi,Does SMO expect .Netframework 4.0 to talk to SQL server Denali or 3.5 is enough?I'm trying to migrate my C#, .Net application to support SQL Server Denali. The application currently supports SQL Sever 2008 and uses .Net Framework 3.5. I'm just wondering if just replacing SMO assemblies (as per http://msdn.microsoft.com/en-us/library/dd206977%28v=sql.110%29.aspx) are enough or do I need to migrate to .Net framework 4.0 too. SQL server Denali expects 4.0 to work but SMO library does not talk about the framework to be used. When I try to remotely connect to the Denali server using framework 3.5 I get the following error."System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"Thanks for the help,Hem

sqlcmd.exe is hanging after running sql statement in inno package

WaitingWonder2 — Tue, 18 Oct 2011 08:19:29 GMT

Hello,I'm write installation package using inno for ms sql script. I have the following code:[code="other"] strParam := '-U hel -P password -S ServerName -d test -Q "sp_test"'; try Exec('sqlcmd.exe', strParam, '', SW_SHOW, ewWaitUntilTerminated, ResultCode); result := ResultCode = 0; except Exec('osql.exe', strParam, '', SW_SHOW, ewWaitUntilTerminated, ResultCode); result := ResultCode = 0; end;[/code]Sp executes ok but black screen with sqlcmd.exe is hanging until either I type exit or close it. I want a window with sqlcmd.exe closed after sp is executed.

SMO Backup event PercentCompleteEventHandler not fired

amosquera — Mon, 10 Oct 2011 02:27:21 GMT

Hi,When I run this code, the event PercentCompleteEventHandler (Backup_PercentComplete); never fired.Then event ServerMessageEventHandler(Backup_Complete) is firedwhen the backup is complete.The backup operation run Ok and without errors.Runnig with vs 2008 and sql server 2008 r2 RTMObjects SMO version 10.0.0.0Any ideas?The code:using System; using System.Data; using System.Collections; using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; class Program { static void Main(string[] args) { BackupDeviceItem bdi = new BackupDeviceItem(@"c:\temp\MyDB.bak", DeviceType.File); Backup bu = new Backup( ); bu.Database ="MyDB"; bu.Devices.Add(bdi); bu.Initialize =true; bu.PercentCompleteNotification = 10; // add percent complete and complete event handlers bu.PercentComplete += new PercentCompleteEventHandler(Backup_PercentComplete); bu.Complete += new ServerMessageEventHandler(Backup_Complete); Server server = new Server("localhost"); bu.SqlBackup(server); Console.WriteLine(Environment.NewLine + "Press any key to continue."); Console.ReadKey( ); } protected static void Backup_PercentComplete( object sender, PercentCompleteEventArgs e) { Console.WriteLine(Environment.NewLine + e.Percent + "% processed."); } protected static void Backup_Complete(object sender, ServerMessageEventArgs e) { Console.WriteLine(Environment.NewLine + e.ToString( )); }}

Using SQLCMD script in SMO

apatel903 — Thu, 18 Aug 2011 05:30:34 GMT

Hello,I have a C# program which uses SMO to submit tsql to SQL Server 2008 R2 for execution. The tsql is read in from a script file and this works fine.However if I give it a script file which contains some sqlcmd syntax I get an exception saying "Incorrect syntax near ':' .Is it the case that SMO does not support script files with sqlcmd syntax?If that is the case, please can anyone you suggest alternatives.Thank you,Anil.

Process profiler log in C#

sqlrand — Wed, 22 Jun 2011 22:34:37 GMT

I am in charge of migration DB from 2k5 to 2k8One of tests to check that all functionality works same way in 2k8 as in 2k5 is “playing” trace file log on 2k5 and 2k8 in parallel and compare result for each statement as well as end result of database.Because DB in heavy usage for period of 8 hours it generates about 140 files 50MB each. I would like to write some code which will do same what profiler does when you open trace file set some filter like DatabaseId and then using menuFile/Export/Extract SQL Server Events/Extract Transact-SQL Eventsto get script file.In my research I only found only articles how to read trace file using Microsoft.Sqlserver.Managment,Trace.TraceFile object. But in this case I have to also build logic which will figure one line out approximately four in trace fileE.g. simple call for SP creates four events- RPC Started -SP Started-SP completed-RPC completedIf any other events happened in between of those four you have process them accordingly and create separate statement in script.SQL Profiler when extracts Transact-SQL Event does all job on it is own. I would appreciate if somebody points me how I can achieve same from code (I need process a lot of trace file) using posible kow figured method of TraceFile or something esleAlternatively I can put tarce file into table using fn_trace_gettable but again will have to figure how query table to generate SQL scriptThanks!Thanks!

Script difference between two databases

thulani.moyana — Thu, 06 Nov 2008 00:48:04 GMT

I need to compare two databases and update one of them. Suppose a table "Table A" is available in both databases but some columns are missing in the other table and I want to create an update sql script to update one database.I can add columns to the database to update and capture script by changing the execution mode, but the question is:How can I clone the column from one table to the other. I need to add the column as is with all constraints(i.e.) the exact copy of the other column.Any method of scripting the differences such that the resulting script with update the other table or all objects in the other database thus resulting in exactly the same databases.Any help will be appreciated.

Schedule SQLExpress Backup using SQLDMO

k-346900 — Thu, 28 Dec 2006 14:49:00 GMT

is it possible to Schedule SQLExpress Backup using SQLDMO?SQL Express does not support SQL Agent then how can I view jobs created using SQLDMO?In management studio there is jobs option is unavailable. I have written code to add JObSchedule/job/jobStep and program is running without errors .. but no way to find out whether job is actually created and executed..

sqlparser parseresult - How do you parse?

Babu Mannaravalappil — Fri, 01 Jul 2011 10:41:28 GMT

I was looking for some sample code to figure out how to effectively use some of the classes in the Microsoft.SqlServer.Management.SqlParser namespace. But to my astonishment, after googling for two entire days, couldn't find even one single sample code that shows what to do with the ParseResult instance returned by the Parse() method. MSDN and Technet both have only the definitions for these classes and methods. But not one single example of how to use them.Here is what I am trying to do. I am trying to design an User interface (similar to the SSMS query designer) when a user can look at the query graphically (again, similar to SSMS), modify the query graphically or directly in sql text and execute it. Here is the sql for AdventureWorks View object "vEmployee" for example.SELECT e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress, c.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, c.AdditionalContactInfoFROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID INNER JOIN HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID INNER JOIN Person.Address AS a ON ea.AddressID = a.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCodeUsing SqlParser or the DependencyTree classes in SqlServer.Management namespaces, how can I parse the tokens out of the View. I need to be able to individually identify the columns, tables, join types, aliases, where clause conditions etc. I know all these information is available as tokens somewhere in either the View object or on the Database. I was able to retrieve all the columns and the dependant table names by walking dependencytree. But I can't figure out how to extract the other items. Without all the items, I won't be able to reconstruct the sql when a user graphically modifies the query. I can really use some sample code (C#, VB.NET, doesn't matter).Babu.

How to retrieve the table names from a View using SMO

Babu Mannaravalappil — Tue, 28 Jun 2011 14:26:37 GMT

Hi,I am writing a User Interface to view all the SMO.View objects and display the components involved in it. One of the issues I am having is to retrieve all the tables involved in an existing View. I can see all the columns involved. But the Column.Parent only points to the View itself. I need the names of the tables for each column including their alias if any. I can see this information in the View.TextBody. I can also see the column information by calling View.EnumColumns(). But I can't find the table information anywhere. I am sure this information is stored somewhere in the view or elsewhere on the database. Can someone help me with this please? I really appreciate it.Babu.

Microsoft.SqlServer.Management.Smo.DatabaseStatus values different from sysdatabases status column values

kiran_nittur — Mon, 30 May 2011 22:12:15 GMT

Not sure if anyone noticed this. Looks like the status bits defined in SMO object is not actually same as that stored in status column of sysdatabases. As I am using SMO i am not able to exactly figure out the status of the database when status goes to offline. I know that I can use DATABASEPROPERTYEX which gives the status in string but I would like to get the status bit value.KiranStatus bits defined in sysdatabasesstatus int Status bits, some of which can be set by using ALTER DATABASE as noted:1 = autoclose (ALTER DATABASE)4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY) 8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY) 16 = torn page detection (ALTER DATABASE) 32 = loading 64 = pre recovery 128 = recovering 256 = not recovered 512 = offline (ALTER DATABASE) 1024 = read only (ALTER DATABASE) 2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER) 4096 = single user (ALTER DATABASE) 32768 = emergency mode4194304 = autoshrink (ALTER DATABASE)1073741824 = cleanly shutdownMultiple bits can be ON at the same time Status bit defined in SMO.namespace Microsoft.SqlServer.Management.Smo{ // Summary: // The Microsoft.SqlServer.Management.Smo.DatabaseStatus enumeration is a list // of constant values that specify the status of a database, whether it be loading, // recovering, or normal, for example. This enumeration has a FlagsAttribute // attribute that allows a bitwise combination of its member values. [Flags] public enum DatabaseStatus { // Summary: // The database is available. Normal = 1, // // Summary: // The database is going through the restore process. Restoring = 2, // // Summary: // The database is waiting to go through the recovery process. RecoveryPending = 4, // // Summary: // The database is going through the recovery process. Recovering = 8, // // Summary: // The database has been marked as suspect. You will have to check the data, // and the database might have to be restored from a backup. Suspect = 16, // // Summary: // The database has been taken offline. Offline = 32, // // Summary: // The database is inaccessible. The server might be switched off or the network // connection has been interrupted. Inaccessible = 62, // // Summary: // The database is in standby mode. Standby = 64, // // Summary: // The server on which the database resides has been shut down. Shutdown = 128, // // Summary: // The database is in emergency mode. EmergencyMode = 256, // // Summary: // The database has been automatically closed. AutoClosed = 512, }}

Powershell for data transfer from non-SQL Server database

mcginn — Mon, 26 Jul 2010 16:24:57 GMT

I have a tool that uses SSIS and stored procedures to extract data from a non-SQL Server database (Sybase) and loads the data into a SQL Server database. I have upgraded to SQL Server 2008 R2 and I am in the process of upgrading to Powershell 2.0. I want to wrap this process in a Powershell script and I would like to be able to remove SSIS from the equation if there is an OLEDB type SMO object available which would allow me to connect to a Sybase database source and manage the data in much the same way as I manage SQL Server databases through the use of SMO.

SMO Transfering specific rows from specific tables?

johnzabroski — Fri, 20 May 2011 15:36:01 GMT

In many situations, I just want to grab one or two rows from a table and script out the inserts. It would be nice if SMO did this for me automatically, including handling whether to generate "set identity_insert [schema].[table_name] on/off" lines.I can't use the traditional way to dump table data:ScriptingOptions.ScriptData = trueThis option scripts out EVERYTHING. I just want one row, e.g. the row where a PK equals 0.What's the best way to do this?

SMO performance issues!

Betim Drenica — Mon, 23 Mar 2009 17:57:32 GMT

Hi guys!I've used SMO in some my latest apps but I am really disappointed with performance! It is horrible slowly!Did you felt this issues also?What do you think!

smo table script generation performance problem

kanasz.robert — Sun, 24 Apr 2011 02:25:15 GMT

Hello,I'm currently working on program for database compare. In this program I need to create scripts for databse objects. i found out that script generation is too slow but generating scripts using SSMS it's pretty fast. In one thread I have found, that SSMS uses for script generation SMO, but I couldn't find any suggestions for performance improvement. CollapseServer srv = new Server(new ServerConnection("SERVER", "USERID", "PASSWORD"));srv.SetDefaultInitFields(typeof(Table), true);Database db = srv.Databases[database];ScriptingOptions so = new ScriptingOptions();so.AllowSystemObjects = false;...db.PrefetchObjects(typeof(Table),so);...foreach(Table tb in db.Tables){ StringCollection sc = tb.Script(); . . .}Interesting is that db.PrefetchObjects(typeof(Table),so) has almost no effect for script generatino of tables, but is rapidlyincreases script generation of stored procedures.Can someone explain me, what kind of mechnizm SSMS uses for script generation, or is top secret informacion? :PRegardsRobert

ListAvailableSQLServers (SQL-DMO) deprecated

judy.keadle — Thu, 28 Apr 2011 10:34:27 GMT

I understand that ListAvailableSQLServers is deprecated, but I cannot find what to replace it with.I see that SQL-DMO is replaced with SMO, but I do not see an equivalent method. code snippet below.Assistance greatly appreciated. SQLDMO.NameList oNames; SQLDMO.Application oSQLApp = new SQLDMO.Application(); oNames = oSQLApp.ListAvailableSQLServers();

Growth of transaction log

kln2020 — Thu, 24 Feb 2011 12:07:13 GMT

someone has script to monitor a Growth of transaction log in sqlserver/or link.thank's

SMO within SSIS Script Task - User.Drop() causes error at runtime.

Scott Clark-275269 — Tue, 19 Feb 2008 10:10:42 GMT

I am having issues with the following code within a SSIS package. It is designed to drop all database users before a restore operation commences and following a kill all connections script. The issue I am running into is that at run-time this code generates an error saying the collection has changed after the first user is dropped which causes the script to exit. Anyone have any ideas what is wrong here? Of note, this is running against a SQL 2000 database not a SQL 2005 database.Any help you can provide would be greatly appreciated.Scott[code]' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.' References Used, includes defaults:' Microsoft.SqlServer.ConnectionInfo' Microsoft.SqlServer.ManagedDTS' Microsoft.SqlServer.ScriptTask' Microsoft.SqlServer.Smo' Microsoft.Vsa' System' System.Data' System.Windows.Forms'Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.SqlServer.Management.SmoPublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() Try Dim srvr As Server = New Server(Dts.Variables("DestinationServer").Value.ToString) Dim db As Database db = srvr.Databases(Dts.Variables("DestinationDB").Value.ToString) Dim usr As User For Each usr In db.Users ' Check to see if the user is a system object, if not then drop the user If Not usr.IsSystemObject Then usr.Drop() End If Next Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try End SubEnd Class[/code]

load assembly - error

kln2020 — Wed, 19 Jan 2011 07:03:05 GMT

hi,I load assembly of managedts.it's work great with the new version of sql 10.5 .but when I try to use this spesfic assembly I get a ERROR Version.mybe someone had exprience about this.thank's

update access to sql server

wendy elizabeth — Wed, 03 Nov 2010 16:43:26 GMT

I would like 'power' users to be able to run queries and update selected tables, I would prefer for this to occur on the web. Can you tell me how to accomplish this task using either sql server 2005 and/or sql server 2008 R2?

Running .sql file using C# .Net - Accents Problem

DiegoPeña — Tue, 26 Oct 2010 10:51:02 GMT

Hello, I have this small app to run a batch of .sql files on multiple databases. When the .sql files have accents, (most of them comments) I get this error:[img]http://img5.imageshack.us/img5/7030/sinog.jpg[/img]The thing is that I can't remove the accents I must leave them there... So if anyone has any experience or advice to share it would be much appreciated.This is a example of the code im using:using System.Data.SqlClient;using System.IO;using Microsoft.SqlServer.Management.Common;using Microsoft.SqlServer.Management.Smo; namespace ConsoleApplication1{ class Program { static void Main(string[] args) { string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"; FileInfo file = new FileInfo("C:\\myscript.sql"); string script = file.OpenText().ReadToEnd(); SqlConnection conn = new SqlConnection(sqlConnectionString); Server server = new Server(new ServerConnection(conn)); server.ConnectionContext.ExecuteNonQuery(script); } }

object with managedDTS

kln2020 — Sun, 24 Oct 2010 14:47:54 GMT

hi, I try to create this object, but I Had error :$obj= new-object Microsoft.SQLServer.ManagedDTS "$ServerName"error - "can't find type [ Microsoft.SQLServer.ManagedDTS ] : make sure the assembly containing this type is loaded "thank's for the help.

Looking for MySQL expert to Skype with us!

tess.vanb — Mon, 23 Aug 2010 18:44:58 GMT

We are looking for a MySQL consultant who is expert in writing conditional SELECT statements using the "CASE WHEN" structure that we can Skype screen share with who will advise us until we have constructed an SQL statement that delivers the desired results.Thanks in advance,Tess

script out all the jobs in sql server 2008 excluding the mirroring jobs

coolchaitu — Thu, 22 Jul 2010 12:24:58 GMT

Hi,I want to script out all the jobs in sql server 2008 excluding the mirroring jobs to a file whose location is passed as a parameter. Can you please let me know how to do this.Thanks,Kiran

change logical names in SMO

douglas.strough — Tue, 22 Jun 2010 16:42:50 GMT

Hello All,I am having issues changing the logical names post restore.I am restoring a database with SMO (using VB.NET). After the database has been restored, I am changing the logical names of the files to match the new database name by using an alter database statement query.Before the restore is performed.I am doing a validation for valid database names (i.e. valid windows OS filenames) via a regex pattern before performing the restore. The problem is, the T-SQL that I am executing after the restore is not adequately compensating for special characters in the database name (i.e. filename). Here are the statementsTaken from SMO SQL capture during a VB.NET application run. SMO changed the database name to DBA[]] instead of DBA[]. This works.[code="sql"]RESTORE DATABASE [DBA[]]] FROM DISK = N'C:\DBA-NoPassword.bak' WITH MOVE N'DBA' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA[].mdf', MOVE N'DBA_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA[]_log.ldf', UNLOAD, REPLACE, STATS = 1[/code]Trying to change the logical names, post restore.Corrected SQL statement that works:[code="sql"]ALTER DATABASE [DBA[]]] MODIFY FILE(Name = [DBA], NEWNAME = [DBA[]]])[/code]Uncorrected statement that does not work:[code="sql"]ALTER DATABASE [DBA[]] MODIFY FILE(Name = [DBA], NEWNAME = [DBA[]])[/code]As you can see, I had to add an extra "]" at the end of the database name "DBA[]" to make the statement work. It is as if the extra "]" is acting as an escape character for the final ]. My question is.. if SMO can correctly restore a database name which contains "[]" in the name, then a SMO operation should be able to change the logical names. I have been googling and have not been able to find SMO code to incorporate into my VB.NET app, and I do not want to create special conditions for each special character that may be used in a database name. I also do not want to limit the special characters either.Did I miss something on my google search for how to change logical names using SMO?Let me know if you guys have any ideas.update:I am aware that when I create a new database, it will create logical names based upon the database name. When creating a new database via a restore, it uses the logical names embedded in the backup headers. I cannot override the default logical names during the restore, and I need the move option because the remote server I am restoring to does not contain the same file paths as the backup file.My question is for already existing databases, that are being restored, which already have logical names different than the database name.Thanks,DougUpdate #2:I think a workaround solution might be for me to first drop the database, create a new database, and then do a restore with move to option. Sure seems like a lot of work. Please let me know if I can change logical names with SMO...

Refresh PivotTables in MS Excel 2007 with OLE Automation

sgovoni — Thu, 06 May 2010 16:03:45 GMT

Hi,I need to update a pivot table that resides on a Microsoft Excel worksheet.I created a stored procedure dbo.USP_DMO_EXCEL_Pivot_RefreshTable(link: [url]http://www.ugiss.org/Content/Article/Aggiornare-una-tabella-pivot-di-Microsoft-Excel.aspx[/url]) that uses OLE Automation (sp_OA*) to refresh, every night (without open the Excel file), the pivot table data in Microsoft Excel worksheet.The stored procedure works well in this enviroment:- OS Windows Server 2003- Office 2003- SQL Server 2008The stored procedure does not work with:- OS Windows Server 2008 64-bit or Windows 7 64-bit- Office 2007- SQL Server 2008 64-bitThe pivot table connecting to SQL Server with an ODBC connection (32 bit).The Excel file will open correctly, bit I get an error during execution method "RefreshTable" in the following line of code:[code="other"]Exec sp_OAMethod @objWorkSheet PivotTables('Pivot_Name').RefreshTable[/code]The execution of stored procedures will not be completed, the only way (to stop it) is to interrupt forcefully the task "EXEC.EXE *32".When I forced to close the task, in SSMS I get the following error:-2146827284 Unable to find Microsoft Office Excel PivotTables property for the class Worksheet. C:\Program Files (x86)\Microsoft Office\Office12\1040\0 XLMAIN11.CHM Message 50000, Level 16, State 1, Server <name> USP_DMO_Excel_Pivot_RefreshTable procedure, line 369 Whilst Error: Return object workbooks, Could not find property PivotTables for Class Worksheet I also installed the 2007 Office System Driver: Data Connectivity Components ([url]http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en[/url]) but without improvement.Any suggestions? Thanks a lot!

DMO UseBulkCopyOption

JMartin-392745 — Mon, 10 May 2010 03:09:22 GMT

GuysWe have just migrated a database to SQL Server 2008 from SQL 2000 SP4, there is an in house application that uses DMO to bulk copy data into the load tables.We are expereincing major performance issues when we switch the database to simple recovery model and a lot of instances in the event logs/SQL logs where the recovery model is being switched to RECMODEL_70BACKCOMP then back to Simple. This is not the case when we switch to Bulk Logged.We have prety much concluded that the issue is to do with our app, however not being too familiar with DMO (the Dev left many moons ago) I am after some advice.At the moment the UseBulkCopyOption Property is set to true, what would be the repercussions of setting this to false and would it potentially sole our issue.Any pointers on how to load lots of data quickly using .NET/SMO etc. would be appreciated.Also does anyone know if using the BULK INSERT T-SQL would have similar issues to the DMO bulk copy that we are seeing?Cheers in advanceJQ

Setting article properties for a publication using RMO in C# .NET

Pavan Kumar Renjal — Mon, 03 May 2010 04:34:26 GMT

Hello everyone i am using transaction replication with push subscription. I am developing a UI for replication using RMO in C#.NET between different instances of the same database [b]within same machine[/b] holding similar schema and structure. I am using Single subscriber and multiple publisher topology. During creation of publication i want to set a few article properties such as Keep the existing object unchanged and allow schema changes at subscriber to false and copy foriegn key constarint to true. How do i set the article properties using RMO in C# .NET. I am using Visual Studio 2008.I also want to know as how we can select all the objects including Tables,Views,Stored Procedures for publishing. [b]I could do it for one table but i want to select all the tables at one stretch.[/b] This is the code snippet i used. TransArticle ta = new TransArticle(); ta.Name = "Article_1"; ta.PublicationName = "TransReplication_DB2"; ta.DatabaseName = "DB2"; ta.SourceObjectName = "person"; ta.SourceObjectOwner = "dbo"; ta.ConnectionContext = conn; ta.Create();

Retreive an error for an UPDATE SQL Statement

animho — Sat, 01 May 2010 02:45:38 GMT

HiI wrote a VB6 program using SQL-DMO. Everything works fine except I don't know how to get the real result of an UPDATE SQL Statement.For exemple:UPDATE MY_TABLE SET CLt_Name=new_name where Clt_ID=myIDWhatever the myID value (existing in my DB or not) this SQL command run from VB60 (ExecuteWithMessage) will produce the same result: No error and number of row is zero.How can I detect an error if myID is wrong??

retrieving tables from view with SMO

payam.ace — Sun, 02 May 2010 01:18:27 GMT

How can I retrieve tables names used in a view by SMO?I can get the columns but I need the tables used in the viewthx

Error: This cache contains no result sets, or the current result set contains no rows.

jeanangeo — Tue, 27 Sep 2005 03:21:00 GMT

Hi all,

Does anyone know what this error means, what causes it and how to fix it?

Exception:
System.Runtime.InteropServices.COMException (0x80045026): [SQL-DMO]This cache contains no result sets, or the current result set contains no rows.
at SQLDMO.QueryResults.GetColumnString(Int32 Row, Int32 Column)
at Mbr.DbGet.DbRestorer.RestoreDatabase()
at Mbr.DbGet.DbRestorerCollection.RestoreAll()

I have created an application that restores a given database. This application works fine on one machine but throws this error on a second machine.

I can't find anything about this error on either google or MSDN.

Can anyone help?

Kindest regards

jeanangeo