Field not sorting in ascending order

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

That's because your field is not numeric, if it were numeric, you would expect 61,100,200

but, with text, it looks at MP with a '1' and sees it first, then, MP with a '2' and sees it next - - naturally 6 is after 2 - - but that's the reason.

|||

It is possible to get text to behave a bit like numbers.

If you had stored this instead, it would sort correctly:

MP061, MP100, MP200

This example presumes that the numerical component will always be no more than 3 characters and all 3 postiions are identified for each record (even if they are zero). In general, it's better not to try to sort alpha-numeric data in a numerical sort order.

|||If all of your records have a 2-character prefix in that column, andthere are only numeric characters that follow, you can use thisapproach to solve your sorting problem:

SELECT
someColumns
FROM
someTable
ORDER BY
CAST(SUBSTRING(mpColumn,3,99) AS integer),
mpColumn

Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Field name as a parameter to a stored procedure?

Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option

field mapping

Hi all,
I have written many stored procedures in our database to import data from
another database. Now I need to create a report that lists the source table
and column names and their corresponding destination table and column names.
I think it will be difficult and time-consuming for me to open each
procedure and copy this information manually. Are there any procedures or
queries that can help me in generating this report.
Thanks in advance.Hi,
maybe this will help you ...
SELECT OBJECT_NAME(id) AS Source, OBJECT_NAME(depid) AS Uses
FROM dbo.sysDepends WHERE id = OBJECT_ID('proc_sys_InsertPropertyValue'
)
All dependencies of objects are stored in the relation dbo.sysdepends!
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htmsql

Field list is empty when calling stored procedure

Hello!
I am having problems retrieving list of fields when using stored
procedure as Dataset Source. I noticed that Reporting Services can retrieve
field list if stored procedure is trivial (simple select etc.). In my case,
I am insrting into temporary table and after some manipulations return
resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
SET FMTONLY OFF when reading list of the fiels. This returns empty result
for my stored procedure.
I can not move any further without field list being populated properly. I
can try to replace stored procedure with the view etc. but I would really
like to know if someone experienced similar problems.
Any help is greately appreciated,
IgorIgor,
I have experienced that same behavior. The stored procedure will run but
I will not get a field list. I received the error that told me to hit
REFRESH to get the fields list ... I returned to the data tab, ran the
procedure and hit the refresh button ... then when I returned to layout tab
... the fields list was there. Otherwise I have read in books that you have
to type in your own fields list which to me was not a very acceptable answer
as my fields list was quite extensive. Hope that helps you!
MJ
"imarchenko" wrote:
> Hello!
> I am having problems retrieving list of fields when using stored
> procedure as Dataset Source. I noticed that Reporting Services can retrieve
> field list if stored procedure is trivial (simple select etc.). In my case,
> I am insrting into temporary table and after some manipulations return
> resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
> SET FMTONLY OFF when reading list of the fiels. This returns empty result
> for my stored procedure.
> I can not move any further without field list being populated properly. I
> can try to replace stored procedure with the view etc. but I would really
> like to know if someone experienced similar problems.
> Any help is greately appreciated,
> Igor
>
>

Field List from a Stored procedure

I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.

Thanks,

Larrynot sure what exactly you looking for. try sp_helptext sproc_name|||I am looking for a way to get a list of the columns that will be in the recordset returned by a stored procedure. I need column name and type information.

sp_helptext seems to return the full contents of the sproc, which would require that I parse this, and any linked procedures, to get the info I am looking for. This is what I am trying to avoid.|||Do your procedures do SELECT only? You can use sp_depends and retrieve the structures of all dependent tables, but this will also include fields that your procedure does not include. It'll be easier to go after views.|||The procedures I am interested in do selects only. But they pull from other procedures and views.|||I am looking for something like that as well. So far I am using sp_sproc_columns to fetch the list of params from a SQL Server stored procedure.

There's nothing that I've found that will return a vertical list of outputs though. Ultimately I would like to display the fields returned by the sproc and their data types. Hopefully I won't have to re-invent the wheel by having to parse the sproc text.

I'll post back if I find anything else.

Thanks,

Doug

few questions about sql server MSDE

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

See my response to your identical posting in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

few questions about MSDE...

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

MSDE has been replaced with SQL Server 2005 Express.

For answers to your questions, as well as a source for tutorials, refer to Books Online.

SQL Server 2005 Express
http://msdn2.microsoft.com/en-us/sql/aa336346.aspx

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||pls if u don't know the answer then don't reply any post ... and thanks for googling for me|||

Some times it is not bad approach to provide the link which answers all your qurestions. Arnie posted all the MSDN URLs, you need not google it. If you read these MSDN you will get answer for all your questions.

|||here we come to get answers.. ofcourse after going thru whole book every1 will get answer and if we have to go thru books then there is no point to come on such platforms. i put that question on two forums and this person asked me on other forum that i shouldn't post here .. i feel convinient to put on different so i could have early and better response. i don't think so any1 should mind on that atleast.
i went on these links but .. anyhow here i don't think so other should ask what to do and what not.. and if there is some question of any1 then that person shouldn't be forwarded to books atleast he can tell me chapter anyhow thanks m disappointed|||

Muhammad,

You have to understand that this forum is simply a medium, most often an unnecessary medium, where you'll find many patterns in responses. For example, the higher the posting count the reponder has, the more likely they will reply with links.

I share an equal frustration with you on not receiving a direct response here. I have found only 1 use for these forums and that is to post actual code, when I encounter a problem, simply for a second or third set of eyes. Outside of that, you'll find no real experts here and you'll discover that this forum has developed into an automated link service.

Just my twist on it,

Adamus

|||no man actually i don't agree with you. here i got answers most of the time and correct and to the point information. all fingers are not same. every person has his/her own approach. its very good platform. i m just waiting for answer here, not references or comments or advises .. i hope you got my point

i still say thanks to that person who try to answer my question but in fact i don't like stupid advises or comments.

i didnt mean to hurt any1. sorry if anything of mine hurt any1.

Fetching data from a Flat file in SQL Server2000

Posted - 11/03/2006 : 08:00:37 AM
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>

Fetch Question

I have a stored procedure that inserts one row into a table. From a
SELECT statement I would like to call the SP on each row in the
results. Is setting up a cursor and using fetch statements the best
way (or even the only way) to do this?Hi

It is probably the safest way to do it, but without knowing exactly what you
are trying to do it is hard to suggest alternatives.

John

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0310010701.261cbff2@.posting.google.c om...
> I have a stored procedure that inserts one row into a table. From a
> SELECT statement I would like to call the SP on each row in the
> results. Is setting up a cursor and using fetch statements the best
> way (or even the only way) to do this?

Fetch Loop stored procedure

What is wrong with this stored procedure? This should work right?

Create PROCEDURE UpdRequestRecordFwd
@.oldITIDint ,
@.newITID int
AS
Declare @.RRID int
Declare @.APID int
Declare crReqRec cursor for
select RRID from RequestRecords where ITID = @.oldITID
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO

(1) what is the error you get ? its hard toguess unless you give us complete info
(2) for what you are doing above you dont seem to need a cursor.

Update RequestRecords set ITID = @.newITID where RRID = @.RRID

would work just fine without any cursor..unless you are doing other stuff in between.

either way, you need to give more details.

hth|||(1) I am not getting any error. It's just won't update
(2) I am have multiple RRID's (unique primary key) with one ITID. I want all the RRID's with this ITID to be replaced with the new ITID returned.

I removed the fetch as you suggested... still doesn't work..

Create PROCEDURE UpdRequestRecordFwd

@.oldITIDint ,
@.newITID int

AS
Declare @.RRID int

set @.RRID = (select RRID from RequestRecords where ITID = @.oldITID and RRStatus = 'IA' and APID is null)

Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID

Go|||if you just need to update ITID's then how do the RRID's come into picture...
see if this helps :

Create PROCEDURE UpdRequestRecordFwd
@.oldITID int ,
@.newITID int
AS
Update RequestRecords set ITID = @.newITID where ITID = @.oldITID
Go

hth|||I do appreciate your help but it was my mistake, permission to execute was not checked. Thank you for your time.

Fetch cursor help

Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...

Any help is highly appreciated...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput
AS
Declare @.RRID int
Declare @.APID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID
set @.APID = (select APID from RequestRecords where REID = @.REID and RRID = @.RRID)
set @.intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
 ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
 Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID and ImplementationGroup_Location.LOID = @.LOID )
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.Identity
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This is the newer version but still getting the same results. PLEASE HELP...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput

AS

Declare @.RRID int
Declare @.APID int
Declare @.APID2 int
Declare @.FS1 int
Declare @.FS2 int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)

Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null

open crReqRec
fetch next from crReqRec into @.RRID
set @.FS1 = @.@.fetch_status

Declare crAPID cursor local for
select APID from RequestRecords where REID = @.REID and RRID = @.RRID
open crAPID
fetch next from crAPID into @.APID2
set @.FS2 = @.@.fetch_status
set @.APID2 = (select APID from RequestRecords where APID = @.APID)
set @.intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID2 and ImplementationGroup_Location.LOID = @.LOID )

insert into ImplementationTasks
(IMID, ITStatus,ITStatusDate) VALUES (@.intIMID,'2',GetDate())
SET @.RetVal = @.@.Identity

while @.FS2 = 0
while @.FS1 = 0

Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID

fetch next from crAPID into @.APID2
FETCH NEXT FROM crReqRec into @.RRID
end

close crReqRec
deallocate crReqRec
close crAPID
deallocate crAPID

--EXEC TrigRetReqRecIDP2 @.REID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Feeding selections into a stored procedure

Hello All,

I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.

Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.

Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.

This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?

Any help appreciated.

Mo

P.S. stored procedure look like this:

------

@.centreid int,
@.startdate varchar(20),
@.enddate varchar(20)

AS

SELECT centreid, datecreated, centrename

FROM tblcentres

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)
AND (DataLength(@.startdate) = 0 OR datecreated >= @.startdate)
AND (DataLength(@.enddate) = 0 OR datecreated <= @.enddate)

I would pass a 0 or some other number like 9999 which is not in the list of centerId's and do a check in the stored proc. If its the 0 or 9999 you passed remove that from the where condition. Something like this:

IF @.centerId = 0SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)ELSE SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR CentreID = @.CentreID)AND (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)

|||

Is this part right:

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)?

Seems like you had the right idea for startdate/enddate, but you didn't implement it correctly for CentreID. You could change it to:

WHERE (@.CentreID=-1 OR CentreID = @.CentreID) then have the "All Centres" value -1. Or you can set the options value to a blank, have the sqldatasource convert blanks to nulls, and check for IS NULL. Any of those work.

I personally prefer to use the blank/null approach as most of the dropdown values I use are values from an identity column, and the field can not possibly be blank (It's marked not null). If it's not from an identity column, and is some kind of varchar field, then I'll simply use "All" as the value, and if someone put that into the database and they want to specifically search for it, they are sol.

|||Good catch Motley. I just cut n paste'ed the OP's query to give him/her an idea of how to approach the problem.|||Thanks to both of you for the advice and also for correcting the errors in my SQL.

Your suggestion(s) worked perfectly!

Mo

Feed stored procedure with SELECT resultset

I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
about 50 input parameters. PROC2 is the main procedure that does some
data modifications and afterwards calls PROC1 using an EXECUTE
statement.

The input parameter values for PROC1 are stored in a table in my
database. What I like to do is passing those values to PROC1 using a
SELECT statement. Currently, all 50 parameters are read and stored in
a variable, and afterwards they are passed to PROC1 using:

EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50

Since it is a lot of code declaring and assigning 50 variables, I was
wondering if there is a possibility to run a statement like:

EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)

Any help on this is greatly appreciated!On 21 Oct 2004 07:19:02 -0700, Dieter Gasser wrote:

> I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
> about 50 input parameters. PROC2 is the main procedure that does some
> data modifications and afterwards calls PROC1 using an EXECUTE
> statement.
> The input parameter values for PROC1 are stored in a table in my
> database. What I like to do is passing those values to PROC1 using a
> SELECT statement. Currently, all 50 parameters are read and stored in
> a variable, and afterwards they are passed to PROC1 using:
> EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50
> Since it is a lot of code declaring and assigning 50 variables, I was
> wondering if there is a possibility to run a statement like:
> EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)
> Any help on this is greatly appreciated!

You could build up a dynamic SQL statement and execute it that way, but I
think it would be vastly better if your spPROC1 read its inputs to be in a
table, rather than as parameters.

Feature request: Code packages

Hi. One feature that would be great for SQL Server is the ability to wrap your code (functions, stored procs) in a package, like you can on Oracle. Then when moving code to production, you can just move the package. This might already be possible and I'm not familiar with how to do it. This would make a great database even better. Thanks

Many folks typically do that by using the Source Code Control tool.

(You do use a Source Code tool, don't you?)

Otherwise, in order to post a suggestion that will be considered by the SQL Server development team, visit this link.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

Feature Request: 2005 Beta 2

Greetings,
I have a feature request for the Enterprise Studio.
We have about 500+ stored procedures in our CRM application and opening up
Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at a
huge list of stored procedures and find the one I'm specifically looking
for. Granted, in 2005 there is a "filter" it is rather unintuitive.
I'd like to request the abililty to add my own "folder" to the list where I
can create categories to group my stored procedures. for example, I can
create a "Invoices" group where all my invoice related SP's go, and a
"Policies" group where all my policies related SP's will go. In addition to
this, we have more tables than SPs, so we should be able to group any
object, Table, SP, View, etc.
Not sure if you've considered this already but the filtering isn't as
intuitive. It requires me to have to constantly enter in new filters to
filter the list before I can browse around. I'd like to browse directly to
what I'm looking for.
Thanks,
Shawn
Hi Shawn,
Have you considered using Schemas for this? (Introduced in SQL Server 2005
also).
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||What are schemas in this context? after a cursory google and help topics
lookup, I have no clue what they can do for me.
Thanks,
Shawn
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Shawn,
> Have you considered using Schemas for this? (Introduced in SQL Server 2005
> also).
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
up[vbcol=seagreen]
at[vbcol=seagreen]
where[vbcol=seagreen]
addition
>
|||I believe this has been suggested before but to make sure please send the
details of your request to sqlwish@.microsoft.com
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||Hi Shawn,
Take a look at the Northwind sample database in SQL Server 2005 Beta if you
have access to it from the MSDN site. Whereas Northwind's tables, procs,
etc. before were just one big list, now there are separate schemas for
HumanResources, etc. with only those objects relevant to that part of the
app in them.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
> What are schemas in this context? after a cursory google and help topics
> lookup, I have no clue what they can do for me.
>
> Thanks,
> Shawn
>
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...
> up
> at
> where
> addition
>
|||Sorry, brain explosion - meant to point you to the Adventureworks sample,
not Northwind.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OLy2UoU0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Hi Shawn,
> Take a look at the Northwind sample database in SQL Server 2005 Beta if
> you have access to it from the MSDN site. Whereas Northwind's tables,
> procs, etc. before were just one big list, now there are separate schemas
> for HumanResources, etc. with only those objects relevant to that part of
> the app in them.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
>

Favorites Storage in SQL Server Books-on-line

Where are the topics (links) saved to the favorites tab of the books-on-line
stored. I want to tranfer my topics to a new machine. Thank you for your
assistance.
BobIn the hh.dat file. The location of this file depends on the
OS but you would want to look for the path to
Application Data\Microsoft\HTML Help\hh.dat
You can rename the existing one on the new machine and then
copy the hh.dat from your current machine.
I've never had problems doing it but if you have problems,
you can delete the hh.dat that you copied and then rename
the original back to hh.dat
-Sue
On Wed, 17 Nov 2004 11:30:17 -0500, "Robert A. Farrenkopf"
<Bob.Farrenkopf@.BestSoftware.com> wrote:
>Where are the topics (links) saved to the favorites tab of the books-on-line
>stored. I want to tranfer my topics to a new machine. Thank you for your
>assistance.
>Bob
>

fatal exception c0000005

My database mysteriously took a nosedive yesterday. When I attempt to
debug any stored procedure inside any database, I have the following
happend:
Debug Window footer message indicates Waiting for Input. The system
stalls for 3-5 seconds and returns this message:
SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The process number varies.
I have followed the information outlined in this MS article but this
has not resolved the problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;270061
What version and SP is your SQL Server?
Also, how many applications are currently running in the same machine?
"etm1109@.gmail.com" wrote:

> My database mysteriously took a nosedive yesterday. When I attempt to
> debug any stored procedure inside any database, I have the following
> happend:
> Debug Window footer message indicates Waiting for Input. The system
> stalls for 3-5 seconds and returns this message:
> SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> The process number varies.
> I have followed the information outlined in this MS article but this
> has not resolved the problem.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;270061
>

fatal exception c0000005

My database mysteriously took a nosedive yesterday. When I attempt to
debug any stored procedure inside any database, I have the following
happend:
Debug Window footer message indicates Waiting for Input. The system
stalls for 3-5 seconds and returns this message:
SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The process number varies.
I have followed the information outlined in this MS article but this
has not resolved the problem.
http://support.microsoft.com/defaul...kb;en-us;270061What version and SP is your SQL Server?
Also, how many applications are currently running in the same machine?
"etm1109@.gmail.com" wrote:

> My database mysteriously took a nosedive yesterday. When I attempt to
> debug any stored procedure inside any database, I have the following
> happend:
> Debug Window footer message indicates Waiting for Input. The system
> stalls for 3-5 seconds and returns this message:
> SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> The process number varies.
> I have followed the information outlined in this MS article but this
> has not resolved the problem.
> http://support.microsoft.com/defaul...kb;en-us;270061
>

fastest way to recieve results?

Hi,
I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
can return 1-4 rows. After extensive reading and overwhelming myself with
help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
to optimize my software performance. I want to do only one round trip to
the server which will include a request to the server and its response
should be all 4 rows of data as a block.
some help files say for read-only-Fast-forward cursors wit ha small result
set, client mode with auto-fetch option is the best way. other help files
say odbc client mode library cursors are slower and way more memory
intensive than native SQL server libraries so caution should be used. what
should i do?
currently i use:
SQLSetConnectAttr(*hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)
SQL_CUR_USE_ODBC,0); ////odbc test
SQLDriverConnect(...)
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (PTR)sizeof(SQLARRAY), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (PTR) 4, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)RowStatusArray,
0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);
is this optimized?
LeonLeon McCalla wrote:
> Hi,
> I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
> can return 1-4 rows. After extensive reading and overwhelming myself with
> help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
> to optimize my software performance. I want to do only one round trip to
> the server which will include a request to the server and its response
> should be all 4 rows of data as a block.
Why? What do you have that is so time critical?
Aaron|||I'm working on a calling card application. millions of callingcards are
already on the street and sometimes i get 10-20 requests per second.
PS i already solver the problem.
Leon
"Aaron Lawrence" <aaronlNOT@.HEREintegration.co.nz> wrote in message
news:eu5N0MZ$GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Leon McCalla wrote:
proc[vbcol=seagreen]
with[vbcol=seagreen]
want[vbcol=seagreen]
to[vbcol=seagreen]
> Why? What do you have that is so time critical?
> Aaron
>