ASP Memory Crash

Hi all,
I am running reporting services on a dedicated server with 2GB of memory,
when ever a report is run that contains over 90,000 records the server
crashes and restarts. We have looked at the reports, and are to reduce the
criteria range to make smaller reports, but it then affects role the system
was designed to play in my organisation. If anyone has come across these
memory crashes and come across any ways of resolving them or ways that
haven't resolved them, including an increase in the memory size, it would be
greatly appreciated if you could reply. We are willing and able to throw
another 2GB at the problem, but are concerned this may only be a short term
solution and the problem will reoccur when the reporting size reaches 180,000
records.
Any assistance you may be able to provide would be greatly appreciatedIf you mean that the result set has 90,000 records or 180,000 records then
you have the wrong product. RS is not designed to generate reports that are
1500+ pages. Rendering is done in RAM so there is a direct correlation to
between number of records and the amount of RAM used. If the rendering
output is Excel or PDF then the amount of RAM consumed is even more. If the
destination is another program then there are better ways to do this. I know
that sometimes people are wanting to get a large amount of rows into Excel
for further analysis but it would be better to be using DTS and getting the
data out in CSV for them. Much much faster process.
If the output is not that many records but you have that many because you
are using filters then try to move away from filters. Filters brings over
all the data and then filters it. Use a query parameter instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> Hi all,
> I am running reporting services on a dedicated server with 2GB of memory,
> when ever a report is run that contains over 90,000 records the server
> crashes and restarts. We have looked at the reports, and are to reduce the
> criteria range to make smaller reports, but it then affects role the
system
> was designed to play in my organisation. If anyone has come across these
> memory crashes and come across any ways of resolving them or ways that
> haven't resolved them, including an increase in the memory size, it would
be
> greatly appreciated if you could reply. We are willing and able to throw
> another 2GB at the problem, but are concerned this may only be a short
term
> solution and the problem will reoccur when the reporting size reaches
180,000
> records.
> Any assistance you may be able to provide would be greatly appreciated|||You might try CSV and see if it takes up less memory than Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> If you mean that the result set has 90,000 records or 180,000 records then
> you have the wrong product. RS is not designed to generate reports that
are
> 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> between number of records and the amount of RAM used. If the rendering
> output is Excel or PDF then the amount of RAM consumed is even more. If
the
> destination is another program then there are better ways to do this. I
know
> that sometimes people are wanting to get a large amount of rows into Excel
> for further analysis but it would be better to be using DTS and getting
the
> data out in CSV for them. Much much faster process.
> If the output is not that many records but you have that many because you
> are using filters then try to move away from filters. Filters brings over
> all the data and then filters it. Use a query parameter instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
message
> news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > Hi all,
> >
> > I am running reporting services on a dedicated server with 2GB of
memory,
> > when ever a report is run that contains over 90,000 records the server
> > crashes and restarts. We have looked at the reports, and are to reduce
the
> > criteria range to make smaller reports, but it then affects role the
> system
> > was designed to play in my organisation. If anyone has come across
these
> > memory crashes and come across any ways of resolving them or ways that
> > haven't resolved them, including an increase in the memory size, it
would
> be
> > greatly appreciated if you could reply. We are willing and able to
throw
> > another 2GB at the problem, but are concerned this may only be a short
> term
> > solution and the problem will reoccur when the reporting size reaches
> 180,000
> > records.
> >
> > Any assistance you may be able to provide would be greatly appreciated
>|||Bruce,
Thanks for your responses.
Do you know if there is away to detect if the report is going to generate
over 200 pages and return a message to the user to say something like 'This
report may crash the server, are you sure you want to continue' ? From your
responses, and others I have read about memory, and RS limitations are the
only issue I am encountering, not programming issues, so if we confirm with
users before they run large reports it could solve our problems, unless they
select 'Yes' of course.
Any further assistance you may be able to provide would be greatly
appreciated.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>|||What you could do is have an intermediary report that does a count and then
provides the appropriate message and link. You can hide the real report from
the user in list view so they have to go through this. Also you could use
jump to url and render to Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||Thanks for the feedback Bruce I will give it a go !|||Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to manage the subscriptions (try to delete the irrelevant
ones) in the RS "Report Manager", for that particular report, System gives
an OutOfMemory exception. I think Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
I posted this question here, because I thought the problem is similar to
what you were talking (Memory management Issue).
Any Suggestions are appreciated.
Regards
Raj Chidipudi
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||I have exact same problem. My report is about 28000 rows and it takes about
15 minutes to render and end user gets frustrated and he tries to "End Task"
the browser - every thing hangs up. I can't put drill down, etc., so that
is not my option. Is there any way I can stream data, instead of wait to
retreive all rows from SQL? - just like SQL Query analyzer window - it starts
producing results as soon as you execute the query. This is really a big
issue in our organization. Because of this, people started to hate RS. I
need some kind of solution asap. Please help.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>

asp / sql server dates in recordsets

Hey,

I've got an Classic ASP app pulling some records out of a DB, which
worked fine when it was Access. When we upped to SQL Server, all of a
sudden, when the Recordsets came back, all the dates were empty, even
though the data was showing up in the DB.

About a year ago (only code asp apps once and a while), I remember
seeing this and finding something that just had something to do with the
order in which something was being referenced, but I just can't for the
life 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"rob" <rob@.pleaseremovespamcherny.com> wrote in message
news:XYadnScIEbMPLpvdRVn-uA@.giganews.com...
> Hey,
> I've got an Classic ASP app pulling some records out of a DB, which
> worked fine when it was Access. When we upped to SQL Server, all of a
> sudden, when the Recordsets came back, all the dates were empty, even
> though the data was showing up in the DB.
> About a year ago (only code asp apps once and a while), I remember
> seeing this and finding something that just had something to do with the
> order in which something was being referenced, but I just can't for the
> life 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

It's not really possible to give a good answer without more information. Do
you have an example of a query which returns data in Query Analyzer, but not
in ASP?

You might also look at www.aspfaq.com, if you haven't already - there are
quite a lot of articles about handling dates.

Simon

ASP - Insert Multiple records

I wanna know how to insert all the records that is checked into my SQL Server.. BUT all the records inserted is the same except for one column in my SQL Server which is the 'Partner' column..All the records that is checked?
Please explain some more!|||Well... i have a webpage in ASP. It displays all the ringing tones on the particular tones.asp page. Currently there is no check box as I do not know how to implement the method..

Therefore what i mean is that all the ringtones (to insert as new record in db), I have to view one by one to insert.

What i want to know is the way on how to mass insert all the ringtones with just a single click on the button. Meaning that i checked a few check boxes, and click the button..

You know what i mean my fren..?

Hope you can help..|||BTW, its sort of like the HOTMAIL... where you checked some boxes and PUT INTO FOLDER.... bla bla bla..

But what i want now is checked my ringtones check boxes and click a button to insert as a new record in my DB..|||<%
If len(trim(Request.Form("mTones"))) > 0 Then
Dim SQLStmt, x
SQLStmt = ""
for x = 1 to request.form("mTones").count
SQLStmt = SQLStmt & "INSERT INTO kek (tone) VALUES('"request.form("mTones")(x) & "';"
next
conn.execute(SQLStmt)
end if
%>

<form methos=post action=tones.asp>
<input type=checkbox name="mTones" id="tone1">tone1
<input type=checkbox name="mTones" id="tone2">tone2
<input type=submit>
</form>sql

As400

Hi, all!
I try run DTS from SQL with connection to AS400 (Client Access 32-bit ver. 8).I know this should be about 9000 records, but after 5000 records
it's stopped.
Plz , explain!
I'll gone to be crazy... :(((Run the profiler on the SQL Server to see that what exactly is going on..

Array?

I have a query where I pull data based on a min function the gives me the
newest order. However when I pull the data I will get four records as I nee
d
to pull all the data for this order. What I need to know is how do I take
the data that I need assign a variable to it and only return one record.
Do I need to write a stored procedure that writes the data fields to a
variable and then bring them into my query?>I have a query where I pull data based on a min function the gives me the
> newest order. However when I pull the data I will get four records as I
> need
> to pull all the data for this order.
I'm lost. Please show DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Here is my code:
declare @.con as varchar(30)
set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus
where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus =
1 and
dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
--Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1 an
d
--dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_1612BinData.Station5c, dtbl_partList.partID,
dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
pckgPackageNumber, pkctSlotNumber
From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
dtbl_OrderStatus, dtbl_packagingDetails
Where @.con=dtbl_workOrder.corhOrderNumber and
dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
Here is my response:
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c partID
CustomerPartId OrderStatus
pckgPackageNumber pkctSlotNumber
----
---- --
-- -- -- --
----
---- --
---- --
2006-0100001 CRM
1,0 0,0 2,0
44040192,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 CURLKOA
1,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 222MID
115,0 47185920,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
2006-0100001 22xMOD
5,0 0,0 0,0
0,0 0,0 1655009B
7L1P 7J228 BDSMGJ 1
000000001 7
I need to take the attvID and station information and string it out like
this...
corhOrderNumber attvId
Station1 Station3 Station5a
Station5b Station5c attvId2 Station1.2 Station2.2
Station5a.2 Station5b.2 Station5c.2
Station1.3 Station2.3 Station5a.3 Station5b.3
Station5c.3
Station1.4 Station2.4 Station5a.4 Station5b.4
Station5c.4
partID CustomerPartId
OrderStatus pckgPackageNumber
pkctSlotNumber
Basically so it only returns one record. I will then need to pass this
information as a variable to another program.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm lost. Please show DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
>
>|||I believe we asked for DDL, not your code. What you posted looks like
"camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
prefix?
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Your entire view of RDBMS is
wrong. You do not know that rows are not ANYTHING like a record, etc.
You have just been told by one of the people who wrote this language
that you do not know what you are doing. Think about that. Then tell
your boss, or I will. You need more help than you can get in a
Newsgroup.|||Hey Celko I really appreciate your help with this. Your the MAN!!
"--CELKO--" wrote:

> I believe we asked for DDL, not your code. What you posted looks like
> "camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
> prefix?
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Your entire view of RDBMS is
> wrong. You do not know that rows are not ANYTHING like a record, etc.
>
> You have just been told by one of the people who wrote this language
> that you do not know what you are doing. Think about that. Then tell
> your boss, or I will. You need more help than you can get in a
> Newsgroup.
>|||If I were in the position to need to do what you ask..
If there were always a max of 4 rows, and you can uniquely identify them,
I would create a user defined function which returns a table. THe table
definition would look like your code suggests.
You could insert the first row, then update it with the other rows of data -
appending to the trailing columns.
Then return the table with a single row...
Alternately you could create a stored procedure which has a local variable
for each value... populate the values with your 4 select statements, Then
SELECT the values out of the SP as a result set.
The first option is more flexible because it yeilds a table instead of a
result set...
Good luck, and hope this helps;
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"meverts" wrote:
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStat
us
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
=
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId OrderSta
tus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I believe we asked for DDL, not your code.
Correct, we did not get any CREATE TABLE or INSERT statements, so we have to
guess about a lot of things (or keep asking, maybe if we put sugar on top?).
However, the rest of your post is a useless tangent. Who cares if he uses
camelCase names? He still has a database problem he needs help with. Just
because his metadata, element names and table structure aren't identical to
what *you* would have created with the same task in front of you, doesn't
mean he should be ignored or told to go take a class or told that he is not
worthy of the newsgroup because he is not as smart as you.
I have been in the situation where the structure is NOT mine and I just have
to deal with it. I have also been in the situation where I've been made
responsible for parts of the project that did not align exactly with my core
competencies. And I have been in the situation where the guy that knew all
about it has been fired or left for other reasons. You should consider
giving people the benefit of the doubt instead of sh*tting on their head.
I have met you in person multiple times, I have shared lunch with you, and I
know that you can be a polite and even humble person. Why you choose to be
such a hateful, miserable know-it-all here, I have yet to figure out.
A|||No offense, but I can't really read this (particularly the results, which
may have looked good while composing your message, but certainly don't now).
Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
more concise version of your desired results, so we can understand exactly
what you need? You might need to see http://www.aspfaq.com/500 again.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
> Here is my code:
> declare @.con as varchar(30)
> set @.con = (Select MIN(corhOrderNumber)from dtbl_workOrder,
> dtbl_OrderStatus
> where dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus
> =
> 1 and
> dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder)
> --Select MIN(corhOrderNumber)from dtbl_workOrder, dtbl_OrderStatus where
> dtbl_OrderStatus.orderstatus is null or dtbl_OrderStatus.orderstatus = 1
> and
> --dtbl_orderstatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder
>
> Select dtbl_workOrder.corhOrderNumber, dtbl_attributeList.attvId,
> dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Stati
on5a,dtbl_1612BinData.Station5b,
> dtbl_1612BinData.Station5c, dtbl_partList.partID,
> dtbl_partList.CustomerPartId, dtbl_OrderStatus.OrderStatus,
> pckgPackageNumber, pkctSlotNumber
> From dtbl_workOrder, dtbl_attributeList, dtbl_1612BinData, dtbl_partList,
> dtbl_OrderStatus, dtbl_packagingDetails
> Where @.con=dtbl_workOrder.corhOrderNumber and
> dtbl_workOrder.wordReleaseOrder=dtbl_attributeList.wordReleaseOrder
> and dtbl_attributeList.attvId=dtbl_1612BinData.attri and
> dtbl_partList.wordReleaseOrder=dtbl_workOrder.wordReleaseOrder and
> dtbl_OrderStatus.wordReleaseOrder = dtbl_workOrder.wordReleaseOrder and
> dtbl_workOrder.wordReleaseOrder = dtbl_packagingDetails.wordReleaseOrder
> Here is my response:
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c partID
> CustomerPartId
> OrderStatus
> pckgPackageNumber pkctSlotNumber
> ----
> ---- --
> -- -- -- --
-
> ----
> ---- --
> ---- --
> 2006-0100001 CRM
> 1,0 0,0 2,0
> 44040192,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 CURLKOA
> 1,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 222MID
> 115,0 47185920,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
> 2006-0100001 22xMOD
> 5,0 0,0 0,0
> 0,0 0,0 1655009B
> 7L1P 7J228 BDSMGJ 1
> 000000001 7
>
> I need to take the attvID and station information and string it out like
> this...
> corhOrderNumber attvId
> Station1 Station3 Station5a
> Station5b Station5c attvId2 Station1.2 Station2.2
> Station5a.2 Station5b.2 Station5c.2
> Station1.3 Station2.3 Station5a.3 Station5b.3
> Station5c.3
> Station1.4 Station2.4 Station5a.4 Station5b.4
> Station5c.4
> partID CustomerPartId
> OrderStatus pckgPackageNumber
> pkctSlotNumber
> Basically so it only returns one record. I will then need to pass this
> information as a variable to another program.
> Thanks,
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Here are the ddl's for the three table from which my query uses.
Basically what I want to do is take 4 sets of data back.
I would like to figure out how to make this into one set.
CREATE TABLE [dtbl_workOrder] (
[corhOrderNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhVinNumber] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[corhSequenceNumber] [int] NOT NULL ,
[prdfId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wordDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordBatchQty] [int] NULL ,
[wordPreScheduleKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBatchIdSched] [int] NOT NULL ,
[wordBatchSerialSched] [int] NOT NULL ,
[wordReleaseOrder] [int] NOT NULL ,
[wordPackOrder] [int] NOT NULL ,
[wordProcessStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordProductionStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordBackflushStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[wordPackStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordHold] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wordCreationDateTime] [datetime] NULL ,
[wordUpdatedDateTime] [datetime] NULL ,
[wordReleaseDateTime] [datetime] NULL ,
[labelID] [numeric](18, 4) NULL ,
[wolvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wolvDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asshId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assuId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wcelId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSequence] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_workOrder] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_OrderStatus] (
[wordReleaseOrder] [int] NOT NULL ,
[OrderStatus] [int] NULL ,
[OrderFillDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSubmitDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_dtbl_OrderStatus] PRIMARY KEY CLUSTERED
(
[wordReleaseOrder]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dtbl_attributeList] (
[wordReleaseOrder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[partId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attributeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[attnId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attvId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[attribkey] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_dtbl_attributeList] PRIMARY KEY CLUSTERED
(
[attribkey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Currently I return the following( I know this doesn't look very good)
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 CURLKOA 1,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 222MID 115,0 47185920,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
2006-0100001 22xMOD 5,0 0,0 0,0 0,0 0,0
1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
The desired results would be like this
2006-0100001 CRM 1,0 0,0 2,0 44040192,0 0,0
CURLKOA 1,0 0,0 0,0 0,0 0,0 222MID 115,0
47185920,0 0,0 0,0 0,0 22xMOD 5,0 0,0 0,0
0,0 0,0 1655009B1655009B 7L1P 7J228 BDSMGJ 1 000000001 7
I hope this helps. I appreciate you help. I obviously am very new at this,
and dont' know what I am doing, but sometimes in the real world you need to
learn on the job.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> No offense, but I can't really read this (particularly the results, which
> may have looked good while composing your message, but certainly don't now
).
> Could you provide DDL (CREATE TABLE ...), sample data (INSERT ...), and a
> more concise version of your desired results, so we can understand exactly
> what you need? You might need to see http://www.aspfaq.com/500 again.
>
>
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:549C0EB5-1406-4433-A165-CF06CE38ABD6@.microsoft.com...
>
>|||In my experience, displaying multiple rows of data as one usually turns out
to be more trouble than it is worth. I reccomend looking for another way to
present the data, or possibly handling it in your app instead of in the
database. I've been forced to do what you are discussing in the past, and
have found ugly ways to do it, but I dont recomend it. The biggest problem
is that the number of rows which need to be consolidated has a tendency to
change over time, which means you are constantly updating your code to
match.
Revisit the reasons for this aproach and see if there is another way to
display this data that will fit your business need.
That said, if you absolutely have to do it this way, populating a temp table
in a stored procedure is better than any of the solutions I have used in the
past. It shouldnt be too much trouble to loop through the results and
insert/update the table as needed, then select from the table when you are
done.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:998EFAD9-7CCD-470D-9183-D89F8CB2FBFB@.microsoft.com...
> If I were in the position to need to do what you ask..
> If there were always a max of 4 rows, and you can uniquely identify them,
> I would create a user defined function which returns a table. THe table
> definition would look like your code suggests.
> You could insert the first row, then update it with the other rows of
data -
> appending to the trailing columns.
> Then return the table with a single row...
> Alternately you could create a stored procedure which has a local variable
> for each value... populate the values with your 4 select statements, Then
> SELECT the values out of the SP as a result set.
> The first option is more flexible because it yeilds a table instead of a
> result set...
> Good luck, and hope this helps;
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "meverts" wrote:
>
dtbl_OrderStatus
dtbl_OrderStatus.orderstatus =
and
dtbl_1612BinData.Station1,dtbl_1612BinData.Station3,dtbl_1612BinData.Station
5a,dtbl_1612BinData.Station5b,
dtbl_partList,
OrderStatus
> -- -- -- --
-
Station2.2
the
as I

Array

We are creating a report that we view detail records and find a row in the
list of records and want to grab one of the fields associated with that
record.
Example:
I have a loan number and principle balance.
I count the number of loans for a borrower.
Here is the data:
LN NUM Prin
1 5.00
2 10.00
3 15.00
The middle record is the one I want to use. But what I want as well is the
prin amount. How do I tell a function to look at the prin amount instead of
the ln num field?Hi Susan,
I've run into a similar issue. Were you able to resolve your issue? If so
please tell.
THanks,
"Susan" wrote:
> We are creating a report that we view detail records and find a row in the
> list of records and want to grab one of the fields associated with that
> record.
> Example:
> I have a loan number and principle balance.
> I count the number of loans for a borrower.
> Here is the data:
> LN NUM Prin
> 1 5.00
> 2 10.00
> 3 15.00
> The middle record is the one I want to use. But what I want as well is the
> prin amount. How do I tell a function to look at the prin amount instead of
> the ln num field?
>|||No I actually was looking for a way to calculate the median. I had to write
the report in Crystal.
"DigitalVixen" wrote:
> Hi Susan,
> I've run into a similar issue. Were you able to resolve your issue? If so
> please tell.
> THanks,
>
> "Susan" wrote:
> > We are creating a report that we view detail records and find a row in the
> > list of records and want to grab one of the fields associated with that
> > record.
> > Example:
> >
> > I have a loan number and principle balance.
> > I count the number of loans for a borrower.
> > Here is the data:
> >
> > LN NUM Prin
> > 1 5.00
> > 2 10.00
> > 3 15.00
> >
> > The middle record is the one I want to use. But what I want as well is the
> > prin amount. How do I tell a function to look at the prin amount instead of
> > the ln num field?
> >

Arithmetic overflow error - in a changed table!

Hello.
I had a table with identity column set to datatype [smallint]. I've
reached near 32800 records. That's the limit for [smallint].
So, I've changed the identity datatype to [int], that SHOULD allow me
to have more records. But the error continues.
"Arithmetic overflow error converting expression to data type
smallint".
AND there's no smallint anymore. It's weird. See by yourself:
CREATE TABLE [forum] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
DEFAULT (getdate()),
[forumnome] [varchar] (100) NULL ,
[forumemail] [varchar] (100) NULL ,
[forumidade] [varchar] (50) NULL ,
[forumcidade] [varchar] (100) NULL ,
[forummensagem] [varchar] (1000) NULL ,
[forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
(1),
[forumip] [char] (15) NULL ,
CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
) ON [PRIMARY]
Am I missing something? Should I need to execute some kind of
"table-reseter" to make SQLServer2000 forget it's [smallint] past?I might be a variable that you are using in one of you sp where you do the
insert
"Rodrigo Volponi" <volps@.hotmail.com> wrote in message
news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?|||Show us how to get that error.
Rodrigo Volponi wrote:
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi Rodrigo,
It's very strange without a doubt. One solution should be simply do the
following steps:
1)select * into forum20050323 from forum
2)truncate table forum
3)insert into forum
select (do just sp_help forum in order to get all the filed but leaving off
the identity field)
from forum20050323
truncate command is bearing on mind the original values on a identity field.
Let me know your doubts or concerns with this.
See you later,
"Denis" wrote:

> I might be a variable that you are using in one of you sp where you do the
> insert
> "Rodrigo Volponi" <volps@.hotmail.com> wrote in message
> news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
>
>|||Check for triggers on the table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rodrigo Volponi" <volps@.hotmail.com> wrote in message
news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?

Are these two statements equivalent?

Both return the same records in the same order, with the same values, and I
think I'm right, but I just wanted to get some other eyeballs on this so I
can win the argument we're having here, because mine (the second one) is
more than twice as fast.
-- Uses a WHERE...IN(...) and subquery on one of the joined tables
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE ss.SampleSourceKey IN (
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
)
ORDER BY ss.SampleSourceKey
-- Uses a column from one of the joined tables.
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE clmr.CountryKey IS NULL
ORDER BY ss.SampleSourceKey
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustanethe usual: if this query
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
can ever return a null, then the first query will return nothing...|||> the usual: if this query
> SELECT clmr.SampleSourceKey
> FROM CLMR WITH (NOLOCK)
> WHERE CountryKey IS NULL
> can ever return a null, then the first query will return nothing...
Ooo I had forgotten that, but we're safe here, because clmr.SampleSourceKey
is required.
Thanks for the brainpick!
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane