Datagrid/Stored Procedure Issue

I have a developer that has databound datagrids as follows:
Datagrid.datasource= DataSet (yes, the compiler allows this)
The dataset is returned from stored procedures. Each of the sps return
a single recordset, but periodically we are seeing some
nondeterministic behavior where the code for the datasource above is
causing a runtime error. More than one recordset is being returned for
some reason.
Of course, the easiest way to fix this is to have the developer change
code to
Datagrid.datasource = DataSet.Table("Some Table or Index"),
but is there are anything that can be done from a SQL Server
standpoint, that will only return one recordset ?
TIAMJKulangara wrote:
> I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
The usual cause of extra resultsets is the lack of a "SET NOCOUNT ON"
statement in the stored procedure. This statement tells the query engine to
suppress the informational "x rows affected" messages that you see when
running queries in QA. These messages get sent as extra resultsets.
--
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.|||Thanks Bob. I checked each of the sps called and they all have "SET
NOCOUNT ON". The strange thing here is that we don't always get an
error. Most of the time (90%) applications rnus without a problem.|||"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1140627492.706939.31610@.z14g2000cwz.googlegroups.com...
>I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
Run the stored procedure in Query Analyser (with parameters that cause the
error in your page).
Check if any messages are generated.
I had a similar problem with a stored procedure and found that it was
because of null values in aggregate functions.
Adding SET ANSI_WARNINGS OFF to the procedure solved the problem.
Note: make sure ANSI_WARNINGS is ON in QA options before doing the test.

Databases show in EM but not in sysdatabases?

I'm managing the back end SQL 2000 instance for a vendors application. The
application allows users to create databases on the fly for testing, etc.
I opened the instance in Enterprise Manager, and noted that the number of
databases had grown to about a dozen (other than the system databases). Hmm,
so being concerned about drive space, etc, I executed a sp_helpdb.
Yikes..only one of the dozen user databases showed in the listing for
sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
But in EM I can open any of the other 11 DB's, open tables from them, view
options, etc etc. So I'm just curious, how can I view them in EM but they
don't show in sysdatabases?
TIA
<*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
"Tibor Karaszi" wrote:

> My guess is that you are connecting to different SQL Server instances.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
> news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
>

Databases show in EM but not in sysdatabases?

I'm managing the back end SQL 2000 instance for a vendors application. The
application allows users to create databases on the fly for testing, etc.
I opened the instance in Enterprise Manager, and noted that the number of
databases had grown to about a dozen (other than the system databases). Hmm
,
so being concerned about drive space, etc, I executed a sp_helpdb.
Yikes..only one of the dozen user databases showed in the listing for
sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
But in EM I can open any of the other 11 DB's, open tables from them, view
options, etc etc. So I'm just curious, how can I view them in EM but they
don't show in sysdatabases?
TIAMy guess is that you are connecting to different SQL Server instances.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
> I'm managing the back end SQL 2000 instance for a vendors application. Th
e
> application allows users to create databases on the fly for testing, etc.
> I opened the instance in Enterprise Manager, and noted that the number of
> databases had grown to about a dozen (other than the system databases). H
mm,
> so being concerned about drive space, etc, I executed a sp_helpdb.
> Yikes..only one of the dozen user databases showed in the listing for
> sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
> But in EM I can open any of the other 11 DB's, open tables from them, view
> options, etc etc. So I'm just curious, how can I view them in EM but they
> don't show in sysdatabases?
> TIA|||<*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
"Tibor Karaszi" wrote:

> My guess is that you are connecting to different SQL Server instances.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
> news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
>|||Happens to all of us... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
news:DBD746A2-59BE-49C8-90A9-757086DA9412@.microsoft.com...[vbcol=seagreen]
> <*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
> "Tibor Karaszi" wrote:
>

Database triggers - SQL Server - Fields only allowed if listed in another field in another

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).

eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.

Does anyone know the easy way to do this? / Syntax for the trigger?

Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:

Creating and Modifying FOREIGN KEY Constraints

|||

I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)

My statement in('Yes','No','') does not work for my check constraint.

Does anyone know what my syntax would be?

|||A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))

--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')

For mor information, you can refer to:?ALTER?TABLE?