SQL 2012 :: Add Unique Numeric Identifier For Text Field With Same Name?
Aug 21, 2014
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
create table dbo.Movie_Pre_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50)
);
insert into dbo.Movie_Pre_Fix (Report_Month, ID,Movie_Name)
VALUES
(201406,0721312144,'SAW'),
[code]....
View 6 Replies
ADVERTISEMENT
Dec 27, 2013
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
View 3 Replies
View Related
Sep 22, 2015
I have a field I am trying to bring into a SQL statement
,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'
I get this error message
Conversion failed when converting from a character string to uniqueidentifier.
the field specs' originatingleadid is attached ....
View 9 Replies
View Related
Mar 12, 2015
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
View 5 Replies
View Related
Jan 19, 2005
I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.
So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.
I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.
So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?
Here is my stored procedure:
CREATE PROCEDURE dbo.REQ_HDR_INSERT
@ddo varchar(50) = null,
@requestor varchar(100) = null,
@dt datetime = null,
@abrtype varchar(20) = null,
@subject varchar(250) = null,
@description varchar(500) = null,
@review char(10) = null,
@ay char(4) = null,
@origallo varchar(50) = null,
@reqallo varchar(50) = null,
@logl_del_dt datetime = null,
@phys_del_dt datetime = null
AS
Insert into dbo.DIM_ABR_REQ_HDR (ABR_ddo, ABR_requestor, ABR_dt, ABR_type, ABR_subject, ABR_description, ABR_review, ABR_AY, ABR_orig_fund_allo, ABR_req_fund_allo, ABR_LOGL_DEL_DT, ABR_PHYS_DEL_DT)
values (UPPER(@ddo), UPPER(@requestor), @dt, UPPER(@abrtype), UPPER(@subject), UPPER(@description), UPPER(@review), @ay, convert(money, @origallo), convert(money, @reqallo), @logl_del_dt, @phys_del_dt)
return @@identity
GO
I would be using @ay and @ddo as the first two parts of the unique identifier number. Any help is appreciated.
Thank you,
-D-
View 1 Replies
View Related
Jan 31, 2004
How can i get a numer for using it as unique identifier in two related tables?
View 3 Replies
View Related
Feb 29, 2008
I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int Or uniqueIdentifier? Or is it all the same??
View 11 Replies
View Related
Aug 1, 2005
Hi there,I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.Any help would be greatCheersStu
View 2 Replies
View Related
Nov 25, 2005
Hello!I work with oracle, mySQL but im completely new to SQLserver.How can i set a auto-increment unique variable for userID ?After deleted user, the userID should be never used again on a new user.Thank you,
View 1 Replies
View Related
Oct 18, 2013
I have trouble to get the uniqueidentifier I just inserted out.
---sp
CREATE PROCEDURE dbo.FAC_Ins_USR
@LAST_NAME AS nvarchar(60)
,@FIRST_NAME AS nvarchar(60)
,@NewID uniqueidentifier output
AS
BEGIN
[Code] ....
The new data went into the table, and the print @myErr shows 0.
But print @myID shows nothing.
---here is the part of the table
CREATE TABLE [dbo].[USERS](
[USER_ID] [uniqueidentifier] DEFAULT NEWID() NOT NULL,...
View 11 Replies
View Related
Jul 25, 2007
Hello,
Does anyone know if there is a read-only unique identifier of a database in a given server instance? E.g. a value stored somewhere in the database meta data that is generated during CREATE DATABASE...
View 3 Replies
View Related
Jul 20, 2006
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
View 1 Replies
View Related
Jan 4, 2007
I need to develop a strong licensing solution based on the database accessed...
Currently our solution is easily hackable because the the license information is kept in the database of your choice and is not dependant on some static information, the current encryption key is static and kept in the software so it can be hacked easily. What i want to do to change this is simple in nature but i don't know how to get that one particular info i need.
I want to get some kind of unique identifier for a database (catalog) that sql server could generate. This info must be static and must not be movable. If for example, someone would backup and restore, this information would not be transfered with the backup. Thus, a user that backups his database and restores it on another database server or another database catalog even on the same server would corrupt his license since the Unique ID returned by the SQL Server would be different; the hashing code would change and thus the decryption would fail.
Is there any such info i can query using SQL that will not require admin rights?
View 2 Replies
View Related
Jun 10, 2008
Please see below post
View 2 Replies
View Related
Sep 5, 2005
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
View 5 Replies
View Related
Jul 10, 2006
Hi guysI'm having a nasty problem with bulk copying into a table that hasunique identifier column. I'm coding on C++, using ODBC driver.I'm coping from a file containing UID description like this:{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}(I also tried a non-bracket version)and in the DB table I get:4233347B-4235-4433-452D-353238302D34which seems random at first sight, but it is:[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.and my question is: what the hell?my code look like this:if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)ret = -1;if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,SQLUNIQUEID, colNo) == FAIL){ret = -1;}(I also tried a VARLEN version:)if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){ret = -1;}and then stuff like sendrow ans save:if (bcp_sendrow(m_hDbproc) == FAIL)return -1;if (bcp_batch (m_hDbproc) == -1)return -1;I also tried specyfiling the column type in the m_hDbproc handle asSQLUNIQUEID, but either I'm doing something wrong, or this just isn'tthe way of a bulk copy samurai:INT * pValue=new INT;INT *pLen=new INT;*pValue=0x24;bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);So like, PLEASE help me on this. I need to get this working by lastmonday :]Thanx, M.
View 4 Replies
View Related
Jul 2, 2015
Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.
I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".
INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname)
SELECT KontaktID, AnredeID, Titel, Nachname
FROM [Susi].MSCMS.Core.tKontakte AS Client
WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);
[Code] ....
View 8 Replies
View Related
Feb 9, 2005
Function GUID() As String
GUID = System.GUID.NewGuid().ToString()
End Function
GUID is always 36 characters, for example:
1737be72-fe96-4c3c-b455-3730b049bef9
What the best way to store this in a MS SQL database? I'm thinking of using a fixed length string (char). Is there a better way than to just store this 36 character field?
View 2 Replies
View Related
Jul 20, 2005
I would like to pass an unique identifier (UserID) to a Crystal Reportfrom a SQL stored procedure. I found an article from Business Objectsabout this issue, but I couldn't get my head around it's hard codedexample.Does anyone have an example of this? Would I need to also create aparameter in my Crystal Report?Help appreciated.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Aug 9, 2006
May I know what does the syntax of inserting data into a field of type Unique Identifier look like?
[code]
INSERT INTO THAI_MK_MT_Log(GUID, Status) VALUES ('2331486348632', 'S')
[/code]
The "2331486348632" is to be inserted into a unique identifier field.
If i coded the insert statement as the above, I got an error saying that "Syntax error converting from a character string to uniqueidentifier".....
Can anyone help?
View 11 Replies
View Related
Oct 12, 2015
I am doing migration from oracle11g to SQL server2012. Facing error while data migration from Oracle rowid dat typeto SQL uniqueidentifier using SSMA6.0
Errors: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.
Refer above error.
View 2 Replies
View Related
Jan 6, 2015
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE RTRIM([program_name])
END ProgramName
, Val1.UqID
, Val1.UqIDStr
[Code] ......
View 6 Replies
View Related
Apr 3, 2015
I have the following insert statement:
INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY
As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?
View 3 Replies
View Related
Aug 21, 2007
Here's a problem description I read on another post but I have the same issue:
I am trying to Import Data from a csv file into a SQL Sever table. The Data being imported has a Unique Identifier in it but it is being considered as DT_WSTR datatype in SSIS
When i directly try to import, it gives an error:
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
I Tried to use Data Conversion to convert it to DT_GUID and import into SQL Server table but it again throws the above error.
Anyone has a clue?
Thanks!
View 3 Replies
View Related
Nov 2, 2015
I developed a main report containing numerous subreports. But now I'm trying to page break on these subreports and because these subreports all have sub-sub reports, I get this error when I try to run this report..The value '22' is invalid. Valid values are between '0' and '1'. (rrRenderingError)..I am grouping on uniqueidentifers and I do not get errors on subreports that have sub-subreports. How can I avoid this error and get these subreports to page break? (I don't get any errors if I remove the page breaks).
View 3 Replies
View Related
May 1, 2007
Hi !
I have a problem with the unique identifier and don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
Thanks!!
View 2 Replies
View Related
Apr 13, 2015
I am new to MDM profisee tool and currently working for Addres verification project for my organization.I wanted to clear my doubts here about Unique Identifer in Stage table and how it works.. Here what i understand till now:
Step 1) I created an Entity using MDM profisee UI and it generated a stage table in MDS database called stg.Address_leaf
Step 2) I have loaded data from external source to MDS stage table using ETL and passed Import type as 2 and Import status id as 0
Step 3) I have run store procudure system generated something stg.udp_Address_leaf to load the model and passed the version name as Version_1, Log flag as '1' and Batch tag as 'Address'
Now my below are my questions:
1) What is the field i can use in MDS stage table to populate my unique intifier value coming from source? (lets say Address_Id is my Unique value for all the records coming from source)
2) Where/how Unique Identifier is useful in this process? Will this be useful in next time load from stage to Model?
3) If i truncate and load my MDS stage table in next run and few earlier records has been updated how it will update those records in Model? will this process (code present in SP) recognize by Unique Identier column present in MDS stage table?
View 3 Replies
View Related
Mar 8, 2015
See attached image...
The columns are of type text
I made this insert stmt using a stored proc...
I mean the [text] field values from another table is converted to varchar(max) and then to VARBINARY(max) and then to HEX value.
Why this error only when I try to insert to this particular column [[Conclusions]] ?
Other columns ( of type text ) did not have this issue
View 1 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Jan 19, 2005
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies
View Related
Apr 6, 2007
I just updated to SQL Express SP2 per this Feedback item:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=132806
But I don't see an option to use quoted identifiers. Is it not included in the Express version?
View 1 Replies
View Related
Aug 17, 2007
Hi, Im trying to run this query
UPDATE DataModif
SET t.Ind_des = Replace (t.Tit_Des,"'",'"')
FROM Tit_Modificables t
WHERE
t.Ind_num in
(SELECT CAST (t2.Ind_num AS VARCHAR(10))
FROM Tit_Modificables t2
WHERE t2.Emp_id ='1100004' AND t2.Ejercicio_fiscal = 2003 AND t2.Nom_tabla = CAST (10 AS NUMERIC))
but ir sends this message error
The multi-part identifier "t.Ind_num" could not be bound.
I don't know if there is a problem because I'm using an Update.. FROM
Any help?
View 6 Replies
View Related
Apr 28, 2006
Hi,
When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "alias.fields" could not be bound.
Where alias is a any alias and, fields is a field of the table with alias.
Already exists one fix to patch this?
Thanks
View 39 Replies
View Related