Reseeding in SQL Server gets different results based on whether the table has ever had data in it.
ref: http://cs.thycotic.net/blogs/bryant_smith/archive/2006/01/17/65.aspx
I am having the same problem. Any suggestions ?
I'm using this query to update the top 1 row:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
set rowcount 0
The key field on the table is not an identity field.
Is there a way to get that KEY field's data (for the row that was updated)?
I tried this just to see what would happen and it returns null:
Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2
select @@IDENTITY
set rowcount 0
Thanks!
Hi,
I am doing Select into to make a table (I want to skip the log), but I want to add an IDent column to the new table. Can I do this somehow in the Select into? I have tried a couple of approaches, but have come up short....
Thanks,
Judith
Hello
I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}
Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;
I get 3 record, the dates is the last, but the lat. and long. is wrong...
Thanks,
Laci
I have 2 identical databases. In DB1 there is no data. DB2 some of the tables have data. DB1 updates DB2 but just for demonstration purposes I need the ID fields to match (identity fields). So I ran a script to reseed all the tables in both databases.
In the database, in tables with NO records, some start at 100 and some start with 101. Help! It's driving me craz!
EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,100) END'
Hello there,
I am having problem in modifying the identity column of a table.
Currently the identity column is seeded by 1 with an increment of 1 and DATA with these properties of the column is stored.
Now, I want to reseed the identity column with an increment of 2.
The query I am using is
ALTER TABLE XP ALTER COLUMN XP_ID BIGINT IDENTITY ( 500, 2 ) NOT NULL
Is there any one out there who can help me?
I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.
Thanks for trying.
Hi,
I'm trying to set up merge replication and when I try to synchrinize with the subscriber I get the error "invalid column name ROWGUIDCOL". Following an article it recomended to reseed the identity columns on the subscriber so that there is no conflict. Now the tables with the identity column have the "Not for replication "option on. Using DBCC CHECKIDENT (table_name, RESEED, some_value) does not change the SEED when I check. SQL server help file remarks on DBCC CHECKIDENT say that if the column was created with the "Not for replication " option on it cannot change the value.
From sql server help file:
Remarks
If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).
Any way around it or any help so I dont get that "invalid column name ROWGUIDCOL" error?
Thanks allot. Looking forword
George
Is there a way to reseed the Identity column in SQL Server Compact Edition? The following command does not appear to work DBCC CHECKIDENT (Table_Name, RESEED, 0) and I cannot seem to TRUNCATE the table either. Not sure if table truncation is even supported.
I have encountered an anomaly. The dbcc checkident(reseed) command behaves differently on two SQL servers.
In both cases, I am deleting from (not truncating) data in two tables, due to foreign key constraints. (I am truncating other tables, the issue is not with those tables, only with the deleted-from tables.) On one server, I need to use dbcc checkident(reseed,0) so that when I insert fresh data, it begins with identity key #1. According to MS documentation, that appears to be the correct behavior, when data has been deleted from a table, rather than truncated.
However, on the other server, I need to use dbcc checkident(reseed,1); if I use ...(reseed,0) on that server, it begins inserting data with identity key #0.
This is consistent, repeatable behavior on both servers.
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View Related
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
I need a job run page to fire a job on a sql server if the job is not already running. How do I check if the job is running on the MSSQL server.
Can I use the sp_job_help as it does return 4 data sets with the first having the data I need, but as yet I have not mastered a multi data set return.
How can I make sure that I have SQL Server 2000 sp3 or sp3a installed?
Thanks you,
http://thelushed.com/forum/showthread.php?t=138
View 1 Replies View RelatedDECLARE @Temp int
DECLARE @FullQry varchar(50)
set @FullQry='select @Temp=Emp_ID from Employee where.....'
Exec(@FullQry)
select @@ROWCOUNT
My Employee table has 3 records and this query sholud return me @@ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?
insert into OPENROWSET('Excel 8.0;Database=D: esting.xls;',
'SELECT * FROM [testing$]')
getting errore
Incorrect syntax near ')'.
Hi,
Is there any way to tell me how many users connecting to specific database / who are using it in the management studio? we are use the windows authentication mode.
Thanks!
I want to built a table to a form....
I have some check boxes in this form....
what is the script line for this check box ?
I know it is suppose to work 1 or 0 ? for false or true .....
How I suppose to do this ?
Hello,I Have a code:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [shop_clients] ([ID], [IMIE]) VALUES (@ID, @IMIE)"> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="IMIE" Type="String" /> </InsertParameters> </asp:SqlDataSource> SO this code It will allow me insert to database SQL textbox - name ?If yes How I can:If I click the button My textbox - name, insert to databasePlease me help :)
View 1 Replies View RelatedAperently I could not insert a text field to another table from INSERTED in a trigger.It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText) SELECT i.myTrID, i.myFirstName, p.myBigText FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID WHERE (i.myTrType = 'In') Thanks,
Hi I was wodering how to add an OR statment right in the Check Constraint expression.
This is what I am starting with in the database
([zip] like '[0-9][0-9][0-9][0-9][0-9]')
and what I want well not exact but this would answer my question
([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')
Thanks for any help
Hi,
I have two web pages in one web page i have 5 check boxes. For example if the user checks the Checkbox1, checkbox2 and clicks on button.
On the button click I am storing the selected checkboxes value in database lke the following:
Year Options
xxx 1
xxx 2
in the above format( user selectes checbox1, check box 2).
And in the Second Web page I am showing the 5 checkboxes but in this web page I need to check the first and second checkboxes on the page load because user selectes those two check boxes in the first web page.
my select query returning the results like this:
Options
1
2
based on options I have to check those corresponding check boxes in the second web page.
How to achive this.
Thanks in advance
I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?
Here's my insert code.
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection);
string usercode = GenPassWithCap(9);
String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)"; SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10)); myCommand.Parameters["@id"].Value = usercode;
myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.Parameters["@firstname"].Value = txtFirstName.Text;
myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50)); myCommand.Parameters["@lastname"].Value = txtLastName.Text;
myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75)); myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue;
myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20)); myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue;
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
Do I run a completey different select command before hand and try to match that field?
Hi,
I'm trying to populate a table in one database with details from a identical table in another database. How do I check to see if the row exsits before I insert the data because at the moment I'm getting a violation of a primary key error.my codes something like the below
insert into db2.table1(values)
select *
from db1.table1, db1.table2 where t1.id = t2.id
Cheers Dave
How can I check if the ( SqlCommand ) return empty values
Can some one write code for this, I want know it is return Null values or not
thanx ....
Hi
I have two tables. one MasterDetail and second is countrydetail
In master detail i have MasterDetailId (Primary) and countryId.
In CountryDetail table I have Countrid (Primary),CountryName.
I don't want to give relationship because i can insert null value in countryid in MasterDetail table.
So i have wriiten query like this
-> select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2'
If In MasterDetail table if CountryId is null then it will not show me any record. So I want that record and its value with this query and checking null values. Help me about solving this query.
Thank You
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
View 2 Replies View Relatedhi, I want to implement a constraint on a talbe for two fields
phone numbers should b (###)###-####
and ss# should be ###-##-####
How can I create such constraint. I tried, but got an error message and could not save the table with the new changes.
Thanks
Ahmed
Can anyone tell me what is wrong with this line of code..
I am missing someting on both line of code
exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
exec ("dbcc inputbuffer (" + @spid + ")")
Hi !
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
TIA MAttias
How do i create a check constraints on column a so it dose not accept $ character? syntax pls.
Thanks
Is there a way to check if a sql 2000 server was installed on the license of "per server" or "per # concurrent users"?
Can't find from server property.
thanks
-D