Hi guysWe have a following problem. For security reasons in each table in ourDB we have addition field which is calculated as hash value of allcolumns in particular row.Every time when some field in particular row is changed we create andcall select query from our application to obtain all fields for thisrow and then re-calculate and update the hash value again.Obviously such approach is very ineffective, the alternative is tocreate trigger on update event and then execute stored procedure whichwill re-calculate and update the hash value. The problem with thisapproach is that end user could then change the date in the tables andthen run this store procedure to adjust hash value.We are looking for some solution that could speed up the hash valueupdating without allowing authorized user to do itThanks in advance,Leon
I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely. Is a view a good idea in this case? Is the Select * from View really a bad idea? Thanks
Can anyone tell me if turning on the AUTO_UPDATE_STATISTICS option will have any adverse effects on performance? I've read that it is possible for it to adversely affect performance, because SQL Server “takes a quick break to update database statistics in the middle of the day”. The book does not explain why and I'm always leery of simply accepting something, simply because I’ve read it from a book. This is the first time that I’ve ever heard this and cannot find anything from SQL BOL or any other source.
I have about six different entities that can have zero or more note entities associated with them. The easy way to do this is obviously to have a different "note" table for each of the entities i.e. WorkItemNote, CustomerNote etc.. But I would much rather have a single "note" table since they would all be identical, so I came up with this design:
CREATE TABLE WorkItem ( WorkItemGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity )
CREATE TABLE Customer ( CustomerGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity )
CREATE TABLE Note ( NoteId int IDENTITY(1,1) NOT NULL PRIMARY KEY, ReferenceGuid uniqueidentifier NOT NULL, Text ntext NOT NULL, -- rest of table declaration removed for bravity )
This way I can get notes associated with a given entity, either Customer or WorkItem, by just selecting from the Note table with its WorkItemGuid or CustomerGuid.
My question is: Is this the best approach to what I am trying to accomplish?
(ps: Apologies if "many to one" is not the right terminology)
What would happen if I dropped the distribution db?
I'm having problems configuring distribution and after searching, I found someone that claims they solved the same problem by dropping the distribution db. The resolution is the last post on this thread: http://www.ureader.com/msg/11563430.aspx
Is this safe to do? There are currently no publications and the distribution server is not configured.
Hi.I am wondering if it is a good idea to replicate sql server db filesusing frs.I don't really know how the frs works, sodoes frs replicates the whole database from time to time or just theportion that is changed?Also if the db is expected to change very often, and wouldn't it makethe whole system down?I wonder if it's a good idea just to make a backup of the database andcopy it.What's the usual practice?
I am out of my element here but I have someone who is working on a project for me that entails him migrating our Access database to sql. He wants to load the sql software on our exchange server but our IT guy is saying this is not a good idea. Any thoughts???
I am writing a web application that uses a Teradata database as the primary data source. While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting. I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors. There could be a few thousand users of the web application but no more than a couple hundred at a time. I just trying to get some opinions on these technique. I am open to all comments and suggestions. Thank You
I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop. To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway? Is 2 seconds enough? Too little or too much?
I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1. I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD. Any help would be most welcome. Many thanks in advance
Hi --SQL SERVER 7.0 I have a table P1 which has one identity column and 9 other columns. i need to put all the data from this table into another table with same structure as P1 called P2.
even though i used Set identity_insert P2 on I get an error saying column list must be specified Basically i was trying to do this
SET IDENTITY_INSERT P2 ON go insert into P2 select * from P1
could somebody throw light onto how to specify the column list,but at the same time inserting the entire data from P1 in one shot(using select * from...)?
Any help ragarding this is highly appreciated Thanx SK
I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column. I am using: set identity_insert soldVehicles on INSERT INTO soldVehicles SELECT * FROM vehicles Where sent2sold = 'yes' but I get this error: Error -2147217900
An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.
set identity_insert soldVehicles on INSERT INTO soldVehicles SELECT * FROM vehicles Where sent2sold = 'yes'
As I have turned ID_insert ON it must be the column list?... not sure what to do next.
my SP worked on friday, then today i ran it, but it's not working and throws these errors. i googled the error, but i still can't fix it. can you help me?
Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 31 An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl2' can only be specified when a column list is used and IDENTITY_INSERT is ON. Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 57 An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl3' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I have 2 databases, HS and BoardAnalyst and the table TCompanies exist in both. I want to delete all of the records from HS.TCompanies and repopulate it with data from BoardAnalyst.TCompanies. I'm getting the error message "Table 'HS.dbo.CompID' does not exist or cannot be opened for SET operation." CompID is the PK for the table. I'm not sure what the problem is. Thanks
I have a table where IDENTITY_INSERT is set to OFF. I also have one stored procedure that I need to run that requires IDENTITY_INSERT to be set to ON. The only problem is the users who run the stored procedure are not owners of the table and are getting the error "The current user is not the database or object owner of table ... Cannot perform SET IDENTITY_INSERT" Is there a way to use the IDENTITY_INSERT command without being the owner of the object?
I have Product table in SQL Server 2005 with Primary key ProductID. This is column with Identity set so the values for this column is auto-generated. I also have Orders table has a key that is foreign key to table Products. Now my dev environment has SQL ME whereas the deployment will be done on SQL CE.
Now I have a requirement where we want to synchronize the master tables from SQL Server with the device. For this we want to have the same identity values on the device. When I try to issue SET IDENTITY_INSERT Products ON on SQL ME it does not work. Is there any way I can set the identity ON on the SQL Mobile Edition?
Also another question I have is: Is SET IDENTITY_INSERT supported on SQL Compact Edition?
We have created an "AdminUser" with the bare minimum rights for the activites it will need to do. One thing the "AdminUser" will need to do is a "SET IDENTITY_INSERT ON/OFF".
I understand that for this user to be able to do this they will need ddl_admin rights. But that gives them all kinds of permission that I don't want this user to have.
Is there a specific permission I can give the user so they can do the "IDENTITY_INSERT"?
I tried "GRANT create table to AdminUser" but that didn't work. I tried "GRANT alter any database DDL trigger to AdminUser" but that didn't work either. I'm looking for something more specific than "ddl_admin".
I'm new to ASP/VS/Linq and I'm having a small problem. I have one table setup in SQL Server Express 2005 through Visual Studio 2008. The table name is "Users" and has three columns (accountID, userName, email). AccountID is the primary key and set to auto incriment. I've added a couple of records by hand and it works. I have a single form with a button, a label, and two text boxes. The button code is below. After entering some fake data that does not already exist in the database and clicking the button I get this. Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF. I understand that it is trying to insert something into the accountID field but I don't understand why since I'm only providing a username and e-mail address to insert. Your help is greatly appreciated.protected void Button1_Click(object sender, EventArgs e) { MyDatabaseDataContext db = new MyDatabaseDataContext(); var query = from u in db.Users where u.email == txtEmail.Text select u;
var count = query.Count(); if (count == 0) { //Create a new user object. User newUser = new User();
//Add the user to the User table. db.Users.InsertOnSubmit(newUser); db.SubmitChanges(); } else { Label1.Text = txtEmail.Text + " already exists in the database."; }
Hi, What is this error ? Error : " Cannot insert explicit value for identity column in table 'Comments' when IDENTITY_INSERT is set to OFF. " Please help me .
Hi all. This is my first post to these forums and I am a newbie. I am using SQL server 2005 and I have written a procedure to insert the data in new row. My procedure runs and executes well, but does not insert a record in my DB table. Instead I get a message in the debugger window that I need to turn ON the Identity Insert property for the tables. But after searching for a while in the IDE, I could not find any such property. So I need to know what went wrong here. Also, I am using ASP.NET to connect to the database and I found out an article on web explaining the way to set the property to ON through my ASP.NET application. But, I would also like to know how to set that property to ON through SQL management studio. Also, my primary key is not null; I am also passing the primary key value and it is unique. Please let me know a remedy for this.
I ma getting following error: Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Tmp_r429_sig' when IDENTITY_INSERT is set to OFF.
Can someone throw some light on how to execute SET IDENTITY_INSERT as a dynamic query.
I'm trying to copy the data from one table to another while preserving the identity keys, but I can't seem to get past this error message. Here's the SQL:
SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview ON
INSERT INTO dbo.Cms_MenuItems_Preview SELECT Id, [Name], Url, Parent, IsActive, SortIndex FROM dbo.Cms_MenuItems
SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview OFF
and the error message is: Msg 8101, Level 16, State 1, Line 3 An explicit value for the identity column in table 'dbo.Cms_MenuItems_Preview' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO2.8) in that I cannot insert a specific value to an identity field. (linesbelow with >>> are code lines. I am using Python, but the syntax should beabout the same as VBScript)First, I create an ADO Connection and create my table.[color=blue][color=green][color=darkred]>>> c = win32com.client.Dispatch('ADODB.Connection')[/color][/color][/color][color=blue][color=green][color=darkred]>>> dsn = 'DRIVER=SQL[/color][/color][/color]Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft DataAccess Components;SERVER=SERVERINSTANCE;"'[color=blue][color=green][color=darkred]>>> c.Open(dsn)[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql = 'CREATE TABLE Table_Name ('[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'Field_2 nchar(50) NOT NULL, '[/color][/color][/color][color=blue][color=green][color=darkred]>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'[/color][/color][/color][color=blue][color=green][color=darkred]>>> c.Execute(sql)[/color][/color][/color]This works fine. Then, I attempt to allow insertion into the ID_Field.[color=blue][color=green][color=darkred]>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")[/color][/color][/color]This seems to work in that it does not throw an error and gives a returnof -1. Then I open a Recordset[color=blue][color=green][color=darkred]>>> r = win32com.client.Dispatch('ADODB.Recordset')[/color][/color][/color][color=blue][color=green][color=darkred]>>> r.Open('Table_Name', c, 2, 4)[/color][/color][/color]Last, I am attempt to add a record to the recordset with an explicit ID,[color=blue][color=green][color=darkred]>>> r.AddNew()[/color][/color][/color][color=blue][color=green][color=darkred]>>> r.Fields.Item('ID_Field').Value = 45[/color][/color][/color]but this fails with the error of"Multiple-step OLE DB operation generated errors. Check each OLE DBstatus value, if available. No work was done."Even worse, if I now try to set the identity field to allow inserts again,Updating() causes an error that I must use an explicit value for ID_Field,but if I try to give it one, it fails with the above error. I have todestroy the recordset object at this point to get any further.I am told that SET IDENTITY_INSERT only remains active for one statement andthus must be combined with the insert, but I do not know how to do this.There is a similar sounding bug w/ SQL 7(http://support.microsoft.com/defaul...b;EN-US;253157), but thereis no indication that it affects newer versions of the DB. Does anyone haveany suggestions or ideas?Thanks for any help,-d
Hi i am unable to set Identity_insert off in sql server compact? Can you please tell me if sqlce supports or not?
If it does not support what is the work around that can be done?
Please tell the workaround in detail i mean altering table for identity key e.g. Because i have seen some posts that talk about alter table but i am unable to understand how that can done?
I know there has already been a thread on this, but I want to push some about it.
In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".
That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.
In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.
The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.
Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)