Default Dates To UTC In SQL Server 2000

May 8, 2007

Hi all,

I hope this is the correct forum. I have SQL Server 2000 installed on a server located in the USA. The local time zone on the server is EST. Therefore getdate() returns EST time. I now realise that I'd like to be saving all my timestamps in UTC. While I could modify all my stored procedures to use getutcdate() rather than getdate() I wonder if there is a database level setting for time zone that will cause getdate() to return UTC date?

Any ideas?

Des

View 3 Replies

Why Date Columns Default To 1900 After Inserting Dates In SQL Server Everywhere.?

Sep 19, 2006

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.
Query:

Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO

ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
                    VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

----------- -------------- ----------------------- -----------------------

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

View 6 Replies View Related

Default Language And Dates

Jul 21, 2006

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting
"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .
I also ran
EXEC @ret = sp_defaultlanguage 'sa', 'British English'
as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

View 1 Replies View Related

Setting Default Dates In Reporting Services

Mar 26, 2008

Thought I'd help some folks with rs and dates..

here is some scalar valued functions:

CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
END

CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END

CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END

CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END

CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END

CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END

CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END

CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END

CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END

CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END

CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END

CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END

CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END

CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END

Then create a Table-Valued Function like so:

CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
year_end datetime,
tomorrow_noon datetime,
today_noon datetime,
date_only datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_year_end(@date) AS year_end,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,
dbo.get_date_only(@date) AS DateOnly
RETURN
END

Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:

CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end

Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:

Now go to the report parameters section of the report:

Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:

Now when you run the report it uses the scalars:

If you have questions feel free to ask :).

Hope this helps someone

View 2 Replies View Related

What Is The Default Page Size In Sql Server 2000....

Dec 7, 2000

What is the default page size in sql server 2000....?

View 1 Replies View Related

DEfault SQL Server 2000 Lock Modes

Jul 12, 2006

Hi everyone,
I wonder that SQL Server 2000 support the following lokc modes by default or not.

Shared (S)
Update (U)
Exclusive (X)

View 3 Replies View Related

No Default Entries In SQL Server 2000 Service Manager Options

Jul 20, 2005

ver: SQL-DMO Version: 8.00.760I have the option to automatically start SQL Server on startup, butwhen it does start up, the SQL Server Service Manager does not havethe green arrow indicating that it is active. When I double-click itto display the SQL Server Service Manager, it shows the name of theserver, but no services in the drop-down box.When I start Enterprise Manager I see an instance of MSDE wheresomeone installed an MSDE application. That is active. But I have to"connect" to the localhost. The instance name is the name of the MSDEapplication. I would like it to connect to the localhost on startup.Any ideas on how to correct this problem?Thanks,RBollinger

View 2 Replies View Related

How To Change The Default Backup Data Directory In SQL Server 2000?

Dec 29, 2006

Hi,

I had SQL Server 2000 installed like below:

Installation Directory = C:Program FilesMicrosoft SQL Server

Default data directory = D:

Default log directory = D:

I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup

However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.

My question is: How can I move the path of the backup directory?

I checked the registy but this is what I found:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer

..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)

..DefaultData=D:

Can somebody help please?

View 3 Replies View Related

Connection String For Default Instance Of SQL Server 2005, On A Box Also Running A Named Instance Of SQL Server 2000

Nov 15, 2007

The box I am trying to connect to is running two instances of SQL Server.
There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.
I can connect to the SQL Server 2000 instance no problem:<add key="ConnectionString" value="server=MYPCSQLSERVER;database=mydatabase;user id=****;password=****" />
However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:
<add key="ConnectionString" value="server=MYPC;database=mydatabase;user id=****;password=****" />
but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:
<add key="ConnectionString" value="server=MYPC,1434;database=mydatabase;user id=****;password=****" />
but this doesn't work either.

Am I mssing something here? Any help much appreciated
Thanks...

View 2 Replies View Related

Named Instances And Default Instances Of SQL Server 2000

Jul 18, 2001

I had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)

thanks

Fede

View 1 Replies View Related

SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It

Aug 11, 2015

@pvColumnName VARCHAR(100) = Default,

However, I am unable to determine what is the value for Default. Is it '' ?

Default is not permitted as a constant - below fails to parse:

WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

View 4 Replies View Related

Comparing Dates In MS SQL 2000 Query

Feb 8, 2008

Hi,
I have a table that temporarily stores information. Before I insert new data in the table, I want to delete the previous day's data.

This is the query I'm using
Code:

q="DELETE FROM SearchTemp WHERE theDate < "&Date()
set RS2 = DB.Execute(q, ra, adCmdText)

I should say I'm using ASP & VBScript.

Problem is, this isn't deleting the previous day's dates.

The dates are stored in this format: m/d/yyyy.

I've searched through old posts but none of the solutions offered have worked for me so far. Anyone have any suggestions?

Thanks!

View 4 Replies View Related

Descending Dates In Sql 2005 Vs. Sql 2000

Jun 18, 2007

I have been using Access 2003 as a front end to a SQL 2k database. Dates are sorted in a descending fashion. I recently upgraded the sql server database to 2005 and dates can no longer be sorted properly. I have set the database compatibility level to sql2000 within the sql2005 database engine but no luck. Any suggetions on how to fix this?

Thanks in advance.

View 1 Replies View Related

Problem With Inserting Or Updating Dates In Mssql 2000

Apr 12, 2006

Hello.I've read many topics about this problem but i couldn't figure it out.I use form where user must insert 2 dates using texboxes.-One is required and other is optional.Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report. Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?question 2. What to do if user left optional texbox date empty.I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)and parameters @date1, sqldbtype date =texbox1.text

View 2 Replies View Related

Diffrence In Handling Dates Between MSDE 2000 And SQL Express

Apr 27, 2007

I have a query that worked fine on MSDE 2000. The query evaluates a date using which is written between ''. Ex '1-1-1970'

Now I detached and attached the database to a SQL Express instance and ran the query but it fails with the error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

When I remove '' around the dates in the query, all works fine. Is this because of a diffrence between MSDE 2000 and SQL Express?

By the way. I am using the sql.net library which 'creates the query for me'.

View 3 Replies View Related

Default Schema SQL Svr Mgt Stdio / 2000

Sep 5, 2007

Hi,

I have SQL Server Management Studio and SQL Server 2000.
What I want to know is:

Is there any way to prevent displaying default schema or owner, when viwing the "tables list" in the object browser?

eg. Instead of
dbo.MyTable1
dbo.MyTable2
....

I want to view as

MyTable1
MyTable2

Thanks

Srinika

View 5 Replies View Related

Default Conflict Resolver In Sql 2000

Sep 20, 2005

Hi, I'm replicating a database between two instances of Sql 2000 using Merge Replication. I have no custom resolvers at present but I'm seeing something unexpected.

View 5 Replies View Related

Default Charset In Sqlserver 2000? / Jtds

Nov 2, 2005

Hi,I am writing to a text column in my SQL Server 2000 database. The textcomes from a web form in my java web application, where the characterencoding is ISO-8859-1. (I have no control over the charset, my app isa plugin inside another app.)Characters such as �(ascii 128) and '(ascii 146) are inserted intothe db as '?'.I'm connecting using the free jtds driver, and I'm not specifying anydetails about charsets in my usage of the driver.Can anyone tell me what the default charset in sqlserver 2000 is?Should I be specifying this charset when using my driver?Thanks.

View 4 Replies View Related

Installation To Default Folder For 2000 And 2005

Apr 29, 2008

Sql server 2000 was installed to a default folder C:Program FilesMicrosoft SQL Server

And sql server 2005 was installed to the same location C:Program FilesMicrosoft SQL Server

how do i know which files are related to 2000 and which are related to 2005

please let me know

View 1 Replies View Related

Creating A Database In MSSql 2000, But NOT In The Default Directory

Sep 28, 2002

I am trying to find a solution to a thorny problem I have encountered...

I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.

I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.

I am using an ADO connection and trying to execute it, as follows:

The text of the relevant part of the program goes like this:

Dim strCreateDatabase As String
Dim MyCn As ADODB.Connection

Set MyCn = New ADODB.Connection

With MyCn
.ConnectionTimeout = 25
.Provider = "sqloledb"
.Properties("Data Source").Value = "MOP"
.Properties("Initial Catalog").Value = ""
.Properties("Integrated Security").Value = "SSPI"
.Open
End With

strCreateDatabase = "Create Database Rimon on (" _
& "Name = Rimon, Filename = '\mopusersMR_DiazsalpmsdataRimon_Data.mdf', " _
& "Size = 20, MaxSize = 100, FileGrowth = 5) " _
& "Log on (Name = 'Rimon', Filename = 'c:usersMR_DiazsalpmsdataRimon_Log.ldf', " _
& "Size = 2, MaxSize = 10, FileGrowth = 1)"

MyCn.Execute strCreateDatabase

Both the name of the Sql Server and the Win2K server is MOP.

All the directories exist, and I have full access to them.

When I try to run this command, I get the following message:

File
'\mopusersMR_DiazsalpmsdataRimon_Data.mdf' is on a network device not supported for database files.

When I omit this location, it works just fine.

If I run it on the server, using another directory, it works just fine.

Do you have any idea what I am doing wrong?

Thanks in advance for any help!
Rimon Rafiah

View 1 Replies View Related

How To Get The Default SA Account Password And How To Change It In Sql 2000 Personal Edition ?

May 9, 2005

hi, all:
         I installed a Sql 2000 Personal Edition in my Laptop,now I want to change my Sql 2000 sa account password.
         but I can not find where I can get the default password for sa account and how to change it .

         thanks

View 1 Replies View Related

Upgrade From 2000 MSDE To 2005 Express - Enable TCP/IP By Default && Current Version Of SQL Running?

Jan 23, 2007

i am working on upgrading the clients and server computers from 2000 MSDE to 2005 SQL Express Adv. Plan to upgrade using the template.ini.

i can find the version of sql server running on th server by @@version but how do i find the version of client connctivity components the computer has installed in order to ugrade?

1. somehow i need to know whether to upgrade a client computer CONNECTIVITY COMPONENTS from 2000 to 2005?

2. is there a way to enable tcp/ip in installation script of SQL Express?

THANKS

View 9 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Named Instance Problems (Default Of 7.0/2000 Instance)

Oct 10, 2001

I am trying to set up a Named Instance of SQL 2000 on the same machine that has a default instance of SQL 7.0. The setup always completes and I am able to register the Named Instance of the SQL Server with which it was installed on. However, when I try to connect the users to the database, with both windows and SQL authentication, I receive a SQL server not found error. I have tried an alias setup as well as physically specifying the port number in settup up an ODBC connection.
Has anyone ran into similar problems?
Also, has anyone been able to successfully complete the process as mentioned above?

View 1 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

View 1 Replies View Related

An Error Has Occurred While Establishing A Connection To The Server. When Connecting To SQL Server 2005, This Failure May Be Caused By The Fact That Under The Default Settings SQL Server Does Not Allow Remote Connections.

Jan 22, 2008

My site works fine in VWD2008 express, but I get this error when I try to use it on my live website.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
According to this article: http://support.microsoft.com/kb/914277 I am supposed to:

1.
Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"...
The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.

View 4 Replies View Related

Sql Server Dates :S

Aug 6, 2006

I have an application where there is a form to enter some data to the sql server 2005 database.
when the form is submitted, I also use Date.Now to insert the date the article was submitted. It enters fine. E.g. if I submitted it today, it would display 08/06/2006 (which is 06 August 2006). When getting the records, I use, "dd MMM yyyy" to format the date according to my culture. However, the result I get is 08 June 2006
What might be the problem. I hate working with dates with sql server

View 2 Replies View Related

Sql Server And Dates

Mar 1, 2006

Hi,
My ultimate goal is to display records within a date range. I am using dropdown lists populated from the database as start and end dates for the ultimate SQL Statement. The field is a smallDateTime type.
The begining date populates without todays date using the following:
connection.Open();
//Auto fill fields
SqlCommand command = new SqlCommand("Select distinct CONVERT(NVARCHAR(10),insertDate,101) as iDate from surveyOwner order by iDate DESC", connection);
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
FirstDate.DataTextField = "iDate";
FirstDate.DataSource = reader;
FirstDate.DataBind();
}
connection.Close();
}
It works great except records with todays date are excluded throughout.
I was hoping there was something small I was missing.
Thanks,
Doug

View 8 Replies View Related

SQL Server Dates

Jul 13, 2000

When I query a table specifying a date on a datetime column the query
works if I specify it as a date. If I specify it as a number of days since
1899 it returns nothing

View 1 Replies View Related

Sql Server Dates

Aug 20, 2004

Hello, really need some help here!

I've been inserting dates from an asp page into an sql server db in mm/dd/yyyy format, which is what is accepted using smalldatetime. However, when I retrieve a date from the db and display it on my page, it is in dd/mm/yyyy format. This wouldn't be a problem, but if I want to update details in the same table as the date, but keep the date the same, the dd/mm/yyyy format is not accepted. Anyone have any idea what's going on here? sql server seems to accept dates in one format and output them in another...

View 3 Replies View Related

Using BETWEEN With SQL Server Dates

Oct 5, 2006

I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle. I don't need the time at all. There will be many times when the current date will be input into a record. All I want is the date part. My first thought was to extract the date part each time I need to use it, but it would be better if I could come up with a test condition that will take any date/time and give the right results. I read about one that uses the following:

get the any dates between 1/1/2006 and 12/31/2006.

The test conditon would be:

SELECT * WHERE(date >= 1/1/2006 AND date < 1/1/2007)

You add a day to your upper range and use the less than operator. This wouldn't be so bad for static dates, but each time I used a dynamic date I'd have to add one day to the upper range before testing. I also read using any kind of function or the "Like" operator on a date renders the indexes useless.

I guess what I'm looking for is method to get the results similar to using the "BETWEEN" statement where I don't have to manipulate any of the dates prior or during testing.

My thought right now is from a different perspective. Any time I need the current date I'd truncate the time part leaving me with a date and a time part of "00:00:00". My only problem with this is what if in code I accidentally forget to truncate the time. I could see this happen giving how much I need to use the current date and being used to not having to worry about the time part.

Any help with this will be greatly appreciated. I think this is my last major hurdle before I'm convinced everything will convert over nicely to SQL Server.

View 13 Replies View Related