Upgrade SQL 7 To SQL 2K: Pros And Cons
Aug 13, 2001
Anyone know where I could find some good articles about pros and cons of upgrading SQL 7 to SQL 2K?
Our boss suddenly got this bug up his rear that we need to upgrade. :(
Probably because we are to a point where everything is running smoothly, and he needs to throw a monkey wrench in it.
We are a very small shop, and are not stressing SQL 7 in the least. I am interested to read some articles about SQL 2K, because right now, I can't see any possible reason an upgrade would be worth doing.
Thanks.
View 2 Replies
ADVERTISEMENT
May 22, 2002
My company is thinking about moving to a product that uses Microsoft SQL Server and I have been asked to find out what are the Pros and Cons of the product, if any.
Any feedback is appreciated.
Thank You
Pam
View 2 Replies
View Related
Nov 3, 2005
I have a requirement where I need to perform a query for positioninformation. But for some types of entries, I need to "expand" the rowto include additional position rows. Let me explain with an example:An index is a security that is made up of components where eachcomponent has a "weight" or a number of shares. So if I have 1 share ofthe index, I have X shares of each component.AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say thatSPY has one component, AAPL, with shares being 10. (1 share of SPY = 10shares of AAPL).So, I do some trading and I end up with positions as follows:+10 AAPL-5 CSCO+2 SPYThe query I need returns:+10 AAPL-5 CSCO+2 SPY+20 AAPL (from 2 SPY * 10 shares)which becomes (after grouping):+30 AAPL-5 CSCO+2 SPY-----------------------------------------Based on that criteria and the following schema (and sample data):-- Drop tablesDROP TABLE [SecurityMaster]DROP TABLE [Position]DROP TABLE [IndexComponent]-- Create tablesCREATE TABLE [SecurityMaster] ([Symbol] VARCHAR(10), [SecurityType] VARCHAR(10))CREATE TABLE [Position] ([Account] VARCHAR(10), [Symbol] VARCHAR(10), [Position] INT)CREATE TABLE [IndexComponent] ([IndexSymbol] VARCHAR(10), [ComponentSymbol] VARCHAR(10), [Shares] INT)--Populate tablesINSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')INSERT INTO [Position] VALUES ('001', 'AAPL', 10)INSERT INTO [Position] VALUES ('001', 'MSFT', -5)INSERT INTO [Position] VALUES ('001', 'CSCO', 10)INSERT INTO [Position] VALUES ('001', 'SPY', 15)INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)INSERT INTO [Position] VALUES ('002', 'APPL', 20)INSERT INTO [Position] VALUES ('003', 'SPY', -2)INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)-- *****************************-- Based on the rules:-- 1) Index positions appear like other positions (account /symbol) pair, but-- its components show up as new rows of account (of index),symbol (equal--to component symbol), position (equal to shares * index position)-- 2) One row for each account / symbol pair (GROUP BY account andsymbol, SUM position)-- Expected output (without grouping) (sorted by account / symbol)-- 001 AAPL 10-- 001 AAPL 375 (component shares * index position) (25* 15) (SPY)-- 001 AAPL 693 (component shares * index position) (33* 21) (QQQQ)-- 001 CSCO 10-- 001 CSCO 750 (component shares * index position) (50* 15) (SPY)-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50 (component shares * index position) (25* -2) (SPY)-- 003 CSCO -100 (component shares * index position) (50* -2) (SPY)-- 003 SPY -2-- Expected output (with grouping account / symbol) (sorted by account/ symbol)-- 001 AAPL 1078-- 001 CSCO 760-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50-- 003 CSCO -100-- 003 SPY -2---------------------------------------------Is a UNION the best way to perform the query. What are the pros andcons? What, if any, is a better way?SELECT[Account], [Symbol], SUM([Position]) AS [Position]FROM(SELECT[Account], [Symbol] , [Position]FROM[Position]UNION ALLSELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *IC.[Shares]) AS [Position]FROM[IndexComponent] ICJOIN[Position] PONP.[Symbol] = IC.[IndexSymbol]) DGROUP BY[Account], [Symbol]ORDER BY[Account], [Symbol]
View 5 Replies
View Related
Jul 20, 2005
Hi alli am building a SQL 2000 database that it is proving a littlechallenging, i have companies with multiple addresses, phone numbers,owning mine sites etc and also joint ventures so maybe you get thepicture with a few design issues that i ma encounteringMy queriy is about a primary key identity, and which one to use withrespect to either the identity data type or the unique identifier ,I am aessentiall building an address table to hold all multipleaddresses as well as phone numbers etc, so my desire to have a uniqueidentity for each record is very important.My view is i will run in to violation errors by just using the tableidentity data type, i could i suppose use composit primary keys butthat may have a performance impact, although thiis will not be a hightransaction database.Does anyone know about performance issues regarding each identitysolution, by using a generated 16 bit identifier there are going to behuge numbers for the DB to verify. or am i worried about nothing?any views greatly appreciatedregardsGreg
View 17 Replies
View Related
Mar 28, 2004
can anyone explain the Pros and Cons of Stored Procedures ??
thanks
View 2 Replies
View Related
Sep 19, 2007
Im about to start converting code to Stored Procedures for all my reports in Reporting Services. I was wondering what the pros and cons of this may be.
View 17 Replies
View Related
Sep 11, 2007
Thanks for the help on the previous thread.
It seems to me that either by accident or design, SQL Server tends to steer you away from backing up directly over the network.
Are there reasons for not doing this because you obviously don't want to leave your backups on a local drive in case the drive fails?
Some possibilties that I can think of are:-
1. Local drives have faster access times and SQL backups can get quite large. I did a quick test and found that a netwrok backup takes 2 to 3 times longer than it does on a local drive.
2. Backing up on the netwrok could hog too much bandwidth. I haven't tested this and would be surprised it it's true.
3. There could be some reason that you don't want the Server and Agent services running under a domain account but want to leave them on the Local System account. I am not aware of any such reasons by the way.
4. Local drives persumably have a slightly higher availability than network drives. If the server is running, the drive should be available.
View 8 Replies
View Related
Apr 4, 2007
Like in the subject: What are the cons and pros of using nvarchar(max) versus ntext?
Does it have something to do with having to enable full text search perhaps in the latter case?
View 2 Replies
View Related
Jun 27, 2004
I'm just wondering what any pros and cons of using multivalue columns in a database are.
I'm designing a database which will have a column for FABRIC_TYPES_AVAILABLE for a certain FURNITURE_ITEM. Each FURNITURE_ITEM can have multiple FABRIC_TYPES_AVAILABLE of course. So I was just going to store a 2 or 3 digit number of the FABRIC_TYPES_AVAILABLE in that row. So I would have something like....34,24,453,32,23,45,67,65,43,21,21,45.
Anyway....thanks in advance for any information. Links I could read would be great too...b/c I did do a bit of searching, but didnt find much.
View 6 Replies
View Related
May 9, 2006
I have a main database...for this large Web site...and Im wondering
what would be the PROS and CONS of using another database (located on
the same, or on another SQL Server). Im just thinking this would be
good incase we ever needed to take some load off one of the servers.
Also, we will be integrating Community Server into this Web site. Of
course you know CS adds its own database objects which crowd up our
main database objects.
We were thinking of giving CS its own database also; bad practice, or....it doesn't matter much?
Thank you
View 3 Replies
View Related
Aug 17, 2006
I was wanting to know. I am making a site that might be come big. And me and this dude are considering pickering abotu GUID. I don't want to use them but he does. And I was wondering what should we do? I know nothing about guids
View 1 Replies
View Related
Jun 15, 2006
vinod writes "Q1>Should I apply filter in sequence(based on primarykey,not null,comparision,between clause) ?
-How to apply filter in correct format,and SQL server internally execute it.
Q2>Should I use 'is not Null' to be put at the last?
Q3>Should I use 'between clause' rather than relation operator i.e(empid>10 and empid<200)
Q4>Does filters of sequence has any impact on the query execution process
table1[pkey1,col1,col2,col3] -->pkey is pkey1
table2[pkey2,pkey1,col11,col12,col13]--->pkey is pkey2, and pkey1 is foreign key
CaseI->select table2.pkey2,table1.col1 ,table2.col12
from table1 inner join table2
on table1.pkey1=table2.pkey1
where (table1.col3>100 and table1.col3<300 ) and table2.pkey1=2020 and table2.col13 is not null
CaseII->select table2.pkey2,table1.col1 ,table2.col12
from table1 inner join table2
on table1.pkey1=table2.pkey1
where (table1.col3 between 101 and 299 ) and pkey1=2020 and table2.col13 is not null"
View 1 Replies
View Related
Oct 26, 2007
Are there any vices to using default constraints on all columns in your table.
For example an Int that defaults to 0
or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad?
Thanks
Darin Clark
View 10 Replies
View Related
Jun 19, 2008
Can someone provide information or a link to information regarding the pros and cons of saving files directly into a SQL 2005 database?
I'm actually for saving files to a database (cleaner implementation then just saving the location then having to get the file, etc), but my project manager is not convinced so I need to make an argument for (or against depending on what I actually find out) using varbinary data type.
Thanks.
View 2 Replies
View Related
Jul 20, 2005
Hi All!We are doing new development for SQL Server 2000 and also moving fromSQL 7.0 to SQL Server 2000.What are cons and pros for using IDENTITY property as PK in SQL SERVER2000?Please, share your experience in using IDENTITY as PK .Does SCOPE_IDENTITY makes life easier in SQL 2000?Is there issues with DENTITY property when moving DB from one serverto another? (the same version of SQL Server)Thank you in advance,Andy
View 49 Replies
View Related
Feb 26, 2008
hi all,
I was wondering if anyone knows of any pros/cons on storing SSIS (2005) packages within SQL Server 2005 SP2.
We're contemplating the migration/storage of a large number of packages, a minority of which designed in dts (SQLServer 2000) and using third party activx components.
thanks much for any feedback,
Cosmin
View 3 Replies
View Related
Apr 20, 2001
We are in the process of replacing our primary production server. In the process of determining how SQL server is going to be structured, it has been suggested that I place all current and new indexes on a separate file group. These filegroups would then reside on a separate shelf on the server. What are the pros and cons of doing this?
View 2 Replies
View Related
Jun 27, 2007
I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
View 1 Replies
View Related
Mar 30, 2008
Hi, I need a help with a SP, professionals only. If you're interested PM/email me please.
View 3 Replies
View Related
Mar 10, 2000
Let's say I have a trigger on my orders table.
When this fires a query is done on the customer table to get some values
and put some order/customer data into an audit trail table.
In one period of time, it appears that the trigger did not put the data into the separate table. No known reason.
Suspicion: What if the customer table were locked by some process when the trigger fired. Maybe the trigger was chosen as a deadlock-victim.
?? I guess I need to check for @@ERROR during the trigger and do something. Any suggestions? I think I can raise the priority of my trigger to "win" during a deadlock.
If trigger activity is chosen as a deadlock-victim, can the trigger make another attempt to complete it's activity?
View 1 Replies
View Related
Nov 29, 2006
When attemping to install SQL 2005 Express using the following command line arguments
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE
or
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE SECURITYMODE=SQL SAPWD=MY_PASSWORD
the following error occurs.
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
View 4 Replies
View Related
May 13, 2008
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet
**********************************************************************************
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet
05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug"
05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation.
05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
Any help would be appreciated.
View 3 Replies
View Related
Jan 19, 2007
Hi,
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------
Program Location:
at System.Xml.Schema.SchemaInfo..ctor()
at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable)
at System.Xml.XmlReaderSettings.get_Schemas()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh()
at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
Greatly appreciated for any kind assistance.
Thanks.
View 1 Replies
View Related
Feb 10, 2007
Hi,
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log:
Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Database Mirroring Transport is disabled in the endpoint configuration.
Starting up database 'master'.
Converting database 'master' from version 589 to the current version 611.
Database 'master' running the upgrade step from version 589 to version 590.
Error: 946, Severity: 14, State: 1.
Cannot open database 'master' version 589. Upgrade the database to the latest version.
System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause.
Error: 15151, Severity: 16, State: 1.
Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
Any help would be greatly appreciated
Thanks KR
View 7 Replies
View Related
Oct 11, 2000
I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
Any comments are appreciated,
-Marc
View 3 Replies
View Related
Dec 6, 2006
Greetings,
I have a client whose DBA upgraded their SQL 2000 database to SQL 2005. They still have it running in 8.0 compatibility mode, for various reasons, and would like me to help them develop a migration strategy to get them to full 9.0 compatibility.
My question is: can I run the Upgrade Advisor on a SQL 2005 engine database running in 8.0 compatibility mode and get anything useful? I already know there will be some issues with existing DTS packages but it would be nice to catch any other issues in advance.
I know that the Upgrade Advisor was run before the upgrade took place but to my knowledge the report was not saved and none of the recommendations were followed.
Any help would be greatly appreciated.
Cheers,
Lon
View 1 Replies
View Related
Jun 1, 2007
SQL 2000 -> SQL 2005 upgrade on windows server 2003 ... We've done numerous side by side types of upgrades which have the advantages of being able to migrate things at a slower pace with limited downtime. I have a situation where it seems like it would be the least amount of work to do an in place upgrade involving 22 transactional in-house developed databases and associated in-house developed applications. There's no replication, no DTS packages, nothing I can think of that would complicate the upgrade. I was wondering if there's people out there that have done lots of these and haven't had any problems OR if there have been lots of problems. Anyway, any advice would be appreciated.
John
View 1 Replies
View Related
Mar 13, 2001
Hi,
I plan to move my servers from Windows NT and SQL 7 to Win2K Advanced Server and SQL 2000. I am not going to upgrade the windows server, as I have been told this isn't a good thing. Therefore, I plan to detach my databases, install win2K, install SQL 2000, and then attach the databases. I've read that this works fine, but I am curious as to whether or not I can do this with master and msdb(as I want to keep all logins, jobs, etc). Is this possible? Also, is my plan decent? it seems to be the easiest way to me...
Thanks!
View 2 Replies
View Related
Jan 8, 2002
Hi All,
I am upgrading sql 6.5 to 7.0. The 6.5 database has 3 data files. Do I need to create 3 datafiles in version 7.0 after the server has been upgraded? Before I do the upgrade on the production server, I want to do a test on another machine loaded with sql 6.5, Do I need create 3 data files on 6.5 to to load the database from the production server before I upgrade the test machine to sql 7.0?
Thanks in advance,
Jason
View 1 Replies
View Related
May 6, 2002
I get this error when I try to run a query. I just upgraded to SQL 7.0
ERROR connecting to local server ...
Error Number Error Text
0x8004D903 [SQL Server] You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to version 7.0 (SQLDMO) to connect to this server.
Thanks
View 1 Replies
View Related
Apr 11, 2001
I HAVE AN NT 4 SERVER WITH SQL 7. WHAT WOULD BE THE PROPER STEPS TO
UPAGRADE THAT AS A WINDOWS 2000 SERVER WITH SQL 2000.
SHOULD I UP THE OS FIRST ?? ETC
ANY INPUT IS MUCH APPRECIATED.
-MIKE
View 1 Replies
View Related
Apr 12, 2001
When I upgraded my server from SQL-6.5 (service pack 5a) to SQL-7.0, I got an error message like this :
<<
Error:
Microsoft SQL-DMO (ODBC SQLState: 42000) returned error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'where'.
The replication upgrade task was not able to completely transfer your existing replication configuration settings to 7.0 server. Following the completion of upgrade, it is recommended that you use the Microsoft Management Console in Sphinx to re-establis>>
Has any one come across this ? The existing setup is using the transaction replication with 5 other servers. In 6.5, it is functioning well.
Rgds
Armstrong
View 2 Replies
View Related
May 7, 2001
I have an NT4 SQL7 server ( no clustering, replication ) and planning to get that to W2K SQL 2000. What would the best and safe to do that ?
Sharing your ideas is much appreciated. Pls. give details.
Thanks,
Mark
View 1 Replies
View Related
