|
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
|
|
|
|
|
kmoorevs wrote: how would you feel if you found out another company was mining your
database?
But it isn't, it's the customer's.
At any rate, I haven't done that from a third-party company, only from the customer. On my last job, one of my primary tasks was to copy data between different databases (Ingres and SQL server in particular).
The vendor that used Ingres didn't seem to mind. If I wrote to the database directly it crashed the system (yes, I did that more than once -- yes, the production database), but I could read the database. Getting data in was more of a problem, but doable.
The vendor that used SQL Server wasn't very happy:
0) At some point before I got there someone had created some tables in the application's database (a big no-no) -- and it was some time (years?) before anyone realized that they had broken the backup. 
1) We had asked the vendor for an API so we could update the database; they said they would, then hemmed-and-hawed, then delivered a unusable tool. 
1.a) In the meantime, I had asked tech support whether or not I could set certain fields and he said I could. So I did. So by the time their management found out and freaked, it was too late. 
Oh, and in both cases, the vendors knew what we were trying to do. It sounds like, in the case you describe, the vendor didn't know, and that's the real problem.
|
|
|
|
|
"the vendor didn't know, and that's the real problem." True and False...the vendor knew about the first client we connected to, some 8 years ago, they just didn't know that we were connecting to other clients we have in common. Originally, they had provided the field layouts for their summary tables of the data we were requesting at that time. Over time, we have identified other areas of import from this vendor's database...and we built the queries, verified the results, and made life easier for our common clients, with the cooperation of the clients' dba. In my experience, database admins feel like they own the data/databases and the idea of automating an export/import process appeals to them.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Basically that is what lawyers and written contracts are for.
I am fairly certain that if you have been accessing the their data for some time and delivering value to them from that then they have no case. Unless there is a contract that explicitly forbids it.
In terms of destroying their data they would need to prove that.
|
|
|
|
|
Just got an email. They are asking for full disclosure of common clients and queries...nothing threatening, actually quite positive. This is fair enough, and probably should have been in practice before. As for the missing data, it was explained as an internal issue. Apology accepted. We'll see where it goes from here. Think positive.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Sounds like a bad idea to me.
They are asking you to reveal your business information to them. Information that should have nothing to do with them.
What are you going to do if they take that client list and tell those other people that they must drop you?
kmoorevs wrote: This is fair enough, and probably should have been in practice before
If your business was based on a smile and a handshake and the other side is no longer satisfied with that then it is time to use a contract.
|
|
|
|
|
But did they actually use the phrase "Full Disclosure"?
Doesn't that sound like their lawyer asked them to get the list?
Think carefully about how you answer them. If your company has a lawyer I'd run it by them. The developer threatened legal action and now wants this piece of information. Perhaps you should take the threat seriously.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Unless it is spelled out differently by contract, I'm not sure how the data ever belongs to the vendor. And I wouldn't sign such a contract with a vendor, I'd tell them to pound sand.
Most of my customers business IS their information or tied directly to it. If the vendor owns it then the vendor can tell you that if you migrate away from their product that you can't take the data with you. Who would agree to such a thing that was thinking?
This would be like Microsoft insisting that they own your documents because you wrote them in Word.
I also wouldn't give these guys a list of common customers. They are considering a lawsuit and they want to have 'evidence' that you've been tampering with their clients and they want a way to calculate damages- so they can decide to move forward, or not.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
Hah! Seems they have done some homework...I got an email this morning with a sql script attached showing all the queries used against their database...must have started a trace. The email informed me that the queries passed QA. A half-hour later, I get another email stating that our request for access their system has been approved pending the acceptance and signature of a formal document. The document simply states that we acknowledge and respect their database and all objects contained therein or created thereby as intellectual property of the vendor. We also agree to identify them as the source of any data retrieved from their system, (already in place) and to destory any backups or databases we may have obtained from the client. (sure thing) As a sidenote, the email stated that 'clients have a favorable opinion of your company and consider the connector to our system as 'integral' to their business objectives'. I have a new contact with their dev team and a promise of cooperation should I need it. We are reviewing the document now. I may have to delete this thread as it is now showing up in google searches on the subject!
"Go forth into the source" - Neal Morse
|
|
|
|
|
This seems to answer the question. The vendor owns the database...structure and objects. The client own's the data in the database. Clients also own the SQL Server license and contol access to databases. In most cases, whether or not they know it, clients have accepted a EULA that prohibits them from sharing software components (including databases) with third parties without written consent. I believe that a contract is a good idea to protect all three parties involved.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Hi,
I built an SSIS package locally and now I need to deploy it to our development instance of SQLServer...can anyone tell me how to go about doing that?
"I need build Skynet. Plz send code"
|
|
|
|
|
ok...I've got the thing deployed, but I'm trying to run it from a job. If my job is running in a database called "dev" and a package named "foo" is running in Integration Services on "dev", how would I go about referencing it in my job step command?
(just a stab)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobID, @step_name=N'Execute SSIS package',
@step_id=1,
@command=N'/FILE "\\dev\MSDB\Maintenance Plans\foo.dtsx" /CHECKPOINTING OFF /SET \Package.Variables[User::Correlator].Properties[Value];' + @Correlator + ' /REPORTING E'
Looking at my @command assignment, is anything missing?
I have the thing deployed to MSDB\Maintenance Plans, but I really don't know if the reference is as simple as this.
..Is it? I'm getting an "XML Parsing error"(<-pseudo) that seems to be a permissions error when trying to open the package. Either the package doesn't exist at the location specified or my account doesn't have access to the file...I'm pretty confident that I have access to the file, so my other logical option is that the context that I have provided in my snippet above is incorrect.
Does anyone know how I should go about referencing it?
"I need build Skynet. Plz send code"
|
|
|
|
|
|
Thank you very much for the documentation, but I had already referenced the materials at those links. My specific question was surrounding the command-line reference that I was making to my package after it had been deployed.
...at any rate, I found out what my problem was. I was attempting to reference the package with the FILE switch, but I had not deployed the package to the file system of the database server which I was attempting to reference (I had deployed it directly to the SQL Server instance)
...I still am unsure of how I would go about referencing the package from SQL Server directly, but I repeated my deployment, this time to the file system, and adjusted my reference to the location on the file system, and the package was able to be loaded at run-time.
just fyi...anyone referencing a package with the "FILE" switch, make sure your package is deployed to the file system lol ...it'll save you a night's sleep.
"I need build Skynet. Plz send code"
|
|
|
|
|
Hi
Im Ashutosh
Student In MCA
Respected Sir
I create a webApplication in ASP.Net i want to store Vedio, Audio, Images, file in my SQL Server2008 so plz give me a solution Imidiat
Yours
Ashutosh
91-8115736699
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
