instance of MSDE we create. What should our installer check to find out if we need to install msde sp3, when our users upgrade our software? DavidHi, Installation of ur application require a valid login to sql server right, then you could verify the service pack by executing the below query. SELECT SERVERPROPERTY('PRODUCTLEVEL') This returns SP3 if the service pack installed is SP3. Based on this u could continue the installation. Hari SQL Server MvpThanks Hari, We have a problem with that solution though. I guess we need to use osql to run the serverproperty query, but we don't know how to run it so that the user doesn't see it run, and also we don't know how to get the response from osql back into our installer code. Do you have any
How do I set the path to the working database for other users? Currently it's on a mapped folder as Z:\ I know I can change the WORKINGDIRECTORY value but was wondering if there was a way to do this when adding a user Thanks to a post by MVP Hari Prasad for the following info: osql -S Name -Usa -Ppassword sp_addlogin 'SKY','password' go Use engdb go sp_adduser 'SKY' go When I first tested MSDE, it looked for a database in the DATA folder on the local computer's drive. We've got the database mapped to a Server's drive. When adding this user (I've got several that will have to be added), is there a SP for the path. I'm either overlooking or there isn't one or I'm looking in the total wrong place. Also,Hari had posted that
to Write to the application log ONLY if the action fails.
the thing is that despite that configuration, sqlmaint always writes an "information" entry to the application log for each Database that it sucessfully backs up saying something like "Successfully backup up database XXX .". this means 200 new "information" entries in the app log DAILY.
As you can imagine every 2/3 days I have a message in the computer saying that the Application Log is full which causes the computer to skip real messages logged from other apps until I clean it.
I don't want nor need sqlmaint to write to the application log since it writes a text file with the result of its execution everytime it is run.
anyone knows how to efectivelly prevent sqlmaint from writing to
or Windows 2000 or Windows 9x - If you use routers and these are configured not to pass UDP broadcasts, only machines within the same subnet show up. Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have .ListAvailableServer method to work properly, becouse precding release of Sql-DMO Components of Sql Server 2000 present a bug in this area. Courtesy of Mr. Gert E.R. Drapers further Information at http://sqldev.net/misc.htm to the besto of my knowledge, as you can see from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_s_7igk.asp, SQLServer object does not directly exposes a disconnected property to get it's state, so you have to connect (and eventually use the Status method,,, but youll''be already
a month worth of backups. I was hoping that somebody could help me with a small SQL script that will backup my database from Monday thru Saturday, automatically. Also it'd be great if the backup file would contain a name and the date of that day. Thanks a lot for your help.Hi, MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below URL for more info. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlmaint_19ix.asp Hari SQL Server MVPHi Hari, Thanks for your attempt to help me. Unfortunately for your suggestion to work, I have to install SP3a and for some reason I cannot successfully apply that. I constantly get the "The instance name specified is invalid" error, even though I did not specify an instance name
Hi Could any one tell me how to diffentiate between a sql instance and an msde instance mmchi, SET NOCOUNT ON SELECT SERVERPROPERTY ( 'Edition' ) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_3mi1.asp it returns "Desktop Engine" for MSDE
MSDE 2000 via the UPGRADE=1 UPGRADEUSER=some_login http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_71b9.asp and not other editions/versions of SQL Server..Hi Andrea, If this is true, the only way to move to MSDE 2000 is to move to MSDE 1.0 first. Is the upgrade from SQL Server 6.5 to MSDE 1.0 straightforward?hi Peter, I'm sorry, AFAIK MSDE 1.0 setup did not provide a way to upgrade from SQL Server 6.5 I think your only solution is an upgrade to a "full blown" edition of SQL Server 2000, that provides the required wizards and binaries for upgrading.. SQL Server 6.5 architecture is very different from 7.0 and 2000 versions it's not just a matter of copying files as the whole database architecure is different .
Administer SQL Server express? I installed it and now what? How to view/browse/create databases? Any visual tools?http://www.aspfaq.com/show.asp?id=2442 OR SQL Server Managment Studio Express http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en http://msdn.microsoft.com/vstudio/express/sql/default.aspx HTH, Jens Suessmeyer.
How I can connect an ASP.NET application with MSDE2000What have you read so far? Have you done any research into ADO.NET? I suggest picking up one of the 30 or so books on the subject or take a look at the documentation that clearly shows how to connect from any application to SQL Server (including MSDE).
MSDE as part of my Project Management database (PMDB).Hi, on the guess that you are trying to integrate the setup in your own projects you should go by these links. Samples: http://www.microsoft.com/downloads/details.aspx?familyid=6e9a7403-c4ba-4d98-bb0b-2c9d6414071f&displaylang=en How To Distribute the Microsoft Data Engine (MSDE) With the Package and Deployment Wizard (PDW) http://support.microsoft.com/default.aspx?scid=kb;en-us;231923&Product=vb6 SAMPLE: Setup1.exe Fully Automating MSDE Setup with the Package and Deployment Wizard (PDW) http://support.microsoft.com/default.aspx?scid=kb;en-us;234626&Product=vb6 There are also 2 post from Andre where the whole thing was discussed earlier: http://tinyurl.com/6ux7p and http://tinyurl.com/4x8pv
Hi. I'm using MSDE 2000. Is there a way to get a query result in a 'readabe' XML format from command line? I found the option 'FOR XML', however this produced something that is not really XML. E.g.:You can execute SQL queries to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve results directly, you use the FOR XML clause of the SELECT statement, and within the FOR XML clause you specify an XML mode: RAW, AUTO, or EXPLICIT. For example, this SELECT statement retrieves information from Customers and Orders table in the Northwind database. This query specifies the AUTO mode in the FOR XML clause: SELECT Customers.CustomerID, ContactName, CompanyName, Orders.CustomerID
SQL Server/MSDE database. After running the wizard, you may still need to do some manual changes on the newly created database on MSDE to make sure PK/Index, data types are OK, and since Access queries may not may not be transferred into the database on MSDE, you definitely have re-write some or all of queries you used in the Access file with views or stored procedures in MSDE. Database. How do I attach this default database into MSDE on my server?Hello, the upsizing tool crashed for me with a overflow error. I use MSDE2000a, that doesn't include the dtsrunui tool ,( so any help how i get a access db to msde2000a ? I check dtsrun , but im very confused how to use it. How i get the data from the access db, which is a file not a server like dtsrun expected.hi Alexander, regarding
How can I determine the instance name of an MSDE instance in code?SELECT @@servername HTH,Greg; Thanks for your response. A second question if I may: application install its own instance of MSDE or attach its DB to an already present instance of MSDE? If it's OK to use an already existing instance of MSDE then I would need the instance name in order to attach my app DB, which was the reason for my initial post. As always, any and all assistance is greatly appreciated.Hi Pete, With MSDE, the preference is that you install your own instance. I'd suggest a named instance with your app name as the instance name. In future with SQL Express though, this recommendation changes. The preference then will be everyone using a single instance. HTH,Greg; I suspected that
to go :) I have a few questions: I've successfully imported DBF files into SQL server (MSDE) but I can't seem to create a data adapter (in VB) unless I have a primary key defined. Do tables require primary keys be defined prior to dragging them onto a forms and working with datagrids? How can I create a primary key on a compound field? For a simple key (one field), I know to open the server explorer, go into design mode, then right click the field and set primary key. This works well but what about a 'details' table where the only DBF format it was a blank character field, but when it was imported in SQL, it became <null> While I might be able to get around this by excluding 'pref' from the key, I'll have that problem
downloads/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en executing that tool you can find "stuffs" that are no longer in order with the new edition of the engine, like deprecated JOIN styles and the like.. this will enable you to modify and test your current platform in the SQL Server 2000 instance as well.. you can read further hints at http://msdn.microsoft.com/en-us/library/bb677622.aspx..I want to move from Machine A with MSDE to Machine B with SQL Express 2005. Should I: a. Move MSDE from Machine A to B. Then perform an in-place upgrade to SQL Express 2005 b. Perform an in-place on MSDE then move to Machine B?hi, I'd go for c. let machine A rest in peace and just install SQLExpress on a clean machine B.. there you have
\<Instance Name>\Setup For a default instance: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup If one of these keys exist but there is no ProductCode, the instance is one of the SQL Server 2000 Editions (such as Enterprise, Enterprise Evaluation, Standard, Developer, or Personal). If a ProductCode entry is present, the instance is MSDE. You can also parse the sql errorlog to determine what edition, see KB: INF: How to Determine the Download Files You Need for Upgrading to SQL Server 2000 Service Pack 3 - http://support.microsoft.com/?id=814618 Fany Vargas Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? For information about the Strategic Technology Protection Program and
I installed SQL Server 2000 desktop (much different installation than 7.0!); then I went to fire it up, and discovered that I have no MMC or Enterprise Manager! I thought this would be standard stuff in the XP Pro package; I was able to find it right away when I used to run W2000. Any suggestions as to how to install it?hi, MSDE comes with no graphical tools but a buch of command line tools like oSql.exe, BCP.exe, DTSRun.exe and the like.. for some graphical tools, both commercial and free, you can have a look at http://www.microsoft.com/sql/msde/partners and/or http://www.aspfaq.com/show.asp?id=2442
Using VB6 and SQLDMO, I'm attempting to get a columns description, as shown in the 'Description' field in the Design view of a table. Problem is that I can't find a 'Description' property in the SQLDMO server.databases(x).Tables(X).Columns(x).Properties(X) Can anybody help? TIA Jim K.hi Jim, you are trying ot access extended properties (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8 _ar_da_0c8k.asp), and SQL-DMO has no support for them, but you have to resort on standard Transact-SQL code please have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/ManagingExtendedProperties.asp for further info
How do I completely remove MSDE so I can do a new clean install?This should help: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q290991
Hi, Can anybody please tell me How to know if a table exist in a Stored Procedure? Thanks in advance,Hi, use dbname go Select object_name(id) from syscomments where text like '%table_name%' Replace table_name with actual table name. Hari SQL Server MVP
Hi, I have SQL Express 2005 installed on my machine. Now I want to restore a database to this SQL express. How to do this?If you do not have SQL Server Management Studio -Express, go to this location and download the file. You could also download SQL Server Books Online. Then you will have both instructional material and tools to allow you to manage your databases. SQL Server Express Edition (Advanced/SSMS) http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx SQL Server Books Online http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
I was directed to create filegroups so that clients that install msde or sql server would look the same Can anyone point me in the right direction on how to create the filegroups on msde. I was thinking about just splitting up the indexes and tables at this point. Is this what a sql server install would do so that they could take advantage of Raid drive?See Filegroups|Creating in Books Online. The basic syntax is: ALTER DATABASE <dbname> ADD FILEGROUP <filegroupname> Then add files to the filegroup with: ALTER DATABASE <dbname> ADD FILE (NAME = <filename>) TO FILEGROUP <filegroupname>i appreciate the information thanx
= 0 Property(S): SqlDoUpgrade = 1 Property(S): ProductName = Microsoft SQL Server Desktop Engine (BESMgmt) Property(S): VersionNT = 502 Property(S): ALLUSERS = 1 I do not have a blank SA password. I just set it up two hours ago. I know the SA password. It is strong, because I was told when I installed it that it had to be strong. Will someone please explain how to get this service pack to install?Just tried again with the BlackBerry services and the database instance shut down. Now I'm back to "Can't run the script files." It looks hopeless. I don't understand why the log file or an error message can't just come out and say what the problem is instead of making me guess.hi Andrew, I've just tried a similar scenario
How to encrypt the storeprocess In SqlServer2000 , so other people Can not see the SP Source in bussiness SoftWare .. any Detail about this ? the better ,give me some document on the web ! Thanks a lot !hi, SQL Server 2000/MSDE provides the CREATE PROCEDURE WITH ENCRYPTION ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp ) clause you can use to encrypt the text of your stored procedures.. if you go this way, please keep your procedures in some kind of source code control so that you maintain the original procedure's text code available.. just a note the SQL Server encryption algorith has been broken in the past, so this protection is not fully secure, even if for standard users will be enought..Don't fool yourself
is using xp_cmdshell really necessary?not anymore its not I have gotten around that by now using a scheduled DTS pacakge that runs every minute. checks some stuff to see if it needs to process and then uses an ActiveXscript task to do what the xp_cmdshell was doing. The advantage of the xp_cmdshell would have been more realtime processing users are uploading files from our website which have to get moved from the webserver to db server and renamed, then loaded up to the database. the xp_cmdshell call was going to be doing the file move and rename. only problem I have now is that the Agent job takes about 5 minutes to run and keeps hanging up with a status of "Performing completion actions" it finishes just taking a while. The DTS itself runs in about
I am not so familiar with sql server. I would like to know how to purge database? How to truncate database. Do we need to do some database maintenance if the databse grows to 33gb? thanks!Ah, SQL Server manages the space allocated to a database based on the amount of data you're storing and the size of the log file(s). When you delete data, the space is reclaimed for use but not deleted unless you request "autoshrink" in which case the pages are discarded. The log must also be managed separately with periodic backups and truncations. I suggest you check out "BACKUP DATABASE" in books online for more information.
the SQL Server db so that I can create a linked-server definition via Enterprise Manager. Does anyone have any thoughts either about how to get this approach to work, or a better way to solve this problem? Thanks in advance for your help!hi Rick, this seems like you want to perform a push operation, from the "master" to all "remote" MSDE instances I'd go the opposite way when the remote MSDE instance is able to connect to the "master", add a linked server from remote to master, perform the operations you need, drop the the linked server the broadcasting mechanism to propagate server presence over the lan is not always granted as lot of stuff is involved for instance: have a look at ListAvailableServer exposed
Hi, How can I install MSDE from the CD be selecting options and not by writing the set up at the command prompt. I would like to select different code page and sort orders while installing.You can't do that unless you write your own custom installer. MSDE is meant to be installed by programs, not by users. But if you have some experience with VB it should not take too long to write something.
Hi te client seems to logon as guest. Do i need to install MSDE on a windows server edition to get the windows authentication working ? SQL Authentication works from remote machines, but becorse the password is sent as cleartext over the network i would like to use the Windows authentication. Can someone give me an answer on this ? ( i want to move some app from using acces to sql server). Johan.If you are all in one domain, just make sure that you disbled "Simple file and printer sharing" on the hosting machine. HTH, jens Suessmeyer.
used a MSDE database. This server had a motherboard failure and I was not able to repair it. I did get all the data off the drives so the MSDE SQL databases did get copied. The problem is the admin password for those database is not known to me and the previous admin does not remember. I need this password so that I can reinstall the web application and give it the admin password for the old databases. The tool the software company gave me to pull the password does not work, as it pulls the information from the registry (which is not the original as I have a new boot drive). Does anyone know how to pull MSDE SQL database passwords without booting from the original drive? There has to be a way to get all of the databases back since the data is intact.If you have the MDF files
Hi, I have a question regarding how to to read the database transaction log? The purpose is to recover any accidentally deleted records in tables. The technical environments are as follow: • Database version : MSDE 2000 SP4 • Operating System : Windows XP Pro SP2 • Database Transaction log file : *.ldf (files with extension .ldf)
How would I access an MSDE database on a win2k server over the internet?a) The server must have an official address for the internet, no internal IP like 192.168.x.x b) the server must be reachable from outside, not blocked by a firewall c) that's all, use the ip like you do in the internal network The only part: so you are totally unsecured, everybody can connect (or try to hack your sa password), the traffic runs unencrypted over the internet, even the login is unencrypted! Easy to find for a profi hacker. Use SSH or VPN to encrypt the traffic or configure the firewall from the server that only defined IP addresses can connect (which does not encrypt the data you are sending). Helmut
When I go into the enterprise manager design table mode and pick image as a field type, it defaults to 16 bytes in size, and doesn't allow me to change the size. I can't create a varbinary field greater than 8000 bytes either. The msde instance is a recent install downloaded fresh from Microsoft, so it is the current version (but for the life of me I can't figure out how to determine the version #). I've also tried using a shareware package called MSDE manager, but with the same results. How do I create an image field of 50K, for example? Marc PelletierHi, It is not an issue, By default it will take bytes. Image data type will allow you store a maximum of 2 GB. No need to change any thing VARBINARY will allow a maximum of bytes.@TK2MSFTNGP12.phx.gbl: I don
Hi, I'm trying to determine from a VB6 app if MSDE2000 is already installed on the client so that the app can autmtically spawn a instalation process without bothering the user. Any idea? (registry or whatever ) Thank you, MECO
Hello, someboyd know how to insert a SQL Script in the core of the MSDE Installation Program ie:, when install MSDE, too install(or run) a script that create a database, tables, triggers and users for a custom application. All installation are maked via Installshield, an Visual FoxPro Application and MSDE with Script. Somebody make it ? Pedro C. Arias Rosario Argentina.De Argentina y escribiendo en Ingles? a donde llegaremos.
do I import a db into MSDE2000? Can it be done? I backed a db up from another server and now I want to import on to this new machine. Not sure how to go about doing that though Use, e.g. in OSQL, RESTORE HEADERONLY FROM DISK = 'backup-file-name-and-path' to get a listing of the contents of the backup file to determine the logical database name. Then, if it is for a single mdf/ldf file pair, RESTORE DATABASE [NewDatabaseName] FROM DISK='backup-file-name-and-path' WITH MOVE 'logical-database-name' TO 'NewDatabaseFilename', MOVE 'logical-database-name_log' TO 'NewDatabaseLogFilename' The NewDatabaseFilename should have a .mdf extension and the NewDatabaseLogFilename a .ldf