SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0 BEGIN DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid) AND ClassID NOT IN (SELECT majReqC.ClassID FROM MajorRequiredClasses majReqC WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors WHERE StudentID = @studid AND MinorID = @minorid END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MinorRequiredClasses]( [MinorClassID] [int] IDENTITY(0,1) NOT NULL, [MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED ( [MinorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MinorRequiredClasses]( [MinorClassID] [int] IDENTITY(0,1) NOT NULL, [MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED ( [MinorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID]) REFERENCES [dbo].[Minors] ([MinorID]) GO ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorRequiredClasses]( [MajorClassID] [int] IDENTITY(0,1) NOT NULL, [MajorDisciplineID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED ( [MajorClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID] GO ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID]) REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID]) GO ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = "Computer Science" DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid)
NEW QUERY DECLARE @studid int DECLARE @minorid varchar(50)
SET @studid = 0 SET @minorid = 'Computer Science' DELETE FROM Student_Classes WHERE StudentID = @studid AND Completed = 0 AND ClassID IN (SELECT minReqC.ClassID FROM MinorRequiredClasses minReqC WHERE minReqC.MinorID = @minorid) AND ClassID NOT IN (SELECT majReqC.ClassID FROM MajorRequiredClasses majReqC WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = @studid)) Let me know if you need more information...Hope someone can help!
The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate) FROM somewhere WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>) SqlParameter param = new SqlParameter(); param.ParameterName = "@return"; param.Direction = ParameterDirection.ReturnValue; command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value int val = int.Parse(command.Parameters[0].ToString()); Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable) ORDER BY itemNumber
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
On cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom // Getting the Correct Answer from the Database. int QuiziD = Convert.ToInt32(Session["QuizID"]); int QuestionID = Convert.ToInt32(Session["QuestionID"]); SqlConnection oConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\quiz.mdf;Integrated Security=True;User Instance=True"); SqlCommand cmd = new SqlCommand("usp_Question_Count", oConn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter QuizParam = new SqlParameter("@QuizId", SqlDbType.Int); QuizParam.Value = QuiziD; cmd.Parameters.Add(QuizParam); SqlParameter QuestionParam = new SqlParameter("@QuestionID", SqlDbType.Int); QuestionParam.Value = QuestionID; cmd.Parameters.Add(QuestionParam); SqlParameter countParam = new SqlParameter("@CorrectAnswer", SqlDbType.Int); countParam.Direction = ParameterDirection.Output; //cmd.Parameters.Add(workParam); cmd.Parameters.Add(countParam); oConn.Open(); cmd.ExecuteNonQuery(); // get the total number of products int iCorrectAnswer = Convert.ToInt32(cmd.Parameters["@CorrectAnswer"].Value); oConn.Close();Heres the stored ProcedureALTER PROCEDURE dbo.usp_Find_Correct_Answer @QuizID int, @QuestionID int, @CorrectAnswer int OUTPUT /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */AS /* SET NOCOUNT ON */ SELECT @CorrectAnswer=CorrectAnswer FROM Question WHERE QuizID=@QuizID AND QuestionOrder=@QuestionID
I have a web application that is data driven from a SQL 2005 database. The application needs to prompt the user to enter some information that will be logged into a SQL table. It should always be the last row in the table that is being worked on at any one time. Over a period the user will need to enter various fields. Once the data is entered into a field they will not have access to amend it. Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited. As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it? Regards Clive
I'm hoping someone can help me w/this query. I'm gathering data from two tables and what I need to return is the following: An employee who is not in the EmployeeEval table yet at all and any Employee in the EmployeeEval table whose Score column is NULL. I'm So lost PLEASE HELP. Below is what I have. CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID ( @deptID nvarchar(20), @Period int)ASSELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName, ev.Score FROM Employee AS eLEFT JOIN EmployeeEval as ev ON e.EmployeeID = ev.EmployeeIDWHERE e.DeptID = @deptId OR (e.deptid = @deptID AND ev.Score = null AND ev.PeriodID = @Period)GO
hello, I have a small problem. i'm adding records into the DB. the primary key is the company name which is abviously unique. before saving the record i check in the stored procedure if the company code is unique or not. if unique then the record is added & an output parameter is set to 2 & should b returned to the data access layer. if not unique then 3 should be returned. but everytime it seems to be returning 2 whether it is unique or not. can u plz help me? here is the code of the data access layer: cmd.Parameters.Add("@Status", SqlDbType.Int); cmd.Parameters["@Status"].Value = ParameterDirection.ReturnValue;
//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters; cmd.ExecuteNonQuery(); status = (int)cmd.Parameters["@Status"].Value;
here is the stored procedure: CREATE PROCEDURE spOrganizationAdd( @OrgCode varchar(10), @OrgName varchar(50), @AddressLine1 varchar(30), @AddressLine2 varchar(30), @City varchar(15), @State varchar(15), @Country varchar(15), @PinCode varchar(7), @Phone varchar(20), @Fax varchar(20), @Website varchar(30), @Email varchar(50), @CreatedBy int, @LastModifiedBy int, @Status INTEGER OUTPUT) AS BEGIN TRAN IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @OrgCode) BEGIN SET @Status = 3
END ELSE BEGIN INSERT INTO tblOrganizationMaster VALUES( @OrgCode, @OrgName, @AddressLine1 , @AddressLine2 , @City , @State, @Country, @PinCode, @Phone, @Fax , @Website, @Email, @CreatedBy , GETDATE(), @LastModifiedBy , GETDATE()) SET @Status = 2 END IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN
Hello, Im currently working on a asp.net file hosting wesite, and im being faced with some problems I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User. I already have an sql statement that performs this exact thing. It is as follow select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName) But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something. For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero. Is there way around this? Im not sure if ive been cleared enough, please feel free to request more info as needed. Thank you very much, and thx in advance.
I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT COUNT(*) AS TodayCount FROM Counter WHERE DATEPART(YEAR, visitdate) = Year(GetDate()) AND DATEPART(MONTH, visitdate) = Month(GetDate()) AND DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?
Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString) Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn) With Cmd.Parameters .Add(New MySqlParameter("?CurrentPage",thisPage)) End With Conn.Open() Exists = Cmd.ExecuteScalar() End Using End Using Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?
Hi everybody, I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated. devmetz
Hi I have two text boxes, Textbox A and Textbox B, what i am trying to do is when values are entered in the textboxes, a query runs and returns the results in a datagrid. However I am unusre on how to structure the stored procedure. Can anyone lead me in the right direction, thanks
I have the following stored procedure that is returning nothing can anyone please help? SELECT job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date, customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM tblOrderWHERE (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End) When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function. Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT AS Insert INTO TM_TalentInvites ( TalentID ) Values ( @TalentID ) SELECT @@IDENTITY AS TalentInviteID RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID) { // Initialize SPROCstring connectString = "Data Source=bla bla"; SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn); cmd.CommandType = CommandType.StoredProcedure; // Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID); conn.Open();int talentUnique = (int)cmd.ExecuteScalar(); conn.Close();return talentUnique; } Any help you can give me will be greatly appreciated thanks!
I have the following query. select top 3 dbo.oncd_incident.open_date,dbo.onca_product.description,dbo.onca_user.full_name,dbo.oncd_incident.incident_id,email, dbo.oncd_contact.first_name,dbo.oncd_contact.last_name,dbo.oncd_contact.contact_id from dbo.oncd_incident inner join dbo.oncd_incident_contact on dbo.oncd_incident_contact.incident_id=dbo.oncd_incident.incident_id inner join dbo.oncd_contact on dbo.oncd_contact.contact_id=dbo.oncd_incident_contact.contact_id inner join dbo.oncd_contact_email on dbo.oncd_contact_email.contact_id=dbo.oncd_contact.contact_id inner join dbo.onca_user on dbo.oncd_incident.assigned_to_user_code=dbo.onca_user.user_code inner join dbo.onca_product on dbo.onca_product.product_code=dbo.oncd_incident.product_code where dbo.oncd_incident.incident_status_code='CLOSED' and email is not null and dbo.oncd_incident.open_date>DateAdd(wk,-2,getdate()) and dbo.oncd_incident.completion_date>=DateAdd(dd,-2,getdate()) and dbo.oncd_incident.assigned_to_user_code in (select user_code from dbo.onca_user) order by newid() I want the query to be executed for each row returned by the sub query.If I use IN keyword it returns top 3 rows for any 3 of the users.But I want top 3 rows to be returned for each of teh user.Please help.
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
I'm using SQL-MSDE and have a table defined with a 'identity seed' column that automatically gets assigned when a record is added (I do not load this value). This column is also my KEY to this table. I'm using INSERT to add a record. Is there a way to return this KEY value after doing the INSERT?
We just upgraded to SQL 7.0 SP2. We enlarged one of our fields from varchar(255) to varchar(500), but when I do a SELECT on the field it only brings back 255. I know this was a limitation in ISQL in 6.5. I tried it in query analyzer and also via a command line. Any ideas how to see all the data?
