Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
Thanks in advance,
sck10
Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah
|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>sql

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete Database User w/o Login name

I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:

Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

How do I remove this user?

It seems like your user owns some schema, most likely the default schema that gets created for the user. You will need to use the "DROP SCHEMA" statement to first drop the user's schema (if it is the default schema, the schema's name will most likely be the same as the user's name). Otherwise you can use "ALTER AUTHORIZATION" to change to the owner of the schema to someone else if you want to keep it. After that you should be able to drop the user.

Thanks,

Sung

|||

You can use the following query to help you identify the schema you need to drop.

select schm.name as 'schema_name'

FROM sys.schemas schm, sys.database_principals usrs

WHERE schm.principal_id = usrs.principal_id AND usrs.name = 'dbo'

-Raul Garcia

SDE/T

SQL Server Engine

delete all tables

Hi folk,

whats the SQL syntax of deleting all user tables of a specific database on a Microsoft SQL server?

thanks in advance,

mulata

use yourTable;
go
EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

--"sp_MSforeachtable" is one of the undocumented SQL Server system stored procedures.

|||

hm sorry I don"t understand your solution.

Is there a way that you do that in a "one line SQL statement" ,

something like: "drop table (select * from usertables) "

thanks

|||I agree with limon's solution, which is really straight and easy to use. It will go through each user table in current database and perform same action on each of them.?Since?DROP TABLE command can only be followed bytablename, I don't think you can drop all tables within one single statement except using such stored procedure.

delete all sql database user objects

Good afternoon,

I have a little trouble with sql server 2005 express database:

customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.

I need delete all user objects from this DB, it must be as new created one.

Is any query whitch can do this?

PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.

Thank's for reply.

so u want to drop all objects created by u...u can generate delete scripts(for all user objects in that DB) for all the objects using the management studio...select all the items u need to delete and run the script...|||Hi,

use this script to first drop the constraints of the tables then afterwards drop the tables. If you have any further schemabound objects like views, procedures or functions you will also have to drop them in the right order.

Select

'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +

' DROP CONSTRAINT ' + Constraint_Schema + '.' + Constraint_Name

from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

WHERE OBJECTPROPERTY(OBJECT_ID(Constraint_Schema + '.' + Constraint_Name), 'IsMSShipped') = 0

Select

'DROP TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME

from INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
TmuldWhy not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
--
Regards,
Jamie
"Tmuldoon" wrote:

> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
>|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also
1;it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
Thank you for sharing your knowledge
"TheSQLGuru" wrote:

> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>
>

Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
Tmuld
Why not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
Regards,
Jamie
"Tmuldoon" wrote:

> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>
|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegr oups.com...
>
|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld
|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also [it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
Thank you for sharing your knowledge
"TheSQLGuru" wrote:

> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>
>
sql

Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
TmuldWhy not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
--
Regards,
Jamie
"Tmuldoon" wrote:
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
>> Hello,
>> I need an sql script that will do a 'DELETE FROM <table name>' against
>> are USER tables in a db.
>> It is for a small database that is getting a lot of test data.
>> How would I go about writing this?
>> Thanks,
>> Tmuld
>|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also [it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
--
Thank you for sharing your knowledge
"TheSQLGuru" wrote:
> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> > Hi
> > Take a look at Dan's script
> > DECLARE @.TruncateStatement nvarchar(4000)
> > DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> > FOR
> > SELECT
> > N'TRUNCATE TABLE ' +
> > QUOTENAME(TABLE_SCHEMA) +
> > N'.' +
> > QUOTENAME(TABLE_NAME)
> > FROM
> > INFORMATION_SCHEMA.TABLES
> > WHERE
> > TABLE_TYPE = 'BASE TABLE' AND
> > OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> > N'.' +
> > QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> > OPEN TruncateStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> > IF @.@.FETCH_STATUS <> 0 BREAK
> > RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> > EXEC(@.TruncateStatement)
> > END
> > CLOSE TruncateStatements
> > DEALLOCATE TruncateStatements
> >
> > "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> > news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> >> Hello,
> >>
> >> I need an sql script that will do a 'DELETE FROM <table name>' against
> >> are USER tables in a db.
> >>
> >> It is for a small database that is getting a lot of test data.
> >>
> >> How would I go about writing this?
> >>
> >> Thanks,
> >>
> >> Tmuld
> >>
> >
> >
>
>

Delete a user. Please help

Hi,
I hve been tring to delete a Login User form SQL 2000 and cant do it
I go to Security, Logins, find the user, right click and delete.
I then get a option that says "Removing this login will remove all
associated database users (if any)".
I say yes to that and then...
Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or
more databases- Drop the user or alisas before dropping the login.
Database name :DB1, DB2, DB, etc.
How can I get past this!
Thanks.
George.
Do what the error message say: Remove the users or aliases from the database as listed in the error
message.
If these are users, use sp_dropuser for if it is a SQL Server Login or sp_revokedbaccess if it is a
Windows Login.
If these are aliases (which i think it is, as EM doesn't remove them for you), use sp_dropalias.
The stored procedures I mentioned above are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George D. Lake" <NoSpam@.bla.com> wrote in message news:eJuY4$JJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I hve been tring to delete a Login User form SQL 2000 and cant do it
> I go to Security, Logins, find the user, right click and delete.
> I then get a option that says "Removing this login will remove all associated database users
> (if any)".
> I say yes to that and then...
> Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or more databases- Drop the
> user or alisas before dropping the login.
> Database name :DB1, DB2, DB, etc.
>
> How can I get past this!
>
> Thanks.
> George.
>

Delete a user. Please help

Hi,
I hve been tring to delete a Login User form SQL 2000 and cant do it
I go to Security, Logins, find the user, right click and delete.
I then get a option that says "Removing this login will remove all
associated database users (if any)".
I say yes to that and then...
Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or
more databases- Drop the user or alisas before dropping the login.
Database name :DB1, DB2, DB·, etc.
How can I get past this!
Thanks.
George.Do what the error message say: Remove the users or aliases from the database as listed in the error
message.
If these are users, use sp_dropuser for if it is a SQL Server Login or sp_revokedbaccess if it is a
Windows Login.
If these are aliases (which i think it is, as EM doesn't remove them for you), use sp_dropalias.
The stored procedures I mentioned above are documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George D. Lake" <NoSpam@.bla.com> wrote in message news:eJuY4$JJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I hve been tring to delete a Login User form SQL 2000 and cant do it
> I go to Security, Logins, find the user, right click and delete.
> I then get a option that says "Removing this login will remove all associated database users
> (if any)".
> I say yes to that and then...
> Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or more databases- Drop the
> user or alisas before dropping the login.
> Database name :DB1, DB2, DB·, etc.
>
> How can I get past this!
>
> Thanks.
> George.
>sql

Delete a user. Please help

Hi,
I hve been tring to delete a Login User form SQL 2000 and cant do it
I go to Security, Logins, find the user, right click and delete.
I then get a option that says "Removing this login will remove all
associated database users (if any)".
I say yes to that and then...
Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or
more databases- Drop the user or alisas before dropping the login.
Database name :DB1, DB2, DB, etc.
How can I get past this!
Thanks.
George.Do what the error message say: Remove the users or aliases from the database
as listed in the error
message.
If these are users, use sp_dropuser for if it is a SQL Server Login or sp_re
vokedbaccess if it is a
Windows Login.
If these are aliases (which i think it is, as EM doesn't remove them for you
), use sp_dropalias.
The stored procedures I mentioned above are documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George D. Lake" <NoSpam@.bla.com> wrote in message news:eJuY4$JJFHA.3340@.TK2MSFTNGP14.phx.gb
l...
> Hi,
> I hve been tring to delete a Login User form SQL 2000 and cant do it
> I go to Security, Logins, find the user, right click and delete.
> I then get a option that says "Removing this login will remove all asso
ciated database users
> (if any)".
> I say yes to that and then...
> Error 15174: Login 'XXXXXXX' is aliased or mapped to a user in one or m
ore databases- Drop the
> user or alisas before dropping the login.
> Database name :DB1, DB2, DB, etc.
>
> How can I get past this!
>
> Thanks.
> George.
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:

> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always recevin
g
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
Jac
ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always receving
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>> Hi how we delete a user in a sql table completly
>> I deleted the user but when im trying to create it again im always
>> receving
>> the error that the user already exist
>> Thanks for your help
>> Jac
>>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?You don't have a primary key on that field with a unique constraint? (something like an identity column?)

If you don't have anything to uniquely identify the record, you will have to select one of the records into a temp table, delete the record, then insert the record back. There really isn't any other way to do it.|||UserId has an indentity set, but not in the table that it references to,it is not the primary key.

Could you explain a bit more what would I need to do, as I have only done regular delitions,where things go smooth.

I will attach a file with the error eventhough you probabaly know what it is

Thanks for help

I forgot to tell you that the record I am trying to delete it from is in a view,don't konw if that makes any difference?|||Can you post the table structure of the table you're trying to delete from and the table that's not letting you delete?|||ok,viewSurveySubmission is where the double record is and RelocateeServices will not let me delete it.|||You need to find where the problem is in the base tables and delete the record from there. The view will try to delete from both tables, which it cannot do because another record is still using one of the records from a table you are trying to delete from. Make sense?

In this case find the duplicate in the base table and delete that record.|||Thanks to all willing to help,but I have actually been able to do it myself.

Thanks

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.
Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?
|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David
|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas

"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?
|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan
|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:

>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>
|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.
|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas

"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.
|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David
sql

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:

>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
>> Shabam
>> Read this article
>> http://www.sommarskog.se/dyn-search.html
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:
>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David

Defaulting the User's ID in a column

Hello,

I'm new to SQL Server and I'm wondering how I can default the User's Id in to a column when they create/modify a record.

Better yet, is there a way to get a list of allowable functions for the Default Parm?

thx so much.

Use the system_user.

ALTER TABLE MyTable
ADD ChangeUser varchar(100) DEFAULT system_user

Most of the system functions are available as DEFAULTS. Check Books Online.

|||

if you want to know the database username then system_user will not be the one. sometimes, you can have many database user names mapped to single login. try this and understand.

drop table TestuserDefault

create table TestuserDefault (userid int)

ALTER TABLE TestuserDefault

ADD systemuser varchar(100) DEFAULT system_user

ALTER TABLE TestuserDefault

ADD dbuser varchar(100) DEFAULT current_user

ALTER TABLE TestuserDefault

ADD dbuser1 varchar(100) DEFAULT user

ALTER TABLE TestuserDefault

ADD dbuser2 varchar(100) DEFAULT user_name()

insert into TestuserDefault(userid) select 1

select *from TestuserDefault

Madhu

|||

And of course, if you have multiple users mapped to the same login, there is no way that SQL Server can provide you the User identification information -you will have to do that in the application code, that is, if your application even tracks UserID information.

However, in those situations where IntegratedSecurity is being used, SYSTEM_USER will provide the correct UserName.