MSSQL Mistake (Excercise)
Feb 18, 2006I have an excercise:
SET @UserID = @@ROWCOUNT
it is said that there is mistake, nad to complete it there i no need of knowledge mssql? So where is mistake?
ADVERTISEMENT
A Trouble With Excercise
Dec 29, 2006Hi all.
A trouble with solving an exsercise.
A short description of the database:
The database scheme consists of four relations:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
The task is:
For each group of laptops with the identical model number, add following record into PC table:
code: minimal code among laptops in the group +20;
model: laptop's model number +1000;
speed: maximal speed among laptops in the group;
ram: maximal ram size among laptops in the group *2
hd: maximal hd capacity among laptops in the group *2;
cd: default value;
price: maximal price among laptops in the group divided by 1.5;
I have performed the following request
insert into pc(code,model,speed,ram,hd,cd,price)
SELECT distinct (Min(b.code)+20),l.model+1000, max(l.speed),(max(l.ram))*2,(max(l.hd))*2,CAST(MAX(cast(left(pc.cd,len(pc.cd)-1)as int))as char(2))+'x',max(l.price)/1.5
from Laptop as l,PC,(
select a.code from laptop as a, laptop as b
where a.model = b.model ) as b
GROUP BY l.model
... but it returns incorrect result on several positions. Where is mistake ?
Where Is Mistake In My Procedure?
Jun 25, 2004Please Help!
What is wrong with this procedure!
Why it doesn't sum the fields Obshta_suma and Pay
Why it doesn't return @Obshta_suma and @PayCREATE PROCEDURE dbo.Nalichni
@Date1 varchar(12),
@Date2 varchar(12),
@Obshta_Suma decimal output,
@Pay decimal output,
@prmO_PKO bit output,--boolean 0-false, 1-true
@prmO_RKO bit output--boolean 0-false, 1-true
AS
set @prmO_PKO = 0
set @prmO_RKO=0
if exists(Select Obshta_suma from PKO where Obshta_suma<>Null and (Vnos_Data >= @Date1) and (Vnos_Data <=@Date2))
begin
set @prmO_PKO=1
Select @Obshta_Suma=sum(Obshta_Suma) from PKO where (Vnos_Data >= @Date1) and (Vnos_Data <=@Date2)
end
if exists(Select Data,Pay from RKO where Pay<>Null and ( Data >= @Date1 and Data <=@Date2))
begin
set @prmO_RKO=1
Select @Pay= sum(Pay) from RKO where( Data >= @Date1) and( Data <=@Date2)
end
GO
Select Into Mistake
Apr 1, 2008I am trying to create a new table containing a subset in an existing table using SELECT INTO, but I get the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'MyDb.MyDestSchema.MyDestTable'.
What am I doing wrong? Here's the SQL statement:
SELECT t2.* INTO [MyDb].[MyDestSchema].[MyDestTable]
FROM [MyDb].[MySrcSchema].[MySrcTable] t2
LEFT JOIN [MyDb].[MyDestSchema].[MyDestTable] t1
ON t2.[DateTime] = t1.[DateTime]
WHERE (t1.[DateTime] IS NULL) AND
(convert(varchar, t2.[DateTime],126) BETWEEN '2006-11-01T08:30:00' AND '2006-12-31T15:30:00') AND
(convert(varchar, t2.[DateTime],114) BETWEEN '2006-11-01T08:30:00' AND '2006-12-31T15:30:00')
ORDER BY t1.[DateTime];
How To Get The Record I Just Updated By Mistake
Mar 10, 2008Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
How To If Needed Recover From Mistake
May 16, 2007Hi.I deleted a stored procedure (one I had written) out of the master database. Ithought I had accidentally put it there, then I got to thinking.. what if itis normally reflected there, and maybe I should not of deleted it.So.. my question.. is it normal for procedures in other databases to bereflected also in the master database, and then how can I restore / recoverthe master database if necessary.I am using sql server 2005 on XP pro.thanks for the adviceJeff Kish
View 3 Replies View RelatedI Ran A Update Quey By Mistake
Sep 24, 2006With out any BEGIN Transaction statement , i ran a update query.
I had to update just one record using the "Where field='abc'"
But i happened to miss that. Is there any chance of recovery? can i use the transaction logs to bring back the records before the update?
A quick answer is appreciated.
Sahel
Table Joins (Mistake Corrected)
Aug 29, 2002I have a correction to my previous thread all values are '83' not '80' My mistake
Message as it should be:
am new to SQL.
MY issue: Table (A) has the customerID field as a primary key. Table(b) has a customerID field but isn't the primary key. I am selecting all records from Table(A) and Table(B) Where Table(A).customerID=83 AND Table(B).customerID=83.
Table(A) has the customerID '83' Table(B) doesn't. The int 83 doesn't exist in table(B)
The various joins I constructed always returns (0 row(s) affected). When I enter a value that is inboth tables I get the desired result.
MY Goal:
I would like one record returned from table(A) if the customerID is not present in table(B) and one record that has a single customerID in both table(A)and Table(B)
Please advise.
Thanks
Urgent! Big Mistake, Data Recover
Mar 10, 2008Can I restore records from a MSDE2000 Database
I copied some stored procedure from other database but unfortunately, I drop all records
I have checked the Transaction Log which is only 1024 KB
I have used Red Gate Log Resure but cannot find the log
Anyone can Help!!!!!
Thanks
(sql 2005) How To Get The Record I Just Updated By Mistake
Mar 10, 2008Hi,
I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record,
is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Thanks a lot.
Group Header && Footer Hidden By Mistake
May 2, 2008
Hi,
I accidentally unclicked the check-boxes for group-header and group footer - I meant to leave the group header in. Now, I can't get to the group to edit it! Is there any way I can get back to it?
How To Backup Tr. Log When Database Is OFFLINE And Mdf File Is Remote My Mistake?
Sep 21, 2006I €˜d like to discus with you the following REAL enough disaster scenario:
1. The TEST database is in the FULL backup mode.
2. WE have a full TEST DB backup and all tr. log backups.
3. DBA moved TEST database OFFLINE for maintenance operations.
4. MDF file for TEST DB was removed by mistake.
5. TRANSACTION LOG (LDF) file is OK.
6. DBA want to recover database to the point of failure.
According with MS SQL Server 2000 documentation it is possible.
We need to backup the transaction log , BUT I CANNOT DO THIS.
PLEASE, HELP.
In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
I Made A Mistake About Service Name And The Transmission Queue Got Filled Of Them
Jun 30, 2006I want to use SSB as a mean to thread multiple SP invocation of the same procedure.
When I launched the initator processes it created in the transmission queue error messages saying for each of them that it is an invalid target service name.
The view (sys.transmission_queue) records them in this way in the status column:
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
Services and queues and activation procedure are all defined into the same database.
I drop services and queues, and i tried to create a service with the initial wrong name. Nothing happened.
Thanks vour your help
How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?
Aug 11, 2004How 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
Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)
Feb 10, 2008Hi,
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 :)
hoping for your kind help guys. thanks :)
br
Frozenice
Server Configuration For MSSQL 2000 And MSSQL 2005
Sep 6, 2006Does 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 RelatedCreating Index In MSSQL 2000 From MSSQL 2005
Mar 24, 2008Hi,
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.
Generating Script For MSSQL 2000 From MSSQl 2005?
May 3, 2008Hello
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
Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?
Oct 26, 1999Hello:
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?
Thanks.
David Spaisman
MSSQL Express 2005 Vs. MSSQL 2000
Jun 15, 2006Ben 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"
Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit
Nov 17, 2007I'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.
Deploying MSSQL 2005 Express DB To MSSQL 2005 WKGP Errors
Sep 29, 2006DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
Mark Roach
Conversion Of MSSql 2000 Database To MSSQL 2005 Database
Jan 18, 2008How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies View RelatedGUI Client For MsSQL (was: MSSQL Help)
Sep 25, 2007I have my MSSQL hosted, any interface i can use so i can't connect to the data source?
for Mysql, i know i can use phpmyadmin, or mysql interface.
i have no idea for MSSQL, any advices?
i know there is one, which is Toad, but it has a lots of limitations, any good idea?
thanks.
MsSQL And Php Resources (was: Need Mssql)
Feb 17, 2008Hi,
till now i have used mysql with php.now i want to use mssql with php.my php and apache versions are
php 5.2.3
apache 2.2.4
from where i can get mssql and what i need to run my php scripts with mssql database.iam newbie in mssql.any of your help will be surely appreciated
thanks
mrjameer
Migrate MSSQL 2005 Express -&&> MSSQL 2005 Srv
Apr 25, 2006Hi!
What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?
Mssql And Asp.net
May 3, 2007Hey guys, im trying to write an application to connect to a database on a mssql server using asp.net. Thus far any code i've seen and tried seems to not recognize the ODBC connections, so i was hoping you guys can offer some suggestions. For the example lets say the server is mssql9.aaa.com the db name is xyz with a username of welsh_welsh and the password is q with a table named abc in the database. How exactly would you go about connecting. The code i've tried so far is below:
<%
"Data Source = mssql9.aaa.com;Initial Catalog = xyz;UserID = welsh_welsh;Password = q;"
%>
The error i get from the sever is
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30035: Syntax error.
Mssql
Jul 2, 2007any free videos to study mssql server 2000 queries
View 1 Replies View RelatedMSSQL Help
Mar 31, 2008Im trying to write a very basic stored porcedure that looks up some data and performs simple calculations, the results of which are then saved (updated) to a specific table. To be more specific, I use the avg function in a simple select statement to average some figures based on a where clause:
SELECT AVG(exactskip)FROM HistoryWHERE Exact = '023'
...This should return 425.66, but it returns either 425 or 425.0000.
The results which are returned and being saved are being shown as a rounded integer or shown with 4 zeros after the decimal. I know it should be ending with a decimal in almost all cases. The column in the table that the result are being saved to is formated as smallmoney. The column in the table whos data is being averaged is set as an integer. Question, how can I make the avg function calculate the decimal values and save the appropriate decimal results. What changes do I need to make to my select statement. Any help would be appreciated.
I am still very new to all this, so please be specific and somewhat step by step in any reply
Thanks
Scott
IIS/MSSQL
Sep 20, 2005I have an SQL 2000 server set up that runs perfectly, and an IIS set up that runs perfectly but I can not get the webpage to read data from the dbase. Is there a connection that I have to make or a program I have to load? The page is a .asp page, and when I run the page that should connectI do not get any errors in the dbase at all, so I can only imagine that it is not even seeing an attemptat a connection. Any help is appreciated.
View 1 Replies View RelatedMSSQL & And ASP
Aug 1, 2000hi,
I'm starting to write web application using ASP, the backend is MSSQL7.
Can I have some sample codes to look at. How to get connection and
select statements and get results and display onto the web.
thanks
Using DAO W/MSSQL 7
Sep 5, 2000Hi, is there an easy way to open a SQL 7 db using DAO code. Seems like a simple
set db = currentdb
won't do the trick. I also tried to open a workspace first, but I keep getting error when I try to do something with the recordsets, and I can't RunSQL. Any suggestions?
Run Mssql Dts From Vbs
Oct 28, 2005I am trying to run a DTS from a vbs but I am pulling my hair out at this point (grey anyway). Here is the script.
set objDTSPackage = CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "(local)", "sa", "", DTSSQLStgFlag_Default, "", "", "", "dts_download", 0
it is not doing it, and giving me error on all steps within the package
your help is greatly appreciated
thank you