How To Retrive A Top2 Records From Table
Dec 20, 2007hi friends
can any one give syntax/suggestion to retrive the top two records from a table.
regrads
madhu
nukala
ADVERTISEMENT
How To Retrive Truncated Records
Oct 6, 2006Hi
I have a table Test a execute this below query
truncate table Test
I want to retrive records of Test table .How can i do.
Ranjeet Kumar Singh
How To Retrive A SP Table Inside A SP?
Feb 28, 2007Hello, inside of my SP i want to execute another SP, something like:
EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID
this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns).
Columns it returns:QResult , Threads , Answers
SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ?
or how do you get it?
Patrick
Retrive Record From Table Which Are Insert Last Dt
Jun 2, 2008How can we retrive record from table those record which are insert,update or deleted on last date.
View 1 Replies View RelatedHow To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.
Sep 13, 2006Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedSQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1
Aug 11, 2015Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table
Oct 21, 2015I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
How Do I Select All Records In One Table That Have NO Related Records In Another Table?
Jul 20, 2005I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies View RelatedDeleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table
Mar 18, 2014I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted
Dec 3, 2014I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
To Retrive A Table From One Database To And Another Database(same Table)
Mar 17, 2006hii have emp table in one database , how to retrieve same table anotherdatabasethrough query analizergive code ,,,
View 2 Replies View RelatedDelete Records From A Table Using Records In Another Table.
Jul 20, 2005I have been searching many postings and I cant seem to find anyonethat has this answer so I decided to post. I am using SQL(Transact-SQL).If I have 2 tables with columnsacct_num,activity_date,and pay_amt and I want to delete one instanceof a record in table 1 for every instance of that record in table 2how could I do that. For example.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00Table 2-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00I need a delete statement that will find 2 of the 5 records(It doesn'tmatter which 2) and delete them.Leaving table one looking like this.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00How can I do this??
View 7 Replies View RelatedHow To Retrive The Value Of @DerivedFileName
Jul 26, 2007SqlCommand sqlCommand = new SqlCommand(spName, Conn);
sqlCommand.CommandType = CommandType.StoredProcedure;sqlCommand.Parameters.Add("@XMLString", SqlDbType.Xml).Value = XMLstring;
sqlCommand.Parameters["@XMLString"].Direction = ParameterDirection.Input;sqlCommand.Parameters.Add("@DerivedFileName", SqlDbType.VarChar,50);
sqlCommand.Parameters["@DerivedFileName"].Direction = ParameterDirection.Output;
int test = sqlCommand.ExecuteNonQuery();
Retrive Xml As Output
Apr 24, 2008My execution fails .
Cannot we retrive xml from stored procedures.
CREATE PROCEDURE USP_XML_TEST (@no int ,@var xml output)
AS
BEGIN
SET NOCOUNT ON;
select @var = details_xml from student where student_id = @no
END
GO
Can Not Retrive @@identity Value
Jul 20, 2005I use select @@identity to return @@identity from my store procedure,but I could not retrive it from my Visual basic code, like variable=oRS.fields.item(0).value, it always says item can not be found....
View 3 Replies View RelatedRetrive Xml As Output
Apr 24, 2008My execution fails .
Cannot we retrive xml from stored procedures as output .
Blow is my code.
CREATE PROCEDURE USP_XML_TEST (@no int ,@var xml output)
AS
BEGIN
SET NOCOUNT ON;
select @var = details_xml from student where student_id = @no
END
GO
How To Retrive Data
Dec 15, 2006
How to retrive data from EXCL file by using SSRS.(My data is not sql server)
How To Retrive A Value Using A Query String?
Jul 7, 2006Hello,I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.This is what I have:<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
'Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> I would like to know how can I get now the "user.FirstName" and pass it to a session variable???how should I code it? thanks,
How Do I Retrive SQL Count Value Programmatically?
Jan 10, 2007Hi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically?
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
How To Store And Retrive .DOC File In Sql
May 28, 2007Hi frds,
My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format
Thanks & regards
Sithender.s
Retrive Images From SqlServer In ASP .NET
Jan 7, 2008I have a table called Image1 and i have stored the image in SQL server 2005 with a feiled called picture
table name ---- Image1
field-- picture (data type image)
please let me know the code step by step code how to Retrive Images from SqlServer in ASP .NET webpage, please help me ....
Retrive A Record From Database
Jan 9, 2008hai everybody!!!!
am developing one application in visual 2005..and sql server.
so how to write the coding for searching one record from a database table according to the id..and to display it
How To Retrive A Record From A Database
Jan 9, 2008hai everybody!!!!!
am working wirh visual 2005 and sql server()asp.net).....so i need the coding in VB for searching one record from a database table according to the id..and want to display it.....
How To Retrive Rownumbers In Mssql2003
Mar 27, 2008hi all,i want to print row numbers from the select command, how to retrive row numbers in mssql2003
View 1 Replies View RelatedIdeal Way To Retrive Dataset.
Jul 12, 2004This is DataSet retriving code from IBUYSPY Events Module.
public DataSet GetEvents(int moduleId) {
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlDataAdapter myCommand = new SqlDataAdapter("GetEvents", myConnection);
// Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
parameterModuleId.Value = moduleId;
myCommand.SelectCommand.Parameters.Add(parameterModuleId);
// Create and Fill the DataSet
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
// Return the DataSet
return myDataSet;
}
My Question is
1) Is this ideal way to retrive data?
2) Will it return connection back to pool?
Help-how To Retrive Date From Sql To Array
Aug 28, 2004i m new 4 asp.net
i retrive data from sql database.
now,
i want to put that retrived data into array.
how could i do this?
i am use vb.net
plz anyone give any idea.
it's urgent
thanks in advance.
How I Can Store & Retrive Images
Aug 14, 2001hi,
i would like to know how i can store and retrive images from SQL server.
I will be happy if i have been explained with some examples.
Retrive Data From Two SQL Server
Apr 24, 2002Hi,
Does anyone how to use SELECT statement data from two different SQL Server ?
Thanks in advance
Wilson
How To Retrive 1st, Next, Previous & Last Record ?
Apr 20, 2007Hi, I am new to using SQL. Currently, I'm using the following statemens to retrive a specific record from my MS Access DB via VB.net.
SELECT * FROM table_name WHERE Field_Name = Criteria
Can someone please tell me, after selecting this record, If I want to go to the FIRST, or NEXT or PREVIOUS of the record just retrived or the LAST record. Can someone please tell me how can write the SQL statment to achieve this ?
Regards
How To Retrive Correct Data
Nov 26, 2007
Code Block
SELECT
tce.TimeCardID,
tce.TimeCardExpenseID,
tc.DateCreated,
e.LoginID,
e.FirstName + ' ' + e.LastName AS FullName,
tce.ExpenseAmount,
tce.ExpenseDescription,
op.ProjectName,
op.ProjectDescription,
ec.ExpenseCode
FROM OPS_TimeCards tc
JOIN OPS_Employees e
ON e.EmployeeID = tc.EmployeeID
JOIN OPS_TimeCardExpenses tce
ON tc.TimeCardID = tce.TimeCardID
Join OPS_Projects op
ON op.ProjectID = tce.ProjectID
Join OPS_ExpenseCodes ec
ON ec.ExpenseCodeID = tce.ExpenseCodeID
WHERE e.LoginID = 'jross'
ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID
1 2
1 3
1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
so if i do Select * From TimeCardID = '1'
it should return
TimeCardExpenseID
2
3
4
Any help on how to get this done....
Retrive A Binary Field From Database
Nov 15, 2006hi
I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem??????
I am really looking forward your answers
private void retrive()
{
public void a()
{
SqlConnection connection = new SqlConnection("Some Connection string");
SqlCommand command = new SqlCommand("Select * from temp", connection);
// Writes the BLOB to a file
FileStream stream;
// Streams the BLOB to the FileStream object.
BinaryWriter writer;
// Size of the BLOB buffer.
int bufferSize = 50;
// The BLOB byte[] buffer to be filled by GetBytes.
byte[] outByte = new byte[bufferSize];
// The bytes returned from GetBytes.
long retval;
// The starting position in the BLOB output.
long startIndex = 0;
// Open the connection and read data into the DataReader.
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Create a file to hold the output.
stream = new FileStream(
"C:\file.txt", FileMode.OpenOrCreate, FileAccess.Write);
writer = new BinaryWriter(stream);
// Reset the starting byte for the new BLOB.
startIndex = 0;
// Read bytes into outByte[] and retain the number of bytes returned.
retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
writer.Write(outByte);
writer.Flush();
// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
// Write the remaining buffer.
if (retval != 0)
writer.Write(outByte, 0, (int)retval - 1);
writer.Flush();
// Close the output file.
writer.Close();
stream.Close();
}
// Close the reader and the connection.
reader.Close();
connection.Close();
}
}
How To Retrive Date(alone) From Datetime Field
Nov 16, 2006hai
In my web application i want to bind data from sql 2005 to ultrawebgrid, when i use
source code
dim cmdselect as sqlcommand
dim cmdstring as string
cmdstring="select name, datefieldname from tablename"
cmdselect=new sqlcommand(cmdstring,connectionstring)
connectionstring.open()
ultrawebgrid1.datasource=cmdselect.executenonquery()
ultrawebgrid1.databind()
connectionstring.close()
----- when i execute above coding i am geting date and time displayed there, but i want to display date alone
--my datefield datatype is datetime
- i am using sql 2005,(vs2005-vb/asp.net)
please help me
thanks in advance
Unable To Retrive Data From MS SQL Database
Jun 6, 2005I have a locally installed MS SQL server 2000. I am trying to retrieve the contents of the table onto a datagrid. But when I use the user name and password, It gives the following errorSystem.Data.SqlClient.SqlException: Login failed for user 'aroop'. Reason: Not associated with a trusted SQL Server connection. string connectionString = "server=(local); uid=aroop; pwd=abcdef; database=mydb"; string commandString = "select * from mytable"; SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString);
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet);
dgEmployees.DataSource = dataSet; dgEmployees.DataBind();Can someone please let me know what to do about this?