<BACKGROUND INFO> We are in the database design stage of creating a db and we plan to use a GUID as the primary key for our tables (the app will be a smart client and will need to support offline functionality so when the app comes back online and the queued up inserts come thru we want to ensure that the primary keys are unique). We plan to have the app generate the guid and to send the GUID over as a parameter i.e.
<code>GUID myPrimaryKey = GUID.NewID(); </code>
and will not be using the DEFAULT NEWID clause on the CREATE TABLE statements.
</BACKGROUND INFO>
<QUESTION > Is it better to make the data type for the primary key columns to be uniqueidentifier or should the datatype be varchar(36).</QUESTION>
I only ask this because the tables have already been created with the primary key columns having a datatype of varchar(36) and changing them would be a little bit of work. Not a big deal but don't want to do it if we don't have to. If its really advantageous we will go ahead and do it but if not we would like to leave it as is. Thanks for your thoughts!
I am presently working on a ETL Process of importing data from XML source to the database (SQL Server 2000/2005).
I have GUID data in the XML file and i need to import that data into the database tables.
My Clarification is, if i import the GUID from XML to the database table, in the future, if the Database Engine generates new GUID's for new data, is there a posibility that the database engine might generate SAME guid as the one i imported from XML? As the GUID's in XML were not generated by the target Database Engine, can the database engine possibly generate the same GUID similar to the one i imported from XML?
anyone knows if there's an easy way to insert new GUIDs into uniqueidentifier cells when editing data in table on Management Studio Express? or Visual Studio 2005 Server Explorer? I can't find a shortcut, and copying from external tools is pain..
I suppose it's possible to build a VB macro in Visual Studio, would it work when editing table data?
Greetings. I just inherited doscovered a 3gb DB that is chewing up lots of CPU time on a pretty beefy server. This DB uses 4 main tables. It has many more, but most of the Inserts/ Updates/ Deletes/ and Joins are done mainly on 4 tables. Anyways, I figured out which sprocs use the most CPU between the hours of 7 and 7 every day, off to a good start. The first one is hardly every run. While it is a pig in terms of CPU usage, the amount of times it's run is somewhat minimal compared to others. So the next one is is a sproc that does auto refreshes, and is run every minute by thousands. I start to analyze it, particularly for missing indexes, but all is well. So I'm beating my head up against the wall and I realize, THE CLUSTERED PRIMARY KEY (PK) FOR THESE TABLES USE A GUID DATA TYPE....
So I have some questions about this data type, and also inserting into a clustered PK in general.
Are joins on this data type as bad as joining on varchar columns in terms of speed?
I don't see that any of my insert sprocs are taking a long time. So if an insert occurs, and a page split needs to happen (which is quite potential with this data type as a clustered PK), does it -- A) Begin the insert, complete the insert, then do a page split? Or does it -- B) Begin the insert, do the page split, then complete the insert? If A, it would make sense that I would see no lag for my insert sprocs, but also see a high CPU usage (provided I have a high number of page splits, which is yet to be determined. If B, I would expect to see a lot of long running insert sprocs, which I don't.
Hy! I am a beginner in SQLServer and I have to design a database with a lot of tables (30-50 tables). I have to choose a primary key for every table between an autoincrement and a GUID. If I choose autoincrement after an insert statement (from the client) I will have to go back to the server to get the value of the primary key which is a waste of time. On the other hand if I choose a GUID I can create the guid from the client and send him with the other values of the new row to the server and I have not to go back to the server. I am aware of the disk space of a guid field so which is the best for me: GUID or AUTOINCREMENT? Thank you!
I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId My VB code is this. Protected Sub btncreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncreate.Click 'set connection string Dim errstr As String = "" Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") 'set parameters for SP Dim cmdcommand = New SqlCommand("sprocInsertNewReport", conn) cmdcommand.commandtype = CommandType.StoredProcedure cmdcommand.parameters.add("@UserName", Session("UserName")) cmdcommand.parameters.add("@Week", vbNull) cmdcommand.parameters.add("@Date", vbDate) cmdcommand.parameters.add("@StartTime", vbNull) cmdcommand.parameters.add("@EndTime", vbNull) cmdcommand.parameters.add("@HeatTicket", vbNull) cmdcommand.parameters.add("@Description", vbNull) cmdcommand.parameters.add("@TakenAs", vbNull) cmdcommand.parameters.add("@Dinner", vbNull) cmdcommand.parameters.add("@Hours", vbNull) cmdcommand.parameters.add("@Rate", vbNull) cmdcommand.parameters.add("@PayPeriod", vbNull) cmdcommand.parameters.add("@LastSave", vbNull) cmdcommand.parameters.add("@Submitted", vbNull) cmdcommand.parameters.add("@Approved", vbNull) cmdcommand.parameters.add("@PagerDays", vbNull) cmdcommand.parameters.add("@ReportEnd", vbNull) Try 'open connection here conn.Open() 'Execute stored proc cmdcommand.ExecuteNonQuery() Catch ex As Exception errstr = "" 'An exception occured during processing. 'Print message to log file. errstr = "Exception: " & ex.Message Finally 'close the connection immediately conn.Close() End Try If errstr = "" Then Server.Transfer("TimeSheetEntry.aspx") End If My SP looks like this ALTER PROCEDURE sprocInsertNewReport
Ok, so I've broken down and decided to write myself an invoicing program. I'd like to use GUID fields as the Primary Keys in my table. Now, I basicly bill for two seperate items:
Deliverables and Services.
So, my Layout's gonna look something like
Create Table Invoice( ID UniqueIdentifier Primary Key, -- Other Data );
Create Deliverable( ID uniqueidentifier Primary Key, ParentInvoice uniqueidentifier, -- Other data); --...
Im sure there are probems with that as it's written feel free to edify me as to what they are.
my questions are such:
1) Does a uniqueidentifier field automagically get a value? 2) If not how do I generate one in TSQL? 3) If so, what do I use to store my Foreign Keys. 4) How do I declare my Foreign key constraints?
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
I am really struggling. I am trying to query a sql database table using a uniqueidentifier. Public Class SalesDataClass Public Function getAccountNumber(ByVal ID As String) As String Dim accountnumber As String = "0" 'Try Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("InterhealthCRM_MSCRMConnectionString").ConnectionString) Using command As New SqlCommand("getAccountNumber", connection) command.CommandType = CommandType.StoredProcedure
Dim parameterdat1 As New SqlParameter("@accountid", SqlDbType.UniqueIdentifier) parameterdat1.Value = ID command.Parameters.Add(parameterdat1) Dim parameterdat2 As New SqlParameter("@accountnum", SqlDbType.NVarChar, 20, ParameterDirection.Output) command.Parameters.Add(parameterdat2) connection.Open() command.ExecuteNonQuery() accountnumber = parameterdat2.Value Return accountnumber
End Using End Using 'Catch ex As SqlException ' Catch ex As InvalidOperationException ' Catch ex As Exception ' You might want to pass these errors ' back out to the caller. ' End Try End Function End Class Can someone help me correct my code.
Sproc: ALTER PROCEDURE [dbo].[getAccountNumber] -- Add the parameters for the stored procedure here @accountId uniqueidentifier, @accountnum nvarchar(20) output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT accountNumber from accountbase where accountid = @accountid return @accountnum END
I am using UNIQUEIDENTIFIER column for my table. I do not insert data for it. I left it on database by setting to default NEWID(). Now in my application I need the value of UNIQUEIDENTIFIER column I just inserted. Is there any function or query to get this value like in case of IDENTITY column we can get the latest inserted value from select @@IDENTITY.
Hey I was curious about the Uniqueidentifier is that better then using the @IDENTITY, apparently the Unique uses your computers mac address as a base???
does SQLCE supports the use of uniqueidentifier datatype.and how can i use it? i have heard that SQLCE supports only integer type as identity column. so what datatype i should use for identification(primary key).
I have a table with id(uniqueidentifier) as the primary key, and another table with id(int) as the foreign key. When I try to INNER JOIN them on id=id in a view, I get an error that uniqueidentifier and int are incompatible. I'm new to SQL SERVER and I consider uniqueidentifier as equal to AutoNumber in MSAccess. Isn't it so? If not, how do I make this JOIN work?
Hello all, I would like to put another line into my combo box using this SQL statement but this part "(select newid() as QuestionID, 'Select a Question' as QuestionText)" is not working. (select newid() as QuestionID, 'Select a Question as QuestionText) union all (SELECT * FROM (SELECT TOP 100 * FROM [dbo.aspnet_Questions]) as tbl) RETURN It gives me an error: Invalid object name 'dbo.aspnet_Questions'. Can anybody please help me with this error? Thank you, Vic.
I just want to ask if there is a passible explaination for why this code thosen't generate the proper RETURN VALUE, My coal is when a user uses the asp:CreateUserWizard i retrive GUID from the new account.With this I will check if the relation tabel has that value, so I would like to run this. Stored Proc. CREATE PROCEDURE proc_CustomerCheckExist@UserId uniqueidentifierASIF EXISTS (SELECT COUNT(*) FROM dbo.aspnet_LD_Customers WHERE UserId = @UserId) Return 1ELSE Return 0GO Problem is if I take 2 different GUID, I still get the same result "Return 1" as true even if I dont have the GUID in my tabel row Thanks!
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId) VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews @strHeader = 'Detta är den andra nyheten', @strAbstract = 'dn första insatt med sp:n', @strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...', @dtDate = '2003-01-01', @dtDateStart = '2003-01-01', @dtDateStop = '2004-01-01', @strAuthor = 'David N', @strAuthorEmail = 'david@davi.com', @strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem? Can I use something similar to the @@IDENTITY? I will be greatful for any ideas...
I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...
I am writing a C# application that uses a SQL server database to hold its data. I need to create a stored procedure that returns a particular row's primary key value. This is no problem if the primary key is an INT. But my primary key is a unique identifier, and the stored procedure doesn't want to let me return any values that aren't INTs. Can someone please tell me how to get around this?
I am trying to use a stored procedure and it seems to be giving me a error - I think it doesn't like the uniqueidentifier. I have included the error, stored procedure, and code behind that calls the stored procedure.Thanks for any help on this! This is the error I get:System.ArgumentException: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type. at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen) at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly() at System.Data.SqlClient.SqlParameter.Validate(Int32 index) at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at view_WardSec_PatientLogDetail2.UpdateRecord_buttonClick(Object sender, EventArgs e) in C:Documents and SettingsKBuchanan.LMHDesktopWebSitesNewest_ERViewsview_WardSec_PatientLogDetail.aspx.vb:line 358 Here is the stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER PROCEDURE [dbo].[UpdateTblVisit_WardSecPatientLog] ( @VID decimal, @lbl_ChiefComplaint nvarchar(50), @TriageDtTmTextBox datetime, @PtInRmDtTmTextBox datetime, @RnInRmDtTmTextBox datetime, @PhyInRmDtTmTextBox datetime, @EDHoldDisposDtTmTextBox datetime, @DispDschDtTmTextBox datetime, @ddlTriageNurse uniqueidentifier, @ddlPriNurse uniqueidentifier, @ddlEDPhy uniqueidentifier, @ddlPriRefPhy decimal, @ddlSecRefPhy decimal, @ddlDschRN uniqueidentifier, @ddlDschPhy uniqueidentifier, @ddl_MnsArriv decimal, @lblDschDiag nvarchar(50), @chkbox_LogComplete bit )AS SET NOCOUNT ON Update tblVisit SET vChiefComplaint = @lbl_ChiefComplaint, vTriageDtTm = @TriageDtTmTextBox, vPtInRmDtTm = @PtInRmDtTmTextBox, vRnInRmDtTm = @RnInRmDtTmTextBox, vPhyInRmDtTm = @PhyInRmDtTmTextBox, vEDHoldDisposDtTm = @EDHoldDisposDtTmTextBox, vDispDschDtTm = @DispDschDtTmTextBox, vTriageRnID = @ddlTriageNurse, vRnID = @ddlPriNurse, vEdPhyProvID = @ddlEDPhy, vPriRefPhyProvID = @ddlPriRefPhy, vSecRefPhyProvID = @ddlSecRefPhy, vDschRnID = @ddlDschRN, vDschPhyID = @ddlDschPhy, vMnsArrvID = @ddl_MnsArriv, vDschDiag = @lblDschDiag, vLogCompletedInd = @chkbox_LogComplete, vLogLastEdit = getDate() WHERE VID = @VID SET NOCOUNT OFF RETURN -----------------------------------------------------------------------------Following is the codebehind for a button oncommand event. This codeshould execute the stored procedure.----------------------------------------------------------------------------- Public Sub UpdateRecord_buttonClick(ByVal sender As Object, ByVal e As System.EventArgs) 'Use this to update the CareGiverID into the tbl_Visit table. Dim sbSql As New System.Text.StringBuilder sbSql.Append("EXEC UpdateTblVisit_WardSecPatientLog ") sbSql.Append("@VID, ") sbSql.Append("@lbl_ChiefComplaint, ") sbSql.Append("@TriageDtTmTextBox, ") sbSql.Append("@PtInRmDtTmTextBox, ") sbSql.Append("@RnInRmDtTmTextBox, ") sbSql.Append("@PhyInRmDtTmTextBox, ") sbSql.Append("@EDHoldDisposDtTmTextBox, ") sbSql.Append("@DispDschDtTmTextBox, ") sbSql.Append("@ddlTriageNurse, ") sbSql.Append("@ddlPriNurse, ") sbSql.Append("@ddlEDPhy, ") sbSql.Append("@ddlPriRefPhy, ") sbSql.Append("@ddlSecRefPhy, ") sbSql.Append("@ddlDschRN, ") sbSql.Append("@ddlDschPhy, ") sbSql.Append("@ddl_MnsArriv, ") sbSql.Append("@lblDschDiag, ") sbSql.Append("@chkbox_LogComplete, ") sbSql.Append("@UserID ") 'Response.Write(sbSql.ToString) 'Response.End() 'Create variables for each of the edited values Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ERTrekker_ProdConnectionString1").ConnectionString) Dim cmd As New SqlCommand(sbSql.ToString(), con) FindControls(DetailsView, "lbl_ChiefComplaint") Dim ilbl_ChiefComplaint As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "TriageDtTmTextBox") Dim iTriageDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "PtInRmDtTmTextBox") Dim iPtInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "RnInRmDtTmTextBox") Dim iRnInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "PhyInRmDtTmTextBox") Dim iPhyInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "EDHoldDisposDtTmTextBox") Dim iEDHoldDisposDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "DispDschDtTmTextBox") Dim iDispDschDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "ddlTriageNurse") Dim iddlTriageNurse As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlPriNurse") Dim iddlPriNurse As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlEDPhy") Dim iddlEDPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlPriRefPhy") Dim iddlPriRefPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlDschRN") Dim iddlDschRN As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlDschPhy") Dim iddlDschPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddl_MnsArriv") Dim iddl_MnsArriv As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "lblDschDiag") Dim ilblDschDiag As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "chkbox_LogComplete") Dim ichkbox_LogComplete As CheckBox = CType(MyControl, CheckBox) 'Add all of the parameters to the command With cmd.Parameters .AddWithValue("@VID", Request("vID")) .AddWithValue("@lbl_ChiefComplaint", ilbl_ChiefComplaint.Text.ToString) .AddWithValue("@TriageDtTmTextBox", iTriageDtTmTextBox.Text.ToString) .AddWithValue("@PtInRmDtTmTextBox", iPtInRmDtTmTextBox) .AddWithValue("@RnInRmDtTmTextBox", iRnInRmDtTmTextBox.Text.ToString) .AddWithValue("@PhyInRmDtTmTextBox", iPhyInRmDtTmTextBox.Text.ToString) .AddWithValue("@EDHoldDisposDtTmTextBox", iEDHoldDisposDtTmTextBox.Text.ToString) .AddWithValue("@DispDschDtTmTextBox", iDispDschDtTmTextBox.Text.ToString) .AddWithValue("@ddlTriageNurse", iddlTriageNurse.SelectedValue.ToString) .AddWithValue("@ddlPriNurse", iddlPriNurse.SelectedValue.ToString) .AddWithValue("@ddlEDPhy", iddlEDPhy.SelectedValue.ToString) .AddWithValue("@ddlPriRefPhy", iddlPriRefPhy.SelectedValue.ToString) .AddWithValue("@ddlDschRN", iddlDschRN.SelectedValue.ToString) .AddWithValue("@ddlDschPhy", iddlDschPhy.SelectedValue.ToString) .AddWithValue("@ddl_MnsArriv", iddl_MnsArriv.SelectedValue.ToString) .AddWithValue("@lblDschDiag", ilblDschDiag.Text.ToString) .AddWithValue("@chkbox_LogComplete", ichkbox_LogComplete.Checked.ToString) .AddWithValue("@UserID", System.Web.HttpContext.Current.Session("UserID")) End With ' Open the connection and execute the command Try con.Open() If cmd.ExecuteNonQuery() < 1 Then Throw New System.Exception("The record was not updated") End If Catch ex As Exception System.Web.HttpContext.Current.Response.Write(ex.ToString()) Finally If Not con Is Nothing AndAlso con.State = System.Data.ConnectionState.Open Then con.Close() End If End Try End Sub
Hello everyone. I have 2 tables in MSSQL Server 2000, Products(idProduct, idProductType, name, quatity) and ProductsType(idProductType, name).
idXXXX are the primery keys and the data type is uniqueidentifier.
In table Products, the collum idProductType is a foreign key refering table ProductsType.
I can insert as many product types as i want without any problems. Insert INTO ProductsType(name) VALUES('milk')
When inserting products i use the idProductType that was automatacly generated in last insert. INSERT INTO Products (uuidProductType,name,quantity,) VALUES ('AD9388A3-CA86-482D-B57F-6FA068E7D405','President Milk 50cl',500)
but i got the following error in SQL Query Analyzer: Server: Msg 8169, Level 16, State 2, Line 1 Syntax error converting from a character string to uniqueidentifier.
I have searched the forum and i cant find any solution. i need to use uniqueidentifer data types because replication with PDA devices.
USE [Ebdata] GO /****** Object: Table [dbo].[EB_Bootloaders] Script Date: 07/12/2006 10:06:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[EB_Bootloaders]( [ID] [bigint] IDENTITY(1,3) NOT NULL, [BootName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL, [Version] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL, [CSD] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL, [FilePath] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL, [FileSize] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL, [CreateDate] [datetime] NULL CONSTRAINT [DF_EB_Bootloaders_CreateDate] DEFAULT (getdate()), [CreatorID] [uniqueidentifier] NOT NULL, [UpdateDate] [datetime] NULL, [UpdateUser] [uniqueidentifier] NULL, CONSTRAINT [PK_EB_Bootloaders_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [Ebdata] GO ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users] FOREIGN KEY([CreatorID]) REFERENCES [dbo].[aspnet_Users] ([UserId]) NOT FOR REPLICATION GO ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users1] FOREIGN KEY([UpdateUser]) REFERENCES [dbo].[aspnet_Users] ([UserId]) NOT FOR REPLICATION
The funny thing is that i copied the sp code from another which runs perfect, I insert a Guid from an asp.net page.
Hope someone can help me here because im am stucked!
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId) VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews @strHeader = 'Detta är den andra nyheten', @strAbstract = 'dn första insatt med sp:n', @strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...', @dtDate = '2003-01-01', @dtDateStart = '2003-01-01', @dtDateStop = '2004-01-01', @strAuthor = 'David N', @strAuthorEmail = 'david@davi.com', @strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem? Can I use something similar to the @@IDENTITY? I will be greatful for any ideas...
Hi: I am using the built in securty database from vs2005 I am trying to write an insert trigger that will add a role each time a user is added but I seem to be having difficulty I believe it's with the uniqueidentifier datatype. when I run this trigger via an insert statement I get the following error. Can anyone set me straight? ========error============== Cannot insert the value NULL into column 'RoleId', table 'R:AAAPROJECTSASPVS2005MERCERBUCKSAPP_DATAASPNETDB.MDF.dbo.aspnet_UsersInRoles'; column does not allow nulls. INSERT fails. ==================insert statement==========
Declare @NewUserID as uniqueIdentifier set @NewUserID=newID() print Cast(@NewUserID as varchar(50)) insert into dbo.aspnet_Users (ApplicationID,UserID,UserName,LoweredUserName,LastActivityDate) Values('f23f01f0-7ad7-463b-87e2-f3e9141e6426',@NewUserID,'lrchase','lrchase','7/20/2005') ======trigger============================ set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER TRIGGER [InsertRole] ON [dbo].[aspnet_Users] AFTER INSERT AS DECLARE @RoleID uniqueidentifier DECLARE @UserID uniqueidentifier Select @UserID=Userid from inserted Print @UserID SELECT @RoleID = RoleID FROM dbo.aspnet_Roles where dbo.aspnet_Roles.LoweredRoleName='user' Print @RoleID SET NOCOUNT ON; Insert Into dbo.aspnet_UsersInRoles Values(@UserID,@RoleID)
hi. im new in using rowguid, i want to know how long can this datatype can hold. and is there a chance that value can be duplicate or the sql is checking it already before it create a new one.
and what will happen if i select yes in isrowguid. thnx
Hi all,I'm trying to run a select where auniqueidentifier/GUID equals a hex, but I don't seem to be gettingmatches.For example, this query returns the expected record:select * from items where itemGUID ='{11111111-2222-3333-4444-555555555555}'But this one does not:select * from items where itemGUID = 0x11111111222233334444555555555555Any tips?thanks, -Scott
