Questions About: ACCESS -&> SQL Server
Nov 3, 2004
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 :)
View 13 Replies
ADVERTISEMENT
Apr 5, 2006
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,
View 3 Replies
View Related
Feb 21, 2005
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.
Thankyou
View 1 Replies
View Related
Sep 20, 2004
Hi there!
I was wondering if anyone can help answer a few questions in regards to MS SQL Server.
1. In stored procedure, am I able to select a bunch of records and run through each record and depending on a certain value do an update or insert?
2. Using DTS, am I able to schedule the stored procedure to run at certain times during the day?
Many thanks!
Richard
View 1 Replies
View Related
Dec 23, 2004
Hey all,
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 ?
Cheers,
-Kilka
View 2 Replies
View Related
Jul 23, 2005
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,
View 10 Replies
View Related
Jul 20, 2005
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
View 5 Replies
View Related
Mar 19, 2001
Does anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
View 1 Replies
View Related
Mar 19, 2001
Does anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
View 2 Replies
View Related
Mar 19, 2001
Does anyone know where i can obtain some SQL Server questions to test interview candidates ??
many thanks
Paul
View 1 Replies
View Related
Sep 25, 2006
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 ***
********************************************
View 2 Replies
View Related
Jul 13, 2007
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?
Thoughts on this?
View 3 Replies
View Related
Aug 21, 2007
Hello,
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.
Many, many thanks
Indy
View 4 Replies
View Related
Oct 17, 2007
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
View 2 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
Jun 7, 2007
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
View 1 Replies
View Related
Sep 24, 2006
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:
Create Table xTable
(
UserId
Int ,
UserName
VarChar ( 3 ) ,
Email
VarChar ( 10 ) Default ( UserId + '_' + UserName )
)
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
Exec Sp_GrantdbAccess 'zProgramManagerUser'
Go
Exec Sp_AddRoleMember 'ProgramManagerRole' ,
'zProgramManagerUser'
Go
But Why these lines not work good and give me a
msg with an error the Role name or User Name not in the db
-- Add
New LogIn to ProgramManagerRole
Exec Sp_AddLogIn 'zProgramManagerUser' , '' ,
'C8_Ahmad_13430_NewSIS' , 'English'
Go
Exec Sp_AddRoleMember 'ProgramManagerRole' ,
'zProgramManagerUser'
Go
Exec Sp_GrantdbAccess 'zProgramManagerUser'
Go
Thank you
View 3 Replies
View Related
Mar 11, 2004
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.
Thanks for any input or ideas!
View 1 Replies
View Related
Nov 8, 2004
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 ?
Thanks
Mikhail
View 4 Replies
View Related
Sep 5, 2005
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?
View 1 Replies
View Related
Apr 4, 2006
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
View 4 Replies
View Related
Apr 11, 2006
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.
View 1 Replies
View Related
May 23, 2007
Hey guys,
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!
Chris.
View 12 Replies
View Related
Aug 17, 2005
DESCRIBE in Oracle = ? in SQL SERVER?
Does anyone have a matrix of Oracle to SQL Server common commands?
I am having trouble accessing my company's hosted SQL Server manuals.
TIA
JEJ
View 2 Replies
View Related
Sep 14, 2005
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
View 20 Replies
View Related
May 23, 2007
Hey guys,
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!
Chris.
View 1 Replies
View Related
Sep 13, 2007
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.
Any advice?
Future guru in the making.
View 5 Replies
View Related
Jan 29, 2008
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.
Dim lclRowRet As SqlDataReader
lclRowRet = LoadUserCommand.ExecuteReader(CommandBehavior.SingleRow)
lclRowRet.Read()
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.
Many thanks for your help in advance.
nik
View 5 Replies
View Related
Oct 10, 2007
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
View 3 Replies
View Related
Nov 26, 2005
I'm a long time user of SQL Server 2000 but no knowledge of 2005 Express.
View 5 Replies
View Related
Jun 27, 2006
Hello,
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.
File C:WINDOWSHotfix HotFix.log shows "success"
-------------------------------------------
06/23/2006 13:45:01.131 Product Status Summary:
06/23/2006 13:45:01.162 Product: SQL Server Native Client
06/23/2006 13:45:01.241 SQL Server Native Client (RTM ) - Success
06/23/2006 13:45:01.287
06/23/2006 13:45:01.350 Product: Setup Support Files
06/23/2006 13:45:01.412 Setup Support Files (RTM ) - Success
06/23/2006 13:45:01.491
06/23/2006 13:45:01.584 Product: Database Services
06/23/2006 13:45:01.647 Database Services (SP1 2047 ENU) - Success
06/23/2006 13:45:01.772 Analysis Services (SP1 2047 ENU) - Success
06/23/2006 13:45:01.834 Reporting Services (SP1 2047 ENU) - Success
06/23/2006 13:45:01.866
06/23/2006 13:45:01.928 Product: Notification Services
06/23/2006 13:45:02.069 Notification Services (SP1 2047 ENU) - Success
06/23/2006 13:45:02.147
06/23/2006 13:45:02.194 Product: Integration Services
06/23/2006 13:45:02.225 Integration Services (SP1 2047 ENU) - Success
06/23/2006 13:45:02.256
06/23/2006 13:45:02.319 Product: Client Components
06/23/2006 13:45:02.366 Client Components (SP1 2047 ENU) - Success
06/23/2006 13:45:02.397
06/23/2006 13:45:02.459 Product: MSXML 6.0 Parser
06/23/2006 13:45:02.491 MSXML 6.0 Parser (RTM ) - Success
06/23/2006 13:45:02.553
06/23/2006 13:45:02.616 Product: SQLXML4
06/23/2006 13:45:02.678 SQLXML4 (RTM ) - Success
06/23/2006 13:45:02.725
06/23/2006 13:45:02.772 Product: Backward Compatibility
06/23/2006 13:45:02.788 Backward Compatibility (RTM ) - Success
06/23/2006 13:45:02.803
06/23/2006 13:45:02.850 Product: Microsoft SQL Server VSS Writer
06/23/2006 13:45:02.881 Microsoft SQL Server VSS Writer (RTM ) - Success
06/23/2006 13:45:02.897
06/23/2006 13:45:02.913 Hotfix package completed
06/23/2006 13:59:42.596 Hotfix package closed
-------------------------------------------
But file C:WINDOWSHotfixSQLTools9Logs shows "not applied"
-------------------------------------------
06/23/2006 13:44:33.565 Product Status Summary:
06/23/2006 13:44:33.596 Product: SQL Server Native Client
06/23/2006 13:44:33.627 SQL Server Native Client (RTM ) - Success
06/23/2006 13:44:33.643
06/23/2006 13:44:33.659 Product: Setup Support Files
06/23/2006 13:44:33.674 Setup Support Files (RTM ) - Success
06/23/2006 13:44:33.721
06/23/2006 13:44:33.737 Product: Database Services
06/23/2006 13:44:33.768 Database Services (SP1 2047 ENU) - Success
06/23/2006 13:44:33.799 Analysis Services (SP1 2047 ENU) - Success
06/23/2006 13:44:33.846 Reporting Services (SP1 2047 ENU) - Success
06/23/2006 13:44:33.877
06/23/2006 13:44:33.893 Product: Notification Services
06/23/2006 13:44:33.924 Notification Services (SP1 2047 ENU) - Success
06/23/2006 13:44:33.940
06/23/2006 13:44:33.955 Product: Integration Services
06/23/2006 13:44:33.971 Integration Services (SP1 2047 ENU) - Success
06/23/2006 13:44:34.002
06/23/2006 13:44:34.018 Product: Client Components
06/23/2006 13:44:34.034 Client Components (SP1 2047 ENU) - Success
06/23/2006 13:44:34.065
06/23/2006 13:44:34.096 Product: MSXML 6.0 Parser
06/23/2006 13:44:34.127 MSXML 6.0 Parser (RTM ) - Not Applied
06/23/2006 13:44:34.143
06/23/2006 13:44:34.174 Product: SQLXML4
06/23/2006 13:44:34.190 SQLXML4 (RTM ) - Not Applied
06/23/2006 13:44:34.206
06/23/2006 13:44:34.221 Product: Backward Compatibility
06/23/2006 13:44:34.268 Backward Compatibility (RTM ) - Not Applied
06/23/2006 13:44:34.284
06/23/2006 13:44:34.315 Product: Microsoft SQL Server VSS Writer
06/23/2006 13:44:34.331 Microsoft SQL Server VSS Writer (RTM ) - Not Applied
06/23/2006 13:44:34.362
06/23/2006 13:59:42.424 Hotfix package closed
Any anwers are appreciated
Harry
View 3 Replies
View Related
Aug 22, 2005
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
View 2 Replies
View Related
Jun 21, 2006
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
View 2 Replies
View Related
