A few years ago I had a problem(think it was 6.5 might have been 7) that was causing weird errors. We found out that the write-ahead cache on our RAID controller was causing problems the way sql server utilizes it's write process with the transaction logs. Now have another problem that is similar on sql 2000 (we have the db server attached to a dell SAN). Has anyone encountered this type of problem with sql 2000 and a RAID or SAN cache?
View 1 RepliesWith the current web app that I am writing, I am starting to plan ahead to the scalability problems that I am planning on encountering with the number of users that I may have...
My hosting plan now allows for a 50MB SQL Server database, but, I know that will not last long, each user will be using 3-5MB each of the database, so I am going to outgrow my space fast.
Would looking into (until i have enough subscribers to get a dedicated host), SQL Hosting be a good idea? Atleast to start off with something like http://www.alentus.com/hosting/sqlserver.asp ?
But then again, would a SQL Database growing to large get bad? Within a few years, i expect to have at max 5,000 users, so that could grow to a 25GB database... with millions of rows.
Would breaking it up into smaller databases for each N amount of users be a wise idea? Or would it not really matter?
Any help is much appreciated
Happy New Year
Harold.
While a stored procedure was running DBCCs against a specific database, the following error was encountered. 'WARNING: No read-ahead slots available...' The SQL Server service hung upon encountering this error and would not respond and the box had to be rebooted. I re-configured the 'RA worker thread' setting from 3 to 8 as a preliminary action. I need you to find out if this setting is 'auto-managed' within SQL Server 7.0, or whether this is something we will need to watch continuously (we had another instance of this failure about 1.5 months ago). Also what else can be done to avoid this problem in the future.
View 2 Replies View RelatedI would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
How can I do this?
I have table T1 with fields T1.ID, T1.CheckBoxCol
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
for each T1.ID
How can I do this?
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection
Combine these two and return the result
I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE?
Thanks
Hi,I
have a hotel reservation system.I need to implement Check availability
(Room checking) function for the project.But I dont understand how do I
start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me
How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid
View 2 Replies View RelatedI need to compare two column in two different tables but I do not know how to write this query.
Example:
Table1
-------------
EmployeeSSN
EmployeePayRate
Table2
-------------
EmployeeSSN
EmployeePayRate
I need the result set to look like this
Result Set
-----------------------------------------------------
EmployeeSSN T1Rate T2Rate Same?
-----------------------------------------------------
111-11-1111 8.95 8.95 True
222-22-2222 9.95 8.95 False
333-33-3333 8.95 NoMatch False
444-44-4444 NoMatch 8.95 False
and so on....
How do I write this in Transact-SQL also using a function to return the "NoMatch" and True or False statements?
Thanks,
Roger
Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.
Here is my table field setup with sample values all fields are numeric
except date
weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142
A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.
What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.
another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..
Someone help me with some code
i thank you kindly in advance
p.s. And good books dedicated to stored procedures??
Many thanks in advance for anyone that can help me write this qry:
To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:
dbo.components
compID compName compType
1 srv1 1
2 srv2 1
3 srv3 1
We also have a services table which hold all our various services we own:
dbo.services
svcID svcName
1052 svc1
1053 svc2
1054 svc3
We then have a tbl that shows the links between components and services
dbo.compUses
svcID compID
1052 310
1052 400
1053 122
1256 134
I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.
This is in SQL2005.
Have I explained this well enough? Any help would be much appreciated!
Hey peeps, fishkake's back, and he's more clueless than ever!
OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.
How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...
This site is just making me lazy now...
Good Afternoon all,
I was wondering how to write the TOP 5 records using Relational Algebra.
Dallr
Hi,
I have to implement a search functionality. In the various filters for the search, Store Number is one such filter.
The user should be able to enter range values for store numbers.
Like 1500-1600. So this should filter for all the stores between 1500 and 1600.
Similarly, all these also should be valid.
1550,1600
1550
1550 - 1580,6000,8000
etc.
I have function which identifies the commas or dashes and seperates out the store number and returns a string like
Stores.Storenumber in(1555,1600)
Store.StoreNumber between 1555 and 1600 etc...
i generate a sql at run time and append this piece and then execute the sql.
I have one of the query below.
declare @strQuery varchar(max)
declare @strConcat varchar(10)
declare @strAppend varchar(max)
set @strAppend=''
set @StrConcat ='And '
if @IsAdmin is null-- Not a Admin
set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)
set @strQuery='
Select
(Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber,
CartonNumber,
ActualReceiptDate as [Scan Date],
isnull(Sum(QtyShipped),0) as [Total Units],
b.BatchNumber
from
Carton c
left outer join
CartonDetail Cd on Cd.Carton_Id = c.Carton_ID
inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton''
inner join Document d on d.Document_ID = c.document_Id
inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5
inner join Stores on Stores.Store_ID = d.DestinationID
inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID
inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code''
where not c.cartonNumber is null '
if not (@StoreId) is null
begin
set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')'
End
if not (@DateFrom) is null and not (@DateTo) is null
begin
set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')'
End
if not (@CartonNumber) is null
Begin
set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')'
End
if not (@Status) is null
Begin
set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')'
End
set @strAppend = @strAppend + ' group by
d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber
order by ActualReceiptDate'
set @strQuery = @strQuery +@strAppend
execute(@strQuery)
This query takes time, if there a little over 1000 records.
I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.
I hope i was able to explain my query fairly. Please let me know otherwise.
Thanks
Renu.
how to write an UDF and run it
View 2 Replies View RelatedHi There,
I have been working in ssis and i have a task to write a log of the all events in daily basis.
I mean writing a log for the entire package for each tasks.
Can some one help me how to do it?
Thanks
Hello all!
I'm triyng to update a NULL value to another value. I know I can do this, just can't seem to write it out right.
Something like
UPDATE Part_Order
SET Ser = 'NA'
WHERE Ser isnull
I know thats not right, but I know I'm close.
Any hints?
TIA!
Rudy
I have table T1 with fields T1.ID, T1.CheckBoxCol
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
for each T1.ID
How can I do this?
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
How can I do this?
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
View 1 Replies View RelatedI have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
View 4 Replies View RelatedI have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated.
View 9 Replies View Relatedhi,i am working on asp.net development server. i have created a web page on which i have put some option to enter the any user id. as soon as user id is entered on button click i want to search the first name, last name, age in my database where i have inserted the table with entries. then i wan to display it on the page.Please tell me how can i write the C# code on the clicking event so that i get results. i have connected the database to my virtual server.
View 1 Replies View RelatedHow can I write a function that accepts a data as a parameter and if the date is
Last Monday in May or
First Monday in September or
4th Tuesday in November
returns true otherwise returns false.
Hi I need to create query which would calculate weekly change of some values. There is table with values for every day. At the end of week I need to calculate % of change. It is something like this:SELECT ((LastFridayValue - PreviousFridayValue) / PreviousFridayValue) * 100 from myTable.or it could be something like this:(LastValue - FirstValue) / FirstValue * 100 from top 5 values from my table order by ID DESC. Please help me translate this into real sql query :)
View 3 Replies View RelatedI have a table named "products", it has a column called "category", I have data such as "CategoryA", "CategoryB" and "CategoryC" etc.... now I want to do a select statement and choose "CategoryA" and "CategoryB" but I want to rename them as "A" and "B". how do I write such a statement... thanks!
Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.
@UserName nvarchar(128), @Email nvarchar(50), @FirstName nvarchar(25), @LastName nvarchar(50), @Teacher nvarchar(25), @GradYr int DECLARE @UserID uniqueidentifier SELECT @UserID = NULL SELECT @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserNameINSERT INTO [table name](UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr)
I wanted to select few columns from the result of calling this store procedure and sort by a column.
{CALL FIQR.Portfolio.Loans_In_Portfolio('2007-09-07', 'FISYND', '', '%')}
How should I write it?(without changing the store procedure)
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
View 8 Replies View Relatedhello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
View 1 Replies View Related