database return issue

Hello all!

I have a stored procedure that I want to return a value to a C# varaiable:

Code:

public decimal GetSiloLevelForDate(string plantId, DateTime date)
{
 decimal total = 0.0M;
 Open();
 SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@.plantId", plantId);
 cmd.Parameters.Add("@.date", date);
 SqlDataReader reader = cmd.ExecuteReader();
 if (reader.Read())
 {
 if (!reader.IsDBNull(0))
 total = reader.GetDecimal(0);
 }
 reader.Close();
 Close();
 return total;
}

this would normally work just fine. However, it is not becuase the actual SP's end statement is:

return (select @.tempTotal)

which should return a value. But it doesnt... if I run this SQL query:

declare @.usedTonnes numeric(13,2)
exec @.usedTonnes = dbo.getSiloLevelForDate ' 11', @.date

@.usedTonnes is a value, its 7096...

So why doesnt the C# return a value?

(so, the reader is not reading anything)Return values from stored procedures are typically used to indicatesuccess or failure, not to communicate data. A resultset or anoutput parameter is better/typically suited for this task.

The way I see it, you have 3 options:

1. Change your C# code
The way to access the return value is via a parameter with ParameterDirection = ReturnValue. You should beperforming an ExecuteNonQuery, and capturing this parameter. The datareader is overkill for what you are doing.

2. Change your stored procedure
instead of :
return (select @.tempTotal)
use:
select @.tempTotal
return
This would allow you to continue to use the datareader.

3. Change your C# code AND your stored procedure code
The way I'd suggest would be to change @.usedTonnes to an outputparameter in your stored procedure instead of a variable. In yourcode, you should beperforming an ExecuteNonQuery, and capturing this outputparameter with a parameter whose ParameterDirection = Output.

SeeInput and Output Parameters, and Return Values for more background information.|||

return(SELECT @.tempTotal) isn't correct.

The return from a stored procedure is a int. You are trying to passback a resultset through the return statement.

In addition your code is looking for a resultset not passed back from return.

Change your stored proc to end in:

SELECT @.tempTotal

return

and you won't have to change your C# code.

|||thanks for the tips guys. I did use the direction method to solve the issue:

SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);

SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

total = (int)param.Value;

worked fine, and the SQL was

if( @.tempTotal is not null) begin
return @.tempTotal
end
else begin
set @.tempTotal = 0
return @.tempTotal
end

and i got the correct result|||I did end up having to use the direction method of the SqlCommand object:

SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);

SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

total = (int)param.Value;

Database Resoration Error

Hi
I am getting the below error message when I restore the database.
Error Message
==========
Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
Could not adjust the replication state of database 'db_sss'. The database wa
s successfully
restored, however its replication state is inderminate. See the Troubleshoot
ing Replication secion in SQL Server Books Online.
RESTORE DATABASE successfully processed 10201 pages in 38.883 secounds(2.466
MB/sec
SQL Server Edition
============
MSDE with SP3
Operating System
============
Windows 2000 ProfessionalStupid question, but did you go through the troubleshooting section, as per
the error message recommendation? If that didn't help, I recommend you post
to the replication group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Balaji Prabhu.T" <anonymous@.discussions.microsoft.com> wrote in message
news:36AF45C6-BB29-411B-A6AD-9ADF6599E830@.microsoft.com...
> Hi
> I am getting the below error message when I restore the database.
> Error Message
> ==========
> Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
> Could not adjust the replication state of database 'db_sss'. The database
was successfully
> restored, however its replication state is inderminate. See the
Troubleshooting Replication secion in SQL Server Books Online.
> RESTORE DATABASE successfully processed 10201 pages in 38.883
secounds(2.466MB/sec
> SQL Server Edition
> ============
> MSDE with SP3
> Operating System
> ============
> Windows 2000 Professional
>

Database Replication

I have to create a replication for a database thar have 7 Gb. The time
that consume is very important: almost 7 hs. Exist a procedure more
efficient?
How can I generate a replication restoring a backup, instead of make an
snapshop (like Informix).
Thanks in advance
In addition to what Paul said: when I generate my snapshots I use the
"Compress the snapshot..." option.
These snapshots are considerably smaller than a normal backup.
Jim.
"cbaffigi@.apsf.com.ar" wrote:

> I have to create a replication for a database thar have 7 Gb. The time
> that consume is very important: almost 7 hs. Exist a procedure more
> efficient?
> How can I generate a replication restoring a backup, instead of make an
> snapshop (like Informix).
>
> Thanks in advance
>
|||Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
should compress to <2GB but as the size increases you might have to consider
an alternative - WinZip or WinRar etc.
Rgds,
Paul Ibison
|||are you sure you aren't thinking of the msf files involved in subscription
copy? There was a 2 Gig limit on zip files some time ago, but I believe this
has changed now.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
> should compress to <2GB but as the size increases you might have to
consider
> an alternative - WinZip or WinRar etc.
> Rgds,
> Paul Ibison
>
|||I am afraid Paul is right with regard to the 2gig (overall compressed, per
uncompressed file) limitations in snapshot compression here. These
limitations partly came from our use of the Cabinet API and the fact that we
limit ourselves to a single cab file. The snapshot compression feature was
originally intended for folks trying to transfer a snapshot over (relatively
speaking) slow WAN and 2gig seemed plenty for that at the time. And if there
is a need to transfer a much larger amount of data, the recommendation is to
do what Paul described below.
I have pretty good idea of how we can retrofit the snapshot compression
feature to get around the 2gig limits and make it generally more pleasant to
use in terms of temporary storage requirements, but it is not easy to do and
will likely involve the use of a different set of compression apis other
than the cabinet API thereby causing backward compatibility breakages with
subscribers using existing version of replication binaries.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:efJEW139EHA.1452@.TK2MSFTNGP11.phx.gbl...
> are you sure you aren't thinking of the msf files involved in subscription
> copy? There was a 2 Gig limit on zip files some time ago, but I believe
this
> has changed now.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> consider
>
|||Raymond,
hopefully you're still monitoring this thread and can answer a related
question for me. If the compression option is used in an alternative
snapshot location, and there isn't the default location selected, I was
trying to find out where is the cab file created during processing? The
directory seems to be immediately created, but there is no CAB file there
during the initial processing and outputting of data - is it all done in
memory, or is there a temp file held in another path? I ask because on some
servers we have severe space issues and I have had to change the WinZip temp
directory several times because of a similar issue.
Rgds,
Paul Ibison
|||Hi Paul,
By default, the cabinet api will build the cab file in the temp directory
and will only copy the resulting cab to the "real" location at the end. This
should explain the disk space problems that you were seeing. Knowing what I
know now, I probably would have implemented the snapshot compression feature
differently but then again, we really didn't (and still don't) have time to
do it properly.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23I2NrZB%23EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Raymond,
> hopefully you're still monitoring this thread and can answer a related
> question for me. If the compression option is used in an alternative
> snapshot location, and there isn't the default location selected, I was
> trying to find out where is the cab file created during processing? The
> directory seems to be immediately created, but there is no CAB file there
> during the initial processing and outputting of data - is it all done in
> memory, or is there a temp file held in another path? I ask because on
some
> servers we have severe space issues and I have had to change the WinZip
temp
> directory several times because of a similar issue.
> Rgds,
> Paul Ibison
>
|||Thanks Raymond.
Rgds,
Paul

database recovery testing - procedure

how frequent you guy verify database backup from tape ?
What is the procedure? do you restore from tape and attach to database
Cheers,
Hi
Have not played long time with tapes, but you can simple restore the
database to verify a file, very good practice.
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
> how frequent you guy verify database backup from tape ?
> What is the procedure? do you restore from tape and attach to database
> Cheers,
|||soonyu,
As Uri says, restore is the best (really the only) way to test if a backup
is good. You can restore the backup to another database name, so the
restore will not overwrite your working database.
RESTORE DATABASE TestRestoreDB FROM TAPE ...
RLF
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
> how frequent you guy verify database backup from tape ?
> What is the procedure? do you restore from tape and attach to database
> Cheers,
|||how frequent you do restoration testing per year
and what do you document in your document ?
Cheers,
soonyu
"Russell Fields" wrote:

> soonyu,
> As Uri says, restore is the best (really the only) way to test if a backup
> is good. You can restore the backup to another database name, so the
> restore will not overwrite your working database.
> RESTORE DATABASE TestRestoreDB FROM TAPE ...
> RLF
> "soonyu" <soonyu@.discussions.microsoft.com> wrote in message
> news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
>
>
|||soonyu,
I do restores all the time as a part of the regular work, to make test
copies of the database, etc. so my backups are tested frequently because of
that. Of course, you should monitor the backup jobs daily to ensure that
they are running successfully.
I have heard it recommended (and think it is a good idea) to restore some
databases every week. Rotate through your databases and servers from week
to week.
As far as documentation, a spreadsheet is fine to tick off the last test and
success or failure. (It only gets interesting when there is a failure, of
course.) I use a workflow system to track problems and resolutions.
RLF
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:46C15702-8FEA-4221-9778-48634D3F1769@.microsoft.com...[vbcol=seagreen]
> how frequent you do restoration testing per year
> and what do you document in your document ?
> Cheers,
> soonyu
> "Russell Fields" wrote:

database recovery testing - procedure

how frequent you guy verify database backup from tape ?
What is the procedure? do you restore from tape and attach to database
Cheers,Hi
Have not played long time with tapes, but you can simple restore the
database to verify a file, very good practice.
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
> how frequent you guy verify database backup from tape ?
> What is the procedure? do you restore from tape and attach to database
> Cheers,|||soonyu,
As Uri says, restore is the best (really the only) way to test if a backup
is good. You can restore the backup to another database name, so the
restore will not overwrite your working database.
RESTORE DATABASE TestRestoreDB FROM TAPE ...
RLF
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
> how frequent you guy verify database backup from tape ?
> What is the procedure? do you restore from tape and attach to database
> Cheers,|||how frequent you do restoration testing per year
and what do you document in your document ?
Cheers,
soonyu
"Russell Fields" wrote:
> soonyu,
> As Uri says, restore is the best (really the only) way to test if a backup
> is good. You can restore the backup to another database name, so the
> restore will not overwrite your working database.
> RESTORE DATABASE TestRestoreDB FROM TAPE ...
> RLF
> "soonyu" <soonyu@.discussions.microsoft.com> wrote in message
> news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
> > how frequent you guy verify database backup from tape ?
> > What is the procedure? do you restore from tape and attach to database
> >
> > Cheers,
>
>|||soonyu,
I do restores all the time as a part of the regular work, to make test
copies of the database, etc. so my backups are tested frequently because of
that. Of course, you should monitor the backup jobs daily to ensure that
they are running successfully.
I have heard it recommended (and think it is a good idea) to restore some
databases every week. Rotate through your databases and servers from week
to week.
As far as documentation, a spreadsheet is fine to tick off the last test and
success or failure. (It only gets interesting when there is a failure, of
course.) I use a workflow system to track problems and resolutions.
RLF
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:46C15702-8FEA-4221-9778-48634D3F1769@.microsoft.com...
> how frequent you do restoration testing per year
> and what do you document in your document ?
> Cheers,
> soonyu
> "Russell Fields" wrote:
>> soonyu,
>> As Uri says, restore is the best (really the only) way to test if a
>> backup
>> is good. You can restore the backup to another database name, so the
>> restore will not overwrite your working database.
>> RESTORE DATABASE TestRestoreDB FROM TAPE ...
>> RLF
>> "soonyu" <soonyu@.discussions.microsoft.com> wrote in message
>> news:78EAC7A2-D57F-4894-AE12-E1951E27CE76@.microsoft.com...
>> > how frequent you guy verify database backup from tape ?
>> > What is the procedure? do you restore from tape and attach to database
>> >
>> > Cheers,
>>

database performance management

Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.

please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.

As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.

It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.

At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.

Database Partitioning - Partition switching

Hi,

I need replies immediately...

This is with regard to switching of partitions in a sliding window scenario.

I wrote a stored procedure which does the partition switching in a sliding window scenario. During one switching, if the stored procedure is half executed, if I re-execute the same procedure it throws errors like

'Filegroup is in a different partition switching failed'

So how to rollback all the transactions in the stored procedure that does switching of partitions? If it is not possible, then how to identify whether a file group is in its original partition before the stored procedure is part executed? How to bring it back to its original partition before executing the stored procedure again switching?

Can any one help me immediately...

Thanks

Sekharpc

Moving thread to appropriate forum.|||

hi sek,

you have to enclose your sp scripts in a begin transaction.... end transaction clause..

create sp swtichpartion

(

)

as

begin transaction

partition switch... code1

partition switch code 2.

partition switch code 3.

commit transaction

in this way all switch task must commit as a unit. a failure in one job

fails the entire operation.

for a more comprehensive solution you can also incorporate the 2005

error handling feature try.. catch block for more information

you can consult my blogs look for the topic

The SQL SERVER 2005 Try Catch Block look for sub topic

"try catch with transaction"

or BOL

regards,

joey

Database Output Problem for a SQL Server 2000 stored procedure

Hi,

I have a problem with "database output" window in executing a select-sp in vs 2005 standard. The db is a sql server 2000 one.

When I execute the sp within VS, the database output correctly displays the execution information:

No rows affected.
(1 row(s) returned)
@.RETURN_VALUE = 3

but I can't see the returned rows (3 rows are returned); I only see the column names and no data.

Running [dbo].[spBkm_GetList] ( @.IDUser = <DEFAULT>, [.......]).
IDBkm UIBkm IDUser
------- ------------ ---No rows affected.

If I run the same sp in Sql Server Manager Studio Express it correctly shows the data of the 3 rows returned.

The sp uses

EXECsp_executesql @.Sql, @.ParamList, .....

for executing the sql statement and the last line of sp is

RETURN@.@.ROWCOUNT

I've tried removing the "RETURN @.@.ROWCOUNT" with no success.

The problem affects only one sp, the others, which are absolutely similar, work properly .

I don't know what is the problem...

Any idea?

Thanks in advance

Ive' resolved the problem in a strange way, it seems to me a bug...

If I exclude from the select statement a field of type uniqueidentifier, alla data are correctly displayed!

I've tried on other sp and the behaviour is the same.

Hope this may help someone else with the same problem...

Database option - Pls help.

Hi all,
I wonder whether is there any system store procedure or any way for me to
display the database option ? I know I can use the MS SQL Server Management
Studio to view it but I want to use T-SQL statement.
Using sp_dboption I can see all database settable options but I can't see
the value, whether is it currently enable or disable ?
Thank you for your help! :D
Cheers,
Meng Soon Chua
> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
To see the options that are currently set, pass the database name:
EXEC sp_dboption 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> Hi all,
> I wonder whether is there any system store procedure or any way for me to
> display the database option ? I know I can use the MS SQL Server
> Management
> Studio to view it but I want to use T-SQL statement.
> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
> Thank you for your help! :D
> Cheers,
> Meng Soon Chua
|||Also note that sp_dboption was replaced with ALTER DATABASE in 2000. IF you are on 2005, I suggest
you do:
SELECT * FROM sys.databases WHERE name = 'Adventureworks'
If you are on 2000, I suggest you use the DATABASEPROPERTYEX() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:850B4CAE-ACF2-4BAB-9585-9915E4644B4F@.microsoft.com...
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>
|||Hi Dan,
Thanks for the response.
Hey ! It does show those option that is turned on. But another question ;-)
I have checked the database (by using MS SQL Studio)
ANSI NULL Default -> False
ANSI NULLS Enabled -> False
Then next I start a 'New Query'
For this connection I write the below statement
USE Pubs
go
SET ANSI_NULLS ON
go
EXEC SP_DBOPTION 'Pubs'
The result display;
autoclose
auto create statistics
auto update statistics
Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought I
turn it on for this connection ? Am I doing something wrong ? I just want to
double check whether I really turn on ANSI_NULLS :-| ...
Please help ? Many thanks ...
"Dan Guzman" wrote:

> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>
|||> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
First, let me make it clear these settings are unrelated. "ANSI NULL
Default" setting affects default nullability of new columns and only when
both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
statements so that you don't need to bother with that option.
The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
a legacy application that can't be changed.

> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
Connection settings override the database default setting. When you check
the database options with sp_dboption (or DATABASEPROPEREX as Tibor
suggested), you are viewing only the database default setting for all
connections. You can execute DBCC USEROPTIONS to view the current
connection active settings.
Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
automatically when you connect so there is usually no need to check these
manually. This also means that the related database setting defaults will
have no affect when ODBC and OLEDB clients connect.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Thanks for the response.
> Hey ! It does show those option that is turned on. But another question
> ;-)
> I have checked the database (by using MS SQL Studio)
> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
> Then next I start a 'New Query'
> For this connection I write the below statement
> USE Pubs
> go
> SET ANSI_NULLS ON
> go
> EXEC SP_DBOPTION 'Pubs'
> --
> The result display;
> autoclose
> auto create statistics
> auto update statistics
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
> Please help ? Many thanks ...
>
> "Dan Guzman" wrote:
|||Hi ...
Thanks a lot guys. It does answer my question ;-)
"Dan Guzman" wrote:

> First, let me make it clear these settings are unrelated. "ANSI NULL
> Default" setting affects default nullability of new columns and only when
> both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
> Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
> statements so that you don't need to bother with that option.
> The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
> evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
> a legacy application that can't be changed.
>
> Connection settings override the database default setting. When you check
> the database options with sp_dboption (or DATABASEPROPEREX as Tibor
> suggested), you are viewing only the database default setting for all
> connections. You can execute DBCC USEROPTIONS to view the current
> connection active settings.
> Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
> automatically when you connect so there is usually no need to check these
> manually. This also means that the related database setting defaults will
> have no affect when ODBC and OLEDB clients connect.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
>

Database option - Pls help.

Hi all,
I wonder whether is there any system store procedure or any way for me to
display the database option ? I know I can use the MS SQL Server Management
Studio to view it but I want to use T-SQL statement.
Using sp_dboption I can see all database settable options but I can't see
the value, whether is it currently enable or disable ?
Thank you for your help! :D
Cheers,
Meng Soon Chua> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
To see the options that are currently set, pass the database name:
EXEC sp_dboption 'MyDatabase'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> Hi all,
> I wonder whether is there any system store procedure or any way for me to
> display the database option ? I know I can use the MS SQL Server
> Management
> Studio to view it but I want to use T-SQL statement.
> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
> Thank you for your help! :D
> Cheers,
> Meng Soon Chua|||Also note that sp_dboption was replaced with ALTER DATABASE in 2000. IF you
are on 2005, I suggest
you do:
SELECT * FROM sys.databases WHERE name = 'Adventureworks'
If you are on 2000, I suggest you use the DATABASEPROPERTYEX() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:850B4CAE-ACF2-4BAB-9585-9915E4644B4F@.microsoft.com...
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>|||Hi Dan,
Thanks for the response.
Hey ! It does show those option that is turned on. But another question ;-)
I have checked the database (by using MS SQL Studio)
ANSI NULL Default -> False
ANSI NULLS Enabled -> False
Then next I start a 'New Query'
For this connection I write the below statement
USE Pubs
go
SET ANSI_NULLS ON
go
EXEC SP_DBOPTION 'Pubs'
The result display;
autoclose
auto create statistics
auto update statistics
Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought I
turn it on for this connection ? Am I doing something wrong ? I just want to
double check whether I really turn on ANSI_NULLS :-| ...
Please help ? Many thanks ...
"Dan Guzman" wrote:

> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>|||> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
First, let me make it clear these settings are unrelated. "ANSI NULL
Default" setting affects default nullability of new columns and only when
both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
statements so that you don't need to bother with that option.
The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
a legacy application that can't be changed.

> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
Connection settings override the database default setting. When you check
the database options with sp_dboption (or DATABASEPROPEREX as Tibor
suggested), you are viewing only the database default setting for all
connections. You can execute DBCC USEROPTIONS to view the current
connection active settings.
Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
automatically when you connect so there is usually no need to check these
manually. This also means that the related database setting defaults will
have no affect when ODBC and OLEDB clients connect.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Thanks for the response.
> Hey ! It does show those option that is turned on. But another question
> ;-)
> I have checked the database (by using MS SQL Studio)
> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
> Then next I start a 'New Query'
> For this connection I write the below statement
> USE Pubs
> go
> SET ANSI_NULLS ON
> go
> EXEC SP_DBOPTION 'Pubs'
> --
> The result display;
> autoclose
> auto create statistics
> auto update statistics
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
> Please help ? Many thanks ...
>
> "Dan Guzman" wrote:
>|||Hi ...
Thanks a lot guys. It does answer my question ;-)
"Dan Guzman" wrote:

> First, let me make it clear these settings are unrelated. "ANSI NULL
> Default" setting affects default nullability of new columns and only when
> both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
> Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
> statements so that you don't need to bother with that option.
> The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
> evaluated. The Best Practice is to always keep ANSI_NULL ON unless you ha
ve
> a legacy application that can't be changed.
>
> Connection settings override the database default setting. When you check
> the database options with sp_dboption (or DATABASEPROPEREX as Tibor
> suggested), you are viewing only the database default setting for all
> connections. You can execute DBCC USEROPTIONS to view the current
> connection active settings.
> Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
> automatically when you connect so there is usually no need to check these
> manually. This also means that the related database setting defaults will
> have no affect when ODBC and OLEDB clients connect.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
>

Database option - Pls help.

Hi all,
I wonder whether is there any system store procedure or any way for me to
display the database option ? I know I can use the MS SQL Server Management
Studio to view it but I want to use T-SQL statement.
Using sp_dboption I can see all database settable options but I can't see
the value, whether is it currently enable or disable ?
Thank you for your help! :D
Cheers,
Meng Soon Chua> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
To see the options that are currently set, pass the database name:
EXEC sp_dboption 'MyDatabase'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> Hi all,
> I wonder whether is there any system store procedure or any way for me to
> display the database option ? I know I can use the MS SQL Server
> Management
> Studio to view it but I want to use T-SQL statement.
> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
> Thank you for your help! :D
> Cheers,
> Meng Soon Chua|||Also note that sp_dboption was replaced with ALTER DATABASE in 2000. IF you are on 2005, I suggest
you do:
SELECT * FROM sys.databases WHERE name = 'Adventureworks'
If you are on 2000, I suggest you use the DATABASEPROPERTYEX() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:850B4CAE-ACF2-4BAB-9585-9915E4644B4F@.microsoft.com...
>> Using sp_dboption I can see all database settable options but I can't see
>> the value, whether is it currently enable or disable ?
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>> Hi all,
>> I wonder whether is there any system store procedure or any way for me to
>> display the database option ? I know I can use the MS SQL Server Management
>> Studio to view it but I want to use T-SQL statement.
>> Using sp_dboption I can see all database settable options but I can't see
>> the value, whether is it currently enable or disable ?
>> Thank you for your help! :D
>> Cheers,
>> Meng Soon Chua
>|||Hi Dan,
Thanks for the response.
Hey ! It does show those option that is turned on. But another question ;-)
I have checked the database (by using MS SQL Studio)
ANSI NULL Default -> False
ANSI NULLS Enabled -> False
Then next I start a 'New Query'
For this connection I write the below statement
USE Pubs
go
SET ANSI_NULLS ON
go
EXEC SP_DBOPTION 'Pubs'
--
The result display;
autoclose
auto create statistics
auto update statistics
Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought I
turn it on for this connection ? Am I doing something wrong ? I just want to
double check whether I really turn on ANSI_NULLS :-| ...
Please help ? Many thanks ...
"Dan Guzman" wrote:
> > Using sp_dboption I can see all database settable options but I can't see
> > the value, whether is it currently enable or disable ?
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> > Hi all,
> >
> > I wonder whether is there any system store procedure or any way for me to
> > display the database option ? I know I can use the MS SQL Server
> > Management
> > Studio to view it but I want to use T-SQL statement.
> > Using sp_dboption I can see all database settable options but I can't see
> > the value, whether is it currently enable or disable ?
> >
> > Thank you for your help! :D
> >
> > Cheers,
> > Meng Soon Chua
>|||> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
First, let me make it clear these settings are unrelated. "ANSI NULL
Default" setting affects default nullability of new columns and only when
both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
statements so that you don't need to bother with that option.
The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
a legacy application that can't be changed.
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
Connection settings override the database default setting. When you check
the database options with sp_dboption (or DATABASEPROPEREX as Tibor
suggested), you are viewing only the database default setting for all
connections. You can execute DBCC USEROPTIONS to view the current
connection active settings.
Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
automatically when you connect so there is usually no need to check these
manually. This also means that the related database setting defaults will
have no affect when ODBC and OLEDB clients connect.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
> Hi Dan,
> Thanks for the response.
> Hey ! It does show those option that is turned on. But another question
> ;-)
> I have checked the database (by using MS SQL Studio)
> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
> Then next I start a 'New Query'
> For this connection I write the below statement
> USE Pubs
> go
> SET ANSI_NULLS ON
> go
> EXEC SP_DBOPTION 'Pubs'
> --
> The result display;
> autoclose
> auto create statistics
> auto update statistics
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
> Please help ? Many thanks ...
>
> "Dan Guzman" wrote:
>> > Using sp_dboption I can see all database settable options but I can't
>> > see
>> > the value, whether is it currently enable or disable ?
>> To see the options that are currently set, pass the database name:
>> EXEC sp_dboption 'MyDatabase'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in
>> message
>> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>> > Hi all,
>> >
>> > I wonder whether is there any system store procedure or any way for me
>> > to
>> > display the database option ? I know I can use the MS SQL Server
>> > Management
>> > Studio to view it but I want to use T-SQL statement.
>> > Using sp_dboption I can see all database settable options but I can't
>> > see
>> > the value, whether is it currently enable or disable ?
>> >
>> > Thank you for your help! :D
>> >
>> > Cheers,
>> > Meng Soon Chua|||Hi ...
Thanks a lot guys. It does answer my question ;-)
"Dan Guzman" wrote:
> > ANSI NULL Default -> False
> > ANSI NULLS Enabled -> False
> First, let me make it clear these settings are unrelated. "ANSI NULL
> Default" setting affects default nullability of new columns and only when
> both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
> Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
> statements so that you don't need to bother with that option.
> The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
> evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
> a legacy application that can't be changed.
> > Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> > I
> > turn it on for this connection ? Am I doing something wrong ? I just want
> > to
> > double check whether I really turn on ANSI_NULLS :-| ...
> Connection settings override the database default setting. When you check
> the database options with sp_dboption (or DATABASEPROPEREX as Tibor
> suggested), you are viewing only the database default setting for all
> connections. You can execute DBCC USEROPTIONS to view the current
> connection active settings.
> Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
> automatically when you connect so there is usually no need to check these
> manually. This also means that the related database setting defaults will
> have no affect when ODBC and OLEDB clients connect.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
> > Hi Dan,
> >
> > Thanks for the response.
> > Hey ! It does show those option that is turned on. But another question
> > ;-)
> >
> > I have checked the database (by using MS SQL Studio)
> >
> > ANSI NULL Default -> False
> > ANSI NULLS Enabled -> False
> >
> > Then next I start a 'New Query'
> > For this connection I write the below statement
> > USE Pubs
> > go
> > SET ANSI_NULLS ON
> > go
> > EXEC SP_DBOPTION 'Pubs'
> >
> > --
> > The result display;
> >
> > autoclose
> > auto create statistics
> > auto update statistics
> >
> > Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> > I
> > turn it on for this connection ? Am I doing something wrong ? I just want
> > to
> > double check whether I really turn on ANSI_NULLS :-| ...
> >
> > Please help ? Many thanks ...
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > Using sp_dboption I can see all database settable options but I can't
> >> > see
> >> > the value, whether is it currently enable or disable ?
> >>
> >> To see the options that are currently set, pass the database name:
> >>
> >> EXEC sp_dboption 'MyDatabase'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in
> >> message
> >> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> >> > Hi all,
> >> >
> >> > I wonder whether is there any system store procedure or any way for me
> >> > to
> >> > display the database option ? I know I can use the MS SQL Server
> >> > Management
> >> > Studio to view it but I want to use T-SQL statement.
> >> > Using sp_dboption I can see all database settable options but I can't
> >> > see
> >> > the value, whether is it currently enable or disable ?
> >> >
> >> > Thank you for your help! :D
> >> >
> >> > Cheers,
> >> > Meng Soon Chua
> >>
>