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
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?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add
db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);
Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie
My C# update method in the DAL (still working on the code)
private static bool Update(AvatarImageInfo avatarImage)
{
//Invoke a SQL command and return true if the update was successful.
db.ExecuteNonQuery("syl_AvatarImageUpdate",
avatarImage.AvatarImageID,
avatarImage.DateAdded,
avatarImage.ImageName,
avatarImage.ImagePath,
avatarImage.IsApproved);
return true;
}
I am using stored procedures to access the data in the database.
My update stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
@AvatarImageIDint,
@DateAddeddatetime,
@ImageNamenvarchar(64),
@ImagePathnvarchar(64),
@IsApprovedbit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
UPDATE[syl_AvatarImages]
SET
[DateAdded]= @DateAdded,
[ImageName]= @ImageName,
[ImagePath]= @ImagePath,
[IsApproved]= @IsApproved
WHERE[AvatarImageID] = @AvatarImageID
RETURN
END TRY
BEGIN CATCH
--Execute LogError SP
EXECUTE [dbo].[syl_LogError];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
My problem is that the 'INSERT INTO' query that sends the records to thetable is dynamically compiled in VBA and and the target table has a twocolumn primary key. I have made a number of attempts at getting 'WHERENOT EXISTS' to cure the problem but so far without success and previouspostings have resulted in advice to create an 'ignore duplicates' index.This solved the problem in asmuch as it allowed the SQL to insert the records that did not alreadyexist but resulted in the message appearing every time the user ran thethe query. Whilst this is not a major problem it is vaguely irritatingand I would like to find a way to stop it happening. I suspect that thesolution may involve using the @@ERROR command but I am not sure of thesyntax.RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedMy site has come under attack from sql injections. I thought I hadthings handled by replacing all single quotes with two single quotes,akaReplace(inputString, "'", "''")Alas, clever hackers have still managed to find a way to drop columnsfrom some of my tables. Can anybody direct me towards a best practicedocument on preventing these attacks?Thank you thank you,Kevin
View 4 Replies View Related
Using RS2000
I have a matrix control that is producing the data for my Row groups fine.
However, it is applying the "Hide Duplicate" rules when I don't want it to.
I have scanned the entire RDL file, running text searches against it to confirm that <HideDuplicates> does not exist anywhere in the file.
I have applied the "Hide Duplicates" rule, then removed it again but the matrix control appears to be doing its own thing.
Anybody able to shed some light on this please?
Thanks in advance,
Carl
Hello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
This morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
View 9 Replies View RelatedHi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:
1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
Hi
I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion.
My Insert code is here: Any ideas??
Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1
Dim Name As String
Dim values As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, DropDownList1, txtDate"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date) VALUES (@themeValue, @guideValue, @guidepageValue, @pagetypeValue, @changecategoryValue, @changefromValue, @changefromtextValue, @changetoValue, @changetotextValue, @contentmanagerValue, @dateValue)"
SqlCommandThemeTest.Parameters.Add("@themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@contentmanagerValue", DropDownList1.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@dateValue", txtDate.Text)
Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
Response.Redirect("WebForm3.aspx")
End Sub
I use paramaterized queries when using ad-hoc queries in my code-behind. Everywhere website I visit says to use stored procedures or paramaterized queries if stored procedures cannot be used. I understand how SQL injection attacks work, but could someone please enlighten me why a paramaterized query helps prevent these attacks? It seems to me that the text that is entered on the web form would just be passed into the paramater, but I'm obviously missing something. Thanks.
View 8 Replies View RelatedI 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
I have a schema/user called mstp who has tables in another schema/user. How do I prevent the mstp user from seeing those tables/data that do not belong to their schema?
example:
database xxxx has tables som.employees and mstp.employees The mstp user should only allowed access to mstp.employees and nothing else.
thanks.
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?
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
View 18 Replies View RelatedThe database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
View 1 Replies View RelatedI'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?
I have a letter I am creating in SSRS and would like the data to flow smoothly from one page to the next even if the whole section doesn't fit on a page instead of it forcing a page break which results in a large blank space on the page before. I have put the data into multiple text boxes (can grow is set for the text boxes) because of formatting requirements (I also tried the table object).
--Start Example--
TEXT HEADER 1
very long report letter information here (call it detail 1)
TEXT HEADER 2
even more very long report letter information here (call it detail 2)
TEXT HEADER 3
The term ""Accountant€™s Letter"" as used herein shall mean the certain math verification report, to be provided by an independent certified public accountant, certifying that the Securities shall generate the debt service payments due under the Loans. (call it detail 3)
--End Example--
So assume that everything but detail 3 fits neatly on the first page. Now part of detail #3 should fit on page one but it pushes the whole section to page 2 because it doesn't all fit. All I want is for say line to to be the last line on page #1 and lines 2 and 3 to be in page 2 instead of lines 1 through 3 on page 2. In reality, this letter is about 6 pages and each section is about a half page to 2 pages. This perceived minor issue has cost me a good day+ set back. Thanks,
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?
Any one any ideas on the cleanest way to achieve this?
Thanks
I have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
Thanks in Advance - SOX is killing me!
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
Is there a way to use trusted connections in ASP.NET and WinForm applications yet prevent users from accessing databases directly (outside of applications) ? I know the use of trusted connections are recommended for several reasons however I have a lot of applications that I need to prevent users from accessing databases directly outside of the applications themselves.
View 5 Replies View RelatedHi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View 10 Replies View RelatedHi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
Hello,
Does anyone know how to prevent a certain machine from accessing SQL server? We have users who use a generic username and password (i.e. one login for 15 users) and I need to prevent a certain machine (in a high risk location) from hitting my databases. Any ideas
Thanks for your time and help
Gurmi
Hi allWe had a small problem when an ASP web page had a missing 'where' statementand updated all the records in the table. Luckily we could retrieve all thedata from the backups.How do you guys prevent this from happening in your large systems. Is theresome teqnique for controlling this, I would imagine if you had thousands ofrecords in a table and some one made a programming error, then theconsequences would be disastrous.is there a setting within SQL Server that could force SQL update commands tobe limited to a criteria and if no criteria is supplied then reject thecommandthanks in advanceAndy
View 7 Replies View Relatedwe're in a business where customers often ask for a foolproof scheme that even prevents folks with DB privileges from fraudulently inserting, updating or deleting data. The scheme must be so air tight that a judge can be convinced of its reliability.
What are the most effective schemes out there?
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
View 2 Replies View RelatedWe are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message
10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
truncate table [stg].[Nationality_1_Leaf]
insert into [stg].[Nationality_1_Leaf](importType, ImportStatus_id, BatchTag, Name)
values(1, 0, 'some batch' , 'American' )
select * From [stg].[Nationality_1_Leaf]
exec stg.udp_Nationality_1_Leaf 'VERSION_1', 1, 'some batch'
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
I created a simple SSIS package that downloads a file from an FTP server and does some processing on it. I scheduled it as a job step with the Sql Job Agent. The problem is that this file is not always available for pick up, but when it is I need it very quickly. I'm setting the schedule to look for it every minute. Anytime the file is not there, the package fails and shows up in the job history in red.
Is there any way to prevent an error in this task from registering a package failure?
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.
Hi €“
We have built a .NET on SQL Server application with extensive business functionality and security. It uses SQL integrated security to control logon and access to the database objects. There is business logic built into the .NET code, so I don€™t want any chance that users access the tables directly through a tool such as Management Studio - many users need update, delete, insert on tables in order to use the applications functions. With integrated security they can logon through SQL Management Studio and change data in the tables directly.
How are people who are using integrated security for their business applications preventing logons through unauthorized tools or applications?
Peter