layout problem

hi all,

i got a table which shows me the number of transfers of a store on everyday day together with the summed amount the store gained. my table looks like this:

store day transfers amount

1 1 1 2.50

1 2 5 3.50

2 1 2 1

and i want to put it in a report like this

store monday tuesday

1 1 / 2.50 5 / 3.50

2 2 / 1

i tried with a matrix but the the result wasnt satisifiying. the best one looked like this:

store monday tuesday

1 1 / 2.50

1 5 / 3.50

2 2 / 1

anyone can give a solution or atleast some hints?

drop store to the row group and day to the column group should satisfy your need.

Layout Issue

I have a dataset that goes like this

1

2

3

4

5

6

Using a table control, report preview appears as above.

I want the report to look like

1 2 3

4 5 6

How can I generate a report like this ?

Thanks

Is there any other control I can use to get the desired output.

Thanks !!

launch an appl from a trigger ?

Is there a way to launch an application(c:\hello.exe)
from a trigger when a new record is inserted into the
table or
a record is updated in the table?
If so, what is the correct syntax ?
THANKS!Understand that launching an application that requires the input from a =user can extremely critical ... This is because this app is invoked in =the same process of SQL Server and the credential of the user to =interact with desktop maynot be there ... Hence such operation can stop =the SQL Server service ... Hence avoid such operations ...
On the contrary you like to send a popup using the network messenger =service then use the xp_cmdshell command ... But it is not recommended =in production code ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"amy" <achen@.comverge.com> wrote in message =news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the > table or > a record is updated in the table?
> > If so, what is the correct syntax ?
> > THANKS!|||Amy,
You can use the xp_cmdshell extended procedure to start an application from
the command prompt. For exact syntax, do please refer to Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"amy" <achen@.comverge.com> wrote in message
news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the
> table or
> a record is updated in the table?
> If so, what is the correct syntax ?
> THANKS!|||I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible. If it
did work, it would get around the issues raised here
about having the .exe hang things up, etc.
>--Original Message--
>Amy,
>What are you trying to do ? Ofcourse you can launch an
application by using
>master..xp_cmdshell, as in
>exec master..xp_cmdshell "dtsrun /?"
>But for every insert and update? well , no..I wont do
that.Also, if this
>hello.exe has any GUI as in exec
master..xp_cmdshell "notepad" ,
>master..xp_cmdshell would hang leading to timeout
problems.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"amy" <achen@.comverge.com> wrote in message
>news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> Is there a way to launch an application(c:\hello.exe)
>> from a trigger when a new record is inserted into the
>> table or
>> a record is updated in the table?
>> If so, what is the correct syntax ?
>> THANKS!
>
>.
>|||Steve,
Since you replied to my post...
It may work or may be we can find a workaround but I still wont do that.In a
heavy OLTP system, triggers are always a overhead for the DML and a trigger
which calls a exe, gui or no gui , would be a recipe for disaster.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> I would investigate having the .exe be part of a "job"
> under DTS Local Packages and having the trigger start
> that job - not really sure if this is possible. If it
> did work, it would get around the issues raised here
> about having the .exe hang things up, etc.
> >--Original Message--
> >Amy,
> >
> >What are you trying to do ? Ofcourse you can launch an
> application by using
> >master..xp_cmdshell, as in
> >
> >exec master..xp_cmdshell "dtsrun /?"
> >
> >But for every insert and update? well , no..I wont do
> that.Also, if this
> >hello.exe has any GUI as in exec
> master..xp_cmdshell "notepad" ,
> >master..xp_cmdshell would hang leading to timeout
> problems.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"amy" <achen@.comverge.com> wrote in message
> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> Is there a way to launch an application(c:\hello.exe)
> >> from a trigger when a new record is inserted into the
> >> table or
> >> a record is updated in the table?
> >>
> >> If so, what is the correct syntax ?
> >>
> >> THANKS!
> >
> >
> >.
> >|||I agree that other paths might serve the same purpose,
but I'm not sure what the need really is. It would make
more sense to me to have a "scheduled" job run regularly
(every x minutes) and sweep up handling all the "rows"
inserted since the last sweep. Or code the .exe in C and
make it an extended stored procedure - run right from
inside the trigger. Or simply code the need in T-Sql and
get rid of the .exe requirement...
>--Original Message--
>Steve,
>Since you replied to my post...
>It may work or may be we can find a workaround but I
still wont do that.In a
>heavy OLTP system, triggers are always a overhead for
the DML and a trigger
>which calls a exe, gui or no gui , would be a recipe for
disaster.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
>> I would investigate having the .exe be part of a "job"
>> under DTS Local Packages and having the trigger start
>> that job - not really sure if this is possible. If it
>> did work, it would get around the issues raised here
>> about having the .exe hang things up, etc.
>> >--Original Message--
>> >Amy,
>> >
>> >What are you trying to do ? Ofcourse you can launch an
>> application by using
>> >master..xp_cmdshell, as in
>> >
>> >exec master..xp_cmdshell "dtsrun /?"
>> >
>> >But for every insert and update? well , no..I wont do
>> that.Also, if this
>> >hello.exe has any GUI as in exec
>> master..xp_cmdshell "notepad" ,
>> >master..xp_cmdshell would hang leading to timeout
>> problems.
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"amy" <achen@.comverge.com> wrote in message
>> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> >> Is there a way to launch an application
(c:\hello.exe)
>> >> from a trigger when a new record is inserted into
the
>> >> table or
>> >> a record is updated in the table?
>> >>
>> >> If so, what is the correct syntax ?
>> >>
>> >> THANKS!
>> >
>> >
>> >.
>> >
>
>.
>|||Steve,
Well if you are asking which of the requirement to take then, my =suggestion would be to use the T-SQL method and a jobs framework ... It =is simply because you are closer to the data and getting out of process =of SQL and then get the job done is something not advisable ...
Now trigger can prove costlier as this will fire with every insert =operation ... But would make it more live data ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Steve Z" <szlamany@.antarescomputing.com> wrote in message =news:0eb201c35779$6045c3b0$a401280a@.phx.gbl...
> I agree that other paths might serve the same purpose, > but I'm not sure what the need really is. It would make > more sense to me to have a "scheduled" job run regularly > (every x minutes) and sweep up handling all the "rows" > inserted since the last sweep. Or code the .exe in C and > make it an extended stored procedure - run right from > inside the trigger. Or simply code the need in T-Sql and > get rid of the .exe requirement...
> >--Original Message--
> >Steve,
> >
> >Since you replied to my post...
> >
> >It may work or may be we can find a workaround but I > still wont do that.In a
> >heavy OLTP system, triggers are always a overhead for > the DML and a trigger
> >which calls a exe, gui or no gui , would be a recipe for > disaster.
> >
> >-- > >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"Steve Z" <szlamany@.antarescomputing.com> wrote in > message
> >news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> >> I would investigate having the .exe be part of a "job"
> >> under DTS Local Packages and having the trigger start
> >> that job - not really sure if this is possible. If it
> >> did work, it would get around the issues raised here
> >> about having the .exe hang things up, etc.
> >>
> >> >--Original Message--
> >> >Amy,
> >> >
> >> >What are you trying to do ? Ofcourse you can launch an
> >> application by using
> >> >master..xp_cmdshell, as in
> >> >
> >> >exec master..xp_cmdshell "dtsrun /?"
> >> >
> >> >But for every insert and update? well , no..I wont do
> >> that.Also, if this
> >> >hello.exe has any GUI as in exec
> >> master..xp_cmdshell "notepad" ,
> >> >master..xp_cmdshell would hang leading to timeout
> >> problems.
> >> >
> >> >-- > >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"amy" <achen@.comverge.com> wrote in message
> >> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> >> Is there a way to launch an application
> (c:\hello.exe)
> >> >> from a trigger when a new record is inserted into > the
> >> >> table or
> >> >> a record is updated in the table?
> >> >>
> >> >> If so, what is the correct syntax ?
> >> >>
> >> >> THANKS!
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Latin to Cyrillic

Hi everybody, i'm a new SQL server user and i have a problem
I have to select a loto of cyrillic records saved on a table that has
Latin collation
How can i see them in Cyrillic?
i just tried to make another table with a column with Cyrillic
collation but it won't work
Thank you
FedericoFederico,
I guess the newsgroups on www.sql.ru is the best place to ask this
question.

Latest value for all members at a given point in time

We have a fact table with a current balance record. For this balance rows are added to the table only when the balance has changed. This way we can reduce the volume in the fact table by not saving redundant information for every point in time that we want to analyze.

In SQL it is a simple task to get a view of the lastest fact records for a given point in time. SELECT f1.* FROM factTable f1 WHERE f1.timestamp = (SELECT MAX(f2.timestamp) FROM factTable f2 WHERE f2.timestamp <= givenPointInTime AND f2.balanceKey = f1.balanceKey). In other words, the given point in time directly determines which rows should be fetched from our fact table, one row for each balance record (balanceKey). This row will of course include several dimension attributes, which represent the point in time when the balance changed.

Now, we would like to reproduce this behaviour in our Analysis Services 2005 cube. Using a time dimension, we would like to be able to select a given point in time and have the cube return all rows previous to that time, one row for each balanceKey. Not only the rows matching the given point in time. How can this be done? For the other dimension attributes, they should behave in a normal way, such that for the returned rows, you can slice using any combination of them.

Any help or ideas are appreciated,
Lars

Assuming that you're using AS 2005 Enterprise Edition, you could try the "Last Nonempty Child" aggregation:

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

>>

SQL Server 2005 Books Online

Defining Semiadditive Behavior

Semiadditive measures, which do not uniformly aggregate across all dimensions, are very common in many business scenarios. Every cube that is based on snapshots of balances over time exhibits this problem. You can find these snapshots in applications dealing with securities, account balances, budgeting, human resources, insurance policies and claims, and many other business domains.

...

LastNonEmpty

The member value is evaluated as the value of its last child along the time dimension that contains data.

>>

|||

Thanks Deepak. We are on an Enterprise Edition (pre-SP1) but we do not get the desired behaviour using the "Last Nonempty Child" aggregation. In fact, it doesn't seem to make any difference at all from using SUM. We have created an example containing a relational database with a simple fact table and an analysis services database built upon it with a measure called "Account Balance". If we browse the cube and add the measure and the Account dimension to the results pane, then filter based on the Time dimension and select the date 2005-12-31, Account number 3 should display the balance 400. Currently it does not.

The example (SQL script + XMLA script) can be found at: http://www.intellibis.se/pub/CumulativeExample.zip

Can anyone help us?

Regards,
Lars

sql

Latest record

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display latest order from the customer

You can use ORDER BY.

Select O.OrderId, C.Name, O.Product, O.DateFROM Order OJOIN Customer CON C.CustomerId O.CustomerIdORDER BY O.DateDesc

|||

SELECT orderId, customerId, product,[date], [name]FROM(select a.orderId, a.customerId, a.product,a.[date], b.name, row_number()over(partitionby a.customeridorderby [date]DESC)as RowNum

from [Order] ainnerjoin [Customer] bon a.customerId=b.customerId) t

WHERE RowNum= 1

sql

latest order date

Hi,
I have an order table which contains the following fielde: 1). orderid (this
is the order number 2). clientid 3). orderdate.
I need to script so that I can find out those clientids which do not have
place an order for at least 90 days.
Can you tell me how to program it?Read the documentation. You want to select clientids which are NOT IN a
subquery that selects all clients that have ordered in the last 90 days. You
could also use a join where max order date is less than 90 days ago.
RR
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
(this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Something like this should give you the client and their last order date.
declare @.DaysSinceOrder as numeric
set @.DaysSinceOrder = 90
select
clientid ,
max(orderdate)
from "YourTableHere"
group by clientid
having max(orderdate) < getdate()-@.DaysSinceOrder
"qjlee" wrote:

> Hi,
> I have an order table which contains the following fielde: 1). orderid (th
is
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Please post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006 )
Since you have a ClientID column in your Orders table, I am guessing that
you have a Clients table somewhere. Here is a complete guess:
SELECT ClientID, ClientName
FROM Clients
WHERE ClientID NOT IN (SELECT ClientID FROM Orders WHERE DATEDIFF(d,
OrderDate, CURRENT_TIMESTAMP) <= 90)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
> (this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>

Latest modification time of a table

Is there any way to get the latest modification time of a table (modified by
either others or myself)?
TIAUnfoortunately, the only way to do this is to write code, (a trigger,
probbaly) that would record, (somewhere) the current datetime whenever
someone updates inserts or deletes a record in the table. If that is not
being done, then you'reout of luck for past changes, but you can implement
something like this for future changes, if you wanted...
"Krygim" wrote:

> Is there any way to get the latest modification time of a table (modified
by
> either others or myself)?
> TIA
>
>

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest enabled version of every document (was "help with sql please!")

Hi, I'm having trouble with some SQL. I have a document table which links to a document version table. I'd like to write a query to get the latest enabled version of every document - a resultset with one row per document. Is this possible with resorting to a cursor?

CREATE TABLE tblDoc (
DocId int
)
GO

CREATE TABLE tblDocVersion (
DocVersionId int,
DocId int,
DateCreated datetime,
IsEnabled bit
)
GO

INSERT INTO tblDoc (DocId) VALUES (1)
INSERT INTO tblDoc (DocId) VALUES (2)

INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '9/9/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '10/10/2006' , 0)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '11/11/2006' , 1)

Desired output:

DocId DocVersionId DateCreated
============================
1 2 9/9/2006
2 5 11/11/2006

Thanks!
MrsMUse a subquery:

select tblDocVersion.DocId,
tblDoVersion.DocVersionID,
tblDocVersion.DateCreated
from tblDocVersion
inner join --LastVersions
(select DocId,
max(DateCreated) as DateCreated
from tblDocVersion
group by DocId) LastVersion
on tblDocVersion.DocId = LastVersion.DocID
and tblDocVersion.DateCreated = LastVersion.DateCreated

By the way...why do you prefix your table name with "tbl"? Are you afraid you are going to forget what it is? Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?|||ah nice, joining on a sub query - never thought of that, thanks a lot.

the tbl thing is part of the standards at my company. doesn't do anyone any harm!

thanks again|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!that's highly doubtful

not the "part of the standards at my company" part

the other part

:)|||Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?

hah! :)

i always hated hungarian. intellisense seems to be slowly killing it off in the world of compiled code thankfully. from the bible of umaintainable code (http://mindprod.com/jgloss/unmain.html):

a_crszkvc30LastNameCol:

"It took a team of maintenance engineers nearly 3 days to figure out that this whopper variable name described a const, reference, function argument that was holding information from a database column of type Varchar[30] named "LastName" which was part of the table's primary key"|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!Why don't you suggest a standard that all desktop PCs be painted schoolbus yellow, all laptops be candy-striped red, and all printers have blue polka-dots? Also harmless, and just as sensible.|||I knew one team at MS that had a convention of putting _sp at the end of every sproc.

Presumably this was because they knew there was some downside to putting sp_ at the front.

I'm sure it really helped them distingush procs from tables, which, btw, all had _tbl at the end. ;)

latency on transaction replication

I am using transactional replication for a 100GB database that has 2 nosync
subscribers. The pull subscriptions are segmented into like table groups.
They all use a custom profile for the distribution agents that I tweaked
over time to minimize latecy. In the last month I have noticed that 1 of the
subscriptions always has a higher latency in EM then the others. After
looking into several things, we also found out that there was a
hardware/firmware problem with Dell, so we ahve applied that patch and it
has fixed a number of other problems in the DB, but still not the latency
problem on this one subscription. Some of the other subscriptions have much
bigger tables and data changes, so I am at a loss of why this one is causing
me so many problems. I frequently will have to apply a snapshot of this
subscription because it will get so far behind, and then will slwly build
back up again until another snapshot is required.
Can anyone help?
Here are some of the current configs of the custom profile:
BcpBatchsize: 100000
CommitBatchSize: 1000
CommitBatchThreshold: 5000
PollingInterval:1
Thanks,
Kristy
Well nevermind for now. Seems like it's finally leveling off today after I
redid the subscription last night. All day it's been getting smaller (now
down to 10 minutes and the others being in the seconds); where as before it
would just get bigger and bigger (up to days behind.)
I suspect that in addition to the firmware updates and reindexing some
corrupted indexes have helped. But I am just guessing.
--Kristy
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:eeniL84RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> I am using transactional replication for a 100GB database that has 2
nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of
the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have
much
> bigger tables and data changes, so I am at a loss of why this one is
causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>

latecny

How can I get an idea of how long it takes for a transaction after it is made in my table I am publishing before it hits the subscriber?
TIA
Miriam
Miriam,
have a look at the Performance monitor. There are several counters for
latency eg Replication Dist. Dist: Delivered Latency.
HTH,
Paul Ibison
|||I don't like the performance monitor counters as they aren't granular
enough. For instance I could have a publication and subscriber on the same
server which will skew these counter values very high.
I prefer to do the following, on my subscriber in the subscription database
I issue a
alter table MSreplication_subscriptions
alter column time datetime
then on my publisher I issue the following
sp_serveroption 'SubscriberServerName','data access','true'
and then in my distribution database I do the following
select time, entry_time from
SubscriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
msrepl_transactions
where transaction_timestamp=xact_seqno
This will show when the distribution agent last replicated something to the
subscription database. This could be the last command or the last time the
distribution agent started.
The difference between time and entry_time is the time the transaction hit
the distribution database and the time it hit the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23T7fKHeWEHA.500@.TK2MSFTNGP09.phx.gbl...
> Miriam,
> have a look at the Performance monitor. There are several counters for
> latency eg Replication Dist. Dist: Delivered Latency.
> HTH,
> Paul Ibison
>
|||Hi Miriam,
Assuming transactional replication here, you could monitor the dataflow by
querying MSlogreader_history (Publisher->Distributor) &
MSdistribution_history(Distributor->Subscriber).
Alternatively, create a dummy table with three columns at publisher two of
datatype datetime and at the subscriber attach a trigger on the table which
calls getdate() to populate one of the datetime columns.
Regards,
Imtiaz Govani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Would this trigger fire over the network? So supposed I have two servers 1)
the Publisher, and 2) the Subscriber. On the Subscriber table, when a record
arrives in the table I am replicating to, I create a insert/update/delete
trigger that will write to my distribution database on my Publisher that a
replicated record has arrived?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Imtiaz Govani" <imtiazg@.online.microsoft.com> wrote in message
news:3GOlszCZEHA.3272@.cpmsftngxa06.phx.gbl...
> Hi Miriam,
> Assuming transactional replication here, you could monitor the dataflow by
> querying MSlogreader_history (Publisher->Distributor) &
> MSdistribution_history(Distributor->Subscriber).
> Alternatively, create a dummy table with three columns at publisher two of
> datatype datetime and at the subscriber attach a trigger on the table
which
> calls getdate() to populate one of the datetime columns.
> Regards,
> Imtiaz Govani [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||Hi,
Step 1: Create this table in your publishing database
CREATE TABLE ReplLatency
(
c1 INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
pubtime DATETIME NOT NULL DEFAULT getdate(),
subtime DATETIME NULL
)
Step 2: Create a separate publication for this table
Step 3: Push this publication to the existing subscribers.
You can do this even if there are already existing publications on the
database.
Step 4: During synchronisation, the subscriber will receive the following
"Insert Stored Procedure" as part of the schema:
Add the blue line below to the Insert Stored Procedure
create procedure "sp_MSins_ReplLatency" @.c1 int,@.c2 datetime,@.c3 datetime
AS
BEGIN
select @.c3 = getdate() -- This line was added to obtain the time when
the record arrived at the subscriber
insert into "ReplLatency"(
"c1", "pubtime", "subtime"
)
values (
@.c1, @.c2, @.c3
)
END
GO
Step 5: Add a row to the publisher every xx minutes with the following
script:
WHILE 1=1
BEGIN
INSERT ReplLatency DEFAULT VALUES
WAITFOR DELAY '00:01:00'
END
Or use a job that executes the INSERT command every xx minutes.
#Using the metrics
SELECT DATEDIFF(SS,pubtime,subtime) AS Latency_in_Seconds, * FROM
ReplLatency
--To find out when we had the maximum latency:
SELECT DATEDIFF(SS,pubtime,subtime) AS Maximum_Latency_in_Seconds,* FROM
ReplLatency
WHERE (DATEDIFF(SS,pubtime,subtime)) = (SELECT MAX(
DATEDIFF(SS,pubtime,subtime)) FROM ReplLatency )
--Or the 10 longest times:
SELECT TOP 10 DATEDIFF(SS,pubtime,subtime),* FROM ReplLatency ORDER BY 1
DESC
Regards,
Imtiaz Govani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Latching Errors

Hi all,

We are trying to load a batch in a huge table ( 150 GB) , but sometimes we are getting timeout errors .

When i see in activity monitor, i can see some entries like PAGEIOLATCH_EX, PAGEIOLATCH_SH.

Can somebody suggets me the workaround for this problem.

Thanks for all your help.

Configuration:

Sql Server 2005 SP1, 4GB Memory, Windows 2003 standard Edition SP1

Could you post an example of the error you are getting?

Can you check what level of growth is specified for you data/log files please? eg 10% or 100MB....
|||

Hi Rich,

Growth for the data file = 2000 MB , Unrestricted Growth. and log file = 10% Unrestricted growth.

And about error, when i see in the activity monitor , i can see PAGEIOLATCH_EX, PAGEIOLATCH_SH, in the wait type column. ( its taking a long time, for rolling back the batch)

Is there anything about indexes , which affects this kind of error.

Thanks.

|||

Are you getting errors in the SQL Error Log? If so, could you copy and paste one of these errors into the thread as it may help diagnose the problem?

|||

In general, Significant wait times on page latches is often related to disk issues. You'd generally want to watch your disk counters in Perf Mon.

But what about your log during this process - is it large enough to handle the load? What is 10% growth - meaning, what size is it trying to grow to if this is the problem. I generally don't have grow logs by a percent - you have no control over what size increments it's growing. If it's a large file you are trying to load, you would also want to try batching the inserts.

-Sue

Latching Errors

Hi all,

We are trying to load a batch in a huge table ( 150 GB) , but sometimes we are getting timeout errors .

When i see in activity monitor, i can see some entries like PAGEIOLATCH_EX, PAGEIOLATCH_SH.

Can somebody suggets me the workaround for this problem.

Thanks for all your help.

Configuration:

Sql Server 2005 SP1, 4GB Memory, Windows 2003 standard Edition SP1

Could you post an example of the error you are getting?

Can you check what level of growth is specified for you data/log files please? eg 10% or 100MB....
|||

Hi Rich,

Growth for the data file = 2000 MB , Unrestricted Growth. and log file = 10% Unrestricted growth.

And about error, when i see in the activity monitor , i can see PAGEIOLATCH_EX, PAGEIOLATCH_SH, in the wait type column. ( its taking a long time, for rolling back the batch)

Is there anything about indexes , which affects this kind of error.

Thanks.

|||

Are you getting errors in the SQL Error Log? If so, could you copy and paste one of these errors into the thread as it may help diagnose the problem?

|||

In general, Significant wait times on page latches is often related to disk issues. You'd generally want to watch your disk counters in Perf Mon.

But what about your log during this process - is it large enough to handle the load? What is 10% growth - meaning, what size is it trying to grow to if this is the problem. I generally don't have grow logs by a percent - you have no control over what size increments it's growing. If it's a large file you are trying to load, you would also want to try batching the inserts.

-Sue

lastUpdatedTs and userID from Inserted table

I wrote the following trigger:

CREATE TRIGGER dbo.tru_Employee
ON Employee
AFTER update
AS

DECLARE @.lastUpdatedTs datetime
DECLARE @.table NVARCHAR(50)
DECLARE @.transID INT
DECLARE @.userID INT

SET @.table='Employee'

SELECT @.userID = lastUpdatedBy,
@.lastUpdatedTs=lastUpdatedTs
FROM inserted

INSERT INTO AUD_tracking VALUES(@.table,@.lastUpdatedTs,@.userID)
SET @.transID=@.@.IDENTITY

Every time this triger fires it always reads the same value for userID and lastUpdatedBy. It's like 'inserted' table is not even affected by my UPDATE statement while @.oldvalue and @.newvalue that I populated the other table with (Tracking details table) are just fine.

What could it be?
ThanksYour trigger fires once for each operation, even if many records were updated. Try this more common syntax:

INSERT INTO AUD_tracking (Table, LastUpdatedTs, UserID)
SELECT 'Employee', LastUpdatedTs, LastUpdatedBy
FROM Inserted

Tip: I like to add a field to my audit table where I can store the Primary Key of the affected records so that I also know what was altered. Sometimes, I even store the before and after values. It makes a thorough paper trail that has proven helpful in diagnosing problems and averting blame.

blindman|||Originally posted by blindman
Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:


Thank you for your reply.

I am sorry for giving a confusing information. This was only a beginning part of the trigger. It is followed by the part for updating each and every column. Also I am creating two tables: one that I already mentioned and the other one with tracking details(oldvalue, newvalue, etc.).

As for the timestamp amd userID being the same, I just noticed that lastUpdatedTs and lastUpdatedBy doesn't change on update in the original table either. At least when I simply run update with QA.
So I guess I have to go back to this on Monday.

last updated date

Hi,
I'm trying to retrieve data of previous day from my 'sales' table. Sales
table has:
order#, order date, cust#, sales, qty
I have a metadates table: one colummn
date
my script is
select sum(sales), sum(qty) from sales where order_date=(select top 1 date
from metadates order by date desc)
But I got time out all the time.
I'm considering the Christmas holiday, when I come back on Dec 28, the job
runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
Please help,
Thanks so much,
SarahPerhaps this will run faster:
declare @.prev_workday as datetime
select @.prev_workday = (select top 1 date from metadates order by date desc)
select sum(sales), sum(qty) from sales where order_date = @.prev_workday
So, if Dec 24 - 27 are not business days, then do not include them in the
metadates table.
"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
>|||"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
This may give you a better idea of what the date table should look like.
http://www.aspfaq.com/show.asp?id=2519sql

Last update of SQL-table

Hi All,
Can you tell how to diplay the last update of a table.
Many thanks,
Regards,"Hicham" <Mechmachi@.hotmail.com> wrote in message
news:01bc01c35abc$60cf0f80$a501280a@.phx.gbl...
> Can you tell how to diplay the last update of a table.
>
Unless you've coded your own trigger to archive table data before it's
updated, there is no way to display the last update.
Steve

Last Update date/time

Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql