Excel connection properities -

In my package I need to change the filename name for my input Excel based on a the contents of an external file. I want to read the external file, get the filename/location from this file and then dynamically set this as the property for the Excel Connection. I saw some posts related to this but did not understand which tasks I need to use and in which order.

thanks

My connection string is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

Delayed validation is True

I noticed my server name and excel file path are = to my source (=" + @.[User::ExcelFileName] + ") and if I change any of the 3 they all change.

When I run I get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Load training data [Read R013 Excel file [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Error at Load training data [DTS.Pipeline]: component "Read R013 Excel file" (1) failed validation and returned error code 0xC020801C.

Error at Load training data [DTS.Pipeline]: One or more component failed validation.

Error at Load training data: There were errors during task validation.

Error at PackageExcel [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK
-

thx

|||

Here is an expression that worked for me, exactly as entered in the Expression box. The double quotes around the Extended Properties value need to be doubled (and escaped). Note that in this case I was using a second user variable for the Extended Properties.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

-Doug

|||

Thanks for the response

When I paste in your code as is it gets changed automatically to this?:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

Doesn't like the (") in front of Provider? Any thoughts?

Tom

|||

Not sure how to explain that. But I did copy and paste exactly what appears in the Expression box in the Expression Builder dialog on my Excel Connection Manager's Expressions property...including the opening quote.

It's been a while since I ran that test when this issue first appeared in the forum, and I no longer recall all the variations that I tried.

The tricky quotes around the value of the Extended Properties argument are only required because there's more than 1 argument. If you just use "Excel 8.0" and allow the HDR argument to default to Yes, you can skip those quotes, which may well be your problem:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties=EXCEL 8.0;

-Doug

|||

I removed your variable for ext properties

have this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcellFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

still getting the error that it can't create file

Is there any way to view what's it's resolving the variable name to before it gets to the step of reading the excel file? I'm thinking that it is still not coming up w/ the correct file name. If I replace the variable w/ the hardcoded path/filename it works.

Thanks for your help

Tom

|||

I did get to see the name resolved and it looks fine

Tom

|||

I changed the variable to be the Provider instead of the Data source - this now gives me an error 'Class not Registered' - It seems like the implementation of the variable name has a syntax problem that formats it incorrectly.

Any suggestions?

Provider=" + @.[User::MyFileName] + ";

thx

|||

I got a bit lost in the thread above, but as an expression your example will not work. There is no opending quote and the semi-colon literal section is not quoted close. Maybe this forum mucks up the format but this should work-

"Provider=" + @.[User::MyFileName] + ";"

|||

Darren

when I tried to wrap the whole string w/ " the UI didn't like it and put a second 'Provider' at the end.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

In the above example I'm trying to set the data source w/ the parameter (what I really want) - when I couldn't get that to work I tried to use the parameter w/ Provider to see what would happen there.

Just to clarify what I'm doing (since I'm new to this), I'm setting the connectionstring property of my Excel connection manager

let me know if you have any other suggestions

thanks

|||

Just to be clear, you're entering this expression in the Expression Builder dialog box, right?

And in the Properties window for your Excel Connection Manager, the ConnectionString property itself is either (1) totally blank or (2) contains a complete, hard-coded, valid Jet/Excel connection string WITHOUT any of the expression syntax?

In other words, you're not entering the expression in the ConnectionString property directly?

|||

No I'm not - I had a feeling that I was doing something stupid - first time at using SSIS - I will give it a try - sorry for the confusion

Thx

Excel Coloring export and SP1

I currently have several formulas to change the background or text coloring
based on values in a report. All format render correctly except excel on out
put. If I have a column that changes to red when there is a negative value,
all numbers end up red in that column when exported to excel.
Is anyone else having this problem with SP1? Is there a work around or is
this a but? If it is a but is it fixed in SP2?
(I have asked users just to print to other formats but the are using the
excel data in excel)
ThanksTom,
This was an error I came across in SP1 quite some time ago, I think it
affects matrices, it was some time ago, I can't remember whether I told
Microsoft or not. We've been discouraging users from exporting to excel
which is why I haven't hit the problem again anyway.
I have SP2 Beta and will try it out for you later today. If it's an
error there I'll raise a bug log with MS.
Chris
Tom wrote:
> I currently have several formulas to change the background or text
> coloring based on values in a report. All format render correctly
> except excel on out put. If I have a column that changes to red when
> there is a negative value, all numbers end up red in that column when
> exported to excel.
> Is anyone else having this problem with SP1? Is there a work around
> or is this a but? If it is a but is it fixed in SP2?
> (I have asked users just to print to other formats but the are using
> the excel data in excel)
> Thanks|||Chris,
Any word on this if it is fixed in SP2? Is it still an open issue?
Thanks
Tom
"Chris McGuigan" wrote:
> Tom,
> This was an error I came across in SP1 quite some time ago, I think it
> affects matrices, it was some time ago, I can't remember whether I told
> Microsoft or not. We've been discouraging users from exporting to excel
> which is why I haven't hit the problem again anyway.
> I have SP2 Beta and will try it out for you later today. If it's an
> error there I'll raise a bug log with MS.
> Chris
>
> Tom wrote:
> > I currently have several formulas to change the background or text
> > coloring based on values in a report. All format render correctly
> > except excel on out put. If I have a column that changes to red when
> > there is a negative value, all numbers end up red in that column when
> > exported to excel.
> >
> > Is anyone else having this problem with SP1? Is there a work around
> > or is this a but? If it is a but is it fixed in SP2?
> >
> > (I have asked users just to print to other formats but the are using
> > the excel data in excel)
> >
> > Thanks
>

Evicting a Cluster Node

I have a 3 node cluster based on a Win2003 Server which is running SP1. Each
physical node also has one named instance of SQL Server 2000 Enterprise with
SP4 which can failover to any other of the 2 nodes.
My question is I'm looking to see what the process would be to temprorarily
evict one physical node and its server instance for a period of time and
later have that node rejoin the cluster.
Is their any re-installation thats required of either the cluster or the SQL
instance?
Would it make any diference if after evicting the physical node if I created
another temporary named instance on that node and then later removed prior
to re-joining it to the cluster?
thksYou must run the SQL installation and remove the node from each instance's
allowed nodes. Look up "Maintaining a Failover Cluster" in BOL.
You will need to re-add the node to the cluster and then re-run the SQL
installer (and any hotfixes) to re-enable each instance for SQL Server.
Again, BOL is your friend here.
You can install a non-clustered instance on a system and then bring it into
a cluster. The clustered instance and the named instance will not confluct,
as long as disk usages and names do not collide. You will still need to
re-run the SQL installer fore each clustered instance whether or not there
are any SQL instances already on the node.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Tom Frost" <frosty@.nospam.nospam> wrote in message
news:99F01D04-63BE-4569-B483-48284C2CD525@.microsoft.com...
>I have a 3 node cluster based on a Win2003 Server which is running SP1.
>Each
> physical node also has one named instance of SQL Server 2000 Enterprise
> with
> SP4 which can failover to any other of the 2 nodes.
> My question is I'm looking to see what the process would be to
> temprorarily
> evict one physical node and its server instance for a period of time and
> later have that node rejoin the cluster.
> Is their any re-installation thats required of either the cluster or the
> SQL
> instance?
> Would it make any diference if after evicting the physical node if I
> created
> another temporary named instance on that node and then later removed
> prior
> to re-joining it to the cluster?
> thks
>

Evicting a Cluster Node

I have a 3 node cluster based on a Win2003 Server which is running SP1. Each
physical node also has one named instance of SQL Server 2000 Enterprise with
SP4 which can failover to any other of the 2 nodes.
My question is I'm looking to see what the process would be to temprorarily
evict one physical node and its server instance for a period of time and
later have that node rejoin the cluster.
Is their any re-installation thats required of either the cluster or the SQL
instance?
Would it make any diference if after evicting the physical node if I created
another temporary named instance on that node and then later removed prior
to re-joining it to the cluster?
thks
You must run the SQL installation and remove the node from each instance's
allowed nodes. Look up "Maintaining a Failover Cluster" in BOL.
You will need to re-add the node to the cluster and then re-run the SQL
installer (and any hotfixes) to re-enable each instance for SQL Server.
Again, BOL is your friend here.
You can install a non-clustered instance on a system and then bring it into
a cluster. The clustered instance and the named instance will not confluct,
as long as disk usages and names do not collide. You will still need to
re-run the SQL installer fore each clustered instance whether or not there
are any SQL instances already on the node.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Tom Frost" <frosty@.nospam.nospam> wrote in message
news:99F01D04-63BE-4569-B483-48284C2CD525@.microsoft.com...
>I have a 3 node cluster based on a Win2003 Server which is running SP1.
>Each
> physical node also has one named instance of SQL Server 2000 Enterprise
> with
> SP4 which can failover to any other of the 2 nodes.
> My question is I'm looking to see what the process would be to
> temprorarily
> evict one physical node and its server instance for a period of time and
> later have that node rejoin the cluster.
> Is their any re-installation thats required of either the cluster or the
> SQL
> instance?
> Would it make any diference if after evicting the physical node if I
> created
> another temporary named instance on that node and then later removed
> prior
> to re-joining it to the cluster?
> thks
>

EventData.xml : React differently based on file names & additional data in xslt

Hi,

I am new bee to NS.

Question 1:

I am able to run sample aplications. Now I have question. I would like NS to react differently based on file name in event folder. For example

If file name dropped is EventData.xml then EventRule should be used as follows:

<EventRule>
<RuleName>PMEventRule</RuleName>
<EventClassName>DData</EventClassName>
<Action>
INSERT INTO SA(SubscriberId,
DeviceName, SubscriberLocale, RsId, artist, title )
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
e.RsId, e.a1, e.t1
FROM DData e, PMS s
WHERE e.RsId = 14

</Action>
</EventRule>

if dropped file name is eventdata1.xml then event rule to eb used is

<EventRule>
<RuleName>PMEventRule</RuleName>
<EventClassName>DData</EventClassName>
<Action>
INSERT INTO SA(SubscriberId,
DeviceName, SubscriberLocale, RsId, artist, title )
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
e.RsId, e.a1, e.t1
FROM DData e, PMS s
WHERE e.RsId = 15

</Action>
</EventRule>

How do i achive this? One way is getting RSId in my feed but my feed can not contain that info and only way we know is by file name. Any help is appreciated.

Question 2:

Additional data in xslt : based on subscriber demographic info we would like to send addition informative message through xslt which is kept in another Database. How do we insert that in Application.xslt?

Thanks for your help.

1. I believe you need 2 separate FileSystemWatcherProvider 's, and configure them to read data from 2 different directories. Anyway, why don't use use a built-in SQLProvider?.. It's much more "powerful" than the filesystemwatcherprovider.

2. If you have no experience with XSLT, then instead of learning it, I would recommend you should write your own custom formatter in C#. It's not nearly as difficult as it sounds. ...and it gives you enormous opportunities to do whatever you want with your notifications data. There are samples both in Pather's book and in NS BOL.

|||

Hi fafnir

Thanks for the reply. Can you please provide some examples on built-in SQLProvider. I do really appreciate it.

Thanks

|||<HostedProvider>

<ProviderName>RawDataAddedSQLProvider110606</ProviderName>

<ClassName>SQLProvider</ClassName> <!-- Do NOT change this name unless you have written and are using your CUSTOM sql event provider!!! -->

<SystemName>%_NSServer_%</SystemName>

- <Schedule>

<Interval>P0DT00H00M15S</Interval>

</Schedule>

- <Arguments>

- <Argument>

<Name>EventsQuery</Name>

<Value>-- Declare variables used to store the last and current -- run times. DECLARE @.lastRunTime DATETIME DECLARE @.currentRunTime DATETIME -- Select the last run time from the chronicle table. SELECT @.lastRunTime = LastRunTime FROM RawDataSQLProviderRunTime110606 -- Obtain the current run time. SELECT @.currentRunTime = GETDATE() -- Update the chronicle, setting the current run time -- as the last run time. UPDATE RawDataSQLProviderRunTime110606 SET LastRunTime = @.currentRunTime -- Select all the RawData events added between the last run time -- and the current run time. SELECT sd.f_id FROM BLNSDB..view_AlertsRawEvent110606 sd WHERE sd.f_date_ins > @.lastRunTime AND sd.f_date_ins <= @.currentRunTime</Value>

</Argument>

- <Argument>

<Name>EventClassName</Name>

<Value>RawDataAdded110606</Value>

</Argument>

</Arguments>

</HostedProvider>

-

This is a part of my own file for SQL NS 2.0.

Also, BOL have samples that you can modify using your own eventclassname, etc.

|||Hi -

1) If the only difference in the two xml files is RsId, can you include that in the data within the xml document?

2) I've got a simple-talk.com article coming out soon that demonstrates how you can create a custom content formatter that a) retrieves additional information from another database and b) formats that information using an XSLTransform. I'll post here when it's out.

HTH...

Joe|||

Hi Joe,

Thanks for the reply.

1. No RSID can not be part of xml document, that would have been easy. We need to some processing logic based on the file names or folder it contains.

2. I will look forward to your article on XSLT Transformation with additional information.

Thanks

Basu

|||Here's the article I mentioned earlier.

http://www.simple-talk.com/sql/sql-server-2005/creating-a-custom-content-formatter-for-ssns-2005/

HTH...

Joe

EventData.xml : React differently based on file names & additional data in xslt

Hi,

I am new bee to NS.

Question 1:

I am able to run sample aplications. Now I have question. I would like NS to react differently based on file name in event folder. For example

If file name dropped is EventData.xml then EventRule should be used as follows:

<EventRule>
<RuleName>PMEventRule</RuleName>
<EventClassName>DData</EventClassName>
<Action>
INSERT INTO SA(SubscriberId,
DeviceName, SubscriberLocale, RsId, artist, title )
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
e.RsId, e.a1, e.t1
FROM DData e, PMS s
WHERE e.RsId = 14

</Action>
</EventRule>

if dropped file name is eventdata1.xml then event rule to eb used is

<EventRule>
<RuleName>PMEventRule</RuleName>
<EventClassName>DData</EventClassName>
<Action>
INSERT INTO SA(SubscriberId,
DeviceName, SubscriberLocale, RsId, artist, title )
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
e.RsId, e.a1, e.t1
FROM DData e, PMS s
WHERE e.RsId = 15

</Action>
</EventRule>

How do i achive this? One way is getting RSId in my feed but my feed can not contain that info and only way we know is by file name. Any help is appreciated.

Question 2:

Additional data in xslt : based on subscriber demographic info we would like to send addition informative message through xslt which is kept in another Database. How do we insert that in Application.xslt?

Thanks for your help.

1. I believe you need 2 separate FileSystemWatcherProvider 's, and configure them to read data from 2 different directories. Anyway, why don't use use a built-in SQLProvider?.. It's much more "powerful" than the filesystemwatcherprovider.

2. If you have no experience with XSLT, then instead of learning it, I would recommend you should write your own custom formatter in C#. It's not nearly as difficult as it sounds. ...and it gives you enormous opportunities to do whatever you want with your notifications data. There are samples both in Pather's book and in NS BOL.

|||

Hi fafnir

Thanks for the reply. Can you please provide some examples on built-in SQLProvider. I do really appreciate it.

Thanks

|||<HostedProvider>

<ProviderName>RawDataAddedSQLProvider110606</ProviderName>

<ClassName>SQLProvider</ClassName> <!-- Do NOT change this name unless you have written and are using your CUSTOM sql event provider!!! -->

<SystemName>%_NSServer_%</SystemName>

- <Schedule>

<Interval>P0DT00H00M15S</Interval>

</Schedule>

- <Arguments>

- <Argument>

<Name>EventsQuery</Name>

<Value>-- Declare variables used to store the last and current -- run times. DECLARE @.lastRunTime DATETIME DECLARE @.currentRunTime DATETIME -- Select the last run time from the chronicle table. SELECT @.lastRunTime = LastRunTime FROM RawDataSQLProviderRunTime110606 -- Obtain the current run time. SELECT @.currentRunTime = GETDATE() -- Update the chronicle, setting the current run time -- as the last run time. UPDATE RawDataSQLProviderRunTime110606 SET LastRunTime = @.currentRunTime -- Select all the RawData events added between the last run time -- and the current run time. SELECT sd.f_id FROM BLNSDB..view_AlertsRawEvent110606 sd WHERE sd.f_date_ins > @.lastRunTime AND sd.f_date_ins <= @.currentRunTime</Value>

</Argument>

- <Argument>

<Name>EventClassName</Name>

<Value>RawDataAdded110606</Value>

</Argument>

</Arguments>

</HostedProvider>

-

This is a part of my own file for SQL NS 2.0.

Also, BOL have samples that you can modify using your own eventclassname, etc.

|||Hi -

1) If the only difference in the two xml files is RsId, can you include that in the data within the xml document?

2) I've got a simple-talk.com article coming out soon that demonstrates how you can create a custom content formatter that a) retrieves additional information from another database and b) formats that information using an XSLTransform. I'll post here when it's out.

HTH...

Joe|||

Hi Joe,

Thanks for the reply.

1. No RSID can not be part of xml document, that would have been easy. We need to some processing logic based on the file names or folder it contains.

2. I will look forward to your article on XSLT Transformation with additional information.

Thanks

Basu

|||Here's the article I mentioned earlier.

http://www.simple-talk.com/sql/sql-server-2005/creating-a-custom-content-formatter-for-ssns-2005/

HTH...

Joe

Event ID 17052 - c0000005 EXCEPTION_ACCESS_VIOLATION

I have a windows 2000 server that hosts sql 2000 (sp3)
and IIS for a web based payroll application that runs on
our LAN. There are 25 users that use the application.
In the last two weeks I have experienced the below error
in the application log on the server.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 58 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
This error only happens when a certain area of the
payroll application is used, however not all of the time;
when the error occurs it also prevents the payroll
application from working.
The really strange part is that one day we will not
receive the error and then the next day the error might
occur all day. There is nothing that has changed in the
last few weeks in either SQL, IIS or the payroll
application.
Any ideas why I am receiving this error?Have you don consistency check on the DB? DBCC CHECKDB and DBCC
CHECKCATALOG.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> I have a windows 2000 server that hosts sql 2000 (sp3)
> and IIS for a web based payroll application that runs on
> our LAN. There are 25 users that use the application.
> In the last two weeks I have experienced the below error
> in the application log on the server.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> This error only happens when a certain area of the
> payroll application is used, however not all of the time;
> when the error occurs it also prevents the payroll
> application from working.
> The really strange part is that one day we will not
> receive the error and then the next day the error might
> occur all day. There is nothing that has changed in the
> last few weeks in either SQL, IIS or the payroll
> application.
> Any ideas why I am receiving this error?|||Yes I have, no problems there.
>--Original Message--
>Have you don consistency check on the DB? DBCC CHECKDB
and DBCC
>CHECKCATALOG.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
>> I have a windows 2000 server that hosts sql 2000 (sp3)
>> and IIS for a web based payroll application that runs
on
>> our LAN. There are 25 users that use the application.
>> In the last two weeks I have experienced the below
error
>> in the application log on the server.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 58 generated fatal
>> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server
>> is terminating this process.
>> This error only happens when a certain area of the
>> payroll application is used, however not all of the
time;
>> when the error occurs it also prevents the payroll
>> application from working.
>> The really strange part is that one day we will not
>> receive the error and then the next day the error might
>> occur all day. There is nothing that has changed in
the
>> last few weeks in either SQL, IIS or the payroll
>> application.
>> Any ideas why I am receiving this error?
>
>.
>|||The I suggest you go with the general recommendations: Search KB if there
are specifics re your problem, make sure you are current on service pack and
open a case with MS if needed.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:d0ea01c3eef7$026a0060$a401280a@.phx.gbl...
> Yes I have, no problems there.
>
> >--Original Message--
> >Have you don consistency check on the DB? DBCC CHECKDB
> and DBCC
> >CHECKCATALOG.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> >> I have a windows 2000 server that hosts sql 2000 (sp3)
> >> and IIS for a web based payroll application that runs
> on
> >> our LAN. There are 25 users that use the application.
> >>
> >> In the last two weeks I have experienced the below
> error
> >> in the application log on the server.
> >>
> >> Error: 0, Severity: 19, State: 0
> >> SqlDumpExceptionHandler: Process 58 generated fatal
> >> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
> Server
> >> is terminating this process.
> >>
> >> This error only happens when a certain area of the
> >> payroll application is used, however not all of the
> time;
> >> when the error occurs it also prevents the payroll
> >> application from working.
> >>
> >> The really strange part is that one day we will not
> >> receive the error and then the next day the error might
> >> occur all day. There is nothing that has changed in
> the
> >> last few weeks in either SQL, IIS or the payroll
> >> application.
> >>
> >> Any ideas why I am receiving this error?
> >
> >
> >.
> >|||What type of memory configuration do U have for SQL server (fixed / dynamic) sounds like a memory contention issue btw iis and sql server, tried fixed memory?
Also possible to monitor memory object (pages/sec) counter to see if there is excessive paging when this problem occurs?|||Do you have the stack trace from the SQL Server event log?
This will provide more information for us to go on and help us debug.
Also, make sure you are on the latest version of SQL Server, as this SQL
Server bug might have already been fixed.
--
Paul Wehland
pwehland at msn dot com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:d42301c3eef2$dace10e0$a501280a@.phx.gbl...
> I have a windows 2000 server that hosts sql 2000 (sp3)
> and IIS for a web based payroll application that runs on
> our LAN. There are 25 users that use the application.
> In the last two weeks I have experienced the below error
> in the application log on the server.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> This error only happens when a certain area of the
> payroll application is used, however not all of the time;
> when the error occurs it also prevents the payroll
> application from working.
> The really strange part is that one day we will not
> receive the error and then the next day the error might
> occur all day. There is nothing that has changed in the
> last few weeks in either SQL, IIS or the payroll
> application.
> Any ideas why I am receiving this error?

Event driven log backup?

Has anyone explored a process whereby the Transaction Log would be
backup up based upon a defined threshold, ie. 75% full? All the
research against news group posts and SQL2000 literature seems to
point to scheduling a log backup job on a periodic basis. My workflow
isn't 24 hour consistant and even adjusting the interval during the
day isn't a good answer when the multiple databases on a server fill
their logs at different rates.fprose@.hotmail.com (Fred Prose) wrote in message news:<195a4770.0403010624.194d7840@.posting.google.com>...
> Has anyone explored a process whereby the Transaction Log would be
> backup up based upon a defined threshold, ie. 75% full? All the
> research against news group posts and SQL2000 literature seems to
> point to scheduling a log backup job on a periodic basis. My workflow
> isn't 24 hour consistant and even adjusting the interval during the
> day isn't a good answer when the multiple databases on a server fill
> their logs at different rates.

You could create an alert using Perfmon on the SQLServer:Databases
(Percent Log Used) counter, which fires at 75% and calls a batch file
or script to backup the log. Personally, I wouldn't do this because a)
it introduces unnecessary complexity, and b) it makes your backup
schedule completely unpredictable. If you don't know the backup
schedule, then you don't know what your recovery options are, which
makes it difficult to tell users and management anything about
possible data loss in the event of an outage.

Simon

Event driven log backup

Has anyone explored a process whereby the Transaction Log would be backup up based upon a defined threshold, ie. 75% full? All the research against news group posts and SQL2000 literature seems to point to scheduling a log backup job on a periodic basis. My workflow isn't 24 hour consistant and even adjusting the interval during the day isn't a good answer when the multiple databases on a server fill their logs at different rates.WHat is the issue of scheduling transaction log backup job at regular intervals?

To create a set of backups, you typically make a database backup at periodic intervals, such as daily, and transaction log backups at shorter intervals, such as every 10 minutes.

You can schedule the Transaction log backup job at different times for each database without any issue.

Log shipping is another good tool to takeup the challenge.|||Well, the issue is that in our environment, periodic scheduling is not working well. We run 50 different databases (same structure) on a server. Some hit heavy, some very infrequently and in both cases the time of day influences the load. We tend to end up with (too many) VERY small logs most of the day and DB's locking up with logs full on occassion. Letting logs grow and then shrinking them has proven too resource intensive.

The concept of backing logs based upon either a threshold or "log full" situation is not new, and other DBMS support this, and and as standard feature.|||Satya, and how would Log Shipping answer the poster's question?

fprose,

There are several ways how you can accomplish this. I've done it by creating a custom alert that invokes a TrxDump job. Another scheduled task was polling the databases for predefined threshold (different for each database) and raising the error that triggered the alert if the condition was met. I did discover issues with this approach and had to abandon the Alert-based solution because jobs were colliding at times. At that point I had to store the fact that the condition was met with the database name into a table. Yet another job was firing periodically (every minute) and checking for previous instance to complete (exited if not completed) and going through the queue of databases to do the log dump.

You may discover yet another way of doing it.|||Why not log shipping, which can take care of transaction log backup without any hitch if involved servers/network is maintained properly. I feel LS is identical solution to take care of backups.

Firstly you need to make sure to maintain correct sizes for all databases Tlog and then schedule the backup job to truncate the log which will avoid filling up of log.

You need to workout the exact size to be set for Tlog by the way of monitoring during dbcc checks, db maintenance plan jobs etc.

In general you can set alerts for 9002 but the nature of DB seems to be volatile and by the time you take action will not suffice the condition and DB will be out of state with errors.|||oh yeah, that's an interesting solution, to set up log shipping for threshold-triggered log dump strategy? how does that relate?|||You can use SQL Alerts to take specific actions (such as executing your log backup job) based on performance threshholds.

However, I have tried this once and I was not thrilled with the results. The alert is constantly monitoring performance values. The TLOG was run repeatedly for several seconds (minutes?) until SQL cleared the space from the log file. It can take some time for a log backup to execute and then truncate and free up space in the log file. There is a feature in the performance alerts setup page to disable repeated running of the alert for a period of time (to allow for the condition to clear).

Still, I'd be careful on this one. I think I might take a more hands-on approach and try to carve out the DBs into two or more groups based on underlying activity. Then set different job schedules for the log backups.

If the fifty databases are all structurally the same (did I read that correctly?), couldn't you also unify them into a single db partioned by schema?

Just a thought...

Regards,

hmscott|||Thanks, I'll look for the option to disable the repeated running.

As to the identical copies on a server: When I first arrived here, I asked the same question. Well, there's all sorts of political, legal, and procedural reasons why that can't happen - or at least it can't happen until the application group invests some major time to rewite the application - which isn't going to happen.

We're developing new applications that will be MSSQL, but our existing databases are Informix and run on two, 4-way AIX servers - with 150 db's spread across the two servers based upon phone company lattas.

When we identified the potential workload that we envision for the MSSQL environment Microsoft got a glassy look and the HP salesman started shopping for his new Hummer. Based upon benchmarks we calculated a minimum of 4, 16-way HP/Compaq servers - and that's if we didn't do hot backup replication.

Back to demand driven log backup. Informix shares a log pool for all db's in a instance. That said, we peal off to Tivoli a 12 mb log file every 5 minutes (or less) during peak production. I've noticed the amount of (wall clock) time and resources expended when SQL2000 backups up a log and it has me a little concerned.|||you need to create a performance condition alert

use the database object and the percent log used counter
set it to equal to 75%
or
set it to greater to 75% but you will need to set the alert delay to 15 minutes or longer because the log will be 75% full or greater at all times once the alert goes off..

Event driven cache refreshing script

Hi,

Can someone share how to setup an event-driven cache refreshing option? The options provided from SSRS interface are based on a fixed schedule. If I would like to setup a dependency in determining when to refresh reports caching, based on the successful refreshing of the underlyinn SSAS cube, how can I achieve it?

Is there an utility/API provided by SSRS? Any samples scripts are very much appreciated.

Thanks,
Jet
Also please let me know how to schedule SSAS Cube and SSRS Report.
Thanks Gurus.
KLS

Event driven cache refreshing script

Hi,

Can someone share how to setup an event-driven cache refreshing option? The options provided from SSRS interface are based on a fixed schedule. If I would like to setup a dependency in determining when to refresh reports caching, based on the successful refreshing of the underlyinn SSAS cube, how can I achieve it?

Is there an utility/API provided by SSRS? Any samples scripts are very much appreciated.

Thanks,
Jet
Also please let me know how to schedule SSAS Cube and SSRS Report.
Thanks Gurus.
KLS

Event based scheduling of SSIS packages

Hi,

Is it possible to do an event based scheduling of SSIS packages from SQL Server Agent? Like schedule a package to run when a file is available in a particular folder.

Does anyone have any similar experiences to share?

Regards,

Emil

Please have a look at the links provided by Daniel Crowell

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1844915&SiteID=1

This should help.

Thanks

Subhash Subramanyam.

Even driven Scheduling of report

I would like to run a data driven scheduled report based on an event that
happened outside reporting services. How to achieve this?
I would like to use some kind of batch program or exe that i can run in my
DTS package that could trigger the Schedule.
Any suggessions welcome.
Thanks
RamdasI'd love to know the answer to this as well. We have a whole lot of jobs
scheduled in a third-party batch scheduler tool and I hate having another set
of schedules in reporting services; it would be nice to have an easy way to
trigger a subscription. I looked through the SOAP API but didn't see
anything that would quite work even if I was to write some code.
"Ramdas" wrote:
> I would like to run a data driven scheduled report based on an event that
> happened outside reporting services. How to achieve this?
> I would like to use some kind of batch program or exe that i can run in my
> DTS package that could trigger the Schedule.
> Any suggessions welcome.
> Thanks
> Ramdas
>