I'm having trouble developing a query that will group on a given set of
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.
Saturday, February 25, 2012
"marking" the processed elements in data-driven subscription
I would like to use e-mail delivery for the following scenario: new orders
arrive in a table and i
would like to send confirmation messages which contain the content of the
order. The RS has an "every
5 minutes" schedule and should send e-mail just about the new orders to the
customers.
How could I differentiate the new elements from the old ones?
- or -
How can I "mark" the orders, when the confirmation report was created that
they were processed?
Thanks in advance,
Kolos"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||'
"Bender" <roman.ilic@.avtenta.si> wrote in message
news:%23%23BJFeHgEHA.3416@.TK2MSFTNGP09.phx.gbl...
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> > I would like to use e-mail delivery for the following scenario: new
orders
> > arrive in a table and i
> > would like to send confirmation messages which contain the content of
the
> > order. The RS has an "every
> > 5 minutes" schedule and should send e-mail just about the new orders to
> the
> > customers.
> >
> > How could I differentiate the new elements from the old ones?
> > - or -
> > How can I "mark" the orders, when the confirmation report was created
> that
> > they were processed?
> >
> > Thanks in advance,
> > Kolos
> >
> >
> >
>
>|||Hi Kolos,
We do something similar.
We have an additional field in our subscription Queue which has a Processed
Flag field (just a BIT). We update this field whenever the entry has been
processed via the data driven subscription's query. If you don't want to
add a field to this table, you could create an additional table recording a
log of processed Orders.
HTH
Matt
"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||That's on the right track, but I'd like to also point out that an easy way
to update the "flag" field is to use a stored procedure. Within the sproc
you can return the records affected by the email notice and revisit each row
to update the flag value. =)
Matt
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Hi Kolos,
> We do something similar.
> We have an additional field in our subscription Queue which has a
> Processed
> Flag field (just a BIT). We update this field whenever the entry has been
> processed via the data driven subscription's query. If you don't want to
> add a field to this table, you could create an additional table recording
> a
> log of processed Orders.
> HTH
> Matt
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
>> I would like to use e-mail delivery for the following scenario: new
>> orders
>> arrive in a table and i
>> would like to send confirmation messages which contain the content of the
>> order. The RS has an "every
>> 5 minutes" schedule and should send e-mail just about the new orders to
> the
>> customers.
>> How could I differentiate the new elements from the old ones?
>> - or -
>> How can I "mark" the orders, when the confirmation report was created
> that
>> they were processed?
>> Thanks in advance,
>> Kolos
>>
>|||lol, actually, I do use a stored procedure. I was being just more general
and wasn't sure of Kolos's particular scenario.
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:10q9p1l72kj6iaa@.corp.supernews.com...
> That's on the right track, but I'd like to also point out that an easy way
> to update the "flag" field is to use a stored procedure. Within the sproc
> you can return the records affected by the email notice and revisit each
row
> to update the flag value. =)
> Matt
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> > Hi Kolos,
> >
> > We do something similar.
> >
> > We have an additional field in our subscription Queue which has a
> > Processed
> > Flag field (just a BIT). We update this field whenever the entry has
been
> > processed via the data driven subscription's query. If you don't want
to
> > add a field to this table, you could create an additional table
recording
> > a
> > log of processed Orders.
> >
> > HTH
> >
> > Matt
> >
> > "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> > news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> >> I would like to use e-mail delivery for the following scenario: new
> >> orders
> >> arrive in a table and i
> >> would like to send confirmation messages which contain the content of
the
> >> order. The RS has an "every
> >> 5 minutes" schedule and should send e-mail just about the new orders to
> > the
> >> customers.
> >>
> >> How could I differentiate the new elements from the old ones?
> >> - or -
> >> How can I "mark" the orders, when the confirmation report was created
> > that
> >> they were processed?
> >>
> >> Thanks in advance,
> >> Kolos
> >>
> >>
> >>
> >
> >
>
arrive in a table and i
would like to send confirmation messages which contain the content of the
order. The RS has an "every
5 minutes" schedule and should send e-mail just about the new orders to the
customers.
How could I differentiate the new elements from the old ones?
- or -
How can I "mark" the orders, when the confirmation report was created that
they were processed?
Thanks in advance,
Kolos"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||'
"Bender" <roman.ilic@.avtenta.si> wrote in message
news:%23%23BJFeHgEHA.3416@.TK2MSFTNGP09.phx.gbl...
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> > I would like to use e-mail delivery for the following scenario: new
orders
> > arrive in a table and i
> > would like to send confirmation messages which contain the content of
the
> > order. The RS has an "every
> > 5 minutes" schedule and should send e-mail just about the new orders to
> the
> > customers.
> >
> > How could I differentiate the new elements from the old ones?
> > - or -
> > How can I "mark" the orders, when the confirmation report was created
> that
> > they were processed?
> >
> > Thanks in advance,
> > Kolos
> >
> >
> >
>
>|||Hi Kolos,
We do something similar.
We have an additional field in our subscription Queue which has a Processed
Flag field (just a BIT). We update this field whenever the entry has been
processed via the data driven subscription's query. If you don't want to
add a field to this table, you could create an additional table recording a
log of processed Orders.
HTH
Matt
"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||That's on the right track, but I'd like to also point out that an easy way
to update the "flag" field is to use a stored procedure. Within the sproc
you can return the records affected by the email notice and revisit each row
to update the flag value. =)
Matt
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Hi Kolos,
> We do something similar.
> We have an additional field in our subscription Queue which has a
> Processed
> Flag field (just a BIT). We update this field whenever the entry has been
> processed via the data driven subscription's query. If you don't want to
> add a field to this table, you could create an additional table recording
> a
> log of processed Orders.
> HTH
> Matt
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
>> I would like to use e-mail delivery for the following scenario: new
>> orders
>> arrive in a table and i
>> would like to send confirmation messages which contain the content of the
>> order. The RS has an "every
>> 5 minutes" schedule and should send e-mail just about the new orders to
> the
>> customers.
>> How could I differentiate the new elements from the old ones?
>> - or -
>> How can I "mark" the orders, when the confirmation report was created
> that
>> they were processed?
>> Thanks in advance,
>> Kolos
>>
>|||lol, actually, I do use a stored procedure. I was being just more general
and wasn't sure of Kolos's particular scenario.
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:10q9p1l72kj6iaa@.corp.supernews.com...
> That's on the right track, but I'd like to also point out that an easy way
> to update the "flag" field is to use a stored procedure. Within the sproc
> you can return the records affected by the email notice and revisit each
row
> to update the flag value. =)
> Matt
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> > Hi Kolos,
> >
> > We do something similar.
> >
> > We have an additional field in our subscription Queue which has a
> > Processed
> > Flag field (just a BIT). We update this field whenever the entry has
been
> > processed via the data driven subscription's query. If you don't want
to
> > add a field to this table, you could create an additional table
recording
> > a
> > log of processed Orders.
> >
> > HTH
> >
> > Matt
> >
> > "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> > news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> >> I would like to use e-mail delivery for the following scenario: new
> >> orders
> >> arrive in a table and i
> >> would like to send confirmation messages which contain the content of
the
> >> order. The RS has an "every
> >> 5 minutes" schedule and should send e-mail just about the new orders to
> > the
> >> customers.
> >>
> >> How could I differentiate the new elements from the old ones?
> >> - or -
> >> How can I "mark" the orders, when the confirmation report was created
> > that
> >> they were processed?
> >>
> >> Thanks in advance,
> >> Kolos
> >>
> >>
> >>
> >
> >
>
"Mark for re-initialization" hangs
Hi, I have a problem with a SQL 2000 transactional replication.
It is a replication with subscriptions on several world wide located
servers.
A WAN connection has been disabled for some time and as result one
subscription has been marked inactive.
Message: "The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated."
In the past this problem could been solved by re-initializing the
subscription but in this case
the Enterprise Manager hangs, when I tried to mark the subscription
for re-initialization.
Both SQL servers (publisher and subscriber) have been re-started but
the problem remained.
There are no sessions on the subscriber database which could block the
initialization.
The subscription can not be deleted.
What more can I do?
can you issue this command in qa on the publisher in the publication
database?
sp_reinitsubscription
'publicationname','all','SubscriberName','subscrip tionDatabase'
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185358724.772454.145180@.k79g2000hse.googlegr oups.com...
> Hi, I have a problem with a SQL 2000 transactional replication.
> It is a replication with subscriptions on several world wide located
> servers.
> A WAN connection has been disabled for some time and as result one
> subscription has been marked inactive.
> Message: "The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be dropped and
> recreated."
> In the past this problem could been solved by re-initializing the
> subscription but in this case
> the Enterprise Manager hangs, when I tried to mark the subscription
> for re-initialization.
> Both SQL servers (publisher and subscriber) have been re-started but
> the problem remained.
> There are no sessions on the subscriber database which could block the
> initialization.
> The subscription can not be deleted.
> What more can I do?
>
|||I tried out the stored procedure but the result was a hanging qa
without any status or error message.
|||use sp_who2 to identify locks/deadlocks caused by the replication processes
and selectively kill them,
Then try again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185443077.713064.265760@.r34g2000hsd.googlegr oups.com...
>I tried out the stored procedure but the result was a hanging qa
> without any status or error message.
>
|||Success!
I have killed again all sessions both on the publisher and subscriber
databases and
then the stored procedure sp_reinitsubscription worked.
Many thanks for your support.
Martin
It is a replication with subscriptions on several world wide located
servers.
A WAN connection has been disabled for some time and as result one
subscription has been marked inactive.
Message: "The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated."
In the past this problem could been solved by re-initializing the
subscription but in this case
the Enterprise Manager hangs, when I tried to mark the subscription
for re-initialization.
Both SQL servers (publisher and subscriber) have been re-started but
the problem remained.
There are no sessions on the subscriber database which could block the
initialization.
The subscription can not be deleted.
What more can I do?
can you issue this command in qa on the publisher in the publication
database?
sp_reinitsubscription
'publicationname','all','SubscriberName','subscrip tionDatabase'
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185358724.772454.145180@.k79g2000hse.googlegr oups.com...
> Hi, I have a problem with a SQL 2000 transactional replication.
> It is a replication with subscriptions on several world wide located
> servers.
> A WAN connection has been disabled for some time and as result one
> subscription has been marked inactive.
> Message: "The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be dropped and
> recreated."
> In the past this problem could been solved by re-initializing the
> subscription but in this case
> the Enterprise Manager hangs, when I tried to mark the subscription
> for re-initialization.
> Both SQL servers (publisher and subscriber) have been re-started but
> the problem remained.
> There are no sessions on the subscriber database which could block the
> initialization.
> The subscription can not be deleted.
> What more can I do?
>
|||I tried out the stored procedure but the result was a hanging qa
without any status or error message.
|||use sp_who2 to identify locks/deadlocks caused by the replication processes
and selectively kill them,
Then try again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hmb1963" <hmb1963@.gmx-topmail.de> wrote in message
news:1185443077.713064.265760@.r34g2000hsd.googlegr oups.com...
>I tried out the stored procedure but the result was a hanging qa
> without any status or error message.
>
|||Success!
I have killed again all sessions both on the publisher and subscriber
databases and
then the stored procedure sp_reinitsubscription worked.
Many thanks for your support.
Martin
"Macro" statement
Is it possible to write a macro statement using Transact-SQL?
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino
"Lost Inserts"
Is there any known SQL Server bug whereby a record can be successfully
inserted and committed, but then later be found not to be in the
database? For example, if there was a server crash just after the
commit, could committed data be lost?
inserted and committed, but then later be found not to be in the
database? For example, if there was a server crash just after the
commit, could committed data be lost?
I'm sure the answer must be "no", but a client is telling me this is
happening, and I said I'd enquire.Tony (andrewst@.onetel.net.uk) writes:
> Is there any known SQL Server bug whereby a record can be successfully
> inserted and committed, but then later be found not to be in the
> database? For example, if there was a server crash just after the
> commit, could committed data be lost?
> I'm sure the answer must be "no", but a client is telling me this is
> happening, and I said I'd enquire.
Of course, in case of a crash or a hardware error, the database could
become corrupt, in which case you could lose data. But in such cases
you should run DBCC CHECKDB to investigate.
If data appears to be lost, but the database appears to be safe and sound,
there are two possibilities:
1) Data was never committed, but was believed to because of an application
error. (There are a few gotchas that could lead to this.)
2) There is some other process that deletes the rows for one reason or
another.
In case of the latter, Lumgients Log Explorer (www.lumigent.com) can
be invaluable tool to track down what is happening.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tony" <andrewst@.onetel.net.uk> wrote in message
news:c0e3f26e.0311250510.d1d9de3@.posting.google.co m...
> Is there any known SQL Server bug whereby a record can be successfully
> inserted and committed, but then later be found not to be in the
> database? For example, if there was a server crash just after the
> commit, could committed data be lost?
Very unlikely, but possible.
> I'm sure the answer must be "no", but a client is telling me this is
> happening, and I said I'd enquire.
What makes them think this is happening?|||> If data appears to be lost, but the database appears to be safe and
sound,
> there are two possibilities:
> 1) Data was never committed, but was believed to because > of an
application error. (There are a few gotchas that could lead to this.)
> 2) There is some other process that deletes the rows for one reason or
another.
Yes, my suspicion is that it is possibility (1). The application is
written in ASP using ODBC to connect to the database. Autocommit is
used. The logic is something like this:
1) Insert record into table
2) Redirect to another page to print out a certificate
Allegedly, there are a few people holding certificates where no
corresponding record exists in the database. There is no code in the
app. that deletes this data, though of course it could be deleted
manually.
I have verified that if the insert fails (e.g. if I add a check
constraint that will always be violated), the ASP page aborts and does
not redirect to the next page.
Are there any particular scenarios I should look out for?
Thanks for your quick response.
Tony
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tony Andrews (andrewst@.onetel.net.uk) writes:
> Yes, my suspicion is that it is possibility (1). The application is
> written in ASP using ODBC to connect to the database. Autocommit is
> used. The logic is something like this:
> 1) Insert record into table
> 2) Redirect to another page to print out a certificate
> Allegedly, there are a few people holding certificates where no
> corresponding record exists in the database. There is no code in the
> app. that deletes this data, though of course it could be deleted
> manually.
> I have verified that if the insert fails (e.g. if I add a check
> constraint that will always be violated), the ASP page aborts and does
> not redirect to the next page.
> Are there any particular scenarios I should look out for?
There are a few nasty situations, which must be handled properly in
application code.
Say that you call a stored procedure which starts a user-defined
transaction. That stored procedure runs for a longer time, because of
the query or because of blocking. By default, all client libraries
have a timeout which sets in after 30 seconds (if no data have been
seen), and cancels the query. Contrary to what you may think, this DOES
NOT rollback the transaction! Thus, in your error-handling code you
need to issue "IF @.@.trancount > 0 ROLLBACK TRANSACTION".
Note here that even you don't use stored procedures but issue plain
INSERT statements, this can happen if there is a trigger on the table.
A trigger alwyas executes in the context of a transaction. (You may
get an automatic rollback when you cancel execution in a trigger; I
have not tested this case, but I would not trust on getting a ROLLBACK.)
There are some variations of this theme. If a stored procedure refers
to a non-existing table, the procedure will abort on that statement,
without rollback any transactions it may have started. Again, the
client could as a matter of routine always issue rollback in case of
an error.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Logon failed" when attempting to connect to cube.
Greetings. I am brand new to SRS, so please bear with me. I'm trying to
create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
am an admin on the AS box. I am using this tutorial:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee-6d86797c8f28.htm
I set up the data source, then I hit "generate model". I type in my model
name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
using "credentials supplied by the user running the report" dialog box with
the "Use as Windows..." checkbox checked. For good measure I have even added
myself to a cube Role with admin privelages. Alos, I can in fact browse and
manipulate the cube.
Any ideas?
TIA, ChrisRHi
I think the issue here is not the users priviliges in SSAS , rather
the priviliges in SSRS. Try using report manager to configure
permissions for the user in question .
Cheers
Shai
On Nov 20, 12:34 am, ChrisR <Chr...@.discussions.microsoft.com> wrote:
> Greetings. I am brand new to SRS, so please bear with me. I'm trying to
> create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
> am an admin on the AS box. I am using this tutorial:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee--6d86797c8f28.htm
> I set up the data source, then I hit "generate model". I type in my model
> name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
> using "credentials supplied by the user running the report" dialog box with
> the "Use as Windows..." checkbox checked. For good measure I have even added
> myself to a cube Role with admin privelages. Alos, I can in fact browse and
> manipulate the cube.
> Any ideas?
> TIA, ChrisR
create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
am an admin on the AS box. I am using this tutorial:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee-6d86797c8f28.htm
I set up the data source, then I hit "generate model". I type in my model
name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
using "credentials supplied by the user running the report" dialog box with
the "Use as Windows..." checkbox checked. For good measure I have even added
myself to a cube Role with admin privelages. Alos, I can in fact browse and
manipulate the cube.
Any ideas?
TIA, ChrisRHi
I think the issue here is not the users priviliges in SSAS , rather
the priviliges in SSRS. Try using report manager to configure
permissions for the user in question .
Cheers
Shai
On Nov 20, 12:34 am, ChrisR <Chr...@.discussions.microsoft.com> wrote:
> Greetings. I am brand new to SRS, so please bear with me. I'm trying to
> create a Report Model in SRS 2005 against an Analysis Services 2005 cube. I
> am an admin on the AS box. I am using this tutorial:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/8e5d2bd3-48ec-45f3-afee--6d86797c8f28.htm
> I set up the data source, then I hit "generate model". I type in my model
> name, hit "OK", and get the "Logon failed (rsLogonFailed)" message. I am
> using "credentials supplied by the user running the report" dialog box with
> the "Use as Windows..." checkbox checked. For good measure I have even added
> myself to a cube Role with admin privelages. Alos, I can in fact browse and
> manipulate the cube.
> Any ideas?
> TIA, ChrisR
"Login Failed" for SQL Express
Whenever I try to connect to my SQL Express Database with C# code,(.net 2.0), it says "Login failed for user 'TUser'. The user is not associated with a trusted SQL Server connection."
But I can connect with SQL Express Manager.
I've heard somewhere this is a SQL authentication problem, I didn't enable it during installation.
So is there a way to fix this? How can I enable SQL authentication without uninstalling it?
Is there anyone to help me?
Subscribe to:
Posts (Atom)
