Silly Questions From A Newbie - Building Diagrams
Nov 23, 2004
I am setting up a development server with SQL 2005 Dev Ed beta 2. I have been going around in circles trying to find out how to create a database diagram. Microsoft really knows how to make things difficult! Would someone mind telling me how to build a diagram of my new database?
Thanks!
Dan
View 9 Replies
ADVERTISEMENT
Mar 2, 2007
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
Thanks in advance,
DBLearner
View 3 Replies
View Related
Sep 18, 1999
Help Please - JMail - SQL SPs - Confirmation Email
I'm at the last stage of my current project and an totally stuck.
I'm trying to build the body of a order processing request email. For security reasons I wish to use a SP to build and send the email. (therefore no sensitive data gets passed to the client)
I have the JMail Object running properly on the server. It collects and sends the email in the normal course of the transaction from the client. Problem is that I cannot figure out how to properly build the body of the email in the SP.
I'm looking to do add the following to a single SQL SP variable to stuff in the Jmail Object to be sent. The content of the variable should look something linke the following:
-----------------------------------------
"Order placed:" = orders.date + '<cr>?'
customers.name<cr>
orders.shippingaddress<cr>
orders.billingaddress<cr>
/*loop each record where
/*customerID and orderID match passed arguments
/***loopstart***
OrderDetails.SKU <tab> Product.Productname <tab><tab> Orderdetails.Qty <tab> OrderDetails.Price<cr>
/***loopend***
Ordertotal.GrandTotal
--------------------------------------
Any help greatly appreciated.
THX,
Charles
View 2 Replies
View Related
Jul 12, 2006
Hi,
I'm trying to build a package programatically.
I've added a variable to my package but how do you specify its datatype? Is it purely determined by the type of the value that you pass in the Variables.Add(...) method?
I've added task and a sequence container to my package - no problem there. My sequence container is referenced in a variable of type DTSContainer but DTSContainer doesn't have an executables collection. So, how do I add a task into my sequence container?
Thanks
Jamie
View 4 Replies
View Related
Sep 8, 2004
There is 1 error when i tried to install msde into my window 2003 server web edition.
1. I use command prompt to enter c:\sql2ksp3>setup SAPWD=aA1234567
the errors is "go to the control panel to install and configure system components"
How do i install ?
Sql client for 2003 server web edition
2. i tried to install sql client in window 2003 web edition but the msdn cd could not start after i double click on the setupsql.exe. I thought the sql client can be install but not the server on web edition ?
View 2 Replies
View Related
Jan 29, 2006
I am a Sys Admin and I am studying SQL to obtain a dba. I am currently running through the 70-228 book and I have a couple questions maybe someone can set me straight.
For SQL Server backups we use Tivoli Storage Manager.
I am currently reading up on DTS, I have used DTS to move databases before. It is pretty straightforward. My question after reading about the usefullness of DTS packages is what are they used for commonly by DBA's? Are they used for backups or are they for pushing transformed data to secondary applications/db's. I see how it works but I want to connect the dots with real world uses.
Another question is about how much you tweak a SQL 2000 db. I have been told by people smarter than me, that very little is done to the DB's after they are created because SQL does a excellent job of optimizing itself. If I have a DB server that is suffering from a lot of I/O overhead what is a way that I can diagnose what is causing the issue. How could I tell if it was hardware growing pains or poor design?
View 1 Replies
View Related
Feb 25, 2004
I am pretty new to databases and was wondering whether you guys can help me out or point me in some sort of direction. I am working on my senior design project for college and my group decided to use SQL Server for our databasing needs. So far we installed SQL Server and created out database with permissions and all that stuff. So here is my question:
As of right now we are talking with a company (sniffer software) to get access to some of there code. This program provides information such as host ip and mac address, destination ip and mac address, protocol, port number, bits in, bits out, etc. What we wanted to do was get access to where this information is stored in there program and then push it to our database so we can do analysis on the data. But aside from getting access to this information in the program (we looked into writing an application that does all this ourselves but we are on a time limit) I am confused how to set the database up so that we can have automatic updates to the database using the information we pull from the program. I did a search here and did not find anything and i have been looking on google and the such and haven't had any luck finding anything that will point me in the right direction. If any of you guys can help us out with information it would be greatly appreciated
thanks
Evan
View 14 Replies
View Related
May 8, 2007
Hi all, ive just started on SQL server 2005 and Visual website developer. I have managed to start up a database and display it on the server.
But for my website, i need place 6-8 links to a catagory,and then to a sub-catagories. Im not sure on how to start such a thing and how to place links really - any chance of possible tutorial on the net? Thank you.
View 1 Replies
View Related
Jun 13, 2007
I have a fair bit of experience in designing database apps using VBA with MS Access for use on a single PC or for multiple users on an office LAN. I also have some limited web design skills using FrontPage 2000 and Adobe GoLive. However, I am now trying to find the best way (for me) to create a website with an associated online database - something I have never done before. Ideally, I want cheap or free software. I would also prefer visual design tools, like the Access interface.
I have some newbie questions:
SQL Server Express looks like it might be part of a solution. However, I would appreciate some guidance on the limitations of using this free software. What are the situations that would create a need for upgrading to one of the paid for versions of SQL Server?
Would the free webspace facilities provided via many ISP broadband services be suitable for hosting an SQL Server Express database or does this normally only come at extra cost?
What web design tools enable visual design of a website using SQL Server Express?
I have no experience of either SQL Server Express or MySQL, but are there any good reasons why one might choose one over the other?
Any guidance would be much appreciated. I realise I have much to learn!
David
View 13 Replies
View Related
Jun 2, 2006
just finished installing SQL Express, and now, dont know what to do next :)
- I have installed FULL. Does it have any GUI tool for me to create a database?
- how can I login into system?
- how can I restore a backup file? I have a backup file from SQL 2005 standard
- how can I create backup for databases?
thanks
View 8 Replies
View Related
Nov 20, 2006
Just starting out with datamining and all the endless posibilities with it. One of my first questions are is it possible to datamine n-series data.
For example:
Serie 1: 10, 12, 14, 16, 12, 10
Serie 2: 2, 3, 4, 6, 3, 2
Serie 3: 30, 24, 20, 10, 24, 30 (a sort of an inverted pattern)
Lets then say somebody add another datapoint in Serie 1, so...
Serie 1: 10, 12, 14, 16, 12, 10, 12
Serie 2: 2, 3, 4, 6, 3, 2, ?
Serie 3: 30, 24, 20, 10, 24, 30, ?
Can datamining tell me Serie 2 and Serie 3 missing datapoint based upon Serie 1's new number?
This is indeed very simplified data, but I hope the it's possible to see the logic in my question
View 1 Replies
View Related
Jul 1, 2007
Dear Friends,
I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
which appears in event handler for saving in line i've indicated.
Me.Validate()
Me.LudzieBindingSource.EndEdit()
Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie ) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code:
exec sp_configure 'user instances enabled', 1.
Reconfigure.
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises
Cannot open user default database. Login failed.
Login failed for user 'FS7120Mm227'.
which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
Michael.
View 5 Replies
View Related
Mar 17, 2006
I've been learning SSIS and the BIDS for a few weeks now and there are 2 things that really annoy me. I'm hoping that there is a setting or option or something that I'm missing.
1. I place objects on the Control Flow surface, get everything arranged the way I want it, constraint lines all nice and tidy and then save it. When I open it things aren't the way they were when I closed it. Any way to make them stay the same?
2. If I select multiple objects and copy them when I paste them they are in really interesting places. Any way to have them stay in the same realitive positions?
This is one of the most helpful forums I've ever posted in so thanks to you all for the assistance.
John Colaizzi
View 5 Replies
View Related
Jan 28, 2007
Hi, I have a report with 3 parameters. One of which is Company name. To get the available list for this I use a query: select distinct name from companies, etc. However, I also want the user to be able to select "All" or an equivalent to get all the companies. I tried putting * and % in the default values (non-query), but the drop down list displays "<select a value>". Also I've enabled drill-down, is there a way to auto-expand the results?
Thanks, Dan
View 5 Replies
View Related
Aug 2, 2006
Running SQL Server 2000 on various servers. On my desktop when I open Enterprise Manager I notice that some of the instances show a green arrow and some show a blank circle. I imagine that might have to do with either permissions or the account that the SQL Server Service is running on. Is that correct?
I am using the sa account on these instances and they are all using mixed mode authentication.
Also, I can not stop or start a SQL Server Service from my Enterprise Manager. When I try to start the service from EM I get an 'Access Denied - Error 5 ' message.
Shouldn't I be able to stop/start these services without going to the box they are on?
Thanks in advance.
View 2 Replies
View Related
Jul 20, 2007
I am using Merge Replication for my scenario (POS). I have made the publication (articles are a set of tables in DB ABC) and its subscription (in same DB and diff tables xyz1, xyz2). I have scheduled the push subscription as run continuously between Dates(e.g current date to 3 days ahead, just for testing). Now, Can I change this schedule? How can I edit/remove the artilce from the above publication?
View 1 Replies
View Related
Dec 7, 2007
Hi All
Well I have some queries as a newbie to SRSS 2000 reports deployment.
1) I have to build a VB .NET application with a web interface to display reports in that program. Is it possible? Is there any example codeing floating around which I can take a look? Or how to do it?
2) I have to pass the parameters for the report via a VB text box into the report and the report should be displayed. So the parameter bar in the report as it comes in IE7 should not come. How can I do that?
Or in simpler terms
- How to turn the SQL 2000 reporting features off to allow a
simpler report display
- How to display the report in a Microsoft Windows Form
application.
Hope I have made myself clear.
Thanks
View 4 Replies
View Related
Apr 5, 2008
Sql 2005
I want to grate execute permissions on my stored procedures to a role. While creating the role, it asks for "schemas owned by this role".
To me, the schema is merely a namespace that allows you to group objects, but arent schemas such as db_datawriter roles that are central to the db and only admin type users should have ownership of these, correct ?
In a nutshell, I want to:
create a new role and assign a user to that role
with a stored procedure, grant execute permissions to this role
I was confused by the sql 2005 dialog that asks me to take ownership of roles such as db_datawriter, db_datareader etc, wouldnt that mess up other things with the database ?
help ...
I think its time I review all of the above items
role
user
login
schema
View 4 Replies
View Related
Jun 22, 2006
1) I have the Beta Preview of Roger Wolter's book on the subject. Has anything major changed from the beta version to the full version (of the book as well as the product)?
2) I want to build a front-end that queues requests for processing that is ultimately done by a standalone console application. The console app knows nothing about SQL Server 2005, so the database will have to fork/execute this app, pass it arguments on its command line, and pick up the result (written to either standard output or standard error) when it finishes. Is this possible with Service Broker? If so, how?
TIA
Andrew
View 3 Replies
View Related
May 10, 2007
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
View 12 Replies
View Related
Sep 12, 2007
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
View 4 Replies
View Related
Feb 24, 2007
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
View 1 Replies
View Related
Jul 1, 2004
I Imported a table over to sql 2000 server(through the DTS), then I exported the table to access 2000. After creating my forms my users were trying ot add data but couldnt, they kept getting an error message saying these records are not updateable. After some research I found that I never appointed a primary key, so after doing so the records became updateable. constant learning process adn I love it :)
View 7 Replies
View Related
Jun 11, 2001
Is there a way to restore just the data.mdf file and accept an existing log?
Current state: A client has a backup where the data file is 400mb and the log is 4.99gb. The avaiable hhd space is 6gb.
Restoring the backup to a test database fails.
Objective: Check the calendar file for missing data, and if found, update the live calendar with the missing items.
We don't need the database to be fully operational, we only need a particular table. I'll write a cursor if needed to update the live cal from the restored backup.
I suspect that the available diskspace is causing the retore to fail.
TIA
View 3 Replies
View Related
Mar 25, 2003
After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.
I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.
Am I F&%ked?
Any help in sorting this out will be rewarded with much kudos and thanks
View 12 Replies
View Related
Jul 8, 2004
I wanted to create a table with an exsisting table, then create a relationship between the two. The table being created needs a DocID autonumbered primary key, is that possible to create through sql. An autonumber like access has, through a query or something. should I just have a insert into query or something?? how would I go about doing that.
Goodmorning Guys :)
View 3 Replies
View Related
Jul 22, 2004
I can execute a stored procedure I created in the Enterprise manager cant I??
View 8 Replies
View Related
Jan 23, 2006
Yes this is a silly question, but I don't know the answer!
I have developed a database using SQL Server 2K. I am now upgrading to SQL Server 2005. Can I still use my current database files in SQL Server 2005? If I can, do u have any idea how I can make SQL Server 2005 load up the old files and start working?
Also my hosting provider has NOT upgraded to SQL Server 2005. He will only accept the old SQL Server 2K files. Can SQL Server 2005 save files that will work on a SQL Server 2K server?
Thank you!!!
View 7 Replies
View Related
Mar 16, 2007
Hi all. I think its a silly question to ask. What is better to use? I mean in terms of performance.
c.* or c.field1, c.field2, c.field3, c.lastfield..... ?
thanks
-Ron-
View 2 Replies
View Related
Jul 20, 2005
HiI've not used SQL Server for a while, and I've forgotten how you hide allthose system procedures (beginning with dt_) in Enterprise Manager.Could some kind person please refresh my memory?ThanksCaptain Nemo
View 2 Replies
View Related
Mar 3, 2008
In a situation when you have a power cut, and then sometime later 'most' of your sql servers come back on line, is it better to leave them all down unless they all come back online, or is it better to let some of them come back up knowing that the ones that do come up will have job failure issues with the ones that are down. I pose this question purely from the perspective of scheduled job problem as we do not have people on site when we have intermittent power cuts at weekends. What would scheduled jobs which are due to run , but miss their run time as we leave the servers down after a power cut till we get back in on Mondays do when we do actually re-power them up, would they just resume from their next scheduled point, or would they try to run as often as they should have run?
Dave
View 3 Replies
View Related
Apr 24, 2007
Hi everyone,
Inside a Script Task I€™ve got this line:
Dts.Variables("Var1").Value = Dts.Variables("Var2").ToString
After that, I get this value for Var1:
"Microsoft.SqlServer.Dts.Runtime.Variable" {String}
String: "Microsoft.SqlServer.Dts.Runtime.Variable"
Does anyone have any idea about the hell is happening here?
Both of them has been defined at the same scope and own String as data type
Thanks in advance and regards,
View 3 Replies
View Related
Oct 11, 2007
I will give you the simplest version of this I know if.
I have 3 tables.
Person Table
PersonID, Forename, Surname
Event Table
EventID, EventName
Involvment Table
PersonID, EventID
In this, the Person table's primary key is PersonID, the Event table's primary key is EventID and the Involvment table's primary key is PersonID, EventID.
There is also a foreign key constrant between Person.PersonID and Involvment.PersonID and a foreign key constraint between Event.EventID and Involvment.EventID.
The sql to create this would be
CREATE TABLE [dbo].[Person](
[PersonID] [int] NOT NULL,
[Forename] [nchar](30) NOT NULL,
[Surname] [nchar](30) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Event](
[EventID] [int] NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Involvment](
[PersonID] [int] NOT NULL,
[EventID] [int] NOT NULL,
CONSTRAINT [PK_Involvment] PRIMARY KEY CLUSTERED
(
[PersonID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([EventID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Event]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Person]
As so obviously stolen from SSMS.
Now what I am having problems with is if someone is involved with more than one event, then I only want them to get listed once.
If we have the following in the Person table.
0
John
Doe
1
Jane
Doe
the following in the events table
0
This
1
That
and this in the Involvment table
0
0
0
1
1
1
then when doing any select using the Involvment to get name and event information from their respective tables, there will be two entries for John Doe. I don't want this if it is possible. Although the same event multiple times is ok in this case.
So, if someone could help with this then it will be greatly appreciated. I'm still not that great with SQL so this is a problem which has been annoying me.
View 4 Replies
View Related