I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc
Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc
I'm using MSSQL 2005
tks
Hello,
I have a SP1 that is calling another SP2,
SP2 is using select and returning records
So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)
When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
How can I access the last recordset ?
Hi
I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP??
Waiting for someone to shed a light of hope.
Thanx a lot
In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.
I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.
The Stored Procedure;
CREATE PROCEDURE dbo.sp_eeLinksByName
@EmployerNum char(6)
AS
SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
ORDER BY dbo.TIMS_eeLinksByName.Employee
returns 169 records when I run it directly from the MS Visual Studio environment.
However whe I try to run it from VBA with the following code;
Dim sp_eeLinksByName As String
Dim ConnectionString As String
Const DSeeLinksByName = "SOS-1"
Const DBeeLinksByName = "Insync"
Const DPeeLinksByName = "SQLOLEDB"
Dim objeeLinksByNameConn As New ADODB.Connection
Dim objeeLinksByNameRs As New ADODB.Recordset
Dim objeeLinksByNameComm As New ADODB.Command
ConnectionString = "Provider=" & DPeeLinksByName & _
";Data Source=" & DSeeLinksByName & _
";Initial Catalog=" & DBeeLinksByName & _
";Integrated Security=SSPI;"
' Connect to the data source.
objeeLinksByNameConn.Open ConnectionString
' Set a stored procedure
objeeLinksByNameComm.CommandText = sp_eeLinksByName
objeeLinksByNameComm.CommandType = adCmdStoredProc
Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn
' Execute the stored procedure on
' the active connection object...
' "CurrTSCalendar" is the required input parameter,
' objRs is the resultant output variable.
objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs
' Display the result.
'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
Select Case objeeLinksByNameRs.RecordCount
Case 0
'Do Nothing
Case Is > 0
'Get the Employee List
objeeLinksByNameRs.MoveFirst
Do While Not objeeLinksByNameRs.EOF
MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
objeeLinksByNameRs.MoveNext
Loop
End Select
'Clean up.
'objRs.Close
objeeLinksByNameConn.Close
Set objeeLinksByNameRs = Nothing
Set objeeLinksByNameConn = Nothing
Set objeeLinksByNameComm = Nothing
I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?
I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;Now, I can get the results I want by using a union query for the "extra" records.
For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345'
UNION ALL SELECT '12345',Null
UNION ALL SELECT '12345',Null;
Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int)
Insert Into @tbl(id, name, age)
Values(1, 'James, Lee', 28),
(2, 'Mike, Richard', 32),
(3, 'Leon Wong', 29)
Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
View 7 Replies View RelatedHi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
Hi.How does one return a range of rows.I know that "Top 5" will return rows 0 - 5but, how do I get 6 - 10?thanks
View 17 Replies View RelatedHow can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
View 2 Replies View RelatedIs there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?
View 10 Replies View RelatedI currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates
@MonthFrom int,
@YearFrom int,
@MonthTo int,
@YearTo int,
@DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)
And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
Hello,John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me anidea how to achieve paging in sql server without row numberfunctionality. Thank you John. The following works for me, not veryeficient though:SELECT * FROM( SELECT top 5 * FROM( SELECT top 10 * FROM( SELECT top 10 *FROM dft_documentORDER BY documentkey ASC) aORDER BY documentkey DESC) b) dORDER BY documentKey ASCThe innermost SELECT gives 10 rows out of which last 5 needed.regards
View 2 Replies View RelatedGiven in a record in from a Table called WorkSchedule:
idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8
I need to return for each record in the WorkSchedule Table
1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006
8 8 8 8..................8
Please help.
Thank you.
-Robert
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View 1 Replies View RelatedUsing MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS (
SELECT PQIM.PQPageID
,PQIM.PageURL as PageDescription
,CONVERT(Date,NCPI.RequestDateTime) AS [Date]
,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR]
,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount
FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
[Code] ....
This is the output I get :
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54
[Code] ....
But I am expecting
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.
Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.
Does anyone have any ideas here?
Thanks,
Al
The code below has this line
SET @SOGallons = @ODTGallons
I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.
I've tried
SET @SOGallons = @SOGallons + @ODTGallons
SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons
Neither Worked
<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')
OPEN TC1
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01
I am not sure how to count using non integers
Thanks
Here is what I am trying to do...
I want to goup "members" togethers in a "group."
A table for members and a table for groups.
each containing coluns...
but inside Groups I would like a column that contains ID numbers for the members that be long to that group.
Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.
Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.
Hope this makes sense
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
View 5 Replies View RelatedI am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:
Table1.SomeNumber1 = 1
Table1.SomeNumber2 = 2
Table2.SomeNumber1 = 2
Table2.SomeNumber2 = 1
I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.
I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.
Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.
Gmz
It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.
I have input parameters @months int, @days int, @years int in a stored procedure.
All I want to do is to get a DateTime variable out of them.
DECLARE @dated DateTime.
Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!
No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?
It is rudiculous. I've tried dozens of variants. Please help.
Thanks.
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies View RelatedI am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!
I'm embarassed I haven't figured this out already but here goes.
Lets say you need a percentage from dividing two integers for example
"select 2000/4000"
This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)
I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.
As always, thanks VERY MUCH for the kind advice.
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:
Column Name: BIG_ID
Derived Column: < add as new column >
Expression: (DT_I8)[ID] + 840230000538058
Data Type: eight-byte signed integer {DT_I8]
The error I get:
The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.
Then I try the expression:
(DT_I8)[ID] + (DT_I8)840230000538058
and
[ID] + 840230000538058
and get the same error.
What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?
Thanks,
Michael
Hello, all.
Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?
(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))
I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.
Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.
Thanks for any help.
I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?
TIA,
barkingdog