Please help me populate my date fields

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:

Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as xupdate orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||populate only orderDate in this

CREATE TABLE [dbo].[Orders] (
[OrdID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[OrderDateONLY] AS ([orderdate]) ,
[OrderDayName] AS (datename(weekday,[orderdate])) ,
[OrderMonth] AS (datename(month,[orderdate]))
.....
)|||harsal_in,

Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

"The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out :confused:|||it works very well on my machine.
anyways try this:
update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||harshal_in,

I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

Thanks.|||Format 120 (or 121 Brett...) are better for date conversion because they are interpreted unambiguously by SQL Server.

update orders
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
orderDayname=datename(dw,orderdate),
orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),
orderweekofyear=datename(wk,orderdate)

...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.|||Unless it's a warehouse...or for performance reasons...which I can't see it...

NEVER store derived data....

What's the reason..

If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

Which means an additional process..

This is OLTP, right?

in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

OLTP is fluid, and alway in a state of flux...(like the capacitor :D )

So why?sql

please help me about :Unable to connect to SQL Server database.

To All Members,

When I create a new project and try to use theASP.Net web application administration :security tab, I get the following :

Thereis a problem with your selected data store. This can be caused by aninvalid server name or credentials, or by insufficient permission. Itcan also be caused by the role manager feature not being enabled. Clickthe button below to be redirected to a page where you can choose a newdata store.

The following message may help in diagnosing the problem:Unable to connect to SQL Server database.

I am usingMicrosoft Visual Web Developer 2005 Express Edition with a localMS SQL Server 2000.

I am new to all of this so a little help would be great

Thanks.

please mail me todehackers@.linuxmail.org

or izzuan@.malysia.com

Dear Izzuan,

I think the bug comes from your connection string, it should be as follows:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

where

myServerAddress is the ip address or UNC name of your server (you can also use . character in place of your local server) If you are using a SQL instance, you should add it in this section: myServerAddress\instanceName|||

What I am guessing is that your DB Server does not have ASPNETDB database. First check to see if there is a database named ASPNETDB. If not, runaspnet_regsql.exe. You can find this exe in the following location:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (XP)

C:\WINNT\Microsoft.NET\Framework\v2.0.50727 (Windows Server 2003)

This exe creates ASPNETDB database in your DB Server instance.

|||

Oh thaks sago...

i`ll try the solution u gave to me, but it still same eror running...

Actually in my server i have 2 localhost are running at the same time i want

to build a new localhost that using ASP.Net configuration

but it containt eror....

my previous d.base are use by MS accsess...

if i remove the MS SQL 2000 then i install WAMP application

either the d.Base (MS Access) are still running or maybe it will be crash?

|||

Hi izzuan,

Asp.net web site administration tool is indenpent of your IIS settings. Whether you have 2 localhost running at the same time or not have very little things to do with the administration tool. So i guess it's not the problem.

The web site administration tool will only connect to your application service database(aspnnetdb), make sure you have that database build up successfully. I suggest you to follow the suggestionbullpit gave you. Run aspnet_regsql first(since you are using sql2000) and then put some new providers into your web.config (another way is to add a new connection string named "localsqserver" into your web.config and direct it to the newaspnetdb which you get from asqnet_regsql).

You can refer to this link for more information:http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx

Hope my suggestion helps

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
Mike
Please do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help : A call to SQL Server Reconciler failed. Try to resynchronize.

Hi all,

While i am doing new substription wizad, I encounter following error: Do I miss something?

A call to SQL Server Reconciler failed. Try to resynchronize.

A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)

The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\compuntername_SQLMOBILE_SQLMOBILE\20060321224198\CustomerData_2.sch' could not be propagated to the subscriber.
HRESULT 0x80070005 (0)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
HRESULT 0x80045017 (0)

I am using

IIS 5.1

SQL 2005 (Upgrade from SQL 2000 SP4)

Visual Studio 2005

Thank you very much

Tony

try generating a new snapshot on the server and also make sure you are not out of memory on your mobile device when performing the replication.

Darren

|||

HI ,

How can I create another snapshot agin. I am not getting the oiption to select a nre one when running the wizard. I am not able to delete the previous snap shot also.

-Please advise

sql

Please help : A call to SQL Server Reconciler failed. Try to resynchronize.

Hi all,

While i am doing new substription wizad, I encounter following error: Do I miss something?

A call to SQL Server Reconciler failed. Try to resynchronize.

A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)

The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\compuntername_SQLMOBILE_SQLMOBILE\20060321224198\CustomerData_2.sch' could not be propagated to the subscriber.
HRESULT 0x80070005 (0)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
HRESULT 0x80045017 (0)

I am using

IIS 5.1

SQL 2005 (Upgrade from SQL 2000 SP4)

Visual Studio 2005

Thank you very much

Tony

try generating a new snapshot on the server and also make sure you are not out of memory on your mobile device when performing the replication.

Darren

|||

HI ,

How can I create another snapshot agin. I am not getting the oiption to select a nre one when running the wizard. I am not able to delete the previous snap shot also.

-Please advise

Please Help ... Can't Figure This One Out

Greetings,
I need to write a SQL stored procedure to accomplish the following and I'm
not having any luck. So that I don't confuse anyone too much, I'll leave out
the code I'm trying and just explain what I'm trying to do.
Start with
Table: sequenceOrder
orderNumber totalUnits
101 26
102 11
103 9
104 8
105 16
I have a VB.NET program that will be passing in two parameters, one is
workDays (number of work days during a given w) and the other is
dailyUnits (maximum number units to be made during each wday).
I need a running total, by record line, for the totalUnits. If the running
total is less than dailyUnits, then I need to add the current value for that
work day. If the running total is greater than dailyUnits, then I need to
add 1 to the current value for that work day. Also, I need to number the
orders within that workDay.
Using the sample table above and having workDays = 3 and dailyUnits = 30 the
output table would like this:
Finish with
Table: sequenceOrder
orderNumber totalUnits workDay sequenceOrder
101 26 1 1
102 11 2 1
103 9 2 2
104 8 2 3
105 16 3 1
106 12 3 2
Is this possible, if so how do I get this done?
Thanks for all the help,
James Walker, Jr."James Walker" <walker@.modernfold.com> wrote in message
news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> I need to write a SQL stored procedure to accomplish the following and I'm
> not having any luck. So that I don't confuse anyone too much, I'll leave
> out the code I'm trying and just explain what I'm trying to do.
> Start with
> Table: sequenceOrder
> orderNumber totalUnits
> 101 26
> 102 11
> 103 9
> 104 8
> 105 16
> I have a VB.NET program that will be passing in two parameters, one is
> workDays (number of work days during a given w) and the other is
> dailyUnits (maximum number units to be made during each wday).
> I need a running total, by record line, for the totalUnits. If the running
> total is less than dailyUnits, then I need to add the current value for
> that work day. If the running total is greater than dailyUnits, then I
> need to add 1 to the current value for that work day. Also, I need to
> number the orders within that workDay.
> Using the sample table above and having workDays = 3 and dailyUnits = 30
> the output table would like this:
> Finish with
> Table: sequenceOrder
> orderNumber totalUnits workDay sequenceOrder
> 101 26 1 1
> 102 11 2 1
> 103 9 2 2
> 104 8 2 3
> 105 16 3 1
> 106 12 3 2
> Is this possible, if so how do I get this done?
>
Ok, this is a fun one. Here's a simple solution, using intiger division of
the running total by the number of hours per day. But I worry that the
assignment of orders to days is too "dumb". I have a suspicion that there's
a better solution somewhere that would sequence the work to fill up the days
and minimize idle time.
create table Orders
(
OrderNumber int primary key,
TotalUnits int not null
)
insert into Orders(orderNumber,TotalUnits)
select 101,26
union all select 102,11
union all select 103,9
union all select 104,8
union all select 105,16
go
declare @.days int,
@.dailyUnits int
set @.days = 3
set @.DailyUnits = 30;
with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
(
select OrderNumber, TotalUnits,
(select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
RunningTotal
from Orders o
)
select
Ordernumber,
TotalUnits,
1+RunningTotal/@.DailyUnits WorkDay,
row_number() over (partition by RunningTotal/@.DailyUnits order by
OrderNumber) SequenceOrder
from UpcomingOrders
where 1+RunningTotal/@.DailyUnits <= @.days
David|||David,
Thanks for the reply. It works great. Also, "filling up idle time" is in the
next phase. As in my example, the first day has 4 more units it could make,
so I will need to search down the records where the totalUnits is <= 4 and
then move that order up for the w.
James Walker, Jr.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JMsfHCgGHA.452@.TK2MSFTNGP02.phx.gbl...
> "James Walker" <walker@.modernfold.com> wrote in message
> news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Ok, this is a fun one. Here's a simple solution, using intiger division
> of the running total by the number of hours per day. But I worry that the
> assignment of orders to days is too "dumb". I have a suspicion that
> there's a better solution somewhere that would sequence the work to fill
> up the days and minimize idle time.
>
> create table Orders
> (
> OrderNumber int primary key,
> TotalUnits int not null
> )
> insert into Orders(orderNumber,TotalUnits)
> select 101,26
> union all select 102,11
> union all select 103,9
> union all select 104,8
> union all select 105,16
> go
> declare @.days int,
> @.dailyUnits int
> set @.days = 3
> set @.DailyUnits = 30;
>
> with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
> (
> select OrderNumber, TotalUnits,
> (select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
> RunningTotal
> from Orders o
> )
> select
> Ordernumber,
> TotalUnits,
> 1+RunningTotal/@.DailyUnits WorkDay,
> row_number() over (partition by RunningTotal/@.DailyUnits order by
> OrderNumber) SequenceOrder
> from UpcomingOrders
> where 1+RunningTotal/@.DailyUnits <= @.days
>
> David
>

Please HELP - I am getting security errors hwen trying to open the

Hi,
I am trying to open Reposrt Services , I get the following error.
'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\'. The directory does not exist or is not accessible
because of security settings.]
I do not know what went wrong. Could any one please help.
Thanks Much,
--
snbatDid it work at one point? What changed? Does the directory exist?
Jeff
"snbat" <snbat@.discussions.microsoft.com> wrote in message
news:2ED1BBAD-3E0A-4CFD-9C08-ABEBB3A7A47F@.microsoft.com...
> Hi,
> I am trying to open Reposrt Services , I get the following error.
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\'. The directory does not exist or is not
accessible
> because of security settings.]
> I do not know what went wrong. Could any one please help.
> Thanks Much,
>
> --
> snbat

Please help - DTS problems in SQL 7 --> AS/400

I was wondering if any of you could shed some light on the following
problem?
We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
DR, Production, Development, Test) on our Datawarehousing Project.
They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
running version 4.5, last one upgraded to 5.1) for data refreshes,
using SP's and lots of DTS Packages.
Problem is that whenever we try to run DTS (using dtsrun.exe, works
fine in Designer Mode) against the upgraded AS/400 system on our Test
Server it gives out the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
string: up_processDTS Failed Error source: Microsoft OLE DB
Provider for SQL Server Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14); Provider
Error: 50000 (C350) Error string: up_processDTS Failed
Error source: Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
Provider Error: 0 (0) Error string: Package failed because Step
'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
Data Transformation Services (DTS) Package Help file: sqldts.hlp
Help context: 700. Process Exit Code 1. The step failed.
However, when I run the same DTS Packages from our Live, Dev and DR
machines, it works fine!
I have checked and double-checked the revision details, all four boxes
are the same. I have run the error message against MS KB and IBM,
nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
they only work from other NT boxes and not the Test Server which is
most bizarre. I've rebooted twice, nada.
Any help, suggestions, pointers etc. would be much appreciated.
Regards
ALIIt is only the one step that is failing
DTSStep_DTSExecuteSQLTask_1
Can you isolate this and run it. What does it do? If everything alse runs
fine and the ONLY difference is the version (5.1) then I would suggest that
as a possible cause
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0308070353.6f5ee16@.posting.google.com...
> I was wondering if any of you could shed some light on the following
> problem?
> We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
> DR, Production, Development, Test) on our Datawarehousing Project.
> They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
> running version 4.5, last one upgraded to 5.1) for data refreshes,
> using SP's and lots of DTS Packages.
> Problem is that whenever we try to run DTS (using dtsrun.exe, works
> fine in Designer Mode) against the upgraded AS/400 system on our Test
> Server it gives out the following error:
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
> string: up_processDTS Failed Error source: Microsoft OLE DB
> Provider for SQL Server Help file: Help context: 0
> Error Detail Records: Error: -2147217900 (80040E14); Provider
> Error: 50000 (C350) Error string: up_processDTS Failed
> Error source: Microsoft OLE DB Provider for SQL Server Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
> Provider Error: 0 (0) Error string: Package failed because Step
> 'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
> Data Transformation Services (DTS) Package Help file: sqldts.hlp
> Help context: 700. Process Exit Code 1. The step failed.
> However, when I run the same DTS Packages from our Live, Dev and DR
> machines, it works fine!
> I have checked and double-checked the revision details, all four boxes
> are the same. I have run the error message against MS KB and IBM,
> nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
> they only work from other NT boxes and not the Test Server which is
> most bizarre. I've rebooted twice, nada.
> Any help, suggestions, pointers etc. would be much appreciated.
> Regards
> ALI

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.
It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
--============================================
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
--============================================
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>
|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.

please help

if i query like
select * from table1 then i will get the following
************************************************** **
Ticker col2 col3 col4 col5
----------------
BRK.A2003-05-02 70400.000.0078500.00
FARM2003-05-02 326.253.60370.99
GREY2003-05-02 680.004.00832.00
----------------------
BRK.A2003-05-09 72700.000.0078500.00
FARM2003-05-09 313.003.60370.99
GREY2003-05-09 655.004.00832.00
----------------------
BRK.A2003-05-16 73550.000.0078500.00
FARM2003-05-16 311.503.60370.99
GREY2003-05-16 649.904.00832.00
-----------------------
BRK.A2003-05-23 73850.010.0077000.00
FARM2003-05-23 317.253.60370.99
GREY2003-05-23 665.004.00832.00
-----------------------
BRK.A2003-05-30 71000.000.0075900.00
FARM2003-05-30 322.003.60370.99
GREY2003-05-30 657.004.00832.00

************************************************** **

but my output should be as follows
***********************************************
Ticker Item 5/2/2003 5/9/2003 5/16/2003 5/23/2003 5/30/2003
----------------------
BRK.A col3 70400.00 72700.00 73550.00 73850.01 71000.00
col4 0.00 0.00 0.00 0.00 0.00
col5 78500.00 78500.00 78500.00 77000.00 75900.00
----------------------
FARM col3 326.25 313.00 311.50 317.25 322.00
col4 3.60 3.60 3.60 3.60 3.60
col5 370.99 370.99 370.99 370.99 370.99
----------------------
GREY col3 680.00 655.00 649.90 665.00 657.00
col4 4.00 4.00 4.00 4.00 4.00
col5 832.00 832.00 832.00 832.00 832.00

**********************************************
can anyone helpme how 2 create the query in order 2 obtain the required outputHi

It looks like you are wanting a pivot table. It is usually better to do this
on the client.
If you don't want to write the code to do it then something like
http://www.rac4sql.net/ can be used.

You can also do in the database, but these may be slow:
http://support.microsoft.com/defaul...b;EN-US;q175574

http://www.sqlteam.com/item.asp?ItemID=2955

John

"kalyan" <kalikoi@.yahoo.com> wrote in message
news:98907965.0410260358.259c792b@.posting.google.c om...
> if i query like
> select * from table1 then i will get the following
> ************************************************** **
> Ticker col2 col3 col4 col5
> ----------------
> BRK.A 2003-05-02 70400.00 0.00 78500.00
> FARM 2003-05-02 326.25 3.60 370.99
> GREY 2003-05-02 680.00 4.00 832.00
> ----------------------
> BRK.A 2003-05-09 72700.00 0.00 78500.00
> FARM 2003-05-09 313.00 3.60 370.99
> GREY 2003-05-09 655.00 4.00 832.00
> ----------------------
> BRK.A 2003-05-16 73550.00 0.00 78500.00
> FARM 2003-05-16 311.50 3.60 370.99
> GREY 2003-05-16 649.90 4.00 832.00
> -----------------------
-
> BRK.A 2003-05-23 73850.01 0.00 77000.00
> FARM 2003-05-23 317.25 3.60 370.99
> GREY 2003-05-23 665.00 4.00 832.00
> -----------------------
> BRK.A 2003-05-30 71000.00 0.00 75900.00
> FARM 2003-05-30 322.00 3.60 370.99
> GREY 2003-05-30 657.00 4.00 832.00
> ************************************************** **
> but my output should be as follows
> ***********************************************
> Ticker Item 5/2/2003 5/9/2003 5/16/2003 5/23/2003 5/30/2003
> ----------------------
> BRK.A col3 70400.00 72700.00 73550.00 73850.01 71000.00
> col4 0.00 0.00 0.00 0.00 0.00
> col5 78500.00 78500.00 78500.00 77000.00 75900.00
> ----------------------
> FARM col3 326.25 313.00 311.50 317.25 322.00
> col4 3.60 3.60 3.60 3.60 3.60
> col5 370.99 370.99 370.99 370.99 370.99
> ----------------------
> GREY col3 680.00 655.00 649.90 665.00 657.00
> col4 4.00 4.00 4.00 4.00 4.00
> col5 832.00 832.00 832.00 832.00 832.00
> **********************************************
> can anyone helpme how 2 create the query in order 2 obtain the required
output

Please help

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
--
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
--
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
--
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.It's not all that pretty, but the following function will do the job,
there may be more efficient approaches, but this one does work.
-- ========================================
====
CREATE FUNCTION JoinRows(@.id int)
RETURN varchar(50)
AS
BEGIN
DECLARE @.f1 char(1)
DECLARE @.str varchar(50)
SET @.f1 = ''
SET @.str = ''
WHILE 1=1
BEGIN
SELECT top 1 @.f1 = f1
,@.str = @.str + f1 + ','
FROM tab2
WHERE id = @.id
AND f1 > @.f1
IF @.@.rowcount = 0 BREAK
END
IF LEN(@.str) > 1
SET @.str = SUBSTRING(@.str,1,len(@.str)-1)
RETURN @.str
END
-- ========================================
====
-- This select query will then return Tab3
SELECT
*, dbo.JoinRows(id)
FROM tab1
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <u4oLJnfxFHA.736@.tk2msftngp13.phx.gbl>, dkrreddy@.hotmail.com
says...
> Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
> matching values.
> Tab3 should have the info as below.
> Tab1
> --
> ID Val1 Val2
> 1 qqq sa
> 2 aaa fa
> 3 ddd we
>
> Tab2
> --
> ID F1
> 1 A
> 1 B
> 1 C
> 1 D
> 2 F
> 2 G
> 3 H
>
> Output Should be as blelow, without using the functions please advise the
> sql query.
> Tab3
> --
> ID Val1 Val2 F1
> 1 qqq sa A,B,C,D
> 2 aaa fa F,G
> 3 ddd we H
>
> Thanks in advance.
>
>|||The following will work as well:
declare @.f1str varchar(100)
set @.f1str = ''
select @.f1str = @.f1str + f1 + ','
from F1
select @.f1str
You'd have to run it an ID at a time in a function but at least it's
only one execution per ID as opped to F1.
Hope that helps.sql

Please give kind attension to my queries very urgent

Please give kind attension to my queries

When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help

Reporting Services Error

---

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help

---

SQL Server Reporting Services

Moving to RS forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||What version of reporting services are you using and what version of sql server did you point it to?|||

Thanku for attension

I am using version SQL server 2005

|||

This thread may help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237706&SiteID=1

cheers,

Andrew

sql

Please comment

Dear all
My server received following information around 2 months from event viewer.
Error: 17883, Severity: 1, State: 0
The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
(We have not a scheduler 3 in my sql)
After that, i disable symantec antivirus on my server, then this error do
not occur again. i dont know why?
Server :windows 2003
SQL : SQL 2000 SP3
Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi
> Generally speaking, it is not a good idea to have anti-virus products on a
> SQL Server.
> http://support.microsoft.com/kb/309422/en-us
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
>
|||Do you have any document ?
"bass_player [SBS-MVP]" wrote:

> Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
>
>

Please comment

Dear all
My server received following information around 2 months from event viewer.
Error: 17883, Severity: 1, State: 0
The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
(We have not a scheduler 3 in my sql)
After that, i disable symantec antivirus on my server, then this error do
not occur again. i dont know why?
Server :windows 2003
SQL : SQL 2000 SP3Hi
Generally speaking, it is not a good idea to have anti-virus products on a
SQL Server.
http://support.microsoft.com/kb/309422/en-us
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
> Dear all
> My server received following information around 2 months from event
> viewer.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
> (We have not a scheduler 3 in my sql)
> After that, i disable symantec antivirus on my server, then this error do
> not occur again. i dont know why?
> Server :windows 2003
> SQL : SQL 2000 SP3
>|||Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi
> Generally speaking, it is not a good idea to have anti-virus products on a
> SQL Server.
> http://support.microsoft.com/kb/309422/en-us
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
>|||Do you have any document ?
"bass_player [SBS-MVP]" wrote:

> Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
>
>

Please comment

Dear all
My server received following information around 2 months from event viewer.
Error: 17883, Severity: 1, State: 0
The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
(We have not a scheduler 3 in my sql)
After that, i disable symantec antivirus on my server, then this error do
not occur again. i dont know why?
Server :windows 2003
SQL : SQL 2000 SP3Hi
Generally speaking, it is not a good idea to have anti-virus products on a
SQL Server.
http://support.microsoft.com/kb/309422/en-us
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
> Dear all
> My server received following information around 2 months from event
> viewer.
> Error: 17883, Severity: 1, State: 0
> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context 0x00CDA950
> (We have not a scheduler 3 in my sql)
> After that, i disable symantec antivirus on my server, then this error do
> not occur again. i dont know why?
> Server :windows 2003
> SQL : SQL 2000 SP3
>|||Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi
> Generally speaking, it is not a good idea to have anti-virus products on a
> SQL Server.
> http://support.microsoft.com/kb/309422/en-us
>
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
>> Dear all
>> My server received following information around 2 months from event
>> viewer.
>> Error: 17883, Severity: 1, State: 0
>> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context
>> 0x00CDA950
>> (We have not a scheduler 3 in my sql)
>> After that, i disable symantec antivirus on my server, then this error do
>> not occur again. i dont know why?
>> Server :windows 2003
>> SQL : SQL 2000 SP3
>>
>|||Do you have any document ?
"bass_player [SBS-MVP]" wrote:
> Or better yet, exclude your MDF/NDF/LDF files in your anti-virus scans
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eFL7JtiuHHA.4764@.TK2MSFTNGP05.phx.gbl...
> > Hi
> >
> > Generally speaking, it is not a good idea to have anti-virus products on a
> > SQL Server.
> >
> > http://support.microsoft.com/kb/309422/en-us
> >
> >
> > --
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> > "SQL" <SQL@.discussions.microsoft.com> wrote in message
> > news:98063DA6-5633-4508-9F38-7A84E18657A9@.microsoft.com...
> >> Dear all
> >>
> >> My server received following information around 2 months from event
> >> viewer.
> >>
> >> Error: 17883, Severity: 1, State: 0
> >> The Scheduler 3 appears to be hung. SPID 0, ECID 0, UMS Context
> >> 0x00CDA950
> >>
> >> (We have not a scheduler 3 in my sql)
> >> After that, i disable symantec antivirus on my server, then this error do
> >> not occur again. i dont know why?
> >> Server :windows 2003
> >> SQL : SQL 2000 SP3
> >>
> >>
> >
> >
>
>

Please advise

Tab1 and Tab2 has the following info.ID column in Tab1 and Tab2 has
matching values.
Tab3 should have the info as below.
Tab1
--
ID Val1 Val2
1 qqq sa
2 aaa fa
3 ddd we
Tab2
--
ID F1
1 A
1 B
1 C
1 D
2 F
2 G
3 H
Output Should be as blelow, without using the functions please advise the
sql query.
Tab3
--
ID Val1 Val2 F1
1 qqq sa A,B,C,D
2 aaa fa F,G
3 ddd we H
Thanks in advance.See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Plase help

Hello,
I have a developer who developed the following stored procedure.
My question is since it started with a Begin Transaction and if the record
exists in the EXISTS statement and it will return an ID without commit /
rollback, does he need to either commit or rollback the transaction?
begin transaction
if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDate
= convert(char(11),getdate(),106))
Begin
Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
CreateDate = convert(char(11),getdate(),106)
Return @.OpportunityId
End
else
Begin
Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
If @.@.Error <> 0
Begin
Rollback Transaction
Return -1
End
RETURN SCOPE_IDENTITY( )
Commit Transaction
End
GOEd,
Try moving the BEGIN TRAN down in the code to just prior the DML (INSERT)
statment.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BA6A49DF-BC78-412D-BC86-9D456BFDDBE1@.microsoft.com...
> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the
> record
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||There is only one insert statement that I can see. Transaction is not
necessary at all in this case.
It is also not necessary to perform the same query two times in a row...
select the value once, then check to see if it is null.
SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
CreateDate = convert(char(11),getdate(),106)
IF @.OpportunityID IS NOT NULL Return @.OpportunityId
ELSE
BEGIN
INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
IF @.@.Error <> 0 RETURN -1
ELSE RETURN SCOPE_IDENTITY()
END
END
GO
John Scragg
"Ed" wrote:

> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the reco
rd
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDa
te
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||However, the is a risk that the row is deleted after the SELECT and before t
he INSERT. To eliminate
that risk, isolation SERIALIZABLE should be used, which requires a transacti
on (to do its work)...
:-)
Also, the code doesn't catch if the SELECT returns several rows. This is why
I prefer SET @.var =
(SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
> There is only one insert statement that I can see. Transaction is not
> necessary at all in this case.
> It is also not necessary to perform the same query two times in a row...
> select the value once, then check to see if it is null.
> SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
> AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
> CreateDate = convert(char(11),getdate(),106)
> IF @.OpportunityID IS NOT NULL Return @.OpportunityId
> ELSE
> BEGIN
> INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> IF @.@.Error <> 0 RETURN -1
> ELSE RETURN SCOPE_IDENTITY()
> END
> END
> GO
> John Scragg
> "Ed" wrote:
>|||Tibor, you make some good points. I am afraid I was not as clear and the
formatting of SQL in the newsgroup does not help :-) Let me try to be more
clear.
The original proc was apparently designed to perform 1 of 2 action. Either
(1) return the ID of an existing row, or (2) in the absance of a row, insert
a new row and return the ID of the new row.
If the row is deleted after the select it should not matter (as far as the
proc is concerned) since there is nothing else happening. If the select
returns a value, the proc returns the coulmn value selected and the INSERT
statement is ignored. The way it was written originally there were two
identical selects. First to check IF EXISTS and then the same query to get
the value. This did provide a tiny window for a row to be deleted between
the two selects, but my opinion was that it is simply ineffcient to query
twice for the same result :-)
As for the possible error on multiple rows, you are absolutely right. It
can error. It is the same as the developer's original code, and so I am
assuming that they have confirmed the values in the where clause will provid
e
a unique row (through constraints, rules, etc.). If not, then he has this
problem in his original code as well.
John
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||That is a good point that I never think about...
How am I able to control/put the Transaction to even lock the Select
statement to make sure when the record/ID is returned, there is no one else
delete the record already?
Thanks
Ed
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||Sorry, John. I misinterpreted the logic. It looked like me as if the row exi
sts, get the values
*and* do an insert. I didn't read the original code carefully enough. I catc
hed that you picked up
on the "read twice" issue, but thought that a hole was left as I assumed ano
ther logic. My bad...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:7C58CD9A-0D47-4D4D-A68B-15F5D64D65B2@.microsoft.com...
> Tibor, you make some good points. I am afraid I was not as clear and the
> formatting of SQL in the newsgroup does not help :-) Let me try to be mor
e
> clear.
> The original proc was apparently designed to perform 1 of 2 action. Eithe
r
> (1) return the ID of an existing row, or (2) in the absance of a row, inse
rt
> a new row and return the ID of the new row.
> If the row is deleted after the select it should not matter (as far as the
> proc is concerned) since there is nothing else happening. If the select
> returns a value, the proc returns the coulmn value selected and the INSERT
> statement is ignored. The way it was written originally there were two
> identical selects. First to check IF EXISTS and then the same query to ge
t
> the value. This did provide a tiny window for a row to be deleted between
> the two selects, but my opinion was that it is simply ineffcient to query
> twice for the same result :-)
> As for the possible error on multiple rows, you are absolutely right. It
> can error. It is the same as the developer's original code, and so I am
> assuming that they have confirmed the values in the where clause will prov
ide
> a unique row (through constraints, rules, etc.). If not, then he has this
> problem in his original code as well.
> John
> "Tibor Karaszi" wrote:
>

Plan Maintenance will not execute

My plan Maintenance to Optimize, TRN Backup and Reporting
has been running for several months. A few days ago, I
started getting the following in "View Job History":
"Executed as user: domain\Administrator. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step failed."
There is no log file created, even though I requested one,
which leads me to believe the Job never actually started.
The Windows 2000 Applications Log has the following:
"SQL Server Scheduled Job 'Optimizations Job for DB
Maintenance Plan 'Test 1''
(0x529723559D047845AB998AFF9BDA397F) - Status: Failed -
Invoked on: 2004-07-14 22:30:00 - Message: The job
failed. The Job was invoked by Schedule 42 (Schedule 1).
The last step to run was step 1 (Step 1)."
When I try and run the command in Query Analyzer, I get
the following:
"Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed."
The recovery model is "Bulk-Logged"
Does anyone have any Idea what has gone wrong in this
maintenance Plan?Hi,
By any chance did you changed the recovery model for a database to SIMPLE
and then turned back to BULK_LOGGED. In this
case you have to do a FULL database backup for that database.
command:
--
BACKUP database <dbname> to disk='d:\backup\dbname.bak' with init
Thanks
Hari
MCDBA
"Ken Bitner" <anonymous@.discussions.microsoft.com> wrote in message
news:2d53601c46a17$38dd0f20$a501280a@.phx
.gbl...
> My plan Maintenance to Optimize, TRN Backup and Reporting
> has been running for several months. A few days ago, I
> started getting the following in "View Job History":
> "Executed as user: domain\Administrator. sqlmaint.exe
> failed. [SQLSTATE 42000] (Error 22029). The step failed."
> There is no log file created, even though I requested one,
> which leads me to believe the Job never actually started.
> The Windows 2000 Applications Log has the following:
> "SQL Server Scheduled Job 'Optimizations Job for DB
> Maintenance Plan 'Test 1''
> (0x529723559D047845AB998AFF9BDA397F) - Status: Failed -
> Invoked on: 2004-07-14 22:30:00 - Message: The job
> failed. The Job was invoked by Schedule 42 (Schedule 1).
> The last step to run was step 1 (Step 1)."
> When I try and run the command in Query Analyzer, I get
> the following:
> "Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed."
> The recovery model is "Bulk-Logged"
> Does anyone have any Idea what has gone wrong in this
> maintenance Plan?
>
>|||No. The Recovery Model has always been "Bulk-Logged".
Also, I have take a full backup every day via the same
command you sent me.

>--Original Message--
>Hi,
>By any chance did you changed the recovery model for a
database to SIMPLE
>and then turned back to BULK_LOGGED. In this
>case you have to do a FULL database backup for that
database.
>command:
>--
>BACKUP database <dbname> to disk='d:\backup\dbname.bak'
with init
>
>--
>Thanks
>Hari
>MCDBA
>
>"Ken Bitner" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2d53601c46a17$38dd0f20$a501280a@.phx
.gbl...
Reporting[vbcol=seagreen]
failed."[vbcol=seagreen]
one,[vbcol=seagreen]
started.[vbcol=seagreen]
1).[vbcol=seagreen]
>
>.
>