SQL 2012 :: Sequence Got Reset To 1
Aug 25, 2015Somehow all of my sequences got reset to 1. The only thing that occured was the database was renamed.
SQL Server 2014
Is this a bug ?
ADVERTISEMENT
SQL 2012 :: SA Password Reset Recovery
Jun 29, 20153rd party company set up SQL 2008 R2 SP1(awhile ago, years), they disabled SA no other logon was created with Admin rights, Database is Mixed Mode, never had a DBA. the database is from a Transactional system. Now comes me. I need to set up backups, maintenance etc..
I have been provided System AdminLocal Admin rights on the Server. The Server has been set up like a Domain Controller. I have tried all of the command line options that I know and I can find. I have not been able to get any to work, they all fail login to SQL. Before I take the route of reinstalling SQL on the server I want to make sure I haven't missed any thing, especially since this is a 100% production environment with 3rd party processes and applications attached that are not documented or supported. (nightmare)
I would prefer a cmdline or PowerShell script (ps2).I figure that the holiday is coming up so whatever I will end up needing to do I will be able to complete over the holiday weekend when it is down.
One set I tried as "run as Admin"
SQLServr.Exe –m
SQLCMD –S <Server_NameInstance_Name>
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
GO
SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
I should point out the rest of our environments are SQL 2012
SQL Server 2012 :: Reset Running Total To X Amount
Dec 22, 2014I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.
I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.
SQL 2012 :: Reset Admin Password Without Reinstalling Server
Jun 17, 2015I have a local instance of SQL 2012 express,
Somehow my admin privileges have been lost, and the sa password is not Password123.
Is there anyway to reset this password without re-installing SQL server.
SQL Server 2012 :: Running Sum With Reset Option And BULK Update
Jul 22, 2015I have come up with an issue where I want to update data in a table using bulk/SET update to get the result shown in below code with output in column titled "Arrear Amt".
Please use this test data.
CREATE TABLE ##vOD_Calc
(
Seq_No INT ,
Contract_id INT ,
Rental_id INT ,
Actual_OD INT ,
Logic_OD INT ,
Due_dte DATETIME ,
[Code] .....
Logic required is that once the sum of column [ArrearAmt] of current row and all previous rows becomes greater than $100 then column [ChArrrearAmt] should show that summed up value and in else case the column [ChArrrearAmt] should show the same value as that of column [ArrearAmt].
Once the column [ChArrrearAmt] reaches the threshold of $100 then the same cycle should start again i.e. in above example rental#1 had $37.17 < $100 then rental#1 + rental#2 is also < $100 and at rental#3 sum of rental#1, rental#2 and rental#3 becomes $111.51 which is greater than $100 so its updated in column [CHArrrearAmt]. The same cycle start overs from rental#4 onwards however the summation of [ArrearAmt] will now begin after rental#4 onwards and not from the starting.
Below is the loop based SQL script which handles the above situation, however in BULK its a total deterioration of performance if thousands of rows are to be processed i.e. with a contract having multiple rentals.
The case here is that I have to use the result of previously updated column value of [ChArrrearAmt] to take decision for the next row, however with BULK update since the row is not yet updated with latest amount therefore the decision on next row is also giving wrong result.
This is the code with which I have achieved to update the column 'chArrear Amount', however its a loop based solution and performance killer.
INSERT INTO ##vOD_Calc_loop
( Rows_count ,
contract_id
)
SELECT COUNT(*) ,
T.Contract_id
FROM ##vOD_Calc T
GROUP BY T.Contract_id
[Code] ....
SQL 2012 :: Reset For Connection Pooling VS Login Failed For User XXXX
Aug 5, 2015We are facing issue on one of our databases on Availability Group replica. FYI, please refer the below messages we are receiving. Moreover, the errors don't happen consistently for the specified user.
The client was unable to reuse a session with SPID 420, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Error: 18456, Severity: 14, State: 46.
Error: 18056, Severity: 20, State: 46.
Login failed for user 'XXXX'. Reason: Failed to open the database 'YYYY' configured in the login object while revalidating the login on the connection. [CLIENT: aa.bb.cc.nn]
SQL Server 2012 :: Sequence In A Table Instead Of Identity
Dec 12, 2012I've started using a SEQUENCE in a table instead of an identity.
I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.
For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7002, but the last inserted row has a primary key of 7032!
I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.
When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.
The definition for the sequence is:
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
The default constraint for the primary key on the table is defined as:
ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]
SQL Server 2012 :: Finding Break In Sequence
Feb 21, 2014I need to be able to identify breaks in a sequence so I can evaluate the data more correctly. In the sample I have given I need to be able to identify the break in sequence at 69397576, ideally I would set that as a D. My query also needs to recognize that the 3 sequences following 69397576 are sequential and would belong to that set. so the out come would look like this.
id file_name page_follow
693975631555557564_22222221114014810D
693975641555557564_22222221114014810F
693975651555557564_22222221114014810F
693975661555557564_22222221114014810F
693975671555557564_22222221114014810F
693975681555557564_22222221114014810F
693975691555557564_22222221114014810F
693975761555557564_22222221114014810D
693975771555557564_22222221114014810F
693975781555557564_22222221114014810F
693975791555557564_22222221114014810F
here is some test data.
create table test1 (id INT
, [file_name] VARCHAR(100)
, page_follow CHAR(1));
go
[code]....
SQL 2012 :: Join Between Two Database - Collation Sequence
Jul 18, 2015I have two databases with different collation sequences, let them be called A (SQL_Latin1_General_CI_AS) and B (Latin1_General_CI_AS). Now I need to join between the two (including through temp tables with server collation being Latin1_General_CI_AS). In order to get rid of the errors when trying to do so I changed all my statements in the WHERE and ON clauses to
A.table.column collate database_default = B.table.column
If I were to change the collation of the character typed columns in database A to the one in database B, would it make any difference in terms of performance, or would it just be a useless exercise? Just asking because many of those columns are part of a primary or foreign key that would need to first be dropped and then recreated after changing the collation, and I'd prefer to save myself the effort of writing scripts to do so if the answer is NO.
BTW, I tried to change the database's collation sequence, but that leaves the collation of the columns unchanged.
SQL Server 2012 :: Populate Value In Create Sequence?
Jul 29, 2015I'm looking to see if there is a way to populate starting number for the sequence from a max value of a table.
CREATE SEQUENCE test_seq
AS [int]
START WITH (select max(col1)+1000 from table1)
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
CACHE
GO
SQL Server 2012 :: How To Find A Missing Sequence In A Column
Mar 20, 2014Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
SQL 2012 :: Breaking Backup Chain Or Sequence In Server?
Aug 4, 2014We are running SQL Server 2012 on Windows Server 2008. Just wondering what type of actions would break the backup-chain or backup sequence? For instance, if you have tlog backups being taken every 10 minutes and you stop the SQL Server Service for say 30 minutes. Would this action break the backup chain? Or would everything return to normal once the SQL Server Service is restarted?
View 7 Replies View RelatedSQL Server 2012 :: Date Sequence Missing Values?
Apr 27, 2015Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....
SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?
Oct 7, 2015If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
SQL Server 2012 :: Assign Sequence Numbers To Twins / Triplets Only
Sep 3, 2014I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field
Oct 8, 2015The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
SQL 2012 :: Moving SSIS Solution To Another User - Reset User Encryption Key
Oct 22, 2014I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
Can I Reset Tbl ID To 1
Nov 27, 2007I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate.
Is there any way to reset the indentity column without removing the constraints?
UDF Does Not 'reset'
Dec 10, 2007My UDF
FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.
Any ideas guys?
Priority Reset
Nov 27, 2006Hi,
consider my table,
s.no priority status
1 2 pending
2 3 pending
3 0 completed
4 1 pending
5 0 completed
Now, if I insert a record as,
6 1 pending
the priority must be reset as follows,
s.no priority status
1 3 pending
2 4 pending
3 0 completed
4 2 pending
5 0 completed
6 1 pending
Im using c# and sql server. How can i do it?
Thanks,
Jasmeeta.
Reset Id Field
Jul 8, 2007I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes
The tables have been used for testing while the application was developed.
I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?
Reset Pimary Key To 1
Jul 17, 2007Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul
View 1 Replies View RelatedSQLDataSource Reset?
Apr 18, 2008I have a SQL data source triggered with this code: string searchstring = TextBox2.Text;
SqlDataSource1.SelectCommand = "mod_search_all_sel";
SqlDataSource1.SelectParameters.Add("search_str", searchstring);
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; The second time this fires however, the SelectParameters.Add function creates another paramter and the stored procedure blows up. How do I do a reset like in old Classic ASP where you would set the DS = Nothing?
Can I Reset Identity?
Jun 16, 2008I have a table where I delete all the records, then reload. can I reset the identity to (1,1) Is there a SQL command for that?
-smcirish
How To Reset Identity ?
Mar 11, 2004In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that?
Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.
Reset Auto-Id
Nov 1, 2000Can I reset the Auto_ID column in a table to start from 1 again?
Thanks
Reset Indentity
Apr 7, 2003How do reset identity seed on table, for example I have table that has gap in the identity such as (1,2,3,5,6, etc..) and I want reset the entire table.
Thank You,
John
Reset Identity?
Jun 27, 2001Can a column of Identity type reset after it reaches its maximum value?
Thanks,
Ben
ID Field Reset
May 15, 2006This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.
When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.
This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.
T-SQL Way To Reset Primary Key - Need For DTS
Aug 9, 2006I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.
What's the syntax in T-SQL to accomplish that?
I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.
Any help here is appreciated.
Thanks,
Peter
Connection Reset
Mar 23, 2007Hi all
I have build an application on top of SQL Server 2005 and i am using microsoft driver. My application stops periodically becoz of Database Connection Loss and I get the following exception
com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.transmit operation. Exception:Socket closed. Context:(5) [Thread[Thread-55,5,main], IO:7e2dc, Dbc:null].
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.DBComms.transmit(Unkn own Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand( Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown Source)
10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)
Unable to find the exact reason for the above problem. Pl advice
Thanks & Rgds
Roshini
Reset All Rows When One Row Changes
Jul 12, 2012I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.
The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.
The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.
Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.
But when I tested it, nothing happened ...
ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT
AS
BEGIN
DECLARE @PartOnly varchar(16) -- Part Number
DECLARE @Rev varchar(4) -- Part Revision
DECLARE @PartCode as varchar(2) -- Part Classification
DECLARE @Last_Checked as datetime -- Checked Date
[Code]...
RESET COUNTERS
Oct 6, 2007Hello All:
i am inserting a counter from a procedure based on the year/month.
I would like to reset the counter to 1 every month.
example of data:
count year/month
_______________
16200711
17200711
18200711
19200711
20200711
21200712 this counter should be reset to 1
22200712
23200712
MY PROCEDURE
____________
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@YMN CHAR(6)
DECLARE c CURSOR
FOR
SELECT DateID,YMN FROM TEST
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID,@YMN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TEST
SET BusDay = @i
WHERE DateID = @DateID
SET @i = @i + 1
FETCH NEXT FROM c INTO @DateID,@YMN
END
CLOSE c
DEALLOCATE c
Thank You
F.