I want to write a sql Query which retrieves the last (some number ) records form a Table ,
the table specification is like :
Table1
==================================
ID int , <----- is identity And identity seed =1
Title char(50) ,
News nvarchar(100)
its like I want to get the last 5 news form DB ...
I 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).Â
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
[code]....
I have a situation where I need a table if bad items to match to. Forexample, The main table may be as:Table Main:fd_Id INT IDENTITY (1, 1)fd_Type VARCHAR(100)Table Matcher:fd_SubType VARCHAR(20)Table Main might have a records like:1 | "This is some full amount of text"2 | "Here is half amount of text"3 | "Some more with a catch word"Table Matcher:"full""catch"I need to only get the records from the main table that do not haveanything in the match table. This should return only record 2.
View 1 Replies View RelatedHello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
hi all
i hav a database with some images...images r stored in binary data form
i want to retrieve that image from database and display it in a details View
im inserting images to database like this
public void OnUpload(Object sender, EventArgs e)
{
int len = Upload.PostedFile.ContentLength;
byte[] pic = new byte[len];
Upload.PostedFile.InputStream.Read (pic, 0, len);
SqlConnection connection = new SqlConnection ("integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbAsoftWeb;Data Source=ASP");
try
{
connection.Open ();
SqlCommand cmd = new SqlCommand("insert into tblStock " + "(Image, Image_Data,[DESC],PRICE,BAL_QTY,PV) values (@pic, @text, @lblProd, @lblPrice, @lblPV, @lblQty) ", connection);
cmd.Parameters.Add("@pic", pic);
cmd.Parameters.Add("@text", Comment.Text);
cmd.Parameters.Add("@lblProd", txtProdName.Text);
cmd.Parameters.Add("@lblPrice", txtPrice.Text);
cmd.Parameters.Add("@lblPV", txtPV.Text);
cmd.Parameters.Add("@lblQty", txtQty.Text);
cmd.ExecuteNonQuery ();
}
finally
{
connection.Close ();
}
im able to get the image in to form but i want to display it in a detailsview
how cani do it..can any one explain me
thanks in advance
Hari
hi,
I´m trying to connect to a integration service hosted in a different machine, but I recive the following message:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) : access denied
I verified and the service that machine is running, maybe could be the user, I´m using windows authentication, I have no problems when I log in in the databse engine.
Can somebody help me??
After getting an RPC error when trying to login to my server via either consol or Terminal services, then rebooting, I can no longer connect to SSIS
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Connect to SSIS Service on machine "BG-22SQL" failed:
The service did not respond to the start or control request in a timely fashion.
.
------------------------------
Connect to SSIS Service on machine "BG-22SQL" failed:
The service did not respond to the start or control request in a timely fashion.
.
Hi all
I've two tables
community, community_related.
I need to get community names for comm_relatedID from community table
where comm_id = 21 , please see my tables below
Community table Community Related
commID, comm_name comm_id comm_relatedID
21 test1 21 24
22 test2 21 22
23 test3 21 26
24 test 5 21 27
26 test6
27 test7
result am trying to display from community related table after joining community table on COMM_ID = 21 is
comm_id comm_relatedID comm_name
21 24 test5
21 22 test2
21 26 test6
21 27 test7
can anybody please help..with a query, am stuck at this point.
I am using ADODB.Recordset to retrieve the records from an MS Access database. The table has 11450 records and the recordset shows this number as the recordcount too. But when I try to fetch the records in a While loop using EOF as the check, I am able fetch only 4000 or so records. No error occurs and no exception is thrown. Control just comes out of the function. Why could this be happening?
Does Recordset have some limit on the number of records for Jet 4.0 provider?
Any help is appreciated.
I am new to SQL, still learning.
If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.
How can I do this?
I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.
Help!
Hi, All
I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.
In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key
Local machine table:
-Fipscodes (table) contain data was provided by customer that I inserted into my machine
and column fields (state,zip,fips) made Zip as Primary Key.
So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.
Query Statement:
SELECT h.lname,h.fname,h.street,h.city,h.state,h.zip,h.zip4,h.carroute,h.gender,
h.keycode,h.purprice,h.mortamt,h.phone,h.lender,h.recorddate,h.fips,h.pubmonth,
h.pubday,h.pubyr,h.trantype,h.condocode,h.transdate,h.ratetype,h.loantype,h.birth,
h.heritage,h.estcurrval,h.estcurreq,h.dpbc,n.state,n.fips
FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n
ON h.FIPS = n.FIPS
WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'
Error occur:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'State'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FIPS'.
, please help me solve this issue and thank you very much everyone.
Hi,
How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links.
Table
-----
Name Age
-----------------
Raja 23
Kumar 26
Suresh 30
Rani 22
Subha 32
Ganesh 25
The result will be
Name Age
-----------------
Suresh 30
Rani 22
Subha 32
Thanx.
GANESAN MURUGESAN
Hello,If I run an action SP from MS Access using ADO:...cmd.executewhere the SP is something like Create...Update tbl1 set fld1 = 'something' where...how can I retrive the count of records affected like from Queryanalyzer?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedHi all,
I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.
Please let me know if some body has any idea.
Thx.
Hi,
I am new to Microsoft Technologies, can any one say how to connect/ retrieve data's from Active Directory through SQL SERVER from scratch. I need with some sample codes.....
Thanks in advance
raj d.s
Hi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.
*desparate*
I have a situation where i am trying to run two query's and retrieveone record from each query, then using Union combine them into onerecordset. The First Query is strait forward and should just returnthe matching record however the second query needs to find a randomrecord and return it. The union is causing me some trouble. It seemsthat any method i try to find a random record just causes an error.Here is an example of a query that's causing the problem.--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by newID()--------------------------------------------------------------------------------------------------------I have also tried the following which does not provide an error but icannot find a way only to return one record from the second query--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select newID() as colID, col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect newID() as colID, col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by colIDIf anyone has any ideas, fixes, or thoughts i would appreciate them...thanks in advance...FYI i am fairly inexperienced in SQL so please feel free to let me knowif you need more of an explination.
View 9 Replies View RelatedI€™m working on a database project that will ultimately contain millions of records for each lot. In addition, each lot will have up to 96 corresponding serial number records.
I would like to add a SQL parameter that would tell the database engine to only return X number of records.
For Example:
If table TBL_LOTS contains one million records I would like to limit the return set to 100 for example.
What would I need to add to the SQL command to below to restrict the data set to the first 100 records in the set of one million?
SELECT [LOT NUMBER]
FROM TBL_LOTS
WHERE [STATMENTS]
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
View 9 Replies View Relatedhi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.
Ok, 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 Relatedhi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please
Table1 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.
hey guys, i getting a minor problem here. Hope u guys may help me =) I would like to count all the records from a table ' member ' and store the value inside a variable ' iCnt ' i wrote like tis:cmd.commandtext = " select count(*) as total from member" dim iCnt = cmd.ExecuteScalar Hmm.. I am wondering whether my code is correct, it seem like cant work =(Any help is appreciated, thanks u guys.
View 1 Replies View Relatedanybody give example code for save Excel records into sqlserver in asp.net1.1 Thanks
View 1 Replies View RelatedHi I am not sure if I am at right place, anyhow I hope I am :) Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date) for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this? RegardsMykhan
View 1 Replies View RelatedI'm a novice to SSIS (and we've never used DTS).
Writing a Control flow that inserts records worked fine, using lookups and all, but i can't find somewhere an example on how to update records of even delete records.
Any suggestions ....
Thx
Harry Leboeuf
Summary: Maximum number of records per second that can be inserted intoSQLServer 2000.I am trying to insert hundreds (preferably even thousands) of recordsper second in to SQLServer table (see below) but I am getting thefollowing error in the Windows Event Viewer Application log file:"Insufficent Memory......"And very few records were inserted and no errors where sent back viathe JDBC.By removing the indexes on the table we have stopped getting the errormessage and have managed to load the table at 300 records per second.However I have couple of questions:1) Are the indexes definitely to blame for this error and is thereanyway of getting around this problem i.e. keeping the indexes in placewhen inserting?2) How should I configure SQLServer to maximise the speed ofinserts?3) What is the limiting factor for inserting into SQLServer?4) Does anyone know of any metrics for inserting records? At wantpoint should we consider load balancing across DBs.I currently populate 1.6 million records into this table. Once againthanks for the help!!CREATE TABLE [result] ([id] numeric(20,0) NOT NULL,[iid] numeric(20,0) NOT NULL,[sid] numeric(20,0) NOT NULL,[pn] varchar(30) NOT NULL,[tid] numeric(20,0) NOT NULL,[stid] numeric(6,0) NOT NULL,[cid] numeric(20,0) NOT NULL,[start] datetime NOT NULL,[ec] numeric(5,0) NOT NULL,)GOCREATE INDEX [ix_resultstart]ON [dbo].[result]([start])GOCREATE INDEX [indx_result_1]ON [dbo].[result]([id], [sid], [start], [ec])GOCREATE INDEX [indx_result_3]ON [dbo].[result]([id], [sid], [stid], [start])GOCREATE INDEX [indx_result_2]ON [dbo].[result]([id], [sid], [start])GO
View 5 Replies View RelatedIn Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from  Master table to parent and child table i am using using expresssion likeÂ
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ONÂ
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER ANDÂ
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) Â and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.
If i run this expression to remote server  i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.
I 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 Related