Asp.net / sql help: names

Showing posts with label names. Show all posts

Thursday, March 29, 2012

attach detach db

Is it possible detaching one db coping under/ different names /master and lof
files and reattaching db again to cause login problems later. if yes why?Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:
> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:
> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
>> Not sure I understand the question but the answer is most likely in one
>> of
>> these:
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> > Is it possible detaching one db coping under/ different names /master
>> > and
>> > lof
>> > files and reattaching db again to cause login problems later. if yes
>> > why?
>> >
>>|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:
> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> > Hi Hary,
> > thanks for your reply. Let me try to explain the situation I am in. I had
> > to
> > make copy for training purposes of a production db. I detached prod db and
> > copied mdf and ldf in the same data directory but with names test.mdf and
> > test.ldf. Then I attached our prod db and new test db. During attaching I
> > had
> > to choose dbowner (domain admin). It seemed to me that i saved time using
> > this teqniche. But later users reported that they couldnt log in prod and
> > test db via the application (its in citrix). After short phone talk with
> > vendors they explained that detaching and attaching caused dbo alias
> > deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> > 'dbo' and everithing looks fine with prod db. But its impossible to use
> > test
> > db. Users that are in sys admin group dont have problem but ordinary users
> > that connect to applications db via app user cannot login at all. I wonder
> > whats the problem? all ordinary users are stored in users table in prod db
> > and have encrypted passwords. app user password is encrypted and stored in
> > different db.
> >
> > i will be dismissed : )
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Not sure I understand the question but the answer is most likely in one
> >> of
> >> these:
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after a
> >> Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> >> > Is it possible detaching one db coping under/ different names /master
> >> > and
> >> > lof
> >> > files and reattaching db again to cause login problems later. if yes
> >> > why?
> >> >
> >>
> >>
> >>
>
>|||It would be the same for any edition.
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:
>> Then you should look at the links I posted. In particular this first one
>> should be most helpful:
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>> > Hi Hary,
>> > thanks for your reply. Let me try to explain the situation I am in. I
>> > had
>> > to
>> > make copy for training purposes of a production db. I detached prod db
>> > and
>> > copied mdf and ldf in the same data directory but with names test.mdf
>> > and
>> > test.ldf. Then I attached our prod db and new test db. During attaching
>> > I
>> > had
>> > to choose dbowner (domain admin). It seemed to me that i saved time
>> > using
>> > this teqniche. But later users reported that they couldnt log in prod
>> > and
>> > test db via the application (its in citrix). After short phone talk
>> > with
>> > vendors they explained that detaching and attaching caused dbo alias
>> > deleting. They have app login that use dbo alias. I ran sp_addalias
>> > 'app,
>> > 'dbo' and everithing looks fine with prod db. But its impossible to use
>> > test
>> > db. Users that are in sys admin group dont have problem but ordinary
>> > users
>> > that connect to applications db via app user cannot login at all. I
>> > wonder
>> > whats the problem? all ordinary users are stored in users table in prod
>> > db
>> > and have encrypted passwords. app user password is encrypted and stored
>> > in
>> > different db.
>> >
>> > i will be dismissed : )
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Not sure I understand the question but the answer is most likely in
>> >> one
>> >> of
>> >> these:
>> >>
>> >>
>> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> >> http://www.databasejournal.com/features/mssql/article.php/3379901
>> >> Moving
>> >> system DB's
>> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> >> Servers
>> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> >> Databases
>> >> to a New Location with Detach/Attach
>> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in
>> >> a
>> >> Restore
>> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> >> and
>> >> Passwords Between SQL Servers
>> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> >> after a
>> >> Restore
>> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins
>> >> to
>> >> users
>> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> >> Permission
>> >> Errors After Restoring Dump
>> >> http://www.support.microsoft.com/?id=240872 How to Resolve
>> >> Permission
>> >> Issues When a Database Is Moved Between SQL Servers
>> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> >> Restoring a .mdf
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> >> > Is it possible detaching one db coping under/ different names
>> >> > /master
>> >> > and
>> >> > lof
>> >> > files and reattaching db again to cause login problems later. if yes
>> >> > why?
>> >> >
>> >>
>> >>
>> >>
>>sql

Tuesday, March 27, 2012

attach db

I'm moving a db from serverA to serverB. I detached the db on serverA and
copied the files to serverB. I want to change the file names when I attach
it on serverB. How can I do this? If I try to change the name and attach
the db, it doesn't like the renamed files; it will only let me attach as the
original name of the files.
If it's not available in attach, can I rename the file once I attach them
somehow?
Thanks, AndreI figured this out, thanks.
"Andre" <no@.spam.com> wrote in message
news:uvackOWzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> I'm moving a db from serverA to serverB. I detached the db on serverA and
> copied the files to serverB. I want to change the file names when I
attach
> it on serverB. How can I do this? If I try to change the name and attach
> the db, it doesn't like the renamed files; it will only let me attach as
the
> original name of the files.
> If it's not available in attach, can I rename the file once I attach them
> somehow?
>
> Thanks, Andre
>

attach db

Thursday, March 22, 2012

At my wits' end: LIKE

(SQL Server 2005, express edition)

I have a list of table names that I need to translate according to a
naming convention. I'm doing this using pattern matching in a LIKE
clause.

In one specific case I get no match where I believe that there should
be one. I must be missing something obvious here, but what?

I have boiled it down to this example (the real one is more complex):

Matching on the first four characters I get a match:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBAA%';

--
yes

(1 row(s) affected)

That is fine, just as I would have expected. But if I try to match
only on the first 3 characters, I get this:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';

(0 row(s) affected)

I have also tried the same on enterprise edition and get the same
strange result. Language is set to us_english

What am I missing here?

Any help appreciated, before I tear out the very last of my remaining
hair

Bo BrunsgaardHave you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...displaylang=en).
I get the correct results on my SP2 Developer Edition instance:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';

--
yes

(1 row(s) affected)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<bbcworldtour@.hotmail.comwrote in message
news:1176718533.092593.280820@.b75g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

(SQL Server 2005, express edition)
>
I have a list of table names that I need to translate according to a
naming convention. I'm doing this using pattern matching in a LIKE
clause.
>
In one specific case I get no match where I believe that there should
be one. I must be missing something obvious here, but what?
>
I have boiled it down to this example (the real one is more complex):
>
Matching on the first four characters I get a match:
>
select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBAA%';
>
--
yes
>
(1 row(s) affected)
>
>
That is fine, just as I would have expected. But if I try to match
only on the first 3 characters, I get this:
>
select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';
>
(0 row(s) affected)
>
I have also tried the same on enterprise edition and get the same
strange result. Language is set to us_english
>
What am I missing here?
>
Any help appreciated, before I tear out the very last of my remaining
hair
>
Bo Brunsgaard
>

|||On 16 Apr., 13:46, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

Have you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...711d5d-725...).
I get the correct results on my SP2 Developer Edition instance:

I upgraded to SP2, but the problem persisted. It turns out that it is
hidden deep inside the finer points of the database collation. I
thought this was kind of interesting in a low-intense way, so here's
the story:

Our databases are running a collation of Danish_Norwegian_CS_AS (we
are a Danish company).

In Danish we have three special phonemes that are represented in
writing as the letters , and . These three letters are
alphabetically placed as the last three letters of the alphabet.

The last one turns out to the culprit (if it doesn't show up proper
imagine an upper-cased A with a small circle superimposed on it).

Using the letter for the phoneme [] is a fairly recent addition to
Danish (around 1950's). Traditionally it was written as "AA". For
instance, my surname can be written as either "Brunsgrd" or
"Brunsgaard", but is still considered the same name.

So in Danish, "AA" can be either the traditional writing of the
phoneme [] OR just two "A"s which happen to be consecutive.

Danish_Norwegian_CS_AS collation recognizes "AA" as "". This is
usually real neat for sorting. Consider the lastnames "grd" and
"Aagaard" - these should be sorted together at the end of a list, and
using any Danish_Norwegian collation will ensure just that.

Consider:

create table taDanishDemo
(
nameInDanish varchar(30)
collate Danish_Norwegian_CS_AS

, nameInEnglish varchar(30)
collate Latin1_General_CS_AS
)
;

Let us insert a couple of rows which contain a case of consecutive
"A"s:

insert
into taDanishDemo (nameInDanish,nameInEnglish)
select 'TBAAA','TBAAA'
union all
select 'TBABA','TBABA'
;

Retrieving the rows ordered will now yield different results depending
on whether we order on the Danish or the Latin1 collated column:

select nameInEnglish
from taDanishDemo
order by nameInEnglish;

nameInEnglish
----------
TBAAA
TBABA

Under Latin1 collation the "AA" is considered just two concecutive
"A"'s and ordered at the beginning of the list.
But, under Danish collation, the "AA" is considered the traditional
writing of [], and placed at the end of the list:

select nameInDanish
from taDanishDemo
order by nameInDanish;

nameInDanish
----------
TBABA
TBAAA

So far, so good.

What threw me completely is that this also affect how the string "AA"
is interpreted by the LIKE operator.

select nameInDanish
from taDanishDemo
where nameInDanish like 'TBA%'

nameInDanish
----------
TBABA

The row containing "TBAAA" isn't returned Trying to match "AA" with an
"A" plus a wildcard will yield no match under Danish collation, since
SQL Serve interprets this as trying to match "" with "A"!

But under Latin1 collation "AA" does match "A" and a wildcard, as "AA"
is just two "A"'s

select nameInEnglish
from taDanishDemo
where nameInEnglish like 'TBA%'

nameInEnglish
----------
TBAAA
TBABA

I'm still not really sure whether this is a useful feature, an
unintended side effect or a bug :-)

Bo Brunsgaard|||Our databases are running a collation of Danish_Norwegian_CS_AS (we

Quote:

Originally Posted by

are a Danish company).

I'm glad you were able to identify the root cause. I briefly considered a
possible collation issue but didn't think that would explain your symptoms
since I didn't know that collation rules considered consecutive characters.
Thanks a lot for the detailed analysis.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<bbcworldtour@.hotmail.comwrote in message
news:1176883896.875730.239770@.n76g2000hsh.googlegr oups.com...
On 16 Apr., 13:46, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

Have you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...1711d5d-725...).
I get the correct results on my SP2 Developer Edition instance:

Our databases are running a collation of Danish_Norwegian_CS_AS (we
are a Danish company).

In Danish we have three special phonemes that are represented in
writing as the letters , and . These three letters are
alphabetically placed as the last three letters of the alphabet.

The last one turns out to the culprit (if it doesn't show up proper
imagine an upper-cased A with a small circle superimposed on it).

So in Danish, "AA" can be either the traditional writing of the
phoneme [] OR just two "A"s which happen to be consecutive.

Consider:

create table taDanishDemo
(
nameInDanish varchar(30)
collate Danish_Norwegian_CS_AS

, nameInEnglish varchar(30)
collate Latin1_General_CS_AS
)
;

Let us insert a couple of rows which contain a case of consecutive
"A"s:

insert
into taDanishDemo (nameInDanish,nameInEnglish)
select 'TBAAA','TBAAA'
union all
select 'TBABA','TBABA'
;

Retrieving the rows ordered will now yield different results depending
on whether we order on the Danish or the Latin1 collated column:

select nameInEnglish
from taDanishDemo
order by nameInEnglish;

nameInEnglish
----------
TBAAA
TBABA

select nameInDanish
from taDanishDemo
order by nameInDanish;

nameInDanish
----------
TBABA
TBAAA

So far, so good.

What threw me completely is that this also affect how the string "AA"
is interpreted by the LIKE operator.

select nameInDanish
from taDanishDemo
where nameInDanish like 'TBA%'

nameInDanish
----------
TBABA

The row containing "TBAAA" isn't returned Trying to match "AA" with an
"A" plus a wildcard will yield no match under Danish collation, since
SQL Serve interprets this as trying to match "" with "A"!

But under Latin1 collation "AA" does match "A" and a wildcard, as "AA"
is just two "A"'s

select nameInEnglish
from taDanishDemo
where nameInEnglish like 'TBA%'

nameInEnglish
----------
TBAAA
TBABA

I'm still not really sure whether this is a useful feature, an
unintended side effect or a bug :-)

Bo Brunsgaard

Wednesday, March 7, 2012

Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.

Assign ID to distinct names

I have a table called NAMES with 2 columns - id, name. I have a bunch
of names in the table but id is null for all. I need to develop a
script that will assign a unique id to each distinct name.
Before:
ID Name
- Tom
- Tom
- Lee
- Lee
- Lee
- Jim
- Jim
After:
ID Name
1 Tom
1 Tom
2 Lee
2 Lee
2 Lee
3 Jim
3 Jim
DDL:
create table NAMES (
id int null,
name varchar(20) not null
)
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Jim');
insert NAMES values (null, 'Jim');
Any help is appreciated. Thanks."Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>
What's the point of duplicating the names? Try:
CREATE TABLE names2 (
id INTEGER NOT NULL
CONSTRAINT pk_names2 PRIMARY KEY,
name varchar(20) NOT NULL
CONSTRAINT ak1_names2 UNIQUE);
INSERT INTO names2 (id, name)
SELECT COUNT(DISTINCT N2.name), N1.name
FROM names AS N1
JOIN names AS N2
ON N1.name <= N2.name
GROUP BY N1.name ;
Result:
id name
-- --
1 Tom
2 Lee
3 Jim
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. In reality, the table has much more columns and already has an
existing primary key. The ID column described above is not apart of the
pk.
Any other solutions please. I'd like to avoid using cursors.
Thanks.|||Green wrote:
> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of
> the pk.
> Any other solutions please. I'd like to avoid using cursors.
> Thanks.
Get a list of the unique name values and put them in a temp table with
an int idenitity column. Update the original table from the temp table:
Create Table names (id int null, name varchar(50))
go
insert names values (null, 'Tom');
insert names values (null, 'Tom');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Jim');
insert names values (null, 'Jim');
go
Create Table #names (id int identity not null, name varchar(50))
go
insert into #names (
name )
Select distinct name from names
go
select * from #Names
go
Update names
Set names.id = t.id
from #names t
where names.name = t.name
go
select * from names
go
drop table #names
go
David Gugick - SQL Server MVP
Quest Software|||> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of the
> pk.
In that case adding the ID column based on the name would create a
transitive dependency in violation of the standard Normal Forms. Do I take
it that you really want to put name into a related table? Accurate DDL would
be a help here.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In sql 2005 you can do
declare @.NAMES table (
id int null,
name varchar(20) not null
)
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Jim');
insert @.NAMES values (null, 'Jim');
select *
,dense_rank() OVER( order by name)
FROM @.NAMES n1
Result:
id name
-- -- --
NULL Jim 1
NULL Jim 1
NULL Lee 2
NULL Lee 2
NULL Lee 2
NULL Tom 3
NULL Tom 3
farmer
"Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>|||to get exact result like yours
select *
,dense_rank() OVER( order by name desc)
FROM @.NAMES n1
"Farmer" <someone@.somewhere.com> wrote in message
news:%23TiCAqkOGHA.140@.TK2MSFTNGP12.phx.gbl...
> In sql 2005 you can do
> declare @.NAMES table (
> id int null,
> name varchar(20) not null
> )
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Jim');
> insert @.NAMES values (null, 'Jim');
>
> select *
> ,dense_rank() OVER( order by name)
> FROM @.NAMES n1
>
> Result:
> id name
> -- -- --
> NULL Jim 1
> NULL Jim 1
> NULL Lee 2
> NULL Lee 2
> NULL Lee 2
> NULL Tom 3
> NULL Tom 3
>
> farmer
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>|||Thanks all. I used Gugick's suggestion and that worked well. I find
Farmer's solution quite interesting. Maybe I'll try that nexttime.
Thanks.

Thursday, March 29, 2012

attach detach db

attach detach db

attach detach db

Tuesday, March 27, 2012

attach db

attach db

Thursday, March 22, 2012

At my wits' end: LIKE

Wednesday, March 7, 2012

Assign name to the DB users.

Assign ID to distinct names

Asp.net / sql help

Blog Archive

About Me