Hi all,
I've a xml file like this:
<ROOT>
<customerlist>
<Customers sequence="1">
<details>
<PersonID>1</PersonID>
</details>
<name>
<LastName>Vinod</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
<Customers sequence="2">
<details>
<PersonID>2</PersonID>
</details>
<name>
<LastName>Saravana</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
</customerlist>
</ROOT>
how should I write an xsd file to store the data in the following sql server table (Person) with columns:
personId - primary key, LastName and FirstName.
Thanks.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:exDw0UcdEHA.320@.TK2MSFTNGP10.phx.gbl...
[snip]
> how should I write an xsd file to store the data in the following sql
> server table (Person) with columns:
I would start with the examples given in the SQLXML documentation. Look at
how the XSD is mapped to the XML. That is the best way to learn.
Bryant
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Help Finding records with matching columns
I'm running MS SQL 7.0.
I've been trying to figure this out for a couple of days without success.
I need to change the values in column6 of a table to NULL where the value of
column6 is not NULL, and the value of column5 is 97, and the values of
column1, column2, column3, and column4 each match the corresponding columns
in the other row.
In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
match the same columns in row 2, and the value of column6 in row 1 is 97 and
in row2 is something else (it doesn't matter what the value is), and the
value of column 6 is not NULL.
Example:
row1 col1 col2 col3 col4 col5 col6
A B C D 97 2
row2 col1 col2 col3 col4 col5 col6
A B C D 1 3
In this example, I need to change the value in row1, column6 from 2 to NULL.
I need to do that to all rows with similar matching qualities, which I
figure to be around 1000 rows. But I DO NOT want rows returned if the value
of col6 in one of the rows is not 97.
Can anyone help?
Thanks,
John Steen
On Fri, 20 Aug 2004 08:43:01 -0700, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>I'm running MS SQL 7.0.
>I've been trying to figure this out for a couple of days without success.
>I need to change the values in column6 of a table to NULL where the value of
>column6 is not NULL, and the value of column5 is 97, and the values of
>column1, column2, column3, and column4 each match the corresponding columns
>in the other row.
>In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
>match the same columns in row 2, and the value of column6 in row 1 is 97 and
>in row2 is something else (it doesn't matter what the value is), and the
>value of column 6 is not NULL.
>Example:
>row1 col1 col2 col3 col4 col5 col6
> A B C D 97 2
>row2 col1 col2 col3 col4 col5 col6
> A B C D 1 3
>In this example, I need to change the value in row1, column6 from 2 to NULL.
> I need to do that to all rows with similar matching qualities, which I
>figure to be around 1000 rows. But I DO NOT want rows returned if the value
>of col6 in one of the rows is not 97.
>Can anyone help?
>Thanks,
>John Steen
Hi John,
Pity you didn't post the DDL (CREATE TABLE statements) and INSERTS for the
sample data that would have allowed me to test. Anyway, here's an untested
suggestion:
UPDATE MyTable
SET col6 = NULL
WHERE col6 IS NOT NULL
AND col5 = 97
AND EXISTS
(SELECT *
FROM MyTable AS x
WHERE x.col1 = MyTable.col1
AND x.col2 = MyTable.col2
AND x.col3 = MyTable.col3
AND x.col4 = MyTable.col4
AND ( x.col5 <> 97
OR x.col5 IS NULL))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||if i understand your requirement correctly you can try the query given in
following example.
--sample data
create table t(col1 char(1),col2 char(1),col3 char(1),col4 char(1),col5
int,col6 int)
go
insert into t
select 'A','B','C','e',97, 2 union all --row will be updated
select 'A','B','C','e',9, 3 union all
select 'A','B','C','e',95, 2 union all
select 'A','B','C','e',97, null union all
select 'A','B','C','e',97, 5 union all --row will be updated
select 'A','B','C','x',9, 3 union all
select 'A','B','C','x',95, 2
go
--query
update t set col6 = null
where exists
(select col1,col2,col3,col4
from t x
where t.col1 = x.col1 and t.col2 = x.col2 and t.col3=x.col3 and t.col4 =
x.col4
group by col1,col2,col3,col4
having count(*) > 1)
and t.col5 = 97 and t.col6 is not null
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks, Hugo and Vishal! Both solutions worked.
I've been trying to figure this out for a couple of days without success.
I need to change the values in column6 of a table to NULL where the value of
column6 is not NULL, and the value of column5 is 97, and the values of
column1, column2, column3, and column4 each match the corresponding columns
in the other row.
In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
match the same columns in row 2, and the value of column6 in row 1 is 97 and
in row2 is something else (it doesn't matter what the value is), and the
value of column 6 is not NULL.
Example:
row1 col1 col2 col3 col4 col5 col6
A B C D 97 2
row2 col1 col2 col3 col4 col5 col6
A B C D 1 3
In this example, I need to change the value in row1, column6 from 2 to NULL.
I need to do that to all rows with similar matching qualities, which I
figure to be around 1000 rows. But I DO NOT want rows returned if the value
of col6 in one of the rows is not 97.
Can anyone help?
Thanks,
John Steen
On Fri, 20 Aug 2004 08:43:01 -0700, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>I'm running MS SQL 7.0.
>I've been trying to figure this out for a couple of days without success.
>I need to change the values in column6 of a table to NULL where the value of
>column6 is not NULL, and the value of column5 is 97, and the values of
>column1, column2, column3, and column4 each match the corresponding columns
>in the other row.
>In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
>match the same columns in row 2, and the value of column6 in row 1 is 97 and
>in row2 is something else (it doesn't matter what the value is), and the
>value of column 6 is not NULL.
>Example:
>row1 col1 col2 col3 col4 col5 col6
> A B C D 97 2
>row2 col1 col2 col3 col4 col5 col6
> A B C D 1 3
>In this example, I need to change the value in row1, column6 from 2 to NULL.
> I need to do that to all rows with similar matching qualities, which I
>figure to be around 1000 rows. But I DO NOT want rows returned if the value
>of col6 in one of the rows is not 97.
>Can anyone help?
>Thanks,
>John Steen
Hi John,
Pity you didn't post the DDL (CREATE TABLE statements) and INSERTS for the
sample data that would have allowed me to test. Anyway, here's an untested
suggestion:
UPDATE MyTable
SET col6 = NULL
WHERE col6 IS NOT NULL
AND col5 = 97
AND EXISTS
(SELECT *
FROM MyTable AS x
WHERE x.col1 = MyTable.col1
AND x.col2 = MyTable.col2
AND x.col3 = MyTable.col3
AND x.col4 = MyTable.col4
AND ( x.col5 <> 97
OR x.col5 IS NULL))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||if i understand your requirement correctly you can try the query given in
following example.
--sample data
create table t(col1 char(1),col2 char(1),col3 char(1),col4 char(1),col5
int,col6 int)
go
insert into t
select 'A','B','C','e',97, 2 union all --row will be updated
select 'A','B','C','e',9, 3 union all
select 'A','B','C','e',95, 2 union all
select 'A','B','C','e',97, null union all
select 'A','B','C','e',97, 5 union all --row will be updated
select 'A','B','C','x',9, 3 union all
select 'A','B','C','x',95, 2
go
--query
update t set col6 = null
where exists
(select col1,col2,col3,col4
from t x
where t.col1 = x.col1 and t.col2 = x.col2 and t.col3=x.col3 and t.col4 =
x.col4
group by col1,col2,col3,col4
having count(*) > 1)
and t.col5 = 97 and t.col6 is not null
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks, Hugo and Vishal! Both solutions worked.
Monday, March 19, 2012
help a newbie
i've been a sql server dba for the last six years, but we're just now
going to implement full text searches so i've got some dumb questions.
1) is the only way to make the fulltext searches realtime, ie document
is indexed immediately after being added to the db, is to enable change
tracking and update index in the background and add a timestamp column
to each table to be indexed?
2) once the change tracking is working, is there any reason to schedule
full or incremental population?
3) my catalog has 4 tables in it. is there any way to enable change
tracking and update index in background for the entire catalog or do i
have to go to each table and enable it?
4) is it better to have one catalog per db or one catalog per table?
5) the population really kills my cpu. my development server has dual
p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
text/image data (pdf, doc, ppt, etc) in it. the population takes about
10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
is this normal? this goes along with #3 because everytime i go enable
change tracking on a table, it starts a repopulation.
6) what's more important for populating fulltext indexes, cpu speed or
disk speed?
7) people can upload any kind of document into these 4 tables including
jpg, gif, bmp. fulltext tries to index these and generates an error in
eventlog. any way to tell it to not index files of those types?
8) backup and recovery is very important here, as it should be
everywhere. what's the best way to restore a db that has fulltext
indexes? restore from backup and rebuild catalogs or try to restore the
catalog files along with the db?
CH,
See answers to your questions inline below starting with [jtkane].
Regards,
John
"ch" <ch@.dontemailme.com> wrote in message
news:408531A7.E2AF907A@.dontemailme.com...
> i've been a sql server dba for the last six years, but we're just now
> going to implement full text searches so i've got some dumb questions.
> 1) is the only way to make the fulltext searches realtime, ie document
> is indexed immediately after being added to the db, is to enable change
> tracking and update index in the background and add a timestamp column
> to each table to be indexed?
[jtkane] - Yes and assuming you're using SQL Server 2000 as CT and UIiB are
new features in SQL Server 2000. Although adding a timestamp column is not
required for CT and UIiB, but recommended as when you enable a populated FT
Catalog, CT will automaticlly run an Incremental Population to re-sync the
table with the FT Catalog, otherwise a Full Population will be executed.
> 2) once the change tracking is working, is there any reason to schedule
> full or incremental population?
[jtkane] - A Full Population, no, unless you have a need to change the
related noise word file, i.e., add or remove noise words. As for
Incremental, yes, depending upon the amount of change, i.e,
inserts/updates/deletes that affect over 60% of the rows in the FT enabled
table. See SQL Server BOL title "Maintaining Full-Text Indexes" for more
info on this.
> 3) my catalog has 4 tables in it. is there any way to enable change
> tracking and update index in background for the entire catalog or do i
> have to go to each table and enable it?
[jtkane] - CT and UIiB are set at the table level, so you would need to
enable each table. Note, this can be easly done in the Enterprise Manager.
However, considering the answer to number 2 above, and if these are large
(>1 million rows) tables, you should do enable these tables one at a time,
until the re-sync Incremental Population has completed and then enable the
next table...
> 4) is it better to have one catalog per db or one catalog per table?
[jtkane] - The answer to this question is that it depends. See the last
paragraph in SQL Server BOL title "Full-text Search Recommendation" for more
info on this question. Additionally, having mutiple tables in one FT
Catalogs can affect the value of RANK, if you use this column in your
CONTAINSTABLE or FREETEXTTABLE queries.
> 5) the population really kills my cpu. my development server has dual
> p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
> text/image data (pdf, doc, ppt, etc) in it. the population takes about
> 10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
> is this normal? this goes along with #3 because everytime i go enable
> change tracking on a table, it starts a repopulation.
[jtkane] - Yes. This is is normal and expected during either the "shadow
merge" or "Master Merge" processes that the MSSearch service does to merge
new "word lists" into it's file system and then at the end of this process
or at midnight (controllable via a registry key). This process occurs during
either a Full or Incremental Population and at midnight for CT & UIiB
enabled tables.
For multi-proc servers, you can set the CPU affinity of the MSSearch service
(or any other service) via launching the following at the AT command prompt
as the sysadmin of the machine where SQL Server is installed using the
following syntax:
at <current_time_plus_1_min> /interactive taskmgr.exe
then once the TaskMgr is running, right-click on the MSSearch service and
set the CPU to one CPU and then use sp_configure and set the CPU affinity
for SQL Server to the other CPU for your dual proc server. This will ensure
that the MSSearch CPU usage will not affect your SQL Server processes during
it's high CPU usage periods.
> 6) what's more important for populating fulltext indexes, cpu speed or
> disk speed?
[jtkane] - All are important, depending what you are trying to optmize. See
SQL Server BOL title "Full-text Search Recommendation" for more info on this
issue. Note, the amount of L3 cache on the CPU is also important.
> 7) people can upload any kind of document into these 4 tables including
> jpg, gif, bmp. fulltext tries to index these and generates an error in
> eventlog. any way to tell it to not index files of those types?
[jtkane] - Yes. All errors, warnings and informational events for FTS and
MSSearch are recorded in the server's Appliation event log and not SQL
Server's errorlog files.
> 8) backup and recovery is very important here, as it should be
> everywhere. what's the best way to restore a db that has fulltext
> indexes? restore from backup and rebuild catalogs or try to restore the
> catalog files along with the db?
[jtkane] - Yes, I agree. For more info on backing up and restoring FT
Catalogs, see KB article 240867 (Q240867) "INF: How to Move, Copy, and
Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Note, SQL Server 2005 (codename Yukon) will fully integrate FT Catalogs into
SQL Server's backup and restore commands.
>
>
going to implement full text searches so i've got some dumb questions.
1) is the only way to make the fulltext searches realtime, ie document
is indexed immediately after being added to the db, is to enable change
tracking and update index in the background and add a timestamp column
to each table to be indexed?
2) once the change tracking is working, is there any reason to schedule
full or incremental population?
3) my catalog has 4 tables in it. is there any way to enable change
tracking and update index in background for the entire catalog or do i
have to go to each table and enable it?
4) is it better to have one catalog per db or one catalog per table?
5) the population really kills my cpu. my development server has dual
p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
text/image data (pdf, doc, ppt, etc) in it. the population takes about
10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
is this normal? this goes along with #3 because everytime i go enable
change tracking on a table, it starts a repopulation.
6) what's more important for populating fulltext indexes, cpu speed or
disk speed?
7) people can upload any kind of document into these 4 tables including
jpg, gif, bmp. fulltext tries to index these and generates an error in
eventlog. any way to tell it to not index files of those types?
8) backup and recovery is very important here, as it should be
everywhere. what's the best way to restore a db that has fulltext
indexes? restore from backup and rebuild catalogs or try to restore the
catalog files along with the db?
CH,
See answers to your questions inline below starting with [jtkane].
Regards,
John
"ch" <ch@.dontemailme.com> wrote in message
news:408531A7.E2AF907A@.dontemailme.com...
> i've been a sql server dba for the last six years, but we're just now
> going to implement full text searches so i've got some dumb questions.
> 1) is the only way to make the fulltext searches realtime, ie document
> is indexed immediately after being added to the db, is to enable change
> tracking and update index in the background and add a timestamp column
> to each table to be indexed?
[jtkane] - Yes and assuming you're using SQL Server 2000 as CT and UIiB are
new features in SQL Server 2000. Although adding a timestamp column is not
required for CT and UIiB, but recommended as when you enable a populated FT
Catalog, CT will automaticlly run an Incremental Population to re-sync the
table with the FT Catalog, otherwise a Full Population will be executed.
> 2) once the change tracking is working, is there any reason to schedule
> full or incremental population?
[jtkane] - A Full Population, no, unless you have a need to change the
related noise word file, i.e., add or remove noise words. As for
Incremental, yes, depending upon the amount of change, i.e,
inserts/updates/deletes that affect over 60% of the rows in the FT enabled
table. See SQL Server BOL title "Maintaining Full-Text Indexes" for more
info on this.
> 3) my catalog has 4 tables in it. is there any way to enable change
> tracking and update index in background for the entire catalog or do i
> have to go to each table and enable it?
[jtkane] - CT and UIiB are set at the table level, so you would need to
enable each table. Note, this can be easly done in the Enterprise Manager.
However, considering the answer to number 2 above, and if these are large
(>1 million rows) tables, you should do enable these tables one at a time,
until the re-sync Incremental Population has completed and then enable the
next table...
> 4) is it better to have one catalog per db or one catalog per table?
[jtkane] - The answer to this question is that it depends. See the last
paragraph in SQL Server BOL title "Full-text Search Recommendation" for more
info on this question. Additionally, having mutiple tables in one FT
Catalogs can affect the value of RANK, if you use this column in your
CONTAINSTABLE or FREETEXTTABLE queries.
> 5) the population really kills my cpu. my development server has dual
> p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
> text/image data (pdf, doc, ppt, etc) in it. the population takes about
> 10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
> is this normal? this goes along with #3 because everytime i go enable
> change tracking on a table, it starts a repopulation.
[jtkane] - Yes. This is is normal and expected during either the "shadow
merge" or "Master Merge" processes that the MSSearch service does to merge
new "word lists" into it's file system and then at the end of this process
or at midnight (controllable via a registry key). This process occurs during
either a Full or Incremental Population and at midnight for CT & UIiB
enabled tables.
For multi-proc servers, you can set the CPU affinity of the MSSearch service
(or any other service) via launching the following at the AT command prompt
as the sysadmin of the machine where SQL Server is installed using the
following syntax:
at <current_time_plus_1_min> /interactive taskmgr.exe
then once the TaskMgr is running, right-click on the MSSearch service and
set the CPU to one CPU and then use sp_configure and set the CPU affinity
for SQL Server to the other CPU for your dual proc server. This will ensure
that the MSSearch CPU usage will not affect your SQL Server processes during
it's high CPU usage periods.
> 6) what's more important for populating fulltext indexes, cpu speed or
> disk speed?
[jtkane] - All are important, depending what you are trying to optmize. See
SQL Server BOL title "Full-text Search Recommendation" for more info on this
issue. Note, the amount of L3 cache on the CPU is also important.
> 7) people can upload any kind of document into these 4 tables including
> jpg, gif, bmp. fulltext tries to index these and generates an error in
> eventlog. any way to tell it to not index files of those types?
[jtkane] - Yes. All errors, warnings and informational events for FTS and
MSSearch are recorded in the server's Appliation event log and not SQL
Server's errorlog files.
> 8) backup and recovery is very important here, as it should be
> everywhere. what's the best way to restore a db that has fulltext
> indexes? restore from backup and rebuild catalogs or try to restore the
> catalog files along with the db?
[jtkane] - Yes, I agree. For more info on backing up and restoring FT
Catalogs, see KB article 240867 (Q240867) "INF: How to Move, Copy, and
Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Note, SQL Server 2005 (codename Yukon) will fully integrate FT Catalogs into
SQL Server's backup and restore commands.
>
>
Wednesday, March 7, 2012
Help - Server stops responding to connections, stops accepting connections
I've been having the same problem for 2 weeks now. If anyone has any
ideas, I'd love to hear them. We are using both SQL and Windows
Authentication. I was running a Profiler Trace at the time, and am
going through it now but have not seen anything yet.
Thanks in advance.
About once a week, at no fixed time (but so far, between 8am and
11am), my SQL Server 2000 on Windows 2000 (8.00.679) will stop
responding. New connections will at first take forever to connect,
then an error message about the PreLoginHandshake(). We have to kill
the service (read below) to stop it, but it comes back without
problems.
The very first time this happened, I got buf latch errors in the SQL
Error Log, but not this time. (However, the first time we waited for
about 5 minutes after it stopped responding , and then the buf latch
error message showed up. This time we restarted the services before 5
minutes had passed.)
The SQL log shows nothing abnormal. The SQL Agent log shows nothing
abnormal. The Event Log shows nothing abnormal. Stopping the service
through the SQL Service Manager doesn't work - we have to go into the
Services control panel, stop the Agent then stop the service, and when
the service says that it cannot be killed, we then run the "kill"
command on it (from the resource kit, I believe) and it stops
immediately. Once this occurs, we can use the SQL Service manager to
start everything up successfully, with no problems in the logs.
Note: we have not rebooted the server yet, just
stopped/killed/restarted the services. We plan on rebooting this
weekend.
Timeline:
10:20 - an openquery job runs successfully - as far as I know,
everything is okay at this point.
10:22 - for some reason, a transaction log backup job does not run
(ran successfully at 10:02, runs every 20 minutes.). That time
doesn't show up at all in the Job History for that job, nor is there a
log file, nor was a report txt file generated.
10:29 - openquery job 1 fails to run. Connections are sluggish, but
open connections can run queries.
10:30 - openquery job 2 fails to run. (not the same query as 1 or 3)
Connections are sluggish, but open connections can run queries.
10:30-10:40 Enterprise Manager cannot connect - stuck during
connection. This is the case on multiple machines, as well as on the
server. My Enterprise Manager doesn't respond, and I cannot start a
new instance. "Select getdate()" can take several seconds to run, and
I get a "Lost connection" error.
10:40 - openquery job 3 fails to run. Profiler shows my openquery job
was the last thing run - no further profile messages for the next 2-3
minutes.
10:43 - We start shutting down the server.
What do the system resources look like when the box starts to drag its
heels? Is the CPU pegged really high? Has it been a long time since the
last reboot? Is SQL using pretty much all the memory?
I had a rare memory fragmentation issue about a year ago on one of the
earlier versions of SQL 2000 (certain pre-SP3). It occurred after adding
more than 3GB of RAM to the box and turning on /3GB in boot.ini. Our backup
(tlog & full) were taking ages to complete and the CPU would go nuts. I had
a Microsoft Premier case going for a couple month trying to sort it out. It
turned out to be a memory fragmentation issue that was most apparent during
backups when the backup process would check for the largest contiguous chunk
of memory to use for a particular part of the backup operation, so even tiny
backups (< 2MB) were taking 30 seconds to complete (when you multiple that
by 100+ databases and repeat that hourly then that becomes some serious
time).
Anyway, for our problem, which to me sounds vaguely similar to your problem,
a (pre-SP3) hotfix sorted us out (after troubleshooting the issue by turning
on a bunch of trace flags at PSS's request). It may not be the same
thing...<shrug>. You need to figure out what's going on with the physical
resources on your box (CPU, memory, NIC, disk I/O, etc.). It may be the
case that SP3a (which you should think about installing some time soon
anyway - remember the SQL Slammer worm?!?!?) fixes your issue...maybe.
HTH
Cheers,
Mike
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0409301020.364a05e2@.posting.google.c om...
> I've been having the same problem for 2 weeks now. If anyone has any
> ideas, I'd love to hear them. We are using both SQL and Windows
> Authentication. I was running a Profiler Trace at the time, and am
> going through it now but have not seen anything yet.
> Thanks in advance.
> About once a week, at no fixed time (but so far, between 8am and
> 11am), my SQL Server 2000 on Windows 2000 (8.00.679) will stop
> responding. New connections will at first take forever to connect,
> then an error message about the PreLoginHandshake(). We have to kill
> the service (read below) to stop it, but it comes back without
> problems.
> The very first time this happened, I got buf latch errors in the SQL
> Error Log, but not this time. (However, the first time we waited for
> about 5 minutes after it stopped responding , and then the buf latch
> error message showed up. This time we restarted the services before 5
> minutes had passed.)
> The SQL log shows nothing abnormal. The SQL Agent log shows nothing
> abnormal. The Event Log shows nothing abnormal. Stopping the service
> through the SQL Service Manager doesn't work - we have to go into the
> Services control panel, stop the Agent then stop the service, and when
> the service says that it cannot be killed, we then run the "kill"
> command on it (from the resource kit, I believe) and it stops
> immediately. Once this occurs, we can use the SQL Service manager to
> start everything up successfully, with no problems in the logs.
> Note: we have not rebooted the server yet, just
> stopped/killed/restarted the services. We plan on rebooting this
> weekend.
> Timeline:
> 10:20 - an openquery job runs successfully - as far as I know,
> everything is okay at this point.
> 10:22 - for some reason, a transaction log backup job does not run
> (ran successfully at 10:02, runs every 20 minutes.). That time
> doesn't show up at all in the Job History for that job, nor is there a
> log file, nor was a report txt file generated.
> 10:29 - openquery job 1 fails to run. Connections are sluggish, but
> open connections can run queries.
> 10:30 - openquery job 2 fails to run. (not the same query as 1 or 3)
> Connections are sluggish, but open connections can run queries.
> 10:30-10:40 Enterprise Manager cannot connect - stuck during
> connection. This is the case on multiple machines, as well as on the
> server. My Enterprise Manager doesn't respond, and I cannot start a
> new instance. "Select getdate()" can take several seconds to run, and
> I get a "Lost connection" error.
> 10:40 - openquery job 3 fails to run. Profiler shows my openquery job
> was the last thing run - no further profile messages for the next 2-3
> minutes.
> 10:43 - We start shutting down the server.
|||"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message news:<OsWUqF7pEHA.132@.TK2MSFTNGP14.phx.gbl>...
> What do the system resources look like when the box starts to drag its
> heels? Is the CPU pegged really high? Has it been a long time since the
> last reboot? Is SQL using pretty much all the memory?
CPU is at about 1%. We've got it set up so that there's about 300mb
free on the system, and the last reboot occured less than a week
before this problem surfaced.
However, the bit about the large database backups is intriguing. I'll
have to check that. Thanks.
|||bourgon@.gmail.com (Michael Bourgon) wrote in message news:<558b578d.0410040510.5139efb2@.posting.google. com>...
> CPU is at about 1%. We've got it set up so that there's about 300mb
> free on the system, and the last reboot occured less than a week
> before this problem surfaced.
> However, the bit about the large database backups is intriguing. I'll
> have to check that. Thanks.
As a followup, we believe it was due to this. We saw some potential
memory problems with the process associated with the NIC, and so we
changed the database backups to not backup to a different computer,
but stay on the system. This seems to have stabilized it.
ideas, I'd love to hear them. We are using both SQL and Windows
Authentication. I was running a Profiler Trace at the time, and am
going through it now but have not seen anything yet.
Thanks in advance.
About once a week, at no fixed time (but so far, between 8am and
11am), my SQL Server 2000 on Windows 2000 (8.00.679) will stop
responding. New connections will at first take forever to connect,
then an error message about the PreLoginHandshake(). We have to kill
the service (read below) to stop it, but it comes back without
problems.
The very first time this happened, I got buf latch errors in the SQL
Error Log, but not this time. (However, the first time we waited for
about 5 minutes after it stopped responding , and then the buf latch
error message showed up. This time we restarted the services before 5
minutes had passed.)
The SQL log shows nothing abnormal. The SQL Agent log shows nothing
abnormal. The Event Log shows nothing abnormal. Stopping the service
through the SQL Service Manager doesn't work - we have to go into the
Services control panel, stop the Agent then stop the service, and when
the service says that it cannot be killed, we then run the "kill"
command on it (from the resource kit, I believe) and it stops
immediately. Once this occurs, we can use the SQL Service manager to
start everything up successfully, with no problems in the logs.
Note: we have not rebooted the server yet, just
stopped/killed/restarted the services. We plan on rebooting this
weekend.
Timeline:
10:20 - an openquery job runs successfully - as far as I know,
everything is okay at this point.
10:22 - for some reason, a transaction log backup job does not run
(ran successfully at 10:02, runs every 20 minutes.). That time
doesn't show up at all in the Job History for that job, nor is there a
log file, nor was a report txt file generated.
10:29 - openquery job 1 fails to run. Connections are sluggish, but
open connections can run queries.
10:30 - openquery job 2 fails to run. (not the same query as 1 or 3)
Connections are sluggish, but open connections can run queries.
10:30-10:40 Enterprise Manager cannot connect - stuck during
connection. This is the case on multiple machines, as well as on the
server. My Enterprise Manager doesn't respond, and I cannot start a
new instance. "Select getdate()" can take several seconds to run, and
I get a "Lost connection" error.
10:40 - openquery job 3 fails to run. Profiler shows my openquery job
was the last thing run - no further profile messages for the next 2-3
minutes.
10:43 - We start shutting down the server.
What do the system resources look like when the box starts to drag its
heels? Is the CPU pegged really high? Has it been a long time since the
last reboot? Is SQL using pretty much all the memory?
I had a rare memory fragmentation issue about a year ago on one of the
earlier versions of SQL 2000 (certain pre-SP3). It occurred after adding
more than 3GB of RAM to the box and turning on /3GB in boot.ini. Our backup
(tlog & full) were taking ages to complete and the CPU would go nuts. I had
a Microsoft Premier case going for a couple month trying to sort it out. It
turned out to be a memory fragmentation issue that was most apparent during
backups when the backup process would check for the largest contiguous chunk
of memory to use for a particular part of the backup operation, so even tiny
backups (< 2MB) were taking 30 seconds to complete (when you multiple that
by 100+ databases and repeat that hourly then that becomes some serious
time).
Anyway, for our problem, which to me sounds vaguely similar to your problem,
a (pre-SP3) hotfix sorted us out (after troubleshooting the issue by turning
on a bunch of trace flags at PSS's request). It may not be the same
thing...<shrug>. You need to figure out what's going on with the physical
resources on your box (CPU, memory, NIC, disk I/O, etc.). It may be the
case that SP3a (which you should think about installing some time soon
anyway - remember the SQL Slammer worm?!?!?) fixes your issue...maybe.
HTH
Cheers,
Mike
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0409301020.364a05e2@.posting.google.c om...
> I've been having the same problem for 2 weeks now. If anyone has any
> ideas, I'd love to hear them. We are using both SQL and Windows
> Authentication. I was running a Profiler Trace at the time, and am
> going through it now but have not seen anything yet.
> Thanks in advance.
> About once a week, at no fixed time (but so far, between 8am and
> 11am), my SQL Server 2000 on Windows 2000 (8.00.679) will stop
> responding. New connections will at first take forever to connect,
> then an error message about the PreLoginHandshake(). We have to kill
> the service (read below) to stop it, but it comes back without
> problems.
> The very first time this happened, I got buf latch errors in the SQL
> Error Log, but not this time. (However, the first time we waited for
> about 5 minutes after it stopped responding , and then the buf latch
> error message showed up. This time we restarted the services before 5
> minutes had passed.)
> The SQL log shows nothing abnormal. The SQL Agent log shows nothing
> abnormal. The Event Log shows nothing abnormal. Stopping the service
> through the SQL Service Manager doesn't work - we have to go into the
> Services control panel, stop the Agent then stop the service, and when
> the service says that it cannot be killed, we then run the "kill"
> command on it (from the resource kit, I believe) and it stops
> immediately. Once this occurs, we can use the SQL Service manager to
> start everything up successfully, with no problems in the logs.
> Note: we have not rebooted the server yet, just
> stopped/killed/restarted the services. We plan on rebooting this
> weekend.
> Timeline:
> 10:20 - an openquery job runs successfully - as far as I know,
> everything is okay at this point.
> 10:22 - for some reason, a transaction log backup job does not run
> (ran successfully at 10:02, runs every 20 minutes.). That time
> doesn't show up at all in the Job History for that job, nor is there a
> log file, nor was a report txt file generated.
> 10:29 - openquery job 1 fails to run. Connections are sluggish, but
> open connections can run queries.
> 10:30 - openquery job 2 fails to run. (not the same query as 1 or 3)
> Connections are sluggish, but open connections can run queries.
> 10:30-10:40 Enterprise Manager cannot connect - stuck during
> connection. This is the case on multiple machines, as well as on the
> server. My Enterprise Manager doesn't respond, and I cannot start a
> new instance. "Select getdate()" can take several seconds to run, and
> I get a "Lost connection" error.
> 10:40 - openquery job 3 fails to run. Profiler shows my openquery job
> was the last thing run - no further profile messages for the next 2-3
> minutes.
> 10:43 - We start shutting down the server.
|||"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message news:<OsWUqF7pEHA.132@.TK2MSFTNGP14.phx.gbl>...
> What do the system resources look like when the box starts to drag its
> heels? Is the CPU pegged really high? Has it been a long time since the
> last reboot? Is SQL using pretty much all the memory?
CPU is at about 1%. We've got it set up so that there's about 300mb
free on the system, and the last reboot occured less than a week
before this problem surfaced.
However, the bit about the large database backups is intriguing. I'll
have to check that. Thanks.
|||bourgon@.gmail.com (Michael Bourgon) wrote in message news:<558b578d.0410040510.5139efb2@.posting.google. com>...
> CPU is at about 1%. We've got it set up so that there's about 300mb
> free on the system, and the last reboot occured less than a week
> before this problem surfaced.
> However, the bit about the large database backups is intriguing. I'll
> have to check that. Thanks.
As a followup, we believe it was due to this. We saw some potential
memory problems with the process associated with the NIC, and so we
changed the database backups to not backup to a different computer,
but stay on the system. This seems to have stabilized it.
Monday, February 27, 2012
Help - Error: Unable to open the physical file
Hi, I've just finished creating my first ASP.NET website. I created it on my laptop and it works perfectly, but I'm having some problems deploying it to my web server (PC).
The site works ok, but when I try to log in to my client area, I get this error:
"Unable to open the physical file "c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf". Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\ONeillStrata\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
I've searched far and wide for a solution and have read many articles, but none seem to be able to fix my problem. I've tried using that SEUtil tool, but that didn't work, I've made sure the App_Data directory on the web server isn't read only (the read-only checkbox isn't ticked, but it DOES have that little green square which I can't get rid of), I've assigned the ASPNET user as a db owner and that didn't work and I've manually attached the database to the web server's instance of SQL Express 2005.
When I launch the solution in VS2005 on the web server, I can browse through the database and see the tables and data etc, but when I try to run the application, I get that message above...
Surely it shouldn't be this hard to get it going?
Any help would be massively appreciated.
Hi,
It seems that the physical file location of the aspnetdb.mdf is wrong.
Please open your IIS, right click on your default web site,and click on the properties, change to the ASP.NET tab, edit configurations.Then you can see the LocalSqlServer string listed there, change the string into the following:data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true.
Thanks.
Subscribe to:
Posts (Atom)