Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts

Tuesday, March 27, 2012

Basic LIKE question?

not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present.

...WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%'

however it appears to finding only the rows where all three words are present in the L_Keywords field

never mind - getting a little late - mind melt.

|||

Big Smile No problem, it happens to all of us.

For posterity, and for others who need the answer, the OR keyword should be used rather than AND.

Don

Sunday, February 19, 2012

backup/restore from SQL 2000 to SQL 2005: diagram ownership

I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
and machine name\my login name.
(2) Copy backup file to another machine with SQL Server 2005.
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database.
(5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
work.
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I do not know the detail steps for doing it. In the MSDN, it suggests that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
It is OK for exec.
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
This produces an error: 15151
Are there any tricks in backup and restore process?
Thank you for any help.
David> This produces an error: 15151
Can you show us the error message and not only the error number?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> I have trouble with it. I have tried several ways and talked to some peopl
e
> in the forum, but it is not working. The following is waht I did.
> (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> and machine name\my login name.
> (2) Copy backup file to another machine with SQL Server 2005.
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database.
> (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> work.
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this databas
e
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog bo
x
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I do not know the detail steps for doing it. In the MSDN, it suggests that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> It is OK for exec.
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> This produces an error: 15151
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>
>|||Can not find the principal 'valid_login', because it does not exist.
Should 'valid_login' be a user name?
How to create a correct user name?
I created one, and use the username instead of 'valid_login' and got same
error.
David
"Tibor Karaszi" wrote:

> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>|||If "valid_login" is a username, how to create such a user?
any detail steps?
Thanks
David
"Tibor Karaszi" wrote:

> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>|||> Should 'valid_login' be a user name?
No, it should be a login name. If you don't know the difference between a lo
gin and a user, I
strongly suggest you read up on the subject. Here's a good start:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808
659449c5.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...[vbcol=seagreen]
> Can not find the principal 'valid_login', because it does not exist.
> Should 'valid_login' be a user name?
> How to create a correct user name?
> I created one, and use the username instead of 'valid_login' and got same
> error.
> David
> "Tibor Karaszi" wrote:
>|||Is a lohin name displayed under security under databases tree node?
David
"Tibor Karaszi" wrote:

> No, it should be a login name. If you don't know the difference between a
login and a user, I
> strongly suggest you read up on the subject. Here's a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-78
08659449c5.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
>|||No, under the server node (the root node), in the security folder.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...[vbcol=seagreen]
> Is a lohin name displayed under security under databases tree node?
> David
> "Tibor Karaszi" wrote:
>|||Thank you.
so my login name in new machine should be (under sqlserver\security\logins:
new_machine_name\Firstname Lastname (a sapce between them)
So I should use
ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
Lastname
Now I get error:
incorrect syntax near 'new_machine_name'.
How to correct it?
David
"Tibor Karaszi" wrote:

> No, under the server node (the root node), in the security folder.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
>|||I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
and got error: Incorrect syntax near ''.
David
"david" wrote:
[vbcol=seagreen]
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins
:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
>|||I used
ALTER AUTHORIZATION on DATABASE::databasename TO 'new_machine_name\Firstname
Lastname'
and I get error:
incorrect syntax near 'new_machine_name\Firstname Lastname' .
If I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
I fet error: incorrect syntax near ''
David
"david" wrote:
[vbcol=seagreen]
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins
:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
>

backup/restore from SQL 2000 to SQL 2005: diagram ownership

I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
and machine name\my login name.
(2) Copy backup file to another machine with SQL Server 2005.
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database.
(5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
work.
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I do not know the detail steps for doing it. In the MSDN, it suggests that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
It is OK for exec.
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
This produces an error: 15151
Are there any tricks in backup and restore process?
Thank you for any help.
David> This produces an error: 15151
Can you show us the error message and not only the error number?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> I have trouble with it. I have tried several ways and talked to some people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> and machine name\my login name.
> (2) Copy backup file to another machine with SQL Server 2005.
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database.
> (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> work.
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I do not know the detail steps for doing it. In the MSDN, it suggests that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> It is OK for exec.
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> This produces an error: 15151
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>
>|||Can not find the principal 'valid_login', because it does not exist.
Should 'valid_login' be a user name?
How to create a correct user name?
I created one, and use the username instead of 'valid_login' and got same
error.
David
"Tibor Karaszi" wrote:
> > This produces an error: 15151
> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> > I have trouble with it. I have tried several ways and talked to some people
> > in the forum, but it is not working. The following is waht I did.
> >
> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> > and machine name\my login name.
> > (2) Copy backup file to another machine with SQL Server 2005.
> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > (4) Use Restore to restore the backup to a database.
> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> > work.
> > the error massage. I can not either display the diagram or create new
> > diagram on the database. The error message:
> > "Database diagram support objects cannot be installed because this database
> > does not have valid owner.
> > To continue, first use the Files page of the Database Properties dialog box
> > ot the Alter Authorization statement
> > to set the database owner to a valid login, then add the datatbase diagram
> > support objects."
> >
> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> > For compatiable, run
> > exec sp_dbcmptlevel 'database_name', '90';
> > It is OK for exec.
> > For alter authorization, run
> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> >
> > This produces an error: 15151
> >
> > Are there any tricks in backup and restore process?
> >
> > Thank you for any help.
> >
> > David
> >
> >
> >
>|||If "valid_login" is a username, how to create such a user?
any detail steps?
Thanks
David
"Tibor Karaszi" wrote:
> > This produces an error: 15151
> Can you show us the error message and not only the error number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> > I have trouble with it. I have tried several ways and talked to some people
> > in the forum, but it is not working. The following is waht I did.
> >
> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> > and machine name\my login name.
> > (2) Copy backup file to another machine with SQL Server 2005.
> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > (4) Use Restore to restore the backup to a database.
> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> > work.
> > the error massage. I can not either display the diagram or create new
> > diagram on the database. The error message:
> > "Database diagram support objects cannot be installed because this database
> > does not have valid owner.
> > To continue, first use the Files page of the Database Properties dialog box
> > ot the Alter Authorization statement
> > to set the database owner to a valid login, then add the datatbase diagram
> > support objects."
> >
> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> > For compatiable, run
> > exec sp_dbcmptlevel 'database_name', '90';
> > It is OK for exec.
> > For alter authorization, run
> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> >
> > This produces an error: 15151
> >
> > Are there any tricks in backup and restore process?
> >
> > Thank you for any help.
> >
> > David
> >
> >
> >
>|||> Should 'valid_login' be a user name?
No, it should be a login name. If you don't know the difference between a login and a user, I
strongly suggest you read up on the subject. Here's a good start:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> Can not find the principal 'valid_login', because it does not exist.
> Should 'valid_login' be a user name?
> How to create a correct user name?
> I created one, and use the username instead of 'valid_login' and got same
> error.
> David
> "Tibor Karaszi" wrote:
>> > This produces an error: 15151
>> Can you show us the error message and not only the error number?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>> > I have trouble with it. I have tried several ways and talked to some people
>> > in the forum, but it is not working. The following is waht I did.
>> >
>> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
>> > and machine name\my login name.
>> > (2) Copy backup file to another machine with SQL Server 2005.
>> > (3) Start SQL Server Management Studio of SQL Server 2005.
>> > (4) Use Restore to restore the backup to a database.
>> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
>> > work.
>> > the error massage. I can not either display the diagram or create new
>> > diagram on the database. The error message:
>> > "Database diagram support objects cannot be installed because this database
>> > does not have valid owner.
>> > To continue, first use the Files page of the Database Properties dialog box
>> > ot the Alter Authorization statement
>> > to set the database owner to a valid login, then add the datatbase diagram
>> > support objects."
>> >
>> > I do not know the detail steps for doing it. In the MSDN, it suggests that
>> > For compatiable, run
>> > exec sp_dbcmptlevel 'database_name', '90';
>> > It is OK for exec.
>> > For alter authorization, run
>> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
>> >
>> > This produces an error: 15151
>> >
>> > Are there any tricks in backup and restore process?
>> >
>> > Thank you for any help.
>> >
>> > David
>> >
>> >
>> >|||Is a lohin name displayed under security under databases tree node?
David
"Tibor Karaszi" wrote:
> > Should 'valid_login' be a user name?
> No, it should be a login name. If you don't know the difference between a login and a user, I
> strongly suggest you read up on the subject. Here's a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> > Can not find the principal 'valid_login', because it does not exist.
> >
> > Should 'valid_login' be a user name?
> >
> > How to create a correct user name?
> >
> > I created one, and use the username instead of 'valid_login' and got same
> > error.
> >
> > David
> >
> > "Tibor Karaszi" wrote:
> >
> >> > This produces an error: 15151
> >>
> >> Can you show us the error message and not only the error number?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> >> > I have trouble with it. I have tried several ways and talked to some people
> >> > in the forum, but it is not working. The following is waht I did.
> >> >
> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> >> > and machine name\my login name.
> >> > (2) Copy backup file to another machine with SQL Server 2005.
> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
> >> > (4) Use Restore to restore the backup to a database.
> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> >> > work.
> >> > the error massage. I can not either display the diagram or create new
> >> > diagram on the database. The error message:
> >> > "Database diagram support objects cannot be installed because this database
> >> > does not have valid owner.
> >> > To continue, first use the Files page of the Database Properties dialog box
> >> > ot the Alter Authorization statement
> >> > to set the database owner to a valid login, then add the datatbase diagram
> >> > support objects."
> >> >
> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> >> > For compatiable, run
> >> > exec sp_dbcmptlevel 'database_name', '90';
> >> > It is OK for exec.
> >> > For alter authorization, run
> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> >> >
> >> > This produces an error: 15151
> >> >
> >> > Are there any tricks in backup and restore process?
> >> >
> >> > Thank you for any help.
> >> >
> >> > David
> >> >
> >> >
> >> >
> >>
>|||No, under the server node (the root node), in the security folder.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"david" <david@.discussions.microsoft.com> wrote in message
news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
> Is a lohin name displayed under security under databases tree node?
> David
> "Tibor Karaszi" wrote:
>> > Should 'valid_login' be a user name?
>> No, it should be a login name. If you don't know the difference between a login and a user, I
>> strongly suggest you read up on the subject. Here's a good start:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
>> > Can not find the principal 'valid_login', because it does not exist.
>> >
>> > Should 'valid_login' be a user name?
>> >
>> > How to create a correct user name?
>> >
>> > I created one, and use the username instead of 'valid_login' and got same
>> > error.
>> >
>> > David
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > This produces an error: 15151
>> >>
>> >> Can you show us the error message and not only the error number?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "david" <david@.discussions.microsoft.com> wrote in message
>> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
>> >> > I have trouble with it. I have tried several ways and talked to some people
>> >> > in the forum, but it is not working. The following is waht I did.
>> >> >
>> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
>> >> > and machine name\my login name.
>> >> > (2) Copy backup file to another machine with SQL Server 2005.
>> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
>> >> > (4) Use Restore to restore the backup to a database.
>> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
>> >> > work.
>> >> > the error massage. I can not either display the diagram or create new
>> >> > diagram on the database. The error message:
>> >> > "Database diagram support objects cannot be installed because this database
>> >> > does not have valid owner.
>> >> > To continue, first use the Files page of the Database Properties dialog box
>> >> > ot the Alter Authorization statement
>> >> > to set the database owner to a valid login, then add the datatbase diagram
>> >> > support objects."
>> >> >
>> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
>> >> > For compatiable, run
>> >> > exec sp_dbcmptlevel 'database_name', '90';
>> >> > It is OK for exec.
>> >> > For alter authorization, run
>> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
>> >> >
>> >> > This produces an error: 15151
>> >> >
>> >> > Are there any tricks in backup and restore process?
>> >> >
>> >> > Thank you for any help.
>> >> >
>> >> > David
>> >> >
>> >> >
>> >> >
>> >>
>>|||Thank you.
so my login name in new machine should be (under sqlserver\security\logins:
new_machine_name\Firstname Lastname (a sapce between them)
So I should use
ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
Lastname
Now I get error:
incorrect syntax near 'new_machine_name'.
How to correct it?
David
"Tibor Karaszi" wrote:
> No, under the server node (the root node), in the security folder.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
> > Is a lohin name displayed under security under databases tree node?
> >
> > David
> >
> > "Tibor Karaszi" wrote:
> >
> >> > Should 'valid_login' be a user name?
> >>
> >> No, it should be a login name. If you don't know the difference between a login and a user, I
> >> strongly suggest you read up on the subject. Here's a good start:
> >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> >> > Can not find the principal 'valid_login', because it does not exist.
> >> >
> >> > Should 'valid_login' be a user name?
> >> >
> >> > How to create a correct user name?
> >> >
> >> > I created one, and use the username instead of 'valid_login' and got same
> >> > error.
> >> >
> >> > David
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> > This produces an error: 15151
> >> >>
> >> >> Can you show us the error message and not only the error number?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> >> >> > I have trouble with it. I have tried several ways and talked to some people
> >> >> > in the forum, but it is not working. The following is waht I did.
> >> >> >
> >> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> >> >> > and machine name\my login name.
> >> >> > (2) Copy backup file to another machine with SQL Server 2005.
> >> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
> >> >> > (4) Use Restore to restore the backup to a database.
> >> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> >> >> > work.
> >> >> > the error massage. I can not either display the diagram or create new
> >> >> > diagram on the database. The error message:
> >> >> > "Database diagram support objects cannot be installed because this database
> >> >> > does not have valid owner.
> >> >> > To continue, first use the Files page of the Database Properties dialog box
> >> >> > ot the Alter Authorization statement
> >> >> > to set the database owner to a valid login, then add the datatbase diagram
> >> >> > support objects."
> >> >> >
> >> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> >> >> > For compatiable, run
> >> >> > exec sp_dbcmptlevel 'database_name', '90';
> >> >> > It is OK for exec.
> >> >> > For alter authorization, run
> >> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> >> >> >
> >> >> > This produces an error: 15151
> >> >> >
> >> >> > Are there any tricks in backup and restore process?
> >> >> >
> >> >> > Thank you for any help.
> >> >> >
> >> >> > David
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >>
> >>
>|||I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
and got error: Incorrect syntax near '\'.
David
"david" wrote:
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
> > No, under the server node (the root node), in the security folder.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
> > > Is a lohin name displayed under security under databases tree node?
> > >
> > > David
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> > Should 'valid_login' be a user name?
> > >>
> > >> No, it should be a login name. If you don't know the difference between a login and a user, I
> > >> strongly suggest you read up on the subject. Here's a good start:
> > >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://sqlblog.com/blogs/tibor_karaszi
> > >>
> > >>
> > >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> > >> > Can not find the principal 'valid_login', because it does not exist.
> > >> >
> > >> > Should 'valid_login' be a user name?
> > >> >
> > >> > How to create a correct user name?
> > >> >
> > >> > I created one, and use the username instead of 'valid_login' and got same
> > >> > error.
> > >> >
> > >> > David
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> >> > This produces an error: 15151
> > >> >>
> > >> >> Can you show us the error message and not only the error number?
> > >> >>
> > >> >> --
> > >> >> Tibor Karaszi, SQL Server MVP
> > >> >> http://www.karaszi.com/sqlserver/default.asp
> > >> >> http://sqlblog.com/blogs/tibor_karaszi
> > >> >>
> > >> >>
> > >> >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> > >> >> > I have trouble with it. I have tried several ways and talked to some people
> > >> >> > in the forum, but it is not working. The following is waht I did.
> > >> >> >
> > >> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> > >> >> > and machine name\my login name.
> > >> >> > (2) Copy backup file to another machine with SQL Server 2005.
> > >> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > >> >> > (4) Use Restore to restore the backup to a database.
> > >> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> > >> >> > work.
> > >> >> > the error massage. I can not either display the diagram or create new
> > >> >> > diagram on the database. The error message:
> > >> >> > "Database diagram support objects cannot be installed because this database
> > >> >> > does not have valid owner.
> > >> >> > To continue, first use the Files page of the Database Properties dialog box
> > >> >> > ot the Alter Authorization statement
> > >> >> > to set the database owner to a valid login, then add the datatbase diagram
> > >> >> > support objects."
> > >> >> >
> > >> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> > >> >> > For compatiable, run
> > >> >> > exec sp_dbcmptlevel 'database_name', '90';
> > >> >> > It is OK for exec.
> > >> >> > For alter authorization, run
> > >> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> > >> >> >
> > >> >> > This produces an error: 15151
> > >> >> >
> > >> >> > Are there any tricks in backup and restore process?
> > >> >> >
> > >> >> > Thank you for any help.
> > >> >> >
> > >> >> > David
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >>
> > >>
> > >>
> >|||I used
ALTER AUTHORIZATION on DATABASE::databasename TO 'new_machine_name\Firstname
Lastname'
and I get error:
incorrect syntax near 'new_machine_name\Firstname Lastname' .
If I use
ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
Lastname
I fet error: incorrect syntax near '\'
David
"david" wrote:
> Thank you.
> so my login name in new machine should be (under sqlserver\security\logins:
> new_machine_name\Firstname Lastname (a sapce between them)
> So I should use
> ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> Lastname
> Now I get error:
> incorrect syntax near 'new_machine_name'.
> How to correct it?
> David
> "Tibor Karaszi" wrote:
> > No, under the server node (the root node), in the security folder.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
> > > Is a lohin name displayed under security under databases tree node?
> > >
> > > David
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> > Should 'valid_login' be a user name?
> > >>
> > >> No, it should be a login name. If you don't know the difference between a login and a user, I
> > >> strongly suggest you read up on the subject. Here's a good start:
> > >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://sqlblog.com/blogs/tibor_karaszi
> > >>
> > >>
> > >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> > >> > Can not find the principal 'valid_login', because it does not exist.
> > >> >
> > >> > Should 'valid_login' be a user name?
> > >> >
> > >> > How to create a correct user name?
> > >> >
> > >> > I created one, and use the username instead of 'valid_login' and got same
> > >> > error.
> > >> >
> > >> > David
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> >> > This produces an error: 15151
> > >> >>
> > >> >> Can you show us the error message and not only the error number?
> > >> >>
> > >> >> --
> > >> >> Tibor Karaszi, SQL Server MVP
> > >> >> http://www.karaszi.com/sqlserver/default.asp
> > >> >> http://sqlblog.com/blogs/tibor_karaszi
> > >> >>
> > >> >>
> > >> >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> > >> >> > I have trouble with it. I have tried several ways and talked to some people
> > >> >> > in the forum, but it is not working. The following is waht I did.
> > >> >> >
> > >> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> > >> >> > and machine name\my login name.
> > >> >> > (2) Copy backup file to another machine with SQL Server 2005.
> > >> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > >> >> > (4) Use Restore to restore the backup to a database.
> > >> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> > >> >> > work.
> > >> >> > the error massage. I can not either display the diagram or create new
> > >> >> > diagram on the database. The error message:
> > >> >> > "Database diagram support objects cannot be installed because this database
> > >> >> > does not have valid owner.
> > >> >> > To continue, first use the Files page of the Database Properties dialog box
> > >> >> > ot the Alter Authorization statement
> > >> >> > to set the database owner to a valid login, then add the datatbase diagram
> > >> >> > support objects."
> > >> >> >
> > >> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> > >> >> > For compatiable, run
> > >> >> > exec sp_dbcmptlevel 'database_name', '90';
> > >> >> > It is OK for exec.
> > >> >> > For alter authorization, run
> > >> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> > >> >> >
> > >> >> > This produces an error: 15151
> > >> >> >
> > >> >> > Are there any tricks in backup and restore process?
> > >> >> >
> > >> >> > Thank you for any help.
> > >> >> >
> > >> >> > David
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >>
> > >>
> > >>
> >|||Thank you
It works when I use double qoutes.
David
"david" wrote:
> I used
> ALTER AUTHORIZATION on DATABASE::databasename TO 'new_machine_name\Firstname
> Lastname'
> and I get error:
> incorrect syntax near 'new_machine_name\Firstname Lastname' .
> If I use
> ALTER AUTHORIZATION on DATABASE::databasename TO new_machine_name\Firstname
> Lastname
> I fet error: incorrect syntax near '\'
> David
> "david" wrote:
> > Thank you.
> > so my login name in new machine should be (under sqlserver\security\logins:
> >
> > new_machine_name\Firstname Lastname (a sapce between them)
> >
> > So I should use
> >
> > ALTER AUTHORIZATION on DATABASE::databasename new_machine_name\Firstname
> > Lastname
> >
> > Now I get error:
> > incorrect syntax near 'new_machine_name'.
> >
> > How to correct it?
> >
> > David
> >
> > "Tibor Karaszi" wrote:
> >
> > > No, under the server node (the root node), in the security folder.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "david" <david@.discussions.microsoft.com> wrote in message
> > > news:65951F87-DC9D-4CC9-8819-896924752E32@.microsoft.com...
> > > > Is a lohin name displayed under security under databases tree node?
> > > >
> > > > David
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> > Should 'valid_login' be a user name?
> > > >>
> > > >> No, it should be a login name. If you don't know the difference between a login and a user, I
> > > >> strongly suggest you read up on the subject. Here's a good start:
> > > >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808659449c5.htm
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://sqlblog.com/blogs/tibor_karaszi
> > > >>
> > > >>
> > > >> "david" <david@.discussions.microsoft.com> wrote in message
> > > >> news:6FFF5FBA-3E01-46AC-982C-B60CE88DD66F@.microsoft.com...
> > > >> > Can not find the principal 'valid_login', because it does not exist.
> > > >> >
> > > >> > Should 'valid_login' be a user name?
> > > >> >
> > > >> > How to create a correct user name?
> > > >> >
> > > >> > I created one, and use the username instead of 'valid_login' and got same
> > > >> > error.
> > > >> >
> > > >> > David
> > > >> >
> > > >> > "Tibor Karaszi" wrote:
> > > >> >
> > > >> >> > This produces an error: 15151
> > > >> >>
> > > >> >> Can you show us the error message and not only the error number?
> > > >> >>
> > > >> >> --
> > > >> >> Tibor Karaszi, SQL Server MVP
> > > >> >> http://www.karaszi.com/sqlserver/default.asp
> > > >> >> http://sqlblog.com/blogs/tibor_karaszi
> > > >> >>
> > > >> >>
> > > >> >> "david" <david@.discussions.microsoft.com> wrote in message
> > > >> >> news:5494E0D2-1097-476A-8C00-45FCE60DA67A@.microsoft.com...
> > > >> >> > I have trouble with it. I have tried several ways and talked to some people
> > > >> >> > in the forum, but it is not working. The following is waht I did.
> > > >> >> >
> > > >> >> > (1) Backup: use Enterprise Manager with COMPLETE backup setup, owners: dbo
> > > >> >> > and machine name\my login name.
> > > >> >> > (2) Copy backup file to another machine with SQL Server 2005.
> > > >> >> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > > >> >> > (4) Use Restore to restore the backup to a database.
> > > >> >> > (5) Tables, Viewers, and SPs are OK. But Database Diagrams need to further
> > > >> >> > work.
> > > >> >> > the error massage. I can not either display the diagram or create new
> > > >> >> > diagram on the database. The error message:
> > > >> >> > "Database diagram support objects cannot be installed because this database
> > > >> >> > does not have valid owner.
> > > >> >> > To continue, first use the Files page of the Database Properties dialog box
> > > >> >> > ot the Alter Authorization statement
> > > >> >> > to set the database owner to a valid login, then add the datatbase diagram
> > > >> >> > support objects."
> > > >> >> >
> > > >> >> > I do not know the detail steps for doing it. In the MSDN, it suggests that
> > > >> >> > For compatiable, run
> > > >> >> > exec sp_dbcmptlevel 'database_name', '90';
> > > >> >> > It is OK for exec.
> > > >> >> > For alter authorization, run
> > > >> >> > ALTER AUTHORIZATION ON DATABASE::databasename to valid_login
> > > >> >> >
> > > >> >> > This produces an error: 15151
> > > >> >> >
> > > >> >> > Are there any tricks in backup and restore process?
> > > >> >> >
> > > >> >> > Thank you for any help.
> > > >> >> >
> > > >> >> > David
> > > >> >> >
> > > >> >> >
> > > >> >> >
> > > >> >>
> > > >>
> > > >>
> > >|||hey dude its quiet easy just
only a single change will do what you want
open your database properties
choose options, next change 'compatability level' to SQL Server 2005(90)
hit on ok
and keep going BOL ^^^
*** Sent via Developersdex http://www.developersdex.com ***

Monday, February 13, 2012

Backup trouble with system database model

Hello all,
Al of a sudden I cannot backup my model database.
When I try to backup the database I get the following message:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'model' is full. Back up the transaction log for t
he database to free up some log space.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Could not write a CHECKPOINT record in database ID 3 because the log is out
of space.
I have 2 questions:
(1) To my knowledge the model database has not been changed since the SQL se
rver installation (+/- 1 year ago), accept a weekly "Optimization". The mode
l database has recovery model "Simple". How did the log file get filled?
(2) How do I empty the log file? I have tried to empty the log file via:
--> DUMP TRANSACTION model WITH NO_LOG
--> BACKUP LOG model WITH NO_LOG
However with no success.
Any suggestion is welcome, thanks,
Annick van den BroekHi,
Have a look into this article;
http://support.microsoft.com/defaul...kb;en-us;317375
Thanks
Hari
MCDBA
"Annick van den Broek" <anonymous@.discussions.microsoft.com> wrote in
message news:5F6BC6C8-BFCD-4A7C-96A4-753A3F27AF12@.microsoft.com...
> Hello all,
> Al of a sudden I cannot backup my model database.
> When I try to backup the database I get the following message:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'model' is full. Back up the transaction log for
the database to free up some log space.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> Could not write a CHECKPOINT record in database ID 3 because the log is
out of space.
> I have 2 questions:
> (1) To my knowledge the model database has not been changed since the SQL
server installation (+/- 1 year ago), accept a weekly "Optimization". The
model database has recovery model "Simple". How did the log file get filled?
> (2) How do I empty the log file? I have tried to empty the log file via:
> --> DUMP TRANSACTION model WITH NO_LOG
> --> BACKUP LOG model WITH NO_LOG
> However with no success.
> Any suggestion is welcome, thanks,
> Annick van den Broek