How to Synchroize XML file with a tabe in SQL Server 2005?

Is there any inbuilt feature of SQL Server 2005 that will allow automatic synchronization of data in a XML file and its corresponding table in database, i.e. the data in XML file is pushed to table?

Have you considered using Scheduled Job? I mean you can import data from XML to database using Import/Export Wizard, with saving the process as a SSIS package. Then go to SQL Agent and create a job which executes the SSIS package, and you can schedule the job

How to switch off automatic blanking of table views in enterprise manager ?

Hi Sqlserver experts,

I use the SQL Server enterprise manager of MSSQL 2000 regularly.
I'm often annoyed by the automatic blanking of the table views.

If this happens then mostly with the hint :
"The Results pane have been cleared to conserve server resources.
To re-establish the result set, run query again."

Is there any possibility to switch that blanking off ?

Best regards,

Daniel WetzlerCan't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.

They've fixed the problem in the new version, so it looks like you'll
remain out of luck.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||Thank you very much although its really bad news :-(((

Jason Kester schrieb:

Quote:

Originally Posted by

Can't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.
>
They've fixed the problem in the new version, so it looks like you'll
remain out of luck.
>
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
>
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

|||Daniel Wetzler wrote:

Quote:

Originally Posted by

Thank you very much although its really bad news :-(((
>
>
Jason Kester schrieb:
>

Quote:

Originally Posted by

Can't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.

They've fixed the problem in the new version, so it looks like you'll
remain out of luck.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

EM locks data when it reads it. That is why MS releases the result set
after X amount of time. You should really use Query Analyzer to look at
data that you do not intend to change.

IMHO -- Mark D Powell --|||Mark D Powell wrote:

Quote:

Originally Posted by

EM locks data when it reads it.

I don't think that's the case. I've just created a test table, filled
it with some data and viewed it in EM. I could still update one of the
viewed rows using QA. Once I re-ran the EM query using the exclamation
icon again, the changed row reflected its new state.

Surely the result set clearing is because of the resources consumed by
the open connection, rather than any locking? If I'm wrong, somebody
stop me - to quote Jim Carey.

Quote:

Originally Posted by

You should really use Query Analyzer

I agree! It's a much better tool for actually executing queries.|||Bobbo wrote:

Quote:

Originally Posted by

Mark D Powell wrote:
>

Quote:

Originally Posted by

EM locks data when it reads it.

>
I don't think that's the case. I've just created a test table, filled
it with some data and viewed it in EM. I could still update one of the
viewed rows using QA. Once I re-ran the EM query using the exclamation
icon again, the changed row reflected its new state.
>
Surely the result set clearing is because of the resources consumed by
the open connection, rather than any locking? If I'm wrong, somebody
stop me - to quote Jim Carey.
>

Quote:

Originally Posted by

You should really use Query Analyzer

>
I agree! It's a much better tool for actually executing queries.

We have traced data access problems to users accessing data via EM. MS
may have modified some of the logic with SP1, SP2, SP3, and/or SP4 so
that EM works more effectively.

Quote:

Originally Posted by

Quote:

Originally Posted by

>>

>From the SQL Server Documentation: Optimizing Query Performance -

SQL Server is essentially a puppet of the client application. The
client application has almost total control over (and responsibility
for) the locks acquired on the server. Although the SQL Server lock
manager automatically uses locks to protect transactions, this is
directly instigated by the query type sent from the client application
and the way the results are processed. Therefore, resolution of most
blocking problems involves inspecting the client application.

A blocking problem frequently requires both the inspection of the exact
SQL statements submitted by the application and the exact behavior of
the application regarding connection management, processing of all
result rows, and so on. If the development tool does not allow explicit
control over connection management, query time-out, processing of
results, and so on, blocking problems may not be resolvable.

Possible cause of problems listed in the above article (these are
things to avoid)

Submitting queries with long execution times

Canceling queries that were not committed or rolled back. [The
application needs to issue a rollback or commit after a query is
cancelled in order to release held locks!]

Applications that are not processing all results to completion

Distributed client/server deadlocks

Locks are held for the length of time needed to protect the resource at
the level requested:
<<

So it depends on how the clients works if one user can cause more
contention than is reasonable. Cursor and especially select for update
cursors can result in unexpected locks waiting. Like I said we tested
and identified a problem, but it was a couple of SP ago.

-- Mark D Powell --

How to stop automatic Replication Validation?

Under EM's Replication Monitor/Publishers/pub1/publisher1 there is a "Validate Subscriptions" option on the right-click menu. Since I see unexplained Replication Validation activity I think that the "Validate Subscriptions" option is turned on. But there is no "turn-off" button or way to say "stop." Does anyone know how this "feature" works?

Thanks

I would suggest that you script the publication out and look at it in Query Analyser.

It may not be obvious what the parameter is as sometimes the description we see in the GUI is far different from what the parameter might mean. You can always look up the parameters in BOL to make sure you understand them.

We have previously used this technique to set some of the less obvious options.

|||

Thanks for your response. I did what you suggested and I see nothing suggestive of validation. It must be hidden elsewhere!

|||

Does your agent job have a parameter -Validate 1 or -Validate 3?

Also check if any of your agent profiles have this parameter set.

How to stop automatic recovery ?

I have a database that is doing a automatic recovery. The
event log indicates this will take about 15 hours. I would
like to stop the recovery and reload from backup. I cannot
connect with Enterprise Manager because it time's out
connecting to the server. Anybody have an idea what I can
do?OK, this is a drastic action.
Stop the SQL Server. Rename the underlying data files (.MDF, .NDF, and .LDF
are the normal file extensions). If you don't have the space to hold the
replacement files, delete them. Restart SQL Server. You can then restore
from backup.
Make sure you want to do this and that your backup is good. This is NOT an
exercise you can undo. If you have the space, I would do a test restore
using a different DB name and file names. Look at the 'WITH MOVE' option
in BOL for RESTORE DATABASE.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Pat" <mcilweep@.ctbsonline.com> wrote in message
news:013a01c35d01$3c70ed10$a301280a@.phx.gbl...
> I have a database that is doing a automatic recovery. The
> event log indicates this will take about 15 hours. I would
> like to stop the recovery and reload from backup. I cannot
> connect with Enterprise Manager because it time's out
> connecting to the server. Anybody have an idea what I can
> do?

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.

How to stop automatic excution of a report?

Hi,

I have a report containing one multi-value parameter with default values given. When open I report in Report Manager, report executes automatically. I need to stop this automatic execution of report. I need to give user an option to check if default values are ok for him or not. Is there any way other than removing default value from parameter to do this?

Thanks in anticipation.

Saeed

Sorry, there is no way to achieve this behavior in Report Manager. You need to remove the default value, otherwise the report will render.|||

It is unfortunate that this was not considered as a requirement during development. However, you can work around this by adding an additional report parameter. Add a Start Report parameter as a string with 1 non-queried availabIe value like 'Yes'. The report will not render until the user selects the only available option for that parameter. Its not the best solution but is a viable workaround to the default rendering problem.

|||Hi
Rather than use default parameters in a report why dont you make the report show a list of valid values e.g. from a query and the user will sellect appropriate value.
e.g.

A report uses a paramater param1; create a dataset with a sql query that generates valid values and make your report parameter populate from this dataset. You can set the default value to null so that the report prompts for the parameters from the valid list.