How Can I Reset The Primary Key Field Numbers To Zero Before Uploading My Site To The Production Sever
Apr 13, 2007
I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.
View 2 Replies
ADVERTISEMENT
Jul 31, 2007
I have a question on autogenerating numbers for a primary key field, "studyID," in a tablebut with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Heres the range of values we want our StudyIDs to be (this is to be our validation rule as well):
10001-19999 for Site 1
20001-29999 for Site 2
Your suggestions are VERY VERY WELCOME! THANKS!
View 2 Replies
View Related
Jun 28, 2007
Hello,
I would like some help in uploading a database from my harddrive to a server using sql server management studio express.
If I try to attach it to the server, this program only looks at the files on this server and not on my harddrive. So how can I copy a sql database to my folder on the server.
I am sure it must be a simple problem, but I've been bizzy with it for about a day now. So, please advice.
Rgds,
Wouter.
View 7 Replies
View Related
Apr 15, 2007
HI, I have upladed my application to my remote server but then I get an error message. I am a beginner so any help would be appreciated and if possible explain the solution to me in a simpler format. Here is the error message: Server Error in '/' Application.
An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
Source Error:
The source code that generated this unhandled exception can only be
shown when compiled in debug mode. To enable this, please follow one of
the below steps, then request the URL:1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %>or:2) Add the following section to the configuration file of your application:<configuration> <system.web> <compilation debug="true"/> </system.web></configuration>
Note that this second technique will cause all files within a given
application to be compiled in debug mode. The first technique will
cause only that particular file to be compiled in debug mode.Important:
Running applications in debug mode does incur a memory/performance
overhead. You should make sure that an application has debugging
disabled before deploying into production scenario.Thanks in advance.
View 1 Replies
View Related
Jan 9, 2008
Hi All,
I have to delete 135 Million records from our production server, keeping only last three months data & the table doesnot have any index.
Can you please suggest the best possible approach to perform this activity.
Approach i am thinking :-
1)I will rename the existing table & create a new table with the same name (By this my application wont be interrupted)
2)then i am planning to create a nonclustered index on the date column sort order desc so that i can get the last three months data.
3)Once the index is created i can transfer the required records to the new table created in step 1.
Please sugeest your valuable feedback or suggestions to perform the task ASAP.
Thanks
View 9 Replies
View Related
Aug 4, 2006
After days of trying to deploy my web application along with the ASPNETDB database I am convinced this is actually just an attempt on the part of the MSFT developers to drive us nuts.
It simply will not work. As reported by everyone in all of the forums, it works fine in development but does not work when deployed. If I move my data to a full SQL Server instance and modify all of my connection strings accordingly, it works fine, as well. It just does not work when I publish the app and try to run it using the AttachDBFilename methodology. I am convinced that only Santa Claus or an honest lawyer can make it work.
Let's recap the steps: first, I create the folder that I want to use for production and give Modify permissions on that folder to <machine>ASPNET and NETWORK SERVICE. Then I open up IIS Administrator and create a new virtual directory that points to that folder and set its permissions to include Write. I check the ASP.NET tab and it's set for 2.0. Now I switch back to VS2005 and publish my web application directly to the target folder.
Feeling optimistic, I go right to IE and bring up the site. Looks good until I attempt to login. That's when I get the first error - Failed to generate a user instance of SQL Server due to a failure in copying database files. The connection will be closed.
From there I start through the forums and try every one of the solutions proposed, even though none of them specifically identify this error wording. I know I should have used the wording from the Exception "Details" - except they state exactly the same thing - what was I thinking? I thought the .NET developers did away with the error message authors from the old days but it looks like they're back with a vengence.
Somewhere along the way I am able to get past that first error. Unfortunately, I've made so many changes I don't know which combination actually worked. But it doesn't matter because now the database is refusing to let <machine>ASPNET login! Okay, so I use SSME and create a login attach the database and create a user (none of which is supposed to be required for UserInstance=true). Detach the database and try IE again. You guessed it - still won't work. Now it can't generate the user instance because a database by that name already exists, or the user doesn't have permissions, or it's Friday and the machine wants to leave early - I give up.
So, at this point, I'm pretty much finished with SQLExpress for anything other than my development environment.
View 2 Replies
View Related
Aug 9, 2006
I 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
View 4 Replies
View Related
Aug 2, 2007
Ho wdo i do this? when i delete records and then post a new one from the start I want that to be number 1? I am using MSSQL
View 10 Replies
View Related
Oct 4, 2007
Hi all... how to reset the primary key to 1?
thanks
Ron
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
View 3 Replies
View Related
Apr 26, 2007
Ok - I have two tables that are relational. I have been inserted data into the tables because of testing. I also have been deleting data. My question is how do I reset the auto-incremented Primary key values.
For example:
Primary key of table one is bizID. Well there are only 3 record currently in the table. The bizIDs are 1-3. If I insert another record the bizID will be 88 because that was the next auto-incremented number. I obviously deleted the other records. I want to start the primary key value over from zero. How do I accomplish this? thnks
View 2 Replies
View Related
Dec 14, 2007
I'm still looking for a solution on msdn, but i've decided also to post my question here.
How can i reset my primary key field auto-increment back to 0 in runtime (or in designtime)?
View 18 Replies
View Related
Jul 8, 2007
I 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?
View 2 Replies
View Related
May 15, 2006
This 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.
View 2 Replies
View Related
Dec 29, 2011
I need an automated process where i can reset the field value to 1 on a table at the begining of each year (first day of the new year). How would you go around this? Triggers or stored procedure?
Can you have more than 1 After insert,update triggers on one table and how does it affect the performance and everything around the table.
View 2 Replies
View Related
Nov 29, 2006
I have test tables with key fields that increment by one each time a record is added. If I delete all the records in a table (using a delete query) is there a way I can make the key start at 1 again.
The reason I ask is that by using test data in these tables (and there are a lot of records),when I clear them out and append the live data the key field starts at something like 764525
View 1 Replies
View Related
Mar 20, 2014
just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.
I need to perform first, my observation is there is no primary key. when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.
CREATE TABLE [dbo].[tbl_OutBox_MT](
[TRAN_ID] [varchar](36) NOT NULL,
[OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL,
[OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)
[code]....
View 1 Replies
View Related
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 Replies
View Related
Dec 28, 2007
i would to make a column contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456")
and made it primary key and which data type i should use. i do not know whether i use after insert trigger in two columns one for three characters and another for code which has identity property >>>so please help me
View 4 Replies
View Related
Apr 25, 2006
Is it possible to connect SQL Sever 2005 Express from SQL Sever 2000 Enterprise Manager? Tried to connect but got the error message "must use SQL Server management tools to connect this sever"
Thanks
View 3 Replies
View Related
Jan 1, 2008
Hi, I have a question regarding inserting numbers automatically. For example, there are 2 fields. One is CountryID and other is CountryName. From Front-end I'm entering the Country Name "India" alone and in my table the CountryID "1" or what ever the sequence should be entered. I believe this can be done using Triggers. I read on KB article in Microsoft website. But didn't get clear idea. Is that the correct way?Can any one show me a small example of this? Or give an idea for this?
View 5 Replies
View Related
Feb 4, 2006
Hi,
I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.
Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.
View 9 Replies
View Related
May 13, 2014
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
View 8 Replies
View Related
Mar 29, 2015
I have run into a problem when searching for a Movie title that is a number (like the movie name 21)
This is an access database and the Movie_Name is a text field
The SQL I am using is
Select * from Movie_Data where Movie_Title = ‘” & Movie_Name & “’”
This works perfectly for everything except when the Movie_Name = a number
Is there a way to make this search work with both characters and numbers?
View 3 Replies
View Related
Mar 3, 2007
I am totally confused, i have set my data type in the ID field for my database as VarChar (SQL Server Database)
I am confused a have it declared in ASP.NET 2 as a string but it doesnt recognise it when i try and insert the data into a database, what am I doing wrong?
Thanks
1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
2
3 <script runat="server">
4
5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
6
7 End Sub
8
9 </script>
10
11 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
12 <span style="font-size: 11pt" id="SPAN1">
13 To initiate a new project, Enter details and
14 select <strong>Insert</strong>.
15 <br />
16 <br />
17 <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1" Height="50px" Width="324px" DefaultMode="Insert">
18 <Fields>
19 <asp:BoundField DataField="ProjectID" HeaderText="ProjectID" SortExpression="ProjectID" />
20 <asp:BoundField DataField="ProjectName" HeaderText="Project Name" SortExpression="ProjectName" />
21 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
22 <asp:TemplateField HeaderText="Project Manager" SortExpression="ProjectManagersID">
23 <EditItemTemplate>
24 <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ProjectManagersID") %>'></asp:TextBox>
25 </EditItemTemplate>
26 <InsertItemTemplate>
27 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ProjMan" DataTextField="PMName"
28 DataValueField="ProjectManagersID" SelectedValue='<%# Bind("ProjectManagersID") %>'>
29 </asp:DropDownList><asp:SqlDataSource ID="ProjMan" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
30 DeleteCommand="DELETE FROM [ProjectManagers] WHERE [ProjectManagersID] = @ProjectManagersID"
31 InsertCommand="INSERT INTO [ProjectManagers] ([PMName]) VALUES (@PMName)" SelectCommand="SELECT [ProjectManagersID], [PMName] FROM [ProjectManagers]"
32 UpdateCommand="UPDATE [ProjectManagers] SET [PMName] = @PMName WHERE [ProjectManagersID] = @ProjectManagersID">
33 <DeleteParameters>
34 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
35 </DeleteParameters>
36 <UpdateParameters>
37 <asp:Parameter Name="PMName" Type="String" />
38 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
39 </UpdateParameters>
40 <InsertParameters>
41 <asp:Parameter Name="PMName" Type="String" />
42 </InsertParameters>
43 </asp:SqlDataSource>
44 </InsertItemTemplate>
45 <ItemTemplate>
46 <asp:Label ID="Label1" runat="server" Text='<%# Bind("ProjectManagersID") %>'></asp:Label>
47 </ItemTemplate>
48 </asp:TemplateField>
49 <asp:TemplateField HeaderText="Project Type" SortExpression="ProjectTypeID">
50 <EditItemTemplate>
51 <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ProjectTypeID") %>'></asp:TextBox>
52 </EditItemTemplate>
53 <InsertItemTemplate>
54 <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ProjType" DataTextField="ProjectType"
55 DataValueField="ProjectTypeID" SelectedValue='<%# Bind("ProjectTypeID") %>'>
56 </asp:DropDownList><asp:SqlDataSource ID="ProjType" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
57 DeleteCommand="DELETE FROM [ProjectType] WHERE [ProjectTypeID] = @ProjectTypeID"
58 InsertCommand="INSERT INTO [ProjectType] ([ProjectTypeID], [ProjectType]) VALUES (@ProjectTypeID, @ProjectType)"
59 SelectCommand="SELECT * FROM [ProjectType]" UpdateCommand="UPDATE [ProjectType] SET [ProjectType] = @ProjectType WHERE [ProjectTypeID] = @ProjectTypeID">
60 <DeleteParameters>
61 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
62 </DeleteParameters>
63 <UpdateParameters>
64 <asp:Parameter Name="ProjectType" Type="String" />
65 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
66 </UpdateParameters>
67 <InsertParameters>
68 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
69 <asp:Parameter Name="ProjectType" Type="String" />
70 </InsertParameters>
71 </asp:SqlDataSource>
72 </InsertItemTemplate>
73 <ItemTemplate>
74 <asp:Label ID="Label2" runat="server" Text='<%# Bind("ProjectTypeID") %>'></asp:Label>
75 </ItemTemplate>
76 </asp:TemplateField>
77 <asp:BoundField DataField="StatusID" HeaderText="Status" insertvisible=False readonly=True SortExpression="StatusID" />
78 <asp:CommandField ShowInsertButton="True" ButtonType=Button />
79 </Fields>
80 </asp:DetailsView>
81 <br />
82 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
83 DeleteCommand="DELETE FROM [Project] WHERE [ProjectID] = @ProjectID" InsertCommand="INSERT INTO [Project] ([ProjectName], [Description], [ProjectManagersID], [ProjectTypeID], [StatusID]) VALUES (@ProjectName, @Description, @ProjectManagersID, @ProjectTypeID, @StatusID)"
84 SelectCommand="SELECT [ProjectID], [ProjectName], [Description], [ProjectManagersID], [ProjectTypeID], [StatusID] FROM [Project]"
85 UpdateCommand="UPDATE [Project] SET [ProjectName] = @ProjectName, [Description] = @Description, [ProjectManagersID] = @ProjectManagersID, [ProjectTypeID] = @ProjectTypeID, [StatusID] = @StatusID WHERE [ProjectID] = @ProjectID">
86 <DeleteParameters>
87 <asp:Parameter Name="ProjectID" Type="Int32" />
88 </DeleteParameters>
89 <UpdateParameters>
90 <asp:Parameter Name="ProjectName" Type="String" />
91 <asp:Parameter Name="Description" Type="String" />
92 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
93 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
94 <asp:Parameter Name="StatusID" Type="Int32" />
95 <asp:Parameter Name="ProjectID" Type="Int32" />
96 </UpdateParameters>
97 <InsertParameters>
98 <asp:Parameter Name="ProjectID" Type="String" />
99 <asp:Parameter Name="ProjectName" Type="String" />
100 <asp:Parameter Name="Description" Type="String" />
101 <asp:Parameter Name="ProjectManagersID" Type="Int32" />
102 <asp:Parameter Name="ProjectTypeID" Type="Int32" />
103 <asp:Parameter Name="StatusID" Type="Int32" DefaultValue=1 />
104 </InsertParameters>
105 </asp:SqlDataSource>
106 </span>
107 </asp:Content>
108
109
View 1 Replies
View Related
Aug 1, 2007
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies
View Related
Jul 23, 2005
I have a feeling I'll be forced to use a script and a trigger for thistype of field format but I'm wondering if any of your wizards couldpoint at a simple way I could do something like this:For example, if I want to be able to keep track of new orders followingthis incrementing convention:ORD100000001ORD100000002ORD100000003.... etc ...Does MSSQL2000 have features that I can simply set for this kind offield or will I be resorting to writing up a SQL script and a trigger?
View 11 Replies
View Related
Jul 25, 2006
I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).
Thanks!
View 3 Replies
View Related
Oct 8, 2015
The 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;
View 2 Replies
View Related
Jun 19, 2015
I have the following text field in SSRS report:
Version
2.2.32.7
1.0.0.0
2.0.0.0
1.2.0.0
2.1.8.8
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
View 3 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Apr 12, 2001
Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View 2 Replies
View Related
Nov 10, 2000
Hey guys.
I just wanted to ask you a question? we are having a field declared
as BIT and we want that field to be unique..We are not able to declare
as a primary key..Is there anyway to declare as unique from the database
point of view?..please help us out in this issue and i'd appreciate that.
Andrew.
View 5 Replies
View Related
Nov 10, 2003
Hi,
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc.
I tried to find it out but it seems to be very complicated.
Could anybody help me in this regards?
Thanks in advance.
Prasanna.
View 3 Replies
View Related
