How can I tell how often a checkpoint is being executed?
I have a lot of data to insert into a table (via a SQL insert from another database on the same server), and I do not want to fill the log ... so I will do a SET ROWCOUNT 100000, insert records, wait for the checkpoint to run which will clear the log, ... and repeat this process untill all the records are inserted.
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year 25,000 July 2014 25,000 August 2014 25,000 September 2014 25,000 October 2014 25,000 November 2014 25,000 December 2014 25,000 January 2015 25,000 February 2015 . . . .
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters. I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT t.gl7accountsid, a.accountnumber, a.description, a.category, t.POSTDATE, t.poststatus, t.TRANSACTIONTYPE, t.AMOUNT, case when t.transactiontype=2 then amount * (-1) else amount end as transamount, t.ENCUMBRANCESTATUS, t.gl7fiscalperiodsid
FROM UrsinusCollege.dbo.gl7accounts a
join ursinuscollege.dbo.gl7transactions t on a.gl7accountsid=t.gl7accountsid
where (t.gl7fiscalperiodsid >= 97 And t.gl7fiscalperiodsid<=@FiscalPeriod_identifier) And poststatus in (2,3) and left(a.accountnumber,5) between '2-110' and '2-999' And right(a.accountnumber,4) > 7149 And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
I have a report which lists sales figures by salesperson, and I need the report to highlight the maximum amount (ie. the person who got the highest sales figures). How can I do this?
My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.
- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.
- the largest data type is nvarchar 125.
- number of rows in the largest table will eventually grow to hundreds of thousands.
- users access the database online
This is large to me but I expect that to some of you this not.
My application would be easier to setup if the contacts table were to include address info.
So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.
Any help you could provide with this scetchy info would be greatly appreciated.
We have created a databse in SQL Server 7 for support issues. We are having trouble with only one aspect of the databse and that is the body of the supported problem.
The databse is basically a Question/Answer Support database whch house Frequently Asked Questions. When the user does a search on a specific problem they have a list of matching questions shows on the screen (links). When the question is clicked on it shows the Questions with the Answer of possible fixes.
Our problem lies in the fact that SQL server is truncating the Answer portion. I have tried different Data Types with maximum lengths with no success it keeps truncating it. Right now I am on Data Type nvarchar with a length of 4000.
If anyone has any pointers on how to solve this problem all input is appreciated. You can post here to the forum or e-mail me directly at jason@flnet.com
I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.
The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL DROP TABLE #tmpCustomerAmount CREATE TABLE #tmpCustomerAmount ( [id] [int] IDENTITY(1,1) NOT NULL,
I have a table with product_name and introduction_date(when the product was first introduced)as columns. now i wana calculate average as below
if item is sold in previous business year(suppose 2011-12) then avg should be avg price in businessyear(2010-11), if it is newly introduced(suppose 2013-14)then avg should be of current year(2013-14).
If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:
Declare @TotalAmount money @TotalAmount = 673000 Col1 Col2 Col3 Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45 Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3) Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)
Hy im PCV I want to know how to calculate the amount of data(in MB) that is transfered from 1 server trought another Puplisher--->Subscriber, using a merge replication. I know that the amount of data depends on the number of the rows and the scale of the colums. I only want to know how to calculate that amount of data. I am using Sql server 2000, and a OS windows XP profesional, thank you
I am fairly new to sql. Reading a table I need to show amount for each customer. Also I need to add amount for each customer that has more than one entry. What would the sql statement look like? Thank you.
Records in file: Company Amount Customer1 24.000 Customer2 36.000 Customer3 72.000 Customer1 20.000 Customer3 15.000
Desired results: Company Amount Customer1 44.000 Customer2 36.000 Customer3 87.000
please help to select these rows from these tables my tables aretable1table1Id groupId table2id price1 1 1 102 1 3 10003 1 4 5004 2 1 55 2 3 10006 2 2 2000table2table2id name1 hello2 test3 test14 test2ok i want to select maximum priced row from table1 grouped by groupidwith table2id and table2.namemy out put isgroupid price table2id table2.name1 1000 3 test12 2000 2 testif i do :select groupid,max(table1.price) as price from table1 group by pricethis will give me the max priced row from table1but when i join them with the table2 it gives me all rowslikeSelect groupid,max(price) as price,table2id,table2.name from table1inner join on table2group by groupid,table2id,table2.nameit gives me all rows cause i had to group by table2.id and table2.namebut i can't take it out cause it give me no aggrigated value errori can't figure out any other way, please helpSQL Server 2000thankseric
When you are pulling the data from PPV SELECT [MATERIAL] ,[IR_AMOUNT] FROM [LATCUBDAT].[dbo].[ppv] where [MATERIAL] = 'MR004' AND [IR_AMOUNT] = 53728.85 Result
material
IR_Amount
MR004
53728.85
When you want to join ppv to rmu and then pull the data then select R.[PRODL] ,P.[MATERIAL] ,P.[IR_AMOUNT] AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL] where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
When I use avg in the query I get this report select R.[PRODL] ,P.[MATERIAL] ,avg (P.[IR_AMOUNT]) AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL] where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85 GROUP BY R.[PRODL],P.[MATERIAL] ORDER BY R.[PRODL],P.[MATERIAL]
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
I will like to see
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
8954.808
32000
MR004
8954.808
36000
MR004
8954.808
50000
MR004
8954.808
52000
MR004
8954.808
54000
MR004
8954.808
If there where 4 product line then avg distributed between 4 and like wise
Hi,I've an application, lets call it simply "A", which creates in a Microsoft Sql Database two huge tables.Lets call them "table1" and "table2"It safes really much data into this tables.After application "A" has finished another application is executed which deletes this two tables.Then application "A" is started again and it will create this two tables again, but the amount of data becomes bigger.It can only proceed if the tables were deleted completely before and the database is empty. This is the procedure which I repeat very often, but everytime the amount of data becomes bigger (table1 and table2 becomes bigger).A couple if times it works fine, but once it seems data becomes too big and application "A" fails. Mostlikely because the data wasnt removed correctly / completely. This is my code of deleting the two tables, maybee there is something I have to change:</p><p> try { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Server=mycomputerdbname;Integrated Security=SSPI;" + "Initial Catalog=testing"); builder["Server"] = "(local)dbname"; builder["Connect Timeout"] = 10; builder["Trusted_Connection"] = true; builder["Initial Catalog"] = ((ComponentConfiguration)this.componentConfig).Persistency.DatabaseName; SqlConnection sqlconnection = new SqlConnection(); sqlconnection.ConnectionString = builder.ConnectionString; sqlconnection.Open(); SqlCommand cmd1 = new SqlCommand("DROP TABLE table1"); // TO Do delete all tables SqlCommand cmd2 = new SqlCommand("DROP TABLE table2"); // TO Do delete all tables cmd1.Connection = sqlconnection; cmd2.Connection = sqlconnection; cmd1.ExecuteNonQuery(); Thread.Sleep(7000); cmd2.ExecuteNonQuery(); Thread.Sleep(7000); sqlconnection.Close(); Thread.Sleep(3000); } catch { }</p><p> </p><p> Thanks for help! mulata
Generally, on any screen, we design filter screen by allowing user to identify range or one value to search. But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
For example On screen which have information of people in any province. So user would like to search it by identify no matter how value to search. There are check box of any province on filter part which enable users choose it.
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province. and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas? Thanks Andy,
I have an application that allows users to "reserve" a certain time and location for practice sessions. I want them to look at a web form that shows which times are available and then allows them to select an open slot and then submit this form with some personal information. My problem is that as soon as they select an open slot, other users should see that this slot is no longer available. On the other hand, if someone selects an open slot and then takes too long to type in their personal information, I want it somehow to kick them out and re-open this slot back up.
I know that I can create a field called 'status' in my database that gets turn to something like 'pending' as soon as the first user clicks on an open slot, but how would I turn this field back to the 'open' state if the user took too long to enter their data?
I'm busy writing a local site search engine that searches through a sql server database and I want to know how or what is the correct sql syntax to use in order to limit the amount of results a page loads at a time? The idea is obviously similar to something like google where you only see a certain amount of results first and then click at the botom for the next eg. 10 results.
The second question is how do I, after the first page with the first set of results that were shown, "clear" the second page of the previous html in order to show the next set of results? To give you an idea what my code looks like at the moment. Please don't kill me if the code is done a bit a lame, because I'm still learning.
resTextDescription2 = "myFile.txt does not exist!"; }
//determine whether male or female in order to display correct sign if ( resGender == "Male") resGender = "Male_sign_1.jpg"; else resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correct words for display if ( resHaveChildren == "have kids" ) resHaveChildren = "Yes"; else resHaveChildren = "No";
//create a connection conClient = new SqlConnection( @"Server=THALIONTHALION;Integrated Security=SSPI;database=DateGame" );
//select statement strSelect = "Select userName From [client] Where userName=@username and userPassword=@userpassword"; cmdSelect = new SqlCommand( strSelect, conClient );
Please help!!! I'm building a database that I have a form. This form allows user input the invoice amount field which is the currency field. My ultimate goal is allow user put in the postive numbers and it will store as negative amount. Please let me know if there is the way to do that. Thanks in advance. Linh
i have just created a test database and now need to insert a large number of records into one of the tables, we were thinking of about 1 million records, has anyone got an sql script that i could use to create these records
I need to make a job that will update up to 8000 rows with the list description of 'berkhold' to 'berknew' in SQL 2000. This is something that I have to do with several projects manually every day by doing the following 2 steps.
SELECT ListDescription, CRRecordID FROM dbo_BerkleyGroupInventory WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC' ORDER BY CRRecordID
I then scroll to the 8000th row and copy the CrrecordID and run the following query
UPDATE dbo.berkleygroupinventory SET listdescription ='berknew' WHERE ListDescription ='BerkHold' AND CRRecordID <=5968432 AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'
I'm sure there's an easier way to do this, but I'm very new to SQL and haven't figured it out yet
Hello,I have experienced that some of my tables occupies an extremely large amountof pages but with few rows. An example is a table with 37 rows over 22000pages !. The columns are simple integer and char. I fixed the problem byintroducing a clustered index. Now it only uses 1 page. But can anyoneexplain this behaviour in SQLServer 2000 ?regards Jakob Mathiasen
I want to write a query that will give me a dynamic amount of columns back.What I want to do, I want to create a calendar application, in which foreach employee, I want to show if he is in the office or not.this should look like:ID, Name, 1,2,3,4,5,6,7,888,Leo,0,0,1,1,1,0,0,1The amount of columns is dynamic, and is a period of time, with a column foreach day.Any suggestions what the best approach to this could be?ThanksLeo
