Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
Showing posts with label empty. Show all posts
Showing posts with label empty. Show all posts
Wednesday, March 28, 2012
how to determine the best timeout value
Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
Wednesday, March 21, 2012
How to determinate a filed is empty
I want to update a field if a field is blank.
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatoli
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatoli
How to determinate a filed is empty
I want to update a field if a field is blank.
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatolisql
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatolisql
Friday, February 24, 2012
How to delete a database without name
Hi,
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
Edgar
Hi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>
|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>
|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> know
>
|||If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tables that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup history tables in msdb (which are OK
to have rows for non-existing databases in).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
>
|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message news:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
Edgar
Hi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>
|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>
|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> know
>
|||If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tables that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup history tables in msdb (which are OK
to have rows for non-existing databases in).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
>
|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message news:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
How to delete a database without name
Hi,
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
EdgarHi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> know
>|||If you actually did a DELETE against sysdatabases, you probably have some ot
her stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tab
les that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup hist
ory tables in msdb (which are OK
to have rows for non-existing databases in).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs
4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
>|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message news
:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have
to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
EdgarHi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> know
>|||If you actually did a DELETE against sysdatabases, you probably have some ot
her stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tab
les that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup hist
ory tables in msdb (which are OK
to have rows for non-existing databases in).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs
4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
>|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message news
:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have
to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
How to delete a database without name
Hi,
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
EdgarHi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > Hi,
> >
> > In my databases list there is one database with an empty name. I don't
> know
> > how it got there, but I want to get rid of it. My whole SQL Server is
> > messed up now, I cannot even add a table or something like that.
> >
> > I cannot drop it from Enterprise manager.
> >
> > I cannot drop it with SQL, because I don't have a database name.
> >
> > I tried with SQLDMO :
> >
> > Public Sub DropDb()
> > Dim ss As New SQLDMO.SQLServer
> > Dim db As New SQLDMO.Database
> > Dim i As Integer
> > ss.Connect , "sa", "sa"
> > For i = 1 To ss.Databases.Count
> > Debug.Print ss.Databases(i).Name, i
> > Next
> > ss.Databases(1).Remove
> > End Sub
> >
> > But that gave me an error: Excepion_Access_Violation
> >
> > How can I delete this database?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks,
> >
> > Edgar
> >
> >
>|||If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tables that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup history tables in msdb (which are OK
to have rows for non-existing databases in).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > In Query Analyzer query the Sysdatabases table in Master database and see
> > the contents for the empty database.
> >
> > select * from master..sysdatabases
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> > news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > > Hi,
> > >
> > > In my databases list there is one database with an empty name. I don't
> > know
> > > how it got there, but I want to get rid of it. My whole SQL Server is
> > > messed up now, I cannot even add a table or something like that.
> > >
> > > I cannot drop it from Enterprise manager.
> > >
> > > I cannot drop it with SQL, because I don't have a database name.
> > >
> > > I tried with SQLDMO :
> > >
> > > Public Sub DropDb()
> > > Dim ss As New SQLDMO.SQLServer
> > > Dim db As New SQLDMO.Database
> > > Dim i As Integer
> > > ss.Connect , "sa", "sa"
> > > For i = 1 To ss.Databases.Count
> > > Debug.Print ss.Databases(i).Name, i
> > > Next
> > > ss.Databases(1).Remove
> > > End Sub
> > >
> > > But that gave me an error: Excepion_Access_Violation
> > >
> > > How can I delete this database?
> > >
> > > Any help will be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Edgar
> > >
> > >
> >
> >
>|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message news:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > Hi,
> >
> > In my databases list there is one database with an empty name. I don't
> > know
> > how it got there, but I want to get rid of it. My whole SQL Server is
> > messed up now, I cannot even add a table or something like that.
> >
> > I cannot drop it from Enterprise manager.
> >
> > I cannot drop it with SQL, because I don't have a database name.
> >
> > I tried with SQLDMO :
> >
> > Public Sub DropDb()
> > Dim ss As New SQLDMO.SQLServer
> > Dim db As New SQLDMO.Database
> > Dim i As Integer
> > ss.Connect , "sa", "sa"
> > For i = 1 To ss.Databases.Count
> > Debug.Print ss.Databases(i).Name, i
> > Next
> > ss.Databases(1).Remove
> > End Sub
> >
> > But that gave me an error: Excepion_Access_Violation
> >
> > How can I delete this database?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks,
> >
> > Edgar
> >
> >
In my databases list there is one database with an empty name. I don't know
how it got there, but I want to get rid of it. My whole SQL Server is
messed up now, I cannot even add a table or something like that.
I cannot drop it from Enterprise manager.
I cannot drop it with SQL, because I don't have a database name.
I tried with SQLDMO :
Public Sub DropDb()
Dim ss As New SQLDMO.SQLServer
Dim db As New SQLDMO.Database
Dim i As Integer
ss.Connect , "sa", "sa"
For i = 1 To ss.Databases.Count
Debug.Print ss.Databases(i).Name, i
Next
ss.Databases(1).Remove
End Sub
But that gave me an error: Excepion_Access_Violation
How can I delete this database?
Any help will be greatly appreciated.
Thanks,
EdgarHi,
In Query Analyzer query the Sysdatabases table in Master database and see
the contents for the empty database.
select * from master..sysdatabases
Thanks
Hari
MCDBA
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Are you sure the database name is empty? It can be a space, for example:
CREATE DATABASE [ ]
GO
DROP DATABASE [ ]
works fine.
You can confirm if the database name is really empty with:
SELECT name, LEN(name)
FROM master.dbo.sysdatabases
The name could be non-space white space though, and in that case you have to
use the ASCII function to find out what it is.
Jacco Schalkwijk
SQL Server MVP
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> Hi,
> In my databases list there is one database with an empty name. I don't
> know
> how it got there, but I want to get rid of it. My whole SQL Server is
> messed up now, I cannot even add a table or something like that.
> I cannot drop it from Enterprise manager.
> I cannot drop it with SQL, because I don't have a database name.
> I tried with SQLDMO :
> Public Sub DropDb()
> Dim ss As New SQLDMO.SQLServer
> Dim db As New SQLDMO.Database
> Dim i As Integer
> ss.Connect , "sa", "sa"
> For i = 1 To ss.Databases.Count
> Debug.Print ss.Databases(i).Name, i
> Next
> ss.Databases(1).Remove
> End Sub
> But that gave me an error: Excepion_Access_Violation
> How can I delete this database?
> Any help will be greatly appreciated.
> Thanks,
> Edgar
>|||Hi,
Deleting the record from master..sysdatabases fixed the problem.
Thanks for your help.
Edgar
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> Hi,
> In Query Analyzer query the Sysdatabases table in Master database and see
> the contents for the empty database.
> select * from master..sysdatabases
> Thanks
> Hari
> MCDBA
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > Hi,
> >
> > In my databases list there is one database with an empty name. I don't
> know
> > how it got there, but I want to get rid of it. My whole SQL Server is
> > messed up now, I cannot even add a table or something like that.
> >
> > I cannot drop it from Enterprise manager.
> >
> > I cannot drop it with SQL, because I don't have a database name.
> >
> > I tried with SQLDMO :
> >
> > Public Sub DropDb()
> > Dim ss As New SQLDMO.SQLServer
> > Dim db As New SQLDMO.Database
> > Dim i As Integer
> > ss.Connect , "sa", "sa"
> > For i = 1 To ss.Databases.Count
> > Debug.Print ss.Databases(i).Name, i
> > Next
> > ss.Databases(1).Remove
> > End Sub
> >
> > But that gave me an error: Excepion_Access_Violation
> >
> > How can I delete this database?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks,
> >
> > Edgar
> >
> >
>|||If you actually did a DELETE against sysdatabases, you probably have some other stuff referring to this
database. I.e., you might just have an inconsistent master database. Two tables that comes to mind are
sysaltfiles (some DBCC CHECK... command might find that) and the backup history tables in msdb (which are OK
to have rows for non-existing databases in).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message news:411c9a6e$0$49711$e4fe514c@.news.xs4all.nl...
> Hi,
> Deleting the record from master..sysdatabases fixed the problem.
> Thanks for your help.
> Edgar
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OiI2E9RgEHA.2984@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > In Query Analyzer query the Sysdatabases table in Master database and see
> > the contents for the empty database.
> >
> > select * from master..sysdatabases
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> > news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > > Hi,
> > >
> > > In my databases list there is one database with an empty name. I don't
> > know
> > > how it got there, but I want to get rid of it. My whole SQL Server is
> > > messed up now, I cannot even add a table or something like that.
> > >
> > > I cannot drop it from Enterprise manager.
> > >
> > > I cannot drop it with SQL, because I don't have a database name.
> > >
> > > I tried with SQLDMO :
> > >
> > > Public Sub DropDb()
> > > Dim ss As New SQLDMO.SQLServer
> > > Dim db As New SQLDMO.Database
> > > Dim i As Integer
> > > ss.Connect , "sa", "sa"
> > > For i = 1 To ss.Databases.Count
> > > Debug.Print ss.Databases(i).Name, i
> > > Next
> > > ss.Databases(1).Remove
> > > End Sub
> > >
> > > But that gave me an error: Excepion_Access_Violation
> > >
> > > How can I delete this database?
> > >
> > > Any help will be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Edgar
> > >
> > >
> >
> >
>|||You should be careful using len() function - it returns the number of
characters excluding trailing blanks.
Leonid.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message news:<OXHCmDSgEHA.3264@.tk2msftngp13.phx.gbl>...
> Are you sure the database name is empty? It can be a space, for example:
> CREATE DATABASE [ ]
> GO
> DROP DATABASE [ ]
> works fine.
> You can confirm if the database name is really empty with:
> SELECT name, LEN(name)
> FROM master.dbo.sysdatabases
> The name could be non-space white space though, and in that case you have to
> use the ASCII function to find out what it is.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Edgar Walther" <EdgarW@.metrixbv.nl> wrote in message
> news:411c9232$0$10528$e4fe514c@.news.xs4all.nl...
> > Hi,
> >
> > In my databases list there is one database with an empty name. I don't
> > know
> > how it got there, but I want to get rid of it. My whole SQL Server is
> > messed up now, I cannot even add a table or something like that.
> >
> > I cannot drop it from Enterprise manager.
> >
> > I cannot drop it with SQL, because I don't have a database name.
> >
> > I tried with SQLDMO :
> >
> > Public Sub DropDb()
> > Dim ss As New SQLDMO.SQLServer
> > Dim db As New SQLDMO.Database
> > Dim i As Integer
> > ss.Connect , "sa", "sa"
> > For i = 1 To ss.Databases.Count
> > Debug.Print ss.Databases(i).Name, i
> > Next
> > ss.Databases(1).Remove
> > End Sub
> >
> > But that gave me an error: Excepion_Access_Violation
> >
> > How can I delete this database?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks,
> >
> > Edgar
> >
> >
Subscribe to:
Posts (Atom)