Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:
> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Is using SQL security a big security hole?
If a user of my application is connecting to their sql server using
integrated security doesn't this allow them to fire up something like QA and
do things to the database that they shouldn't. I know I should have
everything secure even if the user is connected that way but assuming at
least some security functionality is implemented in the app wouldn't they be
able to get around at least what is implemented in the app?
Cheers,
MichaelCorrect. They can login using their Windows account using QA and do whatever
that login has
permissions to do. Consider having your app using application roles.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using integr
ated security doesn't
> this allow them to fire up something like QA and do things to the database
that they shouldn't. I
> know I should have everything secure even if the user is connected that wa
y but assuming at least
> some security functionality is implemented in the app wouldn't they be abl
e to get around at least
> what is implemented in the app?
> Cheers,
> Michael
>|||Hello,
Yes. But it doesn't matter if they connected via Intergrated Security or SQL
security if the the database isn't secure then they will be able to change
what they wanted.
You can look at Application Roles (which are
) or by using SP for all
your interactions with the data. You should be using SP anyway what you may
not be doing is removing the access rights from the tables.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using
> integrated security doesn't this allow them to fire up something like QA
> and do things to the database that they shouldn't. I know I should have
> everything secure even if the user is connected that way but assuming at
> least some security functionality is implemented in the app wouldn't they
> be able to get around at least what is implemented in the app?
> Cheers,
> Michael
>|||As pointed out, it doesn't matter HOW they get in. Once in, it's up to your
SQL Server security to keep them from doing things you don't want them to.
They could just as easily use an application log in/password to connect with
QA (assuming they could see it).|||"Todd Beaulieu" <nothanks@.enoughspam.bad> wrote in message
news:ObMbQmirFHA.2876@.TK2MSFTNGP12.phx.gbl...
> As pointed out, it doesn't matter HOW they get in. Once in, it's up to
> your
> SQL Server security to keep them from doing things you don't want them to.
> They could just as easily use an application log in/password to connect
> with
> QA (assuming they could see it).
Oops, I forgot I asked this questions :-) The thing is if they login using
SQL security they won't necessarily have the password because it might be
stored by the program they are using and entered by an administrator. So if
users are using integrated security you need to make your sql database
bulletproof. You could use stored procs and security to control it but that
means all of your security has to implemented in the database and none in
your app, which would be fairly hard to achieve.
I'll look into application roles as suggested to see if this gives me what I
want.
Regards,
Michael|||> You could use stored procs and security to control it but that
> means all of your security has to implemented in the database and none in
> your app, which would be fairly hard to achieve.
Yes, that's a design that's commonly used. Deny users permissions on
tables and other objects. I don't know why you say that's hard to
achieve.
Other alternatives are application security or an N-tier architecture
where the users only have access to the middle tier. In those scenarios
you should still generally use stored procedures for all data access -
procs have plenty of advantages other than security.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> Yes, that's a design that's commonly used. Deny users permissions on
> tables and other objects. I don't know why you say that's hard to
> achieve.
I mean it's hard to achieve fully. Sure if I want to stop someone creating a
customer without a surname that's easy to do but I could have some
extrememly complicated restrictions on what data could be entered and in
what way, these restrictions would be impossible or very difficult to
implement within the database. In all of those cases connecting direct to
the database would give the user greater access. I just can't see any app
having every single restriction implemented in the database. Surely there'd
be hundreds of restrictions placed within the source code outside the
database.
> Other alternatives are application security or an N-tier architecture
> where the users only have access to the middle tier. In those scenarios
> you should still generally use stored procedures for all data access -
> procs have plenty of advantages other than security.
True, although not likely to happen unless my app gets a full rewrite.
Michael
integrated security doesn't this allow them to fire up something like QA and
do things to the database that they shouldn't. I know I should have
everything secure even if the user is connected that way but assuming at
least some security functionality is implemented in the app wouldn't they be
able to get around at least what is implemented in the app?
Cheers,
MichaelCorrect. They can login using their Windows account using QA and do whatever
that login has
permissions to do. Consider having your app using application roles.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using integr
ated security doesn't
> this allow them to fire up something like QA and do things to the database
that they shouldn't. I
> know I should have everything secure even if the user is connected that wa
y but assuming at least
> some security functionality is implemented in the app wouldn't they be abl
e to get around at least
> what is implemented in the app?
> Cheers,
> Michael
>|||Hello,
Yes. But it doesn't matter if they connected via Intergrated Security or SQL
security if the the database isn't secure then they will be able to change
what they wanted.
You can look at Application Roles (which are
) or by using SP for allyour interactions with the data. You should be using SP anyway what you may
not be doing is removing the access rights from the tables.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OQAf%23ZerFHA.1168@.TK2MSFTNGP11.phx.gbl...
> If a user of my application is connecting to their sql server using
> integrated security doesn't this allow them to fire up something like QA
> and do things to the database that they shouldn't. I know I should have
> everything secure even if the user is connected that way but assuming at
> least some security functionality is implemented in the app wouldn't they
> be able to get around at least what is implemented in the app?
> Cheers,
> Michael
>|||As pointed out, it doesn't matter HOW they get in. Once in, it's up to your
SQL Server security to keep them from doing things you don't want them to.
They could just as easily use an application log in/password to connect with
QA (assuming they could see it).|||"Todd Beaulieu" <nothanks@.enoughspam.bad> wrote in message
news:ObMbQmirFHA.2876@.TK2MSFTNGP12.phx.gbl...
> As pointed out, it doesn't matter HOW they get in. Once in, it's up to
> your
> SQL Server security to keep them from doing things you don't want them to.
> They could just as easily use an application log in/password to connect
> with
> QA (assuming they could see it).
Oops, I forgot I asked this questions :-) The thing is if they login using
SQL security they won't necessarily have the password because it might be
stored by the program they are using and entered by an administrator. So if
users are using integrated security you need to make your sql database
bulletproof. You could use stored procs and security to control it but that
means all of your security has to implemented in the database and none in
your app, which would be fairly hard to achieve.
I'll look into application roles as suggested to see if this gives me what I
want.
Regards,
Michael|||> You could use stored procs and security to control it but that
> means all of your security has to implemented in the database and none in
> your app, which would be fairly hard to achieve.
Yes, that's a design that's commonly used. Deny users permissions on
tables and other objects. I don't know why you say that's hard to
achieve.
Other alternatives are application security or an N-tier architecture
where the users only have access to the middle tier. In those scenarios
you should still generally use stored procedures for all data access -
procs have plenty of advantages other than security.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> Yes, that's a design that's commonly used. Deny users permissions on
> tables and other objects. I don't know why you say that's hard to
> achieve.
I mean it's hard to achieve fully. Sure if I want to stop someone creating a
customer without a surname that's easy to do but I could have some
extrememly complicated restrictions on what data could be entered and in
what way, these restrictions would be impossible or very difficult to
implement within the database. In all of those cases connecting direct to
the database would give the user greater access. I just can't see any app
having every single restriction implemented in the database. Surely there'd
be hundreds of restrictions placed within the source code outside the
database.
> Other alternatives are application security or an N-tier architecture
> where the users only have access to the middle tier. In those scenarios
> you should still generally use stored procedures for all data access -
> procs have plenty of advantages other than security.
True, although not likely to happen unless my app gets a full rewrite.
Michael
Is using linked server with MSDE the way to go for this problem?
We have a problem and I'm not sure of the best way to solve it. Our situation:
We have an ASP.NET application which uses a full SQL Server database.
We want to add the capability for salespeople to initially install (in a
separate
procedure) our app locally on a tablet PC with a blank MSDE
database. We'll figure this part out.
Then, periodically, when they are able to access our
'master' ASP.NET app from the road, we would like for them to be able
to download selected rows from a number of tables from the
central SQL db to their local MSDE. They would make changes locally
(offline),
then reconnect later on and upload the data back to the central db.
I am thinking that creating a linked server temporarily on the master
db (via a call to sp_addlinkedserver) to transfer this data back and forth
to the MSDE db may be the way to go, but haven't found any specific info/case
studies/etc. that supports that. In addition, I created an MSDE database on
a tablet PC which is hooked to our network, but haven't found a way to
successfully 'broadcast' its presence to the SQL Server db so that I can
create a linked-server definition via Enterprise Manager.
Does anyone have any thoughts either about how to get this approach to work,
or a better way to solve this problem?
Thanks in advance for your help!
hi Rick,
"Rick Pinson" <rickpinson@.yahoo.com(donotspam)> ha scritto nel
messaggio news:69A042D0-CEF0-4AC8-A210-7BC29A6CD5E2@.microsoft.com
> We have a problem and I'm not sure of the best way to solve it. Our
> situation:
> We have an ASP.NET application which uses a full SQL Server database.
> We want to add the capability for salespeople to initially install
> (in a separate
> procedure) our app locally on a tablet PC with a blank MSDE
> database. We'll figure this part out.
> Then, periodically, when they are able to access our
> 'master' ASP.NET app from the road, we would like for them to be able
> to download selected rows from a number of tables from the
> central SQL db to their local MSDE. They would make changes locally
> (offline),
> then reconnect later on and upload the data back to the central db.
> I am thinking that creating a linked server temporarily on the master
> db (via a call to sp_addlinkedserver) to transfer this data back and
> forth
> to the MSDE db may be the way to go, but haven't found any specific
> info/case studies/etc. that supports that.
this seems like you want to perform a push operation, from the "master" to
all "remote" MSDE instances...
I'd go the opposite way... when the remote MSDE instance is able to connect
to the "master", add a linked server from remote to master, perform the
operations you need, drop the the linked server...
>In addition, I created an
> MSDE database on
> a tablet PC which is hooked to our network, but haven't found a way to
> successfully 'broadcast' its presence to the SQL Server db so that I
> can create a linked-server definition via Enterprise Manager.
the broadcasting mechanism to propagate server presence over the lan is not
always granted as lot of stuff is involved...
for instance: have a look at ListAvailableServer exposed by SQL-DMO:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
of course, Id suggest you to test your network protocols settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
We have an ASP.NET application which uses a full SQL Server database.
We want to add the capability for salespeople to initially install (in a
separate
procedure) our app locally on a tablet PC with a blank MSDE
database. We'll figure this part out.
Then, periodically, when they are able to access our
'master' ASP.NET app from the road, we would like for them to be able
to download selected rows from a number of tables from the
central SQL db to their local MSDE. They would make changes locally
(offline),
then reconnect later on and upload the data back to the central db.
I am thinking that creating a linked server temporarily on the master
db (via a call to sp_addlinkedserver) to transfer this data back and forth
to the MSDE db may be the way to go, but haven't found any specific info/case
studies/etc. that supports that. In addition, I created an MSDE database on
a tablet PC which is hooked to our network, but haven't found a way to
successfully 'broadcast' its presence to the SQL Server db so that I can
create a linked-server definition via Enterprise Manager.
Does anyone have any thoughts either about how to get this approach to work,
or a better way to solve this problem?
Thanks in advance for your help!
hi Rick,
"Rick Pinson" <rickpinson@.yahoo.com(donotspam)> ha scritto nel
messaggio news:69A042D0-CEF0-4AC8-A210-7BC29A6CD5E2@.microsoft.com
> We have a problem and I'm not sure of the best way to solve it. Our
> situation:
> We have an ASP.NET application which uses a full SQL Server database.
> We want to add the capability for salespeople to initially install
> (in a separate
> procedure) our app locally on a tablet PC with a blank MSDE
> database. We'll figure this part out.
> Then, periodically, when they are able to access our
> 'master' ASP.NET app from the road, we would like for them to be able
> to download selected rows from a number of tables from the
> central SQL db to their local MSDE. They would make changes locally
> (offline),
> then reconnect later on and upload the data back to the central db.
> I am thinking that creating a linked server temporarily on the master
> db (via a call to sp_addlinkedserver) to transfer this data back and
> forth
> to the MSDE db may be the way to go, but haven't found any specific
> info/case studies/etc. that supports that.
this seems like you want to perform a push operation, from the "master" to
all "remote" MSDE instances...
I'd go the opposite way... when the remote MSDE instance is able to connect
to the "master", add a linked server from remote to master, perform the
operations you need, drop the the linked server...
>In addition, I created an
> MSDE database on
> a tablet PC which is hooked to our network, but haven't found a way to
> successfully 'broadcast' its presence to the SQL Server db so that I
> can create a linked-server definition via Enterprise Manager.
the broadcasting mechanism to propagate server presence over the lan is not
always granted as lot of stuff is involved...
for instance: have a look at ListAvailableServer exposed by SQL-DMO:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
of course, Id suggest you to test your network protocols settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Is unicode necessary for a English and Spanish application/database?
We may add a spanish version of the application we are currently developing in the future. Do I need to use unicode datatypes in SQL2K or are plain varchar, etc. datatypes fine? Or can we convert in the future if necessary? Thanks.
Randy
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/r.../sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow?
It's easier to start using
Unicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/r.../sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
Randy
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/r.../sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow?
It's easier to start usingUnicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/r.../sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
Is unicode necessary for a English and Spanish application/database?
We may add a spanish version of the application we are currently developing in the future. Do I need to use unicode datatypes in SQL2K or are plain varchar, etc. datatypes fine? Or can we convert in the future if necessary? Thanks
Randy"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/reference/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow? :) It's easier to start using
Unicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/reference/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
Randy"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/reference/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow? :) It's easier to start using
Unicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/reference/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
Is unicode necessary for a English and Spanish application/database?
We may add a spanish version of the application we are currently developing
in the future. Do I need to use unicode datatypes in SQL2K or are plain var
char, etc. datatypes fine? Or can we convert in the future if necessary? Th
anks.
Randy"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/...e/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow? It's easier to start using
Unicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/...e/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
in the future. Do I need to use unicode datatypes in SQL2K or are plain var
char, etc. datatypes fine? Or can we convert in the future if necessary? Th
anks.
Randy"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/...e/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David|||Actually that charset (cp1252) does have the Euro char (at position
0x80). Randy, if you use any 1252-based collation such as the one that
David referenced you can store English or Spanish data without using
Unicode data types. In fact, code page 1252 is capable of storing most
of the Western European languages like English, Spanish, Portuguese,
German, French, Italian, etc in non-Unicode columns.
You may wish to consider Unicode for further down the road, though... if
you're adding support for Spanish today, is it possible that you may find
a market in Japan or Israel tomorrow?
It's easier to start usingUnicode from the start of an app's lifetime than it is to convert an
existing one. It is possible to convert down the road, although you may
end up needing to change not just the table data types, but also stored
proc and trigger code as well as application code.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com>
References: <FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com>
Subject: Re: Is unicode necessary for a English and Spanish
application/database?
Date: Mon, 26 Apr 2004 12:31:32 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#YJUSR7KEHA.2236@.TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: airband-66-226-246-66.airband.net 66.226.246.66
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
09.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:338669
X-Tomcat-NG: microsoft.public.sqlserver.server
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:FD264AD2-770E-4E25-B38B-1F887B5F1111@.microsoft.com...
> We may add a spanish version of the application we are currently
developing in the future. Do I need to use unicode datatypes in SQL2K or
are plain varchar, etc. datatypes fine? Or can we convert in the future
if
necessary? Thanks.
> Randy
If you find a code page that has all the characters you need, then you can
use varchar instead of nvarchar.
The most common collation and charset in the US is
SQL_Latin1_General_CP1_CI_AI
http://www.microsoft.com/globaldev/...e/sbcs/1252.htm
It has the whole spanish alphabet, but lacks the Euro character.
David
Monday, March 26, 2012
Is this possible? SQL Server 2000 Write times
I created an online application for one of our educational programs. The da
ta is inserted via website entry -> client side validation -> stored procedu
re -> database. Given a table stucture as follows, if 100 or even 1000 peop
le applied at the exact same time using an online application, what is the m
aximum time interval that could separate each record. How long would SQL Se
rver 2000 generally take to insert each record into the database. The probl
em is that each year, only the top 100 students are admitted in the program,
but there is one student who says they applied at 9:00 am and their record
insert time shows 9:59am. There are records before it and after it that sho
ws times like 9:58, 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL
Server 59 minutes to actually write the record if 1000 people hit the datab
ase at the same time? Please help, they want to get counsil involved.
ApplicantID int primary key identity
SSN varchar(11)
FirstName varchar(30)
LastName varchar(50)indexed
Address varchar (50)
City varchar (50)
State char (2)
Zip varchar (11)
Phone varchar (20)
Email varchar (50)
dateEntered smalldatetimeShawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?
ta is inserted via website entry -> client side validation -> stored procedu
re -> database. Given a table stucture as follows, if 100 or even 1000 peop
le applied at the exact same time using an online application, what is the m
aximum time interval that could separate each record. How long would SQL Se
rver 2000 generally take to insert each record into the database. The probl
em is that each year, only the top 100 students are admitted in the program,
but there is one student who says they applied at 9:00 am and their record
insert time shows 9:59am. There are records before it and after it that sho
ws times like 9:58, 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL
Server 59 minutes to actually write the record if 1000 people hit the datab
ase at the same time? Please help, they want to get counsil involved.
ApplicantID int primary key identity
SSN varchar(11)
FirstName varchar(30)
LastName varchar(50)indexed
Address varchar (50)
City varchar (50)
State char (2)
Zip varchar (11)
Phone varchar (20)
Email varchar (50)
dateEntered smalldatetimeShawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?
Wednesday, March 21, 2012
is this disaster recovery / 'high availabilty" setup OK?
An accounting application uses a SQL server.
I am going to use two SQL servers for this application. These two SQL
servers are sync between them.
Each night, a copy of the SQL database is copied to a network share, and
then the backup program, ARCserve, will back up the database on the network
share nightly.
Is this approach a best practice? Any pointers/comments/suggestions are
greatly appreciated.
Thanks,What you want to achieve by using 2 servers ? to me using 2 servers for
accounting is not a good idea.
about backup - that is fine if you put databases in sync.
"Doug Fox" wrote:
> An accounting application uses a SQL server.
> I am going to use two SQL servers for this application. These two SQL
> servers are sync between them.
> Each night, a copy of the SQL database is copied to a network share, and
> then the backup program, ARCserve, will back up the database on the networ
k
> share nightly.
> Is this approach a best practice? Any pointers/comments/suggestions are
> greatly appreciated.
> Thanks,
>
>
I am going to use two SQL servers for this application. These two SQL
servers are sync between them.
Each night, a copy of the SQL database is copied to a network share, and
then the backup program, ARCserve, will back up the database on the network
share nightly.
Is this approach a best practice? Any pointers/comments/suggestions are
greatly appreciated.
Thanks,What you want to achieve by using 2 servers ? to me using 2 servers for
accounting is not a good idea.
about backup - that is fine if you put databases in sync.
"Doug Fox" wrote:
> An accounting application uses a SQL server.
> I am going to use two SQL servers for this application. These two SQL
> servers are sync between them.
> Each night, a copy of the SQL database is copied to a network share, and
> then the backup program, ARCserve, will back up the database on the networ
k
> share nightly.
> Is this approach a best practice? Any pointers/comments/suggestions are
> greatly appreciated.
> Thanks,
>
>
Monday, March 19, 2012
Is this a good scenario for a Service Broker application usage
Hi,
Please excuse me if I get some terms wrong - I am not a developer!!
I am working for a company who has a requirement to interface to a customer to query for work. The customer has an established Web Services that it uses for external interfacing, these services are passive at their end, in that it will be my clients who initiate all the communications using SOAP, receiving responses in XML.
There are, as I see it, seven conversations.
Receiving Datasets
1. Requests All new Jobs - This will be a post to a HTP with a list of job numbers sent back, These jobs will then need to be polled from the same webservice.
2. Job Changes - existing jobs that have changed definitions (costs etc..)- simular to above
3. Job Changes - Appointment Times
Sending Datasets
1. Completed Jobs - one at a time
2. Cancelled Jobs
3. Appointments (I believe this is responses to 1 and 3 above)
4. Subsequent Jobs - new work derived from initial Job.
I believe the sending datasets will be one way only (ie POST).
I have suggested using Service Broker for this application, with a custom App sitting inbetween ServiceBroker and the Web Service to deal with the HTTP POSTS and GETS, and communicating with the Service Broker, posting to queues and reading from them.
Is this viable?
Many Thaks
Lawrenso
We have found customers using Service Broker services to talk to webservices, a common scenario. If you build your apps as internally activated stored procedures (written using CLR), they will always run under the security context of the database engine service account and hence that principle will need access to the web from the machine on which the database engine is running. The other option is to use external applications (running as whatever user you want on whatever machine you choose) that connect to the database to RECEIVE messages from the queue and post requests to the web service. The second pattern does not have a built-in activation mechansim, but you could look at our External Activator sample (on GotDotNet codegallery and www.sqlservicebroker.com).
Rushi
Is there support for the ANS LIST Statement in SQL Server?
We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn Barber
You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net
|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it.
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Procedure?
Glenn
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn Barber
You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net
|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it.
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.
|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Procedure?
Glenn
Is there support for the ANS LIST Statement in SQL Server?
We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or s
ome publically available utility built with with SQLServer SP's? I will inv
estigate it.
As a software publisher there are lots of considerations for us and our cust
omers when straying outside of the vendor's supported syntax. It would be m
uch easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be sup
ported in a future release.|||It is not there and I have not seen it in the planned feature list for the n
ext version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some pu
blically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers
when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly
supported.
> I'd still like to know if the LIST command is supported or planned to be supported
in a future release.|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Pro
cedure?
Glenn
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or s
ome publically available utility built with with SQLServer SP's? I will inv
estigate it.
As a software publisher there are lots of considerations for us and our cust
omers when straying outside of the vendor's supported syntax. It would be m
uch easier if the ANS LIST syntax was directly supported.
I'd still like to know if the LIST command is supported or planned to be sup
ported in a future release.|||It is not there and I have not seen it in the planned feature list for the n
ext version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some pu
blically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers
when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly
supported.
> I'd still like to know if the LIST command is supported or planned to be supported
in a future release.|||Thanks Tibor
I will definitely send my request to the wishlist.
Is there an efficient way of creating an equivalent function in a Stored Pro
cedure?
Glenn
Is there support for the ANS LIST Statement in SQL Server?
We are considering moving our application to MS SQL Server from another
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported
I'd still like to know if the LIST command is supported or planned to be supported in a future release.|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.
database. When I have looked at the documentation I do not see any support
for the ANS LIST statement. (This is an aggregate like function which
allows for concatenation of String Results with Separators (e.g. creates a
string containing a list of items matching the WHERE clause separated by the
designated separator) ).
This is very handy and we use this frequently. IF SQL Server does not
support this feature, how could I implement an equivalent?
Thanks
Glenn BarberYou can do this very easily with the RAC utility/tool for S2k.
No sql coding required.
For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236
RAC v2.2 and QALite @.
www.rac4sql.net|||Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility built with with SQLServer SP's? I will investigate it
As a software publisher there are lots of considerations for us and our customers when straying outside of the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported
I'd still like to know if the LIST command is supported or planned to be supported in a future release.|||It is not there and I have not seen it in the planned feature list for the next version (which is still a year
away, though). I suggest you vent your need through sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Glenn Barber" <anonymous@.discussions.microsoft.com> wrote in message
news:A1F44D8D-E0AA-42C9-AF7F-B2A8A48829F1@.microsoft.com...
> Thanks - I don't know much about RAC. - Is this a proprietary product - or some publically available utility
built with with SQLServer SP's? I will investigate it.
> As a software publisher there are lots of considerations for us and our customers when straying outside of
the vendor's supported syntax. It would be much easier if the ANS LIST syntax was directly supported.
> I'd still like to know if the LIST command is supported or planned to be supported in a future release.
Friday, March 9, 2012
Is there anyway to do SUM on join table?
I would like to start using SQL Server CE rather than SQLite for an application I am developing, due to it's replication support for SQL Server however there appears to be a gaping hole in it that means I can't even consider it. Specifically to do with aggregates against detail tables.
Is there any way to do the equivalent of the following in SQL using SQL Server CE?
SELECT [Customer_Id], [Customer_name], COALESCE([OT].[Count_Orders],0) [CountOrders], COALESCE([OT].[Sum_Value],0) [OrderValue] FROM [Customers] LEFT OUTER JOIN (SELECT 
Any help would be most welcome
Adrian Heath
I think the short answer is no. The long answer is no, not in a single statement. One of the differences when you are working with such a lightweight database is that you need to adjust the way you do operations. Typically if you are writing a client application that uses sql server ce you can split the data processing load between the database (ie selecting records) and the application itself.
In the example you have provided you would have to do the aggregation by Customer_Id across the Orders table and then in memory do the join across to Customer information..
Alternatively you could just maintain an additional table with this information already aggregated - again because it is a client application and you are not expecting thousands of concurrent updates this becomes a workable solution.
|||This works fine (from Northwind sample database) (Ship Name = Customer Name)
SELECT Orders.[Ship Name], SUM([Order Details].[Unit Price]) AS Total
FROM [Order Details] INNER JOIN
Orders ON [Order Details].[Order ID] = Orders.[Order ID]
GROUP BY Orders.[Ship Name]
Hope this assists.
Monday, February 20, 2012
Is there any difference between Integrity and Constraint ?
For an EDMS application, there are two separate tasks for maintaining Data
Integrity and Constraint for the DB.
From my understanding, the Constraint is already includes the Referential
Integrity, is there any other meaning for Data Integrity ?
Your advice is sought.Hi,
You are correct; The data integrity itself is maintained using contraints.
Take a look into the below URL.
http://www.utexas.edu/its/windows/d...ing/rm/rm5.html
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:OL3Ha9OyGHA.1340@.TK2MSFTNGP05.phx.gbl...
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.
>
>|||Data integrity is a broad concept, being a desirable characteristic of
any database. It relates to the consistency, accuracy and validity of
the data.
Referential integrity is only one aspect of integrity. Referential
integrity is usually enforced using foreign key constraints, but can
also be enforced (with less performance and/or safety) using triggers
or other mechanisms (for example on the application side).
The constraints are rules or restrictions that are enforced at the
database level. There are several types of constraints:
- primary key constraints (that ensure entity integrity)
- foreign key constraints (that ensure referential integrity)
- check constraints (that ensure domain integrity and some simple
business rules)
More complex business rules are usually enforced using triggers.
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms184276.aspx
https://www.cs.tcd.ie/courses/baict...s/integrity.pdf
Razvan
John wrote:
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.|||Dear Razvan,
In other words, Data Integrity test may be testing of whether the data is
valid (for instance, within the valid range) .... while constraints test
may be checking the referential integrity ?
Thanks
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1156605409.337915.44370@.75g2000cwc.googlegroups.com...
> Data integrity is a broad concept, being a desirable characteristic of
> any database. It relates to the consistency, accuracy and validity of
> the data.
> Referential integrity is only one aspect of integrity. Referential
> integrity is usually enforced using foreign key constraints, but can
> also be enforced (with less performance and/or safety) using triggers
> or other mechanisms (for example on the application side).
> The constraints are rules or restrictions that are enforced at the
> database level. There are several types of constraints:
> - primary key constraints (that ensure entity integrity)
> - foreign key constraints (that ensure referential integrity)
> - check constraints (that ensure domain integrity and some simple
> business rules)
> More complex business rules are usually enforced using triggers.
> For more informations, see:
> http://msdn2.microsoft.com/en-us/library/ms184276.aspx
> https://www.cs.tcd.ie/courses/baict...s/integrity.pdf
> Razvan
> John wrote:
>|||The particular meanings of the terms "Data integrity test" and
"Constraints test" in a particular software may be different of what we
think about them. Please consult the user manual of that software or
contact the product support to get specific information.
Razvan
John wrote:
> Dear Razvan,
> In other words, Data Integrity test may be testing of whether the data is
> valid (for instance, within the valid range) .... while constraints test
> may be checking the referential integrity ?
> Thanks
Integrity and Constraint for the DB.
From my understanding, the Constraint is already includes the Referential
Integrity, is there any other meaning for Data Integrity ?
Your advice is sought.Hi,
You are correct; The data integrity itself is maintained using contraints.
Take a look into the below URL.
http://www.utexas.edu/its/windows/d...ing/rm/rm5.html
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:OL3Ha9OyGHA.1340@.TK2MSFTNGP05.phx.gbl...
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.
>
>|||Data integrity is a broad concept, being a desirable characteristic of
any database. It relates to the consistency, accuracy and validity of
the data.
Referential integrity is only one aspect of integrity. Referential
integrity is usually enforced using foreign key constraints, but can
also be enforced (with less performance and/or safety) using triggers
or other mechanisms (for example on the application side).
The constraints are rules or restrictions that are enforced at the
database level. There are several types of constraints:
- primary key constraints (that ensure entity integrity)
- foreign key constraints (that ensure referential integrity)
- check constraints (that ensure domain integrity and some simple
business rules)
More complex business rules are usually enforced using triggers.
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms184276.aspx
https://www.cs.tcd.ie/courses/baict...s/integrity.pdf
Razvan
John wrote:
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.|||Dear Razvan,
In other words, Data Integrity test may be testing of whether the data is
valid (for instance, within the valid range) .... while constraints test
may be checking the referential integrity ?
Thanks
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1156605409.337915.44370@.75g2000cwc.googlegroups.com...
> Data integrity is a broad concept, being a desirable characteristic of
> any database. It relates to the consistency, accuracy and validity of
> the data.
> Referential integrity is only one aspect of integrity. Referential
> integrity is usually enforced using foreign key constraints, but can
> also be enforced (with less performance and/or safety) using triggers
> or other mechanisms (for example on the application side).
> The constraints are rules or restrictions that are enforced at the
> database level. There are several types of constraints:
> - primary key constraints (that ensure entity integrity)
> - foreign key constraints (that ensure referential integrity)
> - check constraints (that ensure domain integrity and some simple
> business rules)
> More complex business rules are usually enforced using triggers.
> For more informations, see:
> http://msdn2.microsoft.com/en-us/library/ms184276.aspx
> https://www.cs.tcd.ie/courses/baict...s/integrity.pdf
> Razvan
> John wrote:
>|||The particular meanings of the terms "Data integrity test" and
"Constraints test" in a particular software may be different of what we
think about them. Please consult the user manual of that software or
contact the product support to get specific information.
Razvan
John wrote:
> Dear Razvan,
> In other words, Data Integrity test may be testing of whether the data is
> valid (for instance, within the valid range) .... while constraints test
> may be checking the referential integrity ?
> Thanks
Is there any difference between Integrity and Constraint ?
For an EDMS application, there are two separate tasks for maintaining Data
Integrity and Constraint for the DB.
From my understanding, the Constraint is already includes the Referential
Integrity, is there any other meaning for Data Integrity ?
Your advice is sought.Hi,
You are correct; The data integrity itself is maintained using contraints.
Take a look into the below URL.
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm5.html
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:OL3Ha9OyGHA.1340@.TK2MSFTNGP05.phx.gbl...
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.
>
>|||Data integrity is a broad concept, being a desirable characteristic of
any database. It relates to the consistency, accuracy and validity of
the data.
Referential integrity is only one aspect of integrity. Referential
integrity is usually enforced using foreign key constraints, but can
also be enforced (with less performance and/or safety) using triggers
or other mechanisms (for example on the application side).
The constraints are rules or restrictions that are enforced at the
database level. There are several types of constraints:
- primary key constraints (that ensure entity integrity)
- foreign key constraints (that ensure referential integrity)
- check constraints (that ensure domain integrity and some simple
business rules)
More complex business rules are usually enforced using triggers.
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms184276.aspx
https://www.cs.tcd.ie/courses/baict/baim/js/dbms/integrity.pdf
Razvan
John wrote:
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.|||Dear Razvan,
In other words, Data Integrity test may be testing of whether the data is
valid (for instance, within the valid range) .... while constraints test
may be checking the referential integrity ?
Thanks
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1156605409.337915.44370@.75g2000cwc.googlegroups.com...
> Data integrity is a broad concept, being a desirable characteristic of
> any database. It relates to the consistency, accuracy and validity of
> the data.
> Referential integrity is only one aspect of integrity. Referential
> integrity is usually enforced using foreign key constraints, but can
> also be enforced (with less performance and/or safety) using triggers
> or other mechanisms (for example on the application side).
> The constraints are rules or restrictions that are enforced at the
> database level. There are several types of constraints:
> - primary key constraints (that ensure entity integrity)
> - foreign key constraints (that ensure referential integrity)
> - check constraints (that ensure domain integrity and some simple
> business rules)
> More complex business rules are usually enforced using triggers.
> For more informations, see:
> http://msdn2.microsoft.com/en-us/library/ms184276.aspx
> https://www.cs.tcd.ie/courses/baict/baim/js/dbms/integrity.pdf
> Razvan
> John wrote:
>> For an EDMS application, there are two separate tasks for maintaining
>> Data
>> Integrity and Constraint for the DB.
>> From my understanding, the Constraint is already includes the Referential
>> Integrity, is there any other meaning for Data Integrity ?
>> Your advice is sought.
>|||The particular meanings of the terms "Data integrity test" and
"Constraints test" in a particular software may be different of what we
think about them. Please consult the user manual of that software or
contact the product support to get specific information.
Razvan
John wrote:
> Dear Razvan,
> In other words, Data Integrity test may be testing of whether the data is
> valid (for instance, within the valid range) .... while constraints test
> may be checking the referential integrity ?
> Thanks
Integrity and Constraint for the DB.
From my understanding, the Constraint is already includes the Referential
Integrity, is there any other meaning for Data Integrity ?
Your advice is sought.Hi,
You are correct; The data integrity itself is maintained using contraints.
Take a look into the below URL.
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm5.html
Thanks
Hari
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:OL3Ha9OyGHA.1340@.TK2MSFTNGP05.phx.gbl...
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.
>
>|||Data integrity is a broad concept, being a desirable characteristic of
any database. It relates to the consistency, accuracy and validity of
the data.
Referential integrity is only one aspect of integrity. Referential
integrity is usually enforced using foreign key constraints, but can
also be enforced (with less performance and/or safety) using triggers
or other mechanisms (for example on the application side).
The constraints are rules or restrictions that are enforced at the
database level. There are several types of constraints:
- primary key constraints (that ensure entity integrity)
- foreign key constraints (that ensure referential integrity)
- check constraints (that ensure domain integrity and some simple
business rules)
More complex business rules are usually enforced using triggers.
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms184276.aspx
https://www.cs.tcd.ie/courses/baict/baim/js/dbms/integrity.pdf
Razvan
John wrote:
> For an EDMS application, there are two separate tasks for maintaining Data
> Integrity and Constraint for the DB.
> From my understanding, the Constraint is already includes the Referential
> Integrity, is there any other meaning for Data Integrity ?
> Your advice is sought.|||Dear Razvan,
In other words, Data Integrity test may be testing of whether the data is
valid (for instance, within the valid range) .... while constraints test
may be checking the referential integrity ?
Thanks
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1156605409.337915.44370@.75g2000cwc.googlegroups.com...
> Data integrity is a broad concept, being a desirable characteristic of
> any database. It relates to the consistency, accuracy and validity of
> the data.
> Referential integrity is only one aspect of integrity. Referential
> integrity is usually enforced using foreign key constraints, but can
> also be enforced (with less performance and/or safety) using triggers
> or other mechanisms (for example on the application side).
> The constraints are rules or restrictions that are enforced at the
> database level. There are several types of constraints:
> - primary key constraints (that ensure entity integrity)
> - foreign key constraints (that ensure referential integrity)
> - check constraints (that ensure domain integrity and some simple
> business rules)
> More complex business rules are usually enforced using triggers.
> For more informations, see:
> http://msdn2.microsoft.com/en-us/library/ms184276.aspx
> https://www.cs.tcd.ie/courses/baict/baim/js/dbms/integrity.pdf
> Razvan
> John wrote:
>> For an EDMS application, there are two separate tasks for maintaining
>> Data
>> Integrity and Constraint for the DB.
>> From my understanding, the Constraint is already includes the Referential
>> Integrity, is there any other meaning for Data Integrity ?
>> Your advice is sought.
>|||The particular meanings of the terms "Data integrity test" and
"Constraints test" in a particular software may be different of what we
think about them. Please consult the user manual of that software or
contact the product support to get specific information.
Razvan
John wrote:
> Dear Razvan,
> In other words, Data Integrity test may be testing of whether the data is
> valid (for instance, within the valid range) .... while constraints test
> may be checking the referential integrity ?
> Thanks
Is there an option of adding style sheet for the SSRS application.
I want's to define my custom style for my report project.It should also be consistent.
I doesn't want to set it globally but on the project basis.
Is there any option of defining a style sheet for Sql server reporting services like we do for our web application.
No, there is no way to have a stylesheet for a set of reports in your report project.
You can only modify the style of the report manager by accessing the ReportingServices.css which is usually available in C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager\Styles.
Shyam
|||I have edited the reportingservices.css and then add a new report in my project but nothing happened.
I don't want to edit the style of the report manager .What I want is to edit the report style. [mean the rendering style of the actual report]
Suppose I have to set borderwidth of table =.25pt
bordercolor of table,all of it's cell to silver.
Borderstyle to be windowsinset.
Backgroundcolor of the full layout to be black
How Can I do that ?
|||As I said earlier, it is not possible.
Shyam
Is there an Online Remote SQL Server I can test against?
Hi
I am trying to connect an application to a remote sql server database, however it does not appear to eb configured for remote connections.
Does anyone know a connection string of a database I can connect to , soem sort of learnsers database sort of idea ?
Thank you.Fo anyone looking at the post ,just download SQL express
Is there an easy way to synchronize two tables in different databaes using a SQL
news.valornet.com wrote:
> Hi,
> I am going to be writing an application where a server has MS SQL 2K on it
> with some tables that I will need locally on some notebooks when they aren
't
> connected to the server. I need to also create other tables for my
> application so I figured I would create tables to match the ones I need fr
om
> the server in the same local database on these notebooks. I could probabl
y
> delete the local table and then use a select into from the server to refil
l
> it again, but is there a better way? Like some sort of synchronization
> where you specify both tables and only the changes from the source table a
re
> applied to the destination table?
> Thanks,
> Alan
> [url]http://www.
evelopment.com[/url]
> Home of Partition Boot Manager and Large Drive Tools utilities!
Are the laptops going to have SQL installed on them? If so,
transactional replication should do the trick. Publish the master
tables (from the server), and have each laptop subscribe to those
publications.Hi Tracy,
> Are the laptops going to have SQL installed on them? If so,
> transactional replication should do the trick. Publish the master
> tables (from the server), and have each laptop subscribe to those
> publications.
Thanks for responding.
What I am trying to do is pull a certain few tables from the server and
store them locally. I would prefer to do this with SQL code and a button in
my application called "Synchronize". The customer clicks synchronize and
then it executes some SQL to bring the local tables in sync with the network
ones. The tables aren't so large that they can't be completely emptied and
recopied from the network each time. I am writing in C++ using a component
called SDAC (www.crlab.com) to issue SQL commands. I can create two
connection components but each one can only see its connection to the server
it is configured for. I've taken a look ad sp_addlinkedserver, and wonder
if this is the direction I should go. Any ideas?
Thanks,
Alan
[url]http://www.evelopment.com[/url]
Home of Partition Boot Manager and Large Drive Tools utilities!
> Hi,
> I am going to be writing an application where a server has MS SQL 2K on it
> with some tables that I will need locally on some notebooks when they aren
't
> connected to the server. I need to also create other tables for my
> application so I figured I would create tables to match the ones I need fr
om
> the server in the same local database on these notebooks. I could probabl
y
> delete the local table and then use a select into from the server to refil
l
> it again, but is there a better way? Like some sort of synchronization
> where you specify both tables and only the changes from the source table a
re
> applied to the destination table?
> Thanks,
> Alan
> [url]http://www.
evelopment.com[/url]> Home of Partition Boot Manager and Large Drive Tools utilities!
Are the laptops going to have SQL installed on them? If so,
transactional replication should do the trick. Publish the master
tables (from the server), and have each laptop subscribe to those
publications.Hi Tracy,
> Are the laptops going to have SQL installed on them? If so,
> transactional replication should do the trick. Publish the master
> tables (from the server), and have each laptop subscribe to those
> publications.
Thanks for responding.
What I am trying to do is pull a certain few tables from the server and
store them locally. I would prefer to do this with SQL code and a button in
my application called "Synchronize". The customer clicks synchronize and
then it executes some SQL to bring the local tables in sync with the network
ones. The tables aren't so large that they can't be completely emptied and
recopied from the network each time. I am writing in C++ using a component
called SDAC (www.crlab.com) to issue SQL commands. I can create two
connection components but each one can only see its connection to the server
it is configured for. I've taken a look ad sp_addlinkedserver, and wonder
if this is the direction I should go. Any ideas?
Thanks,
Alan
[url]http://www.
evelopment.com[/url]Home of Partition Boot Manager and Large Drive Tools utilities!
Subscribe to:
Posts (Atom)