Two Queries From Two Different Databases = ONE Display/Table
May 4, 2007
What is the best approach to handle this situation? I have three different databases, which has it's own stored procedure. I need to call them all at page load and piece together the data. The common demoninator is the date.
2007
JAN
FEB
MAR
APR
row 1
50
60
89
63
row 2
44
21
62
46
2006
JAN
FEB
MAR
APR
row 1
60
90
65
41
row2
984
650
452
762
Row 1 and Row 2 come from two different databases and stored procedures.
How can I query the data and present it as it's shown above?
Jord writes "Hi, Is there a way to retrieve or display the queries that I've run against a database? I didn't save them and now I need to know what I've done. Thanks so much in advance. Jord."
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server. I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number. The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.: 1: Database A -DSNA_tblMaiin - Serialnumber 2: Database B -DSNB_tblMaiin - Serialnumber 3: Database C -DSNC_tblMaiin - Serialnumber
I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?
Hi All, The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder. The code I have is:Dim serverConn As New ServerConnection(".SQLEXPRESS") serverConn.LoginSecure = True serverConn.ConnectTimeout = 30 serverConn.Connect() Dim sqlServer As Server = New Server(serverConn)
For Each db As Database In sqlServer.Databases ListView1.Items.Add(db.Name) NextThis does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.Any help on this will be greatly appreciated as it's starting to drive me nuts!Thanks,Paul
When you use sql 2005 management studio to connect to a sql 2005 server, you can deny view on any database and one user will not be able to see any database that he/she does not own.
However, what about if this person uses sql 2005 management studio to connect to a sql 2000 server. Since sql 2005 management studio does not call the store procedure sp_MSdbuseraccess, it will display all the database in the server.
It does not seem like I can use "deny view on any database to username" on a sql 2000 server.
Does anyone have any idea what I need to do in order to not show all the database when I use sql 2005 management studio to connect to a sql 2000 server box?
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT idname, name, address, address2, awardedtoname, suppno FROM contract INNER JOIN house ON contract.idname = house.idname) JOIN (SELECT DISTINCT tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
I have nine type of buttons, EnrollAmtBTM PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation() { WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass(); string SQLCONDITION = "";string RUN_SQLCONDITION = ""; SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null) {ValueSelected = new SessionValues(); Session.Add(Session_UserSPersonalData, ValueSelected); } else { ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]); }ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false; Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode(); SelectedNode = AgencyTree.SelectedNode; SelectedAgency = SelectedNode.Value.ToString(); Agencytxt.Text = SelectedAgency; Agencytxt2.Text = SelectedAgency; Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null; try {CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'"); } catch { }DataTable TableSet = new DataTable(); TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows) {if (IndexingValues == 0) {SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } else {SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'"; } IndexingValues += 1; }SQLCONDITION = SQLCONDITION + ")"; ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0) { Dataviewlisting.ActiveViewIndex += 1; } else { Dataviewlisting.ActiveViewIndex = 0; } SelectedAgency = SQLCONDITION; ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION; try { CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION); EnrollAmtBTM.Text = CollectionCount.ToString(); } catch { }////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg. "SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List" here is the functionpublic int CollectedFinaceDataCount(String SQLStatement) {int DataCollection; DataCollection = 0; try { SQLCommandExe = FinanceConnection.CreateCommand(); SQLCommandExe.CommandType = CommandType.Text; SQLCommandExe.CommandText = SQLStatement; ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar(); DisconnectToDatabase(); }catch (Exception ex) {Console.WriteLine("Exception Occurred :{0},{1}", ex.Message, ex.StackTrace.ToString()); } return DataCollection; }
So here mu requirement request is to display only the value fronm the view i have against the agency selected Please help ASAP Thanks Santosh
I got one table with 3 columns = Column1, Column2, Column3
Sample Table
Column1 | Column2 | Column3 ------------------------------------ A | 12 | 0 A | 13 | 2 B | 12 | 5 C | 5 | 0
Select Column1, Column2, Column3 as New1 Where Column1 = A AND Column2 = 12 AND Column3 = 0
Select Column1, Column2, Column3 as New2 Where Column1 = A AND Column2 = 12 AND Column3 >0
The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table. So how do i display the result in one table where the new Output will be in this manner
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I have a fairly complicated report consisting of numerous datasets and numerous tables.
In one of my tables I want to display the message "No data to display" if nothing comes back from the query. Currrently the table just disappears if there is no data. Is there a way to display the table if there is nothing in the query?
I have one table which contains more than 200 rows of data. Our customer wants this report to be displayed only in one page with vertical scorll bar on the right of it.
In Access, if I want to update one table with information from another,all I need to do is to create an Update query with the two tables, linkthe primary keys and reference the source table(s)/column(s) with thedestination table(s)/column(s). How do I achieve the same thing in SQL?RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part. Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it . Any ideas on how to attack this?
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique. State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks Craig
Basically, i am still relatively new to ASP.net and SQL. And i have the following query. I have a skills table to which the user enters their skills using the following fields: Skillcatagory, SKill, Current Level, Target Level, target date and comments and the serial of the user. I need to check via our staff table, which people have had a skill entered for them. And then produce a report on who has not had a skill entered for them. This table has a serial of the user column aswell which is unique. If there is more information that i can give you to assist me, please ask me. You help would be greatly appreciated.
Hi, I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape. Do you know what it may be???
Hi .. i am SqL beginner. i having trouble output what i want from table. table contain 3 columns ________________________________ |(names)|(item)|(location)| 1.| Jimmy | pizza| TX | 2.| Joe | ball | CA | 3.| Joe | ball | WA | 4.| Jim | shoes| AZ | ________________________________
i try to select all records out from this table. but column 2 and 3 contain same information in names and item only different is location. how can distinct one of them?? and display like the below, please advise.
|(names)|(item)| 1.| Jimmy | pizza| 2.| Joe | ball | 3.| Jim | shoes| ________________________________
I tried to execute sp_depends to get a list of Table dependencies as when you click on "All tasks/Display dependencies" but I only get a few not all of them.
Is there anybody who knows why I got only a part of the list
This is my case. I want in my datagrid to display first record from table A and second record from table B. This is because i need to display a price in the column where it will have current price and history price, therefore the first row will be current price and second row will be history price.
This is the output that i trying to do.
Item | Price -------|--------- A | 2.50 --------> Table A A | 1.50 --------> Table B
Please let me know if my explanation is not details enough.
I'm trying to Display 2 fields using the SalesOrderDetail table.
CarrierTrackingNumber
Count the number of CarrierTrackingNumber and give alias name of “Count CarrierTrackingNumber.”
I only want to return records where the CarrierTrackingNumber has count = or greater than 2.
Below is the SQL I developed, but I'm getting this error when I try to execute: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '4911-403C-98' to data type int.
SELECT CarrierTrackingNumber, ( SELECT COUNT(CarrierTrackingNumber) FROM SalesOrderDetail WHERE CarrierTrackingNumber >= 2 ) AS CountCarrierTrackingNumber FROM SalesOrderDetail
I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:
Create Trigger Tg_Insert on [tablename] FOR Insert AS Select * FROM Inserted --------------------------------------------------------- Assume there is nothing wrong with this trigger as I have already checked the output with Query tool. My question is how can I display the newly inserted data to the client? In my case, Console application. I want to do something like Console.WriteLine("data from inserted table here"); How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005. Thanks in advance.
The dynamic sql is used for link server. Can someone help. Im getting an error CREATE PROCEDURE GSCLink ( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int ) AS SET NOCOUNT ON --Create temp table CREATE TABLE #TempTable ( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit ) INSERT INTO #TempTable (Name, AccountID, Active) --dynamic sql DECLARE @sql nvarchar(4000) SET @sql = 'SELECT a.Name, a.AccountID, a.Active FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b ON a.AccountID = b.oaAccountID WHERE oaAccountID IS NULL ORDER BY Name ASC' EXEC sp_executesql @sql --Find out the first and last record DECLARE @FirstRec int DECLARE @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) --Return the set of paged records, plus an indication of more records or not SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec
Error: Msg 156, Level 15, State 1, Procedure GSCLink, Line 22 Incorrect syntax near the keyword 'DECLARE'.
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select clientnumber,count(distinct clientnumber) as cccnt, 0 as ckcnt, 0 as cacnt from dbo.purchases where orderdate >= 20120101 and orderdate <= 20121231 and payment_type = 'CC' group by clientnumber;
OUTPUT currently looks like this: 1234 2 0 0 1234 0 1 0 1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?: 1234 2 1 4 Y
