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.
Queries against DMVs like sys.dm_XXX causing blocking in user databases? I have never read that querying DMVs has caused blocking in user databases but that question recently came up in my environment.
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?
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
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().
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?
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.
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
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 am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
I have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.
I am using below insert statment to insert data in RPT_Invoice_Shipped table.
insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped] (Day_No, Date, Div_code, Total_Invoiced, Transaction_Date) select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)), sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate()))) from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced] WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate()) group by div_code
while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).
But I am getting difficulty to arrange this in above query. how to achieve this?
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
We have a web-based third-party application that has both background processes and user activity requests running in the same database (SQL Server 2005 SP2). The problem is that a background process will start a long-running transaction and hold an exclusive lock on a few rows in a given table (a small table, <100 rows). The web clients need to scan this same table, but when their "select *" statements get to those locked row(s), the web client queries stall waiting for that exclusive lock to be released. This effectively brings the entire web front end to a halt because all clients must hit this table for each user action. I realize that this is the classic lock condition that multiversioning databases like Oracle, PostgreSQL, SQL Server Compact Edition, and other databases do not suffer because they don't use shared read locks like SQL Server. But since we're on SQL Server for this app, what is the way to get around this problem? Modifying the clients to use WITH (NOLOCK) is not an option... there will be major consistency issues unless the clients run in Read Committed or higher. Any ideas? We could tweak this app if needed. Does SQL Server 2008 introduce multiversioning or at least some mechanism to get around this problem? I did not see it mentioned on the Microsoft site, but maybe I missed it. Thanks in advance.
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.
Hi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
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...
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
hi I have a field call: password, I want to generates some random chars as default password for users (so user got something to login, and update later).
Current Solution I have: I made myself a trigger and it does generate password on insert, but the problem with that is I have to allow NULL on that field. I don't want NULL to be allowed, and if I don't allow null... sql server won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be more applicaions using the same field from the same database. Just in case if the other developer gets sloopy, I don't want other application(s) allow user put to enter a null or leave it blank on my password field.
My question is: 1) can I put a function or some sort at the "Default" field under "Design Table", if so...how? 2) any suggestions for a better implementation? 3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
I am using Sql Server 2008 R2.I have a existing query that basically says
Select Top 50 Subscriber_ID, Member_Name, Group_ID from my_table order by rand(checksum(newid()))
However the client now wants to have at least two from each group_id. There are 17 different groups. When I run this as is I get about six of the 17 groups in the results. How can I change this to get at least two results from each group_id?
