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?
Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental;
drop table Property_type;
drop table Property_owner;
drop table Staff;
drop table Tenant;
drop table Tenant_category;
create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;
Error messages I'm getting;
Code:
Msg 547, Level 16, State 0, Line 55
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 57
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 58
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 59
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 82
The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'.
The statement has been terminated.
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:
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.
Hello,
First off, I'm completly new to SQL.
I'm trying to create an insert command that uses a select command as a value.
INSERT INTO Activation.dbo.Selected (PID) SELECT PID FROM ElecDB.dbo.PTT WHERE Vendor = 'guy'
What I get from this is 5 rows updated which is what I wanted.
Although, I would like to insert a UserID from C# code or for now just 1151 for example.
I'm assuming that it would look something like this:
INSERT INTO Activation.dbo.Selected (PID,UserID) SELECT PID FROM ElecDB.dbo.PTT WHERE Vendor = 'guy' , 1151
but I am not even close.
Please help me with this.
Thanks,
Guy
I have a question that I am sure is an easy answer for the pros, but being a beginner I need a bit of help. My problem is I am trying to average multiple columns and return a single output. Here is an example of the data and layout that I am looking at, and what the intended results would need to be.
TradeAmt TradePrice
100 36.75
200 38.75
100 36.80
The desired results would be the average Trade Price (36.7625 in this case). The problem I am running into is taking into account both columns. I know in other programming languages you can create a loop to multiply the TradeAmt by TradePrice, add the value to the next set of TradeAmy x TradePrice, and ultimately average it out. Since I am new to SQL I am not sure of the best way to proceed, any advice would be greatly appreciated.
Hello,
I have two database servers that I am interested in sending messeges between them. I am not interested in security or encryption at this point, only a simple message sending and recieving.
Can someone please provide simple scripts for object setup (certificates, queues etc) and message send and recieve ?
a link to such a script will be great as well. I am just a bit lost understanding all the new concepts (certificates, service, service binding.....)
Thank you
I.
I'm planning on implementing SSB with an upcoming project but it's crucial that SSB queue handle multiple requests sent by initiators. How does SSB ensure that the queue doesn't fail? Also, what measures are put in place to ensure that the messages are retrievible if the queue does fail.
Thanks
Vince
I need to restore multiple tables in a sequence. I am writing separate data flow for each table restore.
If there is any error in any of the table restore data flow, I want to write that error in a log file.
I am writing a script component in every data flow, which will get error code & error description, that i am writing in the log fie.
Is there a way to create a public reusable error logging script file, which we can call from every data flow and log only errors which we want?
Also want to confirm if the way i am doing is correct way or is there any easier/better way to achieve this?
I'd like to query my Tasks and TaskActivity Tables to return the following results:
Case Id
Estimated Hours
Total Hours
Remaining Hours
Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.
Here's my hack to get the correct results:
First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId
Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId
Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.
TIA,
Rob
Hi there, I can't believe I'm having a problem with this, but I can't seem to get this right. I'm just trying to connect to a MS SQL db (it's my first time). Here's the error I get:
Line 9: Response.Write("Hi there, here's a test for the MsSql db<br />")Line 10: sQuery = "SELECT [Player] FROM [NHL2008]"Line 11: Using connection As New SqlConnection(strMsSqlConnString)Line 12: Dim command As New SqlCommand(sQuery, connection)Line 13: connection.Open()
Here's what I've done:
in web.config I've got:
<connectionStrings><add name="MsSqlConnection" connectionString="Data Source=65.39.254.44;Initial Catalog=JustidutchDB;User ID=******;Password=******" providerName="System.Data.SqlClient" /></connectionStrings>
and in the namespaces section I've added <add namespace="System.Data.SqlClient"/>
Now my code for the aspx.vb file is:
Dim strMsSqlConnString As String = ConfigurationManager.ConnectionStrings("MsSqlConnection").ConnectionString Dim sQuery As String Response.Write("Hi there, here's a test for the MsSql db<br />") sQuery = "SELECT [Player] FROM [NHL2008]" Using connection As New SqlConnection(strMsSqlConnString) Dim command As New SqlCommand(sQuery, connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() While reader.Read() Response.Write(reader(0) & "<br />") End While reader.Close() End Using
I've used this method with Access dbs before (with the OleDb provider ) and it works great. Any ideas what i'm missing or doing wrong?
Thanks much for your time,
Justin
Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error??? I don't understand becuase it is the exact same app and database but at a remote location.
Is @IDENTITY somehow saved in the session and my remote servers session settings?? are off?
strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @ID = @@IDENTITY";
Thanks in advance for any responses.
-Wiley
Hi, I am trying to teach myself ASP.NET from the SAMS 24 Hrs book and I am trying to install MSDE for use with the web matrix project.
I think I succesfully installed it the first time I tried but entered a four digit SAPWD password not knowing it needed to be strong. I clicked on the data tab in the web matrix project as instructed to open the database and entered my user and password but it gave me some error message.
Now when I click the data tab it say I need to install MSDE on my computer and links me to www.asp.net.
Then when I try to install MSDE after it has downloaded, it gives me this error message. ----- 'Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.'---
I have tried uninstalling and reinstalling everything as well as everything else I can think of.
I would be forever grateful if someone could help me out with this.
Many Thanks
Mark
E-Mail: poolstar_uk@hotmail.com
I have a datagrid that is used to insert values into a table. Before
the insert occurs I want to be able to check that there are no
identical values already in the table. eg. I am inserting names, so am
looking for last name duplicates only.
If a duplicate is found, it should return an error message to the page.
My question is, can I do this duplicate checking via SQL script ie. IF
EXISTS etc... and have the error message returned from there, or do I
need to grab a dataset of the table and look for duplicates before even
going as far as the database with the new value to be inerted?
If the best approach is to catch the duplicate error after executing
the SQL script, how do I grab the error and return it to the page with
the datagrid? I'm sure this is pretty basic, but I am only just now
getting into it.
SQL Server 2000 is being used here.
Thanks!