after set new_broker, it works for half hour and don;t work anymore, please advise, thanks!

I use ASP.NET 2.0 and SQL 2005, the SQLCacheDependency didn't work stable for me, it usually works and after a while, it stops working. Recently, It doesn't work. Today I reset service broker by

Alter DB set new_broker

It seems all work, but too early to be happy, it doesn't work now anymore. I don't see any record in

sys.transmission_queue

And I don't see new entries in

sys.dm_qn_subscriptions neither when I modify a record which bind to SQLCacheDependy.

I see some errors in log like

Message
The query notification dialog on conversation handle '{8F8CC642-6340-DB11-8F09-0014227B7B80}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-f79776f7-9ca5-4c5f-8a66-0d81f7673683&apos; because it does not exist.</Description></Error>'.

Any idea how to find out the problem?

thanks!

Have you applied this patch: http://support.microsoft.com/kb/913364/EN-US/ ?

HTH,
~ Remus

Advise: DTS

Version: SQL Server 7.0
App: MS Access 2000

I created DTS to move records from MySQL to SQL Server (approx over 500,000+
per months dataset) for purpose of canned and adhoc reporting. The app
have a simple interface using MS Access where user will input the timeframe
of the dataset they will process.

Both servers are physically located in the same bldg from another state.
Previous to MySQL, they send us cd in text format and my app runs the DTS
using a BAT file containing the DTSRUN command. But I would like stay away
from the DTSRUN since they take awhile to load to the server, plus it runs
on background on client pc. I wanted a functionality where it will run on
the server without user supervision, and they can go about their business
and do something else. In other words, let the server do majority of the
work.

Now that MySQL came into the picture, the Network Support created a filedsn
where sqlserver sits and I use that as a reference on my DTS (using UNC,
\\myserver\file$\sample.dsn)

The problem:
1) I work from home and when creating and running this DTS's, it takes a
while to process;
2) When specifying the UNC path on DTS and saved, for some reason it
switches to the User/System DSN section;
3) I also tested from office with the same result (i connect using cable
modem)

Now the only way I can think of to solve this problem:
a) save the DTS as a file and send it over to Network Support and have them
load it up and manually change the source location OR have them modify the
DTS and change the source location
b) then create a DTS job

The person who will be running this DTS might be running it from home or
office. Also, is there a way to automate the DTS job using an SP? I wanted
to make it as easier as possible for the end user, where all they have to do
is enable the job and run only for this time and for this package.

TIA!
BobHi

You can schedule the DTS package to run periodically on your SQL Server.
Look at sp_add_job and associated procedures as descibed in the topic "How
to create a job (Transact-SQL)" in Books online or the topic "How to create
a job (Enterprise Manager)" to do it manually through EM.

If you don't want run a job without a schedule look at the "Running Jobs"
topic for sp_start_job.

John

"B" <no_spam@.no_spam.com> wrote in message
news:415580f5$0$4029$61fed72c@.news.rcn.com...
> Version: SQL Server 7.0
> App: MS Access 2000
> I created DTS to move records from MySQL to SQL Server (approx over
500,000+
> per months dataset) for purpose of canned and adhoc reporting. The app
> have a simple interface using MS Access where user will input the
timeframe
> of the dataset they will process.
> Both servers are physically located in the same bldg from another state.
> Previous to MySQL, they send us cd in text format and my app runs the DTS
> using a BAT file containing the DTSRUN command. But I would like stay
away
> from the DTSRUN since they take awhile to load to the server, plus it runs
> on background on client pc. I wanted a functionality where it will run on
> the server without user supervision, and they can go about their business
> and do something else. In other words, let the server do majority of the
> work.
> Now that MySQL came into the picture, the Network Support created a
filedsn
> where sqlserver sits and I use that as a reference on my DTS (using UNC,
> \\myserver\file$\sample.dsn)
> The problem:
> 1) I work from home and when creating and running this DTS's, it takes a
> while to process;
> 2) When specifying the UNC path on DTS and saved, for some reason it
> switches to the User/System DSN section;
> 3) I also tested from office with the same result (i connect using cable
> modem)
> Now the only way I can think of to solve this problem:
> a) save the DTS as a file and send it over to Network Support and have
them
> load it up and manually change the source location OR have them modify the
> DTS and change the source location
> b) then create a DTS job
> The person who will be running this DTS might be running it from home or
> office. Also, is there a way to automate the DTS job using an SP? I
wanted
> to make it as easier as possible for the end user, where all they have to
do
> is enable the job and run only for this time and for this package.
> TIA!
> Bob
>

Advise Please

hello
I have two related table

table1
cId
cDesc

table2
Id
Name
phone
cId

These two tables are related
where cId in table 1 is primary key and cId in table2 is foreign key

If I want to delete a record in table 1 which is related to table 2
Which is faster and more accurate

should I write my stored procedure like this
if exists(select * from table2 where cID = @.CID)
delete from table1 where cID = @.CID
else
return 0
-------------
or
-------------
delete from table1 where cID = @.CID

if @.@.error<>0
return 0

Neither of the above will work if you have a primary-foriegn key relationship as it would break the relationship.

Are you wanting to delete the record from table1 and any related records from table2, or do you want to delete from table1 only if there are no references to it in table2?

|||

wel i gues option 2 is faster but first u need to delete it from table 2 otherwise it gives u error

|||

Hi,

I recently ran into such a case and used the following code:
DELETE FROM TABLE2 WHERE id = @.ID
DELETE FROM TABLE1 WHERE id = @.ID
this worked perfectly for me.
But there is other way to achieve your goal:
just define the following foreign key on table2:
FOREIGN KEY (Id) REFERENCES table1 (cID) ON DELETE CASCADE

I hope this heps

|||

The second one

|||

I dont want to delete child if it exists...

Which the better way referenced to my first post

|||

according to my knowledge u need yo delete the refrence from child table i.e. foreign key relation ......

may be im wrong do let me know

|||

It seems that there is a big misunderstanding,

wht I am trying to do is :

check if the parent table has a child, if so message the user u cannot delete (The parent Record)

if the parent does not have a child delete the record..(The parent Record)

So I mentioned in my first post 2 options to do that and I was wondering which is the better and the faster

I hope I made my idea clear now
Thank you

|||

The 2nd option is indeed faster and better depending upon how many rows are in your 2nd table.

|||But is it apropriate to coz sql an error??|||

As long as your trapping the error and resetting the error object I dont see any problems with it. Just be sure you have the FK constraints on your second table otherwise this method will not work.

|||

well now v get the xact n accurate situation dat wht u need n wht u r doing...wel i gues u r on the rite track n again the 2nd option is beter wen eva u get error from sql u can catch it in try catch block n after verifying that its a FK constraint error u can simply display that child record exists.....

|||

Are there any disadvantages using catch block statments??

|||

Are there any disadvantages using "try catch" block statments??

|||

Using Try Catch block statements are highly advisable when you know that there is a possablity of an exception being thrown. Good error handling is always a good idea in any application.

Advise on querying a database daily

I am implementing an application that is looking into another System's database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.

What are my choices?

Thanks,

JasonWindows Scheduled Task is my preference. You could use SQL Tasks as well, I just don't like them.|||Lets say I do use the Windows Scheduled Task what type of project would it be since it does not have an interface?|||I usually just use VBS and then a COM callable .net component. The nice thing about SQL Tasks is that it will just run SQL.|||I have a question about SQL Tasks. I am running MSDE and I do have SQL Server Agent. I am using an Access project (adp) to manage my database. I do not see a utility to take advantage of SQL Server Agent/scheduling jobs. Do you have any advice on what I could use to generate the T-SQL so I can use SQL Server Agent?

Jason|||Sorry no idea, I'm an Enterprise Server user.

Advise on Multiple Column Updates

Using SQL2000, is there another way of optimizing the original query below.
TIA,
Bob

Update Transaction
set field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder where
tblOrder.id = Transaction.id),
field2 = (select avg(price) from tblOrder where tblOrder.id =
Transaction.id),
field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)
etc..
(there are six additional updates)

Is this a quicker way:

Update Transaction
SET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,
field3 = min(cost) , etc....
FROM tblOrder
WHERE tblOrder.id = Transaction.idThe format is:
UPDATE <table> SET (<columnlist>) = (SELECT <select_list> FROM ...)
WHERE ...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Advise on Licensing SSRS on NLB IIS farm

Hi All,
I have the following question, in my organization we employ a policy of
splitting out our enterprise infrastructure, IIS separated from SQL Server
etc.
We currently have a number of SQL Server 2000 and 2005 clusters and also two
NLB IIS farms (each with 4 nodes).
If I install SQL Server Reporting Services on the NLB farms (databases on
two of the SQL Server Clusters) to make this product available to to people
here then am I right in assuming that firstly I will need Enterprise Edition
of Reporting Services 2005 installed on my NLB's as this is the only edition
capable of scale out SSRS deployment.
Secondly and more importantly will I also need in addition a separate SQL
Server 2005 Enterprise Edition license on each of the nodes involved in the
NLB's (8 nodes therefore 8 licenses)?
If this is the case then I am really concerned at this supposed low cost
reporting solution from Microsoft, as I would then for this scenario need
minimum 10 Enterprise Edition SQL Server 2005 licenses (8 for the NLB nodes
and 1 for each failover cluster (Active/Passive) )
This is no longer a cheap solution for our reporting needs.
Thanks for your help.
Kind Regards,
DesYou have the licensing correct. Enterprise is the only one which supports a
web farm and any server that has Reporting Services installed must have a
server license. How expensive it is for you depends on how you license (CAL
or per processor).
If you price Crystal in such a configuration you will find that it is even
more expensive.
Unless you have one heck of a lot of reports and users then I sincerely
doubt that you need such a configuration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Des FitzGerald" <dfitz@.dr-sql.com> wrote in message
news:C3A94D89.73DF%dfitz@.dr-sql.com...
> Hi All,
> I have the following question, in my organization we employ a policy of
> splitting out our enterprise infrastructure, IIS separated from SQL Server
> etc.
> We currently have a number of SQL Server 2000 and 2005 clusters and also
> two
> NLB IIS farms (each with 4 nodes).
> If I install SQL Server Reporting Services on the NLB farms (databases on
> two of the SQL Server Clusters) to make this product available to to
> people
> here then am I right in assuming that firstly I will need Enterprise
> Edition
> of Reporting Services 2005 installed on my NLB's as this is the only
> edition
> capable of scale out SSRS deployment.
> Secondly and more importantly will I also need in addition a separate SQL
> Server 2005 Enterprise Edition license on each of the nodes involved in
> the
> NLB's (8 nodes therefore 8 licenses)?
> If this is the case then I am really concerned at this supposed low cost
> reporting solution from Microsoft, as I would then for this scenario need
> minimum 10 Enterprise Edition SQL Server 2005 licenses (8 for the NLB
> nodes
> and 1 for each failover cluster (Active/Passive) )
> This is no longer a cheap solution for our reporting needs.
> Thanks for your help.
> Kind Regards,
> Des
>|||Hi Bruce,
Its not a case of a lot of users or reports - it is a case of a fault
tolerance / disaster recovery solution, we have network load balanced web
farms to facilitate this setup.
The 2 web NLB farms are for integration and production, these need to be
there for our environment, the decision was taken as we are a multi site
organization to have 2 nodes of each NLB in each of our main offices and
then of these 2 nodes each one is in a separate server room, thus 4 nodes =2 per site and of the 2 per site each is separated per server room, disaster
recovery and fault tolerance taken care of.
We will need per processor licensing as intranet reports are made and CAL
will be too expensive.
This is an extremely expensive solution in this case - I have no idea of the
cost of Crystal reports and this is no place to get into this argument as it
does not help.
I just think that Microsoft should de-couple SSRS from SQL Server and make
it Microsoft Reporting Server or something like this with its own licensing
model, the databases can still be stuck to SQL Server but this would make it
more transparent and quite possibly cheaper as Enterprise SQL Server 2005
Processor licenses are not cheap and if the database and reporting server
sides are split as recommended my MS then this does lead to an expensive
solution when each of the IIS NLB nodes need separate licenses.
Thanks for the answer though.
Cheers,
Des
On 08/01/2008 18:00, in article OyC$EghUIHA.280@.TK2MSFTNGP03.phx.gbl, "Bruce
L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote:
> You have the licensing correct. Enterprise is the only one which supports a
> web farm and any server that has Reporting Services installed must have a
> server license. How expensive it is for you depends on how you license (CAL
> or per processor).
> If you price Crystal in such a configuration you will find that it is even
> more expensive.
> Unless you have one heck of a lot of reports and users then I sincerely
> doubt that you need such a configuration.
>|||Hi Bruce,
A final question for you, if I have SSRS 2000 and SSRS 2005 installed on
these NLBs (8 nodes total as mentioned) do I therefore also need separate
SQL Server 2000 and SQL Server 2005 Enterprise Edition CPU licenses for
these nodes?
So a total of 16 Enterprise Edition CPU licenses for the Reporting Services
setup?
Cheers,
Des
On 08/01/2008 18:00, in article OyC$EghUIHA.280@.TK2MSFTNGP03.phx.gbl, "Bruce
L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote:
> You have the licensing correct. Enterprise is the only one which supports a
> web farm and any server that has Reporting Services installed must have a
> server license. How expensive it is for you depends on how you license (CAL
> or per processor).
> If you price Crystal in such a configuration you will find that it is even
> more expensive.
> Unless you have one heck of a lot of reports and users then I sincerely
> doubt that you need such a configuration.
>|||I'm confused, you want to have both RS 2000 and RS 2005 installed on each
server?
Why would you do that? RS 2000 reports run fine in RS 2005. And they run
much faster.
In case you do want to do this, I believe a SQL 2005 license allows running
both 200 and 2005 so you would not need two licenses. However, I would check
with whoever you buy licenses from.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Des FitzGerald" <dfitz@.dr-sql.com> wrote in message
news:C3AA8E77.7B74%dfitz@.dr-sql.com...
> Hi Bruce,
> A final question for you, if I have SSRS 2000 and SSRS 2005 installed on
> these NLBs (8 nodes total as mentioned) do I therefore also need separate
> SQL Server 2000 and SQL Server 2005 Enterprise Edition CPU licenses for
> these nodes?
> So a total of 16 Enterprise Edition CPU licenses for the Reporting
> Services
> setup?
> Cheers,
> Des
>
> On 08/01/2008 18:00, in article OyC$EghUIHA.280@.TK2MSFTNGP03.phx.gbl,
> "Bruce
> L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote:
>> You have the licensing correct. Enterprise is the only one which supports
>> a
>> web farm and any server that has Reporting Services installed must have a
>> server license. How expensive it is for you depends on how you license
>> (CAL
>> or per processor).
>> If you price Crystal in such a configuration you will find that it is
>> even
>> more expensive.
>> Unless you have one heck of a lot of reports and users then I sincerely
>> doubt that you need such a configuration.
>

Advise on Integration with FoxPro

I have a legacy system based in Fox that I need to generate reports from and I would like to use SQL Server to house some report optimized tables that are derived from the Fox data. I am currently looking at several strategies for manipulating and porting the data.

a. Linked Servers - haven't had much success linking to the OLD fox database (DOS version)
b. DTS - can't find a way around dropping and recreating all the tables every time.
c. Visual Basic App - most control but not sure if the best solution
d. Some Combo of the Above

Does anyone have any advice on what the best approach might be? Are there any other solutions to add to the mix?

Thanks for any assistance,

AllenAt this point of time, I think a good sollution can be to write a DLL in VB/VC++ which will communicate with the FOX data and return the resultset in XML format.

Then your actual app can parse through the XML document using XML DOM or SAX API and provide the data to SQL Server database. If your XML file is going to be big enough, consider using SAX API which is a light-weight alternative to XML DOM.|||I have it in production with win2000 server sp2 + sql server 2000 sp3
by using Microsoft OLEDB Provider for ODBC, so it can be done

I have problems when I want to upgrade to VFPOLEDB, and in the environment of win2003 + sql server 2000 sp3. Running OPENQUERY in QA is fine, but can't be run via SQL Agent job with the error msg

Could not initialize data source object of OLE DB provider 'VFPOLEDB'. [SQLSTATE 42000] (Error 7303) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80040e21]. [SQLSTATE 01000] (Error 7300). The step failed.

Advise on hardware upgrade

I would like to have your opinions on how to upgrade the server hardware
here:
Currently:
Windows 2000 Server SP2
Dual Intel Pentium III 1,2 GHz
2 GB RAM
Harddisks are on a SAN, so they are not needed to upgrade
MS-SQL Server 2000 Standard Edition, no other apps run on this machine
The SQL Server hosts a single database, size approx 3 GB, growing by about
10% per month. 60 highly concurrent users, in fact doing the same things at
the same time. The largest table has about 9.000.000 records, daily
increment 9000.
When the users start their tasks, I encounter - due to the intense
concurrency - performance degradation, although the overall performance is
ok. These peaks are beyond the servers capabilities. I monitored using the
performance monitor:
Processor Queue lenght: average 0,58 max 15
% Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks up
to 90% as well)
% Processor Time Processor 1 average 19,5 max 61,1
% Privileged Time Processor 0 average 1,5 max 7,1
% Privileged Time Processor 1 average 1,3 max 5,7
I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
What´s your opinion?
Thanks for your input.
BerndStrange that nobody seems to have an opninion on this topic. Is there a
reason why?
TIA
Bernd
"Bernd Maierhofer (dato)" <bernd.maierhofer@.dato.at> schrieb im Newsbeitrag
news:en172$OqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I would like to have your opinions on how to upgrade the server hardware
> here:
> Currently:
> Windows 2000 Server SP2
> Dual Intel Pentium III 1,2 GHz
> 2 GB RAM
> Harddisks are on a SAN, so they are not needed to upgrade
> MS-SQL Server 2000 Standard Edition, no other apps run on this machine
> The SQL Server hosts a single database, size approx 3 GB, growing by about
> 10% per month. 60 highly concurrent users, in fact doing the same things
at
> the same time. The largest table has about 9.000.000 records, daily
> increment 9000.
> When the users start their tasks, I encounter - due to the intense
> concurrency - performance degradation, although the overall performance is
> ok. These peaks are beyond the servers capabilities. I monitored using the
> performance monitor:
> Processor Queue lenght: average 0,58 max 15
> % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks
up
> to 90% as well)
> % Processor Time Processor 1 average 19,5 max 61,1
> % Privileged Time Processor 0 average 1,5 max 7,1
> % Privileged Time Processor 1 average 1,3 max 5,7
> I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
> What´s your opinion?
> Thanks for your input.
> Bernd
>

Advise on Forms Authentication

ok i am doing a system in asp.net and also using reporting services to
generate some reports for user to view.
My system consists of two parts which is the Suggestor and the Evaluator.
Firstly the suggestor will go to a aspx page whereby they can submit new
suggestions by filling up the page and before they submit the suggestion,
they had to choose from a dropdownlist box which contains all the available
Evaluator Full Name, so that this suggestion will only be sent to the choosen
Evaluator to be evaluated. And all data will be store in SQL Server 2000.
So now the suggestions need to be rejected or accepted by the evaluator and
i am using reporting services to generate the page for the evaluator to view
the suggestions that had been submitted to them. and i am not sure of how to
go about using forms authentication to allow the evaluator to login, so that
when they login successfully, they will be allow to view suggestions that is
associated to them. Do i use Active Directory login in asp.net then link to
reporting services? or is there any other method that i can use so that the
Evaluator that had been login can view their own suggestions in the reporting
services.
For your information, all the evaluator profiles are store in Active
Directory server thats why i thought of using AD login to carry out my task.
Ok in order to let u all have a clearer view of what i want to achieved, i
will use the following example.
For example now i am going to submit a suggestions. and i go to the aspx
page and fill up all the necessary data and lastly select a Evaluator from
the dropdownlist box. take for example i selected "Tan Boon Keng" as the
evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
evaluate my suggestion n inorder to evaluate the suggestion, he need to login
through some forms authentication so that he can only see the suggestions
that is related to him, n not seeing ALL the suggestions that had been
submitted. and the page where they view the suggestions is generate by
reporting services so how should i go about implementing this system to work
as what i want to achieved.
Sorry for the long post and thanks in advance...Hi, is there anybody that can help me or give me advise on how should i do
this?
i tried using forms authentication in asp.net then when evaluator login
through that page, they will be direct to the reporting services page and
they can view their suggestions only. but i not sure how to get the logon
user session id to query the database for the suggestions related to that
particular logon user.
"JiaN" wrote:
> ok i am doing a system in asp.net and also using reporting services to
> generate some reports for user to view.
> My system consists of two parts which is the Suggestor and the Evaluator.
> Firstly the suggestor will go to a aspx page whereby they can submit new
> suggestions by filling up the page and before they submit the suggestion,
> they had to choose from a dropdownlist box which contains all the available
> Evaluator Full Name, so that this suggestion will only be sent to the choosen
> Evaluator to be evaluated. And all data will be store in SQL Server 2000.
> So now the suggestions need to be rejected or accepted by the evaluator and
> i am using reporting services to generate the page for the evaluator to view
> the suggestions that had been submitted to them. and i am not sure of how to
> go about using forms authentication to allow the evaluator to login, so that
> when they login successfully, they will be allow to view suggestions that is
> associated to them. Do i use Active Directory login in asp.net then link to
> reporting services? or is there any other method that i can use so that the
> Evaluator that had been login can view their own suggestions in the reporting
> services.
> For your information, all the evaluator profiles are store in Active
> Directory server thats why i thought of using AD login to carry out my task.
> Ok in order to let u all have a clearer view of what i want to achieved, i
> will use the following example.
> For example now i am going to submit a suggestions. and i go to the aspx
> page and fill up all the necessary data and lastly select a Evaluator from
> the dropdownlist box. take for example i selected "Tan Boon Keng" as the
> evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
> evaluate my suggestion n inorder to evaluate the suggestion, he need to login
> through some forms authentication so that he can only see the suggestions
> that is related to him, n not seeing ALL the suggestions that had been
> submitted. and the page where they view the suggestions is generate by
> reporting services so how should i go about implementing this system to work
> as what i want to achieved.
> Sorry for the long post and thanks in advance...

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClassCategoryFull TimePart Time
GovernmentFederal()()
State()()
Local()()
Other(Specify)()()
IndustrialLaboratory()()
Agricultural()()
Nutrition()()
Other(Specify)()()
OtherAssociation()()
Service()()
Retired()()
Other(Specify)()()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassIDint (PK)
CategoryIDint (PK)
CategoryDescvarchar(50)
Table: JobType (used for Full or Part Time)
JobTypeIDint (PK)
JobDescvarchar(20)
Table: Member_Employement
MemberIDint (PK)
ClassIDint (PK)
CategoryIDint (PK)
JobTypeIDint
Othervarchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?
Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:

> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClassCategoryFull TimePart Time
> GovernmentFederal()()
> State()()
> Local()()
> Other(Specify)()()
> IndustrialLaboratory()()
> Agricultural()()
> Nutrition()()
> Other(Specify)()()
> OtherAssociation()()
> Service()()
> Retired()()
> Other(Specify)()()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassIDint (PK)
> CategoryIDint (PK)
> CategoryDescvarchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeIDint (PK)
> JobDescvarchar(20)
> Table: Member_Employement
> MemberIDint (PK)
> ClassIDint (PK)
> CategoryIDint (PK)
> JobTypeIDint
> Othervarchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>
|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClass Category Full Time Part Time
Government Federal () ()
State () ()
Local () ()
Other(Specify) () ()
Industrial Laboratory () ()
Agricultural () ()
Nutrition () ()
Other(Specify) () ()
Other Association () ()
Service () ()
Retired () ()
Other(Specify) () ()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassID int (PK)
CategoryID int (PK)
CategoryDesc varchar(50)
Table: JobType (used for Full or Part Time)
JobTypeID int (PK)
JobDesc varchar(20)
Table: Member_Employement
MemberID int (PK)
ClassID int (PK)
CategoryID int (PK)
JobTypeID int
Other varchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:
> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClass Category Full Time Part Time
> Government Federal () ()
> State () ()
> Local () ()
> Other(Specify) () ()
> Industrial Laboratory () ()
> Agricultural () ()
> Nutrition () ()
> Other(Specify) () ()
> Other Association () ()
> Service () ()
> Retired () ()
> Other(Specify) () ()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassID int (PK)
> CategoryID int (PK)
> CategoryDesc varchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeID int (PK)
> JobDesc varchar(20)
> Table: Member_Employement
> MemberID int (PK)
> ClassID int (PK)
> CategoryID int (PK)
> JobTypeID int
> Other varchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClass Category Full Time Part Time
Government Federal () ()
State () ()
Local () ()
Other(Specify) () ()
Industrial Laboratory () ()
Agricultural () ()
Nutrition () ()
Other(Specify) () ()
Other Association () ()
Service () ()
Retired () ()
Other(Specify) () ()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassID int (PK)
CategoryID int (PK)
CategoryDesc varchar(50)
Table: JobType (used for Full or Part Time)
JobTypeID int (PK)
JobDesc varchar(20)
Table: Member_Employement
MemberID int (PK)
ClassID int (PK)
CategoryID int (PK)
JobTypeID int
Other varchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:

> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClass Category Full Time Part Time
> Government Federal () ()
> State () ()
> Local () ()
> Other(Specify) () ()
> Industrial Laboratory () ()
> Agricultural () ()
> Nutrition () ()
> Other(Specify) () ()
> Other Association () ()
> Service () ()
> Retired () ()
> Other(Specify) () ()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassID int (PK)
> CategoryID int (PK)
> CategoryDesc varchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeID int (PK)
> JobDesc varchar(20)
> Table: Member_Employement
> MemberID int (PK)
> ClassID int (PK)
> CategoryID int (PK)
> JobTypeID int
> Other varchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

advise on data replication

Hi,
I have the following situation and would like some advise.
I am using SQL server 2005. I have smart clients access the database
over the internet through web services.
I currently have 1 data warehouse type database. It contains about 95%
read only data which is apended to nightly.
Since the queries are long and complex running on millions of rows of
data, queries are run asyncronously. I use Service Broker to queue and
run the queries.
I have the following read/write tables in the database:
A table to store the results of the queries.
A table to store the status of the request. Front the front end, I can
see if the query is queued, running or completed.
Since the query request itself is very complex, I store the request in
a series of other tables before the request is queued.
Now, for scalability reasons they want to go to 2 database servers,
allowing more requests to be run at the same time. Obviously, in
regards to the read/write tables described above, I need to somehow
replicate the data between the servers. Any thoughts as to which
replication method would best fit my scenario?
Any thoughts would be appreciated.
Thanks in advance
James
Maybe use Partitioned Views for this instead?
"James" <jamesdfriesen@.hotmail.com> wrote in message
news:1142885711.716158.206300@.v46g2000cwv.googlegr oups.com...
> Hi,
> I have the following situation and would like some advise.
> I am using SQL server 2005. I have smart clients access the database
> over the internet through web services.
> I currently have 1 data warehouse type database. It contains about 95%
> read only data which is apended to nightly.
> Since the queries are long and complex running on millions of rows of
> data, queries are run asyncronously. I use Service Broker to queue and
> run the queries.
> I have the following read/write tables in the database:
> A table to store the results of the queries.
> A table to store the status of the request. Front the front end, I can
> see if the query is queued, running or completed.
> Since the query request itself is very complex, I store the request in
> a series of other tables before the request is queued.
> Now, for scalability reasons they want to go to 2 database servers,
> allowing more requests to be run at the same time. Obviously, in
> regards to the read/write tables described above, I need to somehow
> replicate the data between the servers. Any thoughts as to which
> replication method would best fit my scenario?
> Any thoughts would be appreciated.
> Thanks in advance
> James
>

Advise on column type

Hello,

In my web application I am listing steps of action:
STEP ACTION
10
10.1
10.2
10.9
10.10
10.20
30
30.1
etc

How should I set up my columns in my database? I tryed decimal but the problem is that 10.1 and 10.10 is in theory the same while in my case it is absolutely not. I also tryed nvarchar but does not sort correctly.
Should I create 2 int column, one for the main step and another one for the sub-step (one would contain the part before the "." and the other would contain the part after the ".")?

Thanks

you could use the 2 column approach, or you could still use varchar.

to get a numeric sort on string data, you need to carefully control the character positions

10 becomes a string 10.00
10.1 becomes 10.01 <-- notice the leading zero after the decimal

then when your sort the string data, you'd get a correct sort.

10.00
10.01
10.02
10.09
10.10
10.20
30.00
30.01

note: if your data might ever go to three places after the decimal, then you have to account for it now i.e.
10.000
10.001
10.010
10.100

So the question back to you is...will it be simpler for you to manage two columns and put the data back together as its sorted/presented, or will it be simpler for you to control the formatting of the string input?

PS: you could also still use the decimal format as long as you control the positions of each character value...

|||Thanks for the answer.
After carefully reviewing your suggestion, I think it would be easier for me to create 2 columns.
Now, would it be more efficient to put back the data together with my sql request ( do a cast of each int column into varchar and add the character "." between) or to format the string input in my code(C#) ?

Thanks|||

If you perform the string concatenation in Sql, then anyplace you pull the data, it will be formatted as you require.
If you perform the string concatenation in code (C#), then you'd need to repeat the "fixup" anytime you use the data.

In terms of raw cpu, i doubt that there is any statistically significant difference between the 2 choices - so i would choose the method you are most comfortable with.

|||

I finally decided to go with the varchar column.
However, I can not find any rule to automatically transform a 10.1 into a 10.01 or a 10.10 into a 10.1

Would you have a sample on how to do the transformation?

Thanks

Advise needed on Single user to multi-user database change

I am converting a system that is single user to multi-user. My problem is
that I am not familar with SQL Server 2000 and am not sure how to approach
this problem.
The biggest part of this problem is the reports. The reports (Crystal
Reports version 4.X) run from tables that are built in a VB application.
The Crystal Report report form is linked to an odbc connection. Some of the
tables are long running and run the risk of another person starting the same
report and corrupting the data in the table.
The report forms seem to be locked to a table name and accessed via the odbc
connection. I tried to change the table name, but the report stopped
working.
My question is, is there a way I can create a table with the same name, but
only be visible from the current user?Hi,
You can create tables using the user's name.
But this name should be a valid SQL User account that has the permission in
the Database and you need to connect to the SQL Server using that account
or have sufficient credentials to access the other users tables.
How do you connect (authenticate) the user to the SQL server?
How are you planning to connect (authenticate) the different users to the
SQL server?
Do you plan to use the "sa" account or create separate accounts for each
users?
If you are planning to create separate accounts for each user, then you can
definitely create separate tables for each user using
Create table [username].[tablename]
This table will be visible only to the user that has created it, rather,
accessible only with that particular username's credential
Example
Create table [myname].[testtb] (FirstCol int not null default 0)
select * from testtb - would return an error, stating invalid object name.
select * from [myname].testtb - would return the records in that table
So, as long as you are using separate users this will work safely. Each
user will be able to view and access their own table, without the fear of
messing up with other users data.
If you are planning not to use separate username then you can create
temporary tables.
You can create local and global temporary tables. Local temporary tables
are visible only in the current session; global temporary tables are
visible to all sessions.
Prefix local temporary table names with single number sign (#table_name),
and prefix global temporary table names with a double number sign
(##table_name).
SQL statements reference the temporary table using the value specified for
table_name in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
But, since you need to access this tables from Crystal report and that
would be a separate connection, the local temporary tables would not be
visible to the Crystal Report.
This method would work, except for the integration with interaction via a
different connection to the database. As long as you use the same
connection object that created the tables, this
method is gonna work.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.