I have a table, let say it is called 'profile', already created without a primary key. Now, I want to add a primary key to one of the fields called serialno on that table. I am currently using EMS SQL Manager 2005 tool to manage my db and tables. When I try to add the primary key, I am getting following error message.
"CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is ''"
I need help!! Otherwise, I have to create a whole new table and I can't afford do that.
I want to crete temporary table with this coammnd "CREATE TABLE Temp (ID int, name varchar(50))". I would like to know which command is used for setting primary key and identity on ID field. Thankyou in advance.
Not sure if this is the right place for this post or not, but I'll give it a try...
If I am inserting a record into a table that uses a Primary Key, but not an Identity column, how do I insert the new primary key value? I started out with SQL just letting the Identity column increment itself for primary keys, but I cannot do that for this particular situation.
If 'AddressID' is the primary key in the 'Address' table, could I do something like this:
INSERT INTO Address (AddressID, Field1, Field2, ...) VALUES (**max value of primary key in Address table, plus 1**, 'field1data', 'field2data', ...);
If that works, how would I make this part of the insert query: **max value of primary key in Address table, plus 1** ?
I hope this question is clear and makes sense. If it's not, please let me know.
Uma writes "Hi Dear, I have A Table , Which Primary key consists of 6 columns. total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
CREATE TABLE [dbo].[property_instance] ( [property_instance_id] [int] IDENTITY (1, 1) NOT NULL , [application_id] [int] NOT NULL , [owner_id] [nvarchar] (100) NOT NULL , [property_id] [int] NOT NULL , [owner_type_id] [int] NOT NULL , [property_value] [ntext] NOT NULL , [date_created] [datetime] NOT NULL , [date_modified] [datetime] NULL )
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance - property_instance_id will never be a foreign key into another table - queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row - Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
Hello, I am a SQL rookie. I have followed the tutorial and installed MSDN as it says. However, I am unable to create a database with WebMatrix. I keep getting an error that reads "SQL Server does not exist or access denied. ConnectionOpen (Connect())."
When I loaded the SQL, everything seemed to go well. I got all of the results that the tutorial said I should.
Please advise on my next steps. Thank you in advance.
I'm trying to set up a new job to update a field in the table, I've managed to get the select syntax to work, but when I added an IF statement and Update syntax it didn't like it and the following error was shown:-
Server: Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'BEGIN'.
This is the syntax I'm trying to use for the job:-
USE EmployerEngagement
IF (SELECT On_Stop FROM tblEmployer LEFT OUTER JOIN tblWP_Details ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID LEFT OUTER JOIN tblVetting ON tblWP_Details.Record_ID = tblVetting.Record_ID WHERE tblEmployer.On_Stop = 0 AND tblVetting.Next_Vett_Date <= GETDATE()) BEGIN UPDATE tblEmployer SET On_Stop = 1 END
Basically I just want to change the On_Stop value from 0 to 1 if the Next_Vett_Date is before or on todays date.
Hello,I've noticed on my DTS that it is connecting to the remote SQL Servervia Named Pipes. Not sure why I've been getting the following error:[DBNETLIB][ConnectionWrite (WrapperWrite()).] but I wanted to changethe DTS from using Named Pipes to use TCP/IP. How to do it?Also, if anyone has any clues on what this error means, please let meknow!Step Error Source: Microsoft Data Transformation Services (DTS) DataPumpStep Error Description:The number of failing rows exceeds the maximumspecified. (Microsoft OLE DB Provider for SQL Server (80004005):[DBNETLIB][ConnectionWrite (WrapperWrite()).]General network error.Check your network documentation.)Step Error code: 8004206AStep Error Help File:sqldts80.hlpStep Error Help Context ID:0Tnks!
I am trying to connect to my database in SQL SERVER EXPRESS via dreamweaver. I am trying to create DSN connection but when i click on sql server in the optionlist it lists about 100 options of the sql server i want to connect to. How do I find out which one is the one I have my tables in and the sql server express that I use.
I am unable to set the value of my SqlParameter unless it is to a string.sqlCmd.Parameters.Add('@myParameter', SqlDbType.Bit).Value := false; Setting it to false or 0 results in error: "Incompatible types: 'Object' and 'Boolean' (or 'Integer')" I've tried to cast them as objects and that didn't work. I don't understand why something like 'false' (as a string) will. FYI: I'm using Delphi.NET and hating it.
Hi, I have just started with ASP, and have a problem. I have created a stored procedure which looks to a specific tablename for information, based upon the users choice from a dropdown list. The control works fine when executed from within visual web developer, and I manually enter the value that the variable expects. However I can not get the dropdown listbox value to be written to the SQL value. I have tried for days, traweled the net for answers, borrowed 3ft in height of SQL books! so either I am doing something fundamentally wrong, or I am missing something. My SP is: ALTER Procedure GenericTableSelect @tablename VarChar(20) AS Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT [base model] FROM ' SELECT @SQL = @SQL + @tablename Exec ( @SQL) and from the page the command to call it is: SelectCommand=generictableselect></asp:SqlDataSource> But this fails to compile and comes back with "@tablename not defined" any pointers in the right direction would help. The object of this is for two drop down boxes - the first is populated from one database of categories, the selection of which populates the second drop down list with items from within that category. Cheers, Richard
Hello all, I've gotten approval at my work place to implement a new web server cluster. We currently have one web server running Windows 2003 and one SQL 2005 box. I've gotten approval to purchase 2 new web servers that will eventually become our primary servers. I've browsed Microsoft's site but can't seem to find any FAQ's or How-Tos on clustering that are of any help. Is there anyone out there who has some info that could help me get started?
I have been using MySQL as the back end of my .Net 2.0 applications for a while using a custom membership provider without any problems.Having now switched to a new webhost which supports both MySQL and MS SQL Server 2005 and also creating a new site thought I should set up the new site to use MS SQL..... I am having trouble however setting up the SQL server to act as the membership provider. Following the advice on here I tried using aspnet_regsql.exe... Firstly, when trying to get the list of databasses on the remote server I get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'Sysdatabases' If I then just type in the name of my database and go through to the end I get the following error:Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated. I can connect to my MS SQL server remotely without any problem using the likes of MS SQL Server Management Studio Express. Is there any other way I can setup the databases needed for user and roles access control or is the usage of MS SQL much more complicated than what MySQL was?
hey guys heres my problem iam trying to install SQL server2000 but i keep on running into problems....?? here is what iam having problems with setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL it keeps saying the instance name specified is invalid there has to be an easyer way to run the program...?? i have all ready set up an instance name and the SAPWD. any suggestions?? please help
I have the following to create a new table.... CREATE TABLE dbo.test (Id varchar(20) NOT NULL PRIMARY KEY, Name varchar(256) NOT NULL, visible bit NOT NULL )
I need to set the default value of the bit visible to 1. How can this be done using a T-SQL statement? Thanks, Zath
How can I change the Default value of a column in a table using sql? for instance I have a table called Phone and I have a field called AreaCode and the default now is '123' . I would like to change it to '456'.
I am using Windows NT 4.0, SQL Server 6.5 and Exchange 5.0. I would like to setup SQL mail. I tried to create mail login, NT expects for MS mail server name and login. When i enter my exchange server/login name does not recoganise.
I am getting the message DESCRIPTION: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
it is a 3rd party app I can't change with some ugly sql. My current lock setting is
min max config run locks 5000 2147483647 0 0
Do I need to change the min value to higher? What would be a good value to try, I have 16 gig of memory on the server
Hello, Iam new at this SQL stuff and want to setup an SQL Server at work and need to know what do i need to use this software. I have a EVAL version of 6.5 and was told you need MSACCES or Visual Basic. Is there anything else that i need to use this? Thanks!!
Does anyone know if it is possible to set permissions on certain fields in a way that will restrict access for reading a field but will allow a user to do a select using that field? I am looking for a way to block read access to a sensitive field but I want to be able to query other fields using the value that is in the field.
Hello, I am trying to setup a Microsoft Express SQL 2005 server to be accessed by PHP. PHP is setup just fine, the problem lies in the database (or so I believe). Quite simply, the problem is that it won't connect. I need to know if I need to have any special configuration on the database for it to connect. I am fairly positive that I have all of the login information correct, so that is not the problem.
Hi, I have this query basically im trying to set the @db to the current database depending on the quarter. This will be used in a package to change the database that the package will use.
DECLARE @db nVarchar(4000) SET @db = N'TESTDB' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1') THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7') THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END EXECUTE sp_executesql @db, N'@level tinyint', @level = 35
I get the error Incorrect Syntax near 'TESTDB04_Q3'.
