MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on the web.They will all use the same database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

In common, I am a fan of putting all the data together in one database. based on the information that you provided it is hard to decide wheter you need separated database / instances or not.

There are several things which you should consider:

-Organisational structure (You could also split the database up in regions / geographical or country based)
-Maintainance (Due to different timezones / work loads on the databases you might have different maintainance windows to do something)
-Load balancing
-Scaling
-Availability
-Estimated workload

etc.

I think that we both agree that you wouldn′t put 20000 dbs on on server, or create thousends of instances (the limit is 25 for one computer)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on theweb.They will all use thesame database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

I would choose #1 possibly if it would be important for backup purposes to keep the data "separate". Cross reporting would be difficult, but not totally impossible -- if you can work from a "snapshot". It would be easiest to run a process to pull the data you need from all the agencies into a singular table structure just for the report(s), that has the added OwnerID column. You could do this either as some nightly process, or place a trigger on each of the tables that need to be reported on to replicate the changes to the common table. It would be a completely redundant dataset, so the backup of it isn't all that important, other than the table structure, and any special views/stored procedures/extra tables you may have in there.

This approach also makes it a lot easier to insure that a mistake isn't made whereby another agencies data isn't presented to a different agency. If you have a DBA (Which I'm guessing you do not, or you are the DBA), then they should be able to provide you with views and stored procedures that the application programmers are allowed to use to access the data and hence shouldn't be able to get themselves into trouble.

This type of setup is a bit more complicated to set up, but, it does effectively partition the tables for cross-agency reports.

4. Make one database instance, put a database ownerID(int) field in one (or more) of the tables. ID's are global (across agencies), so that a ownerID is redundant in tables that use an ID (or GUID) as it's primary key that is created in a different table.

|||hmm I am sorry but I did not understand how your 4 th approach differs from the 2 nd approach?|||

First 20,000 insurance agencies is not many, if you take them to be users, it means you have 20,000 users. In the US insurance is divided into Property and Casuality for home and cars policies and Life and Health. So just put them into the type of policy sold or by location and it becomes just 20,000 users. I have told you before you need a DDL(data definition language) expert.

If you don't have it installed check the Pubs sample database for ideas if you decide to use location to separate them because car insurance is sold by state drivers license. Hope this helps.

|||

The 4th approach differs from the 2nd in that if you have say these two tables:

Users->UserID,Name, etc.
Policies->PolicyID(IDENTITY),OwnerID(a UserID),SalesmanID(a UserID),PolicyTypeID, etc.
PolicyRider->PolicyID,RiderTypeID,etc.

In approach 2, the tables would look like this:

Users->AgencyID,UserID,Name, etc.
Policies->AgencyID,PolicyID,OwnerID,SalesmanID,PolicyTypeID,etc
PolicyRider->AgencyID,PolicyID,RiderTypeID,etc.

Where in approach 4, the tables would look like this if you allow any user to belong to multiple Agencies:

Users->UserID,Name,etc
UserAgencies->UserID,AgencyID
Policies->AgencyID,PolicyID, ...
PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which already contains a AgencyID)

Or like this, if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)
PolicyRider->PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which has a reference to a Users table entry which contains a AgencyID)

You may want to attach the AgencyID to the Policy instead of the User, in these cases, but the idea is that so long as the "parent" of any relationship already has an AgencyID, the children do not, since you can imply their AgencyID by referencing their (singular) parent's AgencyID.

|||

if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I have read for 4 times.But did not understand how will I know that a record is owned by that AGENCY or by that USER.

maybe it is too late and I am really tired...is there anyuserID on the Policies table?

PS:I really do wanna implement a simple accountability pattern for the user system but performant.So the users can be in agencies but I will define that by simple attributeTypes in database not using the relations...

|||Sorry, yes. I was assuming that the Policies table would also have a OwnerID field (that's a UserID) or an AgentID (also a UserID), or some other field that you would use that to tie the record back to a particular user which is then tied to an AgencyID.

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Multiple users of a website using aspnetdb.mdf database

I don't know whether this question belong here, but...

In the past I had many customers using the same website each with their own domain name and their own database. To accomplish that, I used MS Access and placed each customers database in separate directories. I steered the customer to the correct directory using their domain name. I am now trying to accomplish the same thing using aspnetdb.mdf and other .mdf database files. I am using the aspnetdb.mdf database for membership, etc. I want the aspnetdb.mdf database file to be unique for each customer. Does anyone know how to accomplish what I was doing with MS Access using aspnetdb.mdf? Is there any way to have multiple web.config files each of which would be dedicated to a particular customer?

I am not sure if you can keep the same website, but the obvious way is to create duplicated websites & for each one you set (in web.config) a different configuration database. something like:

<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=aspnetdb1;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

where you have createdaspnetdb1before and configured it usingaspnet_regsql.exe

Multiple threads

Is it safe to use one database connection shared across multiple threads. Each thread has its own set of data i.e its own SQL command etc, but sharing a single database connection. Actually the scenario is that in a web site I want to open a single database connection when web application starts up. On each page hit will construct a SQL command and query/update the database.

I just want to know if multiple requests come at the same time will threads be blocked if I share global connection. What will happen if one request is trying to retrieve the data and another request is updating the database using the same connection?

Thanks

Hi!

I don't think it's a good idea to share DB connection across threads. One of them may open it, another may close, another may break... So many things to keep in mind.

DB connections are fast for open/close and if you make many of them, internally there is pool of connections, so framework will optimize access. If you do not have proven performance drop because of frequent connections opening, then don't waste your time with it - create, open and close connections as logic requires.

One more thing - if you have local connections, then your code is less dependent and can be better maintained, supported, evolved.

|||

Let us imagine a situation that thousands of requests come to web site at a particular time then if I open connection for every request then even the connection pool will be exausted. Second thing I want to clarify that thread does not know if the connection is shared or not so thread will never end up closing the connection. Thread will talk to some kind of manager which will keep connection internally and leave it open for every request.

I still don't know how the thread behaviour will be. If all the requests use the same connection then whether each request will be serialized (running one after previous one is finished) or each can run in parallel.

Any idea?

Thanks

|||

I will agree about thousands and speed problems when I will see some real numbers. So, you can try to simulate test load and watch how the system performs.

Also I want to notice, that connection is logical, it have state, which can be not only Open or Close, but also Executing, Fetching, Broken. What if one of your threads do fetching? All others must wait until it finish. And If you have 2 connections, then you can install 2 network adapters and they can split job. So I still think global connection is not a good thing to do because of scalability & reliability reasons.

Don't think of connections as of physical connections, you do not connect to some port in fact, instead you connect to client side layer, which will take care of real connection. You work with logical connections.

|||Thanks for the suggestions. I will see how it goes.

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

Multiple tasks in the same assembly

Hello,
All the SSIS tasks (and components come to mention it) exist in their own assembly file.

Is there anything that stops us from putting multiple tasks into one assembly?
Assuming you can do that, is there a reason why this perhaps should not be done?

Thanks
JamieActually, the percent sampling and row sampling transforms are in the same DLL.

There is nothing to stop you from having multiple tasks or multiple components in the same DLL. I can't even think of a reason why you couldn't have both a task and a component in the same DLL. In general, the reason why you wouldn't do that is ease of fix and/or upgrade. If you put 10 transforms in one DLL then if you only changed one you still have to "update" all the others because the DLL has changed. If they are all in separate DLLs then you only update the one that changed (i.e. instead of having to update a 5MB DLL you only have to update a 20KB DLL).

HTH,
Matt|||

Matt David wrote:

Actually, the percent sampling and row sampling transforms are in the same DLL.

There is nothing to stop you from having multiple tasks or multiple components in the same DLL. I can't even think of a reason why you couldn't have both a task and a component in the same DLL. In general, the reason why you wouldn't do that is ease of fix and/or upgrade. If you put 10 transforms in one DLL then if you only changed one you still have to "update" all the others because the DLL has changed. If they are all in separate DLLs then you only update the one that changed (i.e. instead of having to update a 5MB DLL you only have to update a 20KB DLL).

HTH,
Matt

Thanks Matt,

I thought it was a requirement that the DLLs for Tasks went in the ..\Tasks folder and the DLLs for components went in the ..\PipelineComponents folder. How could you do that if they were in the same DLL?

-Jamie|||

Jamie Thomson wrote:

Thanks Matt,

I thought it was a requirement that the DLLs for Tasks went in the ..\Tasks folder and the DLLs for components went in the ..\PipelineComponents folder. How could you do that if they were in the same DLL?

-Jamie

Yes, the same DLL would need to go to both of these places (plus Global Assembly Cache). Still, you build just one DLL.

Michael.|||I was working through this and have decided to go for the multiple assembly approach, because of the deployment and install ease.|||

You may want to consider going one step further and separating Task/Component and User Interface into 2 distinct assemblies as well. This allows you to modify one without modifying the other, although the deployment benefit is a bit less obvious in this case.

In the main Task/Component class, the UITypeName property in the Attribute links your main assembly to the UI assembly.

-Doug

|||

One benefit of separating the UI from the task is that you don't have UI code on the server for security reasons. (Easier to lock down).
The reason we did this in the beginning was to make the task dlls smaller so that if we ever shipped something like MSDE (which has no UI), we could ship the tasks without the UI to make the distribution smaller (for web release etc.)
This might be useful for those writing custom tasks as well, though I can't think of when you might wish to ship a task without the UI.
K

multiple search items in one sql string?

hello, sql newbie here.
I have 3 tables(user, education, career), each have their own catalog.
Say I want to search for a person who lives in a certain city with a
certain education and some prior job, where the category equals some
int.
sql:
SELECT DISTINCT * FROM User
INNER JOIN Education ON User.ID = Education.UserID
INNER JOIN Career ON User.ID = Career.UserID
INNER JOIN Category ON User.ID = Category.UserID
WHERE CONTAINS(User.*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
This doesn't work.
How can I search for multiple items across several columns and tables?
You might want to try this
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MB" <mbje@.aats.dk> wrote in message
news:9b3ddf86.0507040156.620273fc@.posting.google.c om...
> hello, sql newbie here.
> I have 3 tables(user, education, career), each have their own catalog.
> Say I want to search for a person who lives in a certain city with a
> certain education and some prior job, where the category equals some
> int.
> sql:
> SELECT DISTINCT * FROM User
> INNER JOIN Education ON User.ID = Education.UserID
> INNER JOIN Career ON User.ID = Career.UserID
> INNER JOIN Category ON User.ID = Category.UserID
> WHERE CONTAINS(User.*,'new york')
> AND CONTAINS(Education.*,'programming')
> AND CONTAINS(Career.*,'assistant')
> AND Category.selectedID = 4
> This doesn't work.
> How can I search for multiple items across several columns and tables?
|||thanks for the reply, I tried it, and it worked, the problem is that I
asked the question wrong. Heres what I really wanted to ask:
It seems like User table allows more than one contains
but education and career tables do not.
if I query like so:
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Education.*,'networking')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
there are no rows returned. If I omit one of the education table
contains, it works.
*** Sent via Developersdex http://www.codecomments.com ***
|||This might work for you
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,"'programming' or 'networking'")
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
as long as programming and or networking were in the same column, otherwise
try
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND (CONTAINS(Education.*,"'programming'") or
CONTAINS(Education.*,"'networking"' ))
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Bjerregaard" <mbje@.aats.dk> wrote in message
news:%235H3duSgFHA.272@.TK2MSFTNGP15.phx.gbl...
> thanks for the reply, I tried it, and it worked, the problem is that I
> asked the question wrong. Heres what I really wanted to ask:
> It seems like User table allows more than one contains
> but education and career tables do not.
> if I query like so:
> SELECT DISTINCT * FROM [User]
> INNER JOIN Education ON [User].ID = Education.UserID
> INNER JOIN Career ON [User].ID = Career.UserID
> INNER JOIN Category ON [User].ID = Category.UserID
> WHERE CONTAINS([User].*,'new york')
> AND CONTAINS(Education.*,'programming')
> AND CONTAINS(Education.*,'networking')
> AND CONTAINS(Career.*,'assistant')
> AND Category.selectedID = 4
> there are no rows returned. If I omit one of the education table
> contains, it works.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

Multiple RS Instances

Can i have multiple Report Server 05 instances on one machine conneting to
one remote Sql Database instance. Each Report Server will have its own
database in the SQL instanceOn Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> Can i have multiple Report Server 05 instances on one machine conneting to
> one remote Sql Database instance. Each Report Server will have its own
> database in the SQL instance
This link should be helpful.
http://technet.microsoft.com/en-us/library/ms403426.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||i read through the documentatation, but its not clear to me if 2 RS 05
standard ed on one machine can share the same database server but separate
databases. Unless i'm misunderstading it... any ideas?
"EMartinez" wrote:
> On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > Can i have multiple Report Server 05 instances on one machine conneting to
> > one remote Sql Database instance. Each Report Server will have its own
> > database in the SQL instance
>
> This link should be helpful.
> http://technet.microsoft.com/en-us/library/ms403426.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> i read through the documentatation, but its not clear to me if 2 RS 05
> standard ed on one machine can share the same database server but separate
> databases. Unless i'm misunderstading it... any ideas?
> "EMartinez" wrote:
> > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > > Can i have multiple Report Server 05 instances on one machine conneting to
> > > one remote Sql Database instance. Each Report Server will have its own
> > > database in the SQL instance
> > This link should be helpful.
> >http://technet.microsoft.com/en-us/library/ms403426.aspx
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
As far as I know, this should be fine as long as your virtual
directories for Reports and ReportServer are physically separate for
both SQL instances. Also, you will want to make sure that you have at
least one default SQL Server Instance and one named SQL Server
Instance local to SSRS. As long as your SQL Servers are not mixed
(i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
Regards,
Enrique Martinez
Sr. Software Consultant|||So am I understanding you correctly that each RS (standard ed) instance will
need to have their own SQL Server Database Instance?
Here is what my environment looks like:
WebServer has RSSite1(default) AND RSSite2(Named)
DatabaseServer has one Instance Of Sql Server called DBServ
DBServ has four databases:
RSSite1_ReportServer
RSSite1_ReportServerTempDb
RSSite2_ReportServer
RSSite2_ReportServerTempDb
I'm doing this right now, but i'm running into some issues. Just want to
know if this is supported so i can stop troubleshooting this.
Thanks
"EMartinez" wrote:
> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > i read through the documentatation, but its not clear to me if 2 RS 05
> > standard ed on one machine can share the same database server but separate
> > databases. Unless i'm misunderstading it... any ideas?
> >
> > "EMartinez" wrote:
> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > > > Can i have multiple Report Server 05 instances on one machine conneting to
> > > > one remote Sql Database instance. Each Report Server will have its own
> > > > database in the SQL instance
> >
> > > This link should be helpful.
> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> As far as I know, this should be fine as long as your virtual
> directories for Reports and ReportServer are physically separate for
> both SQL instances. Also, you will want to make sure that you have at
> least one default SQL Server Instance and one named SQL Server
> Instance local to SSRS. As long as your SQL Servers are not mixed
> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||They need their own databases, not their own instance. RS 2005 should have
no problem with this at all.
From the article that Enrique provided a link to:"You can install multiple
instances of Reporting Services on the same computer. Installing multiple
instances is useful if you want to host different content for specific
sites. Each instance will have its own report server database, configuration
files, and virtual directories. "
Note it says report server database NOT instance.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> So am I understanding you correctly that each RS (standard ed) instance
> will
> need to have their own SQL Server Database Instance?
> Here is what my environment looks like:
> WebServer has RSSite1(default) AND RSSite2(Named)
> DatabaseServer has one Instance Of Sql Server called DBServ
> DBServ has four databases:
> RSSite1_ReportServer
> RSSite1_ReportServerTempDb
> RSSite2_ReportServer
> RSSite2_ReportServerTempDb
> I'm doing this right now, but i'm running into some issues. Just want to
> know if this is supported so i can stop troubleshooting this.
> Thanks
> "EMartinez" wrote:
>> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> > i read through the documentatation, but its not clear to me if 2 RS 05
>> > standard ed on one machine can share the same database server but
>> > separate
>> > databases. Unless i'm misunderstading it... any ideas?
>> >
>> > "EMartinez" wrote:
>> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> > > > Can i have multiple Report Server 05 instances on one machine
>> > > > conneting to
>> > > > one remote Sql Database instance. Each Report Server will have its
>> > > > own
>> > > > database in the SQL instance
>> >
>> > > This link should be helpful.
>> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
>> >
>> > > Regards,
>> >
>> > > Enrique Martinez
>> > > Sr. Software Consultant
>>
>> As far as I know, this should be fine as long as your virtual
>> directories for Reports and ReportServer are physically separate for
>> both SQL instances. Also, you will want to make sure that you have at
>> least one default SQL Server Instance and one named SQL Server
>> Instance local to SSRS. As long as your SQL Servers are not mixed
>> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Thank You for the clarification...I've been trying that configuration with no
luck. I'll look more into it then. Thanks.
"Marvin" wrote:
> Can i have multiple Report Server 05 instances on one machine conneting to
> one remote Sql Database instance. Each Report Server will have its own
> database in the SQL instance|||Thank you for clarifying...so here's my issue.
I installed a default instance of Report Services Std on a Non-Default
Website with Sql Server on a remote machine. I implemented custom security.
No problems so far. I am able to log-in using Forms Auth and add a Folder.
Then I would install a named instance on the same webserver. Without even
touching it after install (choosing to configure later), the DEFAULT instance
returns a "Bad Request" error when trying to log in. I planned to set up the
named instance on another Website.
Any Ideas?
"Bruce L-C [MVP]" wrote:
> They need their own databases, not their own instance. RS 2005 should have
> no problem with this at all.
> From the article that Enrique provided a link to:"You can install multiple
> instances of Reporting Services on the same computer. Installing multiple
> instances is useful if you want to host different content for specific
> sites. Each instance will have its own report server database, configuration
> files, and virtual directories. "
> Note it says report server database NOT instance.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> > So am I understanding you correctly that each RS (standard ed) instance
> > will
> > need to have their own SQL Server Database Instance?
> >
> > Here is what my environment looks like:
> > WebServer has RSSite1(default) AND RSSite2(Named)
> > DatabaseServer has one Instance Of Sql Server called DBServ
> > DBServ has four databases:
> > RSSite1_ReportServer
> > RSSite1_ReportServerTempDb
> > RSSite2_ReportServer
> > RSSite2_ReportServerTempDb
> >
> > I'm doing this right now, but i'm running into some issues. Just want to
> > know if this is supported so i can stop troubleshooting this.
> >
> > Thanks
> >
> > "EMartinez" wrote:
> >
> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> > i read through the documentatation, but its not clear to me if 2 RS 05
> >> > standard ed on one machine can share the same database server but
> >> > separate
> >> > databases. Unless i'm misunderstading it... any ideas?
> >> >
> >> > "EMartinez" wrote:
> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> > > > Can i have multiple Report Server 05 instances on one machine
> >> > > > conneting to
> >> > > > one remote Sql Database instance. Each Report Server will have its
> >> > > > own
> >> > > > database in the SQL instance
> >> >
> >> > > This link should be helpful.
> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >> >
> >> > > Regards,
> >> >
> >> > > Enrique Martinez
> >> > > Sr. Software Consultant
> >>
> >>
> >> As far as I know, this should be fine as long as your virtual
> >> directories for Reports and ReportServer are physically separate for
> >> both SQL instances. Also, you will want to make sure that you have at
> >> least one default SQL Server Instance and one named SQL Server
> >> Instance local to SSRS. As long as your SQL Servers are not mixed
> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||Did you use different databases for the second install of RS?
Have you checked to see what the default website is? Could it have changed
to the second installation of RS?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:7CA5523E-B6D7-4524-A3C2-DA30939B3F2B@.microsoft.com...
> Thank you for clarifying...so here's my issue.
> I installed a default instance of Report Services Std on a Non-Default
> Website with Sql Server on a remote machine. I implemented custom
> security.
> No problems so far. I am able to log-in using Forms Auth and add a Folder.
> Then I would install a named instance on the same webserver. Without even
> touching it after install (choosing to configure later), the DEFAULT
> instance
> returns a "Bad Request" error when trying to log in. I planned to set up
> the
> named instance on another Website.
> Any Ideas?
> "Bruce L-C [MVP]" wrote:
>> They need their own databases, not their own instance. RS 2005 should
>> have
>> no problem with this at all.
>> From the article that Enrique provided a link to:"You can install
>> multiple
>> instances of Reporting Services on the same computer. Installing multiple
>> instances is useful if you want to host different content for specific
>> sites. Each instance will have its own report server database,
>> configuration
>> files, and virtual directories. "
>> Note it says report server database NOT instance.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
>> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
>> > So am I understanding you correctly that each RS (standard ed) instance
>> > will
>> > need to have their own SQL Server Database Instance?
>> >
>> > Here is what my environment looks like:
>> > WebServer has RSSite1(default) AND RSSite2(Named)
>> > DatabaseServer has one Instance Of Sql Server called DBServ
>> > DBServ has four databases:
>> > RSSite1_ReportServer
>> > RSSite1_ReportServerTempDb
>> > RSSite2_ReportServer
>> > RSSite2_ReportServerTempDb
>> >
>> > I'm doing this right now, but i'm running into some issues. Just want
>> > to
>> > know if this is supported so i can stop troubleshooting this.
>> >
>> > Thanks
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> >> > i read through the documentatation, but its not clear to me if 2 RS
>> >> > 05
>> >> > standard ed on one machine can share the same database server but
>> >> > separate
>> >> > databases. Unless i'm misunderstading it... any ideas?
>> >> >
>> >> > "EMartinez" wrote:
>> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com>
>> >> > > wrote:
>> >> > > > Can i have multiple Report Server 05 instances on one machine
>> >> > > > conneting to
>> >> > > > one remote Sql Database instance. Each Report Server will have
>> >> > > > its
>> >> > > > own
>> >> > > > database in the SQL instance
>> >> >
>> >> > > This link should be helpful.
>> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
>> >> >
>> >> > > Regards,
>> >> >
>> >> > > Enrique Martinez
>> >> > > Sr. Software Consultant
>> >>
>> >>
>> >> As far as I know, this should be fine as long as your virtual
>> >> directories for Reports and ReportServer are physically separate for
>> >> both SQL instances. Also, you will want to make sure that you have at
>> >> least one default SQL Server Instance and one named SQL Server
>> >> Instance local to SSRS. As long as your SQL Servers are not mixed
>> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||i was able to figure out my problem...i used the custom security extention
sample and during login it was returning the wrong url from one of the
methods (using WMI for some reason to get the url). i just overrid that and
got the correct value from web.config. all is well. thank you for taking the
time to help me.
"Bruce L-C [MVP]" wrote:
> Did you use different databases for the second install of RS?
> Have you checked to see what the default website is? Could it have changed
> to the second installation of RS?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> news:7CA5523E-B6D7-4524-A3C2-DA30939B3F2B@.microsoft.com...
> > Thank you for clarifying...so here's my issue.
> > I installed a default instance of Report Services Std on a Non-Default
> > Website with Sql Server on a remote machine. I implemented custom
> > security.
> > No problems so far. I am able to log-in using Forms Auth and add a Folder.
> >
> > Then I would install a named instance on the same webserver. Without even
> > touching it after install (choosing to configure later), the DEFAULT
> > instance
> > returns a "Bad Request" error when trying to log in. I planned to set up
> > the
> > named instance on another Website.
> >
> > Any Ideas?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> They need their own databases, not their own instance. RS 2005 should
> >> have
> >> no problem with this at all.
> >> From the article that Enrique provided a link to:"You can install
> >> multiple
> >> instances of Reporting Services on the same computer. Installing multiple
> >> instances is useful if you want to host different content for specific
> >> sites. Each instance will have its own report server database,
> >> configuration
> >> files, and virtual directories. "
> >>
> >> Note it says report server database NOT instance.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> >> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> >> > So am I understanding you correctly that each RS (standard ed) instance
> >> > will
> >> > need to have their own SQL Server Database Instance?
> >> >
> >> > Here is what my environment looks like:
> >> > WebServer has RSSite1(default) AND RSSite2(Named)
> >> > DatabaseServer has one Instance Of Sql Server called DBServ
> >> > DBServ has four databases:
> >> > RSSite1_ReportServer
> >> > RSSite1_ReportServerTempDb
> >> > RSSite2_ReportServer
> >> > RSSite2_ReportServerTempDb
> >> >
> >> > I'm doing this right now, but i'm running into some issues. Just want
> >> > to
> >> > know if this is supported so i can stop troubleshooting this.
> >> >
> >> > Thanks
> >> >
> >> > "EMartinez" wrote:
> >> >
> >> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> >> > i read through the documentatation, but its not clear to me if 2 RS
> >> >> > 05
> >> >> > standard ed on one machine can share the same database server but
> >> >> > separate
> >> >> > databases. Unless i'm misunderstading it... any ideas?
> >> >> >
> >> >> > "EMartinez" wrote:
> >> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com>
> >> >> > > wrote:
> >> >> > > > Can i have multiple Report Server 05 instances on one machine
> >> >> > > > conneting to
> >> >> > > > one remote Sql Database instance. Each Report Server will have
> >> >> > > > its
> >> >> > > > own
> >> >> > > > database in the SQL instance
> >> >> >
> >> >> > > This link should be helpful.
> >> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >> >> >
> >> >> > > Regards,
> >> >> >
> >> >> > > Enrique Martinez
> >> >> > > Sr. Software Consultant
> >> >>
> >> >>
> >> >> As far as I know, this should be fine as long as your virtual
> >> >> directories for Reports and ReportServer are physically separate for
> >> >> both SQL instances. Also, you will want to make sure that you have at
> >> >> least one default SQL Server Instance and one named SQL Server
> >> >> Instance local to SSRS. As long as your SQL Servers are not mixed
> >> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> >> >>
> >> >> Regards,
> >> >>
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant
> >> >>
> >> >>
> >>
> >>
> >>
>
>