equal distribution of start and finish dates

Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls helpQuestion. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:
> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rated
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:
> mita wrote:
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||GO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
"Hugo Kornelis" wrote:
> On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
> >Hi all
> >I have got 3 columns in my table- start date,finish date and cost..in the
> >following format...
> >
> >start_date finish_date cost
> >12/12/2000 20/12/2000 $2000
> >01/09/2000 12/10/2000 $400
> >
> >Now if the month and year of the start and finish date is same, the cost
> >remains same...
> >but if the month of the two dates are different, i have to distribute the
> >cost between the two months by calculating the cost for the number of days
> >for both of the months..
> >but i am not able to figure out how?
> >i am using sql 2005 ..
> >my table has got about 1 million rows...
> >pls help
> Hi mita,
> First, you need to create a table that holds all months in your
> reporting period (or more). Something like this:
> CREATE TABLE dbo.Months
> (MonthStart datetime NOT NULL PRIMARY KEY,
> MonthEnd datetime NOT NULL);
> go
> DECLARE @.TheMonth datetime;
> SET @.TheMonth = '200000101'; -- Start at january 2000
> WHILE @.TheMonth <= '20191231' -- End at december 2019
> BEGIN;
> INSERT INTO dbo.Months (MonthStart, MonthEnd)
> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
> END;
> The above is a one time operation, provided you never drop the table.
> Don't forget to add some extra months to the table in a year or ten!
> With this table in place, your query becomes something like this:
> SELECT PeriodStart, PeriodEnd,
> TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
> / DATEDIFF (day, start_date, finish_date) AS cost
> FROM (SELECT CASE WHEN a.start_date > b.MonthStart
> THEN a.start_date
> ELSE b.MonthStart
> END AS PeriodStart,
> CASE WHEN a.finish_date < b.MonthEnd
> THEN a.finish_date
> ELSE b.MonthEnd
> END AS PeriodEnd,
> a.start_date, a.finish_date,
> a.cost AS TotalCost
> FROM dbo.MyTable AS a
> INNER JOIN dbo.Months AS b
> ON b.MonthStart <= a.finish_date
> AND b.MonthStart >= a.start_date) AS d
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 18 Jun 2006 16:32:02 -0700, mita wrote:
>"Hugo Kornelis" wrote:
(snip)
>> First, you need to create a table that holds all months in your
>> reporting period (or more). Something like this:
>> CREATE TABLE dbo.Months
>> (MonthStart datetime NOT NULL PRIMARY KEY,
>> MonthEnd datetime NOT NULL);
>> go
>> DECLARE @.TheMonth datetime;
>> SET @.TheMonth = '200000101'; -- Start at january 2000
>> WHILE @.TheMonth <= '20191231' -- End at december 2019
>> BEGIN;
>> INSERT INTO dbo.Months (MonthStart, MonthEnd)
>> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
>> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
>> END;
>hi hugo
>i just ran this query for creating the months table which u suggested... i
>am getting an error....
>"Conversion failed when converting datetime from character string." what do
>i do?
Hi Mita,
Sorry for the delayed reply.
The error is caused by a stupid typo in my code (quoted above) - I have
one zero to many in the date constant for Jan 1st, 2000. If you change
'200000101' to '20000101', the error should go away.
--
Hugo Kornelis, SQL Server MVP

equal between a String and a Table atribute?

Ihave a if statement and a table with 2 atributes. They are descriptionand date. I will check if there is in the table some date like01-01-2008.

String test "01-01-2008";

if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable))
{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

Does someone know how I can make something like this in C#? Must I use a datasource?

Hi,

Because of the nature of Datetime type i think you should not use string values to compare date values. The seperator character may differ or the time (hour,minute etc. ) part may differ...

Try one of these.

First way;

datetime test=new datetime("2008","1","1");

datetime dbValue=convert.ToDateTime(convert.ToDateTime(..get the value from database..).ToShortDateString()) ;

if (test.Equals(dbValue))

{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

here we get rid of the hour minute part that is different than 0: convert.ToDateTime(convert.ToDateTime(..get the value from database..).ToShortDateString())

Second way;

?n this way we use ToShortDateString() function of datetime type to make the hour minute part as zeros 0.

datetime test=new datetime("2008","1","1");

datetime dbValue=convert.ToDateTime(..get the value from database..).ToShortDateString() ;

if (test.ToShortDateString().Equals(dbValue.ToShortDateString()))

{

textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

}

|||

khalidelmeknesi:

Does someone know how I can make something like this in C#? Must I use a datasource?

Well, you will need to make a connection to the data base, to retrieve the results from your SQL statement, and then read the results.

This can be done using any of the datasource objects or manually by

1, creating a sqlconnection, (the connection to your database) sqlcommand (the SQL statement or stored procedure), and sqldataadapter (the mechanism that will save the results to a dataset)

2. fill the sqldatadapter into a dataset (dataset will hold your return results from the database in DataTables and DataRows)

3. check the values returned by iterating through the DataRow in the DataTable of the DataSet

Epoch Date

Help!!! I have an application I support that the vendor is not being
cooperative sin giving me data purge routines. The application uses Epoch
dates (# of ms since 1/1/1970). I found this script on Oracle but need help
converting it to T-SQL where it works without the arithmetic overflow error:
SELECT to_char(
TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
,'dd-mon-yyyy hh24:mi:ss') normal_date
FROM dual;Try,
select dateadd(day, epoch_num / 86400, cast('19700101' as datetime))
AMB
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||Hi
here is the solution:
select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
from <TABLE>
alternatively just go through DATEDIFF and DATEADD functions available in BO
L
thanks and regards
Chandra
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||How do I get around the arithmetic overflow this statement gives me?
"Chandra" wrote:
> Hi
> here is the solution:
> select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
> from <TABLE>
>
> alternatively just go through DATEDIFF and DATEADD functions available in
BOL
> thanks and regards
> Chandra
>
> "Cathy Soloway" wrote:
>|||Hi
Please go through the following post. This might help you
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Are you sure it is milliseconds?
If you see the formaula (epoch_num / 86400), 86400 is the number of seconds
in a day, so the formaula seems to be calculating days. Try second instead
milli:
select DATEADD (second, epoch_num, '01-JAN-1970') normal_date
from <TABLE>
AMB
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Cathy,
Here's one way. If your epoch_num value is a decimal type,
you'll need to convert it to bigint, so you can use the modulo function.
declare @.epoch_num bigint
set @.epoch_num = 1092347839827
select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
Steve Kass
Drew University
Cathy Soloway wrote:
>How do I get around the arithmetic overflow this statement gives me?
>"Chandra" wrote:
>
>|||Thanks Steve. This does it.
"Steve Kass" wrote:

> Cathy,
> Here's one way. If your epoch_num value is a decimal type,
> you'll need to convert it to bigint, so you can use the modulo function.
> declare @.epoch_num bigint
> set @.epoch_num = 1092347839827
> select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
> Steve Kass
> Drew University
> Cathy Soloway wrote:
>
>

Enterprise manger won't connect

Installed SQL 2000 on a completely up to date windows 2003 advanced server,
then applied sp3a, then I installed reporting service developer addition,
then installed VS .net 2003 the reinstalled reporting services to get the
Report Developer.
Now I can't connect to the Server using EM.
I Get "SQL_alloc_handle on SQL_handle_env failed"
Tried all types of registrations.
Seems to be some sort of ODBC problem since I can attach to the database
remotely from access.
Any suggestions short of starting from scratch?I haven't encountered this particular problem, but I'd suggest you could try
re-intalling the SQL client tools only & see if that solves your problems.
Regards,
Greg Linwood
SQL Server MVP
"sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> Installed SQL 2000 on a completely up to date windows 2003 advanced
server,
> then applied sp3a, then I installed reporting service developer addition,
> then installed VS .net 2003 the reinstalled reporting services to get the
> Report Developer.
> Now I can't connect to the Server using EM.
> I Get "SQL_alloc_handle on SQL_handle_env failed"
> Tried all types of registrations.
> Seems to be some sort of ODBC problem since I can attach to the database
> remotely from access.
> Any suggestions short of starting from scratch?
>|||Thanks, I will give it a try.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> I haven't encountered this particular problem, but I'd suggest you could
try
> re-intalling the SQL client tools only & see if that solves your problems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
> news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> > Installed SQL 2000 on a completely up to date windows 2003 advanced
> server,
> > then applied sp3a, then I installed reporting service developer
addition,
> > then installed VS .net 2003 the reinstalled reporting services to get
the
> > Report Developer.
> >
> > Now I can't connect to the Server using EM.
> >
> > I Get "SQL_alloc_handle on SQL_handle_env failed"
> >
> > Tried all types of registrations.
> >
> > Seems to be some sort of ODBC problem since I can attach to the database
> > remotely from access.
> >
> > Any suggestions short of starting from scratch?
> >
> >
>|||I solved the problem. Installing VS.net 2003 overwrote the ODBC and SQL
related DLL's in the windows\system32 directory. Restoring them fixed the
problem.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> I haven't encountered this particular problem, but I'd suggest you could
try
> re-intalling the SQL client tools only & see if that solves your problems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
> news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> > Installed SQL 2000 on a completely up to date windows 2003 advanced
> server,
> > then applied sp3a, then I installed reporting service developer
addition,
> > then installed VS .net 2003 the reinstalled reporting services to get
the
> > Report Developer.
> >
> > Now I can't connect to the Server using EM.
> >
> > I Get "SQL_alloc_handle on SQL_handle_env failed"
> >
> > Tried all types of registrations.
> >
> > Seems to be some sort of ODBC problem since I can attach to the database
> > remotely from access.
> >
> > Any suggestions short of starting from scratch?
> >
> >
>|||Thanks for the feedback
Regards,
Greg Linwood
SQL Server MVP
"sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
news:eHipkUyAEHA.3988@.tk2msftngp13.phx.gbl...
> I solved the problem. Installing VS.net 2003 overwrote the ODBC and SQL
> related DLL's in the windows\system32 directory. Restoring them fixed the
> problem.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> > I haven't encountered this particular problem, but I'd suggest you could
> try
> > re-intalling the SQL client tools only & see if that solves your
problems.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
> > news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> > > Installed SQL 2000 on a completely up to date windows 2003 advanced
> > server,
> > > then applied sp3a, then I installed reporting service developer
> addition,
> > > then installed VS .net 2003 the reinstalled reporting services to get
> the
> > > Report Developer.
> > >
> > > Now I can't connect to the Server using EM.
> > >
> > > I Get "SQL_alloc_handle on SQL_handle_env failed"
> > >
> > > Tried all types of registrations.
> > >
> > > Seems to be some sort of ODBC problem since I can attach to the
database
> > > remotely from access.
> > >
> > > Any suggestions short of starting from scratch?
> > >
> > >
> >
> >
>

Enterprise manger won't connect

Installed SQL 2000 on a completely up to date windows 2003 advanced server,
then applied sp3a, then I installed reporting service developer addition,
then installed VS .net 2003 the reinstalled reporting services to get the
Report Developer.
Now I can't connect to the Server using EM.
I Get "SQL_alloc_handle on SQL_handle_env failed"
Tried all types of registrations.
Seems to be some sort of ODBC problem since I can attach to the database
remotely from access.
Any suggestions short of starting from scratch?I haven't encountered this particular problem, but I'd suggest you could try
re-intalling the SQL client tools only & see if that solves your problems.
Regards,
Greg Linwood
SQL Server MVP
"sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> Installed SQL 2000 on a completely up to date windows 2003 advanced
server,
> then applied sp3a, then I installed reporting service developer addition,
> then installed VS .net 2003 the reinstalled reporting services to get the
> Report Developer.
> Now I can't connect to the Server using EM.
> I Get "SQL_alloc_handle on SQL_handle_env failed"
> Tried all types of registrations.
> Seems to be some sort of ODBC problem since I can attach to the database
> remotely from access.
> Any suggestions short of starting from scratch?
>|||Thanks, I will give it a try.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> I haven't encountered this particular problem, but I'd suggest you could
try
> re-intalling the SQL client tools only & see if that solves your problems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
> news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> server,
addition,
the
>|||I solved the problem. Installing VS.net 2003 overwrote the ODBC and SQL
related DLL's in the windows\system32 directory. Restoring them fixed the
problem.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> I haven't encountered this particular problem, but I'd suggest you could
try
> re-intalling the SQL client tools only & see if that solves your problems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
> news:OB0VFOO9DHA.2944@.TK2MSFTNGP11.phx.gbl...
> server,
addition,
the
>|||Thanks for the feedback
Regards,
Greg Linwood
SQL Server MVP
"sdrexler" <sdrexler@.nospam.optonline.net> wrote in message
news:eHipkUyAEHA.3988@.tk2msftngp13.phx.gbl...
> I solved the problem. Installing VS.net 2003 overwrote the ODBC and SQL
> related DLL's in the windows\system32 directory. Restoring them fixed the
> problem.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:eYlgmYO9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> try
problems.
> addition,
> the
database
>

Enterprise Manager table property consistency

I have one table in my SQL server 2000 (w2k server up to date service packs) where when I right click 'properties' of the table in Enterprise Manager I get 13316 records. But if I open the table in enterprise manager and SELECT count(*) I get 13315 recor
ds. However, using the same table in Query Analyzer, Select count(*) results in 13315 records and Select *
results in 13315 rows showing in the lower right of the Query pane.
It seems to be occuring in just this one table using Enterprise Manager.
Probably no big deal. Anyone know what may be causing this?
TIA
Ken,
Refer DBCC UPDATEUSAGE in BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIA
|||In addition to Dinesh's response, both counts and table/index sizes are
numbers that are NOT updated for each insert, update or delete. They are
periodically updated, so they will be close most of the time , but not
exact...
The command that Dinesh showed you will update the information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIA

Enterprise Manager table property consistency

I have one table in my SQL server 2000 (w2k server up to date service packs)
where when I right click 'properties' of the table in Enterprise Manager I
get 13316 records. But if I open the table in enterprise manager and SELECT
count(*) I get 13315 recor
ds. However, using the same table in Query Analyzer, Select count(*) result
s in 13315 records and Select *
results in 13315 rows showing in the lower right of the Query pane.
It seems to be occuring in just this one table using Enterprise Manager.
Probably no big deal. Anyone know what may be causing this?
TIAKen,
Refer DBCC UPDATEUSAGE in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIA|||In addition to Dinesh's response, both counts and table/index sizes are
numbers that are NOT updated for each insert, update or delete. They are
periodically updated, so they will be close most of the time , but not
exact...
The command that Dinesh showed you will update the information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIAsql

Enterprise Manager table property consistency

I have one table in my SQL server 2000 (w2k server up to date service packs) where when I right click 'properties' of the table in Enterprise Manager I get 13316 records. But if I open the table in enterprise manager and SELECT count(*) I get 13315 records. However, using the same table in Query Analyzer, Select count(*) results in 13315 records and Select
results in 13315 rows showing in the lower right of the Query pane
It seems to be occuring in just this one table using Enterprise Manager
Probably no big deal. Anyone know what may be causing this
TIAKen,
Refer DBCC UPDATEUSAGE in BooksOnLine.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIA|||In addition to Dinesh's response, both counts and table/index sizes are
numbers that are NOT updated for each insert, update or delete. They are
periodically updated, so they will be close most of the time , but not
exact...
The command that Dinesh showed you will update the information.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:53400F61-A8C3-4423-BCCA-B14A84E7FDBD@.microsoft.com...
> I have one table in my SQL server 2000 (w2k server up to date service
packs) where when I right click 'properties' of the table in Enterprise
Manager I get 13316 records. But if I open the table in enterprise manager
and SELECT count(*) I get 13315 records. However, using the same table in
Query Analyzer, Select count(*) results in 13315 records and Select *
> results in 13315 rows showing in the lower right of the Query pane.
> It seems to be occuring in just this one table using Enterprise Manager.
> Probably no big deal. Anyone know what may be causing this?
> TIA

Enterprise Manager Date format has changed

I am not sure how this even changed but...

Until just recently when I went into any database and listed stored
procedures I could list by the date which was in the format: mm/dd/yyyy.
This allowed me to find the most recent stored procedures as they could be
sorted to the top.

Somehow the date has been changed to: yyyy-mm-dd hh:mm:ss:kkk
(Okay I didn't know what to use for milli second, I used K's ;)

You would think that I could still sort by date and get the newest to the
top, oldest to the bottom. I can't. Something has changed the date format,
and while I can click on the data bar and change the order from ascending to
descending, it does not appear to be sorting by the date so it does me no
good.

Does anyone know:

a) how to fix this
b) what could have happened to break it?

Thanks."Sandy" <sandy@.murdocks.on.ca> wrote in message
news:92822120.0504060450.1890eeab@.posting.google.c om...
>I am not sure how this even changed but...
> Until just recently when I went into any database and listed stored
> procedures I could list by the date which was in the format: mm/dd/yyyy.
> This allowed me to find the most recent stored procedures as they could be
> sorted to the top.
> Somehow the date has been changed to: yyyy-mm-dd hh:mm:ss:kkk
> (Okay I didn't know what to use for milli second, I used K's ;)
> You would think that I could still sort by date and get the newest to the
> top, oldest to the bottom. I can't. Something has changed the date format,
> and while I can click on the data bar and change the order from ascending
> to
> descending, it does not appear to be sorting by the date so it does me no
> good.
> Does anyone know:
> a) how to fix this
> b) what could have happened to break it?
> Thanks.

I have no idea, except to guess that your regional settings may have
changed - I assume that EM would use them to determine date and time
formats. You might also want to check that you've installed SP3 on your
client as well.

For what it's worth, there are a couple of previous posts about similar
issues in the group archives (Google "sql enterprise manager sort date"),
but unfortunately it looks like no one ever found a good
explanation/solution.

Simon

Enterprise Manager Create Date

Folks,
When viewing the tables of the databases we have on our Server, the
values in the "Create Date" column are generally of the format
"DD/MM/YYYY HH:MM:SS". Curiously however, I just had to recreate one
of the tables, and on that table alone the create date displayed is
"YYYY-MM-DD HH:MM:SS.FFF" (FFF = fractions of a second I assume?)
Anyone know why this has changed? Could it be some kind of change of
regional settings on the Server?
Suggestions invited
Thanks
SteveSteve Hall wrote:
> Folks,
> When viewing the tables of the databases we have on our Server, the
> values in the "Create Date" column are generally of the format
> "DD/MM/YYYY HH:MM:SS". Curiously however, I just had to recreate one
> of the tables, and on that table alone the create date displayed is
> "YYYY-MM-DD HH:MM:SS.FFF" (FFF = fractions of a second I assume?)
> Anyone know why this has changed? Could it be some kind of change of
> regional settings on the Server?
See if it helps you:
Solving the Datetime Mystery (SQL Server Magazine)
http://www.winnetmag.com/SQLServer/.../9147/9147.html
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote in message news:
<OoKQfbfBEHA.2804@.tk2msftngp13.phx.gbl>...
> Steve Hall wrote:
> See if it helps you:
> Solving the Datetime Mystery (SQL Server Magazine)
> http://www.winnetmag.com/SQLServer/.../9147/9147.html
> sincerely,
Sebastian,
Thanks for that... but I think I may be missing something still - that
information suggests how I can format dates returned from queries...
But this is the table's create date property - internal to SQL - where
is that format defined?
Forgive me if I am still missing your point!
Steve

Enterprise Manager - Want to display date in different format

Currently I am using Enterprise Manager to see the table content. It is
displaying date in American format but i want to view it as British date.
How can i change the settings so that i can view it differently ?
regards
Manisha
"manisha_css" wrote:

> Currently I am using Enterprise Manager to see the table content. It is
> displaying date in American format but i want to view it as British date.
> How can i change the settings so that i can view it differently ?
> regards
> Manisha
Manisha -
Try following Steps :-
1. Go to EM
2. Go to Security --> Logins (Inside EM)
3. Double Click your Login Name.
4. Inside General Tab of Login Window, Change the Language to the desired one.
5. Exit EM & Start a New Instance of EM
6. Right Click Server Name & Edit SQL Server Registration Property
7. Connect with your Credentials.
8. Verify Date Time columns from existing tables.
Regards
Surajit

Enterprise Manager - Want to display date in different format

Currently I am using Enterprise Manager to see the table content. It is
displaying date in American format but i want to view it as British date.
How can i change the settings so that i can view it differently ?
regards
Manisha"manisha_css" wrote:
> Currently I am using Enterprise Manager to see the table content. It is
> displaying date in American format but i want to view it as British date.
> How can i change the settings so that i can view it differently ?
> regards
> Manisha
Manisha -
Try following Steps :-
1. Go to EM
2. Go to Security --> Logins (Inside EM)
3. Double Click your Login Name.
4. Inside General Tab of Login Window, Change the Language to the desired one.
5. Exit EM & Start a New Instance of EM
6. Right Click Server Name & Edit SQL Server Registration Property
7. Connect with your Credentials.
8. Verify Date Time columns from existing tables.
Regards
Surajit

Enterpirise Manager Help

I need to know what tables in a particular database have been updated. The
Enterpirise manager only displays the creation date and not when the table
was updated. If their any way of finding this out?..
Thanks
OK, I've just read that you cant do that so the problem I have is this;
I have a DB with around 80 tables which only 20-30 are used on a daily
basis. I created a snapshot of all tables so thats not a problem.
What I want to know is when my subscriber has new data (merge) will it
automaticly push that data (table) to my distributor/publisher or does it
have to be triggered.
I'm new to this so please be patient.
Many thanks
""confused"" wrote:

> I need to know what tables in a particular database have been updated. The
> Enterpirise manager only displays the creation date and not when the table
> was updated. If their any way of finding this out?..
>
> Thanks
|||When the subscriber changes the data at the subscriber, it gets recorded in
metadata tables. These tables are read by the merge agent which then applies
the changes to the publisher. IE the data only flows upstream when the merge
agent runs. If this is continuously, this'll be usually within a minute;
alternatively it might be set to run on a schedule - you have to look at the
job to see how it is set.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
Another question I have is why do I have hundreds of 'tsvw_xxxxx' and
'ctsv_xxx' views. Also 'sp_selxxxxx', 'sp_updxxxxx' and 'sp_insxxxx' in the
stored procedure area??. What purpose are these files or do I have to
delete them manually.
Many thanks
"Paul Ibison" wrote:

> When the subscriber changes the data at the subscriber, it gets recorded in
> metadata tables. These tables are read by the merge agent which then applies
> the changes to the publisher. IE the data only flows upstream when the merge
> agent runs. If this is continuously, this'll be usually within a minute;
> alternatively it might be set to run on a schedule - you have to look at the
> job to see how it is set.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The stored procedures 'sp_selxxxxx', 'sp_updxxxxx' and 'sp_insxxxx' etc are
all used by the distribution agent in transactional replication. When an
update occurs on the publisher, this is replicated (by default) as a call to
execute the 'sp_updxxxxx' procedure on the subscriber. The views " tsvw_"
are used internally by the merge engine. When I'm removing replication
completely from a database and some procedures/views remain afterwards, I
delete them manually. Otherwise I'd recommend leaving them in place or
you'll have to examine each one carefully so as to not break the existing
setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

entering data into sql database

hi

I am working on webforms i have to insert system date into sql database when a button is clicked.

I have have 3 fields on the webform and a submit button.

I have to insert the date along with the other fileds into the sql database

when I am trying to insert date using getdate() it is getting inserted but not into the same row as that of other fields.

Can you plzzz help me with the code for inserting system data into the database into the same row as that of other fields

myCommand2 =New SqlCommand("Insert into Items(Requestdate) values (getdate()) where Hospital= '" & DropDownList2.SelectedItem.Text & "' ", myConnection)

ra = myCommand2.ExecuteNonQuery()

myConnection.Close()

THE ABOVE CODE GIVES AN SYNTAX ERROR NEAR "WHERE"

please help

you can also post me ur replies onurs_forever_tanya2001@.yahoo.co.in

Thanks bye

You can't specify awhere clause on an insert statement as the whole point of an insert is to add a record (i.e. it doesn't already exist). If you want to update and existing record, you will have to use anupdatestatement.

|||

by using update statement will i be able to insert new record then?

because i have to take system date while inserting a new record...

please help

|||

tanya2001:

by using update statement will i be able to insert new record then?

No. If you want to insert a record you have to use an insert statement (but remove your where clause). If you want to update an existing record, use an update statement.

|||

Hey tanya ,

Thanks for your question .

let me descrive some essesntioal stuff for you first.

Insert Statement , we use this statement when we wantadd new record to our table , it means that record doesn't exist and we are going to add that , so in this case we wont usingwhere on this state ment becuase there is no any rows and we want to add that.

Update Statement , we use this statement when we already we have one or more records in our table and we want just change some of their value we use update statement and we use where to specify which rows we are going to change.

In you solution , if you are going to Insert New value to table that doesn't not exist at all use Insert Statement, if you are going to change some value in your database use update.

|||

thanks for your answer but i want to insert system date as well with the new record in the database

so how can i insert the system date as well as the new record by entering a single submit button

its actually like i have 3 fields in a webform so i have to insert those three fields as well as system date should be inserted when submit is clicked

please help

|||

thanks all i got the answer

Enter Today's Date in SSMS Open Table Grid View

What date constants are available for entering today's date in a
datetime column after using the Open Table command within SSMS? I
have tried GETDATE() and CURRENT_TIMESTAMP, but neither worked.
Another question - if I open the samed named table in two databases to
compare the data contents using Open Table, the tab labels only
display the table name and are not fully qualified. Is there an SSMS
option to show the table labels fully qualified.
Thanks in advance - Peter
pwc (pcrickman@.verizon.net) writes:
> What date constants are available for entering today's date in a
> datetime column after using the Open Table command within SSMS? I
> have tried GETDATE() and CURRENT_TIMESTAMP, but neither worked.
You look at your watch, and the type what you see. Open Table is a very
simple data-entry tool.
If you don't want to consult your watch, you will have to write an INSERT
or an UPDATE statement instead.

> Another question - if I open the samed named table in two databases to
> compare the data contents using Open Table, the tab labels only
> display the table name and are not fully qualified. Is there an SSMS
> option to show the table labels fully qualified.
Yes, it's called SP2. That is, they've changed this in SP2 so that the
database name is included.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx