I am a DBA working for a large business in the North East of England.
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
I have a VB.Net 2005/SQLCE desktop application that imports XML data into an SQLCE database. The needs of the application require that upon import, certain values in the incoming data are used to look up and store additional values in the import datatable. The completed import data is then stored in a 'production' table that constitutes the application's reason for being - the data is processed, assessments are made on it, and reports are generated.
Anyway, this valid value process is performed three times for different types of data. The way I currently have it running is that the three valid value lookup processes are built into loops through the incoming data.
<Pseudocode>
Read incomingData into datatable
For each row in incomingData for each row in refTable1 if incomingData.field1 = reftable1.Field1 then incomingData.Field2 = refTable1.Field2 incomingData.Field3 = refTable1.Field3 else send user to valid value mapping dialog collect dialog mappings add mapped data to incomingData end if next Next For each row in incomingData for each row in refTable2 if incomingData.field4 = reftable2.Field4 then incomingData.Field5 = refTable2.Field5 incomingData.Field6 = refTable2.Field6 incomingData.Field5 = refTable7.Field5 JOIN ON refTable2("ID") = refTable7("ID") incomingData.Field6 = refTable7.Field6 JOIN ON refTable2("ID") = refTable7("ID") else send user to valid value mapping dialog collect dialog mappings add mapped data to incomingData end if end if next Next For each row in incomingData for each row in refTable3 if incomingData.field7 = reftable3.Field7 then incomingData.Field8 = refTable3.Field8 else send user to valid value mapping dialog collect dialog mappings add mapped data to incomingData end if end if next Next
</Pseudocode>
One of these lookup processes, let's say the one involving refTable2, takes a long time; so long that I have it executing on a separate thread. For example. in a 512 kb incoming XML file, there may be around 700 records. Also, refTable2 contains around 800 possible matches. So, we are checking against 800 possible values, 700 times. That's a minimum of 56,000 operations, not counting the additional lookups to refTable7. For a 1.5 MB XML file, it may take ~40 seconds to do the following:
check each incoming record against refTable3 look up any matching values and store in the import datatable create a list of unmapped incoming values create a list of valid values from refTable2 present the user with the valid value mapping dialog
All that being said, a colleague recently suggested that before executing the CPU-intensive refTable2 lookup, that I first get a distinct list of Field7 from incomingData before performing the lookup (see pseudocode above) for that set of values.
QUESTIONS:
1) Would this really help performance? I don't see how, since I must still iterate through incomingData and add looked-up values to each record before storing it in the production datatable.
2) Is there a better way to achieve desired results rather than the brute-force matching routine described above?
This deadline has been ridiculous as usual, so I've done the first thing that came to mind with this import/lookup routine. However, I figured I'd check to see if I was overlooking anything fundamental that would speed things up a bit.
Hi,I am starting a new job next week. Part of what I am required to do isa "system diagnostic" on a SQL Server 2000 box to determine what areascan use improvement - this would include configuration settings,backup/recovery, sql tuning, etc... and anything else I may not havementioned here!What I need is a thorough and systematic approach to doing this. Cananyone please give me advice, or point to a FAQ or other links thatdiscuss this. I am running out of time.THANKS MUCH
Hello all,I've been recruited to assist in diagnosing and fixing a performance problemon an application we have running on SQL Server 7.The application itself is third party software, so we can't get at thesource code. It's a Client Management system, where consultants all overthecountry track their client meetings, results, action plans, etc. , and hasapparently been problematic for a long time now. I came into thisinvestigationin mid-stream, but here's the situation as I understand it:We have users reporting it's slow, with no discernable pattern with respectto what part of the application they're using or now particular time of day.I am told that it doesn't appear to be a bandwith or computer resourceproblem. They apparently added two app servers a year or so ago, whichtemporarilyimproved the performance. We're using a nominal percentage of CPU andmemory.There are three large tables (approx 8 million rows) that are queried often,as users click to see their calendar of appointments or review past meetingswith a client, etc. The activity on these tables is over 90% reads(SELECTS) with about 10% INSERTS/UPDATES. We have attempted to run the IndexAnalyzer Wizard twicebut so far it just seems to hang (it could be that the workload file is toobig?) . So, what we're doing now is isolating the SELECT statements thattake a long time to run and manually comparing them to the indexes thatexist on these large tables. Since we can't alter the SQL source code,we're trying to alter the indexes to improve performance.What I would like to know is, is there a good way to get benchmarkmeasurements so we can explicitly measure any performance changes? Also, doyou thinkwe're going about this the right way, or is there some other avenue we couldbe looking at to improve performance?I recognize that performance questions are tricky to post/answer in anewsgroup, because usually you need more information than is provided. Theproblem isthat this is a high profile investigation (they're hauling us into meetingsevery two days to report our progress) and I need to be able to convincinglystate that we have either improved performance by X% , or that it is theapplication itself that's the problem and we're stuck with it.Any thoughts would be deeply appreciated.Thanks and best regards,Steve
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
Okay, I'm a novice! I installed Back Office with NT opperating system on my home computer. I want to learn SQL 7.0. This is incredible, but I can't launch the program. I've tried everything under Start, Programs, SQL, but nothing listed seems to get me into SQL 7.0. What am I missing? Help is most appreciated!!
public int RunSQLQuery(string sqlStatement) { if (_sqlConnection.State != ConnectionState.Open) _sqlConnection.Open();
_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection); return _sqlCommand.ExecuteNonQuery(); }
Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks
Hi,I have quite a complicated request to do in sql.I've got on table with 3 fields (id, field1, field2) and I have toprocess a request on each of the records, one by one, and thendetermine if the status is OK for each recordsFor example, I would check if the sum of field1 and field2 is greaterthan 10.What is the best way to do this ? It has to be done in a storedprocedure. I can't return the status for each one of the records, soshould I store it in a temporary table and do a select on it from mytier application (vb.net) ?ThxSam
Hi,I'm going to explain as clearly as possible:I have two tables:Relationships(relation_id, table1, table2)Relationfields(relation_id, field1, field2)In Relationships, relation_id is the primary keyIn Relationfields, relation_id is the foreign keyI have a front-end interface that allows the user to add records toRelationships and Relationfields as followed:The user selects a table1 and table2 values from listboxes. These arereal table names from sys.objects, so then the user can select fieldsof these tables on which he wants to create a JOIN.Anyway, I can easily insert the table1 and table2 into Relationships(relation_id is an auto-increment). Then I need to get the relation_idof this new Relationship (easy since I know which values I've insertedand table1-table2 associations are unique.Now the PROBLEM :I need to insert into Relationfields all the fields selectioned by theuser for each of the two tables . But the user might have selectedseveral fields from table1 and table2, so I need to pass A LISTPARAMETER to my Stored Procedure as I don't know how many values offield1 and field2 there is going to be.I hope this is clear enough. Is it possible to achieve what I want ?Should I pass an entire concatenated string with values separated bycomma or whatever and then decrypt it in the stored procedure ?Thx
I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:SELECT ROW_NUMBER() OVER(ORDER by PointID) as 'Num', Latitude, Longitude, Elevation FROM [PointTable] WHERE (RouteID = 5) But I cannot (or do not know how to) add a clause that saysAND (Num = 3) So can someone show me how to request a specific row?
I have two tables:-------- Bids-------PKBidsFKAuctionsFKUsersBidAmount-------------------FollowedLots-------------------PKFollowedLotsFKAuctionsFKUsers Fields beginning with PK are Primary keys and those beginning with FK are Foreign Keys. For each FollowedLot of a specific User, I would like the MAX BidAmount of the FKAuctions and also the MAX BidAmount of the FKAuctions of that specific User. I hope you can understand my question.Thank you!
due to SQL Injection Attack I use parametric sql request like this SqlConnection cnx = new SqlConnection(strCnx)) SqlParameter prm; cnx.Open(); string strQry = "SELECT Count(*) FROM Users WHERE UserName=@username "; int intRecs; SqlCommand cmd = new SqlCommand(strQry, cnx); cmd.CommandType= CommandType.Text; prm = new SqlParameter("@username",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtUser.Text; cmd.Parameters.Add(prm);but how do I retrieve values ndlr, I have several rowsnormally its like SqlDataAdapter ... DataSet... DataTable... foreach (Datarow datarow in DataTable.Rows) { ...... }so how do I retrieve values in parametric request ?????
Hie, I have a big request involving 24 tables working with Oracle. I`m porting my application onto SQL Server 6.5. The problem is the maximum number of tables: 16. I did create a view with 9 tables and recontruct my query with the 15 remaining tables and the view. I tried and I got an error message: "Msg 4408, Level 19, State 1 The query and the views in it exceed the limit of 16 tables."
Here is the query: SELECT ENT.ENT_DOM FROM CTI, ENT, IEX, V_VAL, V_DEV DEV_A, VTM VTM1, VTM VTM2, VTM VTM3, VTM VTM4, VTM VTM5, V_DEV DEV_B, NIE, POS, TIE, TLP, TPL
There is only 16 tables... Can anyone explain me why it does not work. Thanks a lot.
Hi, (sorry for my english...)I have a probelm to build my Pivot table.Indeed, The goal is just to see an example of Table Pivot with our Database...For that, I use this link : http://www.csharphelp.com/archives4/archive623.htmlI try to adapt the code but some problems...I use visual developper, and i have installed OWC11 & .NET framework 2.0.using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Office.Interop.Owc11;public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");PivotTableClass PTClass = new PivotTableClass();PTClass.ConnectionString = ConfigurationManager.ConnectionStrings["TimeTrackingDB"].ToString();PTClass.DataMember = "";PivotView pview = PTClass.ActiveView;Response.Write(pview.FieldSets.Count);}}}When i'm lauching there is an error : HRESULT E_FAIL http://fists148.free.fr/Error.JPGIf i put the last lines in commentary, public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");}}}The component is loading but it seems to be an issue with the connection database...In my web.config, I Have this : <connectionStrings><add name="TimeTrackingDB" connectionString="Data Source=PORT06139DEV_2000;Initial Catalog=TimeTracking;Persist Security Info=True;User ID=user_TTT;Password=user_TTT;pooling=false"/></connectionStrings>Error connection (http://fists148.free.fr/OWCscreen.JPG)If you have any idea, i take it...I'm deseperate :eek: Thanks By advance
Im now working on a assignment where i have hierarchial tree structure arrangement for representing an health care. This is supported by SQL DB 2005.One of the node in the tree is dissolved and all it functionality have to ported to the other tree node which share the similar structure. Problem in porting is complex due to the following things
1. There are capabilities and permission associated with the each node.
2.each node has a unique identifier across the three (note: though this seems to be of no problem it might become issue when search occurs for eg. a->b->c search now becomes a->b->x->n ->?).
3.Already there are messages left in the DB. Please can anyone help me how can i initiate this migration. Im very new to the design and im just started "ABCD" of it.
I am not sure if this belongs under this forum or 'New to SQL' so please feel free to move it if necessary.
Following on from an issue i have had with sysdepends and syscomments tables filling up, I have been trying to research what these tables are and how they get filled up (i.e. what is the trigger to insert a row into these tables).
Unfortunately on all my web searches and book searches i have not found anything much more than 'They are system tables'. From what i have gleaned, it looks like every time a stored procedure or database object is changed, then this will create a new entry into the sysdepends table which highlights what the new object is linked to.
Is this understanding correct? And does anyone have any other sources of information which might give me a better idea of what and how these tables are about.
Hi,I've got two primary keys in a table:Constraint(QueryId, ConstraintName)In a stored procedure I select {QueryId, ConstraintName} couples thatmatch some criteria, and what I want to do is specifying in my a SELECTstatement that I want all of the {QueryId, ConstraintName} that are notin my stored procedure result. With only one field, it would be easy :Select * from Constraint where QueryId not in (Select QueryId fromOtherTable)My explanations are not great but I think it's enough to understandwhat I want.Select * from Constraint where QueryId and ConstraintName not in(select QueryId ,ConstraintName from OtherTable)--> of course not correct, but then how can I do that ?Thx
Hi,I have a DB where I would like to maintain a fixed size and control itby myslef.I do not have the options: "Auto-Grow" and "Auto-Shrink" enabled.[color=blue]>From time to time, without a notice, or any logging, the database files[/color]gets shrinker and this causes a database full error.As I wrote, I would like to maintain the size of the database by myselfand not automatioc by the DB server.Please help me find out what can cause this problem and how to solvethis issue.Thnaks,- Ze'ev
I Stipulate that I am the most ignorant creature in the universe, incapable of following exactly written directions available all over the internet to install Microsoft Visual Web Developer 2008 Express Edition and SQL Server Express, in a way that will enable me to make a connection to SQL Server Express.
Having Stipulated the above, is there someone out there that can explain to me, in a dumb-proof way, how to do it in a way that is understandable to the most ignorant creature in the universe?
I am running Windows XP SP2 with all the lattest updates, frameworks, etc., and using IIS.
The dynamic SQL at the buttom causes a error: Msg 402, Level 16, State 1, Line 9 The data types ntext and nvarchar are incompatible in the equal to operator.
I think it is due to the one of the where conditions [URL] = @old_URL ,both are set to NULL(@URL=NULL, @old_URL=NULL). How do I make changes to the SQL statement to compare the conditions where NULL comparsion is inevitable. exec sp_executesql N'UPDATE [Announcement] SET [Atype] = @Atype, [Title] = @Title, [Content] = @Content, [URL] = @URL WHERE [AnnouncementID] = @old_AnnouncementID AND [Atype] = @old_Atype AND [Title] = @old_Title AND [Content] = @old_Content AND [URL] = @old_URL',N'@Atype nvarchar(3),@Title nvarchar(3),@Content nvarchar(41),@URL nvarchar(4000),@old_AnnouncementID decimal(1,0),@old_Atype nvarchar(3),@old_Title nvarchar(3),@old_Content nvarchar(41),@old_URL nvarchar(4000)',@Atype=N'CMC',@Title=N'aaa',@Content=N'asdfg',@URL=NULL,@old_AnnouncementID=6,@old_Atype=N'CMC',@old_Title=N'aaa',@old_Content=N'asdfg',@old_URL=NULL Any help would be apprecaied, Ricky.
Hi everybody. Got a nice little problem here. I have a accessdatabase containing 100 000 rows. Im fetching these rows to a dataset and then inserting them, row by row, to a MSSQL dB. The dB and IIS is running on the same server. I also have full control over this webserver, so I pushed the Server.ScriptTimeout value up to 3600 sec (both in IIS and in the c# code) but when executing this query (witch aprox. take me 8 minutes) I recieve a Error Code: 408. The operation timed out. The remote server did not respond within the set time allowed error. Someone got a clue for me? :) -Thomas
Is it possible for SQLServer to make a http web request in a stored procedure? I know that you can configure it to send email, but I'd rather do that on the webserver side, and that needs to be triggered by a request of some sort. I have a sp that runs nightly, and I'd like it to send out a http request when its done so that I can do further processing/send email/what have you. I haven't been able to find anything on this subject in any of the books I have, and my google-fu isn't strong enough. Thanks for any hints.
Hello! I am learning the RS from SQL 2000 and am having an issue. Does anyone have a "Working" example of how to render a report and render images embedded/external/database to the web? I have the report generated, but the pictures just don't want to render (through the web). I have contacted my hosting service (who are all supposedly Microsoft Certified Technicians) and they have no clue why it won't work. I have gone through every online resource that I have been able to find (including the Microsoft thing about the RenderStream) and it still doesn't work. If any of you have a working example of this, it would be very helpful!! As of right now, I am using a database image, and this is the ASP.Net code that I have been using:
Dim rs As New newafp.RSService.ReportingService rs.Credentials = New System.Net.NetworkCredential("domainusername", "password", "") Dim items As newafp.RSService.CatalogItem() = rs.ListChildren("/", True) Dim results As [Byte](), image As Byte() Dim streamids As String(), streamid As String results = rs.Render("/NewUserLetter" & Location, "HTML4.0", Nothing, "<DeviceInfo><HTMLFragment>True</HTMLFragment><StreamRoot>/newafp/</StreamRoot></DeviceInfo>", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids) For Each streamid In streamids image = rs.RenderStream("folder/NewUserLetter", "HTML4.0", streamid, _ Nothing, Nothing, Nothing, Nothing, Nothing)
Dim stream As System.IO.FileStream = _ System.IO.File.OpenWrite(Server.MapPath("") & "" & streamid & ".jpg")
stream.Write(image, 0, CInt(image.Length)) stream.Close() Next
I'm not really sure how to explain this, so please bear with me. I have a SQL statement, such as: SELECT TOP (10) FROM chartTracks This works with SQL Server Express 2005, but when I moved my site over to work with a MSSQL Server 2000, the statement had to be changed in order for it to work: SELECT TOP 10 FROM chartTracks I was just wondering if there was a technical term for this, and if possible, the locations of anymore sourecs of information regarding the above? I'm just writing a report and would like to include this, if possible. Thanks in advance!
What I want is to update the field 'SaleMonthName' based on the 'SaleMonthNum' field using the CASE expression. I want something like this (pseudo-code):
update Mytable set SaleMonthName Case: IF SaleMonthNum =1, then := 'January' IF SaleMonthNum =2, then := 'February' ... IF SaleMonthNum =12, then := 'December'
Can you tell me why the following queries would give me slightly different results?
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) UNION SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7))
SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) GROUP BY dateadd(day, datediff(day, 0, CallDate), 0) UNION SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) GROUP BY dateadd(day, datediff(day, 0, CallDate), 0)
I'm running a (phpBB) forum which uses a MYSQL data base - I can access the database via a webpage through my ISP and it lets me run short scripts (through a web interface). I need to do the following (shown in psuedo code below). I would be eternally grateful if someone could translate it into pure SQL commands for me to paste into my SQL quiery input field on the web page I'm using to access my database. (I'm a complete SQL novice)
===================== /* I need to be sure these commands will do nothing if 'jeff' does not exist in the user table */
/* extract the id value for the user called 'jeff' from the users table */ user = GET id FROM users WHERE username = "jeff"
/* remove all entries from the forums_watch and topics_watch tables that contain Jeff's userID */ REMOVE ALL ENTRIES WHERE userID = user
notify = 0;
/* insert many entries into the topics_watch table, one for each topic that exists in the topic table */ arrayOfTopicIDs = GET ALL id FROM topics FOR EACH current_Topic IN arrayOfTopicIDs { INSERT INTO topics_watch (topic_id, user_id, notify_status) values (current_Topic, user, notify) }
/* insert many entries for jeff into the forums_watch table, one entry for each forum that exists in the forum table */ arrayOfForumIDs = GET ALL id FROM forums FOR each current_Forum in arrayOfTopicIDs { INSERT INTO forums_watch (topic_id, user_id, notify_status) values (current_Forum, user, notify) }
=====================
I'm going to implement this with php and MYSQL as a mod for my forum application (phpBB) - but I could really do with a way of doing this immediately with pure SQL commands as a quick and dirty one shot paste into the input field of the MYSQL database web interface until I get the php mod going.
Can this be done using only SQL commands without tedious repetition?
