out-of-range datetime value

Hi,

I have a page working with not problem since 3 years, and now that we've moved our database from a SQL Server 2000 to SQL Server 7 and i receive this error everywhere a date is used.

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

The error append on this line "While myReader.Read()" .. what can i do to solve this problem ?

Thank you

This may be caused by date format that differs from the SQL2000 instance to SQL7.0 instance. Try to change the DATEFORMAT option like this (can be set to mdy,ydm,and ymd) and test again:

SET DATEFORMAT mdy
GO

You can take a look at this link about converting to datetime type:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp?frame=true

sql

Outer Join syntax

I am now working on SQL Server 2000 having had previous experience on
a different database. Both of the OUTER JOIN syntaxes is different
from what I am used to and I am finding it slightly confusing.

For example, given two tables :

wipm_tbl_mi
wipm_tbl_wi (which may not have data in it for a specific record that
exists in the first table.)

If I use the old style syntax :

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi , wipm_tbl_wi wi
WHERE mi.workitemid *= wi.workitemid
AND mi.workitemid = 1
AND wi.dataname = 'XXX'

I get back

1,NULL

when there is no matching record in wipm_tbl_wi, which is what I
expect.

However, if I try to use the SQL-92 syntax

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi
LEFT OUTER JOIN wipm_tbl_wi wi
ON mi.workitemid = wi.workitemid
WHERE mi.workitemid = 1
AND wi.dataname = 'XXX'

I don't get anything back. Please can someone help me understand what
is wrong with the bottom query.

Thank you,

Martin"Martin" <cook_ml@.hotmail.com> wrote in message
news:ac63e8cd.0401300807.25d1f38f@.posting.google.c om...
> I am now working on SQL Server 2000 having had previous experience on
> a different database. Both of the OUTER JOIN syntaxes is different
> from what I am used to and I am finding it slightly confusing.
> For example, given two tables :
> wipm_tbl_mi
> wipm_tbl_wi (which may not have data in it for a specific record that
> exists in the first table.)
> If I use the old style syntax :
> SELECT mi.workitemid, wi.datavalue
> FROM wipm_tbl_mi mi , wipm_tbl_wi wi
> WHERE mi.workitemid *= wi.workitemid
> AND mi.workitemid = 1
> AND wi.dataname = 'XXX'
> I get back
> 1,NULL
> when there is no matching record in wipm_tbl_wi, which is what I
> expect.
> However, if I try to use the SQL-92 syntax
> SELECT mi.workitemid, wi.datavalue
> FROM wipm_tbl_mi mi
> LEFT OUTER JOIN wipm_tbl_wi wi
> ON mi.workitemid = wi.workitemid
> WHERE mi.workitemid = 1
> AND wi.dataname = 'XXX'
> I don't get anything back. Please can someone help me understand what
> is wrong with the bottom query.
> Thank you,
> Martin

*= is the obsolete proprietary syntax used for outer joins by SQL Server
prior to version 6.5. OUTER JOIN is the Standard SQL way of doing
outer joins and should always be used. However, it's not simply syntactically
different but semantically different than *=. With outer joins, the join condition
must be evaluated separate from the condition applied to the rows resulting
from the join. The Standard SQL solution allows for this by being able to
specifiy the join condition for the outer join in the FROM clause and
the condition to be applied to the resulting rows of the outer join in the
WHERE clause. The *= syntax at best doesn't make this clear and at
worst won't allow you to specify the result you're looking for since both
kinds of conditions, for the join and the restriction of subsequent rows,
are specified in the WHERE clause.

It's always helpful to provide sample data in the form of INSERT
statements so a proposed solution can be tested, but try

SELECT mi.workitemid, wi.datavalue
FROM wipm_tbl_mi mi
LEFT OUTER JOIN
wipm_tbl_wi wi
ON mi.workitemid = wi.workitemid AND
wi.dataname = 'XXX'
WHERE mi.workitemid = 1

From the LEFT OUTER JOIN condition, every row of mi will be joined to
all rows in wi where mi.workitemid = wi.workitemid and wi.dataname = 'XXX'.
All rows of mi that don't satisfy this condition are joined to a single row
of all NULL values to indicate that there's no matching row from wi.
From the resulting outer join, the WHERE clause only keeps those rows
where mi.workitemid = 1. This will keep rows where wi.dataname is 'XXX'
or NULL, which is your desired result.

Regards,
jag|||Thank-you, Jag. Your explanation has helped me understand the general
point, and your suggested SQL has solved my specific problem.

Martin

OUTER JOIN not working?

I have this query

select
created_date,
tyg_aging_due_dates.object_type,
tyg_aging_due_dates.due_date
from
#tyg_aging_service_metrics
right join
tyg_aging_due_dates
on tyg_aging_due_dates.due_date =
#tyg_aging_service_metrics.due_date
and tyg_aging_due_dates.object_type =
#tyg_aging_service_metrics.object_type
order by PS, tyg_aging_due_dates.due_date

Basically the table tyg_aging_due_dates have this for data
object_typedue_date
------
report1/1/2005
report1/1/2006
report1/1/2007
image2/1/2006
image2/4/2006

The temporary table retuns something similar
created_dateobject_typedue_date
----------
6/1/2006report1/1/2005
6/10/2006image2/4/2006

So basically I want to join the two tables and for the due date's that
are missing from my temporary table, I want to display NULL (thus the
right join).

So my query would return
created_dateobject_typedue_date
----------
6/1/2006report1/1/2005
NULLreport1/1/2006
NULLreport1/1/2007
NULLimage2/1/2006
6/10/2006image2/4/2006

The date fields are of smalldatetime. Object type is a varchar(15).

When I run my query though, I get only 2 rows back. No matter if I
switch the right join to a left, full, inner, whatever. I still only
get 2 rows back. Is this a known issue?

I am running Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May
3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.|||rhaazy wrote:

Quote:

Originally Posted by

Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.

Scripts are as follows:

CREATE TABLE [dbo].[tyg_aging_due_dates] (
[due_date] [smalldatetime] NOT NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL
) ON [PRIMARY]

-- Data
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-10', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-14', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-11', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-24', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-28', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-25', 'yg_decisionnote')

CREATE TABLE #tyg_aging_service_metrics (
[created_date] [datetime] NOT NULL DEFAULT (getdate()),
[due_date] [smalldatetime] NOT NULL ,
[res_ref_id] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL) ON [PRIMARY]

-- Data
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-08-14 00:00:00', '1234', 'yg_report')
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-09-25 00:00:00', '5524', 'yg_decisionnote')

So you see my temp table only has two records. I want to return all
the rows from the due_dates table regardless if they match those
records in tyg_aging_service_metrics.|||For the output, for each row in tyg_aging_service_matrix, I want to
return all the records for tyg_aging_due_dates

So in my example, there should be 10 rows returned. Their will be 8
created_date fields that are null when it's done. This is what i'm
trying to do.

kamin@.yankeegroup.com wrote:

Quote:

Originally Posted by

rhaazy wrote:

Quote:

Originally Posted by

Please post propper DDL and table creation scripts and some sample data
so that we may have a better understanding of the problem.

Scripts are as follows:
>
CREATE TABLE [dbo].[tyg_aging_due_dates] (
[due_date] [smalldatetime] NOT NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL
) ON [PRIMARY]
>
-- Data
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-10', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-14', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-11', 'yg_report')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-07-24', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-08-28', 'yg_decisionnote')
insert into tyg_aging_due_dates (due_date, object_type)
values ('2006-09-25', 'yg_decisionnote')
>
>
>
CREATE TABLE #tyg_aging_service_metrics (
[created_date] [datetime] NOT NULL DEFAULT (getdate()),
[due_date] [smalldatetime] NOT NULL ,
[res_ref_id] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL ,
[object_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT
NULL) ON [PRIMARY]
>
-- Data
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-08-14 00:00:00', '1234', 'yg_report')
insert into #tyg_aging_service_metrics (created_date, due_date,
res_ref_id, object_type)
values ('7/1/2006', '2006-09-25 00:00:00', '5524', 'yg_decisionnote')
>
>
So you see my temp table only has two records. I want to return all
the rows from the due_dates table regardless if they match those
records in tyg_aging_service_metrics.

|||On 7 Jul 2006 11:04:59 -0700, kamin@.yankeegroup.com wrote:

Quote:

Originally Posted by

>For the output, for each row in tyg_aging_service_matrix, I want to
>return all the records for tyg_aging_due_dates
>
>So in my example, there should be 10 rows returned. Their will be 8
>created_date fields that are null when it's done. This is what i'm
>trying to do.

Hi Kamin,

I don't know why you expect 10 rows, since the sample data for
tyg_aging_due_dates has jjust 6 rows. Maybe you meant to add four more?

Anmyway, I tried the query yoou had in your first post, and I got an
error because you attempt to order by a non-existing column named PS.
After removing the ORDER BY clause, I got six rows. Two with a valid
date in created_date; the remaining four had NULLS:

created_date object_type due_date
-------- ----- --------
NULL yg_report 2006-07-10 00:00:00
2006-07-01 00:00:00.000 yg_report 2006-08-14 00:00:00
NULL yg_report 2006-09-11 00:00:00
NULL yg_decisionnote 2006-07-24 00:00:00
NULL yg_decisionnote 2006-08-28 00:00:00
2006-07-01 00:00:00.000 yg_decisionnote 2006-09-25 00:00:00

--
Hugo Kornelis, SQL Server MVP

Outer Join not working when using SQL-92

All,

I have a perplexing problem that I hope someone can help me with.

I have the following table struct:

Permission
------
PermissionId
Permission
Description

UserPermission
------
PermissionId
UserId
Active

I am attempting to retrieve all records from the permission table
whether there is a match on UserPermission.PermissionId or not.
Therefore I implemented this query, which does not produce the results
that I expect:

SELECT p.Permission,
up.Active

FROM Permission p
LEFT OUTER JOIN UserPermission up
ONp.[Id] = up.PermissionId
WHERE up.UserId = 3

However, if I exec this query, it works as it is supposed to:
SELECTp.Permission,
up.Active

FROM Permission p, UserPermission up
WHEREp.[Id] *= up.PermissionId AND up.UserId = 3

In the first query, only the records that match on "permissionId" are
returned, in the second all records are returned from the Left table
and those records that do not have matching columns are set to null, as
it should be. My question is, what have I done wrong here?

I am running MS-SQLServer 2000The problem is with the way you are using SQL-92 standard.

Instead use the query as follows:

SELECT P.Permission, up.Active
FROM Permission p
LEFT OUTER JOIN UserPermission up
ON p.permissionid = up.PermissionId
AND up.UserId = 3

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||Thanks Debian, that did it. Is it then incorrect to use the WHERE
clause when using an outer join? I've looked at books on-line and
could not find a reference to using WHERE clauses with outer joins,
however they appear to be a typical part of inner joins.|||Here is a "cut & paste" lecture:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||thilbert (thilbert@.gmail.com) writes:
> Thanks Debian, that did it. Is it then incorrect to use the WHERE
> clause when using an outer join? I've looked at books on-line and
> could not find a reference to using WHERE clauses with outer joins,
> however they appear to be a typical part of inner joins.

Incorrect and incorrect, it has a different meaning. The FROM clause
lasts all the way to WHERE, so you have

FROM a LEFT JOIN b ON a.col = b.col

Then the WHERE is applied. But if you say

WHERE b.othercol = 3

you are effectively filter out the rows from a which did not have a
matching row in b.

If you say

FROM a LEFT JOIN b ON a.col = b.col AND b.othercol = 3

The condition of othercol becomes part of the join, so that for
rows where a.col = b.col but b.othercol = 2 you will get NULL
values for b.*.

You can also say:

FROM a LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 3 OR b.col IS NULL

But this gives a different result. Here the rows where a.col = b.col
but b.othercol = 2 will be removed from the result set.

See also Celko treatise on the subject.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Outer Join not working

"Steve Kass" <skass@.drew.edu> wrote in message
news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Only two rows of the OUTER JOIN satisfy the WHERE clause of
> the query, (where JobID = 3720). If you want all the rows of the
> outer join, including those with JobID values other than 3720,
> don't include a WHERE clause to restrict what you get to rows with
> JobID = 3720. You may want that condition in the ON clause
> of the outer join, but since you only tell us what you don't want,
> not what you do want, I won't speculate.
I thought I did tell you what I wanted.

> In short, a SELECT query's WHERE clause is always enforced.
> The ON clause of an OUTER JOIN is the one that may not be
> true for all the result set rows.
That was what I was looking for. I always have trouble with Outer Joins.
What worked was:
select S.PositionID,s.QuestionUnique,Question,QuestionType,
Weight,AnswerTime,jobID
from SkillsTest as S
left outer join ApplicantTest j
on (j.questionUnique = s.questionUnique and JobID = 3720)
where PositionID = 25
If I leave out the where clause, it almost works, but I also get all the
rows with any PositionID, so I needed to add that.
I am still not sure why I had to move the JobID test into the "on" clause,
but it worked. I will have to study it more to understand it.
Thanks,
Tom
> SK
>
> tshad wrote:
>
,
,
53
12
from
all
(as
would
s.questionUnique)Might interest you:
http://support.microsoft.com/defaul...kb;en-us;176480
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"tshad" <tfs@.dslextreme.com> wrote in message
news:ur$XX5wGFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I thought I did tell you what I wanted.
>
> That was what I was looking for. I always have trouble with Outer Joins.
> What worked was:
> select S.PositionID,s.QuestionUnique,Question,QuestionType,
> Weight,AnswerTime,jobID
> from SkillsTest as S
> left outer join ApplicantTest j
> on (j.questionUnique = s.questionUnique and JobID = 3720)
> where PositionID = 25
> If I leave out the where clause, it almost works, but I also get all the
> rows with any PositionID, so I needed to add that.
> I am still not sure why I had to move the JobID test into the "on" clause,
> but it worked. I will have to study it more to understand it.
> Thanks,
> Tom
>
first.
NULL
> ,
NULL
> ,
2
7
> 53
8
1
1
8
0
0
> 12
2
Answer
> from
want
> all
> (as
> would
> s.questionUnique)
>|||On Thu, 24 Feb 2005 23:49:52 -0800, tshad wrote:
(snip)
>I am still not sure why I had to move the JobID test into the "on" clause,
>but it worked. I will have to study it more to understand it.
Hi Tom,
That's because of the order in which a SELECT statement is evaluated.
The order of evaluation whould, in theory (most DBMS's will reshuffle to
optimize, as long as it doesn't change the end result), be as follows:
1. Evaluate FROM clause. Make a temporary result set that holds all
columns from all tables in the FROM clause, with all the rows that
satisfy the ON conditions.
Rows that don't match the ON condition are not included if it's an INNER
JOIN; for OUTER JOINS, the row from the outer table will be retained
with NULL values substituted for the columns from the inner table.
2. Evaluate WHERE clause. Check each row in the temporary result set
from the previous step against the WHERE condition. The row is retained
only if the WHERE condition evaluates to TRUE. If it evaluates to FALSE
or UNKNOWN, the row is removed from the result set.
3. After this, the GROUP BY, HAVING, SELECT and ORDER BY are processed
(in that order) - but these are not relevant for your question.
In your original question, you had a WHERE clause
where JobID = 3720
where the JobID column is from the inner table of an outer join. For all
rows retained because of the outer join, JobID is NULL. By definition,
the expression NULL = 3270 evaluates to FALSE. Therefor, all rows
retained by the outer join are subsequently removed by the where clause,
effectively changing your query to an inner join.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Outer Join Not Working

I'm working with SQL Reporting Services 2005. I'm trying to list
opportunities that do not have a future open activity.
Below is my SQL statement. StateCodes of 0 are open opportunities and open
activities
SELECT Opportunity.name
FROM Activity RIGHT OUTER JOIN Opportunity
ON Activity.RegardingObjectId = Opportunity.opportunityid
WHERE (Activity.StateCode = 0) AND
(Activity.ScheduledStart > { fn NOW() }) AND
(Opportunity.statecode = 0)
It is only returning matched records when I run the query in the Data tab.
There are no instances of an Opportunity without an activity (and we have
loads!)
What am I doing wrong?It has been my experience that when the data tab works and the report
doesn't, and no errors are present, the setting of parameters is usually the
culprit. If you set default parameters, make sure that "O" and 0 (zero) are
not confused.
"Cindy" wrote:
> I'm working with SQL Reporting Services 2005. I'm trying to list
> opportunities that do not have a future open activity.
> Below is my SQL statement. StateCodes of 0 are open opportunities and open
> activities
> SELECT Opportunity.name
> FROM Activity RIGHT OUTER JOIN Opportunity
> ON Activity.RegardingObjectId = Opportunity.opportunityid
> WHERE (Activity.StateCode = 0) AND
> (Activity.ScheduledStart > { fn NOW() }) AND
> (Opportunity.statecode = 0)
> It is only returning matched records when I run the query in the Data tab.
> There are no instances of an Opportunity without an activity (and we have
> loads!)
> What am I doing wrong?
>
>|||In my case the data tab is NOT working.|||SSRS is just flaky as ever.
I would reccomend calling Microsoft once an hour and demanding to know
when SP2 is coming out since this product is basically just not
usable-- since it's soooooo buggy
Sorry; I just dont want to candy-coat it any longer
-Aaron
Cindy wrote:
> In my case the data tab is NOT working.|||Hi,
My understanding of your issue is:
Assume that your opportunity table has the following records:
OpportunityName OpportunityID StateCode
O1 1 0
O2 2 1
O3 3 0
O4 4 0
your activity table has the following records
Owner RegardingObjectId StateCode
W1 1 1
W2 2 0
W3 3 0
W4 4 0
You wanted to get the result:
OpportinityName Owner OpportunityID StateCode
O1 NULL 1 0
O3 W3 3 0
O4 W4 4 0
If I have misunderstood, please let me know.
Please try the following statement:
SELECT Opportunity.name
FROM Activity RIGHT OUTER JOIN Opportunity
ON ( Activity.RegardingObjectId = Opportunity.opportunityid AND
Activity.StateCode = 0 AND Activity.ScheduledStart > { fn NOW() })
WHERE Opportunity.statecode = 0
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I finally got this working with some help from the CRM Developer
newsgroup -- but the answer was all about SQL syntax. See below:
select opportunityid, name from FilteredOpportunity
where opportunityid not in
(select regardingobjectid from filteredactivitypointer where
(regardingobjecttypecode = 3) and (statecode = 0))

Outer Join Issues - Please Help

I am working on the record source for a report to produce monthly customer statements. Thanks to everyone here, I have been able to overcome many, many hurdles I have encountered. I just have one last issue I need to get resolved (famous last words, I know).

At the heart of my record source are two entities:

1. AROPNFIL - A table of all Accounts Receivable items (invoices, payments, credits, etc.)
2. fnBalance(@.StartDate) - A table valued function that gives me the starting balance for a customer on a particular date by summing the net Amounts of all items in the AROPNFIL that occured before the @.startdate. (i.e. Invoices have a positive amount, payments have a negative amount, so they net out so that only unpaid invoices remain.)

These are joined on customer_number. The query also selects only the rows in the AROPNFIL table that are between @.StartDate and @.EndDate for the month.

Everything was going great until...I realized that no statement was being created for a customer if they didn't have any activity in the current month, even though they had a starting balance.

So I tried an outer join, telling the query to select all rows from the fnBalance table. But that still wasn't returning the rows I wanted. After several hours of cursing and feeling the need for a drink, I realized why that wasn't working. Because the customer in question, let's call it "Coast01" had rows in the AROPNFIL table before the @.StartDate, it was seeing that as having completed the join and hence no reason to return a row for that starting balance.

So, this is what I need, a way to write what I am going to try to interpret as the following.

Select *
From (AROPNFIL WHERE Doc_date BETWEEN @.StartDate AND @.EndDate) RIGHT OUTER JOIN fnBalance(@.StartDate)

Does that make sense? I need the join with fnBalance to take place after the query has selected only the rows between @.startdate and @.enddate so that the row for the balance for "Coast01" will appear even though there is no activity in the current month.

But I am at a loss for how to write that in the FROM and WHERE clauses. I guess one way would be to create a query selecting rows from AROPNFIL WHERE doc_date BETWEEN @.StartDate AND @.EndDate and then create another query performing the outer join between that query and fnBalance, but I don't really want to do that because the rest of my record source query is actually a lot more complicated than what I've explained here, and I'd rather not have to create additional queries if I don't have to. But if you guys tell me there is no other way, I'll believe you.

Thank you all!Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:

SELECT *
FROM (SELECT * FROM
AROPNFIL WHERE Doc_date BETWEEN @.StartDate AND @.EndDate) RIGHT OUTER JOIN
fnBalance(@.StartDate) StartBalance ON AROPNFIL.cus_no = StartBalance.cus_no

Obviously, I'll get rid of the *s, but no one here cares about what columns I'm actually pulling out.|||This is correct, I got it to work!!!

Outer Join - shifting result set.

I'm a quantitative securities analyst working with Compustat data
(company fiscal reports and pricing feeds).

My coworker came across a problem that we fixed, but I'd like to
understand 'why' it was happening and just don't get it yet.

Here's the starting query (reduced to simple prefixes):

--INITIAL--

declare @.mthDate datetime
set @.mthDate = (select max(datadate) from t)
declare @.wkDate datetime
set @.wkDate = (select max(datadate) from z)

Select
...
from
z
left join a on a.idA = z.idA and a.idB = z.idB
and a.datadate = z.datadate
left join b on b.idA = z.idA and b.idB = z.idB
and b.datadate = @.mthDate
left join c on c.idA = z.idA and c.idB = z.idB
and c.datadate = @.mthDate
left join d on d.idA = z.idA and d.idB = z.idB
and d.datadate = z.datadate
left join e on e.idA = z.idA
and e.datadate = @.mthDate
left join f on f.idA = e.idA and f.datadate=e.date2
left join g on g.idA = e.idA and g.datadate=e.date2
left join h on h.idA = z.idA
left join k on k.ticker = z.ticker
left join m on m.idA = z.idA and m.idB=z.idB
where
z.datadate = @.wkDate
<..some other expression filters...>
and k.ticker is null

--END INITIAL----

As you can see 'z' is the main table that things are linked to via
outer joins (our security master). Table 'k' has a list of securities
that we wish not to have results for.

There are 77 entries in table k and 4933 in table z for that given
time. We'd expect 4856 to be in this, but no. it's 4400, and then the
next time you run it (no changes whatsover) it's 2312, and so on.
Every time you execute you get a different record count.

My thought/and fix was to move the (k.ticker) predicate out of the
where clause and get a differenced set from z using NOT EXISTS:

--AMENDED-----
from
(z where not exists(select * from k where k.ticker=y.ticker)) y
left join a on a.idA = y.idA and a.idB = y.idB
and a.datadate = y.datadate
left join b on b.idA = y.idA and b.idB = y.idB
and b.datadate = @.mthDate
left join c on c.idA = y.idA and c.idB = y.idB
and c.datadate = @.mthDate
left join d on d.idA = y.idA and d.idB = y.idB
and d.datadate = y.datadate
left join e on e.idA = y.idA
and e.datadate = @.mthDate
left join f on f.idA = e.idA and f.datadate=e.date2
left join g on g.idA = e.idA and g.datadate=e.date2
left join h on h.idA = y.idA
left join k on k.ticker = y.ticker
left join m on m.idA = y.idA and m.idB=y.idB
where
y.datadate = @.wkDate
<..some other expression filters...
--------

And this works. It's stable now.

I'm hoping someone here can help me up the wisdom curve by explaining
to me 'why' the recordset kept changing before.

My guess is that the cost-based optimizer was resorting the outer joins
and handing back different sets as a result, but i want to understand,
and thought i'd come to this group for help.

I appreciate your time and look forward to replies.

Greg McIntireGreg,

it sounds like a bug to me. If the data doesn't change, then the query
result shouldn't change.

What version and service pack are you running? Can you create a
(simplified?) script that reproceduces the problem?

Gert-Jan

Greg wrote:
> I'm a quantitative securities analyst working with Compustat data
> (company fiscal reports and pricing feeds).
> My coworker came across a problem that we fixed, but I'd like to
> understand 'why' it was happening and just don't get it yet.
> Here's the starting query (reduced to simple prefixes):
> --INITIAL--
> declare @.mthDate datetime
> set @.mthDate = (select max(datadate) from t)
> declare @.wkDate datetime
> set @.wkDate = (select max(datadate) from z)
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null
> --END INITIAL----
> As you can see 'z' is the main table that things are linked to via
> outer joins (our security master). Table 'k' has a list of securities
> that we wish not to have results for.
> There are 77 entries in table k and 4933 in table z for that given
> time. We'd expect 4856 to be in this, but no. it's 4400, and then the
> next time you run it (no changes whatsover) it's 2312, and so on.
> Every time you execute you get a different record count.
> My thought/and fix was to move the (k.ticker) predicate out of the
> where clause and get a differenced set from z using NOT EXISTS:
> --AMENDED-----
> from
> (z where not exists(select * from k where k.ticker=y.ticker)) y
> left join a on a.idA = y.idA and a.idB = y.idB
> and a.datadate = y.datadate
> left join b on b.idA = y.idA and b.idB = y.idB
> and b.datadate = @.mthDate
> left join c on c.idA = y.idA and c.idB = y.idB
> and c.datadate = @.mthDate
> left join d on d.idA = y.idA and d.idB = y.idB
> and d.datadate = y.datadate
> left join e on e.idA = y.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = y.idA
> left join k on k.ticker = y.ticker
> left join m on m.idA = y.idA and m.idB=y.idB
> where
> y.datadate = @.wkDate
> <..some other expression filters...>
> --------
> And this works. It's stable now.
> I'm hoping someone here can help me up the wisdom curve by explaining
> to me 'why' the recordset kept changing before.
> My guess is that the cost-based optimizer was resorting the outer joins
> and handing back different sets as a result, but i want to understand,
> and thought i'd come to this group for help.
> I appreciate your time and look forward to replies.
> Greg McIntire|||It's Version 8.00.194

While my ego would like to say it's not me, it's Microsoft, I'm pretty
doubtful. I think it's much more likely that it's a mental mistake...

Greg|||Well, could be, but I would install a service pack anyway. 8.00.194
means you have not installed any SQL-Server service pack, which
basically means you are missing all bug fixes of the last 5 years...

Gert-Jan

Greg wrote:
> It's Version 8.00.194
> While my ego would like to say it's not me, it's Microsoft, I'm pretty
> doubtful. I think it's much more likely that it's a mental mistake...
> Greg|||alright will talk to our tech guy. thanks a bunch.

Greg|||Greg (jacore70@.hotmail.com) writes:
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null

The part with e, f and g looks suspicious to me. Not if I can tell
whether they are the cause of your SELECT:s returning a different
number. But you might want to have said:

left join (e
join f on f.idA = e.idA and f.datadate=e.date2
join g on g.idA = e.idA and g.datadate=e.date2)
on e.idA = z.idA
and e.datadate = @.mthDate

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Greg" <jacore70@.hotmail.com> wrote in message
news:1110566766.025803.171120@.l41g2000cwc.googlegr oups.com...
> I'm a quantitative securities analyst working with Compustat data
> (company fiscal reports and pricing feeds).
> My coworker came across a problem that we fixed, but I'd like to
> understand 'why' it was happening and just don't get it yet.
> Here's the starting query (reduced to simple prefixes):
>
> --INITIAL--
> declare @.mthDate datetime
> set @.mthDate = (select max(datadate) from t)
> declare @.wkDate datetime
> set @.wkDate = (select max(datadate) from z)
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null
> --END INITIAL----
> As you can see 'z' is the main table that things are linked to via
> outer joins (our security master). Table 'k' has a list of securities
> that we wish not to have results for.
> There are 77 entries in table k and 4933 in table z for that given
> time. We'd expect 4856 to be in this, but no. it's 4400, and then the
> next time you run it (no changes whatsover) it's 2312, and so on.
> Every time you execute you get a different record count.
> My thought/and fix was to move the (k.ticker) predicate out of the
> where clause and get a differenced set from z using NOT EXISTS:
>
> --AMENDED-----
> from
> (z where not exists(select * from k where k.ticker=y.ticker)) y
> left join a on a.idA = y.idA and a.idB = y.idB
> and a.datadate = y.datadate
> left join b on b.idA = y.idA and b.idB = y.idB
> and b.datadate = @.mthDate
> left join c on c.idA = y.idA and c.idB = y.idB
> and c.datadate = @.mthDate
> left join d on d.idA = y.idA and d.idB = y.idB
> and d.datadate = y.datadate
> left join e on e.idA = y.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = y.idA
> left join k on k.ticker = y.ticker
> left join m on m.idA = y.idA and m.idB=y.idB
> where
> y.datadate = @.wkDate
> <..some other expression filters...>
> --------
> And this works. It's stable now.
> I'm hoping someone here can help me up the wisdom curve by explaining
> to me 'why' the recordset kept changing before.
> My guess is that the cost-based optimizer was resorting the outer joins
> and handing back different sets as a result, but i want to understand,
> and thought i'd come to this group for help.
> I appreciate your time and look forward to replies.
> Greg McIntire

Your guess sounds pretty reasonable to me.

If you use the show query plan setting in Query Analyzer, you can look and
see if the plan selected changes from execution to execution.

As a general rule ... I would take the variable comparison filtering out of
the joining and into the where clause.|||> As a general rule ... I would take the variable comparison filtering out of
> the joining and into the where clause.

You can't do that with outer joins, because that changes the meaning
(i.e. the resultset).

Gert-Jansql

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Other databases stopped working

I have a similar problem but not with the sample databases. All the sample databases work perfectly fine but all my other databases that were hosted appear to have similar error
"The database cannot be opened because it is version 607. This server supports version 603 and earlier. A downgrade is not supported"
I'm using the SQL Server that came with Visual Studio 2005 Beta 2. Version 9.01116
Any ideas?

The error message indicates that your other databases were created in a version of SQL Server 2005 newer than the version of SQL Server included in the VS Beta2. Databases are not backward compatible, so that's why you're getting the error. Did you have another version of SQL Server installed before you installed the VS Beta (perhaps the June CTP or the September CTP) ?

Assuming that's what happened, here are a couple of possible solutions.

1. Install the September CTP version of SQL Server 2005 from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/ctp.mspx and try to attach these databases there. (This leaves the older version of SQL Server in place).
or
2. Uninstall the current version and install the September CTP. The September CTP bits are compatible with VS Beta 2, so this shouldn't create any problems.

These solutions might or might not work depending on which versions the September CTP supports. If this doesn't work, you'll need to install an older version like the June CTP and try to attach the databases there. For the June CTP Express Edition, see http://www.microsoft.com/downloads/details.aspx?FamilyId=1A722B0F-6CCA-4E8B-B6EA-12D9C450ED92&displaylang=en . For the June CTP Developer Edition, see http://www.microsoft.com/downloads/details.aspx?FamilyId=B414B00F-E2CC-4CAB-A147-EACA26740F19&displaylang=en.

Hope that helps.
Regards,

osql with proxy account errors

I've got a proxy account set up that is working fine for DTS packages, etc.
I've also got an environmental variable set up for osql setting the user to
the proxy account. The problem I'm having is within a scheduled job, when I
execute this statement:
EXEC master..xp_cmdshell 'osql.exe -S OnyxRptServer -E -n -w 121 -Q "EXEC
SBTDataAudit.dbo.OrnumCshipnumScript" -o
" \\onyxrptserver\SBTDataAudits\Outputs\Cs
hipno_Ornum_Audit_Results.txt'
I'm getting an error saying my job owner doesn't have permissions to execute
xp_cmdshell.
My job owner is not a member of the sa server role, so I was thinking that
the proxy account user would pick up and execute the xp_cmdshell call.
Any help would be appreciated.
Thanks,
JoanFrom BOL:
"If you choose to use a Windows NT account that is not a member of the local
administrator's group for the MSSQLServer service, users who are not members
of the symin fixed server role cannot execute xp_cmdshell."
"jmeyers" wrote:

> I've got a proxy account set up that is working fine for DTS packages, etc
.
> I've also got an environmental variable set up for osql setting the user t
o
> the proxy account. The problem I'm having is within a scheduled job, when
I
> execute this statement:
> EXEC master..xp_cmdshell 'osql.exe -S OnyxRptServer -E -n -w 121 -Q "EXEC
> SBTDataAudit.dbo.OrnumCshipnumScript" -o
> " \\onyxrptserver\SBTDataAudits\Outputs\Cs
hipno_Ornum_Audit_Results.txt'
> I'm getting an error saying my job owner doesn't have permissions to execu
te
> xp_cmdshell.
> My job owner is not a member of the sa server role, so I was thinking that
> the proxy account user would pick up and execute the xp_cmdshell call.
> Any help would be appreciated.
> Thanks,
> Joan
>|||The proxy user is a local admin.
"Cris_Benge" wrote:
> From BOL:
> "If you choose to use a Windows NT account that is not a member of the loc
al
> administrator's group for the MSSQLServer service, users who are not membe
rs
> of the symin fixed server role cannot execute xp_cmdshell."
>
> "jmeyers" wrote:
>

Osql Syntax Help

Dear friends,

I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:\30704' WITH

REPLACE, MOVE'NAVIMEX_Data' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:\Program Files\Microsoft SQL

Server\MSSQL\Data\NAVIMEX_LOG.LDF'" QUIT
================================================== ========

Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:

Thanks in advance.
HotBirdWhat errors are you getting? You can add "/o<filename>" or use command line redirection to capture all errors. And don't use QUIT unless your RESTORE was unsuccessful.|||make sure that you dont hit enter after "microsoft sql" in the path
try a shorter path
also i believe that when you issue a restore cmd from disk, you have to specify the file name
ex
from disk = 'c:\data\northwind.mdf'

to back up robert, what errors do you get?|||Dear Friends,

Thank you for your replies,

The script works but I'm facing a problem while trying to restore from a network drive or a remote path (\\xxx.xxx.xxx.xxx\mypath\filename)

can you help please?

Regards,

Samer Chaer :rolleyes:|||It's usually due to a lack of necessary permissions on behalf of the security context under which the process is invoked.