SQL Server 2008 :: Random GUID And New SequentialID
Mar 20, 2015
I have a DB using random GUID values as a PK. I know it should not be that way but it is third party and I am trying to persuade them to do something else, like a sequential GUID using NEWSEQUENTIALID. My question is this. Since I have GUID values already in the database, if we change the table definition from simply uniquidentifier to NEWSEQUENTIALID what is to keep NEWSEQUENTIALID from generating a GUID that already exists and creating a PK violation?
View 3 Replies
ADVERTISEMENT
May 1, 2015
We have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online system
The query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running.
View 6 Replies
View Related
May 20, 2015
While trying to solve a SQL challenge I found myself trying to understand what is happening when you CAST a INT to date time.
Trying to understand the results. Here are some random numbers and Castings. My question is why do they produce the datetimes they do?
SELECT CAST((1.1) AS DATETIME)
SELECT CAST((200) AS DATETIME)
SELECT CAST((15) AS DATETIME)
SELECT CAST((99.99999) AS DATETIME)
View 9 Replies
View Related
Jun 2, 2015
I have written a script to GENERATE Random Password. But I want to modify that to a some kind of password which is little easy to remember!
I have seen some of the online sites where they generate passwords which are easy to remember and having some way to remember them,
my script:-
declare @db_pswd nvarchar(4000);
declare @len1 int = 16,
@min tinyint = 40,
@range tinyint = 74,
@exclude varchar(50) = '11cdtyuXR#0:;<=>?@O[]`^/abfty#$%^&1234567890*',
@output varchar(500)
[Code] .....
View 4 Replies
View Related
Apr 8, 2008
I am trying to test a stored proc and I can't execute the stored proc when I use a Guid. Has anyboyd had the same problem. I right click in VS 2008 on the Stored proc click Execute and fill in the variables one of them is a Guid and no records are returned.
Here is my guid, I have tried '{0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2}', {0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2} and "0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2" no success. This stored proc might not work but I have had the same issue with other stored procs when I wanted to test them by using execute and using a Guid.
Testing with an int works fine when I try to Select the records by UserID which is a Guid no cigar. I have notice this behavier before is there a way around this problem, thanks newbie
View 3 Replies
View Related
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
Oct 14, 2004
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Help is greatly appreciated.
View 1 Replies
View Related
Jun 11, 2008
Dear Expert,
Iam new to sql server2000
I have table Table1 in that table I want to generate unique guid in one column.I want the code for that one kindly help me.
View 2 Replies
View Related
May 1, 2006
In Access, newID returns a unique for the table. In SQL Server, newid()returns a GUID - unique in the world. I do not need or desire thatcomplexity. Is there a way to get a simple unique int on the table inSQL Server?Brad Eckhttp://www.sitesdynamic.comhttp://www.basketsetcetera.com*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Apr 19, 2007
Hello,
I want to create unik primary key for my database.
Somebody advice me to use the GUID.
I'm not sure on how it can be created. I have to create it on my application side or database side?
Kindly advice.
Best regards,
Tee Song Yann
View 7 Replies
View Related
Mar 4, 2008
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
View 4 Replies
View Related
Mar 31, 2008
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
View 1 Replies
View Related
Jan 21, 2008
I have three tables that are joined together to retrieve a piece of data.Table One has the columns pid1(PK), idTable Two has the columns pid1, qid1, idTable three has columns qid1, question, answerHere is my sql statement:SELECT Table1.pid1, Table2.qid1, Table3.Question FROM Table1 JOIN Table2 ON Table1.pid1=Table2.pid1 JOIN Table3 ON Table2.qid1=Table3.qid1 WHERE Table1.pid=...(N)I put an elipsis because I'm stuck on how to proceed from here. See I need to randomly select a value from column pid1 in table1 then use this value to replace N in the sql statement, then have the statement run and randomly return exactly one piece of data instead.Is this possible? How can you randomly get one piece of data from joining three table without a WHERE CLAUSE.
View 1 Replies
View Related
Sep 23, 2004
As a part of my unpaid internship, I am creating a ASP.NET interface for a MS SQL Server 2000. The table I am having problems with has over 750,000 rows by 26 columns. There isn't a primary key. It stores a transaction dump from another primitive Database server.
Problem:
When perform a query I get one set of results. I run the same query again after a short wait and the rows returned are in a different order. The majority of the rows returned are the same ones returned in the previous query. Only, some rows may be missing and the order may change.
I really wasn't surprised by some duplicate rows. I am confused why they are showing up in a different order and the above mentioned inconsistancy in results.
I thought I was having problems with my Repeater Control; however, SQL Query Analyzer returns the same results.
Question:
What is causing the problem?
View 3 Replies
View Related
Jun 12, 2007
I Got the Sql query "Select Top 1 * from Books order by NewId()"........ for select Random row in a table .. any one can explain Above Query
Regards,
S.sajan
View 4 Replies
View Related
Apr 23, 2008
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
Any pointers to documentation or any advice?
Thanks,
A Brown
View 1 Replies
View Related
Feb 25, 2008
Hello!
Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7.
I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong?
Many thanks for help,
Jan
View 1 Replies
View Related
Jan 14, 2008
Hi I am using VS 2005 with SQL 2005.
I had a Datagrid and i Need to bind data into it.
I need to show Random Unique ID's based on the Parameter i pass.
I wrote select top 5 * from Employee order by NEWID()
for this i had created a Stored Procedure as
create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end
Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......
View 6 Replies
View Related
Oct 3, 2015
I have a function which generate random number in range :
CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO
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??
SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )
View 2 Replies
View Related
Oct 29, 2007
We have a legacy C++ application which uses ODBC to connect to SQL server. The application is a server process that opens a connection and caches it. Unfortunately on a connection drop, it does not refresh the connection. The standard installation is a 3 machine web/app/sql setup.
It has been working fine since last 4-5 years unless the SQL Server goes down. In that case, I keep seeing "Function sequence error" and "Communication link failure" errors. On one particular client installation on application server, I started getting this exception while SQL Server was up and running (on a different machine). I would see the connections randomly dropping in the activity monitor after 3-4 hrs of inactivity.
I wrote a .NET test app which uses ODBC drivers for SQL Server and makes a connection and then just waits. Out of 5 connections established by this application I saw one connection being dropped as well. Another test app used ADO.NET and it did not drop any connections. I asked the IT to check the network and they say its fine.
The servers are W2k3 SP2 and database is SQL Server 2005. IsAutoClose on all the databases is false. Nothing goes in the logs even after turning on traces using DBCC.
Since the connection drop was very random, I turned off connection pooling on "ODBC driver for SQL Server" on the application server and now it is working fine.
Can someone explain me what could be the reason? Also, is there a way to upgrade or downgrade ODBC drivers on W2K3 machines?
Thanks,
Pranav
View 8 Replies
View Related
Mar 7, 2008
Hi all,
New to this so please be patient and please help.
I have developed an SQL 2005 Express command line option install for our company which has been working seamlessly for the last 18 months.
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
I downloaded the €œMicrosoft SQL Server 2008 Express CTP, February 2008€? from http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en
I simply replaced the 2005 file €œSQLEXPR.EXE€? with the 2008 file €œ€?, recompiled the installation and tested only for it to fail. I than read the 2008 books online and noted the change in command line options.
http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en
http://msdn2.microsoft.com/en-us/library/ms144259(SQL.100).aspx
I then changed the command line to suit the Microsoft 2008 books online, recompiled the installation and tested only for it to fail once more.
Interestingly I tested the install from the default GUI and at the point of adding the €œsa€? login credentials it fails to allow the installation to proceed. Strangely by selecting the windows authentication credentials, €œnext€? than €œback€? it now allows me to add the €œsa€? login credentials and continues to install correctly as required.
I hope I have explained this clearly enough.
1. Is this a bug in the €œMicrosoft SQL Server 2008 CTP, February 2008€? installation?
2. If so is this causing the command line install options to fail?
3. How do I obtain a version of €œMicrosoft SQL Server 2008 Express€? that will work installing from the command line?
Thanks in advance.
View 1 Replies
View Related
Jan 30, 2015
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies
View Related
Aug 31, 2015
I am checking some ratio numbers for our system engineers, those are
Read/write ratio?
Random/sequential ratio?
Read/write block size?
For Read/write ratio, I am using below query,
SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'
[Code] ....
Random/sequential ratio, I googled but cannot find a similar query to get the result?
View 1 Replies
View Related
Oct 20, 2015
I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
View 9 Replies
View Related
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
Jan 28, 2015
I have this table
CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),
[Code] ....
I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.
View 3 Replies
View Related
Feb 1, 2001
we are currently using id's as primary key and replication is not part of our project.
will this be a problem if we decide to do replication? will microsoft generate an identifier then.
what is the advantages of using GUID now or doing it latter?
View 1 Replies
View Related
May 26, 2006
Hello,
I'm working on a smart client app that has an offline sql express store and needs to work with several types of central databases (support for multiple products - ms sql, DB2 etc)
While trying to put together some offline functionality that needs the user to create records on the offline sql express data store, we've run into the need of being able to uniquely identify records so replicating the data in the offline store back into the primary database should not be a problem.
The data created offline spans many tables and involves several tables with relation ships - FK etc...Clearly not a simple case of store and forward.
We dont want to get into the mess of performing key replacement during a synch job with the server. Thats way too much trouble.
GUID seems like a good choice, but as always we have several stake holders having different opinions. And with databases other than MS SQL we will have to store them as strings.
To cut to the chase - can we not hash a GUID to get an integer while retaining atleast the same likelyhood of producing unique ids ? [no drop]
Thanks,
Aviansh
View 13 Replies
View Related
Apr 13, 2007
I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled)
Can anyone tell me whether it is possible and How?
I am currently using the session state to store it in and then using the session=... to get the value into the parameter.
Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource?
Thanks in advance.
View 3 Replies
View Related
Sep 25, 2007
Hi Everyone,
I'm trying to create a SQL Delete statement using a string builder and the WHERE clause uses a Guid. Here is the code:stb.Append("DELETE FROM UserRights WHERE UserIDPtr = ");
stb.Append(TargetUserID);The resulting string is: "DELETE FROM UserRights WHERE UserIDPtr = e01549fb-edf5-4668-de8b-b13dd5661a6e"
When I try to do an ExecuteNonQuery() using the string as the CommandText, I get an error.
Invalid column name 'e01549fb',Invalid column name 'edf5',Invalid column name 'de8b',Invalid column name 'b13dd5661a6e'
It is also strange that '4668' did not show up as an invalid column name, but I don't think that is relavent to this issue.
Can someone show me (or point me to an article) about using Guid's in a text string as a SQL command? Thanks in advance!
View 2 Replies
View Related
May 5, 2008
My database is using the membership store for all the user information. I added a tabel "Skills" with 3 fields "SkillID (GUID)" "SeekerID (GUID, This is the UserId)" and "SkillName ( Varchar(MAX))"
on the page i have a ListView setup to display the SkillName fields based on the SeekerID.
The original code was Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Get a reference to the currently logged on user Dim CurrentUser As MembershipUser = Membership.GetUser
' Determine the currently logged on user's UserId value
Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object)
End Subthat kept returning string to GUID conversion errors, so i had to change Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) to Dim SeekerID As Object = CType(CurrentUser.ProviderUserKey.ToString(), Object) that appears to be working now. On the same page however, I want to insert records into the table, I tried 2 options both of which have a different problem.
Option 1: Use a textbox (ID = NewSkill) and a button with the following code on it:
Protected Sub SetNewSkill_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As MembershipUser = Membership.GetUser
Dim SeekerGUID As Object = CType(CurrentUser.ProviderUserKey, Object)
Dim NewSkill As TextBox = CType(FindControl("NewSkill"), TextBox) Dim connectionString As String = ConfigurationManager.ConnectionStrings("QJSdatabase").ConnectionString()
Dim insertSql As String = "INSERT INTO Skills(SeekerID, SkillName)VALUES(@SeekerGUID, @NewSkill)" Using myConnection As New SqlConnection(connectionString)
myConnection.Open() Dim myCommand As New SqlCommand(insertSql, myConnection)
myCommand.Parameters.AddWithValue("@SeekerGUID", SeekerGUID) myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim())
myCommand.ExecuteNonQuery()
myConnection.Close()
End Using
End Sub
Problem: myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) gets outlined and returns the error: Object reference not set to an instance of an object
Option 2: Use a DetailsView linked to a SQLDataSource on the page
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="210px"
AutoGenerateRows="False" DataSourceID="SqlDataSource2" DefaultMode="Insert"> <Fields>
<asp:BoundField DataField="SkillName" HeaderText="Add Skill:" SortExpression="SkillName" />
<asp:CommandField ShowInsertButton="True" />
<asp:TemplateField InsertVisible="False">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:database %>"
DeleteCommand="DELETE FROM [Skills] WHERE [SkillID] = @SkillID" InsertCommand="INSERT INTO [Skills] ([SeekerID], [SkillName]) VALUES (@SeekerGUID, @SkillName)"
SelectCommand="SELECT SkillName FROM [Skills]"
UpdateCommand="UPDATE [Skills] SET [SkillName] = @SkillName WHERE [SkillID] = @SkillID">
<DeleteParameters>
<asp:Parameter Name="SkillID" Type="Object" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="SeekerGUID" Type="Object" />
<asp:Parameter Name="SkillName" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="SkillName" Type="String" />
<asp:Parameter Name="SkillID" Type="Object" />
</UpdateParameters>
</asp:SqlDataSource>
http://junk.icore-studios.com/junk/Codeissues/postresumeerror.html
The @SeekerGUID is being generated by the same pageload code as the first chunk i gave (I have 2 variables SeekerID is the GUID converted to string to work with the ListView filter and SeekerGUID is the GUID)
Ultimatly getting either option to work would be fine. Though I think the second would be preferable because I think it'd be easier to replicate later on.
Thanks in advance for your time and any help
View 3 Replies
View Related
Apr 17, 2004
Hi,
I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.
CREATE PROCEDURE spScanUpdate
@id varchar (100),
@name varchar (75)
AS
DECLARE @GUID_ID as uniqueidentifier
SELECT@GUID_ID = CAST ( @id as uniqueidentifier )
UPDATE tScan
SET name = @name
WHEREid = @GUID_ID
GO
View 5 Replies
View Related
Apr 17, 2006
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)
View 6 Replies
View Related
