How To Output Multiple Recordsets On One Row Using SQL?

Nov 8, 2001

Hello Folks,

Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.

Below is an example of the situation.

Thanks a lot folks, - Jerry


************* Tables *******************

** ITEMS **
ItemID
=======
10
11
12

** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight

** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights


************* Queries *******************

-- QUERY 1 --
==============
(dashes preserve indentation)

SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName


FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'


************* Output *******************

Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights


I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights



-= End-O-Message =-

View 2 Replies


ADVERTISEMENT

Output Parameters Versus Recordsets In Stored Procedures

Jul 20, 2005

I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Multiple Recordsets

Mar 16, 2001

Is it possible to return multiple recordsets from SQL to ASP in a single query?

TIA
Pilot

View 5 Replies View Related

@@IDENTITY Problem With Multiple Recordsets

Apr 24, 2008

Can anyone help with this problem.

I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.

The @@IDENTITY value returned is null unless I close the selection recordset.

I have created a simplified version of the code to demonstrate this.

The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)

ASP Code


<%
dim objConn, objRS1, objRS2, objRS3

sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub

sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub

function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function


OpenDatabase

strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3

do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")

objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close

' *1 objRS1.Close

strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close

response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")

' *2 response.end

objRS1.movenext
loop

objRS1.close

CloseDatabase

%>



Output

Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL

If I unrem the lines *1 and *2 then the output is

Old ID = 1, New ID = 11

I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.

Thanks is advance

Kennedy

View 3 Replies View Related

Report From Prcedure That Return Multiple Recordsets

Feb 11, 2007

Hi there,




I am creating a report from a stored procedure that returns
multiples record sets. For an example, my stored procedure is as follows








CREATE PROCEDURE MYPROCEDURE
AS









BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END












Now lets say I want to create a report that will display the
result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How
can I do this? Is there any way to bind the second record set to the report
dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this
Thanks
Moim

View 8 Replies View Related

Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution

Jun 14, 2007

Hi all -



I know this is prolly an old one but I would certainly appreciate some assistance =)



environment:

SERVER (IIS6, .NET2.0, SQL2005)

CLIENT (WIN2000,IE6,VBScript)



I have an aspx that is invoking ado on the clientside. I read somewhere that javascript does not support connecting to SQL clientside. I may be wrong. In any case I am using VBScript on the client.



I am using something like this to invoke the script




Code Snippet

<a href="#" onclick="doReport()">Click Here</< FONT>a>



doReport() looks something like this ...






Code Snippet

' ========================================================

sub doReport()



Dim stSQL, rs, oWord, oDoc, oRng

Dim stCurrentSection, stTemplatePath, dtNow, dtSOR, dtLastDataPoint

Dim iLastDataPoint



set oWord = CreateObject("Word.Application")

stTemplatePath = "http://crivm-ccdev/ccprocharts/supportfiles/CCWordReport.doc"

set oDoc = oWord.Documents.Open(stTemplatePath)

oWord.visible = true









stCurrentSection = "a"



loadDebug

'loadLive







openConnection()

' == THIS DB CALL GETS 2 RESULT SETS

'set rs = getrsCustomerInfoReport(mstUnits, left(mstCycles,3), mstAppName)









set rs = getReadOnlyRS("sCC_GetCustInfo_Report '" & mstUnits & "','" & left(mstCycles,3) & "','" & mstAppName & "'")



' == POPULATE VARS FROM 1st RESULT SET

dtLastDataPoint = rs(0)

iLastDataPoint = rs(1)



set rs = rs.NextRecordset ' <== THIS IS WHERE IS ERROR IS



msgbox rs(0)

end sub









here is my openconnection sub ... oConn is global




Code Snippet

<script type="text/vbscript" language="vbscript">



'== Cursor Location

CONST adUseClient = 3

CONST adUseServer = 2

' == Cursor Type

CONST adOpenStatic = 3

CONST adOpenForwardOnly = 0

CONST adOpenDynamic = 2

' == Lock Type

CONST adLockReadOnly = 1

CONST adLockOptimistic = 3

CONST adLockPessimistic = 2



' ========================================================



sub openConnection()



Dim stConn

set oConn = CreateObject("ADODB.Connection")





'stConn = "DRIVER={sql server};Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

stConn = "Provider=SQLOLEDB;Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

'msgbox oConn.state & vbcrlf & stConn1

oConn.open(stConn)

'msgbox oConn.state

ApplySecurity



end sub

' ============================================================

Sub ApplySecurity

'gbhasDBAccess is a global variable on both client and server sides.

'Server side is set in seccheck.asp, which should be at the top of every page

'Client side is set in ApplySecurity() of ConnectServer.asp

Dim appRole



appRole = "appWriters,(tsvc123)"



oConn.Execute "sp_setapprole '" & split(appRole,",")(0) & "','" & split(appRole,",")(1) & "'"



End Sub



</< FONT></script>



Before we moved to SQL2005 I always used this




Code Snippet

' ========================================================

Function getReadOnlyMultRS(strSQL)

set rs = CreateObject("ADODB.Recordset")

rs.CursorLocation = adUseClient

rs.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly

'Disconnect the Recordset

Set rs.ActiveConnection = Nothing

'Return the Recordset

Set getReadOnlyMultRS = rs

End Function



and this always allowed me to use set rs = rs.nextresultset

but now that we switched to SQL2005 it does not seem to work. I have verified in sql studio that this sp does indeed return 2 resultsets

View 4 Replies View Related

ADO Error 3251: Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution?

Jul 20, 2005

I posted this in the MS Access group, but no luck.------------------------------------------I've got another stored procedure in the same app that returns multiplerecordsets and the code works.But now I've written another SP and the code traps out with the 3251 message.The SP is writing two recordsets.When I run the SP in Query Analyzer, both recordsets appear.But when I step through the code, when the first RS should be there, it's"Closed" and nothing I've tried will make it open.Provider=SQLOLEDB.1 (which works on the other screen...)Seems like I've been here before, but I can't remember what the problem was.--PeteCresswell

View 7 Replies View Related

Named SQL 2005 Instance Multiple Active Recordsets-C# Stored Procedure-Connection String

May 7, 2008

I am working on a C# stored procedure for SQL 2005, and i've uncovered a couple questions.

First a description of the procedure:

I have a series of equations taking place to calculate a score based on activities in which the user participated in, that will give them an over all grade or rating. The calculations currently take place in the database, and I am moving this from T-SQL to C# CRL.

1. In order to connect the stored procedure to the database I use a SqlConnection and a SqlCommand to execute either dynamic sql or a stored procedure to return data to a data reader. Is there an easier way to connect to the database? In SSMS if i open up the query it knows what database i am connected to. Do I have to make a sql connection in C# stored Procedures?

2. I have multiple functions within the main C# Stored Procedure that I'm working on. This ends up requiring Multiple Active Recordsets. I must set this withing the connection string. Seeing as I'm using a named instance of SQL 2005, I now must put the userid, password, and server name into the code. Is there a more secure way to connect to SQL Server in a C# Stored procedure that allows MARS?

3. I encryped the connection string, and put it into the assembly, I wrote a decryption class, and in the procedure itself everytime I need to refrence the connection string, I call it, decrypt it, and pass it along. But my code to decrypt the connection string is in the compiled DLL, if the server was ever compromised the encrypted connection string and the key to decrypt it are sitting in the DLL. Is there a config file that I can use for C# Stored Procedures?

4. If I have to keep the connection string in the file, then I need to change that per environment. Example I have 3 test environments before production. So I would need to change the connection string for each file. That may be fine for one procedure, but what if I have 20, that will quickly get of hand?

5. Along the security lines, can the assembly for a C# Stored Procedure be called from outside the assembly? From a command prompt, or by a maliceous program? Or could it be called directly by a .NET application instead of going through a T-SQL Stored Procedure that is using
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PROJECTNAME].[CLASSNAME].[METHODNAME]


Thanks

View 1 Replies View Related

How Can You Get Multiple Output Rows From One Row?

May 5, 2003

I inherited a database with Street, CrossStreet1 and CrossStreet2. I wish to read the row once and output 3 rows.

I know I can run 3 separate query's but why read through 1 million rows 3 times, if you can read through it once.

I can do this in SAS using the output statement.
Hoping someone knows how to do this in SQL.
:confused:

View 6 Replies View Related

Multiple Output Parameter From Procedure

Nov 26, 2007

I have a sqlserver stored procedure with multiple output parameter like below. I am not sure how I can read the two output parameters at my VB.net code. I am trying to use ExecuteScalar from VB.net and it is throwing an exception.
Procedure:CREATE PROCEDURE dbo.spGetUser
@UserLoginId VARCHAR(80),  'E-mail of user
@Password VARCHAR(50), 'Password in hash
@UserId INT=NULL OUTPUT, 'system generated user id@UserType CHAR(2)=NULL OUTPUT 'type of the user
AS
BEGIN
..........
END
 VB Code:
Inside my business layer class 
===========================
TryWith objSQLCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "spGetUser".Parameters.AddWithValue("@UserLoginId", UserLoginId)
.Parameters.AddWithValue("@Password", Password).Parameters.AddWithValue("@UserId", UserId)
.Parameters.AddWithValue("@UserType", UserType).Parameters("@UserId").Direction = ParameterDirection.Output
.Parameters("@UserType").Direction = ParameterDirection.Output
End With
bolReturn = mobjDataAccess.Execute(objSQLCommand)
If bolReturn Then      GetDetail = True
End If
==========================
Inside my dataaccess layer class
===========================
Public Function Execute(ByVal objSQLCommand As SqlCommand) As Boolean
Execute = False
Dim intReturn As Integer ' 0 = Success, Otherwise Error
intReturn = 1
TryCall Connect()With objSQLCommand
.Connection = mobjConnection
.ExecuteScalar()
End WithCall Disconnect()
If intReturn = 0 Then
Execute = TrueEnd If
Catch ex As ExceptionCall Disconnect()
ex.Source = "Execute"
Execute = False
End Try
End Function
 

View 1 Replies View Related

Output Returning Multiple Lines

Sep 29, 2004

I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:

/* this creates the temp table */
Select count(*) as 'Donations', d_vst_id
into Donations_temp
from dnr_vst_db_rec
where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112)
and d_vst_status = 'DN'
and d_vst_dontyp in ('E1', 'E2')
group by d_vst_id

/* this query generates the output */

select distinct cast(getdate() as varchar(30)) as 'TODAY'
,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE'
,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE'
,case Donations
when '1' then sum(1)
else 0
end as 'ONE1'
,case Donations
when '2' then sum(1)
else 0
end as 'ONE2'
,case Donations
when '3' then sum(1)
else 0
end as 'ONE3'
,case Donations
when '4' then sum(1)
else 0
end as 'ONE4'
,case Donations
when '5' then sum(1)
else 0
end as 'ONE5'
,case Donations
when '6' then sum(1)
else 0
end as 'ONE6'
,case Donations
when '7' then sum(1)
else 0
end as 'ONE7'
,case Donations
when '1' then Sum(0)
when '2' then Sum(0)
when '3' then Sum(0)
when '4' then Sum(0)
when '5' then Sum(0)
when '6' then Sum(0)
when '7' then Sum(0)
else Sum(1)
end as 'ONEA'
from Donations_temp
group by Donations

Thanks.

View 1 Replies View Related

Output Search Criteria For Multiple OR?

Oct 21, 2014

My selection criteria is as follows:

where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'

I output the content field if the search is true but would like to also output which specific 'like' has been found.

Can I do this in the one pass or do I have to read the database separately for each condition?

View 3 Replies View Related

Saving Output From Multiple Queries

Sep 15, 2005

Greetings,I have what seems a simple problem....I want to save the output from the following queries in 1 file in'SQL Query Analyser', but I can only save each 'grid' separately.I have about 30 queries in total. 'Messages' tells me the number ofrows affected but I need the data.So can I save all the grids together at the same time in 1 file ?ThanksMikePRINT 'HEAT 3510'SELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,d.tpitdaydate AS Payroll_Date,c.tishactualenddate AS TS_end_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidLEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050614'and (d.tpitdaydate > '20050614'or d.tpitdaydate IS null)order by d.tpitdaydateSELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,c.tishactualenddate AS TS_end_date,g.thisdaydate AS TSH_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidINNER join timesheethistory g ON g.thistishguid = c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050619'order by g.thisdaydatePRINT 'HEAT 3213'

View 3 Replies View Related

How To Render Multiple Reports Into One Output File?

Nov 21, 2006

We are using SQL Server 2005 Reporting Services and looking to render multiple reports into one output file. So, for example, say we have four reports and when the user asks for the reports to be generated, we would like to have all four reports come out in one PDF (or Excel or HTML) file instead of four separate files. I hope this makes sense.

Is there any way to render multiple reports to one output file?

Thanks!


View 9 Replies View Related

Can You Have Multiple Output Parameters? Difference Between A Select Statement?

Mar 9, 2004

I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View 1 Replies View Related

SQL Server 2012 :: Get XML Output For Multiple Tables With 1:M Joins?

Jan 22, 2014

Assume i have 3 tables

Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children

Its not possible to display the results in SQL rows and columns.

If i try to it will give

PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2

Instead of writing seperate queries the application wants to receive an xml output as follows

<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>

How to get this output ?

View 1 Replies View Related

Output Param && Multiple Recordests From Stored Procedures

Jul 20, 2005

here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!

View 1 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

Why Recordsets

Aug 8, 2005

why should one use Recordsets and not SQL itself in database work. Isn't the latter muvch faster, more portable, and needs to be learnt only once?

View 1 Replies View Related

How To Concatenate Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all 

View 5 Replies View Related

Returning One Of Three Recordsets

Jun 21, 2007

Hi all,

I've written a query which needs to return different records depending on its argument.

I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?


Code:


ALTER PROC [dbo].[spFilteredContractListByUser]

@UserID varchar(7) --The user's 'T' number

AS

PRINT 'User ID is' + @UserID

--Get this user's filter type from tbl

DECLARE @FilterType int

SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID

-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END



The VBA code is:


Code:


rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")



Me.cmbUserFilter.Value contains the argument.

Thanks all

Dave

View 2 Replies View Related

Nested Recordsets

Nov 19, 2004

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.

View 8 Replies View Related

How To Concatenate 2 Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ?

i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.

if i use union, all the results will be mixed.

thanks for the help!

View 7 Replies View Related

Passing Recordsets

Sep 26, 2007

Greetings,

I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.

What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.

I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).

What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?

Thanks for your ideas

View 7 Replies View Related

How Can I Use The Last Recordset In A SP That Return 3 Recordsets ?

Mar 9, 2005

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 ?

View 3 Replies View Related

Browser Updateable Recordsets ?

Mar 26, 2000

I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net

View 2 Replies View Related

Paging Large Recordsets

Oct 21, 2007

Here's a question you'll never quit hearing: is there a convenient way to page through large recordsets in SQL Server 2000?

I'm writing some software which, for all intents and purposes, works like a messageboard: users can create threads, leave replies, and so on.

I have about a half million records in a few of my tables, and some of my queries return 1000s of results. I'd prefer not to return 1000s of records all at once, so I don't want to page my records in code; I'd rather page them in SQL Server. Naturally, I want to page replies. However, I don't know of a convenient way to page records in SQL Server 2000.

View 1 Replies View Related

Opening Mulitple Recordsets

Jul 20, 2005

I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave

View 2 Replies View Related

Asp / Sql Server Dates In Recordsets

Jul 20, 2005

Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob

View 1 Replies View Related

Closed Recordsets From Stored Procedures

Jun 18, 2001

Hello all,

I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.

So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.

Has anyone got any ideas on how to get round this?

Thanks
Dave Sykes

View 2 Replies View Related

Delete Recordsets With Same Date And Line

Jul 23, 2005

Hi All!I need help with a Statement!I am working with an Access2000 DB.I have the following Problem.ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH582-064L2.1008.03.2005 02:30:00FCAA14821701582-064L2.1008.03.2005 02:30:00FCAA14871701582-114L2.1208.03.2005 01:00:00FAC827501240582-114L2.1208.03.2005 01:00:00FAC827441240582-114L2.1208.03.2005 01:00:00FAC827501240582-094L2.707.03.2005 19:45:00FAE74323481582-094L2.707.03.2005 19:45:00FAE74489481582-094L2.707.03.2005 19:45:00FAE74489481581-294L2.807.03.2005 18:20:00FA8V2658221581-294L2.807.03.2005 18:20:00FA8V2652221581-294L2.807.03.2005 18:20:00FA8V2658221582-114L2.1207.03.2005 17:45:00FAAR20721236As you can see I have a few recordsets that are double. The Thing is, thereis an ID that makes them different.I need a Statement that deletes the surplus records where 'Datum' and'Linie' are identical to another record. 1 record has to remain of course.I thought of something like this.DELETE FROM tbAuswertWHERE EXISTS(SELECT *FROM tbAuswertWHERE (Linie AND Datum)IN (SELECT Linie AND Datum AS SuchkritFROM tbAuswertGROUP BY SuchkritHAVING ((Count(Suchkrit)>1)))But I get an error:You wanted to execute a Query that did not have the following expression'Not Linie = 0 And Not Datum = 0' in its AggregatefunctionPerhaps you ccan help me.ThanksJulia--Message posted via http://www.sqlmonster.com

View 3 Replies View Related

Empty Recordsets And Artificial Records

Jul 5, 2006

I'm currently running the following statement that is used in a CrystalReport. Basically, a record is returned when the T_PAYMENT.amounthas a record in the database based on the value of the T_MULTILIST.codefield. Currently, if there is no record returned, there is no listingin the report for the given T_MULTILIST.code.The user now wants a record to be displayed on the report when there isno record in the database - she wants it to display a value of '$0'for the given T_MULTILIST.code record. I tried to explain the fact thatis not possible the way things stand at the moment. Basically I needsome typeof case statement that says 'if there is no record returned, create asingle record and set T_PAYMENT.amount = 0' AFTER each query has beenexecuted.Anyone have any idea how to accomplish this?SELECT DISTINCT'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAILT_SHIPPING_DETAIL ONT_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='1040')AND (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )UNIONSELECT DISTINCT'English Language Arts, Kindergarten' as Rec_Type, 'EnglishLanguage Arts (Consumable)' as Super_Type,'' as Other_Type, 'Continuing Contracts' as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description,T_MULTILIST.code, T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAILON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='0040')and (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )Up to 40 more UNION statements follow the above 2.

View 3 Replies View Related

Rotating Information From Recordsets To Columns.

Jul 20, 2005

Hello,This problem perplexes me and I hope that someone has done somethingefficient.Take for example the data in the MASTER..SYSPERFINFO:SELECTCAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))FROM MASTER..SYSPERFINFOWHERE INSTANCE_NAME = N'TEMPDB'tempdb Data File(s) Size (KB) 51200tempdb Log File(s) Size (KB) 1272tempdb Log File(s) Used Size (KB) 738tempdb Percent Log Used 58tempdb Active Transactions 0tempdb Transactions/sec 186281tempdb Repl. Pending Xacts 0tempdb Repl. Trans. Rate 0tempdb Log Cache Reads/sec 0tempdb Log Cache Hit Ratio 0tempdb Log Cache Hit Ratio Base 0tempdb Bulk Copy Rows/sec 0tempdb Bulk Copy Throughput/sec 0tempdb Backup/Restore Throughput/sec 0tempdb DBCC Logical Scan Bytes/sec 0tempdb Shrink Data Movement Bytes/sec 0tempdb Log Flushes/sec 1578tempdb Log Bytes Flushed/sec 67882496tempdb Log Flush Waits/sec 226tempdb Log Flush Wait Time 47tempdb Log Truncations 248tempdb Log Growths 3tempdb Log Shrinks 0<I did the CAST and LTRIM so that it looks better when displayed in abrowser>I would like to keep statistics in a table with the following columns:INSTANCE_NAME,DATA_FILE_SIZE,LOG_FILE_FIZE,ACTIVE_TRANS,TRANS_PER_SECSo, instead of having a table with three columns and 23 rows(only 4 ofwhich I want), I would have a single row with 4 columns(plus theInstance_Name).Visualy, I want to call this a 90 degree rotation. Here's what theselect statement would then look like:SELECT *FROM SYSPERFINFO_ARCHIVEWHERE INSTANCE_NAME = N'TEMPDB'Here's the result set:tempdb 51200 1272 0 185198Is it possible to 'rotate' a recordset into columns?How would it be done?Gracias.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved