Stored Procodure To Connect 4 Tables

Apr 28, 2006

I have 4 tables connected with one particular Id.

I will explain the table with sample data to make it more clear.

table 1                                                             Table 2

Id(PK)       Firstname   Lastname                   T2Id(PK)   Id(FK)           Pfirstname     Plastname

65                   a                  b                                 22             65                     AA                        BB

                                                                              

Table 3                                                                                    Table 4

 T3Id(PK)     Id(FK)     Firstname Lastname                     T4Id(PK)  Id(FK)   T3Id(FK)  Pfirstname   Plastname

45                  65               c                 d                                       33            65            45            CC                     DD

46                  65               e                 f                                         44            65            46            EE                     FF

I want the output to be

               Firstname          Lastname      Pfirstname       PlastName

                  a                               b               AA                           BB

                  c                              d               CC                              DD

                  e                                f               EE                              FF

I am able to join table3 and table 4 to get the last 2 rows of output but not able to get all three rows by inner join of 4 tables.

 

pls help me

 

 

View 2 Replies


ADVERTISEMENT

Stored Procodure??

Dec 17, 2006

Hi,

What is a stored procedure? How it will work out? Any help or Referance links?

Pls anybody can help me !!

With regards
Shaji

View 3 Replies View Related

How Can I Connect Tables.

May 27, 2007

Hi,How do i connect tables so they are interrelated to each other. like the asp.net 2.0 membership tables have the "id"  and how can i use that? i have multiple tables that are all connected.like i have a table for profile. and images profile with include their profile, while images will include there avatar.now when i display there profile on page, i need to grab the data from both the profile table and avatar. right now i've been using the "JOIN" statement. but is tehre any other way 

View 6 Replies View Related

How To Connect To SQL Database, Create Tables

Jan 2, 2006

Hi All, 1st of all happy New Year to all asp.net forum members
    I am new at asp.net. I want to design a website
using asp.net as frontend and sql database as backend. I am able to
connect and add,update as well delete records when I use MsAcess and
Asp.net using the following connection strings...
**********
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
**********

But when I try to connect to sql database using the following connection strings I am unable to do so...

**************
SqlConnection myConnection = new SqlConnection("server=PLATINUMVSdotNET;database=pubs;Trusted_Connection=yes");
        SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);

        DataSet ds = new DataSet();
        myCommand.Fill(ds, "Authors");

        MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
        MyDataGrid.DataBind();

**************
I have written the servername as "PLATINUMVSdotNET" because when I
installed SQL SERVER 2000 I found a tray icon where the server name was
displaying the same (my computer name is PLATINUM).
When I used webmatrix I enterd the same server name and  windows
authentication I was able to create a database but How to create
table...

Please help me out
Thanks in advance...

View 4 Replies View Related

Best Way To Connect MS Access To SQL 2000 Tables?

Oct 5, 2005

Hello All;

I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?

Can anyone answer that simple question?

Grarful for ANY resonse,

Larry.

View 2 Replies View Related

How Do I Connect To Tables Within Another Database Via View Designer/gui

Nov 21, 2007

I'm using the gui to create a view which requires my connecting to another database on the same server. I've tried to create synonyms; however, I can not access them (they do not display) on the synonym tab when I try to add them to the view.

I'm a novice so any suggestions to get me on my way is greatly appreciated.

Thank you in advance,
Steve

View 1 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Can SAP BI Connect To SQL Server 2005 And Read Tables And Columns NOT In Upper Case?

Apr 25, 2008

An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?

View 4 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Connect To Another Server From A Stored Proc

Sep 21, 2004

I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?

thanks
D.

View 1 Replies View Related

User Who Can Connect/run Stored Procedures Only?

Nov 6, 2007

I'm new to 2005 (new to sql server in general). Up until now it's only been writing easy stored procedures. Read that it's a good idea to have a user who can only run stroed procedures (so aren't connceting with 'sa' privileges and such). How would I go about doing this?

The server I am working on has about 15 different databases on it. My database (named "myDBForTest") is the only one I want my end user to be able to connect on. I'm writing a .NET application that will have several users using it at same time. They will all be connecting with same information (limited to only running stored procedures and connecting/disconnecting). I basically want them to be able to run any stroed procedures that are part of my database. If I go back in 4 months and add 100 stored procedures, the next time the log on they should be able to use those new ones also.

Is there some kind of command I run to add a user (maybe with username of sprocUser?) who can do this? I'm interfacing with database using SQL Server Management Studio Express (from MS).

Thanks for any help you can give.

View 2 Replies View Related

Connect To Microsoft Access Via Stored Procedure

Sep 29, 2006

Hi,

I'm new to SQL server, still in the beginning stage of learning SQL Server. I'm here would like know, besides using the Connectivity from the DTS Designer to connect to different databases, is it possible to connect the database, i.e: Access via stored procedure? and how? Pls advise...

I'm have been trying to look for the solution via a lot of SQL Server site, but fail to get what I want.

What I'm trying to do is something like :
First connect to the Database and Query the data, after that insert it into another database....

View 3 Replies View Related

How To Know All Tables In A Stored Proc

Sep 28, 2007

Im trying to find a way without doing it in excel or something by hand to show what tables are used in each of my many stored procs. I didnt know if there was a way to do this with say another stored procedure or what not. Any input would be greatly appreciated.

View 3 Replies View Related

Tables And Stored Procedures

Jul 20, 2005

SQL Server 2000:Question 1If you drop / rename a table and then recreate the table with the SAMENAME, what impact does it have on stored procedures that use these tables?From a system perspective, do you have to rebuild / recompile ALL the storedprocedures that use this table?I had a discussion with someone that said that this is a good idea, sincethe IDs of the tables change in sysobjects and from a SQL SERVER query planperspective, this needs to be done...Question 2If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if anerror occurs? Will it Rollback? The theory is that it won't becauseTruncate doesn't utilize the logs where as Delete From uses the SQL Logs?Thanks!

View 3 Replies View Related

INSERT INTO Tables Using Stored Procedures

Apr 28, 2007

Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using: 

View 5 Replies View Related

Inserting Into Tables Using Stored Procedure

Dec 7, 2007

I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
 
 

View 4 Replies View Related

How To Copy Some Tables, Stored Procedures

Feb 14, 2008

hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks 

View 3 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you

View 4 Replies View Related

Copy DB Tables And Stored Procedures To A New DB With Different Name

Feb 14, 2006

Hello all!
What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB???
So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?
The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!
Any help would be greatly appreciated!!!

View 3 Replies View Related

Stored Procedures And Multiple Tables

Mar 2, 2006

Hi,
     I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table.
i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates.
As I know nothing about stored procedures I'm not even sure how to go about this??
Regards.
Peter.

View 2 Replies View Related

Searching Thru Stored Procs Via The Sys Tables

Sep 3, 2003

Basically, I want to be able to have a stored procedure that will search through all the stored procedures looking for a given string and returning the names of all the stored procs that contain that string value.

I know I can script it off and then do a text search in Notepad or whatever, but I figured there must be a more elegant way to do it. More than likely dealing with the DB sys tables.

Is there already a tool in SQL Server that does this? Or has anybody had a chance to roll their own?

Thanks,
Tim

View 4 Replies View Related

Stored Procedure Checking 2 Tables

May 12, 2004

ok, ill lay it out like this, and try not to be too confusing.

I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,


i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item


so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.

well i tried not to be confusing, but i think i failed

View 1 Replies View Related

List Of All Tables Or Stored Procedures

Jan 17, 2008

Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.

View 3 Replies View Related

Using Temporary Tables Within The Stored Procedure

Nov 15, 2006

Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu

View 1 Replies View Related

Temp Tables In Stored Procedure.

Oct 22, 2007

Hi

I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.

I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.

Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.

Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.


Thanks

View 13 Replies View Related

Multiple Stored Procedures ,same Tables

Sep 3, 2007



Hi,

I am using multiple stored procedures which are using same set of tables at a time .
As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.
My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins
where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you
Raj Deep.A

View 4 Replies View Related

Creation Of Two Tables In A Stored Procedure

Oct 17, 2006

Hi,

I was wondering if there was a way to create two temp tables within the same stored procedure.

Can we have two create statements one after the other?

Thanks


View 2 Replies View Related

Stored Procedures With Temp Tables

Nov 23, 2007

If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?

View 3 Replies View Related

Multiple Stored Procedures,same Set Of Tables

Sep 3, 2007


Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

View 2 Replies View Related

Joining Two Tables In Stored Procedure

Apr 28, 2008

Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.


INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

Table2
(NEW DATA)
SYMBOL LAST TRADE






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20

OUTPUT DATA:
(OLD DATA) (NEW DATA)










as
SYMBOL CATEGORY SHARES Prev.Close Last Trade






ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY

electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma

211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746

1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55

1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20




View 1 Replies View Related

SQL 2012 :: Applications Cannot Connect Because Logins Cannot Connect To Their Default Databases

Oct 17, 2014

I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn?

View 9 Replies View Related

Change Schema Name On Tables And Stored Procedures

Sep 12, 2006

Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved