I am using this select statement to radomly display a record
SelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()
I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.
I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.
I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.
These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.
It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.
What are the options available for me to be able to query and differentiate the 'deleted' records ?
Hi,I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy. I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages. Thank you.
hello members i want to no that weathere there is any procedure to select come random records from the database for eg i want to select 10 students out of 100 randomly is there any query for this in SQL server 200
I'm familiar with querying for a random record, however I can't seem to do it using a parameter driven TOP value.
Here's what I've tried in a stored procedure: ( @Count int = 1 ) declare @sql varchar(1000) set @sql = 'SELECT DISTINCT TOP ' + convert(varchar, @Count) + ' * FROM RandomisedView'
The view I am using looks like:
SELECT TOP 100 PERCENT dbo.TableName.* ORDER BY NEWID()
The stored procedure does not seem to cause the View to reorder the records.
I need to be able to select X random records. Any help would be appriciated.
I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.
(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)
The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.
Hello. I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number. So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc... If any one have any ideas please respond. Thanks in advance.
I need to write a select statement where I need to select the record randomely. Is there any function to do that? What's the best way to do that? Please help...
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
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.
Hi, I'm trying to use trace flag 1204 to get some detailed deadlock information In EM, I add startup parameter -T1204, and then I stop and start the server. I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.
I have a perl script (uses sql query) that will run if there is anyupdate in the row. Currently, I am a trigger that sets the DateModifiedfield with getdate() Then my perl script looks for certain range ofmodified dates and runs the necessary query.I am trying to come up with a different mechanism where I don't have touse the date field. I am looking into flag bits. But not sure howto use it.Should I create a trigger that will set the flag bit after any update?This way the script will just look for the updated records, regardlessof what time it was updated. Maybe I am misunderstanding flag bits, thenafter another subsequent update how would the perl/sql script know whichrecords were updated? I hope this make sense.thanks.--s
I'm working on a program and I want it to check to see if the database it is pointed to has had a backup done against the database in the last 10 days. I will then throw a message box indicating that the database has not been backed up and that they should consider doing so.
Is there any attibute that I can check to see when the last time the database was backed up?
I am trying to build a decision tree algorithm with multiple regressors. I am having trouble changing the properties for the input variables to regressors (the only multiple flag choice is "not null"). I created a mining model using the DMX query thinking I could change the code to include multiple regressors, however I get a syntax error when trying to execute the model. Also, I am wondering if you can view the dmx code for a model built using the mining wizard.
If you have a table with columns like Category int, Cost money, EffectiveDate DateTime, SpecialFlag (char Y or N) how would you write a query to get for each category, get the most recent, with a specialFlag of Y if a Y record exists and if not, grab the N record?
I have tried using the exists clause for the flag, but as soon as I mix in the group by to get the latest effectivedate, the max is evaluated first and I am not guaranteed to use the Y records if they exist.
To illustrate: A 1, $10, 1/1/2007 Y B 1, $12, 1/1/2007 N C 2, $5, 6/7/2007 N D 2, $4, 1/1/2007 N E 3, $1, 1/1/2007 Y F 3, $2, 6/7/2007 Y G 3, $3, 2/1/2008 N
I want records A because it is flagged Y, C because it is the most recent and there is no Y record and F because it is flagged and most recent.
I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.
Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2' )
i want to make a stored procedure that will update the notification flag in the table.
If the email was succesfully sent the flag will be updated as SENT. And if it is un successful the flag will be NOT SENT in column of the table in SQL. The email will be send using .NET codes and updates of notification flag will use SQL stored procedure. Please help
I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate
My source date
ID  WorkId  HireDate   TerminateDate 1    123   2006-01-05  2013-01-13 1    456   2006-02-23  2013-04-13 1    789   2005-12-12  2013-06-13 2    913   2004-12-15  2012-01-13 2    406   2006-04-13  2014-04-13 3    113   2009-11-15  2011-01-13 3    106   2010-04-13  4    513   2002-11-15  4    168   2011-10-23  5    342   2007-01-05  2009-01-13      5    416   2008-02-23                   5    159   2008-12-12  2010-06-13   6    542   2005-01-03  2007-01-13     Â
Rule -1:Â set flag = yes
-Whenever terminatedate is not null -Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No - with in the ID group Whenever terminatedate is null
My output should look like this..
ID  WorkId  HireDate   TerminateDate    Flag 1    123   2006-01-05  2013-01-13       yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null) 1    456   2006-02-23  2013-04-13       yes 1    789   2005-12-12  2013-06-13       yes  2    913   2004-12-15  2012-01-13       yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
In order to use Microsoft Dynamics NAV with the SQL Server 2005, I need to set Trace Flag 4616, and I know it's a setting somwhere, where I can add this flag no. -but I can't remember where? Please help?
I created a package wide OnError event handler with the following script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
Dts.Variables("FailureFlag").Value = True
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I ran the package with an error and set a break on error condition. When I looked at the value of "FailureFlag" it was False. Any ideas why it is not being set?
I have a post execute event that is supposed to rollback from a snapshot on failure and it is not doing so on failure so I'm trying to figure out what's wrong.
Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalischgelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung drin.Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrereDatensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefährlich (daFließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP
I recieved an alert that says 'The Auto Shrink flag for database is not set according to best practice.' Can anyone tell me where I can find this flag and what it shoul dbe set to?
i am using Sql Server 2005 SP2, I am trying to configure Mirroring. The issue is ,After configuring mirroring ,Whem i am starting START MIRRORING button .I am getting the error
Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)
I used BDCC TRACEON (1400) and I altered the Startup parameter in configuration manager of SQL SERVER ;-T1400
But still I am getting the above error which is indicated in red
I know that several hotfixes need you to turn on trace flag 4199 to activate them. But after reading an article on that trace flag, and the way it was worded, I couldn't tell if you still needed to use the trace flag, once that hotfix was incorporated into a service pack.
