Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.

Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you

query

create table a
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:

> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>

Query

Hi,
I have an Audit table that has the following fields and values
FieldName ChangedValue
FirstName Scott
LastName Hello
how am i able to create a query to show the above in one line
FirstName LastName
Scott Hello
Thanks
EdHi
Unless you can identify how to pair your records then you may get spurious
results
If say there was an identifier column (called id) you can link using that
SELECT a.ChangedValue as [Firstname], b.ChangedValue as [LastName]
FROM dbo.MyAudit A
JOIN dbo.MyAudit B on A.id = B.Id
WHERE a.FieldName = 'FirstName'
b.FieldName = 'LastName'
This previous post may also help:
http://tinyurl.com/6rhsj
John
"Ed" wrote:

> Hi,
> I have an Audit table that has the following fields and values
> FieldName ChangedValue
> FirstName Scott
> LastName Hello
> how am i able to create a query to show the above in one line
> FirstName LastName
> Scott Hello
> Thanks
> Ed
>
>

Query

I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance on
both cases to see which one bring the data faster.
Thanks: Cesar Sandoval
There are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>
sql

query

Hi,
There is a table which contains the rows and I would like to create a query
that can show the below result. Can a query do that?
Thanks
Table
--
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
1 2 Dergi
2 1 Ceket
2 1 Gmlek
2 2 Kravat
2 3 orap
2 3 Pantolon
---
The Query result
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
2 1 Ceket
2 2 Kravat
2 3 orap
---A query can probably do this, but you will have to tell us
how you decide which Name to return when there is more
than one row with the same Owner and Cat values. Here,
it looks like you are choosing the one that appears first in
the list of all table rows, but in order to do this with a query,
you need to define the row you want in terms of the column
values, not a particular output order that you can't rely on.
Steve Kass
Drew University
tolgay wrote:

>Hi,
>There is a table which contains the rows and I would like to create a query
>that can show the below result. Can a query do that?
>Thanks
>
>Table
>--
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 G闣lek
> 2 2 Kravat
> 2 3 よrap
> 2 3 Pantolon
>---
>The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 よrap
>---
>
>|||Actually it doesn't matter which row comes with the query. But the main
point is the query must get one of the rows.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> A query can probably do this, but you will have to tell us
> how you decide which Name to return when there is more
> than one row with the same Owner and Cat values. Here,
> it looks like you are choosing the one that appears first in
> the list of all table rows, but in order to do this with a query,
> you need to define the row you want in terms of the column
> values, not a particular output order that you can't rely on.
> Steve Kass
> Drew University
> tolgay wrote:
>
query|||in that case...
Select owner
, cat
, max(name)
from CatTable
group by owner
, cat
However, this table structure needs keys to prevent duplicates. Two rows as
shown here should never exist. You probably want to change your database
structures before doing anything else. You can guarantee that the name you
pull back will be wrong half the time.
"tolgay" <tgul@.tgul.com> wrote in message
news:%23DJTKpFcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Actually it doesn't matter which row comes with the query. But the main
> point is the query must get one of the rows.
> Thanks
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> query
>|||Jim has offered a solution, and I'll just add that while
it doesn't matter to you, you still have to tell SQL Server
what to do. There's no ANY_OLD_ONE aggregate
in SQL. :)
SK
tolgay wrote:

>Actually it doesn't matter which row comes with the query. But the main
>point is the query must get one of the rows.
>Thanks
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
>
>query
>
>
>|||thanks a lot you saved my day :)
"tolgay" <tgul@.tgul.com> wrote in message
news:un$oOaFcGHA.3856@.TK2MSFTNGP03.phx.gbl...
> Hi,
> There is a table which contains the rows and I would like to create a
> query
> that can show the below result. Can a query do that?
> Thanks
>
> Table
> --
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gmlek
> 2 2 Kravat
> 2 3 orap
> 2 3 Pantolon
> ---
> The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 orap
> ---
>sql

query

Hi,
There is a table which contains the rows and I would like to create a query
that can show the below result. Can a query do that?
Thanks
Table
--
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
1 2 Dergi
2 1 Ceket
2 1 Gömlek
2 2 Kravat
2 3 Çorap
2 3 Pantolon
---
The Query result
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
2 1 Ceket
2 2 Kravat
2 3 Çorap
---A query can probably do this, but you will have to tell us
how you decide which Name to return when there is more
than one row with the same Owner and Cat values. Here,
it looks like you are choosing the one that appears first in
the list of all table rows, but in order to do this with a query,
you need to define the row you want in terms of the column
values, not a particular output order that you can't rely on.
Steve Kass
Drew University
tolgay wrote:
>Hi,
>There is a table which contains the rows and I would like to create a query
>that can show the below result. Can a query do that?
>Thanks
>
>Table
>--
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gé?£lek
> 2 2 Kravat
> 2 3 ã'rap
> 2 3 Pantolon
>---
>The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 ã'rap
>---
>
>|||Actually it doesn't matter which row comes with the query. But the main
point is the query must get one of the rows.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> A query can probably do this, but you will have to tell us
> how you decide which Name to return when there is more
> than one row with the same Owner and Cat values. Here,
> it looks like you are choosing the one that appears first in
> the list of all table rows, but in order to do this with a query,
> you need to define the row you want in terms of the column
> values, not a particular output order that you can't rely on.
> Steve Kass
> Drew University
> tolgay wrote:
> >Hi,
> >
> >There is a table which contains the rows and I would like to create a
query
> >that can show the below result. Can a query do that?
> >Thanks
> >
> >
> >Table
> >--
> > Owner Cat Name
> > 1 1 Kitap
> > 1 2 Defter
> > 1 3 Kalem
> > 1 2 Dergi
> > 2 1 Ceket
> > 2 1 G?lek
> > 2 2 Kravat
> > 2 3 ?rap
> > 2 3 Pantolon
> >
> >---
> >The Query result
> >
> > Owner Cat Name
> > 1 1 Kitap
> > 1 2 Defter
> > 1 3 Kalem
> > 2 1 Ceket
> > 2 2 Kravat
> > 2 3 ?rap
> >
> >---
> >
> >
> >
> >|||in that case...
Select owner
, cat
, max(name)
from CatTable
group by owner
, cat
However, this table structure needs keys to prevent duplicates. Two rows as
shown here should never exist. You probably want to change your database
structures before doing anything else. You can guarantee that the name you
pull back will be wrong half the time.
"tolgay" <tgul@.tgul.com> wrote in message
news:%23DJTKpFcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Actually it doesn't matter which row comes with the query. But the main
> point is the query must get one of the rows.
> Thanks
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> > A query can probably do this, but you will have to tell us
> > how you decide which Name to return when there is more
> > than one row with the same Owner and Cat values. Here,
> > it looks like you are choosing the one that appears first in
> > the list of all table rows, but in order to do this with a query,
> > you need to define the row you want in terms of the column
> > values, not a particular output order that you can't rely on.
> >
> > Steve Kass
> > Drew University
> >
> > tolgay wrote:
> >
> > >Hi,
> > >
> > >There is a table which contains the rows and I would like to create a
> query
> > >that can show the below result. Can a query do that?
> > >Thanks
> > >
> > >
> > >Table
> > >--
> > > Owner Cat Name
> > > 1 1 Kitap
> > > 1 2 Defter
> > > 1 3 Kalem
> > > 1 2 Dergi
> > > 2 1 Ceket
> > > 2 1 G?lek
> > > 2 2 Kravat
> > > 2 3 ?rap
> > > 2 3 Pantolon
> > >
> > >---
> > >The Query result
> > >
> > > Owner Cat Name
> > > 1 1 Kitap
> > > 1 2 Defter
> > > 1 3 Kalem
> > > 2 1 Ceket
> > > 2 2 Kravat
> > > 2 3 ?rap
> > >
> > >---
> > >
> > >
> > >
> > >
>|||Jim has offered a solution, and I'll just add that while
it doesn't matter to you, you still have to tell SQL Server
what to do. There's no ANY_OLD_ONE aggregate
in SQL. :)
SK
tolgay wrote:
>Actually it doesn't matter which row comes with the query. But the main
>point is the query must get one of the rows.
>Thanks
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
>
>>A query can probably do this, but you will have to tell us
>>how you decide which Name to return when there is more
>>than one row with the same Owner and Cat values. Here,
>>it looks like you are choosing the one that appears first in
>>the list of all table rows, but in order to do this with a query,
>>you need to define the row you want in terms of the column
>>values, not a particular output order that you can't rely on.
>>Steve Kass
>>Drew University
>>tolgay wrote:
>>
>>Hi,
>>There is a table which contains the rows and I would like to create a
>>
>query
>
>>that can show the below result. Can a query do that?
>>Thanks
>>
>>Table
>>--
>> Owner Cat Name
>> 1 1 Kitap
>> 1 2 Defter
>> 1 3 Kalem
>> 1 2 Dergi
>> 2 1 Ceket
>> 2 1 G?lek
>> 2 2 Kravat
>> 2 3 ?rap
>> 2 3 Pantolon
>>---
>>The Query result
>> Owner Cat Name
>> 1 1 Kitap
>> 1 2 Defter
>> 1 3 Kalem
>> 2 1 Ceket
>> 2 2 Kravat
>> 2 3 ?rap
>>---
>>
>>
>>
>
>|||thanks a lot you saved my day :)
"tolgay" <tgul@.tgul.com> wrote in message
news:un$oOaFcGHA.3856@.TK2MSFTNGP03.phx.gbl...
> Hi,
> There is a table which contains the rows and I would like to create a
> query
> that can show the below result. Can a query do that?
> Thanks
>
> Table
> --
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gömlek
> 2 2 Kravat
> 2 3 Çorap
> 2 3 Pantolon
> ---
> The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 Çorap
> ---
>

QUERY

Using sql 2000 here is my table, query, and error
Table
create table projec
(task varchar(14) not null
startdate datetime
enddate datetime)
Query
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH
FROM PROJEC
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE
Error
Server: Msg 195, Level 15, State 10, Line
'ADD_MONTHS' is not a recognized function nameI think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

Query

I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance o
n
both cases to see which one bring the data faster.
Thanks: Cesar SandovalThere are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>sql

query

Hi,
There is a table which contains the rows and I would like to create a query
that can show the below result. Can a query do that?
Thanks
Table
--
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
1 2 Dergi
2 1 Ceket
2 1 Gmlek
2 2 Kravat
2 3 orap
2 3 Pantolon
---
The Query result
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
2 1 Ceket
2 2 Kravat
2 3 orap
---A query can probably do this, but you will have to tell us
how you decide which Name to return when there is more
than one row with the same Owner and Cat values. Here,
it looks like you are choosing the one that appears first in
the list of all table rows, but in order to do this with a query,
you need to define the row you want in terms of the column
values, not a particular output order that you can't rely on.
Steve Kass
Drew University
tolgay wrote:

>Hi,
>There is a table which contains the rows and I would like to create a query
>that can show the below result. Can a query do that?
>Thanks
>
>Table
>--
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 G闣lek
> 2 2 Kravat
> 2 3 よrap
> 2 3 Pantolon
>---
>The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 よrap
>---
>
>|||Actually it doesn't matter which row comes with the query. But the main
point is the query must get one of the rows.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> A query can probably do this, but you will have to tell us
> how you decide which Name to return when there is more
> than one row with the same Owner and Cat values. Here,
> it looks like you are choosing the one that appears first in
> the list of all table rows, but in order to do this with a query,
> you need to define the row you want in terms of the column
> values, not a particular output order that you can't rely on.
> Steve Kass
> Drew University
> tolgay wrote:
>
query[vbcol=seagreen]|||in that case...
Select owner
, cat
, max(name)
from CatTable
group by owner
, cat
However, this table structure needs keys to prevent duplicates. Two rows as
shown here should never exist. You probably want to change your database
structures before doing anything else. You can guarantee that the name you
pull back will be wrong half the time.
"tolgay" <tgul@.tgul.com> wrote in message
news:%23DJTKpFcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Actually it doesn't matter which row comes with the query. But the main
> point is the query must get one of the rows.
> Thanks
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> query
>|||Jim has offered a solution, and I'll just add that while
it doesn't matter to you, you still have to tell SQL Server
what to do. There's no ANY_OLD_ONE aggregate
in SQL.
SK
tolgay wrote:

>Actually it doesn't matter which row comes with the query. But the main
>point is the query must get one of the rows.
>Thanks
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
>
>query
>
>
>|||thanks a lot you saved my day
"tolgay" <tgul@.tgul.com> wrote in message
news:un$oOaFcGHA.3856@.TK2MSFTNGP03.phx.gbl...
> Hi,
> There is a table which contains the rows and I would like to create a
> query
> that can show the below result. Can a query do that?
> Thanks
>
> Table
> --
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gmlek
> 2 2 Kravat
> 2 3 orap
> 2 3 Pantolon
> ---
> The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 orap
> ---
>

Queries

Hi,
how can I create a Form with a Datasource of two tables.
Regards
cucmejc
| From: <cucmejc@.yahoo.com>
| Subject: Queries
| Date: Sun, 17 Oct 2004 01:59:51 +0200
| Lines: 10
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
| X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
| Message-ID: <e6qZkt9sEHA.1308@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: pd9e9f165.dip.t-dialin.net 217.233.241.101
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6350
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| how can I create a Form with a Datasource of two tables.
|
| Regards
| cucmejc
|
|
|
|
|
Hello,
Can you be more clear on what you are requesting?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Quarterly Values

i have a date heirarchy

dim date

Year

Quarter

Month

and measures SalesVolume

how can i create salesvolume calculated measure for prev quarter, prev 3 quarters and so on.

Dear Chilukurisri,

try to use the same logic of the other post and try to use the function QTD too.

Helped?

Regards!

|||thanx pedroCGD, but QTD will give Quarters to date value so if we select the current member then it will give the sum of quarters for all the values starting beginning of the year but what i am looking is for a formula to calculate last 3 quarters irrespective of wheather i select what member in the current year i.e end of the quarter or middle of the quarter. How can i achieve that?

Quarterly Chart

Hi, i have a table with column for quarter and year (just like the sample Report - Company Sales).
now, i'm trying to create a chart which shows the quarter as the series group /x-axis... how can i show the label of the quarter and the year. currently, it can only use either year or quarter.
so, what i want the x-axis label to be like this:
e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
2003 2004If you want the quarter to show up on the x-axis, then it has to be a
category group, not a series group. Just use two category groupings (one for
quarter, one for year) and you should get the desired labels.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> Hi, i have a table with column for quarter and year (just like the sample
Report - Company Sales).
> now, i'm trying to create a chart which shows the quarter as the series
group /x-axis... how can i show the label of the quarter and the year.
currently, it can only use either year or quarter.
> so, what i want the x-axis label to be like this:
> e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> 2003 2004|||ah yes, i meant the category. however, the label can only show either quarter or year. not both.
i need to differentiate between the first q1 and the second q1. I can only put on field for the 'label'. if i leave it blank, it will only show it by the quarter.
"Robert Bruckner [MSFT]" wrote:
> If you want the quarter to show up on the x-axis, then it has to be a
> category group, not a series group. Just use two category groupings (one for
> quarter, one for year) and you should get the desired labels.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> > Hi, i have a table with column for quarter and year (just like the sample
> Report - Company Sales).
> > now, i'm trying to create a chart which shows the quarter as the series
> group /x-axis... how can i show the label of the quarter and the year.
> currently, it can only use either year or quarter.
> >
> > so, what i want the x-axis label to be like this:
> > e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> > 2003 2004
>
>|||Please try and investigate the attached example which gives you two levels
of groupings on the category axis (inner level: month, outer level: year).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Type>Circle</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales / Cost</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="YearGroup">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="MonthGroup">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.625in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||got it... i didn't pay attention to the category groups, instead i put it in the group on inside the category group.
"Robert Bruckner [MSFT]" wrote:
> Please try and investigate the attached example which gives you two levels
> of groupings on the category axis (inner level: month, outer level: year).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="TotalSalesByYear">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style />
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <Color>Brown</Color>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Marker>
> <Type>Circle</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=(Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)+8000)*1.15</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Marker>
> <Type>Diamond</Type>
> <Size>10pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> <PlotType>Line</PlotType>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>100</PointWidth>
> <Type>Line</Type>
> <Title>
> <Caption>Sales / Cost</Caption>
> <Style>
> <FontSize>14pt</FontSize>
> <FontWeight>700</FontWeight>
> </Style>
> </Title>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="YearGroup">
> <GroupExpressions>
> <GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="MonthGroup">
> <GroupExpressions>
> <GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderDate.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>6.125in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Cost</Label>
> </StaticMember>
> <StaticMember>
> <Label>Sales</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundGradientEndColor>White</BackgroundGradientEndColor>
> <BackgroundGradientType>TopBottom</BackgroundGradientType>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>6.5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.625in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
> Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
>
same[vbcol=seagreen]
like[vbcol=seagreen]

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank you
Hi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegr oups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
same[vbcol=seagreen]
like[vbcol=seagreen]

Quality of Displayed charts is poor

I create some charts in VS and they look crisp.
Then deploy, but when I view in the bouser(IE7) they look jaggered.
What am I doing wrong.
Thanks for any adviceOn Nov 7, 8:37 pm, gavin <ga...@.discussions.microsoft.com> wrote:
> I create some charts in VS and they look crisp.
> Then deploy, but when I view in the bouser(IE7) they look jaggered.
> What am I doing wrong.
> Thanks for any advice
This is just a thought, but you might want to shrink the chart control
in the report and see if maybe that improves the resolution, etc. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

QRY question: If field1 is null then field2

Is there a way to create a column that will show the
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:

>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>

q; Tracing the deadlock

Hi,
I need to trace deadlock, one of article was mentioning â'QL Server
Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
7.0, is there any way I can do this in Sql Server 2000?INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
How to identify SQL Server performance issues, by analyzing Profiler output?
http://vyaskn.tripod.com/analyzing_profiler_output.htm
AMB
"JIM.H." wrote:
> Hi,
> I need to trace deadlock, one of article was mentioning â'QL Server
> Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server
> 7.0, is there any way I can do this in Sql Server 2000?
>

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server =
N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server =
> N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
> N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =
> N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>
|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:

> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
> N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>
>
|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote: