Data Flow Task Problem: current

Showing posts with label current. Show all posts

Tuesday, March 27, 2012

Data File and Log File

In our current scanario log and data file on the same drive, I want to move
the log file into another drive for optimization and database is in use. how
can I
Current Scanario :-
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2C:\MSSQL\data\MyDB1.ldf
Desiring Scanario
MyDB1_DATA 1C:\MSSQL\data\MyDB1.mdf
MyDB1_TLOG 2D:\MSSQL\data\MyDB1.ldf
>> I want to move the log file into another drive for optimization and[vbcol=seagreen]
You cannot do this while the database is in use. One easy alternative is to
detach the database and move the log file to the new drive. When you attach
the database to the server, specify the new location of the logfile.
Anith

Monday, March 19, 2012

data design question - record versioning

Wanted to get feedback from everyone. Current project requires the
need on certain key tables to keep versions of records, and to
possibly use those older versions to selectively rollback. The
business needs the ability to selectively rollback everything that
happened in the data load last Wednesday, for example, but keep all
data loads since then. So I'm exploring history/auditing tables,
filled by triggers, but it seems like a huge pain. I also saw an
article by Ben Allfree ( http://www.codeproject.com/useritems/LisRecordVersioning.asp
) on codeproject that was interesting, but was conceptually quite
different, with some detractors in the feedback. Surely this is a
common issue. What are everyone's thoughts?Common issue, no common answer. I'd say it is dependent on your application
(just like we DBA like to put it). The ratio of active and inactive records
plays an important role in here.
You may also consider set up (indexed) view to help address the issue.
Quentin
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
> Wanted to get feedback from everyone. Current project requires the
> need on certain key tables to keep versions of records, and to
> possibly use those older versions to selectively rollback. The
> business needs the ability to selectively rollback everything that
> happened in the data load last Wednesday, for example, but keep all
> data loads since then. So I'm exploring history/auditing tables,
> filled by triggers, but it seems like a huge pain. I also saw an
> article by Ben Allfree (
> http://www.codeproject.com/useritems/LisRecordVersioning.asp
> ) on codeproject that was interesting, but was conceptually quite
> different, with some detractors in the feedback. Surely this is a
> common issue. What are everyone's thoughts?
>|||Hmmmm - that's what I was afraid the answer would be. I was hoping
there was a magical, documented, easy to implement solution.
There will be one and only one active record at any given time. There
may be 1-n history records. But realistically I doubt there would be
less than 15 history records associated with any given record. And
probably < 10% of all records would have any history at all. Majority
would be fresh inserts.
On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
wrote:
> Common issue, no common answer. I'd say it is dependent on your application
> (just like we DBA like to put it). The ratio of active and inactive records
> plays an important role in here.
> You may also consider set up (indexed) view to help address the issue.
> Quentin
> "CoreyB" <unc27...@.yahoo.com> wrote in message
> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>
> > Wanted to get feedback from everyone. Current project requires the
> > need on certain key tables to keep versions of records, and to
> > possibly use those older versions to selectively rollback. The
> > business needs the ability to selectively rollback everything that
> > happened in the data load last Wednesday, for example, but keep all
> > data loads since then. So I'm exploring history/auditing tables,
> > filled by triggers, but it seems like a huge pain. I also saw an
> > article by Ben Allfree (
> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
> > ) on codeproject that was interesting, but was conceptually quite
> > different, with some detractors in the feedback. Surely this is a
> > common issue. What are everyone's thoughts... Hide quoted text -
> - Show quoted text -|||I'd go with the active/passive flag. Sounds at most the inactive records
would be as many as the active records, if not drastically less. Putting
adequate index (on the columns PK and that states "same group") will almost
eliminate any access contention problems due to this active/passive issue.
<unc27932@.yahoo.com> wrote in message
news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
> Hmmmm - that's what I was afraid the answer would be. I was hoping
> there was a magical, documented, easy to implement solution.
> There will be one and only one active record at any given time. There
> may be 1-n history records. But realistically I doubt there would be
> less than 15 history records associated with any given record. And
> probably < 10% of all records would have any history at all. Majority
> would be fresh inserts.
> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> > Wanted to get feedback from everyone. Current project requires the
>> > need on certain key tables to keep versions of records, and to
>> > possibly use those older versions to selectively rollback. The
>> > business needs the ability to selectively rollback everything that
>> > happened in the data load last Wednesday, for example, but keep all
>> > data loads since then. So I'm exploring history/auditing tables,
>> > filled by triggers, but it seems like a huge pain. I also saw an
>> > article by Ben Allfree (
>> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> > ) on codeproject that was interesting, but was conceptually quite
>> > different, with some detractors in the feedback. Surely this is a
>> > common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>|||Quentin Ran wrote:
> I'd go with the active/passive flag. Sounds at most the inactive records
> would be as many as the active records, if not drastically less. Putting
> adequate index (on the columns PK and that states "same group") will almost
> eliminate any access contention problems due to this active/passive issue.
I would also consider having a separate history table instead of
active/inactive flag.
Since the rollover/rollback operations are expected to happen
significantly less often than active record access/update, keeping the
history in a separate table may make sense.
Y.S.
> <unc27932@.yahoo.com> wrote in message
> news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
>> Hmmmm - that's what I was afraid the answer would be. I was hoping
>> there was a magical, documented, easy to implement solution.
>> There will be one and only one active record at any given time. There
>> may be 1-n history records. But realistically I doubt there would be
>> less than 15 history records associated with any given record. And
>> probably < 10% of all records would have any history at all. Majority
>> would be fresh inserts.
>> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
>> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> Wanted to get feedback from everyone. Current project requires the
>> need on certain key tables to keep versions of records, and to
>> possibly use those older versions to selectively rollback. The
>> business needs the ability to selectively rollback everything that
>> happened in the data load last Wednesday, for example, but keep all
>> data loads since then. So I'm exploring history/auditing tables,
>> filled by triggers, but it seems like a huge pain. I also saw an
>> article by Ben Allfree (
>> http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> ) on codeproject that was interesting, but was conceptually quite
>> different, with some detractors in the feedback. Surely this is a
>> common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>

Friday, February 24, 2012

DAO > SQL Server Processes

I need to know exactly what VBA lines Create and Destroy SQL Server
Processes - i.e. those visible in Enterprise Manager under Management
> Current Activity > Process Info.

Why?

I am experiencing strange behaviour with Processes that are created
when I create a DAO Database Object with the following line:

Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,
False, strODBC)

This creates the process as expected.

However the following lines don't always close the ensuing Process:

If Not m_ResRecordSet Is Nothing Then
m_ResRecordSet.Close
Set m_ResRecordSet = Nothing
End If
If Not m_ResDatabase Is Nothing Then
m_ResDatabase.Close
Set m_ResDatabase = Nothing
End If
If Not m_ResWorkspace Is Nothing Then
m_ResWorkspace.Close
Set m_ResWorkspace = Nothing
End If

It seems as if SQL Server keeps hold of the first two Processes and
then will release any subsequent ones.
Can anyone shed any light in this - or any good web pages where I
might find some answers?

Regards ChrisChris (chris.laycock@.addept.co.uk) writes:
> I am experiencing strange behaviour with Processes that are created
> when I create a DAO Database Object with the following line:
> Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,
> False, strODBC)
> This creates the process as expected.
> However the following lines don't always close the ensuing Process:
> If Not m_ResRecordSet Is Nothing Then
> m_ResRecordSet.Close
> Set m_ResRecordSet = Nothing
> End If
> If Not m_ResDatabase Is Nothing Then
> m_ResDatabase.Close
> Set m_ResDatabase = Nothing
> End If
> If Not m_ResWorkspace Is Nothing Then
> m_ResWorkspace.Close
> Set m_ResWorkspace = Nothing
> End If
> It seems as if SQL Server keeps hold of the first two Processes and
> then will release any subsequent ones.
> Can anyone shed any light in this - or any good web pages where I
> might find some answers?

Do DAO have connection pooling? Modern client libraries have connection
pooling, which means that when you close a connection from the code,
the API lingers on the connection for a minute, in case you would
reconnect directly. In such case, it's perfectly normal to see the
connections around.

Else, the only reason I can think of is that you had a transaction in
progress when you closed the connections, and the rollback takes a
long time. If you vie the processes with sp_who what state and active
command do they have?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Do DAO have connection pooling? Modern client libraries have connection
> pooling, which means that when you close a connection from the code,
> the API lingers on the connection for a minute, in case you would
> reconnect directly. In such case, it's perfectly normal to see the
> connections around.
> Else, the only reason I can think of is that you had a transaction in
> progress when you closed the connections, and the rollback takes a
> long time. If you vie the processes with sp_who what state and active
> command do they have?

I've seen this in Access and it's a PITA as it makes changing users
impossible without restarting it, e.g. log in as "sa" then close
everything and log in as "joe", the front end thinks you are "joe" but
the back end thinks you are "sa", which can cause unpredictable results.

If it's connection pooling in place then I don't think it was
implemented right. I've seen it work the other way as well while logged
in as normal user I then try to log in as "sa" to manage users, etc and
get told I have no permission to do it.

Sunday, February 19, 2012

Daily report generating Monthly rollup stats

I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.

SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP,
Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION
FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE
WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position

SELECT rptdate = GETDATE() - 1
, Errors.WTG_ID
, lookup.Phase
, Errors.STATUS_TYPE
, Errors.STATUS_CODE
, STATUS_CODES.STATUS_DEF
, Errors.TIME_STAMP
, Errors.ANSI_TIME
, lookup.WTG_TYPE
, Errors.POSITION

FROM Errors

INNER JOIN lookup
ON Errors.WTG_ID = lookup.WTG_id

RIGHT JOIN STATUS_CODES
ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE
AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE

WHERE STATUS_CODES.STATUS_DEF IS NOT NULL
-- You're interested in summarising all days in a
-- month so only perform comparisson on Year and Month
-- For 1st of Jan subtract a year
-- For first of any month subtract a month

AND Year(Errors.TIME_STAMP) = Year(GETDATE())
- CASE
WHEN Month(GETDATE()) = 1
AND Day(GETDATE()) = 1 -- 1st Jan
THEN 1
ELSE 0
END
AND Month(Errors.TIME_STAMP) = Month(
DATEADD(
, mm
, GETDATE()
, CASE
WHEN Day(GETDATE()) = 1
-- 1st of the month
THEN -1
ELSE 0
END
)
)

ORDER BY Errors.WTG_ID
, Errors.TIME_STAMP
, position

|||I appreciate the information - I used Jan 1 as an example because they when it truely happened. Forgivie my ignorance, but will the same code work for the 1st of any month thus pulling the previous month's data?|||The above code caters for both cases (read the comments). Normally you just subtract a month, but with 1st of Jan you also have to subtract a year (at least that's how my example works).

Tuesday, March 27, 2012

Data File and Log File

Monday, March 19, 2012

data design question - record versioning

data design question - record versioning

data design question - record versioning

Friday, February 24, 2012

DAO > SQL Server Processes

Sunday, February 19, 2012

Daily report generating Monthly rollup stats

Data Flow Task Problem

Blog Archive

About Me