Answers,common,questions,MSDESQL,Server,Inter
Quick Search for:  in language:    
Answers,common,questions,MSDESQL,Server,Inter
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
SQL Stats

 Code: 31,543 lines
 Jobs: 372 postings

 
Sponsored by:

 

You are in:

 
Login



Latest Code Ticker for SQL.
Creating and Manipulting With Virtual Table
By Manivannan D Uma on 10/27


Select the Nth Biggest Value from SQL
By Manivannan D Uma on 10/26


Select the 2nd Biggest Number using SQL
By Manivannan D Uma on 10/26


SQL Detect and or Delete Dups
By Sean P. O. MacCath-Moran on 10/25


Find week number for a date
By Hendra Kusumadi on 10/25


Unix Date Convertor Function
By George Graff on 10/23


Insert pipe delimited rows into multiple rows.
By Charles Toepfer on 10/21


Logfiles by PL/SQL
By Stephan Rechberger on 10/21


To display a name in a default language if the given one doesn't exist
By Serge Alard on 10/18


Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!





Affiliate Sites



 
 
   

Questions and Answers abut SQL Server and MSDE Interoperability

Print
Email
 

Submitted on: 7/13/2000 9:38:53 PM
By: Found on the World Wide Web 
Level: Beginner
User Rating: By 2 Users
Compatibility:SQL Server 7.0, Other

Users have accessed this article 8129 times.
 
 
     Answers to common questions on MSDE/SQL Server Interopability (http://www.programmingmsaccess.com/FAQs/SQLServer.htm)

 

 

  •  
How many processors will MSDE support?
  MSDE supports up to 2 processors on an NT box, and it supports 1 processor on a Win 9x box.
  •  
Is there a license limit on the number of concurrent MSDE users?
  There is no limitation (via licensing or otherwise) on the number of users that connect to MSDE. However, Microsoft asserts it is "tuned" for 5-6 concurrent connections.  Within the context of Access projects, a connection equals a session.  Generally, each user will have just one session, but there is nothing to stop an individual from opening concurrent, multiple sessions.  Anecdotal information suggests that MSDE can support many more than 5 concurrent connections for some applications.  Sign the Guest Register and tell your story about MSDE capacity in the comments section.
  •  
How do I find out the names of the diagrams in my SQL Server or MSDE database?
  Run this query to generate a list of the diagram names in a database:

SELECT *

FROM dtproperties

WHERE (property = 'DtgSchemaNAME')

  •  
How do I copy a database from one SQL Server to another (including an MSDE server)?
  There are at least a couple of ways to tackle this problem programmatically.  First, you can use Transact-SQL in a stored procedure.  Second, you can use SQL-DMO objects in a VBA procedure.  You can find out about either through the SQL Server Books Online resource.

No matter which of the above two approaches, you will want to learn about the following system stored procedures: sp_detach_db, sp_attach_db, and sp_attach_single_file_db.

  •  
How do I manually copy a database from one SQL Server to another (including an MSDE server)?
  The way that I like the most uses the Data Link Properties dialog that you can open with the File > Connections command.  Copy your database and log files to disk. If you control the server, shut it down to do this manually and then re-start your server.  Otherwise, you have to use something like the file system object. When you get to the MSDE computer (maybe it is at home), copy the database and log files to your MSDE server.  Open an .adp file and connect it to the copied files using the Data Link Properties dialog.  Select the radio button for attaching a database file and browse to the .mdf file for your copied database.
  •  
I cannot get milliseconds to appear for datetime values in stored procedures that I display from the Access 2000 stored procedure template?  How can I include milliseconds in my results from Access 2000.
  The inability of Access 2000 to return milliseconds from stored procedures that run in the Access stored procedure template is a bug.  For example, milliseconds do appear when appropriate from stored procedures that you run from Query Analyzer.  One work-around to the problem is to capture milliseconds along with other time units with the DATEPART function.  Then, compose your own string representation for time.
  •  
How can I obtain the developer edition of SQL Server 7.0 for FREE?
  If you have a license for Microsoft Office 2000 Developer Edition, you may not know that you can obtain a copy of the SQL Server 7.0 developer edition with the Access Worflow Designer (AWD).  You can learn more about the AWD and how to get your copy of the developer version of SQL Server 7.0 by clicking here.
  •  
What's a system stored procedure?
  This is a built-in Transact-SQL program that typically performs some administrative task with SQL Server and MSDE.  The preceding answer references three system stored procedures.  There are literally scores of other system stored procedures.  System stored procedures start with an sp_ or an xp_ prefix.  You invoke them from an Access project stored procedure template by replacing text after the As keyword with EXEC followed by the name of the system stored procedure and any relevant arguments.  For example, EXEC sp_columns Customers can provide meta about the columns in the NorthwindCS database.
  •  
Will NorthwindCS install automatically if I already installed MSDE?
  The NorthwindCS front-end is copied to your machine when you choose to install the Access samples. This process is totally independent of MSDE being on the box or not.

If MSDE is running on your machine when you open the NorthwindCS ADP for the first time, you are given the opportunity to install the back-end database. If you choose to install it, scripts that generate the database, tables and data run. If you are not running MSDE, you are prompted for the location of a SQL Server so that the database can be created there.
  •  
Do I have to use CREATE PROCEDURE  to create a new stored procedure?
  No.  You can use the Alter Procedure statement inside of an Access project.
  •  
When I perform division between two integer quantities with SQL Server, the result is an integer quantity that truncates the fractional values in the quotient.  This does not happen with Access.  How do I get the fractional part of a quotient when dividing one integer into another in SQL Server?
  Apply a Cast function to the numerator and denominator that transforms each from an integer quantity to a floating quantity.  For example, your SELECT statement can have an expression like this CAST(SUM(quantity) AS FLOAT)/CAST(COUNT(quantity) AS FLOAT).
  •  
I have used the ADP data wizard many times, but I always had my computer connected.  Recently, I tried to create a database without having my computer connected.  The wizard failed with a message about network connectivity.  Is the database wizard for adp files with MSDE not to work unless you are connected?
  When using the database wizard to create a new .ADP file (whether to an existing or a new database), you must have a valid network connection to either a SQL Server backend or the SQLServer service (MSDE or SQL Server 7.0) on your local machine.
  •  
A Win 9x machine in my office fails to enable MSDE as a server on a local network, but MSDE acts as a server from other computers in the office network.  The computer that does not expose MSDE as a server performs file sharing with the rest of the network.  What could cause this?
  One cause of this problem can be the Browse Master setting for File and printer sharing on Microsoft networks.  Right click your Network Neighborhood icon and choose Properties.  Then, select File and printer sharing on Microsoft networks and click Properties to open a dialog containing the property setting.  The default setting is Automatic, but it should be Enabled for this and selected other functions.
  •  
I'm developing an application with SQL Server as a back end using triggers.  How do I deploy this to my customer with MSDE? (The customer isn't ready to buy SQL Server.)
  Go to http://msdn.microsoft.com/library/techart/msdedeploy.htm for a white paper on deploying Access solutions  and the Office 2000 Developer Edition re-distributable MSDE via its Package and Deployment wizard.  If you do any work with Visual Studio, you may also find some value in http://msdn.microsoft.com/vstudio/msde/deploying.asp.  Additionally, Rick Dobson has several articles either out or forthcoming on developing Access 2000 solutions with SQL Server/MSDE databases in SQL Server Magazine, Microsoft Office & VBA Developer, and Visual Basic Programmer's Journal.  In addition, the Programming Microsoft Access 2000 book has a chapter on the topic.
  •  
I am not able to install the sample database from Rick Dobson's MSDE article in the Visual Basic Programmer's Journal?
  Two common factors emerged as reasons for the installation program associated with the article not working.  First, your VBE project requires a reference to the SQL-DMO object library.  Use the Tools > Reference command to create the reference.  Second, you are running an obsolete version of NT 4.  The sample application requires Service Pack 4 or greater.  If you have an older version, the sample will not work.
  •  
After having MSDE work for months, I was no longer able to add tables, or other database objects to Access projects.  What can I do to get my MSDE back?
  Actually, the problem need not be with MSDE at all.  See if you can connect and create tables for the MSDE databases from another computer with a different copy of Microsoft Office.  If so, your problem may be with  Office 2000 instead of MSDE.  If you have already re-installed MSDE and the problem still persist, this is particularly likely.

Another site visitor reported success with your problem by running ScanDisk.  Select automatically fix errors before starting the program.

By the way, you should be careful about re-installing MSDE since the version of the MSDE database engine will not by default know about your databases from the prior version.  If you have a version of the master database from the former database version, you can try that to see if it lets you recover your databases from the earlier version.  Otherwise, there are several more involved techniques.  One of these is discussed on pages 480-481 of the Programming Microsoft Access 2000 book.   Go to the About the Book link on the Home Page for this site to learn more about the book.

 


Other 5 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments

 There are no comments on this submission.
 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | SQL Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.