SQL Server 2000 - Preventing Multiple Update
Jul 20, 2005
Hi all
We had a small problem when an ASP web page had a missing 'where' statement
and updated all the records in the table. Luckily we could retrieve all the
data from the backups.
How do you guys prevent this from happening in your large systems. Is there
some teqnique for controlling this, I would imagine if you had thousands of
records in a table and some one made a programming error, then the
consequences would be disastrous.
is there a setting within SQL Server that could force SQL update commands to
be limited to a criteria and if no criteria is supplied then reject the
command
thanks in advance
Andy
View 7 Replies
ADVERTISEMENT
Sep 9, 2013
I have a like clause like this:
WHERE COLUMN LIKE CAT1%
or
COLUMN LIKE CAT2%
or
COLUMN LIKE CAT3%
ETC..
I want to know if it is possible just have one like clause from 1-9:
CAT1, CAT3, ...., CAT9
View 3 Replies
View Related
Feb 25, 2015
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
View 7 Replies
View Related
Nov 11, 2007
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
View 2 Replies
View Related
Oct 9, 2007
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Any ideas?
View 1 Replies
View Related
Oct 10, 2007
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Any ideas?
View 5 Replies
View Related
May 11, 2007
I am implementing the security and add different windows domain group and also assign then appropriate rights and permission.
No i want that if some knows the "sa" password and want to connect to sql server 2005 via query analyzer then message should display to user that u can not login with sa login while using SQL Server Query analyzer.
How i can identify and can display the message?
Thanks a lot in advance.
View 1 Replies
View Related
Mar 25, 2015
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NULL,
[Qty] [int] NULL)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
[code]....
View 5 Replies
View Related
May 14, 2007
Hi!
I am trying to update my table Members in my SQL server database "Portfolio".
When I click the button, It throws an exception with the following message: "String or binary data would be truncated.
The statement has been terminated."
The code I wrote is as follows:
Private Sub UpdateUser()
If Page.IsValid Then
Dim cmd As New SqlCommand
Dim sqlQuery As String
Dim sb As New StringBuilder
Dim values As New ArrayList
sb.Append("UPDATE Members SET ")
sb.Append("Email='{0}', UserName='{1}', Pass_word='{2}'")
sb.Append("WHERE MemberID='{3}'")
values.Add(txtEmail.Text)
values.Add(txtUserName.Text)
values.Add(txtPassword.Text)
values.Add(Context.User.Identity.Name)
sqlQuery = String.Format(sb.ToString(), values.ToArray())
cmd = New SqlCommand(sqlQuery, SqlConnection1)
SqlConnection1.Open()
Try
cmd.ExecuteNonQuery()
lblMessage.Visible = True
lblMessage.Text = "You have updated your details successfully!"
Catch ex As Exception
lblMessage.Visible = True
lblMessage.ForeColor = Color.Red
lblMessage.Text = "Couldn't update your details!"
Trace.Warn(ex.Message)
Finally
SqlConnection1.Close()
End Try
End If
End Sub
I have already retrieved the data with a select statement in the Load event of the page.
I have tried to debug it but all the data seems to be loading in the right fields and the connection also opens normally.
If someone can explain to me I would be very grateful.
View 1 Replies
View Related
Apr 22, 2015
I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.
I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.
I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.
View 4 Replies
View Related
Jun 15, 2007
Hi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
View 17 Replies
View Related
Sep 18, 2001
Hi Iam trying to do a trigger that everytime I Update a record de date get update too I finally find a trigger close to that, but this trigger update all the dates from all the record of the same table I wonder is there are a way that I can do it by the date of the record, if somebody could help I will really appreciate.
Thi is the trigger that I have so far
Create Trigger Update_Date
on DBO.Company After Update as
Update dbo.Company
Set ActualiizationDate=Getdate()
go
View 1 Replies
View Related
Sep 15, 2006
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes
--TapeNo
--Status (Spare / Assigned)
Jobs
--JobNo
--Name
--Description
--TapeNo
--AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
--
Paul Anderson
View 3 Replies
View Related
Jul 20, 2005
I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!
View 3 Replies
View Related
Nov 28, 2007
Hi all,
I have a trigger for column eISBNEnteredDate on update or insert changes of eISBN of the table Products2 ( both belong to the same table). The column eISBNEnteredDate can either be added manually along with eISBN value or when only eISBN value is entered it is updated with present date.
The problem I am facing is when I send eISBN along with eISBNEnteredDate the present date is what is getting saved. Upon the same record when a new date is updated the new date is getting saved. Can someone tell me where I am going wrong?
Here is my trigger:
Code Block
ALTER TRIGGER [dbo].[Products2_eISBNEnteredDate] ON [dbo].[Products2]
For Insert, Update
As
Begin
Declare @ProductId int
Declare @eISBN Varchar(17)
Set @eISBN = '0'
If ( Update(eISBN) )
Begin
Select @Productid = I.Productid,@eISBN = I.eISBN
From Inserted I Left Join Deleted d on I.Productid = D.Productid
Left join Products2 P on P.Productid = I.Productid
Where (ISNULL(I.eISBN,'') <> ISNULL(D.eISBN,''))
If (IsNull(@eISBN,'') <> '' and IsNull(@eISBN,'') <> '0')
Begin
Update Products2
Set eISBNEnteredDate = getdate()
Where ProductID in (select i.ProductID
From Inserted i
Left join Deleted d on d.ProductID = i.ProductID
Where (i.eISBN is not null or replace(i.eISBN,' ','') != '') --where the new eISBN is not null or blank
and (d.eISBN is null or replace(d.eISBN,' ','') = '') --where the old eISBN is null or blank
and isnull(i.eISBN,'') != isnull(d.eISBN,'') --where the new eISBN is not equal to the old ISBN13
and d.eISBNEnteredDate is null)
End
If IsNull(@eISBN,'') = '' and IsNull(@eISBN,'') = ''
Begin
Update Products2
set eISBNEnteredDate = NULL
Where ProductID = @Productid
End
End
End
View 5 Replies
View Related
Aug 31, 2015
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
View 9 Replies
View Related
Oct 18, 2005
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
View 2 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Feb 29, 2008
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
View 4 Replies
View Related
Jul 6, 2006
HI all
I have a relativley unique setup
where all the sql servers have been consolidated
so multiple applications are connected to their respective databases on the same SQL server
The problem I have is when an app locks tempdb all the other apps that require it are blocked/slow
The ?
can tempdb be split or multiple tempdb's be created
View 1 Replies
View Related
Jul 5, 2007
Hi all,
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
Thank you,
View 2 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Jun 22, 2006
Hi,
How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ???
Thanks,
Alex
View 3 Replies
View Related
Nov 27, 2006
I have current current sql server 2000 database containing some columns in big5. To display these cols correctly, my asp.net nust have directive with CodePage="1252" ContentType="text/html;charset=BIG5". I can not update, or insert big5 character into these columns via .aspx page. I'm using .net framework 2.0. Please help me, thanks a lot for any help.
View 7 Replies
View Related
May 12, 2008
Hi all
I have Re-Builld index Job,it is runnining evry week.now a days my queries are runnign very slow.eben after running re-build indexs.Please guide me how to check where was the problem.
I am suspecting Stastitics how check the stastitics updation.
any help appreciated.
View 1 Replies
View Related
May 30, 2007
Hi,
I am using SQL Server 2000 sp4 (standard edition); when I run an Update Stats command against a table I get error 3628:
UPDATE STATISTICS IMSV7.BLITEMRT WITH SAMPLE 20 PERCENT
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.
Does anyone know of a fix or a workaround?
View 3 Replies
View Related
Apr 17, 2008
hi friends,
i need the steps for full text search with more than one tables in single database. I know the steps for full text search by single table in single database.
Thanks in advance
View 1 Replies
View Related
Jul 20, 2005
Hi all,I am doing some large updates,that may update 10,000 plus rows.This works fine when I execute the SQL directlyin Query Analyzer.If I set the timeout on my VB connection to 0 (zero)the connection should not time out????But it does.If I set the time out to a high value, say 1200,I get the same problem well within 1200 seconds.Also, I am getting the problem that the log fills up,but it is set to auto grow????Any ideas would be appreciated.ThanksGreg
View 1 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
Jul 20, 2005
I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?
View 4 Replies
View Related
Jul 4, 2007
1 ALTER PROCEDURE links_GetAllLinks
2 @startRowIndex INT,
3 @maximumRows INT,
4 @sortExpression nvarchar(50)
5 AS
6
7
8
9
10
11 SET @startRowIndex = @startRowIndex + 1
12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END
13 -- populate the table CAST(getdate() as int)
14
15
16 IF @sortExpression LIKE ''
17 BEGIN
18 SET @sortExpression = 'links_Links.link_date'
19 END
20 DECLARE @sql nvarchar(4000)
21 SET @sql = 'Declare @Links TABLE
22 (RowNumber INT,
23 link_id INT,
24 cat_id INT,
25 cat_title VARCHAR(100),
26 sub_id INT,
27 sub_name VARCHAR(100),
28 link_aproved BIT,
29 link_name VARCHAR(100),
30 link_url varchar(100),
31 link_desc varchar(500),
32 link_date datetime,
33 link_rating INT,
34 link_ratingscount INT,
35 link_ratingvalue INT,
36 link_poster uniqueidentifier,
37 link_comments INT,
38 hit_date datetime);INSERT INTO @Links
39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date
40 FROM links_Links INNER JOIN
41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN
42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id
43 WHERE links_Links.link_aproved = 1;SELECT * from @Links
44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1'
45
46
47
48 EXEC sp_executesql @sql
49
50
51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
View 4 Replies
View Related
Mar 25, 2003
Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance .... j
View 3 Replies
View Related
