.Net 2.0 and datetime

Hi,

Here's my problem.

So using a stored proc and some parameters I want to update a datetime field

So I define @.theDate as datetime in my stored proc

in my vb.net app I have

dbcmd.Parameters.AddWithValue("@.theDate", SelectedDate)

where SelectedDate is

Dim SelectedDate as Nullable(of Datetime)

Now if SelectDate is a value all is good with the world.

However if SelectDate is Null I get an error.

How do I pass a null date to the stored proc ?

You need to add code like this:

dbcmd.Parameters.AddWithValue("@.theDate", (SelectedDate==null) ? DbNull.Value : SelectedDate)

DbNull.Value is a null for the database.

**Restore error**

Hi
I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L1
should backup and restore (restoring as new db too) it's databes ,so I set
the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in db1
and 'db_owner' in master too.now it can backup the database successfully but
when it wants to restore the db as a new one at the end of progressing the
blue bar of restoring following error appearred :
"Server user 'L1' is not a valid user in database 'db1' ,restore db is
terminated abnormally."
any help would be thakful.Hi
You don't say if these are on the same server! But this may help
http://support.microsoft.com/defaul...kb;en-us;240872
John
"M R" wrote:

> Hi
> I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L
1
> should backup and restore (restoring as new db too) it's databes ,so I set
> the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in d
b1
> and 'db_owner' in master too.now it can backup the database successfully b
ut
> when it wants to restore the db as a new one at the end of progressing the
> blue bar of restoring following error appearred :
> "Server user 'L1' is not a valid user in database 'db1' ,restore db is
> terminated abnormally."
> any help would be thakful.
>
>|||These are on the same server,
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:714B1D9E-A21F-4850-AFE6-8A85137E05DD@.microsoft.com...
> Hi
> You don't say if these are on the same server! But this may help
> http://support.microsoft.com/defaul...kb;en-us;240872
> John
> "M R" wrote:
>
.L1
set
db1
but
the|||Hi
If you change the owner of the database before backing up do you have
the same problem?
You may want to try dropping the user/login and re-create them.
John

**defining public function**

Hi
I'm working with SQL2000,and I want to know how can I define a public
function to use in each database ?
(I know I can define a function and call it by refering to its database.)
but I would like to know if there is another way .
I would be grateful if somebody help me.
ThanksR-M <R> wrote in news:opszjo79gkmw7tkz@.system109.parskhazar.net:

> I'm working with SQL2000,and I want to know how can I define a public
> function to use in each database ?
> (I know I can define a function and call it by refering to its database.)
> but I would like to know if there is another way .
I've heard that creating a function named sp_<something> in the master
database will do just that, but I've not tried it myself yet.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I doubt that you can do that with UDF
Take a look at below script to do what you wanted
use master
create table t(c1 varchar(50)) insert t values('master')
go
create proc sp_test as select * from t
GO
use northwind
create table t(c1 varchar(50)) insert t values('northwind')
use pubs
create table t(c1 varchar(50)) insert t values('pubs')
use pubs
exec sp_test --returns 'master'
use master
exec sp_MS_marksystemobject sp_test
use pubs
exec sp_test --returns 'pubs'
use northwind
exec sp_test --returns 'northwind'
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns97015645B6489olekristianbangaas@.
207.46.248.16...
> R-M <R> wrote in news:opszjo79gkmw7tkz@.system109.parskhazar.net:
>
> I've heard that creating a function named sp_<something> in the master
> database will do just that, but I've not tried it myself yet.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:ekfA9hr3FHA.2524@.TK2MSFTNGP10.phx.gbl:

> I doubt that you can do that with UDF
> Take a look at below script to do what you wanted
You're absolutely right, thanks for the correction. It works fine with
stored procedures, but not with UDFs. (Checked on SQL Server 2005 Sep CTP).
Does anyone know why you cannot do this with UDFs as well?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||I will not go into details, as this is not supported and not recommendable.
But it is doable with UDFs as well.
A hint: who is the owner of the system SPs, and who of the system UDFs?
Dejan Sarka
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns97015F4B16DAFolekristianbangaas@.
207.46.248.16...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:ekfA9hr3FHA.2524@.TK2MSFTNGP10.phx.gbl:
>
> You're absolutely right, thanks for the correction. It works fine with
> stored procedures, but not with UDFs. (Checked on SQL Server 2005 Sep
> CTP).
> Does anyone know why you cannot do this with UDFs as well?
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl:

> I will not go into details, as this is not supported and not
> recommendable. But it is doable with UDFs as well.
> A hint: who is the owner of the system SPs, and who of the system
> UDFs?
Thanks a lot Dejan :) It's MAY come handy one day.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi, Dejan
Yes, you are right ,it is not documented, I just wonderful does it exist in
SQL Server 2005?
> But it is doable with UDFs as well.
Can you provide some examples?
PS. I will be glad to see you in Israel in the next two ws.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl...
>I will not go into details, as this is not supported and not recommendable.
>But it is doable with UDFs as well.
> A hint: who is the owner of the system SPs, and who of the system UDFs?
> --
> Dejan Sarka
> "Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
> news:Xns97015F4B16DAFolekristianbangaas@.
207.46.248.16...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in news:uMvuJmt3FHA.3880
@.TK2MSFTNGP12.phx.gbl:

> Can you provide some examples?
Seems like MSDN is our friend today:
<URL:http://msdn.microsoft.com/library/d...rl=/library/en-
us/dnsqlpro01/html/sql01l1.asp>
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||> Yes, you are right ,it is not documented, I just wonderful does it exist
> in SQL Server 2005?
No, the tricks suggested in
http://msdn.microsoft.com/library/d... />
ql01l1.asp
will NOT work in 2005. If you're at all interested in migrating to SQL
Server 2005, I would recommend that you not try to implement this. You'll
just end up having to undo it when you get to 2005.
This is discussed in the Upgrade Advisor tool documentation in detail.
Here's the bulk of the topic:
In SQL Server 2005 system object storage and access has changed in the
following ways.
System objects are stored in the read-only Resource database and
direct system object updates are disallowed.
System objects logically appear in the sys schema of every database. This
maintains the ability to invoke system functions from any database by
specifying a one-part function name. For example, the statement SELECT *
FROM fn_helpcollations() can be run from any database.
The undocumented user system_function_schema has been removed.
The user ID associated with system_function_schema (UID = 4) is
reserved for the sys schema and is restricted to internal use only.
These changes have the following affect on user-defined system functions:
Data Definition Language (DDL) statements that reference
system_function_schema will fail. For example, the statement CREATE FUNCTION
system_function_schema.fn_MySystemFunction . will not succeed in
ssVersion2005.
After upgrading to SQL Server 2005, existing objects owned by
system_function_schema are contained only in the sys schema of the master
database. Because system objects cannot be modified, these functions can
never be altered or dropped from the master database. Furthermore, they
cannot be invoked from other databases by specifying only a one-part
function name.
BBefore you upgrade to SQL Server 2005, perform these operations:
1. Change the ownership of existing user-defined functions to dbo by
using the sp_changeobjectowner system stored procedure.
2. Consider renaming the function to not use the prefix 'fn_'. This
will avoid potential name conflicts with current or future system functions.
3. Place a copy of the modified functions in every database that uses
them.
4. Replace references to system_function_schema with dbo in all scripts
that contain user-defined function DDL statements.
5. Modify scripts that invoke these functions to use either the
two-part name dbo.function_name, or the three-part name
database_name.dbo.function_name.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMvuJmt3FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hi, Dejan
> Yes, you are right ,it is not documented, I just wonderful does it exist
> in SQL Server 2005?
> Can you provide some examples?
> PS. I will be glad to see you in Israel in the next two ws.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:egf7VFt3FHA.2816@.tk2msftngp13.phx.gbl...
>