Hi,
Has anyone worked on a system that needs per day around 1 Million writes, almost that many reads and deletes once every 1-2h. File sizes range from 2KB-1MB only that 85% of the files are in the 1-8KB range.
This is all done in a single table with a GUID key.
If yes could you tell me what problems did you encounter, any suggestions etc.
Thank you in advance
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with a column named
PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from
tblPercent.
I am getting an error message for the following query.
Have Two tables
1.CriticalDoctors
2.tblPercent
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated.
Pls give me reply on how to write a stored procedure so that I can equate the percentage column
with one value and also check the condition with one value.
Thanking you guys in advance.
madhav
In
one of our forth coming projects, with ASP.Net/C#/MSSQL Server, We have
to deal with a Business table having about 15 millions of records. We
want to know, that which methodologies should we adopt, both regarding
front end and back end perspective, so the site could give optimised
performance. Also in place of a Dedicated Server, the Hosting Company
provides MSDE (that come with .net). Will this create any problem with
this project, that have such a huge table? Should we go for some
advanced database technique, such as, Clustering, Spliting Tables, etc.
Followings are the fields that the business table contains:
ID, Category ID (which comes from a Category table, each business is
under a category), BusinessName, SignupDate, Address1, Address2, Phone
Number,
Hours Of Operation, Years in Business, LicenseNumber, DiscountCoupon, Website
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedHi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
I have an SSIS package that is creating an Excel worksheet and writing data to it. It works fine when i run it inside Visual Studio. But when it runs as a scheduled job it writes the header and no data. I turned on logging and the log even says it is writing the 10,456 rows that it should be.
But they are not showing up in the Excel document. The job is setup as 32 bit and writing to Excel 97-2003. The job ends normally and does not generate any type of messages that are out of the ordinary. This is running on SQL Server 2008r2.
I have a set of parent/child records that need to be exported to a space delimited Flat File. Each parent record must be followed by 3 child records, each on their own line with different format.
I have a prototype using the Derived Column component that concatinates the various fields of each record into one "wide" text column. This fools SSIS to think that each row has the same format. Then I merge them together using an artificial sort id. But this seems overly tedious and very brittle.
What would be the best approach to writing these records out? I'm hoping there is a better more maintainable method.
Thanks,
Jon
I am developing an application in vb.net 2005 using SQL Server 2000.
In this I have two tables SessionMaster and SessionChild.
Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type,
Fields of session child - SessionChildId, SessionMastId, UserName, Comment.
SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields.
Please how to write trigger to insert record into both tables at a time.
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
I am having a problem with an ASP program that inserts data into a table on
SQL Server 2000.
No error msg is returned upon submission and the confirmation msg that
displays after the commit command is sent to the server displays, but when
we go to the DB, the data sent isn't there. This is an occassional
occurance and usually the data is there, just some times, it isn't. Other
forms function just fine, using the *exact* same file to perform the submit
function (all the forms "include" the same submit page). The only
difference we can find is a trigger on the table having problems which
executes upon update, capturing the information about who updated the record
when. From what we can see, this is the only programmatic difference. The
other thought tickling our minds was the possiblity of a simultaneous
submission, since all the users submit with the same db user name via the
form, if user 1's data gets written but not yet commited, user 2's data is
submitted, then the commit transaction is submitted by user 1 as the program
steps run in sequence, would the commit by user one cause eiither of the
records inserted but not commited to be lost? If so, why wouldn't that be causing problems on other forms ...
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies View RelatedHello,
Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?
Thanks.
Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:
SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC
Where this statement would return only records 2000 to 5000 of the returned results.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies View RelatedHi,
How does one install MSSQL 2000 on a machine that's already running MSDE 2000? Do i need to unintstall MSDE, or both can be installed seperately??
Is there a way to simply upgrade MSDE into MSSQL 2000?
Thanks!
Hi,I have an Access application with linked tables via ODBC to MSSQLserver 2000.Having a weird problem, probably something i've done while not beingaware of (kinda newbie).the last 20 records (and growing)of a specific table are locked - cantchange them - ("another user is editing these records ... ").I know for a fact that no one is editing records and yet no user canedit these last records in the MDB - including the administrator -while able to add new records.Administrator able to edit records in the ADP (mssql server) where thetables are stored.Please help, the application is renedred inert .Thanks for reading,Oren.
View 3 Replies View RelatedI have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies View Relatedhelo guys,
can someone send me the script/procedure in performing bcp on ms sql 2000? pls help me, i badly need it.. thank u so much!:)
HelloI watchet some posts but did not find answer to my question : how toimport data from xml into mssql 2000 using t-sql?i tried:USE Northwindif(object_id('dbo.test_xml') is not null)drop table dbo.test_xmlgocreate table dbo.test_xml (Id int identity(1,1), col1 text)goinsert into test_xml(col1) values('')godeclare @cmd varchar(512)set @cmd = 'D:Progra~1Micros~3MSSQLBinnTextCopy.exe /S /U[user] /P [password] /D Northwind /F c:pobierz.xml /Ttest_xml /C col1 /I /W "where Id = 1"'exec master..xp_cmdshell @cmd, 'no_output'godeclare@hdoc int, @doc varchar(1000)select @doc = col1 from dbo.test_xmlexec sp_xml_preparedocument @hdoc out, @docselect @hdoc as hdoc-- ...select * from openxml(@hdoc, ...--...exec sp_xml_removedocument @hdocBut my documet have more than 1000characters and more than max varchar. Sohow to do that?Best regardsAJA
View 4 Replies View RelatedMSSQL 2000 Replication between two servers on a workgroup
Is it possible? How do you set it up?
I am using Visual Studio 2005 and MSSql-2005 Server. But i also want to add MSSql-2000 Server Database.
Both the servers are running but when i provide the connection string of MSSql-2000 in SqlConnection to access data base, i get an error. It says like MSSql-2005 is not allowing to access.
So, How can i add MSSql-2000 to Visual Studio-2005???
Hi,I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.What is the most efficient way to do this using C#2005 and mssql2000?Any good article or tutorial?
View 2 Replies View Related:confused:
I have a single table "amount" which has only three columns such as memberid referenceid and AMOUNT with following data ........
memberid referenceid AMOUNT
L001 L001
L004 L003
L003 L002
L002 L001
and so on
my needed query willbe if i choose L003 from referenceid the query should select the column where it was a memberid and again it choose the same row refernceid of(L003) as L002 where L002 was a memberid and choose L002s referenceid which is L001
Finally i should REACH the memberid(L001) and
the other one i need is i should return the count of no of rows crossed ,
and my biq question is everytime the query gets into new row i should add some data in amount (50,25,12.5,10,7.5,5,4,3,2,1)for (immediate row,next row,next row and so on)
how can i do this in MSSQL2000??
I have crossed accross many forums pls help im stuck up
:(
Hi,
I am now trying to configure Log Shipping in my compeny but I have encountered some difficulties. The destination site and the source site are far from each other and the transaction logs are too big, this situation causes an huge overload on our private network (we use WAN which is complitly disconnected from the internet). Is there any way to compress the transacion logs with zip or rar and combine it in the log shipping mechanizem?
Is there another way for making this files smaller?
I would be greatful for any ideas in this matter.
Thanks in advance,
Zvi Gilinsky
Dear Community,
I have recently downloaded the MSSQL 2000 Evaluation package from microsoft website,but i can't get it to install.I run the autorun normally,but when i select the option install database server nothing happens.I am trying to install it on Windows XP Service Pack 2 Edition 5.1....As this would help,in 3-4 hours of computer inactivity,i see the setup loader but nothing happens,it says not responding.
Thanks in advance,
Dimitris.
Yao,
Could anyone help me with, or point me to information regarding, scheduling jobs for MSSQL 2000? I need to execute a SQL Command, monthly, which sets a budget field to a fixed ammount, something like: UPDATE Programs SET Budget = 500.
--Kyle Johnson
hi guys, are there any official or some sort of documents to talk about the advantages and disadvantages of storing image in the SQL's Image datatype field or storing the pointer to the image file? i have lots of static images (no more than 1MB each) that requires little updating and i need to retrieve them constantly. i am really struggled here on which method i should store the images: to store the images as Image datatype in SQL or save them as files and store the paths to the files. i would like to know, in my case, would there be any performance and efficient difference on using either method?
naturally, i'd think the 1st method seems like a better solution. it's easier to back up and it protects integrity. But at the same time, i am not sure if the constant inserting and retrieving of images will affect the performance? also, i want to share the images and may create a ticker thing (using JavaScript) to display the images on the web page randomly and it seems like the 2nd method is easier to program. if you were me, which method would you use and why? thanks!
hi, i'm monitoring a SQL server 2000 and every 5 minutues aprox. a dbcc scan is triggered on 2 databases only.
how can i deactivate this scans or how can i define a longer period of time between them?
thanks
Hi,
I am installing MQQSQL2000 onto NT4 SP6. Installation prompts me this msg "Setup failed to configure the server". I click on, it terminate.
Below is the sqlstp.log file for reference.
Any one got any idea ?? The error at the last few lines.
TQ
13:01:27 Begin Action BuildServer:
13:01:27 C:TEMPSqlSetupBinscm.exe -Silent 1 -Action 5 -ExePath "C:Program FilesMicrosoft SQL ServerMSSQL$MOMSERVERinnsqlservr.exe" -Service "MSSQL$MOMSERVER"
13:01:27 Process Exit Code: (0)
13:01:27 Begin Action: CreateRegistrySetSQL
13:01:27 End Action: CreateRegistrySetSQL
13:01:27 Begin Action: RegWriteSetupEntry
13:01:27 End Action: RegWriteSetupEntry
13:01:27 Begin Action: CreateSer
13:01:27 End Action: CreateSer
13:01:27 Begin Action: SkuIt
13:01:27 End Action: SkuIt
13:01:28 SetFileSecuritySQLAndAdmin for C:Program FilesMicrosoft SQL ServerMSSQL$MOMSERVER returned: 0, 0
13:01:28 SetRegSecuritySQLAndAdmin for SoftwareMicrosoftMicrosoft SQL ServerMOMSERVER returned: 0, 0
13:01:28 Begin Action: UpdateSystemPath
13:01:28 Path successfully updated.
13:01:28 C:PROGRA~1COMMON~1UeWI;C:PROGRA~1UeWISYS;C: PROGRA~1UeWICommon;C:PROGRA~1UeWI;%SystemRoot% system32;%SystemRoot%;C:Program FilesUeWIReconin;C:Program FilesUeWIRecondll;C:Program FilesMicrosoft SQL Server80ToolsBinn
13:01:28 End Action: UpdateSystemPath
13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0
13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0
13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0
13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0
13:01:30 C:Program FilesMicrosoft SQL Server80ToolsBinncnfgsvr.exe -F "C:WINNTsqlstp.log" -I MOMSERVER -V 1 -M 0 -Q "SQL_Latin1_General_CP1_CI_AS" -H 65908 -U sa -P
################################################## #############################
Starting Service ...
SQL_Latin1_General_CP1_CI_AS
-m -Q -T4022 -T3659
Connecting to Server ...
driver={sql server};server=SG-ISDEV-1MOMSERVER;UID=sa;PWD=;database=master
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
driver={sql server};server=SG-ISDEV-1MOMSERVER;UID=sa;PWD=;database=master
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
driver={sql server};server=SG-ISDEV-1MOMSERVER;UID=sa;PWD=;database=master
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
SQL Server configuration failed.
################################################## #############################
13:03:27 Process Exit Code: (-1)
14:06:27 Setup failed to configure the server. Refer to the server error logs and C:WINNTsqlstp.log for more information.
14:06:30 Action CleanUpInstall:
14:06:30 C:TEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service SQLAgent$MOMSERVER
14:06:30 Process Exit Code: (1060) The specified service does not exist as an installed service.
14:06:30 C:TEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service MSSQL$MOMSERVER
14:06:50 Action Exit Handler:
14:06:52 StatsGenerate returned: 2
14:06:52 StatsGenerate (0x0,0x1,0xf0000000,0x600,1033,303,0x0,0x1,0,0,0
14:06:52 StatsGenerate -1,Administrator)