I am using SQLDMO.DLL for Backup & restore utility in C# application.
Following is the code :
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect("IBM0505d-040","sa","");
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Devices = res.Files;
res.Files = this.txtRestorefrom.Text;
res.Database = "abc";
res.ReplaceDatabase = true;
res.SQLRestore(srv);
While running above code it gives following exception :
"[Microsoft][ODBC SQL Server Driver][SQL Server]Database in use. The system administrator must have exclusive use of the database to run the restore operation.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally."
I am trying to write a database compare application for our SQL Server databases using VB and SQLDMO.
I find SQLDMO to be rather slow but on large databases with about 800+ tables, this loop eats up all of my abundant memory until everything crashes. On smaller db's it works fine but is a slower than I expected.
Dim Tbl as SQLDMO.Table Dim Col as SQLDMO.Column
For Each Tbl in DB.Tables 'DB was previously set For Each Col in Tbl.Columns Debug.Print Tbl.Name & "." & Col.Name Next Col Next Tbl
Here's a pic of what the app looks like so far. If it looks useful to you, let me know, I'll give you a copy if I can get it working better!
I have a program using SQLDMO object.When I use it on my computer and I have install sqlserverit works OK,but when I move the program to another computer without sqlserverthe program runs error the error message is com exception 80040154can the proram using SQLDMO running on computer without installing sqlserver?BTW: my System is win2003,and the other is windows xp
I need to install sqldmo.dll and related files - so I can use SQL Objects. I've been searching the Internet and trying for hours to register the file on XP Client machines - but not go.
I have put the following files in the locations below:
sqldmo.dll Program FilesMicrosoft SQL Server80ToolBinn sqldmo.rll Program FilesMicrosoft SQL Server80ToolBinnResources1033 sqlresld.dll Program FilesMicrosoft SQL Server80ToolBinn sqlsvc.dll Program FilesMicrosoft SQL Server80ToolBinn sqlsvc.rll Program FilesMicrosoft SQL Server80ToolBinnResources1033 sqlunirl.dll System32 w95scm.dll Program FilesMicrosoft SQL Server80ToolBinn
I have also tried putting the files in System32 with the appropriate subdirectories - still not go. I cannot seem to get this to work.
If anybody has successfully installed sqldmo.dll for use on SQL Server 2000 client machines running XP, I'd be really grateful for a few pointers.
IS there a component/way of retrieving a list of SQL Servers or databases on your local network for SQL 2005/Express. The same way sqldmo worked for a .net project.
Hi,how to create an SQL DMO object in .net and use it to enumerate theproperties of Sql server. Can we use this object to list propertieswithout logging in to the server?
Hi all I am trying to do a Bulk Copy from a "tab delmimited" text file to atable in my database. I have it almost working except when the file hastoo few columns for the table (table has 421 columns).Some of my the files will have 419 columns some others files will have 421columns.When my bulk insert script encounters a file with 419 colums it will putsome of the data from the next line in the last 2 columns.I tried creating a DTS package with a bulk insert and I get the same outcome.Here is my test script can anyone help'--------------------------------------------------------------<%Dim objServer: Set objServer = Server.CreateObject("SQLDMO.SQLServer")Dim objBCP: Set objBCP = Server.CreateObject("SQLDMO.BulkCopy")Dim objDB: Set objDB = Server.CreateObject("SQLDMO.Database")dim BulkCopydim objTabledim itemdim g_strUploadPath: g_strUploadPath =Server.MapPath("../DOC2/")&"upload"dim strFileName: strFileName = g_strUploadPath & "ex_test.txt"'if file doesn't exist and it's an import,'don't waste time (too be add later)'If Import = True And Dir(FileName) = "" Then Exit Function'On Error GoTo ErrorHandlerobjServer.Connect "XXX.XXX.XX.XX", "XXX", "XX"objServer.EnableBcp = 1Set objDB = objServer.Databases("Advia120v2_dev")With objBCP.DataFilePath = strFileName.UseBulkCopyOption = True'tab delmitted, carriage return line feed ends row.DataFileType = 2'.ColumnDelimiter = chr(9)'.RowDelimiter = chr(13) & chr(10).IncludeIdentityValues = FalseEnd WithobjDB.Tables("Hemo_193_39552_39").ImportData objBCP'BCP = True'ErrorHandler:'Set objBCP = Nothing'Set objServer = Nothing%>'--------------------------------------------------------------thanks
Hi, I need to access and read the transaction log and run an update according to the log....is it possible through sqldmo....is there any other methods through which i can do the same
Problem is: "I am trying to attach a database through SQLDMO using VB6 In MSDE (Microsoft Sql Server Desktop Engine). If my '.mdf' and '.ldf' files are placed in a folder with Spaces then it give me error because it is picking up the folder name up to first space."
In VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?Thanks,Rainer.
I am finding that if the Files property specifies a folder for the path & filename that contains a space, the SQLDMO object appends a path of C:Program FilesMicrosoft SQL ServerMSSQLBackup and then my path.
If I use a path like C:TempBackup all works ok.
How do I get SQLDMO to accept a path with a space in the definition.
Hello, Does anyone know how I add the SQLDMO library to enable me to reference it in DTS Active X scripts? I do not want to install VB on the server to do this.
I have code that instantiates a sqlserver object and starts a job. Ihave used this on numerous XP workstations successfully, when I triedto move this to a Windows 2000 Application Server attached to a SmallBusiness Windows 2003 network and run the code, I am receiving theerror:Automation Error. The Operating System Cannot Run %1.(Error Number -2147024714)The following is a code snippet:*********Dim app As New SQLDMO.ApplicationDim oSQLServer As New SQLDMO.SQLServerDim oJob As SQLDMO.JoboSQLServer.LoginTimeout = 30 ' ERROR OCCURS HEREoSQLServer.LoginSecure = True**********The code fails when setting the LoginTimout parameter. The SQLDMO.dllhas been registered successfully on the server.Any ideas whey the behavior of this code is different on my applicationserver vs the client workstations on the network?ThanksMike G
My application uses sqldmo to retrieve the list of sql servers in thenetwork, it works fine in some machines, but it is crashing theapplication in others, no error and no exception, it shuts down theapplication. It fails in the following line:SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();any ideas?thanksmusachy
Hi,I am new to the mysteries of SQLDMO.I have written a program whereby the user can select which SQL serverto connect to on the Network and this works fine.I am using the SQLDMO.Application.ListAvailableSQLServers routine andthis works fine.However, the Server on the users machine appears as 'Local'I need to display the server name as the actual server name instead of'Local'Any ideas how to do this with SQLDMO.I know I could delve into the registry and get the name from there, butI want to avoid this.CheersAlex
Hi, this is a bit of background to what I'm trying to achive: I have an application that creates a new database in MS 2000. The application will then create a login, with then create a user and add the login to the user, it will then add that user to the folling roles; db_owner, db_datareader, db_datawriter. A SQL script is then run to add tables, view and stored procedures to the new database. Most of this works however I'm having a few problems with loging in as the new user. Aslo how do I ensure that if new objects (tables, stored procs etc) are added the new user will have full permissions on them? This is the code I have so far...I'm going round in circles with it and was hoping that someone with fresh eyes might be able to see where I'm going wrong: Many thanks 1 protected string CreateUser(string strDatabaseName) 2 { 3 //database hase alreday been created 4 SQLDMO.SQLServer gSQLServerDMO = new SQLDMO.SQLServer(); 5 6 string serverName = ConfigurationManager.AppSettings["DbsServerName"]; 7 gSQLServerDMO.Connect(serverName, 8 ConfigurationManager.AppSettings["DbsUserName"], 9 ConfigurationManager.AppSettings["DbsUserPwd"]); 10 //get the database object 11 SQLDMO.Database dbs = (SQLDMO.Database)gSQLServerDMO.Databases.Item(strDatabaseName, ""); 12 13 #region create a login object and populate it 14 SQLDMO.Login login = new SQLDMO.Login(); 15 string loginName = strDatabaseName + "_WebLogin"; 16 login.Name = loginName; 17 string password = "password"; 18 19 login.SetPassword("", password); 20 login.Database = strDatabaseName; 21 22 //check if it exists 23 bool found = false; 24 foreach (SQLDMO.Login ologin in gSQLServerDMO.Logins) 25 { 26 if (ologin.Name == loginName) 27 { 28 found = true; 29 break; 30 } 31 } 32 if (!found) 33 gSQLServerDMO.Logins.Add(login); 34 #endregion 35 36 #region create the user 37 SQLDMO.User user = new SQLDMO.User(); 38 //assign the login 39 string userName = strDatabaseName+ "_WebUser"; 40 user.Name = userName; 41 user.Login = loginName; 42 //dbs.Users.Add(user); 43 44 found = false; 45 foreach (SQLDMO.User oUser in dbs.Users) 46 { 47 if (oUser.Name == loginName) 48 { 49 found = true; 50 break; 51 } 52 } 53 if (!found)//add the user to the dbs 54 { 55 dbs.Users.Add(user); 56 57 //add user to role 58 //db_owner 59 //SQLDMO.DatabaseRole oRole = new SQLDMO.DatabaseRole(); 60 //oRole = 61 //oRole.AddMember(loginName); 62 dbs.DatabaseRoles.Item("db_owner").AddMember(userName); 63 dbs.DatabaseRoles.Item("db_datareader").AddMember(userName); 64 dbs.DatabaseRoles.Item("db_datawriter").AddMember(userName); 65 } 66 #endregion 67 68 string connString = "server=" + serverName + ";database=" + strDatabaseName + ";uid=" + userName 69 + ";pwd=" + password + ";";
I run into a problem which I cannot connect to the database using SQL Server 2000 Enterprise Manager. The SQL Server 2000 was working well until I hook a SQL Server 7.0 application to it. The error message: "General OLE Error 16386, You must upgrade your SQL Enterprise Manager & SQL-DMO (SQLOLE) to SQL Server 2000 (SQLDMO) to connect to this server". I checked fixed in the sp1 for SQL Server 2000, and found no entry. In MS support website and found nothing. Need help from someone who has experience on this. Thanks in Advance.
I developed an application in Vb.net and SQLDMO that backs up a database and performs certain operations on it once this is done. I have set the backup to backup to file which the user selects. However when i execute the backup i get an error message that " Backup device not found or error finding device" .THis error baffles me because i have not specified the backup to be to device can anyone help?
I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.
Reason I'm trying to do this:
We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.
According to BOL, I thought atleast UNSAFE should support this, but it seems not.
We are having a Windows Installer Program (32 bit) which calls the SQLDMO to get the list of installed instances in a 64 bit 2005 sql server machine. This fails as the windows installer tries opens up the 32 bit sqldmo and there is no instance of 32 bit sql server on this machine. Is there any way by which sqldmo can return all 64 bit sql server instance when called from a 32 bit Application?
hi i have a project and i want make thats; 1-)The list All SQL Server(s) in my network to dropdrown list(that's ok) 2-) the list all databases to selected sqlserver(that's ok) 3-) the list tables to selected database(i cant)
i wrote my source kod please help me
1-) Dim mDMOApp As New SQLDMO.Application Dim mNames As SQLDMO.NameList Dim t As Integer
mDMOApp = New SQLDMO.Application mNames = mDMOApp.ListAvailableSQLServers() lstServers.Items.Clear()
For t = 1 To mNames.Count lstServers.Items.Add(mNames.Item(t)) Next
2-)
Dim server As New SQLDMO.SQLServer Dim db As SQLDMO.Database
server.Connect(mysqlserver, "xxx", "xxx") For Each db In server.Databases lst.Items.Add(db.Name)
I am a new SQL Server developer using Visual Basic 6 Professional Edition. I am using ADO and SQLDMO to develop my application. The SQL Server is Version 7.0, SP2 and SP3 (two different servers).
I am having difficulty gaining access, using ADO, to databases I create using SQLDMO. I create the database, create the DBFILE object, create a login and attach it to the database, create a user, assign the created login to the user, and assign the created user to the 'db_datareader', 'db_datawriter' and 'public' roles. the login object was set to 'standard', not NT. the SQL Server is set to 'mixed' NT -SQL Server login mode. Thw SQL Server is running under the 'system account'. Using SQL Server Enterprise Manager, everything looks OK. An attemp to connect using the ADO connection object fails, giving an error of Login failed for user 'engbom3admin'.
Connecting to the SQL Server using SQLDMO uses the 'sa' user name with a blank password.
I can successfully connect to the created database using the ADO connection object using the 'sa' user name and blank password. Using the SQL Server Enterprise manager, I cannot see anything at all different between the 'sa' and my created 'engbom3admin' user. I've manually set the created user to have 'db_owner' roles, etc. Same result. I'm stumped. I've read all I can gather from the Microsoft SQL Server books. I'm still stumped.
I would greatly appreciate any help, information or tips you could provide. Thank you in advance.
Sincerely, Bob Wohlers SVP, IS Datamax Corporation
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
I use SQLDMO.Bulkcopy in an VB6 (have to) program to load data from aplain text file into a SQL Server 2000.One of the target columns is NOT NULL but it happens that I receive amissing value for that column in the source file. BulkCopy then has anODBC error raised that complains about the violated NOT NULLconstraint. The Bulkcopy error file however is empty. Is there a wayto figure out in which row of the source file the error occured ?Maybe a call to some ODBC diagnostics method to receive furtherinformation ? I'd like to avoid the obvious solution to drop theconstraint and then query the loaded data for NULL values.ThanksNils
I wanted to to detach a DB say MyDB from MSDE and wanted to attach it to SQL Express. It could able to Detach the DB from MSDE,but attachment to SQL 2005 is failing. When I verified MSDE Instance through Managment studio "MyDB" wasnot there ; means it got detached.I couldnt find the DB in SQL 2005. When I tried manually through command prompt I was able to attach "MyDB" to SQL2005. Please help me out!! Here is the code which I have written.
if SUCCEEDED(hr = CoCreateInstance (CLSID_SQLDMOServer, NULL, CLSCTX_INPROC_SERVER, IID_ISQLDMOServer, (LPVOID*)&pSQLServer)) {
pSQLServer->SetLoginSecure (TRUE); SQLDMO_LPCSTR DB = OLESTR("MyDB"); //Connect to MSDE First if SUCCEEDED(hr = pSQLServer->Connect (szDbServer, szUserName, szPassword)) {
pSQLServer->DetachDB(DB,lpszResult); //Detach the DB pSQLServer->DisConnect(); //Disconnect MSDE LPBSTR lpszResult2 = NULL; long lNumDB; //Connect to SQL 2005 if SUCCEEDED(hr = pSQLServer->Connect (szSQLExpDbServer, szUserName, szPassword)) { //LPCOLESTR lpo = A2COLE(sTemp); //sTemp.operator LPCTSTR pSQLServer->GetDatabaseCount (&lNumDB); SQLDMO_LPCSTR files = OLESTR("C:\Program Files\Microsoft SQL Server\MSSQLData\MyDB.mdf,C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_log.ldf"); pSQLServer->AttachDB(DB,files,lpszResult2); // Attach the DB to SQL 2005
Hi the following code generates an error while executing dbServer.Databases("InvModel").Transfer dbTransfer error description [SQL-DMO]Invalid parameter type; must be string or numeric ordinal, or an object; check documentation for this property or method. I am trying with sql2005 express edition. the code was one which we used for sql 2000
Code Block Dim dbTransfer As New SQLDMO.Transfer2 Dim dbServer As New SQLDMO.SQLServer2 dbServer.Connect gStrSQLServerName, "sa", "NEWTECH001" With dbTransfer .DestDatabase = Trim(txtCompCode) .DestLogin = "sa" .DestPassword = "NEWTECH001" .DestServer = gStrSQLServerName .DropDestObjectsFirst = True .CopyAllObjects = True .CopyAllTables = True .CopyAllViews = True .CopyData = SQLDMOCopyData_Replace .IncludeDependencies = False End With Screen.MousePointer = vbHourglass prgbar.Value = 35 dbServer.Databases("InvModel").Shrink 1, SQLDMOShrink_Default DoEvents Screen.MousePointer = vbHourglass prgbar.Value = 50 DoEvents dbServer.Databases("InvModel").Transfer dbTransfer DoEvents Screen.MousePointer = vbHourglass
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object. Here are some clipits of what you need. Imports SQLDMOPrivate myServer As New SQLServer Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString() Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString() Dim storedProcedure As New StoredProcedure Dim qresults As SQLDMO.QueryResults Example Stored Procedure Try myServer.Connect(serverName, userName, password) storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) Label1.Text = "Creation of stored procedure successful"Catch ex As Exception Label1.Text = "Creation of stored procedure failed" Finally myServer.DisConnect()End Try Run Stored Procedure and Obtain Results myServer.Connect(serverName, userName, password) qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows MemberRowNo = qresults.GetColumnString(num, 1) Next myServer.DisConnect()
