Q Statistics & Query Performance

In the last couple of ws, my SQL server has been occasionally
bogging down during the day. I noticed that it was when a stored
procedure that usually takes 2 seconds to run timed out.
I ran sp_updatestats in Query Analyzer and everything went back to
normal.
AUTO_UPDATE_STATISTICS is set to ON in the database
As part of my database maintenance, I do the following:
- Update Statistics at 05:30am on wdays
- Perform a complete backup nightly at 03:00am
- Perform a transaction log backup every half an hour between 9am and
7pm on wdays
My first hunch is that I happened to be running the sp when the logs
were being backed up. However, the logs take less than a couple of
seconds to back up. Shouldn't things go back to normal after the
back-up is done?
The load on the server is pretty low, at most 10 users hitting the web
app front end at one time.
Any suggestions or tips? Thank You(george.durzi@.gmail.com) writes:
> In the last couple of ws, my SQL server has been occasionally
> bogging down during the day. I noticed that it was when a stored
> procedure that usually takes 2 seconds to run timed out.
> I ran sp_updatestats in Query Analyzer and everything went back to
> normal.
> AUTO_UPDATE_STATISTICS is set to ON in the database
> As part of my database maintenance, I do the following:
> - Update Statistics at 05:30am on wdays
> - Perform a complete backup nightly at 03:00am
> - Perform a transaction log backup every half an hour between 9am and
> 7pm on wdays
> My first hunch is that I happened to be running the sp when the logs
> were being backed up. However, the logs take less than a couple of
> seconds to back up. Shouldn't things go back to normal after the
> back-up is done?
> The load on the server is pretty low, at most 10 users hitting the web
> app front end at one time.
It's difficult to say with this little amount of information. But I
would guess that parameter sniffing is part of the plot. When SQL Server
builds the query plan for a stored procedure, it looks at the parameter
values, and uses these as guidance when building the plan. This means
that if the procedure is initially called with some odd value, you
may be stuck with a plan that is not good for regular values. Here
is a brief example:
CREATE PROCEDURE get_data @.last_key int = 0 AS
IF @.last_key = 0
SELECT ... FROM tbl
ELSE
SELECT ... FRON tbl WHERE keycol > @.last-key
Assume that this procedure is called in the morning to do an initial
load of a screen of some sort, and is then called repeatedly during
the day too update that screen. Assume further that the index on last_key
is non-clustered. If there is no plan cached in the morning, the optimzer
will use a table scan for both cases, as the NC index is not good
for reading all values.
When you run sp_updatestats, the optimizer might notice that statistcs
have changed and recompile the procedure with the currently value,
for which the index is useful.
This is a bit of speculation on my part. You might be able to get some
more cluse, if you start to inspect query plans. You can also use
DBCC SHOW_STATISTCS before and after to see whether are any significant
changes in statistics.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thanks for your tip, I'll look into this further

Puzzled by Query Plan

I'm hoping somebody can explain exactly what's going on here - I can't
find it documented anywhere.

Go to the Northwind database, and run the following SQL:

create index IX_UnitPrice on [order details](unitprice)

Now, turn on SHOWPLAN (either graphical or text, it doesn't matter),
and run the following query:

select * from [order details]
where unitprice = 2

Output:

StmtText
|--Index Seek(OBJECT: ([Northwind].[dbo].[Order
Details].[IX_UnitPrice]), SEEK: ([Order
Details].[UnitPrice]=Convert([@.1])) ORDERED FORWARD)

Now, alter the SARG slightly by making it a float:

select unitprice from [order details]
where unitprice = 2.000

Output:

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
[Expr1005]))
|--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
[Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
(Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
(Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
| |--Constant Scan
|--Index Seek(OBJECT: ([Northwind].[dbo].[Order
Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
[Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
(Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)

Right. I understand that in both cases the SARG datatype is different
from the column datatype (which is money), and that in the first
example the SARG constant gets implicitly converted from int -> money
(following the datatype hierarchy rules), and so the index can still
be used.

In the second example, the datatype hierarchy dictates that money is
lower than float, so the table column gets implicitly converted from
money -> float, which strictly speaking disallows the use of the index
on that column.

What I DON'T understand is what exactly all that gubbins about the
expressions (especially the definition of [Expr1005] is all about; how
does that statement decide whether Expr1005 is going to be NULL, 6, or
10?

I'm soon going to be giving some worked tutorials on index selection
and use of Showplan to our developers, and being a bolshi lot they're
bound to want to know exactly what all that output means. I'd rather
be able to tell them than to say I don't actually know!

How about it someone?

Thanks,

Phil"Philip Yale" wrote:

<snip
> select unitprice from [order details]
> where unitprice = 2.000
> Output:
> StmtText
> |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> | |--Constant Scan
> |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
>
> Right. I understand that in both cases the SARG datatype is different
> from the column datatype (which is money), and that in the first
> example the SARG constant gets implicitly converted from int -> money
> (following the datatype hierarchy rules), and so the index can still
> be used.
> In the second example, the datatype hierarchy dictates that money is
> lower than float, so the table column gets implicitly converted from
> money -> float, which strictly speaking disallows the use of the index
> on that column.
> What I DON'T understand is what exactly all that gubbins about the
> expressions (especially the definition of [Expr1005] is all about; how
> does that statement decide whether Expr1005 is going to be NULL, 6, or
> 10?

<snip
Phil,

It appears that SQL Server is converting your float SARG to 2 money scalars
(SARG - 1 and SARG + 1) so that it can perform an index seek with money
types and still handle loss of precision and floating point rounding. The
second part of the seek (listed as the WHERE) then converts the index values
to float: this way it doesn't have to convert the table column until after a
seek has been performed. Pretty smart if you ask me...

[Expr1005] use a bitwise or so that Expr1005 has a unique value for the
various NULL states of the other two calcuated values

6 = 0110 in binary
10 = 1010 in binary

SARG-1 SARG+1 Result
NOT NULL NOT NULL 6 | 10 = 1110
NOT NULL NULL 6 | 0 = 0110
NULL NOT NULL 0 | 10 = 1010
NULL NULL 0 | 0 = 0000

So you have a 4-bit value where you can examine bit 1 to see if you have any
non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
why unless it has something to do with the internal representation of null's
or float's or something else.

What I'm not clear on is exactly how this bit mask is used in the nested
loop join unless a row is rejected out of hand for a zero value for the
expression. Perhaps someone else can shed light on this...

Craig|||"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<L8ydd.14900$OD2.132@.bgtnsc05-news.ops.worldnet.att.net>...
> "Philip Yale" wrote:
> <snip>
> > select unitprice from [order details]
> > where unitprice = 2.000
> > Output:
> > StmtText
> > |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> > |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> > [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> > (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> > (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> > | |--Constant Scan
> > |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> > Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> > [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> > (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
> > Right. I understand that in both cases the SARG datatype is different
> > from the column datatype (which is money), and that in the first
> > example the SARG constant gets implicitly converted from int -> money
> > (following the datatype hierarchy rules), and so the index can still
> > be used.
> > In the second example, the datatype hierarchy dictates that money is
> > lower than float, so the table column gets implicitly converted from
> > money -> float, which strictly speaking disallows the use of the index
> > on that column.
> > What I DON'T understand is what exactly all that gubbins about the
> > expressions (especially the definition of [Expr1005] is all about; how
> > does that statement decide whether Expr1005 is going to be NULL, 6, or
> > 10?
> <snip>
> Phil,
> It appears that SQL Server is converting your float SARG to 2 money scalars
> (SARG - 1 and SARG + 1) so that it can perform an index seek with money
> types and still handle loss of precision and floating point rounding. The
> second part of the seek (listed as the WHERE) then converts the index values
> to float: this way it doesn't have to convert the table column until after a
> seek has been performed. Pretty smart if you ask me...
> [Expr1005] use a bitwise or so that Expr1005 has a unique value for the
> various NULL states of the other two calcuated values
> 6 = 0110 in binary
> 10 = 1010 in binary
> SARG-1 SARG+1 Result
> NOT NULL NOT NULL 6 | 10 = 1110
> NOT NULL NULL 6 | 0 = 0110
> NULL NOT NULL 0 | 10 = 1010
> NULL NULL 0 | 0 = 0000
> So you have a 4-bit value where you can examine bit 1 to see if you have any
> non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
> check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
> why unless it has something to do with the internal representation of null's
> or float's or something else.
> What I'm not clear on is exactly how this bit mask is used in the nested
> loop join unless a row is rejected out of hand for a zero value for the
> expression. Perhaps someone else can shed light on this...
> Craig

Craig,

Thanks very much for that - an extremely well-explained and detailed
answer. I'm intrigued to know how you knew all that stuff - or did you
just deduce it? (Don't want a job do you? :-) )

I must confess I never thought that the values might be bitmaps. Like
you say, it's all pretty smart. All we need to know now is just where
Expr1005 is actually used.

Phil|||"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<L8ydd.14900$OD2.132@.bgtnsc05-news.ops.worldnet.att.net>...
> "Philip Yale" wrote:
> <snip>
> > select unitprice from [order details]
> > where unitprice = 2.000
> > Output:
> > StmtText
> > |--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],
> [Expr1005]))
> > |--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@.1]))-1.00,
> > [Expr1004]=Convert(Convert([@.1]))+1.00, [Expr1005]=If
> > (Convert(Convert([@.1]))-1.00=NULL) then 0 else 6|If
> > (Convert(Convert([@.1]))+1.00=NULL) then 0 else 10))
> > | |--Constant Scan
> > |--Index Seek(OBJECT: ([Northwind].[dbo].[Order
> > Details].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >
> > [Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:
> > (Convert([Order Details].[UnitPrice])=Convert([@.1])) ORDERED FORWARD)
> > Right. I understand that in both cases the SARG datatype is different
> > from the column datatype (which is money), and that in the first
> > example the SARG constant gets implicitly converted from int -> money
> > (following the datatype hierarchy rules), and so the index can still
> > be used.
> > In the second example, the datatype hierarchy dictates that money is
> > lower than float, so the table column gets implicitly converted from
> > money -> float, which strictly speaking disallows the use of the index
> > on that column.
> > What I DON'T understand is what exactly all that gubbins about the
> > expressions (especially the definition of [Expr1005] is all about; how
> > does that statement decide whether Expr1005 is going to be NULL, 6, or
> > 10?
> <snip>
> Phil,
> It appears that SQL Server is converting your float SARG to 2 money scalars
> (SARG - 1 and SARG + 1) so that it can perform an index seek with money
> types and still handle loss of precision and floating point rounding. The
> second part of the seek (listed as the WHERE) then converts the index values
> to float: this way it doesn't have to convert the table column until after a
> seek has been performed. Pretty smart if you ask me...
> [Expr1005] use a bitwise or so that Expr1005 has a unique value for the
> various NULL states of the other two calcuated values
> 6 = 0110 in binary
> 10 = 1010 in binary
> SARG-1 SARG+1 Result
> NOT NULL NOT NULL 6 | 10 = 1110
> NOT NULL NULL 6 | 0 = 0110
> NULL NOT NULL 0 | 10 = 1010
> NULL NULL 0 | 0 = 0000
> So you have a 4-bit value where you can examine bit 1 to see if you have any
> non-null value, bit 2 to check SARG-1 for a non-null value, and bit 3 to
> check SARG+1 for a non-null value. Bit 0 tells you nothing: I don't know
> why unless it has something to do with the internal representation of null's
> or float's or something else.
> What I'm not clear on is exactly how this bit mask is used in the nested
> loop join unless a row is rejected out of hand for a zero value for the
> expression. Perhaps someone else can shed light on this...
> Craig

Craig,

Thanks very much for that - an extremely well-explained and detailed
answer. I'm intrigued to know how you knew all that stuff - or did you
just deduce it? (Don't want a job do you? :-) )

I must confess I never thought that the values might be bitmaps. Like
you say, it's all pretty smart. All we need to know now is just where
Expr1005 is actually used.

Phil|||"Philip Yale" wrote:

> Craig,
> Thanks very much for that - an extremely well-explained and detailed
> answer. I'm intrigued to know how you knew all that stuff - or did you
> just deduce it? (Don't want a job do you? :-) )
> I must confess I never thought that the values might be bitmaps. Like
> you say, it's all pretty smart. All we need to know now is just where
> Expr1005 is actually used.
> Phil

Phil,

Thank you for the multiple (and very flattering) compliments!

I make it a habit to examine query plans and make sure I understand them
when unit testing stored procedures, so they aren't totally unfamiliar to
me; however, I don't consider myself a query plan expert by any means. I
was mostly able to deduce what was going on fairly quickly because that kind
of handling of floating point values is one of two fairly common idioms in
C/C++ (especially since portable floating point handling can be, ahem,
challenging given the differing implementations out there). But I'm still
very curious as to where the bitmap is used...

As far as employment is concerned, I'm fairly happy where I'm at but obscene
piles of money are always a great inducement ;)

Craig

Putting SqlDataSource code in code-behind

Hi,

I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this?

Thanks geniuses.

This link:

http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

|||

Hi ahTan,

You may need to use a SqlDataAdapter to fill the result set into a DataSet. Here is an example.

SqlConnection cnn = new SqlConnection("your connection string here");
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Table1", cnn);
DataSet ds = new DataSet();
sda.Fill(ds);

sql

Putting 'NA' in a Money Datatype Column

Hello,
Can anyone solve this prob...
BACKGROUND:
I have a column in my query that is calculated from money datatypes. To
avoid the divide by zero error I created a nice inline case statement that
places something else in that field instead. It works great with any number
of my choice. For example I can put 100 everywhere that this calculated
field would've had a divide by zero error. HOWEVER, I want to put the word
'NA' in that field when this happens. But I can't because the datatype is
wrong:
ERROR:
Hence the error:Implicit conversion from data type char to money is not
allowed.
PROBLEM:
So is there a way to legally cast or convert so that I can put 'NA' in this
field instead of a number like zero or a hundred?
Here is my attempt so far (doesn't work):
CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
expression]
Thanks.
--
Alex A.You will need to cast the entire field as varchar.
Another option would be to return NULL when the expression = 0 and have the
presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
NA.
"Alex A." <AlexA@.discussions.microsoft.com> wrote in message
news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
> Hello,
> Can anyone solve this prob...
> BACKGROUND:
> I have a column in my query that is calculated from money datatypes. To
> avoid the divide by zero error I created a nice inline case statement that
> places something else in that field instead. It works great with any
> number
> of my choice. For example I can put 100 everywhere that this calculated
> field would've had a divide by zero error. HOWEVER, I want to put the
> word
> 'NA' in that field when this happens. But I can't because the datatype is
> wrong:
> ERROR:
> Hence the error:Implicit conversion from data type char to money is not
> allowed.
> PROBLEM:
> So is there a way to legally cast or convert so that I can put 'NA' in
> this
> field instead of a number like zero or a hundred?
> Here is my attempt so far (doesn't work):
> CASE WHEN [expression] = 0 THEN CAST('NA' AS CHAR(2)) ELSE [other
> expression]
> Thanks.
> --
> Alex A.
>|||WKidd,
Thanks for the post...
I can't cast the whole field because if it is not a divide by zero situation
I need the dollar amount to calculate... But I like the Null idea. Anyone
have the syntax in mind for that? Can I just put the word NULL after the
THEN statement?
"WKidd" wrote:

> You will need to cast the entire field as varchar.
> Another option would be to return NULL when the expression = 0 and have th
e
> presentation tier (Crystal Reports, Excel, ASP.NET, etc.) display NULL as
> NA.
> "Alex A." <AlexA@.discussions.microsoft.com> wrote in message
> news:12E0480D-DB52-4664-A2E2-F9D335FE5BB5@.microsoft.com...
>
>|||Look up hopw to use a NULL in SQL. But I would also get rid of the
MONEY datatype.
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||For divisions, I usually write:
SELECT A / NULLIF(B, 0)
This will yield NULL if B is 0, because Anything / NULL yields NULL.
Regarding the precision of the money data type, you may want to do an
implicit (or an explicit) conversion to the decimal (or the float) data
type, like this:
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SELECT CAST((1. * @.my_part / @.total_amt) * @.gross_amt AS money)
The correct result would have been 13530.5038673170731707317073170(...)
but converted back to the money datatype it is 13530.5039
Razvan

putting mdx query in sql job and emailing the results?

Is this possible to email w/ just using the job script window, as long as working with 2D and 3D queries?

I want to know the dates of data in the cube ie:

WITH MEMBER Min1 as

Head(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

MEMBER Max1 as

Tail(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

SELECT {Min1, Max1} on COLUMNS

FROM [Adventure Works]

In the past, I've used xp_sendmail to send out query results. Of course, this requires a SQL query to be performed. One option here would be to perform the MDX query through a SQL OPENROWSET function call. This KB article is a little dated but shows you the basics: http://support.microsoft.com/kb/218592.

So, I have to ask, where are the dates in the OLAP cube coming from? Are these coming from a relational database? If so, could you more easily just query that database?

B.

|||I could query the sql data. but I wanted to know without a doubt that the data is in the cube. I can more easily look at my email than remote in to connect to the cube.

I'll first try openrowset().
Thanks!

Putting attribute on root node using FOR XML PATH

I would like to know how to put an attribute on the root node of the
xml returned from a FOR XML PATH query. One thing I tried is this:

select
m.msgid '@.msgID',
st.namelong 'set/@.namelong',
st.nameshort 'set/@.nameshort',
from
msgset m
inner join settable st on (st.setid = m.setid)
where m.msgID = 195
for xml path('set'), root('message')

but it gives me:

<message>
<set msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</set>
<set msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</set>
<set msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</set>
</message
here is what I want:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
<set namelong="MORE STUFF" nameshort="M STUFF" />
<set namelong="TESTING 123" nameshort="TEST" />
</message
I can't get it. If I use: root(''), then it tells me: "Row tag
omission (empty row tag name) cannot be used with attribute-centric FOR
XML serialization." I'm sure there is a trick to this-- any
suggestions?

Many thanks.
the chippsterTry using a nested query instead

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from settable st
where st.setid = m.setid
for xml path('set'),type)
from
msgset m
where m.msgID = 195
for xml path('message'), type|||Thank you for your suggestion. However, running your query gives me
this:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</message>
<message msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</message>
<message msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</message
Thanks|||Can you post your DDL and sample data.

Here what I used based on your narrative which
gives the results you wanted

declare @.settable table(msgid int,namelong varchar(20),nameshort
varchar(10),setid int)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'STUFF
HERE','STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'MORE
HERE','M STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'TESTING
123','TEST',1)
declare @.msgset table(msgid int,setid int)
insert into @.msgset(msgid,setid) values(195,1)

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from @.settable st
where st.setid = m.setid
for xml path('set'),type)
from
@.msgset m
where m.msgID = 195
for xml path('message'), type|||Thanks again for your reply. I think the problem here is that the
relationship between the msgset table and the settable is many-to-many.
So, the sample data would look like this:

declare @.settable table(msgid int,namelong varchar(20),nameshort
varchar(10),setid int)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'STUFF
HERE','STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'MORE
HERE','M STUFF',2)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'TESTING
123','TEST',3)
declare @.msgset table(msgid int,setid int)
insert into @.msgset(msgid,setid) values(195,1)
insert into @.msgset(msgid,setid) values(195,2)
insert into @.msgset(msgid,setid) values(195,3)

This is an important detail, I'm sorry I left it out.

Any help would be much appreciated-- it seems like there should be a
simple solution to this.
Thanks.|||OK, I figured out a solution:

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from settable st, msgset ms
where st.setid = ms.setid
and ms.msgid = 195
for xml path('set'),type)
from
message m
where m.msgID = 195
for xml path('message'), type

I joined another table that created a one-to-many against the msgset
table. That seems to keep the root node down to one. Thanks for
pointing me in the right direction.

--chip

Put Windows Logs into SQL

Hi!
I am looking into a way to put Windows Logs into SQL Server database
and to be able to query them for specific information. Anybody know
the tool that I can use and be able to schedule it to run on a weekly
basis?
Thank you,
T.tolcis,
In Windows Scripting you can use the WMI datareader to read the Event Logs
into a structured resultset. Then you can use ADO to insert the rows into a
table. I don't have any examples handy, but this link might have what you
need. If not search Google.
http://www.databasejournal.com/feat...cle.php/1503181
-- Bill
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1172591825.527761.216540@.8g2000cwh.googlegroups.com...
> Hi!
> I am looking into a way to put Windows Logs into SQL Server database
> and to be able to query them for specific information. Anybody know
> the tool that I can use and be able to schedule it to run on a weekly
> basis?
> Thank you,
> T.
>sql

Put Windows Logs into SQL

Hi!
I am looking into a way to put Windows Logs into SQL Server database
and to be able to query them for specific information. Anybody know
the tool that I can use and be able to schedule it to run on a weekly
basis?
Thank you,
T.tolcis,
In Windows Scripting you can use the WMI datareader to read the Event Logs
into a structured resultset. Then you can use ADO to insert the rows into a
table. I don't have any examples handy, but this link might have what you
need. If not search Google.
http://www.databasejournal.com/features/mssql/article.php/1503181
-- Bill
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1172591825.527761.216540@.8g2000cwh.googlegroups.com...
> Hi!
> I am looking into a way to put Windows Logs into SQL Server database
> and to be able to query them for specific information. Anybody know
> the tool that I can use and be able to schedule it to run on a weekly
> basis?
> Thank you,
> T.
>

Put Windows Logs into SQL

Hi!
I am looking into a way to put Windows Logs into SQL Server database
and to be able to query them for specific information. Anybody know
the tool that I can use and be able to schedule it to run on a weekly
basis?
Thank you,
T.
tolcis,
In Windows Scripting you can use the WMI datareader to read the Event Logs
into a structured resultset. Then you can use ADO to insert the rows into a
table. I don't have any examples handy, but this link might have what you
need. If not search Google.
http://www.databasejournal.com/features/mssql/article.php/1503181
-- Bill
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1172591825.527761.216540@.8g2000cwh.googlegrou ps.com...
> Hi!
> I am looking into a way to put Windows Logs into SQL Server database
> and to be able to query them for specific information. Anybody know
> the tool that I can use and be able to schedule it to run on a weekly
> basis?
> Thank you,
> T.
>

put queries together

Hi:
I have a complicated access query which is select from another
complicated query that is select from another complicated query. Now I am
trying to creat a dataset based on the first query.
Is there anyway I can create a temp view or dataset so my query can select
from.
regards,Can you link to it from SQL Server and handle it all in stored procedure?
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"ken" <ken@.discussions.microsoft.com> wrote in message
news:BA2B71E5-0708-4E6B-BDF0-A7A9D90CC603@.microsoft.com...
> Hi:
> I have a complicated access query which is select from another
> complicated query that is select from another complicated query. Now I
> am
> trying to creat a dataset based on the first query.
> Is there anyway I can create a temp view or dataset so my query can
> select
> from.
> regards,
>|||Yes, I only can do it in the sql server not in access.
Thanks.
"Douglas McDowell" wrote:
> Can you link to it from SQL Server and handle it all in stored procedure?
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
> "ken" <ken@.discussions.microsoft.com> wrote in message
> news:BA2B71E5-0708-4E6B-BDF0-A7A9D90CC603@.microsoft.com...
> > Hi:
> >
> > I have a complicated access query which is select from another
> > complicated query that is select from another complicated query. Now I
> > am
> > trying to creat a dataset based on the first query.
> > Is there anyway I can create a temp view or dataset so my query can
> > select
> > from.
> >
> > regards,
> >
> >
>
>

purchase order query: very funny and challenge ^_^

i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004

i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number

SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"

i checked my database, last record is LP-0545381-0300

in my debuging process, surprisingly found that selected record is LP-0545381-301 !

any one hav any suggestion? ^_^

I fail to see the humor here, but that's irrelevant. It's difficult to know the query behavior without knowing what GetYearCode() is supposed to return and how it relates to the data in your table. Also, why does your last record (LP-0545381-301) have only 3 digits at the end when your format suggests that it should contain 4 digits? Finally, what were you expecting the result to be?|||

database never had that record, but selected out, isnt it very funny...anyway, GetYearCode() will return the middle code as0545381, i also suprisingly why return 3 digit?!unreasonable, isnt it

what i expected result isLP-0545381-0300

pulling unique records from this query

Hi guys, need your help! (sorry this is quite long)
I've got a table of Projects which I'm using with an asp:Repeater to display
a list of the projects. Here's the sql...
SELECT ProjectID, ProjectName, ProjectClient, DartsContact, LeadArtist,
Projects.AreaOfWork, AreaOfDoncaster, StartDate, EndDate, Running,
WorkAreas.AreaofWork AS AOWName, WorkAreas.RelatesTo AS AOWRelates FROM
Projects, WorkAreas
WHERE (NOT Running=0) AND (Projects.AreaOfWork LIKE '%' + WorkAreas.AOWCode
+ '%') AND (Deleted = 0) ORDER BY ProjectName ASC
As you can hopefully see, I'm using two tables to pull the data together.
It worked fine, until we made a change to the way the data is stored. The
Projects.AreaOfWork field now contains multiple AOWCodes seperated by a
delimiter. So now, whenever I run this query, I get more than 1 line for eac
h
project where there are multiple values in AreaOfWork. So, if I have a
project...
ProjectID, ProjectName, AreaOfWork
1, Test Proj 1, EDU
2, Test Proj 2, EDU|COM
I get 2 lines for Test Project 2, each with a unique AreaOfWork (one with
EDU, one with COM).
2 things... I need to stop it returning multiple records for the same
project when theres more than one AreaOfWork, but I also need to return the
entire AreaOfWork string, because I still need access to those.
Any help would be greatly appreciated.
Cheers
Danwhy have you chosen to store your data like that (delimited) - it
breaks with normalisation, and is the main reason your having problems.
surely it would be easier if you had a separate table like
tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
doing this?|||I see your point mate. Time constraints are the main reason for this.. it's
an addition to a project that's been running for a couple of years (the
having multiples instead of one).
Is there a way to get the query to work!?
"Will" wrote:

> why have you chosen to store your data like that (delimited) - it
> breaks with normalisation, and is the main reason your having problems.
> surely it would be easier if you had a separate table like
> tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
> doing this?
>|||actually, further to this... I *think* i can do half of what I want to do in
code, if I can get it to just select unique records... :)
"Will" wrote:

> why have you chosen to store your data like that (delimited) - it
> breaks with normalisation, and is the main reason your having problems.
> surely it would be easier if you had a separate table like
> tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
> doing this?
>|||depends on what you want out. If we take your example where you have
EDU|COM, what output would you want - the area of work columns x2?
could you post a fuller example in terms of data from both tables, and
what you'd like your query to result in.|||Hi Dan,
Thanks for using MSDN Managed Newsgroup Support.
As Will mentioned, it is not a good idea to store your data like that.
So I want to know why you use the WorkAreas.AreaofWork and
WorkAreas.Relates in the query. If you want to unique the only Project in
this query, I think you may need to exclude the WorkAreas Table.
Also, you may provide me the result of the query now if you have 2
AreaOfWork in the Project Table.
I need to know the exactly different of these 2 records.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you both for your help. I managed to get this to work using a differen
t
method, so no need to concern yourselves anymore :) I do appreciate that
using a third table would be a much better solution, and may consider that
for future redevlopment.
In answer to your question Wei, the reason for showing the AOW fields is
simply to show which Areas of Work a Project belongs to. WorkAreas.Relates i
s
a field that allows us to have inherited Areas of Work in the table, like so
.
aowcode aowname relates_to
1 Education null
2 Adult Ed 1
3 Preschool 1
etc.
Again, thank you both for your help today!
Cheers
Dan
"Wei Lu" wrote:

> Hi Dan,
> Thanks for using MSDN Managed Newsgroup Support.
> As Will mentioned, it is not a good idea to store your data like that.
> So I want to know why you use the WorkAreas.AreaofWork and
> WorkAreas.Relates in the query. If you want to unique the only Project in
> this query, I think you may need to exclude the WorkAreas Table.
> Also, you may provide me the result of the query now if you have 2
> AreaOfWork in the Project Table.
> I need to know the exactly different of these 2 records.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>

Pulling in data from many tables.

I am trying to pull data from 4 tables. When I query them and us distinct
with three of them I can get the data I need. But when I try adding the
fourth one I get more rows then I need/or many dup roles. Want am I missing
or how can I only return want I need?
Hi
Can you show us DDL+ sample data + expected result?
"DPassed" <DPassed@.discussions.microsoft.com> wrote in message
news:69A1A0B4-0F91-46C9-A4EB-B3338D9D1D06@.microsoft.com...
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I
missing
> or how can I only return want I need?
|||Hi DPassed
You might have missed the Join Condition when u joined Table-4.
Just check your query once or just post the query that you are facing the
problem
thanks and regards
Chandra
"DPassed" wrote:

> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I missing
> or how can I only return want I need?

Pulling in data from many tables.

I am trying to pull data from 4 tables. When I query them and us distinct
with three of them I can get the data I need. But when I try adding the
fourth one I get more rows then I need/or many dup roles. Want am I missing
or how can I only return want I need?Hi
Can you show us DDL+ sample data + expected result?
"DPassed" <DPassed@.discussions.microsoft.com> wrote in message
news:69A1A0B4-0F91-46C9-A4EB-B3338D9D1D06@.microsoft.com...
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I
missing
> or how can I only return want I need?|||Hi DPassed
You might have missed the Join Condition when u joined Table-4.
Just check your query once or just post the query that you are facing the
problem
thanks and regards
Chandra
"DPassed" wrote:

> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I missin
g
> or how can I only return want I need?

Pulling in data from many tables.

I am trying to pull data from 4 tables. When I query them and us distinct
with three of them I can get the data I need. But when I try adding the
fourth one I get more rows then I need/or many dup roles. Want am I missing
or how can I only return want I need?Hi
Can you show us DDL+ sample data + expected result?
"DPassed" <DPassed@.discussions.microsoft.com> wrote in message
news:69A1A0B4-0F91-46C9-A4EB-B3338D9D1D06@.microsoft.com...
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I
missing
> or how can I only return want I need?|||Hi DPassed
You might have missed the Join Condition when u joined Table-4.
Just check your query once or just post the query that you are facing the
problem
thanks and regards
Chandra
"DPassed" wrote:
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I missing
> or how can I only return want I need?|||select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
join poitem on rcmast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
"Chandra" wrote:
> Hi DPassed
> You might have missed the Join Condition when u joined Table-4.
> Just check your query once or just post the query that you are facing the
> problem
> thanks and regards
> Chandra
>
> "DPassed" wrote:
> > I am trying to pull data from 4 tables. When I query them and us distinct
> > with three of them I can get the data I need. But when I try adding the
> > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > or how can I only return want I need?|||Hi
Try this Query Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
--CHANGED HERE
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
--CHANGED HERE
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
thanks and regards
chandra
"DPassed" wrote:
> select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> join poitem on rcmast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> "Chandra" wrote:
> > Hi DPassed
> > You might have missed the Join Condition when u joined Table-4.
> >
> > Just check your query once or just post the query that you are facing the
> > problem
> >
> > thanks and regards
> > Chandra
> >
> >
> > "DPassed" wrote:
> >
> > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > with three of them I can get the data I need. But when I try adding the
> > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > or how can I only return want I need?|||Didn't change rows returned. I do thank you for your help.
"Chandra" wrote:
> Hi
> Try this Query Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> --CHANGED HERE
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> --CHANGED HERE
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> thanks and regards
> chandra
>
> "DPassed" wrote:
> > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > join poitem on rcmast.fpono = poitem.fpono
> >
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > "Chandra" wrote:
> >
> > > Hi DPassed
> > > You might have missed the Join Condition when u joined Table-4.
> > >
> > > Just check your query once or just post the query that you are facing the
> > > problem
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > with three of them I can get the data I need. But when I try adding the
> > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > or how can I only return want I need?|||Hi,
Can you try this Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on pomast.fpono = rcmast.fpono
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
regards
Chandra
"DPassed" wrote:
> Didn't change rows returned. I do thank you for your help.
> "Chandra" wrote:
> > Hi
> > Try this Query Now:
> >
> > select
> > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > --CHANGED HERE
> > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > --CHANGED HERE
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> >
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > thanks and regards
> > chandra
> >
> >
> > "DPassed" wrote:
> >
> > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > join poitem on rcmast.fpono = poitem.fpono
> > >
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > > Hi DPassed
> > > > You might have missed the Join Condition when u joined Table-4.
> > > >
> > > > Just check your query once or just post the query that you are facing the
> > > > problem
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > > "DPassed" wrote:
> > > >
> > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > with three of them I can get the data I need. But when I try adding the
> > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > or how can I only return want I need?|||Again thanks for your help, but didn't change the results. I have desided to
just us two of the tables and get as much info out of them as possible.
Thanks again, Have a great day!!
"Chandra" wrote:
> Hi,
> Can you try this Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on pomast.fpono = rcmast.fpono
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> regards
> Chandra
>
> "DPassed" wrote:
> > Didn't change rows returned. I do thank you for your help.
> >
> > "Chandra" wrote:
> >
> > > Hi
> > > Try this Query Now:
> > >
> > > select
> > > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > --CHANGED HERE
> > > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > > --CHANGED HERE
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > >
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > thanks and regards
> > > chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > > ,poitem.flstpdate, poitem.fordqty
> > > > from rcmast
> > > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > > left join pomast on rcmast.fpono = pomast.fpono
> > > > join poitem on rcmast.fpono = poitem.fpono
> > > >
> > > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > > order by rcmast.fpono, rcitem.fitemno
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > > Hi DPassed
> > > > > You might have missed the Join Condition when u joined Table-4.
> > > > >
> > > > > Just check your query once or just post the query that you are facing the
> > > > > problem
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > > "DPassed" wrote:
> > > > >
> > > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > > with three of them I can get the data I need. But when I try adding the
> > > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > > or how can I only return want I need?

Pull between 2 dates from user input

I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this:

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))"

This works when the dates are included in the statement but when i try and use parameters to pull them in like this:

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@.Choice1', 2) + '/' + SUBSTRING('@.Choice1', 4, 2) + '/' + RIGHT ('@.Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@.Choice2', 2) + '/' + SUBSTRING('@.Choice2', 4, 2) + '/' + RIGHT ('@.Choice2', 4)))"

I then recieve the following error message why is this?

ERROR ------> Syntax error converting datetime from character string.

Any Help would be greatly appreciated thanks

Dont surround a parameter with single quotes. Also if choice1 is a data already why not try something like this

"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >=@.Choice1 AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) <@.Choice2"

|||

Thanks for the immediate response that works perfectly exactly what i wanted

Thanks Again

Dazza22

Publish custom assembly with query

My custom assembly works in design mode but when i publish it :

- function without query work well.

- function with query return an error (#Error).

The assembly was copied in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin

What can i do the use query in my custom assembly.

Thanks for all your answers.

Have you looked at this article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;842419

Problems with custom assemblies are usually related to not granting the correct permissions to the assembly in the config files.

Public role and guest security concern in SQL 2000 SP4

Hi all,

I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.

1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.

2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?

Thanks,

Regards,

Edwin

1. You may of course get errors from users trying to access system tables without being specifically granted access. You can resolve these issues by granting access to those users.

2. guest cannot be actually dropped - it can only be denied access to the database (hasdbaccess will show as 0). The msdb database is used by replication and SQL agent, among other components. You should check on the respective forums to see the impact of disabling guest access to the msdb database: SQL Server Replication and SQL Server Tools General.

Thanks
Laurentiu

|||

Thanks Laurentiu.

1. I have a search on web, some people mentioned that if revoke the default privilege from "Public" role on DBs (inclu. select system table, execute stored proc.). Microsoft wouldn't support my issues on this SQL in future. Is it true?

Thanks!

Edwin

|||

Microsoft may not be able to provide support if the user directly modifies system tables. Since all you are doing is changing permissions, your product will still be supported. Keep in mind that some features may require "public" to function properly. I can't think of any off the top of my head so once you come up with this locked down configuration you will have to do some verification testing to ensure that the functionality that you require works.

|||

As long as the changes that you make are made using documented features (revoking a permission using REVOKE statement is a documented feature), then you don't have to worry about invalidating your support options.

You should avoid making changes using undocumented techniques, such as, for example, directly updating system tables.

Thanks
Laurentiu