Hi All,
Let's say I have a SELECT that returns 1000 records and I want them unordered or randomized How would I do that?
So instead of:
OrderID
1
2
3
4
5...and so on
I want:
OrderID
67
300
100
1
910..totally random, but all 1000 records must still be returned.
Regards
Melt
Hi.
I'm using stored procedures in SQL7 to return a list of items to a website where they are presented. A new requirement is to present these items in a random order on the website. I have two approaches to this one, either making the result from SQL7 random or presenting the ordered results randomly. Since the presentation logic is used several times (different country subsites), it would be the easiest way to get a randomized result from the stored procedure, which would mean that I only have to change the procedure.
Any thoughts on how to accomplish this, that is returning a list of items but in random order, so that the presentation sequence would be different for each access to the website ??
Any help would be appreciated : )
Thanks!
Eirik Kjølsrud
Systems Developer, Apropos Internett - Norway
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
"SELECT TOP 20 to 40 * FROM Northwind"
Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ
My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?
View 4 Replies View RelatedAny information as to how to handle this?
Thanks.
Hi,
I have a SQL question which I suspect is very easy to answer but can't seem to find one for.
I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.
So, the SP would for example accept the parameters
@Upperbound = 80
@Lowerbound = 61
and would return a recordset of 20 records being the 61st thru 80th record from the queried table.
Thanks for the help
Guy
PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.
Following Proc is returning all records.I have passed any value to parameters but no luck.
CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;
if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO
zx
I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike
View 3 Replies View RelatedHello -
I'm trying to write a select statement that will return only "billable" records.
A record is billable if:
- it is the only record for a Case (Case is an FKey ID column in my records table)
OR
- the last billable record for Case X has a DateTime > 24 hours before the record in question.
Getting records for the first condition is easy:
SELECT ID FROM Records r
LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case]
WHERE r2.[Case] IS NULL
It's the second part I'm having trouble with.
UNION
SELECT ID FROM Records
JOIN ?
....
WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??
I have to assume there can be any number of records for a case.
Thanks in advance!
HI,
If i have the following data
Code Block
Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);
Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
thanks.
I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.
Thanks
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0,
CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS'
WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS'
ELSE 'NIGHTS'
END)
END
IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END
END
END
SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription,
dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift
FROM dbo.LineFaultDescription INNER JOIN
dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND
dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID
and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')
WHERE (dbo.LineFaultData.AssetID = {Asset_ID})
AND (dbo.LineFaultData.Shift = '{@Shift}')
ORDER BY dbo.LineFaultData.StartTime DESC
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields.
SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR"
SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue)
I have more code to show, if this looks correct. Just let me know.
Thanks in advance.
Hi
I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1
The SQL I have written however returns all records
SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2
WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1">
Please could someone help
Many thanks Colin
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:
SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC
Where this statement would return only records 2000 to 5000 of the returned results.
Here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:
PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 01/12/2012 01/12/2013 arr_56464
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494
And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.
But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:
PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494
But can't seem to get this using a self join query like this:
select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID
from arrivals a INNER JOIN arrivals b
ON b.personID = a.personID
WHERE
a.arrivalid != b.arrivalid
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159
I'm trying to fully utilize parameters on a report.
I have 3 parameters, a start date, end date, and a cause.
The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.
Should I be using Filter, Report Parameters, or some other means to solve this?
Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.
Thanx!
TR
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.
sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub
What am I doing wrong?
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
Hi,I have a sql server database with 1.7 million records in a table, withabout 30 fieldsWhen I run select * from tablename it can take over 5 minutes.How can I get this time down or is it normal?ThanksJerry
View 4 Replies View RelatedOur users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4
2 CS 4
3 CP 2
4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4
CP 2
CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
Thanks,
Taurkon
I am running a SP using this code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly]
(
@postcode nvarchar(50)
)
AS
SET NOCOUNT ON;
SELECT
Cust_Address.Cust_Address_ID,
Cust_Address.Cust_Address_1,
Cust_Address.Cust_Address_2,
Cust_Address.Cust_Address_Post_Code,
Cust_Address.Cust_Post_Town,
Post_Town.Post_Town_ID,
Post_Town.Post_Town_Name,
Post_Town.Post_Town_Priority
FROM Cust_Address
INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID
WHERE Cust_Address.Cust_Post_Town LIKE @postcode
The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all.
Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine.
Cheers Matt
Hi,
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account.
My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this:
*******************************************
name = contact_id
data type = integer
prompt = [blanked out]
hidden = checked
internal = checked
multi-value = unchecked
allow null value = checked
allow blank value = unchecked
available values:
from query: dataset = account
value field = contact_id
label field = contact_id
default values:
from query: dataset = account
value field = contact_id
**********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
larry
Hi all,I am using the following stored procedure in SQL 7 to return a range of records.CREATE PROCEDURE spRetMyTable@maxRows int, @lastRecord int, @SortPhrase varchar(122)ASbeginDECLARE @stSql VARCHAR(255) SET ROWCOUNT @maxRowsSET @stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @lastRecord) + 'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'exec(@stSql)set rowcount 0endTo execute the above procedure I am issuing the command:execute spRetMyTable @maxRows 10, @lastRecord 1500The column [colPrimary] is the primary field in the table.I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.Is there any performance overhead in the above method in the first place?Could it be improved by any alternative process?I need YOUR ASSISTANCE to cater to the above issues in a feasible way.Thanks in advance.
View 2 Replies View RelatedHi, and thanks in advance.
I have VWD 2005 Express and SQL 2005 Express running.
I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting...
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded.
Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle.
Any help would be appreciated!
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString %>"
ProviderName="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString.ProviderName %>"
SelectCommand="spDisplayServiceOrders" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" EnableCaching="True" cacheduration="300" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="RadioButtonList1" ConvertEmptyStringToNull="False"
DefaultValue="2005-11-1" Name="SDate_Entered" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ALTER PROCEDURE [dbo].[spDisplayServiceOrders]
(
@SDate_Entered SmallDateTime,
@SClosed nvarchar(50)= NULL
)
AS
If @SClosed IS NULL
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
Order by SDate_Entered DESC
END
ELSE
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
and SClosed = @SClosed
Order by SDate_Entered DESC
END
Hi
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go
This is asp.net code
Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)
conn.Open()
dr = cmd.ExecuteReader
nRecords = convert.int32(cmd.parameters(@return).value)
conn.close
Thanks
Lbob
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
I have created a table from another table where I specified that one of the fields, an number field, is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.
Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?
In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?
Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies View Related