SQL Server 2008 :: Generate Increment Number With Conditional Restart

May 22, 2015

I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.

What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.

CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),


View 8 Replies


How Do I Generate Auto Increment Number In SQL Express?

May 22, 2006

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?

In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?

Thanks a lot.

View 2 Replies View Related

SQL Server 2008 :: Max Concurrent Sessions Since Last Restart

Feb 2, 2015

Is there a way to find the max number of sessions that were on a sql server since its last restart? I know how to get how many total connections there were since last restart, and the current number of sessions, but not the max number of sessions that were running since last restart. Also, opinion on the optinum number of allowable sessions, or a good way to figure out the balance?

View 0 Replies View Related

SQL Server 2008 :: Log Shipping Report Not Running After Restart?

May 13, 2015

When trying to open the log shipping status report, I get an error: user must be in the master database. The jobs appear to be running correctly.

View 0 Replies View Related

SQL Server 2008 :: How To Avoid Restart After Applying Patching

Aug 28, 2015

I am going to apply SQL ServicePack 3 in our production Server . Is it possible to avoid restart ?

View 2 Replies View Related

SQL Server 2008 :: How To Increment Sequential Values

Sep 25, 2015

I have to increment sequencial values for the following:

Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43

Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43

What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.

View 2 Replies View Related

SQL Server 2008 :: Read A Value From Table And Increment It By 1 - Row Lock

May 12, 2015

I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.

CREATE TABLE [dbo].[tblOnboardingSequence](
[OnboardingSequenceID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[NextNumber] [bigint] NOT NULL,
[OnboardingSequenceID] ASC


My Stored Procedure has below logic

----------- Acquire row lock here
SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO'
UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'
----------- Release row lock here

I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query.
What si the best way to deal with this?

View 2 Replies View Related

SQL Server 2008 :: How To Generate DDL For Table

Sep 11, 2015

I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...

View 3 Replies View Related

SQL Server 2008 :: Cannot Generate SSPI Context

Jan 27, 2015

Our end users is getting below error, when they try to connect to our database:

"Cannot generate SSPI Context."

That is Windows based application and we have done everything like restart our DB Server , reinstall exe in users system, but still issue is same.The issue has occured from when DB Server has restarted and at the same time few users are connected. before that it was working fine. we could not find what is issue.

View 1 Replies View Related

SQL Server 2008 :: How To Generate CSV With Custom Data

Feb 26, 2015

It might be an old question but wanted to see, if we have any latest techniques (other than bcp).

SELECT Field1, Field2 FROM MyTable

If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).

View 3 Replies View Related

SQL Server 2008 :: How To Generate Week Ranges

May 28, 2015

I need to generate the week ranges like this format :

Here from date and to date would be picked up from the table but just to make you understand i have hardcoded it but this is the real date which is falling inside the table.

Note : Week should be generated from Monday to Sunday within desired date range

View 9 Replies View Related

SQL Server 2014 :: Generate Random Number In Range

Oct 3, 2015

I have a function which generate random number in range :


If you run this script you always get result between 1 and 4

SELECT dbo.Func_Rand(4, NEWID())

BUT, in this script sometimes have no result !!!!!!! why??

WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )

View 2 Replies View Related

SQL Server 2008 :: Generate Data Models Of All Databases

Feb 12, 2015

I need to generate data models of all databases for an instance. How can I accomplish this?

View 3 Replies View Related

SQL Server 2008 :: Code To Generate XML File Based In XSD

Aug 2, 2015

I have data in Sql table , I want to convert it to xml using xsd using script component in ssis.

View 0 Replies View Related

SQL Server 2008 :: Generate Invoice Document For Customers?

Sep 25, 2015

i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.

I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing.

View 9 Replies View Related

SQL Server 2008 :: How To Generate Test Data Using Only VS 2013

Sep 29, 2015

I've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.

View 5 Replies View Related

SQL Server 2008 :: Query To Generate Report Of Locked Account

Jan 9, 2011

Just wondering if there is any query which can give me report of locked account in SQL 2008. I know there are options like:-

1) Can go in ssms--security--account and right click and select status. This is applicable to individual account.

2)SELECT LOGINPROPERTY('accountid', 'IsLocked'). Also applicable to individual account

3) SELECT name,type_desc,is_disabled,modify_date,default_database_name from sys.server_principals order by type_desc

Generate the report but give the informatio as is_disabled. But I think is_disabled <> is_locked.

View 5 Replies View Related

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015


Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
Returns char(8)

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

SQL Server 2008 :: Unknown Error Messages During Generate Script From Database

Mar 23, 2009

I have a SQL2008 database, running Standard Edition 64-bit, database owns by sa, connected to Management Studio using Windows Authentication mode. When I tried to generate scripts from a database, I got the following error messages:-

[Operation is not valid due to the current state of the object. (SqlManagerUI)]

which happened at one particular table. I have reviewed this table definitions are normal, and I could select data from it.

I couldn't find any information anywhere relating to this error messages, except that someone got it when they were trying to change the authentication mode or sa password.

View 5 Replies View Related

How To Set The Database To Always Use Next Increment Number?

May 22, 2007

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

View 9 Replies View Related

Increment Number On Fields With Same Value

Dec 2, 2013

I have a Column named 'Series' and a column named 'Linenumber'. They look like this:

Series Linenumber
234 NULL
234 NULL
235 NULL
235 NULL
234 NULL
234 NULL
235 NULL
235 NULL
236 NULL
236 NULL
236 NULL

And I want to run a query and make it look like this:

Series Linenumber
234 1
234 2
235 1
235 2
234 3
234 4
235 3
235 4
236 1
236 2
236 3

So basically I want Linenumber to be an increment column but to increment seperately for each value of Series. RDBMS is Microsoft SQL Server 2008.

View 4 Replies View Related

Increment Number Code - Phantoms?

Feb 9, 2000

Here is a piece of code I found in an application I'm supporting.
Problem: We're seeing duplicate numbers and I think this code may be the source of it.

At first glance I did not understand how it worked.
It locks a table <begin tran> , updates a number, reads that number, then commits. This code appears to work - it returns the newly added number.

My Question - How? How can this code return a value that is not "Committed" to the database yet. Please critique..... I have an alternate method, please comment on it as well. Thanks for your input.

CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
If @@ERROR <> 0 OR @@ROWCOUNT <> 1 /* Check for Errors */
Rollback Tran
Return -999
If @@ERROR <> 0 OR @@ROWCOUNT <> 1 /* Check for Errors */
Rollback Tran
Return -998

My Newly suggested method. Using Implicit transaction.
I believe this is more "concurrency" friendly. Do you?

Begin loop until success
Read Number
Update to Number+ 1 WHERE number is the one I just read
If row was updated, use this new number and set success flag

View 1 Replies View Related

Get The Deleted Auto Increment Number.

May 16, 2008

Hi All,

Is there any option to get the velue of auto increment number before inserting record.

My problem is that I want to get the auto increment number, for this I am using MaxId function, but If i have deleted the some rows from table, I could not get the actual.

For example there are 20record s in the table I have delete the last 3 records now I have the last value of Identity is 17. When I used the Maxid function It gives me the 18 number. But I need 21.

help required.



View 6 Replies View Related

Automatic Number Increment In Ms Sql 2005

Jan 24, 2007

will it be possible to increase number as below automatically000000010000000200000003....whenever the row is inserted, number will be increased like aboveformat.which data type should I select and do some other setting to recordlike that?thanks

View 4 Replies View Related

Retrieve The Next Auto-increment Number

Apr 10, 2006


I have one master table and multiple detail tables. The primary key of the master table is an auto-increment number, which is a foreign key in those detail tables. I am wondering if SQL Server allows us to get the next available auto-increment number of the master table up front. Thanks a lot.

View 9 Replies View Related

Procedure For Increment Number Based On Alphabets?

Dec 28, 2013

writing the procedure for incrementing the number based on alphabets from A-Z

for example:

if alphabet is A then increment should be A001,next A002..etc
if alphabet is B then increment should be B001,next B002...etc
if alphabet is Z then increment should be Z001,next Z002...etc

View 4 Replies View Related

SQL Server 2008 :: Slow Row Number Over Partition

Jul 28, 2015

I have a simple table with 4 columns

(idAuxiliarPF(BIGINT+PK), pf(BIGINT+FK), Data(DateTime), Descr(NVARCHAR))that has aprox. 50k rows.

I need to create a partition of the data to join to another table, the query that i have:

FROM dbo.PFAuxiliar
WHERE Data <= GETDATE()This query takes around 40 seconds to return the results

If i remove the Descr column, the query it takes no time.

FROM dbo.PFAuxiliar
WHERE Data <= GETDATE()I have two indexes, Clustered (idAuxiliarPF), NONClustered(pf).

How can i improve the performance of this query?

View 9 Replies View Related

SQL Server 2008 :: Calculate Number Of Days

Oct 8, 2015

I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .

1001 12/24/200712/8/2010
1001 5/27/20138/2/2013

for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013

then AVG them.

View 3 Replies View Related

SQL Server 2008 :: Restore Number Of Databases Using Powershell

Aug 15, 2012

I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....

The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.

View 5 Replies View Related

SQL Server 2008 :: Select Alternate Number Of Rows?

Jan 28, 2015

A simple Query to select alternate rows from a table ?

View 9 Replies View Related

SQL Server 2008 :: How To Get Output Based On Sequential Number

Feb 13, 2015

The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.


CaseNumberStart CaseNumberEndExported


CaseNumberStart CaseNumberEndExported

How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.

View 1 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved