|
"I wanted to use a single SQL statement since this is web-based and the less calls to the database the quicker the website will load. "
If you look at the time it takes for a web page to load, the call to the database is the fastest part. If you want the page to load quickly, keep it as light as possible. No graphics, simple layout, limited javascript, etc.
You may be looking to optimize a portion that really doesn't need to be optimized. If you want a quick web-page, maybe consider using Ajax so that you are not pushing and pulling the entire page around like you do with a traditional postback.
Just something to think about.
|
|
|
|
|
Agree with Blue_Boy, Union All, just give a type, description, and ID to each subset you need like this:
Select 1 as nType, Instructor_Name as sName, Instructor_ID as nID
From Instructor
Where (optional condition)
Union All
Select 2 as nType, Class_Name as sName, Class_ID as nID
From Class
Where (optional condition)
...etc, etc,
Order By nType, sName
add to combo boxes based on nType
"Go forth into the source" - Neal Morse
|
|
|
|
|
Using UNION ALL as suggested by some folks would not be an optimal solution, because the time taken to access the tables would remain the same, you would just end up saving the time to open and close additional connections (which ASP.NET saves you anyway through connection pooling).
Since the Instructors and Courses do not change often, you can use the System.Web.Caching.Cache class to load the data from the database in the event and bind the controls from the Cache. There are mechanisms in SQL Server to hook on to Table change notifications and invalidate the Cache. So anytime the data in the base tables change, your Cache gets invalidated and the fresh data is loaded.
Here's an example on how to use the Cache and SqlCacheDependency classes: http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx[^]
|
|
|
|
|
I'm not sure I understand what you want to do.
You have several different drop-downs which hold unrelated data from different tables in the database.
You want to populate all these different drop-downs from a single result set produced from a single database query.
How is that going to work?
|
|
|
|
|
I think you need to go back and look at your datadase design. You say there is no connection between Instructors and Courses but I think, in reality, there is a many-to-many relationship. You can achieve the connection with a linked (or intermediate) table which only contains the two primary keys - one from each table. Then create foreign keys on this table to the Instructors and Classes tables. The Primary Key of the linking table needs to be the composite of the two elements InstructorID and CourseID - see below.
Instructor Table: InstructorID, FirstName, LastName,...<br />
Course Table: CourseID, Subject, DateStart, DateEnd,.... <br />
InstructorCourse Table: InstructorID, CourseID
I presume you don't want Lecturer A being able to teach Geography when really his/her subject is History, but Lecturer B can teach Mathematics and Statistics - no problem.
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
AnnieMacD wrote: which only contains the two primary keys
Two primary keys? I presume you meant to say "a two column primary key".
|
|
|
|
|
No, she's correct. This isn't the primary key for the middle table but the elements from the two other tables that are primary keys in those tables.
Like so:
Table A
PKA
Instructor
Table B
PKB
Class
Table Middle
PKMiddle
PKA
PKB
The primary key for the middle table is neither PKA nor PKB in that case.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Then, it's no longer called Primary Key, we call it a Foreign key. A table can have one and only one Primary key.
|
|
|
|
|
But the Primary Key can be a composite.
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
AnnieMacD wrote: But the Primary Key can be a composite.
Yes, of course. I know what you were talking about. But a beginner who happens to see this thread might think that a table can have more than one Primary key.
|
|
|
|
|
Thanks for pointing that out. I supplied the script for creating the linking table to avoid any misunderstanding. 
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
Yes, and we all know that.
She was referring to them in explanatory fashion.
There was nothing wrong with her explanation.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
smcnulty2000 wrote: Yes, and we all know that.
I agree, but a beginner who happens to view this thread might get confused and misled.
|
|
|
|
|
Fair enough. I took your original use of the term as condescending.
Perhaps it wasn't meant that way, but, oh well.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
The Primary Key for the linking table is a composite of InstructorID + CourseID (in my example). This then makes it unique which a Primary Key has to be. There is NO other data in the linking table.
This then effectively creates a many-to-many relationship.
Here are the commands for creating the linking table assuming you have a Course table with Primary Key CourseID and an Instructor table with Primary Key InstructorID.
CREATE TABLE [dbo].[CourseInstructor](
[CourseID] [int] NOT NULL,
[InstructorID] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[InstructorID] 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].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Instructor] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Instructor] ([InstructorID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Instructor]
GO
It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
|
|
|
|
|
I think several good points have been made in this thread.
You could, if you are bent on doing this, use the row number function and join based on that.
select * from
(
select
name
,row_number() over (order by name) rowa
from instructor
) tableA
full outer join
(
select name Classname
,row_number() over (order by name ) rowb
from class
) tableb
on rowa=rowb
This should give you a listing of columns from table a, and table b as if they'd been put together into a spreadsheet. Then you just have to be sure you don't have a duplicate column name (as I showed in the second subquery).
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
So I've built a nice big Stored Procedure and call it from my SSIS package.
My SP uses a temp table since it uses nestes SP's.
Here's what I tries:
+------+-Table variable-+-Temp Table-+--Temp Table from SQL Server Management Studio--+
|Result|Failure | Failure | Success
It always fails unless I force a 'create table' from SQLSMS and keep SQLSMS open, the moment I close it the temp table gets dropped.
What on earth is going on?
I can only guess it's got to do with permission but I wouldn't really know where to look.
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
|
|
|
|
|
Your question is not clear. Where do you create the temp tables? In your SP or in SSMS ? Would you show us some code?
|
|
|
|
|
Sounds like the account you are using from the SSIS package does not have the correct permissions compared to the account you are using while connected via SQLSMS.
Have you added logging into your SP so any errors get written to the SQL Event Logger (there are maybe errors already being logged which will give you a clue).
|
|
|
|
|
Try using ##yourtemptablename when creating the temp table.
The ## makes the table usable by other sessions.
|
|
|
|
|
nbgangsta wrote: My SP uses a temp table since it uses nestes SP's.
Where is the temp table being created at?
nbgangsta wrote: the moment I close it the temp table gets dropped.
That is how temp tables work, as soon as the process that created the temp table completes and returns (exits scope) all temporary objects such as temp tables and variables get cleaned up (ie dropped)
What are you trying to accomplish with the temp table? Return results to the SSIS package?
I am guessing here that you are looking to parse the results of the stored proceedures in the SSIS package.
-> In your SSIS pacakge
-> Execute SQL Task
-> General tab, Change ResultSet to 'Full Result Set'
-> Result Set tab, 'Result Name' = 0 and 'Variable Name' = User::User_Defined (Variable of type object)
-> Change stored proceedure
ALTER PROCEDURE test
...
CREATE TABLE #Results(msg VARCHAR(200), LogTime SMALLDATETIME DEFAULT GETDATE())
INSERT INTO #Results ("Start first child proc")
EXEC sp_Test_First_Child_Proc
INSERT INTO #Results ("Completed first child proc")
INSERT INTO #Results ("Start second child proc")
EXEC sp_Test_Second_Child_Proc
INSERT INTO #Results ("Completed second child proc")
...
SELECT msg, LogTime FROM #Results
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Hi , I'm doing log shipping for publisher database (Marge Replication).
I configured my publisher db for log shipping its doing work well. All transaction logs are getting restore after a time.The publisher Database is readonly/Offline mode.I then activate/Online this db.
As we also need master,Msdb and distributor (if same publisher is the distributor) databases to make secondary server as publisher, i am backing up these system dbs using maintenance plans. I have no all four latest databases (i.e. publisher,distributor,msdb and master db).
My publisher database is online and latest,I restored latest backup of msdb and distributor that is backed up by maintenance plans.When i restore master database my publisher database is no more available , i can just see its name icon in management studio but when click on it nothing to expand/attached with this database.
Both Primary server and secondary servers have SQL server enterprise with sp1 installed on same path. I wanna to just rename secondary server SQL instance name & computer name and it should works just like my primary server as a publisher. Help will be appreciated.
Thank you
|
|
|
|
|
You need to have the secondary server in "standby mode" rather than "no recovery" to access the data on the second server.
|
|
|
|
|
We have an application that combines data from multiple business areas. (POS, Inventory, Accounting, Payroll, etc.) Most of the time, these sources are from different vendors and most of the time these vendors utilize SQL Server. For years, we have simply worked with the IT departments of our customers to create sql logins for these other databases (db_datareader role only!) in order to extract data used by our application. This usually involves the customer sending us a backup that we restore, analyze against a report set, and build queries against. The goal is to solve the business need of the customer who needs to have these many puzzle pieces in a single place...we are not competing with the other vendors, only making use of the data from their systems. The reason for this post is that last week, I got a call from an angry software developer for one of these vendors who wanted to know who gave us permission to connect to their sql database and blaming us for missing records. I expained the concept of the db_datareader role and expressed my opinion that the data belongs to the customer...the customer gave us permission for the connection in order to fulfill a business need. He said he will be contacting his legal experts on the matter. So, how would you feel if you found out another company was mining your database?
"Go forth into the source" - Neal Morse
|
|
|
|
|
This all depends on the contract between the other vendor and the client. Almost certainly the data belongs to the client, however the structure the data is contained in may well be the IP of the vendor and having another SW house ratting arround the structure could be percieved as infringing on their IP.
Most vendors needing to protect their IP in the structure will publish views that can be consumed by the client (and other vendors).
I suspect his legal experts are going to tell him to pull his head in!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
