Alaising The Same Table Multiple Times
Oct 18, 2004
To All--
First off, I'm new to SQL Server and apologize if this is a trivial question.
What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.
Table1
Emp_Number
Emp_Code_1
Emp_Code_2
Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc
I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.
Hope this makes sense.
C
View 1 Replies
ADVERTISEMENT
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Aug 11, 2015
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
View 2 Replies
View Related
Jan 9, 2006
Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
View 1 Replies
View Related
May 31, 2007
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies
View Related
Oct 20, 2006
I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.
Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.
I would like to do this:
Task1 ------Success-----> Set Vars -----Success--> Log
|
Task2 ------Success-----> Set Vars -----Success-| (do the Log task again)
|
Task3 -------Success-----> Set Vars -----Success-| (do the Log task again)
|
etc
This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.
Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.
Any ideas?
View 8 Replies
View Related
Oct 14, 2007
Hello,
After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication?
One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice?
Thanks!
View 1 Replies
View Related
Dec 21, 2007
Hey all,
I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:
Dim conString As String = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString
Dim con As New SqlConnection(conString)Dim cmd As New SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con
con.Open()
Dim RS As SqlDataReader = cmd.ExecuteReader()
While RS.Read()
blah blah blah
End While
End Using
Then in my other spot on the page I have the following:
Dim cmdresults As New SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")
Using con
con.Open()Dim RD As SqlDataReader = cmdresults.ExecuteReader()
While RD.Read()%>
<tr>
<td><%=RD%>
</td>
</tr>
<%End While
End Using
When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "
RD As SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.
Thanks in advance!!,
Chris
View 5 Replies
View Related
May 15, 2000
I need to create a linked server that can access more than one database.
I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the
problem is the Server Name. I cannot use the same servername twice and
if I use a name that is not an exisiting SQL server name I get an error
that "server not found".
So, how do I addlink the same server a mulitple times to access different databases in the server?
Thanks.
Ranjit
View 3 Replies
View Related
Mar 2, 2005
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
View 5 Replies
View Related
Apr 4, 2008
I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line?
To elaborate:
I have a table of backup job failures, with a reason and the time it failed.
ID | Reason | Time
1 | No tape | 3/13/3008
2 | Bad drive | 3/14/2008
I'm trying to create a summary, and I want to list the information as follows:
There were 2 failed backup jobs on 3/13/2008, 3/14/2008
My SQL query is as follows:
SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC
How do I "loop" the Time field on a single line in Reporting Services?
View 6 Replies
View Related
Nov 9, 2015
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz
after the update: BcDxyz
Following is the sample data
declare @t1 table
(
c1 varchar(5),
c2 varchar(10),
c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')
[Code] ....
View 5 Replies
View Related
Jun 25, 2007
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
View 7 Replies
View Related
Sep 27, 2005
Lets say I have a User Defined Function. I need to call the UDF a number of times within the same View. However, the results are not as expected. The only way (I know of) is to have seperate instances on the same function saved under different names. Is there a way to call a single UDF multiple times within a View without the returned values being messed-up?
View 3 Replies
View Related
Feb 1, 2008
I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.
I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.
Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.
Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.
Any help is very much appreciated.
View 2 Replies
View Related
Aug 1, 2014
I am importing xml multiple times a day from a vendor. However when SSIS created the ID's for nested XML data it is not unique. So importing the first time and I get 3-4 records it looks fine. However subsequent imports all use the same ID's so it isn't unique, how do I go about changing this as I cant find anything about it.
View 7 Replies
View Related
Apr 23, 2015
we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also
View 7 Replies
View Related
Oct 26, 2007
Hi all,
In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
Any help would be appreciated.
Thanks in advance.
Swati
View 5 Replies
View Related
Oct 22, 2007
I have an SSIS package that contains a For Each Loop Container. I have three Data Flow tasks within the container. I have an OnError event handler associated with the encapsulating container. When one of the Data Flow tasks within the For Each Loop Container fails, the OnError for the Loop Container gets called 5 times. The OnError handler is just a script task that sends a notification email. I am not explicitly Dts.taskresult = failure, nor am I calling FireError.
View 1 Replies
View Related
Jun 12, 2015
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
SELECT
--DISTINCT
p.SPID,
p.LogiName,
p.HostName,
[Code] ....
View 8 Replies
View Related
Apr 14, 2015
I am recently encountering proble with SSAS cube,In a day cube is going to offline for several time and unable to browse it and after some time automatically cube is getting online.I am unable to figure it out what is happening.
FYIP..For every 15 Min cube will be Proccessed Full.
View 8 Replies
View Related
Jan 2, 2008
Happy new year everyone!!!
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:
1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:
SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
Help anyone? Please ...
View 5 Replies
View Related
Jun 11, 2014
I have this update statement that I need to have joined by MSA and spec.
I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.
Here is my statement below. How can I change this?
UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])
View 3 Replies
View Related
Jul 23, 2005
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0
View 8 Replies
View Related
Mar 26, 2008
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)
View 5 Replies
View Related
Apr 16, 2008
Anyone please suggest me that
How to search specific word present multiple times from a paragraph stored in sqlserver database using asp.net 2.0
With Regards
Tapan
View 4 Replies
View Related
Oct 26, 2013
I have an EXECUTE SQL Task, which gets a result-set from SQLServer using OLEDB Connection.
The result set is mapped to an object variable , say @[User::FilePath]
There are 33 row is the above resultset.
Then, I have a For-each loop, inside which, I have a Script task .
I am trying to put the above @[User::FilePath] recordset into a DataTable using DataAdapter.Fill() function. I perform some read operations to its rows.
The problem is , in the First Iteration of For-Each-Loop, the number of rows in data-table shows 33.
But from the Next Iteration, it comes out to be 0. (ZERO!!)
This causes my package to fail.
View 5 Replies
View Related
Jan 7, 2008
I have a scenario where given an incident, for example a helpdesk request, I need to get the time invloved given the status of the incident as it moves through a queue. Each incident has a unique incidentID and can have any number of status events, each with a statusID (1 to many) For example:
status1(S1) - Incident opened -- the time here is captured as start time
S2 - misc. status - time to capture is time between start time and the new status time i.e., 2 minutes
S3 - misc. status - time to capture is time between start time and S3 i.e., 3 minutes, and the time between S2 and S3 i.e., 1 minute
... can include any status count -->> Sx
... ending with a closing time
I'd like to able to capture any time between any 2 status levels between the start and closing time.
I'm figuring that using cursors would be the best way to go, but realize that there are always better ways to do things. Listed below is the SQL I plan to use. If anyone has a better way to go about accomplishing the desired goal please let me know. The code should be able to run under either SQL Server 2000 or 2005. Thanks in advance.
SAMPLE SQL:
declare @ticketnumber varchar(20)
,@opendate int
,@closedate int
select ticketnumber,min(opendate),max(closedate)
into temp
from mastertable
group by tickernumber
declare MyCursor cursor for
select *
from temp
order by 1
open MyCursor
fetch next from MyCursor into @ticketnumber,@opendate,@closedate
while (@@FETCH_STATUS =0)
begin
select sum(.....)
from mastertable
where ticketnumber = @ticketnumber and ?field? = 'EX'
fetch next from MyCursor into @ticketnumber,@opendate,@closedate
end
close MyCursor
deallocate MyCursor
View 1 Replies
View Related
Oct 18, 2005
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
View 3 Replies
View Related
Mar 7, 2008
Hey!
DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?
View 3 Replies
View Related
Aug 21, 2007
Hi all
I have SQL 2000 SP4 running on a W2K server SP4.
The server acts as a subcriber and publisher and acts as its own distributor.
Each publication uses its own distribution agent and is set to loop.
There are 4 queued updating subscriptions on this box.
When running 3rd party tools to determine open active ports, the replication executables (distrib and logreader) are connecting to local server multiple times. Easily 15+ established connections plus many more waits.
Everything connects to 1433 then opens a new port.
Is this because of the queued updating?
Replication is functioning as it should without any problems.
I even checked our 2 main distribution servers which only act as distributors and there is not one connection from replication to itself.
Any insight would be appreciated
Thx
John
View 3 Replies
View Related
Jan 30, 2015
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
View 0 Replies
View Related
Dec 31, 2006
Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
View 8 Replies
View Related
