we have a Dell PE 2650 with 2GHz and 2MB RAM with 2000 Server. on it we
have about 6 MSSQL DB's. a couple of the DB's are the back-end for a
decent sized web app (5-10 users on average, 20 users at most). many
times, the cpu skyrockets on our db server when using the web app.
sometimes things timeout, other times not. at this time, upgrading the
hardware is not an option. aside from increasing the timeout periods, i
was wondering if windows 2003 server would utilize the processor better
(even just a little bit?) enough to know the difference.
should i attempt the upgrade, or is 2003 no more efficient with
processor resources?
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
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5 cient software.
However, I would like to install mssql 7.0 server on my nt workstation and work with it to become familiar with 7.0. Can I install mssql 7.0 server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0 on the same machine if they are in different directories?
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. I followed all steps. I was imformed that IT installed sp2 for sql server 2005. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?
I created a report where collected payments are listed by each collector from month to date.Everything Looks ok on the reporting side until a requirement came up where I am Also supposed to list the total number of payments collected by each collector (the count) and also the (total sum). If you look at the attached excel you would see two tables, I am getting the first table, 2nd is the one I want expected). How do I perform sum and count operations?
ALTER PROC USP_PaymentsByCollector ( @CollectorName AS varchar(20) = NULL, @CollectorCode AS varchar(20) = NULL, @LoanID AS varchar (20) = NULL --@StartDate AS DATETIME = NULL, ----@EndDate AS DATETIME = NULL
I open query analyser and on one tab I update a record in a transaction and hold it.
begin tran update customers set territory = 'x' where customer = 'A00001' --rollback tran
In a second tab I attempt to update all records in the table
update customers set carrier = ''
Clear this fails because of the lock placed during the first script and this is fine.
However, is there a way to get the 2nd script to ignore the locked rows and just update as many as it can? The obvious answer seemed to be the READPAST hint like follows…
update customers with (READPAST) set carrier = ''
…but this is still blocked by the original lock. I’ve tried combining it with all sorts of other table hints but all seem to get blocked.
The following does work, ignoring the lock and not returning the data
Select * from customers with (READPAST) where customer = 'A00001'
I’ve tried combining this with the update like so…
update customers set carrier = '' from customers with (READPAST) where customer = 'A00001'
..but this is blocked too.
I’m so desperate I tried moving the update into a cursor and update one row at a time. Nothing worked. I thought I might be able to do something like this
If (Select count(*) from customers with (READPAST) where customer = 'A00001') > 0 --then perform update
..but this returns a value of 1 even though the following returns no rows.
Select * from customers with (READPAST) where customer = 'A00001'
For the best practice I issued full SQL Server database, differential and transaction log backups. I have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
I'm not quite sure where to post this question, so I'll try here. We've been struggling getting SQL2005 reporting services integrated with Share Point Services 3.0 for several days now.
We've installed SQL Reporting Services with SP2 and run the SharePoint Reporting Services add-in. We've been able to go through the SQL Reporting Services Configuration and now were at the last step in the SharePoint Application Management Reporting Services configuration.
The first two steps of Manage Integration Settings and Grant Database Access was sucessful. The final step of "Set Server Defaults" gives us a 404 error message. There is nothing in the Event Log to suggest that there was a problem, and the SQL2005 Server is in SharePoint Integration mode. I've searched but can't find a solution. Can anyone steer me in the right direction please?
I'm trying to load data from old SQL server 2000 to new SQL server 2014. I need to do a checksum to check if all the source data is loaded in the target database(SQL server 2014). I've created the insert statement for the same which works. I need to use checksum to make sure all the source rows are loaded in the target table. I haven't done checksum before.
Here is my insert statement:
INSERT INTO [Test].[dbo].[Order_tab] ([rec_id] ,[date_loaded] ,[Name1] ,[Name2] ,[Address1] ,[Address2]
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID" FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA" WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
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
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?!
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.
Sorry to ask a stupid question. I have SQL Server 2000 on SBS 2003. I can't find Perform.exe. Anybody know where it should be (I'm sure that you all do!),
Hi,any body pls help me with the concept called transaction ?Where excatly we are doin this ? In the SQL or through c# codings.i have 3 stored procedures, where i am calling this one by one to perform a single operation in the client side.if any one fails, due to some exceptions. it should rollback and come to the initial state.... How to perform this ? any ideas..
/*Reset Identity on tables with identity column*/ exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
-- City SET IDENTITY_INSERT City ON INSERT INTO Elbalazo.dbo.City ( [CityID] ,[CityName] ,[CountyID] ,[Active]) SELECT [CityID],[CityName],[CountyID],1 FROM [ElbalazoProduction].dbo.tbl_City SET IDENTITY_INSERT City OFF
-- State SET IDENTITY_INSERT [State] ON INSERT INTO Elbalazo.dbo.State ( [StateID] ,[State] ,[Active]) SELECT [StateID],[State],1 FROM [ElbalazoProduction].dbo.tbl_State SET IDENTITY_INSERT [State] OFF
-- NumberOfPeopleOption SET IDENTITY_INSERT NumberOfPeopleOption ON INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption] ([NumberOfPeopleOptionID] ,[NumberOfPeopleNameOption] ,[Active]) SELECT [NumberOfPeopleID], [NumberOfPeopleName],1 FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople SET IDENTITY_INSERT NumberOfPeopleOption OFF
-- DeliveryOption SET IDENTITY_INSERT DeliveryOption ON INSERT INTO [Elbalazo].[dbo].[DeliveryOption] ([DeliveryOptionID] ,[DeliveryOptionName] ,[Active]) SELECT [DeliveryOptionID], [DeliveryOptionName],1 FROM [ElbalazoProduction].dbo.tbl_DeliveryOption SET IDENTITY_INSERT DeliveryOption OFF
-- User SET IDENTITY_INSERT [User] ON INSERT INTO [Elbalazo].[dbo].[User] ([UserID] ,[FirstName] ,[LastName] ,[Address1] ,[Address2] ,[CityID] ,[StateID] ,[Zip] ,[PhoneAreaCode] ,[PhonePrefix] ,[PhoneSuffix] ,[Email] ,[CreateDate] ,[Active])
SELECT [CustomerID] ,[FirstName] ,[LastName] ,[AddressLine1] ,NULL ,[CityID] ,[StateID] ,[Zip] ,[PhoneAreaCode] ,[PhonePrefix] ,[PhoneSuffix] ,[EmailPrefix] + '@' + [EmailSuffix] ,[CreateDate] ,1 FROM [ElbalazoProduction].dbo.tbl_Customer SET IDENTITY_INSERT [User] OFF
-- EntreeOption SET IDENTITY_INSERT EntreeOption ON INSERT INTO [Elbalazo].[dbo].[EntreeOption] ([EntreeOptionID] ,[EntreeOptionName] ,[Active]) SELECT [EntreeOptionID] ,[EntreeOptionName] ,1 FROM [ElbalazoProduction].dbo.tbl_EntreeOption SET IDENTITY_INSERT EntreeOption OFF
-- CateringOrder SET IDENTITY_INSERT CateringOrder ON INSERT INTO [Elbalazo].[dbo].[CateringOrder] ([CateringOrderID] ,[UserID] ,[NumberOfPeopleID] ,[BeanOptionID] ,[TortillaOptionID] ,[CreateDate] ,[Notes] ,[EventDate] ,[DeliveryOptionID]) SELECT [CateringOrderID] ,[CustomerID] ,[NumberOfPeopleID] ,[BeanOptionID] ,[TortillaOptionID] ,[CreateDate] ,[Notes] ,[EventDate] ,[DeliveryOptionID] FROM [ElbalazoProduction].dbo.tbl_CateringOrder SET IDENTITY_INSERT CateringOrder OFF
-- CateringOrder_EntreeItem SET IDENTITY_INSERT CateringOrderEntreeItem ON INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem] ([CateringOrderEntreeItemID] ,[CateringOrderID] ,[EntreeItemID]) SELECT [CateringORder_EntreeItemID] ,[CateringOrderID] ,[EntreeItemID] FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem SET IDENTITY_INSERT CateringOrderEntreeItem OFF
select * from BeanOption select * from CateringItemIncluded select * from CateringOrder select * from CateringOrderEntreeItem select * from CateringOrderEntrees select * from City select * from Country select * from DeliveryOption select * from EntreeOption select * from NumberOfPeopleOption select * from [State] select * from [User]
Hi all, I hope you guys can help me with the following bit of T-SQL. I already have a solution but I really don't like it and I've been trying to find a simpler more elegant way of doing the same thing.
Firstly, let me present you with a brief explanation of what I am trying to do together with some sample data for you to play with and hopefully assist me in finding a better solution than the one I’ve come up with.
insert into #VehMake select 222, 'FORD' union all select 210, 'FORD (USA)' union all select 223, 'FORD (AUS)' union all select 269, 'HONDA' union all select 253, 'NISSAN' union all select 280, 'VOLKSWAGEN'
This contains various vehicle makes which I'm sure you'll recognise!
insert into #VehicleHistory (PersonId, VehMakeVehModel) select 1, 'FORD (USA) MUSTANG' union all select 2, 'HONDA CIVIC' union all select 3, 'NISAAN ALMERA' union all select 4, 'VOLKSWAGEN PASSAT'
As you can see, in the second table, the second column contains a string of the vehicle Make and Model in one string. What I need to do is to split the Make and Model in to separate columns with an update statement.
This seems easy enough with a simple LIKE comparison:
VehMakeVehModel like VehMake+' %'
....BUT if you notice, there are two records in the #VehMake table that are similar but not the same. These are the 'FORD (USA)' and 'FORD (AUS)'. The update statement would return two records from the #VehMake table when trying to match with the first record in my #VehicleHistory table.
As I said, I did come up with a solution but it seems over complicated and I have a feeling that there is a way of doing this with an update. Maybe use the LEN() function but I'm not sure.
Your help would be much appreciated.
BTW, once I've identified the correct Make, I can easily populate my model as all I have to do is use the replace function on VehMakeVehModel column and remove the matched make to get the full model name.
Hope that makes sense and thanks for any help in advance.
I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:
Hello, i am using Full text search for my web application search SP. i have used Contains for Exact search and Freetext for Any word kind of search. now i want to perform Like search using Contains. same behaviour which we can get using LIKE word. (e.g. Select * from tbl where field like 'abc%') even i have tried using contains(title,'"*Documen*"') . but its not working.
this is my code: 1 myconnection = new SqlConnection(kiemtraloi,chuoiketnoi); 2 mycommand.Parameters.Add(new SqlParameter("@mancc",SqlDbType.VarChar,10)); 3 mycommand.Parameters["@mancc"].Value = TextBox1.Text; 4 myconnection.Open(); 5 myreader = mycommand.ExecuteReader(); 6 if(myreader.HasRows) And when I click to start button, a error appear: NO overload for method 'sqlconnection' takes '2' argumentsI don't know what i must do :(, please help methx
My e-commerce site is currently running the following process when items are shipped from the manufacturer:1. Manufacturer sends a 2-column CSV to the retailer containing an Order Number and its Shipping Tracking Number.2. Through the web admin panel I've built, a retail staff member uploads the CSV to the server.3. In code, the CSV is parsed. The tracking number is saved to the database attached to the Order Number.4. After a tracking # is saved, each item in that order has its status updated to Shipped.5. The customer is sent an email using ASPEmail from Persits which contains their tracking #.The process seems to work without a problem so long as the CSV contains roughly 50 tracking #'s or so. The retailer has gotten insanely busy and wants to upload 3 or 4 thousand tracking #'s in a single CSV, but the process times out, even with large server timeout values being set in code. Is there a way to streamline the process to make this work more efficiently? I can provide the code if that helps.
Hi all, Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below:I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows:
FloorRoomLocation
Fields inside each of the tables can be found below:
The Relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table. Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer
So i have created this form to accept user input:
this form is in a aspx page called AddRooms.aspx
Country Name:______________ (data sent to location table)
City Name:_________________ (data sent to location table)
Building Name:______________ (data sent to location table)
FloorID:[This is a DropDownList which is databinded to the Floor Table]
Room Name:_______________ (data sent to room table)
*Just imagine that the above is a form to accept user input
*With a hit of a submit button all data will be sent to their respective tables in the database
Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.
Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment
Just need the SQL Statment that i will need to construct to perform the JOIN i need to.
I want to build some paging functionality into my repeater (b4 you ask, datagrid not providing flexibility required for presentation).
I will have no problem with the VB logic but I will need to execute SQL that only returns results from x to y (e.g. results 21 thru 40 for page 2). I know I can do something like 'SELECT TOP 20 * FROM...', or something like it, for page 1. But, I'm not sure if it's possible to build SQL for the pages greater than 1. Any suggestions.
Hi, Appreciated someone able to provide me some clu on how to perfom DBCC CHECKDB. Is it necessary to set the database to "Read only" before execute DBCC CHECKDB?
