Sql 2000 Compared 2...
Apr 2, 2004anyone know reasons why sql server 2000 is a better choose for db creation instead of say oracle and mc access???
THANK YOU
ADVERTISEMENT
VERY Slow Generate Scripts On SQL 2005 Compared To SQL 2000
Aug 1, 2007i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.
can anyone tell why ? thx in advance.
Stored Procedures On SQL 2000 Compared To Dotnet's Datatable.select
Jul 20, 2005Hi,Has any one ever compared the performance of calling a DataTable'sSelect method with a stored procedure doing the same thing?My point is:dataRows = DataTable.Select(filter) is better orPassing paramters to stored procedure?The datatable holds about 500-700 rows at any given time.If I select one of the approaches the business logic will go intorespective layers.With dotnet in picture what would be a good approach- Have the data in Datatable and do a filter on the data or callstored procedures which has been the convention.Can some one pl. suggest?
View 3 Replies View RelatedData Transfer From Lotus Notes Very Slow Compared To SQL 2000 DTS
Sep 7, 2006To extract data from an ODBC source, try the following:
Add an ADO.Net Connection Manager.
Edit the Connection Manager editor and select the ODBC Data Provider
Configure the Connection Manager to use your DSN or connection string
Add a Data Flow Task to your package.
Add a Data Reader Source adapter to your data flow
Edit the Data Reader source adapter to use the ADO.Net connection manager that you added.
Edit the Data Reader source to query for the data you wish to extract.
hth
Donald
Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.
When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.
Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?
Thanks!
SQL 2000 On Win NT Server Compared To Win 2K Server
Jul 19, 2001Are there any signicant advantages on changing the operating sytem to Win 2k server for a SQL 2K SQL server. Or drawbacks to running SQL2k on Win NT.
Jim
SSIS Compared To DTS?
Dec 7, 2007There is no doubt that SSIS has been designed to build on the lessons learned from DTS but in one respect I think that there may have been a backward step and that is ease of use. A big attraction (a hallmark even) of DTS was that you could configure a package to move data between data sources and destinations in minutes.
When you first start using SSIS this is far from being true. I tended to get a lot of errors (many of them not very helpful) relating to stored procedure parameters, truncation of data etc. Looking around this Forum I think that I am not alone. This is partly a learning curve issue but I think that the learning curve is much steeper for SSIS.
.
This is not a plea to go back to DTS, we must move on, but I would like to think that there will be an emphasis on improving ease of use for SSIS going forward.
For the record two general improvements that I can see in SSIS (over DTS) are:-
1. A more logical structure.
2. The ability to easily set any Package Property, not just Variables at run time.
Two Strings To Be Compared Have Different Collation.
May 10, 2006I've written a stored proc which passes in an SqlString parameter and compares it with an SqlString read from an SqlDataReader.
I get the following exception:
System.Data.SqlTypes.SqlTypeException: Two strings to be compared have different collation.
Any ideas on collation inside CLR stored procs?
Thanks.
SQL 7 High Page Faults/sec Compared To 6.5 ?
Aug 11, 1999I am running an application on one NT Server, running against SQL Server 6.5 sp 3, and SQL 7 with sp1 applied.
The application is a 'data migration' type application - ie heavy insert and update workload - against many (50+ tables) with many different SQL statements.
The SQL 7 server is configured with 'floating' memory.
On SQL 7 - I am experiencing very high page faults/second for the sqlservr process - sometimes peaking at over 1,000. I was under the impression any number greater than 10 indicates a problem with system performance.
The same application, same data, same NT configuration etc against SQL 6.5 does not page fault. SQL Server 6.5 completes the work faster than 7.
Could anyone help me understand what's going on ?
Thanks in advance.
WHILE Loop Speed Compared To Cursors?
Jul 23, 2005Working on some new code, I'm coming across WHILE loops used instead ofcursors. I was curious if anyone had any stats on how the speed ofdoing this compares to the speed of a cursor. I typically avoidcursors for performance sake, but I'm not sure how this avoids thespeed hit of a cursor, since it's doing essentially the same thing.Many thanks.
View 4 Replies View RelatedData Access Very Slow In .net As Compared To VB
Jan 9, 2006
Hi,
I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.
I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.
I am using DataAdapter.FiLL method to fill the datatable.
I have tried.
Using DataReader ( Made the things worse )
Using BeginLoadData and EndLoadData
Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.
But i feel now that whatever is done. the problem is with the fill method only?
Is there any alternative?
Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.
Shri
Log Sent Rate Is Low As Compared To Log Bytes Flushed/sec
Jun 12, 2007Hi,
We have Asynchronous Database Mirroring on SQL Server 2005 SP2 Entprise Edition/Windows 2000 Advanced Server. We noticed that log sent rate is quite low (average 1.3 MB/sec) in most of the cases whereas "Log bytes flushed/sec" is high (1.4 MB/sec) as a result Log send queue keeps on increasing and finally taking all the transaction log space. Our disk queue length is always in range of 0.01. And prinicipal and mirror servers are on local LAN.
I tried on low end server and high end server and in both cases Log sent rate is approx 1.3 MB/sec (Maximum 4 MB/sec).
Is there any limitation on Log sent rate?
How can we improve on log sent rate? Since both servers are on local LAN, network bandwith does not seems to be an issue.
Any help is greatly appreciated.
Thanks,
Ramesh
VERY Slow Generate Scripts On SQL 2005 Compared To
Jun 10, 2008i was using sql 2000, the database contains 500+ tables, 3000+ sp.
i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts).
i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes.
i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes.
average generate script time with sql 2000 (sp 4): 2-3 minutes.
can anyone tell why ? thx in advance
MSSQL ODBC Difference Compared To MySQL
Mar 6, 2007The source for this problem can be found http://www.wellytop.com/SQLProblem.zip
This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.
The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.
I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.
To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.
To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.
Transact SQL :: Text Data Type Cannot Be Compared With Distinct
Oct 9, 2015Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.
But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
DECLARE @c NVARCHAR(MAX)
WITH c1 AS (
SELECT [abcd] AS table_name
FROM [intranet].[dbo].[soccerfieldinfo]
where [abcd] IS NOT NULL
), c2 AS (
SELECT Row_Number() OVER (ORDER BY table_name) AS r
[Code] ....
Adding Logins Via SSEUtil Compared To SQL Server Management Studio
Feb 7, 2007Hello all,
I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:
USE [DBName]
GO
EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO
USE [master]
GO
EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO
CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBName]
GO
EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO
ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO
EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO
EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO
Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.
Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAMEInstance -run USERS.SQL), it processes everything, except the Logins.
This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.
Am I on the right track? Or is there another way to automate the adding of Logins?
Thanks in advance,
DSXC
Power Pivot :: Find Percent Of One Of Last Occurrence Compared To All Last Occurrences (DAX)
Jun 3, 2015I have a fact -REVENUE table of accounts, each account can have multiple instances . And I have an Aggregate that summarize the latest occurrence of a revenue for each account ( in a chosen Period ) :
AccountLastRevenue:=SUMX(
VALUES('scd_FactAccountRevenue'[Account]),
CALCULATE(
SUM('scd_FactAccountRevenue'[Revenue]),
LASTDATE('scd_FactAccountRevenue'[Revenue_Date]) )
)
How can I find the percent of one of the lastest accounts Compared to all accounts? Assuming i have connect Dim_Time ( Y-Q-M-D ) to Revenue_Date, how can i find the percent of one Month Compared to all the months in the Quarter ( And so on hierarchies ) ?
scd_FactAccountRevenue:
powerpivot :
ADO.NET Returns Different Colum Value When Compared To View Results In SQL 2005 Management Studio
Feb 7, 2007I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
Report Running Very Slow Compared To Query Analyzer - High TimeDataRetrieval
Jun 26, 2007Hi,
I have a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!!
I have checked the execution log on the RS using the below sql:
Code Snippet
use ReportServer
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout:
TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
601309 2227 3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about 400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this to retrieve data.
The report is very simple - it basically returns the records straight out into a table.
The only thing I somewhat suspected was a parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't.
Any help would be greatly appreciated.
Error Description Differs When Logged With Redirect Rows Compared With Debug Mode
Jan 18, 2007Hi,
Can any one please tell me how to get the complete error description for example when i dont Redirect Row for Error in OLEDB Source i get a detailed error message with column name as
[RCheck [385]] Error: There was an error with input column "CHECK_STATUS" (456) on input "OLE DB Destination Input" (398). The column status returned was: "The value could not be converted because of a potential loss of data.".
But when I set Redirect Row for error and use the Script component to log them into a Table with ErrorDescription based on ErrorColumnID it only gives me this.
The data value cannot be converted for reasons other than sign mismatch or data overflow.
Thanks
Sat
Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?
Nov 15, 2007do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
T-SQL (SS2K8) :: Varchar Datatype Field Will Ignore Leading Zeros When Compared With Numeric Datatype?
Jan 28, 2015Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
Where Statement Compared With Join Statement
Jan 11, 2006for complex views should I use "where" statements or "joins" in terms of performance?
Which one is faster?
DB File Size Limit With SQLServer 2000 In Small Business Server 2000
Mar 15, 2006Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies View RelatedSql Server 2000 Developer Edition W/MSDE 2000 Release A Install
May 21, 2005Can you install Sql Server 2000 Developer Edition with MSDE 2000 release A already installed?
View 2 Replies View RelatedMicrosoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
Feb 17, 2004My objective is to use Enterprise Manager to move (copy) my SQL db from the server to my windows desktop computer.
I downloaded MSDE and am having trouble installing it, no doubt because I do not understand the documentaion (ReadMeMSDE2000A.htm).
When I try to run setup, I get that message that says:
"A strong SA password is required for security reasons. Please use SAPWD switch to supply the same."
Considering my purpose, do I need a "strong" SA password? If not, how do I get around it? If yes, how do I set it up?
I am a Mac user so I have poor windows skills, please make it as painless as possible for me, thanks!
Ron
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release
Feb 16, 2006venu writes "Hi,
Am very new to MS SQL adminstration
Can anybody help me out how to work on Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A just for the practice.
The activity which am going to workout on MSDE is below.
How to install SQL(on XP)
How the layout will be(like if i insall MSDE what are all Application will be and how they depends on each other)
How to create/delete tables if so, how can we do it either by GUI or CUI
just i need a clarifications reg same
Thank you,
venu"
Performance Issues - Access 2000 Frontend SQL Server 2000 Backend
Jul 23, 2005Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies View RelatedVista Business ODBC/DSN To Remote Windows 2000 Server / SQL 2000
Oct 8, 2007Hi,
Just upgraded some development desktops to Vista Business. However we need
to still connect to some older remote windows 2000/SQL 2000 servers.
Trying to setup an ODBC system DSN on our Vista Business local desktop we get the
following errors -
-START ERROR WINDOW-
Connection Failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(SECDoClientHandshake()0.
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error
-END ERROR WINDOW-
Any help greatly appreciated as this is stopping us from making
database/table connections etc. We've checked the firewall setup and all is well there.
PS - we can still connect fine using XP or windows 2000 desktops and their
local DSNs.
SQL 2005 Thinks A SQL 2000 Backup Is Corrupt, But SQL 2000 Restores Just Fine
Jul 19, 2007I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Maximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)
May 19, 2008Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedListing Users In A Winwos 2000 Group For Sqlserver 2000
Jan 25, 2005Hi, I wanted to see what are all the users in a windows nt group that has a group access to sql server 2000. I have a windows 2000 group access to sqlserver 2000 as "xxxsomegroup". How can I list all users that belongs to this windows 200 group? is there any stored procedure to find out this?
any information could be greatly appreciated.
thanks
Reconnecting Access 2000 Front End To New Instance Of SQLServer 2000
Jul 27, 2004I recently had to reinstall a new instance of SQLServer 2000, but was unable to use the previous server name. As a result, my Access2000 front end is not happy with it's linked tables. I can't seem to find anyplace within Access to universally change the address of the SQLServer used as the back-end for all linked tables.
When I do try to access the linked tables through Access, I get an error, and the option to change the server location. When I try to type-in the new SQLServer location, there is an attempt to reconnect to SQLServer, but a whole lot of errors are generated, and none of the data is transferred into the Access table.
I really don't want to have to re-do my Access front end, so it seems it would be easiest to somehow reinstall SQLServer to have the same server location it used to. Is there a good way to completely erase all traces of SQLServer so that I can have better luck reinstalling it to the same location it used to be in? Just using the uninstall program from SQLServer doesn't seem to be cutting it.
Thanks!