Im consulting a financial company on their Access database. It has a back-end with about 100 tables (some close to 100,00 records) and a front-end with all the Forms, Queries, Modules, and Reports. The last few weeks they have had to compact and repair the front end twice a week where before it was a bi-weekly thing.
Im trying to sell them the service of moving everything I can to SQL Server and link it back to the front-end Access GUI, but Im not sure of everything that goes into this process and how long it will take - of course my client needs the process on paper with an estimate timeline/cost.
My questions are:
1) What does Compact and Repair actually do?
2) Can Access Queries be moved to SQL Server Views and be linked?
3) What can I do about Access Modules and Macros? Can they be moved to SQL Server somehow easily?
4) What other concerns should I have?
Also if any of you fine people have done this sort of thing in the past please share your stories :)
Hi, I am trying to learn more about SQL Server, so I can try to make the transition from Access, as I have read, and heard from many people that SQL server is far superior. I have a few questions that I am unsure of, I have basically no real experience with SQL server. 1) I have been watching the "How Do I" videos, posted on MSDN (which are AWESOME, btw), and for most of their examples, they use the SQL Server 2005 Express which comes with VWD. I do not have any problem running any of the sample applications on my local machine, but what about for when you want to actually create a web app to publish online? From what I have read, most hosts do not give support for the express edition of SQL Server 2005, so how do you go about changing your app? For example, if you wanted to use the Login control, which automatically creates a SQL express database for you, can it be changed to implement regular SQL server 2005, or SQL server 2000? I am just confused as to how to make use of the features available other than on a local machine. 2) Where would be a good place to learn more? Any recommended books? 3) Does anyone know of a good (affordable) host that would offer SQL Server 2005/ASP.NET 2.0 support? Any advice would be greatly appreciated. I know these are probably very basic questions, but I am very new to SQL server. Thanks,
My current website is based on access and running fine but a bit slow, the size of the file have always increase rapidly (i don't know why) and I have to download the mdb file every week, using access to compact it and send it back to the hosting company's server. Therefore I concern to upgrade it to SQL server or MSDE. I never used any of them and I have no idea how they work. Here are a few questions:
1, Should I use SQL server or MSDE? I know the differences among access, SQL server and MSDE, but I am curious why is MSDE free? are there any limitation or anywhere worse then access? (considering access does cost money but less powerful then MSDE)
2, Is there a file size problem like what I got with mdb files? it is very important to me because I have limited space for the hosting company.
3, I downloaded MSDE and installed into my WindowsXP, and what I see is only an icon in the task bar any nothing else. I have no clue how it work at all. What else do I need for the upgrade? What are the steps?
4, It seems SQL server and MSDE are tightly integrated into the system. Can I still have a database file and upload it to the hosting company just simply replace the mdb file? Please if someone could provide any tutorial how to run and manage MSDE.
I'm wondering what command I could issue to see the amount of memory a named instance is using from within the instance itself. We've enabled address extensions (PAE), and task manager no longer shows the correct amount of mem being used by the process. Under perfmon, I've added the object MSSQL$INSTANCENAME:Memory and I'm looking at the Total Server Memory. I'm seeing 4 gigs, when max memory is capped at 2gigs. This must show the server memory and not the instance memory ? Is there some way to see the instance memory ?
Hi,I copied the DATA directory from a backup ghost image to a new SQL6.5 installation and, after fixing a problem with the directory structure(which caused the databases to be marked as "suspect") I was able to workwith it in a normal way.Opening the company database works without problems. The DATAdirectory seems to be correctly copied. The only problem is that I'm unableto see the scheduled tasks. I'm a using predefined database user (not sa).My questions are:1. Is it normal not to see the scheduled tasks when logged asanother user than the sa or the owner of the tasks ? If it isn't, thensomething went wrong copying the DATA directory (even if SQL didn't markthem as suspect). Any advise or comment about this would be really welcome.2. If it's normal not to see the scheduled tasks, Is there any wayto recover the lost sa password ? The docs containing the password werelost around 4 years ago.Any help would be really welcome,
Hi Folks,I am a newbie when it comes to SQL Server 2000 and have a Sybasebackground. How can I reset my sa password in MSSQL like we do it inthe runserver file(-p) in sybase??? Also, can I configure replicationusing the standard edition of MSSQL server????ThanksRegardsdba_sybase2003
I have created logins and user accounts in SQL Server. How come, when I open SQL Server, it did not prompt for passwords ? How can I make it to do so ?
******************************************** *** Thank you very much in advance, security gurus *** ********************************************
Guys, Let me first give you a little bit of background...
One of my goals for this year is to migrate all DTS packages to SSIS, those DTS packages are used across the company by different groups/users.
Since I'm still not that familiar with SSIS and like many here, was well used to the enterprise manager view (all DTS packages on 1 single place), I'm unsure on how to structure my SSIS packages in a way that I could break it down by group let's say. The way that I'm doing right now is: I'm creating a solution for each group and creating the SSIS packages (.dtsx) relative to that group inside each solution, that's pretty easy. Now, the next thing that I'll have to figure out (and hope you guys can help) is how to deploy them structured this way and how to handle these packages to my end users. Basically, how do I handle "Group A" all of their SSIS packages? Will they have to open the package in debug mode on VS every time they need to execute a package?
We are currently running a corporate client with Windows 2000 and .Net 1.1. We are running a number of SQL Server 2000 applications and are now thinking of upgrading to SQL Server 2005 as part of a data consolidation exercise. I am concerned on a number of points:
Can I connect to SQL Server 2005 using old ADO connectors? We have about 40 Excel VBA solutions, and we dont want to upgrade to SQL Server 2005 if we will be unable to connect to the data source. We cannot upgrade any new versions of MDAC or upgrade the .NET framework so this is a concern.
Do we need .NET 2.0 or Visual Studio 2005 to connect and work with SQL Server 2005? If so, this will be a problem as we cannot upgrade any client beyond .NET 1.1, and only have VS 2003 as a scripted application we can install for any development.
Has anyone have any experience of the KPI capabilities of SQL 2005? We are bordering on committing to a Business Objects BI platform, and having worked with BO Dashboard Manager and Performance Manager for 4 months (it was horrible), I am not relishing the prospect and would like to propose SQL 2005 as an alternative.
Hi,I have a few questions about sql 2005 as follows:1. Which MS SQL version (edtion) is good as database tosupport a midium size web size?2. I have old *.mdf and .ldf file from ms sql 2000.Does it work if I just copy them (or just *.mdf file) to2005 sql server (any edition).TIA,steve
Hi,Is there any website which lists the various SQL Server relatedquestions which might be helpfull for interviews. I am looking forone that contains complex Queries that includes all kinds of operatorslike GROUP BY, HAVING etc...Regards,Kamlesh
what are pseudo tables how queries run by MSDE how stored procedures run in backend by MSDE how triggers run by MSDE where triggers and stored proc stored in MSDE and in which form where logs are being maintained of transactions/DML statement by default
I asked my tutor those questions and I want to ask you too
Hi Sir
I hope you doing fine
I have some questions please;
What is the diffrent between For Attach in Create Database Command and Sp_Attach_DBWhat is the diffrent between store db file in folder and store it raw partition e.g. C:What is the useful from TextImage_On in Create Table Command; it is order Sql Server to stor the photo in diffrent table's fileCan you give us a basic steps to insert *.JPG photo in Sql ServerIf I have this statement:
Why This Code not works and how can I do this without using Triggers.
Can you tell us the different between Media Set and Media Family and the useful of them. Can we do Backup and Restore Commands on Virtual Tape and How?When I write this code
Create Trigger xTrig on xTable For Insert AS
When this trigger will fire Before Insert, during or after.
What is the useful from EDURE in Create ProcedureWhat is the useful from Recompile in Create Procedure In Sp_AddRole how can I create this role and make the owner anther person e.g. sa What is the different between User and LogIn in Sql ServerWhat is the different between Role and Group in Sql ServerWhy can I add user to group in Sp_AddUser and I could not add one in Sp_AddLogin
These lies work good
-- Add New LogIn to ProgramManagerRole
Exec Sp_AddLogIn 'zProgramManagerUser' , '' , 'C8_Ahmad_13430_NewSIS' , 'English' Go
I'm new to using SQL server and have a few setup questions. I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
I've installed SQL Server Reporting Services and designed few useful reports on local workstation, now I want to deploy those on the production reporting server.
I have the following questions:
1) We have some demo databases with the similar structure so the the same report would fit all the demos. Is there any way to change the connecting string for the report programmatically without recreating report each time ? May be there is a way to pass database name to the report (encrypted) ? Is there any way to
2) I'd like to be able to protect those reports from unauthorised users ? Is there any way of doing that ? Is there any way to set up security on the base of folder anf for the whole group of reports ?
Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise. The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server. So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
Hello - I'm about to rebuild my website on a nice new windows 2k3 server. I was previously using SQL server 7 but I'd like to use the 2005 edition. Here's my first question - is the Express edition 2005 good enough to use on my live web server? Are there limits on the amount of connections at any one time? My second question is this - what's the best way to connect to SQL Server 2005 Express? At the moment I would use something like: Server=.SQLEXPRESS;Integrated Security=True;Database=myDB And then I would set up a COMPUTERNAMEASPNET account under SQL Server 2005 Express. Is that an ok way to connect?? Any ideas? Thanks.
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
If you have followed a link to this page (i.e. the URL contains "SearchTerms=xxx") please scroll down to see items of interested highlighted
This list of SQL Server Frequently Asked Questions (FAQ) was originally compiled in August 2005 based on the most frequently "read" posts on SQL Team. Since then new links have been added as topics become popular - so it might be better referred to as FGA - Frequently Given Answers!
NC=Indicates that the Article has NOT been checked for relevance. As time goes on these are getting fewer and fewer The latest change is in Red Recent changes are in Teal
URL to link to this post: 17Nov2005 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms= Followed by either "Keyword1,Keyword2" or "PhraseWord1+PhraseWord2"
The icon allows you to RightClick-CopyShortcut to provide a link to this topic with appropriate keywords - I got bored of having to make them manually! 07Mar2006 Stats
Homework I put this one at the top, in case you don't read any further. To find out if your homework will be answered on this forum you should see if it qualifies: Requirements for homework questions 07Mar2006 Example of Failure to meet the minimum requirements 07Mar2006 Learning SQL www.sql-tutorial.net 07May2007 www.firstsql.com/tutor.htm 07May2007 www.w3schools.com/sql/default.asp 07May2007
Why? Why is my LDF Log File so big / getting bigger / bigger than my MDF 14Oct2005
How to
How to ask a question on SQL Team and get a quick answer - i.e. the information you need to provide 06Nov2006 How to format your question or a reply (in particular see the [code] option 18Sep2007 how to encrypt and decrypt my password 07May2007 Rounding up/down to nearest multiple (e.g. order in dozens, buy at least 30) 28Sep2006 Replace multiple spaces, or other character, with single space 28Sep2006 Generate a Random String 13Sep2006 String comparison 24Jul2006 Article: Images and SQL Server How to get Images or blobs into SQL table (NC) Inserting Images as BLOB in SQL server table (NC) How to use DeBUG window in QUERY Analyzer (NC) Use a Parameter in the Top clause Dynamic SQL or How do I SELECT TOP @var records? Can't insert NULL into table with unique key (NC) Efficient paging of recordsets with T-SQL Article: Paging in SQL 2005 07May2007 Execute dynamic SQL that is longer than varchar(8000) 05Oct2005 Convert multiple spaces to single spaces 14Oct2005 Making Upper/Lower Case Sensitive comparison 27Oct2005 Convert to Proper Case (each word starts with capital) 19Feb2006 Deleting a massive number of rows 04Mar2006 Combine values from rows into a column 05Oct2005 i.e. FOO 1 FOO 2 becomes: FOO 1,2
CSV / Splitting delimited lists WHERE IN @MyCSV Article: Passing a CSV or Array to a Stored Procedure (NC) Best split functions and Concatenating data from rows with same GroupBy column Triggers Triggers to audit changes trigger to keep tables matching Set Update/Modified date column using a trigger 02Nov2005 Deactivating a trigger 07Feb2006
SQL Team Articles Article: Identity and Primary Keys - and Surogate Keys See also Article: Sending SMTP Mail using a Stored Procedure (NC) Article: Dynamic ORDER BY (NC) Article: More Trees & Hierarchies in SQL (NC) Article: Using SELECT to INSERT records (NC) Article: Deleting Duplicate Records Article: Returning a Row Number in a Query (NC) Article: Calculating Running Totals (NC)
Datatypes / Collation
GUIDs (NC) IsGUID check GUID is valid 01Aug2007 DATALENGTH() and LEN() 21Sep2005 Compare BIT columns and a BitMask column 05Jan2006 Collation conflict in Assignment or WHERE clause 05Feb2006 (See "Developer / Techie stuff" below for some other Collation links)
Datatypes - Dates
Time Only Function: F_TIME_FROM_DATETIME 29Apr2006 Date/Time Info and Script Links (including UNIX time) 16Apr2006 Date formatting ( Format Date/Time (NC) last date of the month (NC) Getting the date portion of a datetime value or Round a date to Year, Quarter, Month, Day, Hour or Minute How to get date in t-sql (NC) compare date with current date (NC) convert varchar to datetime (NC) Compare dates regardless of time? (NC) Convert varchar to date (NC) Time in Analog (NC) Start of Week Function (NC) Earlier date than 1/1/1753
Techniques
Prevent Administrators accessing a database 14Sep2006 SELECT vs SET 26Sep2005 What is a cursor, Why is it bad? 23Sep2006 Cursor or Not (NC) TSQL/ SP Best Practices (NC) case-sensitive (NC) User-Defined string Functions Transact-SQL (NC) Passing an array into a stored procedure (NC) Comparing two tables or views easily (NC) All Tables in All Databases (NC) Better Phonetic Matching Algorithm (A better Soundex) Levenshtein Edit Distance Algorithm (A better Soundex) Fuzzy matching 01Aug2007 Find tables with no PK (NC) Function: HTMLDecode (NC) How to protect errorlogs to tables from rollbacks (NC) Distance of Zip/Postcodes Great Circle Formula 07May2007 Haversine Formula 07May2007 Accuracy v. Speed 07May2007 See also here, here, here, here and here and here Upsert - combined Update/Insert Procedure 01Aug2007 and here Display all columns but only for rows with the most recent date 01Aug2007
Developer / Techie stuff
Changing column names on referencing objects 07May2007 When was DB object last updated 07May2007 How to clear the whole database 07May2007 Move / Script Development Database Changes to Production Database / Ship Application updates 04Nov2006 Script to analyze table space usage 11Oct2006 What version, and Service Pack, of SQL Server am I using? 23May2006 SQL Beautifier 13Apr2006 Performance / System Monitor / PerfMon - preferred values for counters 04Apr2006 SQL Server 2000 Knowledge Base Articles - updated every 3 days 21Jan2006 Concerns about SQL 2000 SP4 08Jan2006 Support stops for SP3a AND installing sp4 on cluster 26May2006
How to read Mssql transaction log file Compare data in two tables / two databases 31Oct2005 Recommended books 07Oct2005 Good tools for DBAs 06Oct2005 Best SQL Security Method (NC) Some query optimalisation (NC) Requirements for Horizontal Partitions Multiple SQL Server 2000 Instances (NC) Primary Key v. Unique Index (NC) HOWTO Run SQL Profiler without sysadmin rights (NC) upgrade from SQL7 to SQL2000 (NC) xp_cmdshell execute right (NC) Monitor object usage over time (NC) size of user tables in a database (NC) Changing Column Length (NC) Add, Sub, Mul, Div With Really! Big Numbers (NC) So you're a DBA eh?! Why I'm not a DBA (NC) Do you S-Q-L or Sequel (NC) Interview questions (NC) More Interview questions 11Sep2006 Best steps to becoming a DBA (NC)
Developer / Techie stuff - Collation
Bulk Delete of Records 07May2007 and locking issues 01Aug2007 Deleting lots of Records from a large table 01Aug2007 Synchronising data using staging tables 07May2007 Version Control 07May2007 how to change Collate on the server 20Sep2005 How to change default collate in SQL 20Sep2005 Cannot resolve collation conflict for equal to OP 20Sep2005 different Collation Settings between Databases 20Sep2005
Database Design and Structures
Codes - Common Lookup Tables CLT 04Jun2006 Good table naming convention 09May2006 Naming Store Procedures 09May2006 Recursion / Hierarchy 29Apr2006 Hierarchies (NC) Normalisation (NC) NULL opinion? (NC) Finding evidence of sql injection attack Security Testing: SQL Injection 09May2006
Locking
Using the NOLOCK hint 07Jun2006 how does row locking work (NC)
EMail
sp sending mail (NC) CDOSYS Send Email (NC)
XML
SQL Server 2000 XML (NC) Adding an XML Root Tag Handling tricky namespaces in OpenXML
Backup / Restore / Replication / Jobs / Houskeeping
Backup How Backup works What type of backup should I use? - Full, Differential, Transaction? Should I shrink the log? 22Jan2006 Automating Backups - Scripting v. Maintenance Plan / Wizard 08Mar2006 Backup to Disk, or direct to Tape? 05Dec2006
Disaster Procedure Checklist I think I have a database corruption, how do I mitigate any further damage and get upright again? 07May2007 How I Rescue Data from Corrupted Databases 07May2007 Disaster Recovery Plan 07May2007 Quiesce the SQL Service to enable low-level copy 01Aug2007
Restore RESTORE syntax / example Restore SQL 2005 to SQL 2000 via Downgrade 07May2007 Restore Full and all TLogs backups based on files in disk folder 07Dec2006 Move database to a new server (with minimal downtime) 16Feb2006 Moving to a new sql server 25Jun2006 Fix Orphaned Users (after a Restore or moving DB to a different server) Script User Logins after a Restore 01Aug2007 and Scripting users & logins from one server to another (e.g. after moving DB to a different server) 25Jul2006 how long will take to restore a 10 gb? All I have is the .bak file Restoring .BAK file to a different server? Unable to restore/attach a MDF file (single file attach / sp_attach_single_file_db) See also: This and this and this
Shrink Database / Files
Shrinking TEMPDB 19Apr2006 Database Shrinking 09Jan2006 Log Shipping -Shrinking Log and Data files (NC) MSDB Massive msdbdata.mdf
Housekeeping / Maintenance
Tara Blog Houskeeping Routines 06Oct2006 Index Tuning Wizard 07May2007 User Rights for SQL Service 07May2007 When was SQL Server last restarted? 07May2007 Optimising your indexes with DBCC INDEXDEFRAG 06Oct2006 Detach Move Db & Log File Reattach 17Mar2006 Rebuilding Indexes DBCC CHECDB, CHECKTABLE, NEWALLOC (old) Suspect Databases after Power Cut? Jobs failing (NC) Reindex All Tables in All Databases (old) deleting replication (NC)
Export / Import / DTS
Copying/backing up DTS Packages (NC) Export to Excel (NC) Generate INSERT statements for N rows of data Tool to script database records? (i.e. generate INSERT statement script) How to convert DATE Format from DD/MM/YY to MM/DD/YY in DTS (NC) Importing an Excel File with DTS (NC) Is DTS Secure for credit card backup to Access? (NC) Winzip in scheduled DTS Package (NC) Article: Using BULK INSERT to Load a Text File (NC)
Output / Reporting / Formatting
Pivot table (NC) Article: Dynamic Cross-Tabs/Pivot Tables (NC) which led to: More Dynamic CrossTabs using T-SQL 04Aug2006 (Jeffs Blog)
Error Messages
SQL Server does not exist or access denied (NC)
Connections
Linked Server Newbie Question (NC)
Configuration / Hardware
/3GB in boot.ini on Win2003 standard 19Oct2005 SQL memory /3gb /PAE AWE 19Sep2005 SQL Server Standard & 4GB memory Using AWE option in SQL Increasing SQL Server Memory
MSDE
MSDE Setup fails (NC) MSDE Install Issue (NC) Installing MSDE on XP Pro (NC) Backing up MSDE database (NC)
SQL 2005 / Yukon
Migrating to SQL 2005 Hints and Tips 01Aug2007 SELECT not allowed in Yukon/SQL2005? (NC) Download Yukon/SQL2005 (NC) SQL Server Management Studio (NC)
MySQL, Oracle, and the others
MySQL not as good as MS SQL? Migrating from Oracle to SQL Server
Other
SQL Reporting Service SP2? (NC)
Got an hour .. or ten ... to spare?
Would you work at Elwoo's office? Simple Quiz What is a Yak? 07May2007
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
I have recently installed 2005 Standard and 2005 Reporting Services (on a separate server), today we built a service account for the SQL services in Active Directory. I planned to use SQL Configuration tools to change the account but it fails with the message:
'No mapping between account names and security IDs was done'
I ended up going through and following the manual steps outlined in KB article 283811 - http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811
But I am baffled and concerned as to why it failed.
Hi pardon my ignorance but I wonder if someone could answer a few questions for me.
I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.
A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.
So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.
How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.
I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.
I suspect the problem is the way my program is going about the checking.
Should I use an SQL insert statement like??
If exists(SELECT username from users where username=@username) BEGIN RAISEERROR("Username already exists") END ELSE BEGIN INSERT etc
If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?
Also some more general questions.
1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.
2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()
As a rule I close all my connections as soon as I'm done with them.
A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AIcollation, but there is no such collation returned fromfn_helpcollations(). Also, if I try to use this collation ina CREATE DATABASE stmt, SQLS yells about it.I see that there is a Latin1_General_CS_AI. What effects are therein using this collation? The SQL_* collations are SQL collations,while non-SQL_* collations are Windows collations, yes? SQLS runsonly on Windows, so am I safe in using Latin1_General_CS_AI? Whatdoes the CP1 in the SQL collation signify? Am I asking for trouble?------------------------------------Assuming that I set Latin1_General_CS_AI (or any other case-sensitivecollation) at the database level, I believe my DDL/DML for that databasealso becomes case-sensitive. How can I specify that I want ONLY my dataaccess to be case-sensitive, and not my DDL/DML? I don't want to haveto remember to type "select * from MyCamelCase" when "mycamelcase"should work.Any help appreciated.A new SQLS DBA..aj
I have installed Sp1 for Sql Server 2005 I also received "locked files" and "reboot required". These errors I can find on all forums and do not worry me anymore. However i still would like to know more about the messages in the log files.
- What does this mean: "Failed to read associated hotfix build information for the following file"...... - What does this men: "Failed to read version information for the following file".........
- Why are some products NOT APPLIED, while in the other log file it says "SUCCES" for the same product? These beneath the 2 log files.
I have an ODBC 3.0 Application on Windows NT Server maintaining aconnection to a SQL Server 2000 database on the same machine. When anerror occurs, I'd like to be able to determine whether the error isserious enough that the database connection has been lost, so I canhave the application try to reconnect automatically. My understandingis that any error of severity 20 or above includes a broken connection,but I'm having difficulty retrieving the error severity through ODBC.So my first question is, how do I retrieve the error severity throughODBC?Also, as a test, I stopped the database while my application is stillrunning, and the first error I received had a native error code of 55.There is no 55 error in the sysmessages table, so I'm confused as towhat the severity of the error is. Can anyone shed some light on why Iwould be receiving an error code that is not in the sysmessages table,and where I might be able to find more information about the error?Thanks in advance,Abram
Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
