Primary Keys

I have managed to confuse myself as to when and how you can set up Primary
Keys on an MSDE database.
Are Primary Keys only used when you are connected to an MS Access project or
can they be used in other places.
I come from an MS Access background and am familure with the use of Primary
Keys my problem is that I cannot seem to turn them on. I already have a
clustered index set up on the table and am using Identidy to declare a field
as unique. Could this be the reason why I cannot turn on the Primary Key
option?
Any insight would be useful.
Thanks
June
> Keys my problem is that I cannot seem to turn them on.
What does this mean? How are you trying to "turn them on"? What tool are
you using, and are you using ALTER TABLE or clicking buttons in a GUI? Are
you getting an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I have tried using Andrea Montanari's DbaMGR2k and Microsoft Web Data
Administrator.
Using DbaMGR2k - When logged on as dbo I cannot check the checkbox beside
Primary Key although I can change all the other field settings. There is no
message, simply nothing happens.
Using Microsoft Web Data - when editing a table I can set the Primary Key
only on tables that have no rows of data in them. As soon as there is data
in the table the option to edit the fields is removed.
I want to be able to set up Primary Keys and Foreign Keys in order to set up
a relationship. I also want my tables to have a clustered index which in
most, but not all, cases will be the same field as the Primary Key.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OraGgF%23cEHA.3512@.TK2MSFTNGP12.phx.gbl...
> What does this mean? How are you trying to "turn them on"? What tool are
> you using, and are you using ALTER TABLE or clicking buttons in a GUI?
Are
> you getting an error message? If so, what is it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||If you can use osql or isql to connect to MSDE, you can issue an ALTER TABLE
/ ADD CONSTRAINT command, which isn't limited by what an application thinks
you should/shouldn't be able to do. Barring that, you can create a dummy
table with the primary key intact, copy the data over, drop the old table,
and rename the new table. If these tools will let you do those things too,
of course.
http://www.aspfaq.com/
(Reverse address to reply.)
"June Macleod" <junework@.hotmail.com> wrote in message
news:#xwOW39cEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have managed to confuse myself as to when and how you can set up Primary
> Keys on an MSDE database.
> Are Primary Keys only used when you are connected to an MS Access project
or
> can they be used in other places.
> I come from an MS Access background and am familure with the use of
Primary
> Keys my problem is that I cannot seem to turn them on. I already have a
> clustered index set up on the table and am using Identidy to declare a
field
> as unique. Could this be the reason why I cannot turn on the Primary Key
> option?
> Any insight would be useful.
> Thanks
> June
>
|||hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:eKX7Vb%23cEHA.1644@.tk2msftngp13.phx.gbl...
> I have tried using Andrea Montanari's DbaMGR2k and Microsoft Web Data
> Administrator.
> Using DbaMGR2k - When logged on as dbo I cannot check the checkbox beside
> Primary Key although I can change all the other field settings. There is
no
> message, simply nothing happens.
as regard DbaMgr2k, the help file specifies the Primary Key can not be set
in the "Table Management" window, where the check is read only, but in the
Indexes/Keys/Check window =;-D
so you have to access that window, both for PKs and indexes management as
for Foreign Keys management, as DbaMgr2k does not implement Diagrams
management [ =:-( , I know ]

> Using Microsoft Web Data - when editing a table I can set the Primary Key
> only on tables that have no rows of data in them. As soon as there is
data
> in the table the option to edit the fields is removed.
yes, you are right... WDA will not allow you to edit existing columns when
the base table is populated (sqlwish@.microsoft.com =;-D )

> I want to be able to set up Primary Keys and Foreign Keys in order to set
up
> a relationship. I also want my tables to have a clustered index which in
> most, but not all, cases will be the same field as the Primary Key.
and you can do it... with WDA you have to resort on executing Transact-SQL
statements, becouse it only supports PK setting (AFAIK)... with my tool you
can do it both graphically (but not in the Table Management window =;-D)
and/or performing Transact-SQL statements execution...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> as regard DbaMgr2k, the help file specifies the Primary Key can not be set
> in the "Table Management" window, where the check is read only, but in the
> Indexes/Keys/Check window =;-D
> so you have to access that window, both for PKs and indexes management as
> for Foreign Keys management, as DbaMgr2k does not implement Diagrams
> management [ =:-( , I know ]
>
Thank you, that works
June

Primary filegroup is full.

I seem to be having problems with a very simple install of MSDE on a
Windows 2000 Server. When attempting to add new records, I receive
the message "Could not allocate space for object 'MyTable' in database
'MyDatabase' because the 'PRIMARY' filegroup is full."

I have dealt with countless issues like this in the past and they have
been easily resolved due to max limit of MSDE, trans log full, or the
hard drive where the database is stored is full. None of these appear
to be the case in this instance. The system has been working fine for
about a year.

The Master database is 11 MB. MyDatabase is 33 MB. The log files are
both under 1 MB. I have 730 MB of storage available on the drive
where the database is stored.

I downloaded MSDE off of Microsoft's website and installed with the
following switches: BLANKSAPWD=1 SECURITYMODE=SQL. I create a user
in MSDE that is the DBO for MyDatabase. Other than that, I make no
changes to the default installation.Giadis (lonewolf3557@.hotmail.com) writes:
> I seem to be having problems with a very simple install of MSDE on a
> Windows 2000 Server. When attempting to add new records, I receive
> the message "Could not allocate space for object 'MyTable' in database
> 'MyDatabase' because the 'PRIMARY' filegroup is full."
> I have dealt with countless issues like this in the past and they have
> been easily resolved due to max limit of MSDE, trans log full, or the
> hard drive where the database is stored is full. None of these appear
> to be the case in this instance. The system has been working fine for
> about a year.
> The Master database is 11 MB. MyDatabase is 33 MB. The log files are
> both under 1 MB. I have 730 MB of storage available on the drive
> where the database is stored.

Have you verified with sp_helpdb that the database has "Unlimited" in
maxsize and 10% in Growth? These are values in the second result set.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Preventing customers from hacking my MSDE database?

Recently I installed a commercial client-only product utilizing an
MSDE database.
The product installed its own instance of MSDE rather than adding a
database to the default instance (or install a default instance).
Of course, not with a blank sa password, so I could not access it
just by firing up Enterprise Manager or Query Analyzer.
Still, I was curious about the structure of the database, so
all I had to do to gain access to the table structure and data was to
stop the service for the MSDE instance, locate the database and log
files, copy them to another location, and attach them to the default
instance. I now had access to the table structure and all the data.
Still, the triggers and stored procedures were stored with encryption,
but this was no problem for a tool like Orbital's SQL Decryptor or a
couple of other scripts freely available on the Internet.
Question is: Is there anything the author could have done to prevent
me from doing any of the above? Or to put it another way: Is there
anything I can do to prevent others from accessing data in my own
programs in unauthorized ways?
TIA,
JBHave you tried to remove the user from System Admin server role and give
them an appropriate permissions?
"Joergen Bech @. post1.tele.dk>" <jbech<NOSPAMNOSPAM> wrote in message
news:cjdpn0hjt5dtilasl28hg29g9qt76c0v5o@.
4ax.com...
> Recently I installed a commercial client-only product utilizing an
> MSDE database.
> The product installed its own instance of MSDE rather than adding a
> database to the default instance (or install a default instance).
> Of course, not with a blank sa password, so I could not access it
> just by firing up Enterprise Manager or Query Analyzer.
> Still, I was curious about the structure of the database, so
> all I had to do to gain access to the table structure and data was to
> stop the service for the MSDE instance, locate the database and log
> files, copy them to another location, and attach them to the default
> instance. I now had access to the table structure and all the data.
> Still, the triggers and stored procedures were stored with encryption,
> but this was no problem for a tool like Orbital's SQL Decryptor or a
> couple of other scripts freely available on the Internet.
> Question is: Is there anything the author could have done to prevent
> me from doing any of the above? Or to put it another way: Is there
> anything I can do to prevent others from accessing data in my own
> programs in unauthorized ways?
> TIA,
> JB
>
>|||Basically no. If it is installed on their server then most likely they will
have the ability to access your files and or the db directly. Your best
recourse is to have very good copywrite, NDA's etc. in place.
Andrew J. Kelly SQL MVP
"Joergen Bech @. post1.tele.dk>" <jbech<NOSPAMNOSPAM> wrote in message
news:cjdpn0hjt5dtilasl28hg29g9qt76c0v5o@.
4ax.com...
> Recently I installed a commercial client-only product utilizing an
> MSDE database.
> The product installed its own instance of MSDE rather than adding a
> database to the default instance (or install a default instance).
> Of course, not with a blank sa password, so I could not access it
> just by firing up Enterprise Manager or Query Analyzer.
> Still, I was curious about the structure of the database, so
> all I had to do to gain access to the table structure and data was to
> stop the service for the MSDE instance, locate the database and log
> files, copy them to another location, and attach them to the default
> instance. I now had access to the table structure and all the data.
> Still, the triggers and stored procedures were stored with encryption,
> but this was no problem for a tool like Orbital's SQL Decryptor or a
> couple of other scripts freely available on the Internet.
> Question is: Is there anything the author could have done to prevent
> me from doing any of the above? Or to put it another way: Is there
> anything I can do to prevent others from accessing data in my own
> programs in unauthorized ways?
> TIA,
> JB
>
>