Begging your patience, I'm wondering if someone can help me with a beginner question. I am trying to write a query against our trouble ticket DB. I want to find tickets that have multiple tech assignments associated with a single ticket. How can write a query that says if in the CALLID field you find the same value more than once, return those rows and only those rows?
Thanks very much.
can anyone explain what are superkeys & candidate keys with a simple example?
Thanx...
I'm use to working with MSaccess and coldfusion and am trying to convert to sql. I ran the upsize wizard in access and now see my DB in the SQL Query Analyzer. How do I open / modify / view the DB? I have zero experience with sql so treat me like a 5 year old.
And if anyone has any knowledge of connecting coldfusion with sql that would also help.
Thanks.
Hi,
I have a query that takes 15minutes to run the first time after sql server start but then for subsequent querys is superfast (less than 10 secs).
Why? What could I do to speed up the first run?
Here is the source:
Code Block
SELECT Articoli.Articolo, Articoli.BarCodeVen, Articoli.Descri, Articoli.Merce, Articoli.CodIVA, Articoli.UMBase, Articoli.PrzBase, Articoli.Sconto1, Articoli.Sconto2,
Articoli.ScontoFormula, Articoli.Ricarico, Articoli.TipoArt, Articoli.CatMerc, Articoli.CatOmo, Articoli.CatProvv, Articoli.CntrprtVen, Articoli.CntrprtAcq,
Articoli.Dispniblta, Articoli.Vendita, Articoli.SiCli, Articoli.SiFor, Articoli.NotaPriv, Articoli.NotaPubb, Articoli.Immagine, Articoli.Produttore, Articoli.SiMatr,
Articoli.OldCodArt, Articoli.Disattivo, Articoli.CatProd, Articoli.SotCatProd, Articoli.EsplKit, Articoli.ScaricoCom, Articoli.ImmagLun, Articoli.DataModCS,
Articoli.ValorizPr, Articoli.Natura, Articoli.UMSecScelta, Articoli.SecScelta, Articoli.Acquisto, Articoli.CntConsumi, Articoli.NonMovimentabile,
Articoli.ProvAgente, Articoli.Taglia, Articoli.Colore, Articoli.CodArtNeutro, Articoli.Collezione, Articoli.Assortimento, Articoli.Centro,
Articoli.InibisciRicercaUnMis, Articoli.Commessa, Articoli.DescriTesto, Articoli.Disattivabile, Articoli.KwTipoArt, Articoli.KwTipoArtNeutro,
Articoli.KwNeutro, Articoli.KwCollezione, Articoli.KwLinea, Articoli.KwModello, Articoli.KwMateriale, Articoli.KwComposizione, Articoli.KwPosRiga,
Articoli.KwVarRiga, Articoli.KwPosColonna, Articoli.KwVarColonna, Articoli.KwPos3D, Articoli.KwVar3D, Articoli.KwGrpVarRiga, Articoli.KwGrpVarColonna,
Articoli.KwGrpVar3D, Articoli.KwEtiMan, Articoli.KwAssortimento, Articoli.KwGrpFormule, Articoli.KwSpecimen, KwVarArticolo.TipoVariante,
KwVarArticolo.Variante, KwVarArticolo.Posizione, KwVarArticolo.Categoria, KwVarArticolo.DescriVariante,
KwVarArticolo.P07009Descrizione1 AS Descrizione1, KwVarArticolo.P07009Descrizione2 AS Descrizione2, CatOmo.Descri AS CatOmoDescri,
KwModelli.Descri AS DescriModello, KwMateriali.Descri AS DescriMateriale, MFDBCompon_Tomaia2.DatoTecnico,
MFDBCompon_Tomaia2.DBPosColonna, MFDBCompon_Tomaia2.DBPos3D, MFDBCompon_Tomaia2.CompNeutro,
MFDBCompon_Tomaia2.CompVarRiga, Articoli_1.Descri AS DescriCompNeutro, MFKwVariantiRiga.DescriVariante AS DescriRigaCompo,
CatProvv.Descri AS CatProvvDescri, MFDBCompon_Tomaia3.CompNeutro AS CompNeutroT3,
MFDBCompon_Tomaia3.CompVarRiga AS CompVarrigaT3, Articoli_2.Descri AS DescriCompNeutroT3,
MFKwVariantiRiga_1.DescriVariante AS DescriRigaCompoT3, MFDBCompon_Acc1.CompNeutro AS CompNeutroAcc1,
MFDBCompon_Acc1.CompVarRiga AS CompVarRigaAcc1, Articoli_3.Descri AS DescriCompNeutroAcc1,
MFKwVariantiRiga_2.DescriVariante AS DescriRigaCompoAcc1, MFDBCompon_Acc2.CompNeutro AS CompNeutroAcc2,
MFDBCompon_Acc2.CompVarRiga AS CompVarRigaAcc2, Articoli_4.Descri AS DescriCompNeutroAcc2,
MFKwVariantiRiga_3.DescriVariante AS DescriRigaCompoAcc2, MFDBCompon_SottoP_Tallonet.CompNeutro AS CompNeutroSottop,
MFDBCompon_SottoP_Tallonet.CompVarRiga AS CompVarRigaSottop, MFDBCompon_SottoP_Tallonet.DescriCompNeutroSottop,
MFDBCompon_SottoP_Tallonet.DescriRigaCompoSottop, MFDBCompon_SottoP_Tallonet.DatoTecnico AS DatoTecnicoSottop,
MFDBCompon_RicSol.CompNeutro AS CompNeutroRicSol, MFDBCompon_RicSol.CompVarRiga AS CompVarRigaRicSol,
MFDBCompon_RicSol.DescriCompNeutroRicSol, MFDBCompon_RicSol.DescriRigaCompoRicSol,
MFDBCompon_Fodera.CompNeutro AS CompNeutroFodera, MFDBCompon_Fodera.CompVarRiga AS CompVarRigaFodera,
MFDBCompon_Fodera.DescriCompNeutroFodera, MFDBCompon_Fodera.DescriRigaCompoFodera, MFDBCompon_Forma.Descri AS DescriForma,
MFDBCompon_Tacco.Descri AS DescriTacco, MFVar3D_Pos1.DescriVariante AS Descri_Pos1, MFVar3D_Pos2.DescriVariante AS Descri_Pos2,
MFVar3D_Pos3.DescriVariante AS Descri_Pos3, MFVar3D_Pos4.DescriVariante AS Descri_Pos4, MFListinoP00New.Prz AS P00,
MFListinoP00New.InVigoreDa AS DataP00, MFListinoP05New.Prz AS P05, MFListinoP05New.InVigoreDa AS DataP05, MFListinoPUSANew.Prz AS PUSA,
MFListinoPUSANew.InVigoreDa AS DataPUSA,
CASE WHEN KwVarArticolo.P07009Descrizione2 <> '' THEN KwVarArticolo.P07009Descrizione2 ELSE CASE WHEN Articoli.CatProvv = '30SPORT' THEN Articoli.KwNeutro
+ KwVarArticolo.Variante + '.jpg' ELSE Articoli.KwNeutro + '.jpg' END END AS MFFoto, MFListinoRUSANew.Prz AS RUSA,
MFListinoRUSANew.InVigoreDa AS DataRUSA, MFListinoRITANew.Prz AS RITA, MFListinoRITANew.InVigoreDa AS DataRITA, ArtMerce.Fornitore
FROM Articoli INNER JOIN
KwVarArticolo ON Articoli.Articolo = KwVarArticolo.Articolo INNER JOIN
CatOmo ON Articoli.CatOmo = CatOmo.Categoria INNER JOIN
KwMateriali ON Articoli.KwModello = KwMateriali.KwModello AND Articoli.KwMateriale = KwMateriali.KwMateriale INNER JOIN
KwModelli ON Articoli.KwModello = KwModelli.KwModello AND Articoli.KwMateriale = KwModelli.KwMateriale LEFT OUTER JOIN
ArtMerce ON Articoli.Articolo = ArtMerce.Articolo LEFT OUTER JOIN
MFListinoRUSANew ON KwVarArticolo.Variante = MFListinoRUSANew.VarRiga AND Articoli.KwNeutro = MFListinoRUSANew.Neutro LEFT OUTER JOIN
MFListinoRITANew ON KwVarArticolo.Variante = MFListinoRITANew.VarRiga AND Articoli.KwNeutro = MFListinoRITANew.Neutro LEFT OUTER JOIN
MFListinoPUSANew ON KwVarArticolo.Variante = MFListinoPUSANew.VarRiga AND Articoli.KwNeutro = MFListinoPUSANew.Neutro LEFT OUTER JOIN
MFListinoP05New ON KwVarArticolo.Variante = MFListinoP05New.VarRiga AND Articoli.KwNeutro = MFListinoP05New.Neutro LEFT OUTER JOIN
MFListinoP00New ON Articoli.KwNeutro = MFListinoP00New.Neutro AND KwVarArticolo.Variante = MFListinoP00New.VarRiga LEFT OUTER JOIN
MFVar3D_Pos4 ON Articoli.KwNeutro = MFVar3D_Pos4.Articolo LEFT OUTER JOIN
MFVar3D_Pos3 ON Articoli.KwNeutro = MFVar3D_Pos3.Articolo LEFT OUTER JOIN
MFVar3D_Pos2 ON Articoli.KwNeutro = MFVar3D_Pos2.Articolo LEFT OUTER JOIN
MFVar3D_Pos1 ON Articoli.KwNeutro = MFVar3D_Pos1.Articolo LEFT OUTER JOIN
MFDBCompon_Forma ON KwVarArticolo.Variante = MFDBCompon_Forma.DBVarRiga AND
Articoli.Articolo = MFDBCompon_Forma.DBNeutro LEFT OUTER JOIN
MFDBCompon_Tacco ON KwVarArticolo.Variante = MFDBCompon_Tacco.DBVarRiga AND
Articoli.Articolo = MFDBCompon_Tacco.DBNeutro LEFT OUTER JOIN
MFDBCompon_Fodera ON KwVarArticolo.Variante = MFDBCompon_Fodera.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_Fodera.DBNeutro LEFT OUTER JOIN
MFDBCompon_RicSol ON KwVarArticolo.Variante = MFDBCompon_RicSol.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_RicSol.DBNeutro LEFT OUTER JOIN
MFDBCompon_SottoP_Tallonet ON KwVarArticolo.Variante = MFDBCompon_SottoP_Tallonet.DBVarRiga AND
Articoli.KwNeutro = MFDBCompon_SottoP_Tallonet.DBNeutro LEFT OUTER JOIN
MFDBCompon_Acc2 INNER JOIN
Articoli AS Articoli_4 ON MFDBCompon_Acc2.CompNeutro = Articoli_4.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_3 ON MFDBCompon_Acc2.CompVarRiga = MFKwVariantiRiga_3.Variante ON
KwVarArticolo.Variante = MFDBCompon_Acc2.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Acc2.DBNeutro LEFT OUTER JOIN
MFDBCompon_Acc1 INNER JOIN
Articoli AS Articoli_3 ON MFDBCompon_Acc1.CompNeutro = Articoli_3.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_2 ON MFDBCompon_Acc1.CompVarRiga = MFKwVariantiRiga_2.Variante ON
KwVarArticolo.Variante = MFDBCompon_Acc1.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Acc1.DBNeutro LEFT OUTER JOIN
MFDBCompon_Tomaia3 INNER JOIN
Articoli AS Articoli_2 ON MFDBCompon_Tomaia3.CompNeutro = Articoli_2.Articolo INNER JOIN
MFKwVariantiRiga AS MFKwVariantiRiga_1 ON MFDBCompon_Tomaia3.CompVarRiga = MFKwVariantiRiga_1.Variante ON
KwVarArticolo.Variante = MFDBCompon_Tomaia3.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Tomaia3.DBNeutro LEFT OUTER JOIN
CatProvv ON Articoli.CatProvv = CatProvv.Categoria LEFT OUTER JOIN
Articoli AS Articoli_1 INNER JOIN
MFDBCompon_Tomaia2 ON Articoli_1.Articolo = MFDBCompon_Tomaia2.CompNeutro INNER JOIN
MFKwVariantiRiga ON MFDBCompon_Tomaia2.CompVarRiga = MFKwVariantiRiga.Variante ON
KwVarArticolo.Variante = MFDBCompon_Tomaia2.DBVarRiga AND Articoli.KwNeutro = MFDBCompon_Tomaia2.DBNeutro
WHERE (Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND
(Articoli.CatProvv IN (@CatProvv)) AND (Articoli.Disattivo = '0') AND (KwVarArticolo.Disattivo = '0') AND (ArtMerce.Fornitore IN (@Fornitore)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND
(Articoli.CatProvv IN (@CatProvv)) AND (Articoli.Disattivo = '0') AND (KwVarArticolo.Disattivo = '0') AND ('ALL' IN (@Fornitore)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND (Articoli.Disattivo = '0')
AND (KwVarArticolo.Disattivo = '0') AND (ArtMerce.Fornitore IN (@Fornitore)) AND ('ALL' IN (@CatProvv)) OR
(Articoli.KwCollezione = @KwCollezione) AND (Articoli.KwLinea = @KwLinea) AND (KwVarArticolo.TipoVariante = 52559872) AND (Articoli.Disattivo = '0')
AND (KwVarArticolo.Disattivo = '0') AND ('ALL' IN (@CatProvv)) AND ('ALL' IN (@Fornitore))
ORDER BY Articoli.CatProvv, Articoli.CatOmo, Articoli.Articolo, KwVarArticolo.Posizione
Thank you!
Roberto
Hi,
This is probably a no-brainer for most of you, but I'm really really new to sql.
I'm using ms sql 2005 (I think), and I'm making a scheduling program in VB that uses a database hosted on the sql server. Does sql come with archiving tools, or will I need to make something? All the other DBs on the server are 3rd party apps that have a little archiving tool as part of the utilities package. Will I have to make something like that to remove and save the old schedule entries? If so, can anyone point me in the right direction?
Thanks,
Chris
I did lot of google but did not get script for candidate and super key. I do not have syntax to create candidate and super key in sql.
View 4 Replies View RelatedWell, I can't backup up my tran log - too big. I knew enough to know you have to back it up, didn't know enough to know that tran log backup is not included in "Full backup". Contractor installation set Growth to "un-restricted". Me, too swamped to delve deeply in time to learn this is a recipe for disaster, and then it was too late - System down because tran log filled up the disk...
(Ironically, this happened the morning of the first day of some training they sent me to - Microsoft's Programming a Microsoft SQL 2000 Database...)
So, some details... The drives on the system :
___ c: size 12G, free 7G
___ d: size 44.9G, free 10M
___ e: size 44.9G, free 13G
Tran log info :
___ filename .... d:Microsoft SQL ServerMSSQLdatahorizon_Log.LDF
___ name ........ horizon_Log
___ size ........ 5606392
___ maxsize ..... -1
___ growth ...... 10
___ status ...... 1081410
___ filename ...E:Horizon_Log2Horizon_Log2_Temp
___ name ........ horizon_1_Log
___ size ........ 167168
___ maxsize ..... 640000
___ growth ...... 640
___ status ...... 32834
(there are 2 because the other totally green newbie dba added 1 the day the system went down...)
I *thought* the addition of the 2nd tran log file meant the 1st file was no longer part of the picture, and that my available free disk space was sufficient to backup the 2nd tran log file.
Clearly I'm missing something - when I try to back up, I get this in SQL server error log :
___ Operating system error 112(There is not enough space on the disk.)
:confused:___ Does that mean it's trying to backup both the 1st & the 2nd tran logs?
:confused:___ Or is it only backing up the 2nd log, but trying to use D: (with only 10M)?
:confused:___ Or, is it using C: or E:, but tran log backups need some multiple of the size of the actual log, and there's not enough space even though there are multiple G?
I've been reading & googling. So far tried to truncate both files.
( dbcc shrinkfile('horizon_1_log',truncateonly) )
No luck, got these :
___ Cannot shrink log file 2 (horizon_Log) because all logical log files are in use.
___ Cannot shrink log file 3 (horizon_1_Log) because all logical log files are in use.
:confused:
:confused:___ How do I get them to be not "in use"?
:confused:___ Must the system be down for that? (certainly seems to...)
Also, checked out the EMPTYFILE option. Books Online says :
"Migrates all data from the specified file to other files in the same filegroup"
:confused: ___ Migrates to which "other files", exactly?
:confused: ___ Is this the right way to go?
:confused: ___ If so, how would I proceed after achieving a successul EMPTYFILE operation?
Also...
:confused: ___ In the database's Properties dialog, Transaction Log tab, there's a Delete button. Could that button be used to get me out of this difficulty?
Last, but not least... If I'm going about this altogether wrongly, can I get a pointer or two?
TIA for any and all feedback...
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ----------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
go
ALTER PROCEDURE [dbo].[sp_UpdatePersonalization]
@p_id nvarchar(50),
@cust_num varchar(50),
--@publication varchar(25),
@full_name varchar(500),
@email varchar(200),
@web_address varchar(300),
@include_web bit,
@ReturnAddressYN varchar(1),
@include_email bit,
@job_title varchar(500),
@company_name varchar(500),
@tagline varchar(1000),
@phone1 varchar(30),
@phone2 varchar(30),
@phone3 varchar(30),
@phone4 varchar(30),
@phoneext1 varchar(10),
@phoneext2 varchar(10),
@phoneext3 varchar(10),
@phoneext4 varchar(10),
@phonedesc1 varchar(20),
@phonedesc2 varchar(20),
@phonedesc3 varchar(20),
@phonedesc4 varchar(20),
@company_mail_address varchar(500),
@masthead varchar(250),
@verbiage_page1 varchar(1400),
@verbiage_page4 varchar(2650),
@inc_bbb bit,
@inc_ehl bit,
@inc_eho bit,
@inc_rl bit,
@p_comments varchar(500),
@p_update_flag varchar(10)
--@frequency varchar(50),
--@mail varchar(50),
--@fold varchar(50),
--@contact varchar(50),
-- do not add this on the update! @date_added,
AS
declare @last_updated datetime
select @last_updated=GETDATE()
declare @set_update bit
--if @p_update_flag='False'
--select @set_update = 1
--else
--select @set_update = 0
if @p_update_flag='False'
INSERT INTO pers_main_arch
(
p_id,
cust_num,
publication,
full_name,
email,
web_address,
include_web,
include_email,
job_title,
company_name,
tagline,
phone1,
phone2,
phone3,
phone4,
phoneext1,
phoneext2,
phoneext3,
phoneext4,
phonedesc1,
phonedesc2,
phonedesc3,
phonedesc4,
company_mail_address,
masthead,
verbiage_page1,
verbiage_page4,
inc_bbb,
inc_ehl,
inc_eho,
inc_rl,
p_comments,
frequency,
mail,
fold,
contact,
date_added,
last_updated,
start_month,
ReturnAddressYN
)
SELECT
pm.p_id,
pm.cust_num,
pm.publication,
pm.full_name,
pm.email,
pm.web_address,
pm.include_web,
pm.include_email,
pm.job_title,
pm.company_name,
pm.tagline,
pm.phone1,
pm.phone2,
pm.phone3,
pm.phone4,
pm.phoneext1,
pm.phoneext2,
pm.phoneext3,
pm.phoneext4,
pm.phonedesc1,
pm.phonedesc2,
pm.phonedesc3,
pm.phonedesc4,
pm.company_mail_address,
pm.masthead,
pm.verbiage_page1,
pm.verbiage_page4,
pm.inc_bbb,
pm.inc_ehl,
pm.inc_eho,
pm.inc_rl,
pm.p_comments,
pm.frequency,
pm.mail,
pm.fold,
pm.contact,
pm.date_added,
pm.last_updated,
pm.start_month,
pm.ReturnAddressYN
FROM pers_main pm
WHERE pm.cust_num = @cust_num
if @p_update_flag='True' OR @p_update_flag='False' OR @p_update_flag IS NULL OR @p_update_flag=''
UPDATE pers_main SET
--cust_num=@cust_num,
--publication=@publication,
full_name=@full_name,
email=@email,
web_address=@web_address,
include_web=@include_web,
include_email=@include_email,
job_title=@job_title,
company_name=@company_name,
tagline=@tagline,
phone1=@phone1,
phone2=@phone2,
phone3=@phone3,
phone4=@phone4,
phoneext1=@phoneext1,
phoneext2=@phoneext2,
phoneext3=@phoneext3,
phoneext4=@phoneext4,
phonedesc1=@phonedesc1,
phonedesc2=@phonedesc2,
phonedesc3=@phonedesc3,
phonedesc4=@phonedesc4,
company_mail_address=@company_mail_address,
masthead=@masthead,
verbiage_page1=@verbiage_page1,
verbiage_page4=@verbiage_page4,
inc_bbb=@inc_bbb,
inc_ehl=@inc_ehl,
inc_eho=@inc_eho,
inc_rl=@inc_rl,
p_comments=@p_comments,
--frequency=@frequency,
--mail=@mail,
--fold=@fold,
--contact=@contact,
--date_added,
last_updated=@last_updated,
updated_flag=1,
ReturnAddressYN=@ReturnAddressYN
WHERE cust_num=@cust_num
------------------------------------------ code ----------------------------------------------
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!
Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:
tbAutombbileCategories
CategoryID | Category
-------------------------------
1 | Car
2 | Truck
3 | Motorcycle
tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
-------------------------------------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity
tbAutomobile
VIN | Category | Make | Model
-------------------------------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150
tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
---------------------------------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000
Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?
tbCategories
CategoryID | Category
--------------------------
1 | Cars
2 | Trucks
tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
-------------------------------------
1 | 1 |Honda | Accord
tbCars
fkVIN | Doors |
-----------------
1 | 2
tbTrucks
fkVIN | Cab | Capacity
---------------------------
2 | 0 | 1000
Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.
Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...
Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.
E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?
tbTrucks
fkVIN | Cab | Capacity | fkCapID
--------------------------------------
2 | Y | 1000 | 1
tbCaps
CapID | Vendor | Price | et....
Any thoughts at all? I thought this would have been a pretty damn hot topic!
Mike B
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
I am just a sql beginner. I wonder if it is possible to jump to table B to continue searching while I am still doing search on table A. Thanks
J
Can anyone tell me where I can learn more about DTS.
As I have to export data from a .CSV(comma separated file) into SQL Server.
I have MS SQL 2000 and 2005 Express install on my computer. I would like to know where I can get some good beginner's training and I also need to know how to run sql scripts and import database to both.
This is so easy to do in mysql.
Thanks
I have a SQL server that my web host has provided but I don't know who to set it up! I'm currently have an MS Access database on my web site and I want to change it to MSSQL using the Access upsizing wizards, can anyone talk me through it?
View 2 Replies View Relatedhi there. this is my first post on this forum. im new to SQLServer so please go easy on me. :)
i am trying desperately to call a stored procedure from within another stored procedure. if i post my code below could somebody tell me where i am going wrong. the SP i have written is executing OK but the OUTPUT parameter Id_User is coming back as null, whereas it should be giving my a value there. i have tried changing the line 'EXEC sp_User_I' for 'INSERT INTO @User_Id EXEC sp_User_I' but this wont compile.
CREATE PROCEDURE sp_Account_I]
@Username varchar(16),
@Password varchar(88),
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64),
@Id_Account int OUTPUT,
@Id_User int OUTPUT
AS
DECLARE @ErrorCode int;
BEGIN
SET NOCOUNT ON;
EXEC sp_User_I
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country,
@Id_User
...do some other stuff here for the account...
END
Hi all,
I've been banging my head against this issue, and I haven't managed to find a solution. I was hoping that maybe somebody here has done something similar.
I am trying to get the latest available pricing from a Rate table, which is based on the customer ID and the provided date from another table (Table1).
This is what the code looks like, essentially:
select
table1.*
,table2.*
,table3.Rate
from table1
left outer join table2 on table1.Id = table2.Id
left outer join (
Select Top 1 Rate from RateTable
Where RateTable.date < table1.date
order by RateTable.date desc
) as table3 on table3.custId = table1.custId
I understood that table1.date can't be explicitly passed into the nested join within table3, but does anybody know a work around that can achieve the above example (the environment is SQL server 2000 and inside a view not on in a stored proc)?
Thanks in advance for any tips or workaround.
Hi,I am completely new to the BCP utility and fairly new to SQL ServerI am learning from a book and I am trying the following example (the serverI'm learning on is called contractor and a password has not been give to thesa)bcp pubs..authors out authors.txt -C -r -t, -U sa -P -S contractorWhen I run this in Query Analyser i get the error message..Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '.'.I have tried puuting quotes around the database and the table name asfollows-bcp "pubs..authors" out authors.txt -C -r -t, -U sa -P -S contractorand get the errorServer: Msg 179, Level 15, State 1, Line 1Cannot use the OUTPUT option when passing a constant to a stored procedure.Please can anybody help me to get this first bit working?Thanks in anticipation.
View 2 Replies View RelatedWhat is the best way to go about learning SQL? Where should I start?
View 20 Replies View RelatedHow do i learn SQL server 2000...I'm new to development, and need to get my basics right before i proceed further.
Do give me your feedback / learning tips.
Hi
is there any SQL server book or online meterial for beginners?? i've checked msdn its seems not helpful at beginner level.like issues for installing and first time configuring SQL Server. and installing its clients etc.
hello all i'm new in dot net and as every one beginning in something i have some problems hope u guys can help me my first question is really easy i'm feeling silly to ask such a question i made database in sql server 2005 and i made a table and columns but i cant insert data into the table i dont know how to fill the table with data? my second question is can i convert sql server 2000 database to 2005 if i can how i cando that? that's all for now hope u can really help me thanks in advance
View 4 Replies View RelatedMy company needs a database. My first thought was to do it in Access, because it's available! But I wanted to check that that was smart, what its limitations are, when it's better to move to bigger / more expensive software, etc.
It's probably going to have several hundred thousand records in and will grow by more than a hundred thousand every year. Is there a size limit?
Not that many fields, though - it's not that complicated a database.
Many thanks in advance for any help :beer:
Hi!
I'm trying to make a dictionary.
WordEng(#id,word);
WordIta(#id, word);
tables with words in Italian and English.
Connect1(#id,id1,id2); connecting word form Eng with Ita translation.
Connect2(#id,id1,id2); same for Italian.
Is this correct? 3. normalisation? Will this work?
if i am creating an SP then i only specify output parameters if the SP is to return a single set of values. is that correct? if my SP is returning multiple rows from a SELECT statement then i would not specify any parameters on the SP. can somebody clarify this for me. thanks!
View 2 Replies View RelatedHi guys,
I am new to database programming... i know the answer is somewhere in the past forums and i unfortunately i can't locate it... I have this problem i hope you can help me.
I am trying to insert values into table hosp_wareitem and one of it is the itemid where in i would like to get the item id of an existing medicine... (i hope you can understand me guys) here is my query.. right now im puzzled and i know the answer is already there.. i just cant see it...
set xact_abort on
declare @id as integer
begin transaction
select @id (select medicines_id from
hosp_medicines where medicines_id = medicines_id)
insert into Hosp_WareItem
(ItemID,
Department_ID,
SalesGL_Code,
COSGL_Code,
InventoryGL_Code,
ExpenseGL_Code,
ReadersFeeGL_Code,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
values
(@ID,
'1',
'AAA-111',
'AAA-000',
'AAA-112',
'',
'',
'0',
'',
'',
'1/1/1900 12:00:00 AM',
'1/1/1900 12:00:00 AM',
'')
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'wareitem'
commit transaction
it returns an error
(1 row(s) affected)
(24 row(s) affected)
Server: Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'ItemID', table 'Medix_Hospital.dbo.Hosp_wareitem'; column does not allow nulls. INSERT fails.
it tries to insert a null value in itemid because it cant get the value of the medicine_id.
any help is much appreciated!
puzzled
joel
Hi all,
This looks like a great place for SQL Server support. I hope you don't mind a few n00b questions.
I'm a sysop whose main experience is with Windows XP and SBS 2003. I'm also reasonably familiar with Access 97/2000, having programmed a few small databases. I've recently acquired a client who is running the premium version of SBS, which includes SQL Server 2005. This means I have to manage this thing. This *huge* thing. So my first question is: Where can I find information to get me started, first from a sysop perspective, and later from a Access programmer perspective. What is your experience?
Then two current issues. One is that for some reason SQL Server 2005 is using about 2 gigabytes of memory out of the box. Is this normal? Second, I can't for the life of me find the backup system. I can see backups being made of the AdventureWorks database (at 3:00), but even after 2 hours of searching I can't change the schedule. I knew how to do this in SQL Server 2000, but this version is very different.
I'd much rather figure these things out on my own, but this product is just too complicated to learn in a few days, and the memory usage issue is critical.
I'd be much obliged for any help you can offer.
Please add me for real-time ask for help.
My MSN: ices_ailee@hotmail.com
My Yahoo: ices_ailee@yahoo.com.sg
Please add my msn or yahoo account, easy for me to enquiry straight forward ...thx yo. I am a junior and beginner with SQL Server.
thx yo!
chia ling
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies View RelatedI just installed SQL Server 2005, Office 2007, and the add-in, but when I check services.msc, I don't see the SQL Server Analysis Services. Does anyone know how I can get this to load? Also, when I run the server configuration utility in the DM add-in folder, do you know what I should put in the server name to get it to run off my hard disk and not a server? local host doesn't seem to work. Thanks in advance!
View 11 Replies View RelatedHi! Everybody,
i am totally new to the environment of MS, (.Net, SQL Server...)
i was working previously with Oracle 9i. I have downloaded MS SQL Server Express 2005, MS SQL Mgmt Studio Express, ..
i want to build a database, and import my data to it. How to do so?
Hi all....
I'm not good t adatabases and never had to install one before, but I have an easy question....
I need to install a big application that needs a database server in the back end to run, I have a box with SQL2005 installed on it and I need to let the server be the database server for this application that will be installed on a seperate box....
How would I be able to point this application to the database server? do I need to install a SQL client on this server and point it to the SQL2005 server in the back?
Do I need to create a "new database" on the SQL sever and give it a name and credentials then point the application to that database? if that is the case, how would I do it?
Thanks.
I have created two reports. One links to the other with a drillthrough. The linking works fine, but no values show up in the fields besides the group titles and column titles. Why is this happening?
The user is given a list of Counties with values. The Counties(in a matrix) are clickable for a drillthrough of Ages and Genders in the Counties. Looks something like this:
Years
Counties Total Crashes Fatalities
Cook 85 98
Manchester 2850 258
Sagamon 987 526
So when a user clicks a county, such as "Cook", this is displayed
Years
Age Gender Total Crashes Fatalities
0-8 Female 25 8
Male 55 15
9-15 Female 850 258
Male 185 35
16-22 Female 87 526
Male 10 35
But I am getting this in :
Years
Age Gender Total Crashes Fatalities
0-8 Female
Male
9-15 Female
Male
16-22 Female
Male
Whats going on? Can anyone make any suggestions? Just to let you know its based off of an Analysis Services Cube and not relational tables.