Tuesday, March 27, 2012
can't block delete permissions
d the table’s ‘manage permissions’ dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database
role membership and saw that she was a member of the db_owner role, so I rev
oked that. I then ran a DENY statement: "deny delete on Histories to edenr".
I removed her memberships in the db_accessadmin and db_securityadmin roles,
and had her close and reop
en Enterprise Manager. After all that, she was still able to delete records.
The manage permissions dialog for this table shows that she is denied delete
permissions. She is still a member of the public, db_datareader, and db_dat
awriter groups, but that shouldn’t override explicitly denied permissions.
I’m the dbo of the datab
ase, so I certainly should have sufficient rights to issue a denial.
What does it TAKE to block a programmer from having permission to delete rec
ords?Yes but what Login is Enterprise Manager using? It is probably not hers.
Andrew J. Kelly SQL MVP
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Hi,
Check the role associated for the user first by executing below command:-
sp_helplogins <Login_name_for that _user'
If you have any roles apart from db_datareader and db_datawriter revoke
that.
After this Execute the below command
use <dbname>
go
deny delete on <table_name> to <user_name>
After that login to query analyzer using that user and run the command:-
select suser_sname()
Now execute the delete statatement on that table.
Thanks
Hari
MCDBA
"eachus" <eachus@.discussions.microsoft.com> wrote in message
news:A4C20AFD-E526-4EFD-BAE0-42125FE1641F@.microsoft.com...
> I'm trying to lock down an audit table in our database. As a test, I
opened the table's 'manage permissions' dialog and explicitly denied delete
permission to one of our programmers. She was still able to delete records.
We looked at her database role membership and saw that she was a member of
the db_owner role, so I revoked that. I then ran a DENY statement: "deny
delete on Histories to edenr". I removed her memberships in the
db_accessadmin and db_securityadmin roles, and had her close and reopen
Enterprise Manager. After all that, she was still able to delete records.
> The manage permissions dialog for this table shows that she is denied
delete permissions. She is still a member of the public, db_datareader, and
db_datawriter groups, but that shouldn't override explicitly denied
permissions. I'm the dbo of the database, so I certainly should have
sufficient rights to issue a denial.
> What does it TAKE to block a programmer from having permission to delete
records?
>|||Thanks for the suggestions. I tried this, and got the same result. It did ha
ve the effect of re-confirming that the deletions were being run under the p
ermissions of the user in question, which was useful.
The goal here is to be able to block anybody, including programming team mem
bers, from being able to delete records in the production database's audit t
able.
Got any other suggestions where she might be getting delete permissions that
override the explicit denial?
"Hari" wrote:
> Hi,
> Check the role associated for the user first by executing below command:-
> sp_helplogins <Login_name_for that _user'
> If you have any roles apart from db_datareader and db_datawriter revoke
> that.
> After this Execute the below command
> use <dbname>
> go
> deny delete on <table_name> to <user_name>
> After that login to query analyzer using that user and run the command:-
> select suser_sname()
> Now execute the delete statatement on that table.
>|||Check server roles as well. Maybe she is a member of
sysadmins either directly or through windows group
membership
-Sue
On Fri, 2 Jul 2004 09:07:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the suggestions. I tried this, and got the same result. It did h
ave the effect of re-confirming that the deletions were being run under the
permissions of the user in question, which was useful.
>The goal here is to be able to block anybody, including programming team me
mbers, from being able to delete records in the production database's audit
table.
>Got any other suggestions where she might be getting delete permissions tha
t override the explicit denial?
>"Hari" wrote:
>|||Thanks--it looks like that was it. Most of our programmers, including the on
e I'm using as a test case, are members of the System Adminstrators role, an
d the System Adminstrators role has delete permissions on any object in any
database.
All domain admins are automatically members of the sysadmins role, so anyone
who is a domain admin can't be removed from the group even if I decided tha
t was the best solution.
It looks like permissions granted due to membership in the sysadmins role ca
n't be overridden by a denial? Is there any way to override these permission
s in a particular database?
"Sue Hoegemeier" wrote:
> Check server roles as well. Maybe she is a member of
> sysadmins either directly or through windows group
> membership|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: 0-1pool76-99.nas29.thornton1.co.us.da.qwest.net 67.4.76.9
9
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.security:21673
Good - glad to hear that helped you track it down.
On the sysadmins, someone who is a member of the role can do
everything. Members of this role bypass any denies you set
up for them. You can't override this on any level, not by
database or anything else. They can do whatever.
Regarding domain admins, they get their access through the
BUILTIN\Administrators group in SQL Server that is by
default a member of sysadmins. You can remove the
BUILTIN\Administrators but doing this can cause some
problems. Whether you get problems or not depends. The
following article has an more information section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Tue, 6 Jul 2004 11:38:02 -0700, Eachus
<Eachus@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks--it looks like that was it. Most of our programmers, including the o
ne I'm using as a test case, are members of the System Adminstrators role, a
nd the System Adminstrators role has delete permissions on any object in any
database.
>All domain admins are automatically members of the sysadmins role, so anyon
e who is a domain admin can't be removed from the group even if I decided th
at was the best solution.
>It looks like permissions granted due to membership in the sysadmins role c
an't be overridden by a denial? Is there any way to override these permissio
ns in a particular database?
>"Sue Hoegemeier" wrote:
>
Sunday, March 25, 2012
Can't Add Tables To A Diagram
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
Jerry
Hi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Can't Add Tables To A Diagram
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
JerryHi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql
Can't Add Tables To A Diagram
I can create a blank diagram for my database, but when I try to add a table
I get "Invalid Class String" - regardless of the table I try to add. I
can't find anything that addresses this issue. Anyone have an answer on how
to get past it? Thanks.
JerryHi Jerry,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood when you want to create the diagram
via SQL Server Enterprise Manager, you will encounter the error message
"Invalid Class String". If I have misunderstood your concern, please feel
free to point it out.
Based on my knowledge, it seems some DLL in the SQL Server Client Tools is
corrupted. You will have to reinstall the SQL Server 2000 Client Tools and
then re-apply the Service Pack on the server.
If the reinstallation also fails, please attach the sqlstp.log and
sqlsp.log in the newsgroup for my further research. Thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, March 22, 2012
cant add new columns to data tables
Hi All,
I am having a problem for which i dont find any reasons ..I hope to get a solution from here.
I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.
The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.
I cant even drop this view.
Please any help on this??
ThanksThe question is what type of view did you create? If it is updatable view you may have to update the parent table because a view is just a query rewrite that is persisted. Run a search for views and restrictions in the BOL (books online). I wrote a tutorial on SQL Server views a while back use my email in my profile for short info to get you started on resolving your problem. Hope this helps.
Can't Add a linked table to SQL Server 2005 with Access 2003
Hi,
I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.
Any suggestions?
I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->
http://www.microsoft.com/office/community/en-us/default.mspx
My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.
|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.
Thanks for the info.
Can't Add a linked table to SQL Server 2005 with Access 2003
Hi,
I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.
Any suggestions?
I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->
http://www.microsoft.com/office/community/en-us/default.mspx
My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.
|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.
Thanks for the info.
sqlCant access table due to no owner (I think)
{SQL-DMO} The name 'Table Name' was not found in the tables collection. If the name is a qualified name, use [] to seperate various parts of the name, and the try again.
Have tried to change the owner by using sp_changeobjectowner but I am either getting the syntax wrong or it ain't working.
Any ideas??
c8lWhen you look in Enterprise Manager, what do you see?|||Hi,
The only thing I see is the table name (TABLE1), the type (USER) and the creation date. The owner column is blank. Normally you would expect it to say dbo.|||Blank?
What happends when you run this in QA?
SELECT * FROM INFORMATION_SCHEMA.Tables
Maybe you need to close EM and relaunch it. ...|||Thanks for the help but the problem has been resolved, I had to manually update the sysobject table so that the UID for each of the objects that could not be accessed was set to 1. For some reason this was 6.
Cheers
c8l
Tuesday, March 20, 2012
Can't access linked server through Query Analyzer
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
Thanks for your help
Israel,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Manager -- this should open the QA connection with the same credentials with which the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is the linked server name correct? Did you check on your server "B" that "SELECT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from Table') syntax?
4. Can you double check your linked server settings match the settings (e.g. Provider) that you have on the servers which are able to execute the query successfully.
Thanks,
Tea C.
"Israel Perez" wrote:
> Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
> Thanks for your help
Can't access linked server through Query Analyzer
ager. When I log into Query Analyzer, I try to access the remote tables with
the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlse
rver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it wo
rks fine. Other servers have a linked server to this same server B (PGJE_SP_
06) and they work fine.
Thanks for your helpIsrael,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Mana
ger -- this should open the QA connection with the same credentials with wh
ich the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is
the linked server name correct? Did you check on your server "B" that "SELE
CT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from
Table') syntax?
4. Can you double check your linked server settings match the settings (e.g.
Provider) that you have on the servers which are able to execute the query
successfully.
Thanks,
Tea C.
"Israel Perez" wrote:
> Hi, i created a linked server and can see the tables shown in Enterprise M
anager. When I log into Query Analyzer, I try to access the remote tables wi
th the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sql
server authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it
works fine. Other servers have a linked server to this same server B (PGJE_S
P_06) and they work fine.
> Thanks for your helpsql
Cant Access a Database via the Database Explorer
Hi,
I have been able to view the databases and all of their tables but now when I cilck the "+" next to the database or try to connect a new one i get an error message:
"Generating user instances in SQL Server is disabled. Use sp_configure 'user Instances enabled' to generate user instances"
I don't know what I have done and I don't know how to fix it, Please help me!
Best regards,
Elliot
I would do a backup first, before trying anything! Two backups!
It sounds like Sql Server wants you to enter the following command in a query window:
exec sp_configure 'user Instances enabled'
But you might want to google on that first...
|||
Thanks you didn't answer it 100% but I googled it and found the answer in another forum:http://www.mydatabasesupport.com/forums/sqlserver-faq/339672-generating-user-instances-sql-server-disabled.html
Thanks for your help,
Elliot
Thursday, March 8, 2012
Cannot update identity column
don't know the language). I have a column in one of the tables that is an
autonumber as well as a primary key. Here is the problem, the autonumber
was created to display the current year and a number. Example, 2004001,
2004002, 2004003, etc. Now that the year has changed, the column is still
generating numbers for 2004, when I try to update the field and change a
record for example: Update ABC set rptnum = '2005001' where rptnum =
'2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
need to change the value to reflect 2005001, I also need the next record
enter to automaticly get the next number ex. 2005002, 2005003, etc.
Thanks in advance,vickie wrote:
> I have just taken over a sqlserver 2k database using C# (not to
> mention I don't know the language). I have a column in one of the
> tables that is an autonumber as well as a primary key. Here is the
> problem, the autonumber was created to display the current year and a
> number. Example, 2004001, 2004002, 2004003, etc. Now that the year
> has changed, the column is still generating numbers for 2004, when I
> try to update the field and change a record for example: Update ABC
> set rptnum = '2005001' where rptnum = '2004257' I get 'Cannot update
> identity column 'rptnum''. Not only do I need to change the value to
> reflect 2005001, I also need the next record enter to automaticly get
> the next number ex. 2005002, 2005003, etc. Thanks in advance,
AutoNumber sounds like an Access term. SQL Server uses IDENTITY
attribute on integer-type columns. THe first thing you should know about
identity columns is that they are not really designed to be intelligent
keys. There are usually used as a surrogate key, where the internal
value is meaningless.
Your inherited application is using them as intelligent keys, which I
don't think is a really good idea. Identity columns cannot be updated
directly. This is by design to prevent chaning values which SQL Server
is trying to make sure are unique.
You can update an identity by using the SET IDENTITY_INSERT ON / OFF
function before the update. In any case, you'll need to reseed the
identity value before you do this to get it to start pulling numbers
from your desired range.
You can reseed the identity using the DBCC CHECKIDENT function. See BOL
for more information on the actual parameters for the call. Reseed
first, leaving enough range for you to make the updates to the incorrect
identity values and then update the old rows. And then reconsider the
use of an identity on this table. Maybe an identity column for the PK
and a data/time column for dating would be a better idea.
David Gugick
Imceda Software
www.imceda.com|||By default, identity inserts are prohibited. However, by calling
SET IDENTITY_INSERT <table> ON/OFF
you can alter this behaviour.
So you may turn identity inserts on, insert a single new record and turn
it off again.
/Jo
vickie wrote:
> I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Hopefully, the person who designed this table got fired - or sent on a
course to learn something about SQL Server! ;-)
Using IDENTITY (the proper term for an "autonumber" column). as a
"meaningful" key is very wrong. I suggest that your best bet is to add
a DATETIME column (if you don't have one already) to represent the date
and then ignore the IDENTITY values. Set a default of the current date
on the DATETIME column. That will likely require some application
changes but the alternative of trying to control the IDENTITY value is
a very messy kludge.
The short term fix is to use DBCC CHECKIDENT to change the identity
seed value:
DBCC CHECKIDENT (YourTable, RESEED, 2005001)
Then delete and re-insert the rows that have the wrong ID (You cannot
UPDATE an IDENTITY value).
If you do resort to the kludge rather than fix the table properly then
you will obviously have the same problem again next year.
Hope this helps.
David Portas
SQL Server MVP
--|||First the preaching. The identity property of a column is not good for what
you are doing with it. It is only an effective tool for building a
surrogate key. It is not meant to be modified, so it makes a bad value to
show to users. Reconsider how you are doing this, and just make the column
an integer that you insert values into. I also assume that you probably
don't want the chance of getting gaps in the sequence either, which is
common with identities.
As for what you actually asked: You cannot (as the error says) update
identity values. You will have to recreate your table, insert the existing
values (can't be more than a thousand rows, apparently.) You can change
the seed:
dbcc checkident(<tablename>,reseed,20050000)
Here is a test script to show you it in action.
create table testIdentity
(
id int identity (2004001,1),
value varchar(10)
)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
*/
dbcc checkident(testIdentity,reseed,2005000)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
2005001 stuff
2005002 stuff
2005003 stuff
2005004 stuff
*/
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"vickie" <victoria.wheaton@.ngc.com> wrote in message
news:c6e431cc1937df694405d2d97d6d6c6e@.lo
calhost.talkaboutsoftware.com...
>I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Yeah, but the question was can you UPDATE the identity value. Which you
cannot, even with this setting set.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:eEb4AYtCFHA.1296@.TK2MSFTNGP10.phx.gbl...
> By default, identity inserts are prohibited. However, by calling
> SET IDENTITY_INSERT <table> ON/OFF
> you can alter this behaviour.
> So you may turn identity inserts on, insert a single new record and turn
> it off again.
> /Jo
> vickie wrote:
>|||>> You can update an identity by using the SET IDENTITY_INSERT ON / OFF
Are you sure?
Identity columns are non-updateable. The only way one could alter the
identity column value is to delete and insert the same data within a single
transaction. But then, any existing references on this column could all be
messed up.
Anith|||Anith Sen wrote:
> Are you sure?
> Identity columns are non-updateable. The only way one could alter the
> identity column value is to delete and insert the same data within a
> single transaction. But then, any existing references on this column
> could all be messed up.
Oops. Talking about updates and inserts before coffee is a bad idea.
David Gugick
Imceda Software
www.imceda.com|||On 4 Feb 2005 08:33:38 -0800, David Portas wrote:
(snip)
>If you do resort to the kludge rather than fix the table properly then
>you will obviously have the same problem again next year.
Or a much bigger problem if the company fares well and more than 999 new
rows are inserted during this year......
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.
You have all of the horse manure and probably cannot kill the guy who
screwed this up. Bummer.
Rows are not records; fields are not columns; tables are not files.
More importantly, autonumbers (IDENTITY, GUID, ROWID, et al) are not
keys by definition. They are exposed physical locators and have
nothing to do the logicl data model.
You need to start over and move the data to a new schema. If you
business logic is to use the 1950's IBM magnetic tape label codes, then
write a procedure to build them.
I also hope that your business rules are not to grow beyond 1000
transactions per year -- plan stagnation and failure rather than
letting it happen.
Sunday, February 19, 2012
cannot set name conflicts
I set the Name conflicts from the defualt-DROP the existing tables and
re-create it
to Delete All data in the existing table.
and set check in Include declared refertial integrity in Copy objects to
destination secton.
After I close these perperties window and reopen the publiction property
window to check the property on snapshop tab on article properties. I found
both the properties goes back to default values again
I am unable to repro your problem. Is this on SQL 2000 or SQL 7? Also which
sp?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chen" <mxchen100@.hotmail.com> wrote in message
news:%231XSwgo2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> For a snapshot replication.
> I set the Name conflicts from the defualt-DROP the existing tables and
> re-create it
> to Delete All data in the existing table.
> and set check in Include declared refertial integrity in Copy objects to
> destination secton.
> After I close these perperties window and reopen the publiction property
> window to check the property on snapshop tab on article properties. I
found
> both the properties goes back to default values again
>
>
|||on SQL 2000
Table
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%236uJRJu2FHA.476@.TK2MSFTNGP15.phx.gbl...
> I am unable to repro your problem. Is this on SQL 2000 or SQL 7? Also
which[vbcol=seagreen]
> sp?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chen" <mxchen100@.hotmail.com> wrote in message
> news:%231XSwgo2FHA.1572@.TK2MSFTNGP10.phx.gbl...
property
> found
>
>
|||which sp? can you do a select @.@.version from QA?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chen" <mxchen100@.hotmail.com> wrote in message
news:uUXNF7w2FHA.2292@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> on SQL 2000
> Table
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%236uJRJu2FHA.476@.TK2MSFTNGP15.phx.gbl...
> which
to
> property
>
>
Thursday, February 16, 2012
cannot see the 2nd table(2 tables returned from stored procedure) in dataset in report designer
Hi,
I am using stored procedure(SP) to create the dataset in report designer. My SP is returning 2 tables/recordset and i am only able to see first table/recordset in the dataset in report designer. I have to place fields from second table onto my report. Is there anyway i could get the 2nd table in my dataset so that i could place the fields in my report.
Please help
This is by design. We'll only look at the first resultset.
You create a second sproc to return the second resultset and build a second dataset to consume it.
|||
Hi
I am also facing the same problem. Could you help how to write a stored procedure to retrieve the second table from stored procedure. So that I will create second dataset as you said.
Thanks & Regards
Kumar
cannot see the 2nd table(2 tables returned from stored procedure) in dataset in report designer
Hi,
I am using stored procedure(SP) to create the dataset in report designer. My SP is returning 2 tables/recordset and i am only able to see first table/recordset in the dataset in report designer. I have to place fields from second table onto my report. Is there anyway i could get the 2nd table in my dataset so that i could place the fields in my report.
Please help
This is by design. We'll only look at the first resultset.
You create a second sproc to return the second resultset and build a second dataset to consume it.
|||
Hi
I am also facing the same problem. Could you help how to write a stored procedure to retrieve the second table from stored procedure. So that I will create second dataset as you said.
Thanks & Regards
Kumar
Tuesday, February 14, 2012
Cannot see index in Object Explorer on Replicated Database
I have replicated all the tables from the Business Data database to my instance of SQL Server 2005. They did not create primary keys on the tables, so I am adding them on the replicated database. I was trying to save one of the tables after adding a new primary key and got this error message:
'BuildingValues' table
- Unable to create index 'PK_BuildingValues'. CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.BuildingValues' and index name 'PK_BuildingValues'. The duplicate key value is (2). Could not create constraint. See previous errors. The statement has been terminated.
The problem is that there is nothing displayed in the Indexes folder for this table in Object Viewer (or any of the other folders for this table), so how would I know an index already existed? The index shows up for this table on the Publisher database, but not the Replicated database. Is this normal?
Sounds like you only replicated the data over without transfering other objects (i.e. pk, constraints, indexes, etc.). The error here is that your current data is not qualified for a pk constraint - pk creates an unique key/constraint underneath, thus, the values cannot be duplicated.To find the dupes, try:
select [your_pk_col]
from tb
group by [your_pk_col]
having count(*)>1
Resolve that and you should be able to create your pk.
Indexes should get created on the subscriber if you configure it during publication creation - go to the object properties.|||
Thank you very much for your answer. The articles at the Publisher were configured to replicate all the keys and constraints to the Subscriber. You were right about the cause of the problem. The index could not be created, because of duplicate values in the column on the Subscriber. Once I added another column to make it unique, the primary key worked fine. Thanks again.
Sunday, February 12, 2012
Cannot ROLLBACK in a distributed transaction!
Any help is welcome ...By the very definition of the word "transaction" you should be able to either commit or rollback them, regardless of the fact that you are working in a heterogenous environment. What exactly is the problem, error message, platforms, etc...|||Hi,
This is because the operation may not have rolled back all effects of the operation. Since some sites may be inconsistent with others, the transaction must roll back to a savepoint or roll back entirely.|||HI,
You either Roll back to a savepoint or roll back the entire transaction and resubmit the rolled-back statements.|||ODBC Error Code = S1000 (General error)
[Oracle][ODBC][Ora]ORA-02074: cannot ROLLBACK in a distributed transaction ORA-06512: at "***.COPY_CURS_TOT_TO_NEW", line 460 ORA-00911: invalid character ORA-06512: at line 1
The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (287:2) to (287:76).
That's the error ... I just don't see it ... this is the error message of the coldfusion server I'm working with, the procedure doesn't give errors itself. I putted it in SQL*Plus and all I had was 'Procedure is aangemaakt' -> Dutch for 'Procedure created'
In Oracle DBA Studio the status of the procedure is 'VALID', so ... dunno.
Any help is welcome!|||Well, sounds no good
ORA-02074 cannot string in a distributed transaction
------------------
Cause: A commit or rollback was attempted from session other than the parent of a distributed transaction.
Action: Only commit or roll back from the parent session.
I will see if I can get my hands on some more info about the workings of Two-Phase commit. Maybe you can check the Oracle web-site for more details.
Cannot return results of temporary table
I've written a stored procedure which loops over several tables, processes
the data and stores the processed results in a temporary table.
At the end of the procedure, I'd like to return a 'select *' from the
temporary table to an ASP page, from where I call the stored procedure.
The procedure works, I can execute it and view the results in my Query
Analyzer, but I get an error when I call the procedure from my ASP page.
The error I receive is 'Operation is not allowed when the object is
closed'. I've read on the net that a temporary table is closed and removed
as soon as the stored procedure ends - could this be the problem?
The line on which the error occurs is:
if not (rec.eof) and not (rec.bof) then
(the place where i first use the recordset given to me by the stored
procedure.
Anyway, I'm at a loss here as to how I can solve this problem. Any help is
greatly appreciated.
Thanks,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricksJust add "set nocount on" as the very first line after "as" in your sproc.
-oj
"jcsnippets.atspace.com" <admin@.jcsnippets.atspace.com> wrote in message
news:Xns97805DB2D943Cadminjcsnippetsatsp
a@.195.238.0.34...
> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks|||hi,
Could you please so kind to show us that code snippet?
--
current location: alicante (es)
"jcsnippets.atspace.com" wrote:
> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks
>|||"oj" <nospam_ojngo@.home.com> wrote in
news:OfUOBnoQGHA.1160@.TK2MSFTNGP09.phx.gbl:
> Just add "set nocount on" as the very first line after "as" in your
> sproc.
>
Alas, that did not solve my problem...
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks|||examnotes <Enric@.discussions.microsoft.com> wrote in
news:2E51C9AF-3A4A-4277-926E-578691A83FCF@.microsoft.com:
> hi,
> Could you please so kind to show us that code snippet?
Of course - I should have posted this right away. Here is the code - if I
leave out the code marked '-- problem --', and only do the test insert,
the code works fine.
-- Code
alter procedure log_getProductHits
(
@.custid int,
@.category int
)
as
set nocount on
begin
declare @.seq1 int
declare @.seq2 int
declare @.name1 varchar(100)
declare @.name2 varchar(100)
declare @.count1 int
declare @.count2 int
create table #temphits
(
product varchar(100),
hits int
)
insert into #temphits values ('product', 15)
-- PROBLEM --
-- Get category level 1
if (@.category is null)
begin
declare cat1 cursor for
select seq
from cat_pd_cat
where custid = @.custid
and parent_seq is null
end
else
begin
declare cat1 cursor for
select seq
from cat_pd_cat
where custid = @.custid
and parent_seq = @.category
end
open cat1
fetch cat1 into @.seq1
while (@.@.FETCH_STATUS = 0)
begin
print 'Seq1: ' + cast(@.seq1 as varchar)
-- Insert valid products
declare prod1 cursor for
select cat_pd_product.name, count(*)
from cat_dom_productlog,
cat_pd_product
where cat_dom_productlog.custid = @.custid
and cat_dom_productlog.cat_pd_cat = @.seq1
and cat_pd_product.seq =
cat_dom_productlog.cat_pd_product
group by cat_pd_product.name
order by cat_pd_product.name
open prod1
fetch prod1 into @.name1, @.count1
while (@.@.FETCH_STATUS = 0)
begin
insert into #temphits values (@.name1, @.count1)
fetch prod1 into @.name1, @.count1
end
close prod1
deallocate prod1
fetch cat1 into @.seq1
end
close cat1
deallocate cat1
-- PROBLEM --
select * from #temphits order by product
end
-- /Code
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks|||That error means that the recordset within ASP is closed. If your procedure
works in query analyzer but gives this error in ASP then the error is in
your ASP code. Verify the SQL command and all the parameters immediately
before calling your stored procedure. Output them all to the page then
execute the stored procedure in query analyzer with the displayed
parameters.
Use cut and paste to do this. The problem could be as simple as a typo, or
a parameter could be blank, etc. Your connection string could also be
invalid.
Posting your ASP code might provide some insight as well.
"jcsnippets.atspace.com" <admin@.jcsnippets.atspace.com> wrote in message
news:Xns97805DB2D943Cadminjcsnippetsatsp
a@.195.238.0.34...
> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in
news:OXvPtyrQGHA.5808@.TK2MSFTNGP12.phx.gbl:
> That error means that the recordset within ASP is closed. If your
> procedure works in query analyzer but gives this error in ASP then the
> error is in your ASP code. Verify the SQL command and all the
> parameters immediately before calling your stored procedure. Output
> them all to the page then execute the stored procedure in query
> analyzer with the displayed parameters.
> Use cut and paste to do this. The problem could be as simple as a
> typo, or a parameter could be blank, etc. Your connection string
> could also be invalid.
> Posting your ASP code might provide some insight as well.
It appears that the error was not in the ASP page, it was in the stored
procedure.
After tampering with the procedure, I found out that I should have written
select * from #temphits order by product
instead of
return select * from #temphits order by product
at the end of the stored procedure.
Thanks to everyone who took the trouble to reply!
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks
Friday, February 10, 2012
Cannot resolve collation conflict for equal to operation.
I am getting this error while trying to join two tables on two different
databases.
How can I find out the collation on each and how can I make them similar?
Plz help.
Thnx in advanceMay be the collation of the columns participating in the join are not the
same. You can select from information_schema.columns to see the collation of
those columns and pick one collation to be used with COLLATE clause.
Example:
declare @.t1 table(c1 varchar(25) collate SQL_Latin1_General_CP1_CI_AS)
declare @.t2 table(c1 varchar(25) collate SQL_Latin1_General_CP1_CS_AS)
insert into @.t1 values('microsoft')
insert into @.t2 values('microsoft')
select
*
from
@.t1 as t1
inner join
@.t2 as t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
if you do not use the "collate" clause in the join, you will get same error
that you mentioned.
AMB
"dotnettester" wrote:
> Hi,
> I am getting this error while trying to join two tables on two different
> databases.
> How can I find out the collation on each and how can I make them similar?
> Plz help.
> Thnx in advance
Cannot resolve collation - ERROR!
I seem to get the following error when I join two tables from two separate databases.
[[Cannot resolve collation conflict for equal to operation.]]
Using
SELECT DATABASEPROPERTYEX('AAA', 'COLLATION'),
SELECT DATABASEPROPERTYEX('BBB', 'COLLATION')
returns exactly the same answer therefore the default collation order in both databases is the same. Why do I still get this error then?!
I know that you can change it for each individual table but isn't there a better solution?
Your help would be appreciated.Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database .... ALTER TABLE... refer to books online for more information.
If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.|||Originally posted by Satya
Didn't specified which version of SQL is using, if its SQL 2K then you can change COLLATION using ALTER Database .... ALTER TABLE... refer to books online for more information.
If its version 7 then using REBUILDM utility to rebuild the master database for similar collation settings.
I am using SQL Server 2000.
The problem is that altering the database to set the collation order to be the same as the other one is what I have done but I still get the same error.
I am not keen on altering the collation order for every table/column hence my original question on this forum.
Your help would be appreciated.|||Refer to this DBJournal (http://www.databasejournal.com/features/mssql/article.php/1587631) article for more information.|||hi,
you might specify the collation directly like this
:
join table on fieldname COLLATE ... = fieldname COLLATE ...
:
and use the same collation for both fields
or have a look at bol "COLLATE"
markus|||Originally posted by msieben
hi,
you might specify the collation directly like this
:
join table on fieldname COLLATE ... = fieldname COLLATE ...
:
and use the same collation for both fields
or have a look at bol "COLLATE"
markus
Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.
I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!|||Originally posted by Crespo-n00b
Thanks for your help but as I said, I am not keen on using collate on every column I happen to use. Mind you, thank God MicroSoft introduced this new command in SQL Server 2000.
I am still trying to figure out why I keep getting the error even though both databases have the same default collation order!
Probably, we could help you more if you'll post the JOIN in the forum.
I got similar error when in a stored procedure I've created a "Table" type variable and used it in a join... all this happened in one database. The problem was solved by creating the "Table" type variable with the current database's collation order.
Hope this helps!
Best regards,
Botond