Basic Full Text search question

Hi

I am just starting to do a full text search on a sql 2000 database. My question is:

If I want to query a table Employee for 1. a particular word in a very large varchar field named "resume" 2 - a boolean field named "willtransfer" and 3. a field that holds the index of another table named "Cities" Cities just holds an index and cityname field of 15 characters.

do I create a fulltext index for all three fields or only the "resume" field since it is the only field that is being searched? What would a query for these 3 fields look like if I am searching for. I have seen no examples that combine multiple fields.

"speaks French" in resume, "true" for "willtransfer " field, and "Miami" in the cities table (there would need to be an inner join on the cityid field in both Employee and City table.

Thanks for any help on this. This is not the tables I am using, but this seemed like an easy example to explain.

smhaig

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE e.resume LIKE '%FRENCH%'
AND e.willtransfer = True
AND c.CityName = 'MIAMI'

I think maybe you might be looking for something like:

DECLARE @.mystring varchar(50)
SET @.mystring = 'french transfer=true Miami'

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE @.MyString LIKE '%FRENCH%'
AND @.MyString LIKE '%transfer=True%'
AND @.MyString@. = 'Miami'
AND e.resume LIKE '%FRENCH%'
AND e. willtransfer = 'True'
AND c.cityname = 'Miami'

I'm not sure if you want to concatenate all three fields or not.

Please see above

Adamus

|||

You only put a full text index on the 1 field you filter on the other columns using a standard where clause

i.e.

select employeeId

from employee

join city on employee.cityId = cities.cityId

where willtransfer = 1

and cityname = 'Miami'

and contains (employee.resume,'French')

This would match any resume that contains French you might want to include more complex search like "French NEAR speaks"

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
Regards
Johan
Johan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan
|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>
|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||Yes, it goes through 2 Indes Seeks, one for each table.
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>
|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>
|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh
|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>

Bad day with .net

Hi

I am new to ASP.net and trying to connecting with SQL Server in Visual Studio by following instruction are given in below url page.

http://www.codeproject.com/aspnet/SQLConnect.asp?

I have done all steps upto where below code is shown in page

*********************************************************

Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection1.Open()
If SqlConnection1.State = ConnectionState.Open Then
Label4.Text = "SQL Connection is open"
Else
Label4.Text = "SQL Connection is closed"
End If
End Sub

******************************************************

But I am getting error !

Server Error in '/WebService1' Application.

---

Login failed for user 'COMP3\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'COMP3\ASPNET'.

Source Error:

Line 126: 'Put user code to initialize the page hereLine 127:Line 128:SqlConnection1.Open()Line 129:If SqlConnection1.State = ConnectionState.Open ThenLine 130: Label1.Text = "SQL Connection is open"

Source File:c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb Line:128

Stack Trace:

[SqlException: Login failed for user 'COMP3\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 WebService1.SqlConnect.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb:128 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

Note : All steps are finely executed by me !

One thing more As one instruction in Help Page that "right click theSQLConnection1 object to study its properties."

But i am not able to see the properties of it becoz properties tag is disable in right click and i am strange with this experience in Visual Studio that i am not able to edit the code in Editor area ( Where Below code in seen)

Private Sub Page_Load(ByVal sender As System.Object, _ByVal e As .EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

End Sub

So I am totally confused or unable to find out the problem that in my Visual Studio Or Connectivity?

PLEASE HELP ME TO RESOLVE this issue!!

Check your connection string and see if you are providing the correct credentials to access the database.|||

Connection String is automatically generated by Visual Studio.Net aftter dragging table 'employee' from server explorer

So I think it not changeable.

Suggest me other way to connecting with database

|||

See "SQL Server" in this document:How To: Use the Network Service Account to Access Resources in ASP.NET.

In your case, though, you are using the ASPNET account, not NETWORK SERVICE, but all the same steps still apply.

The connection string is still configurable, though.

backward compatibility (2000->2005)

Hi!
I have heard that it's not possible to use (for example) *= as a join in SQL
Server 2005 and now I have tried to find other things that I can't use. Does
anyone know where I can find this?
Thanks =)
//MalinHi
"Breaking Changes to Database Engine Features in SQL Server 2005" in Books
Online.
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Check out Backward Compatibility in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec
-ba920476dd69.htm).
This documents discontinued, deprecated and breaking changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Micheal and Dan,
this was exact what I was looking for =)
Thank you!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Om3dtTn0GHA.2636@.TK2MSFTNGP06.phx.gbl...
> Check out Backward Compatibility in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96
ec-ba920476dd69.htm).
> This documents discontinued, deprecated and breaking changes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Backups with Maintenance Plans

Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJ
Hi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>
|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.
|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

go

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

go

create login companyA default_database 'customersA'

go

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

go

use CustomersA

go

sp_changedbowner 'CustomersA'

go

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

go

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

go

create login companyA default_database 'customersA'

go

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

go

use CustomersA

go

sp_changedbowner 'CustomersA'

go

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar