Consider the following tableCustomercustId char(10)accountExpiryDate datetimeaccountStatus bitNow, I want to update the accountStatus to False as soon as thecurrent date becomes accountExpiryDate.I think it can be done using "SQL Agent" but my webhost doesnt provideme access to that. I have access only to the Query Analyzer.ThanksShane
My table has more columns than this but I will just use the ones here tht are important. I have two columns in a table these are "ExpiryDate" and "Archived". When the row is created the "Archived" field will be null but the user will have entered a expiry date. When this date is reached I would like the "Archived" data to change to "1". Is this possible? Would I have to do it through some kind of script or can I set the database to do this automatically?
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion. As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ? CREATE TABLE [dbo].[LoginsExpiry]( [LoginName] [varchar](50) NULL, [ExpiryDate] [date] NULL, [Roles] [varchar](500) ) ON [PRIMARY] GO
I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
Hi All,I have a job coming up which involves me creating a vacancy system for our website.I (think) I'll only need one table:ID (PK)JobTitleJobLocationSalaryHoursJobDescriptionValidFrom (Date/Time)ValidTo (Date/Time)I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?Thanks in advance,Brett
SQL Server 2005 allows to use the EncryptByCert function to encrypt a Column, for example in an Instead-of Insert Trigger, and to use a DecryptByCert function to decrypt a Column, for example in a View.
This is indeed good news, but I have a simple :confused: question. To create a Certificate using the CREATE CERTIFICATE you have to give an EXPIRY DATE, or it will default to an expriry date of 1 year. How can one read the encrypted Column after the certificate has expired ?
Is there a way to renew a certificate without having to first decrypt all the fields and export them ?
I have a Column CCExp Varchar(7) that stores data like below
NULL '01/15' '02/15' '03/15' '04/15' '05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT CustomerBillingInfoID , CCExp FROM CustomerBillingInfo WHERE LEFT( CCExp, 2) < MONTH(GETDATE()) AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2) and CCExp NOT LIKE '#%' and CCExp not like 'NULL'
RETAINDAYS option in BACKUP command. SqlServer2000 (standard) on Win xp server. Old backups are not being removed from backup sets.
I've created logical dumpdevices which maps to a physical file on disk - using sp_adddumpdevice. Regular, scheduled backups are successfully written to the backup sets (Complete, differential & Tlog) - This works fine and I can restore from them OK. I've set the RETAINDAYS option of BACKUP command to keep backups for varying periods (eg full = 8 days, diff = 2 days, and Tlog = 1 day). When the backups are viewed by 'drilling down' thru >Backup > open backupdevice > view contents.... all previous backups are still available even though their expiry date has passed. The backup set files (dumpdevice files) are getting inexorably bigger and will eventually fill the available disks ! I don't want to delete or overwrite the entire contents of the backup set - just those backups more than 8 days old.
I've run sp_configure 'media_retention' 1 RECONFIGURE WITH OVERRIDE
but the old backups are still there.
dbs are getting ever bigger (high activity OLTP system) currently 2 production dbs totaling 50 GB. Is this a bug? - ie. the option to set a retention period even though I'm writing to a logical 'dump device' rather than a normal *.BAK / *.TRN file? or Am I doing something wrong?
HELP.
Cris Yarker email to crispin@paymentsystemsltd.com
The company that I work for has an internet application that has been running in our client's environment for 2 years.
The SQL box is a quad Xeon 750 with 4 gig of ram and over 50gig of disk. This box has 4 databases that are used for the application. The primary database is about 1.5gig in size (.mdf file) the others are relatively small.
The box is queried from a separate windows 2000 server box with our COM based application running under IIS.
The boxes are located on the same switched LAN.
Up until recently the application has been running fine, but all of a sudden we have had masses of database time outs and the processor utilisation is always 80-100. In turn these time outs crash IIS eventually.
I Understand all of the ideas about increasing the timeout in a command object, etc.
I have monitored blocking on the SQL server and over a 30 minute period of live usage I saw 1 block and 0 deadlocks.
In addition to the above often our application gets an imediate time out error, hence after no time.
We have also monitored the network bandwidth between the two boxes and they only use about 30% on average of available bandwidth.
My 1st question is - Does SQL give a time out error (hence refuses connections) if it is running at 100% and cannot handle any more load??
My 2nd question is, In other peoples experience is the box we are using powerful enough.
My 3rd question is, Can the network have an effect on the time outs even though there is plenty of bandwidth.
Thank you in advance for your time in reading this and I appreciate any input that you may have.
Simon Gratton Senior/Lead Developer Fastrack Software Ltd.
The windows password used for the client subscriber expired on the publisher.
I reset it on the publisher - but SQL still seems to think that it is not expired - I get the following message in the event log of the publisher running SQL Server 2005
Login failed for user 'XYZ'. Reason: The password of the account has expired. [CLIENT: <local machine>]
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
I do realize that his could be posted in a few spots but I think the answer is in the SQL. I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting. ex: <asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox> I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together. I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters. Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update. There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it. wbochar
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
Here is the code... How do I get the table just to give me back the date by latest date??
update RIO.dbo.tblArCust SET CustName = LEFT (CustomerName,25), Addr1 = LEFT (Address1,25), Addr2 = LEFT (Address2,25), City = LEFT (COFFEE.dbo.vueCustomerAddress.City,15), Region = LEFT (State,2), PostalCode = LEFT (Zip,5) from COFFEE.dbo.vueCustomerAddress join RIO.dbo.tblArCust on COFFEE.dbo.vueCustomerAddress.CustomerCode = RIO.dbo.tblArCust.CustId AND COFFEE.dbo.vueCustomerAddress.addresstypeid = 1 and COFFEE.dbo.vueCustomerAddress.LastUpdatedDate > ????
I have two tables incidents and prodwin . I have prodrecddt (datetimecolumn) in prodwin table which I have to update and it should be updated such that it should be greater than initdt and less than invcompleteddt which are in incidents table. Right now my prodrecddt is same as initdt. My date diff between initdt and invcompleteddt is from 3- 28 days
I thought of increasing the prodrecdt plus one and then updating it like this but it wont work this way
UPDATE prodwin SET prodrecddt = invcompleteddt -DATEPART(hour, invcompleteddt)%5 + 1
is there any way i can use WHERE field so that i can say where datediff is more than 5
i have a column that stores the last update date for a dimension table. This column will store the date when a row was last updated.
i'm using SCD in my dimension table data flow. i can't manage to set the lastupdate column to be modified when a row has been changed. when i set the column as fixed attribute, the column will not change even though it has been updated. if i set the column as changing attribute, the whole column for the table will change regardless it has been updated or not...
Is there a way to find when was a table last updated? We do not have any timestamp column in it. Idea is to get rid of tables that are not used for long long time! Thanks. Di.
hi. i am working with PHP and MSSQL (i know its not a smart move, but I don't have a choice). I got almost everything working correctly except the date format.
I can select the date and display it the way I want by doing this select convert(VARCHAR(10), date, 105) as new_date FROM table.
But when I have a field to update the date I am having trouble.
How do I update the MSSQL database with a new date if the date displayed is DD-MM-YY
How do I update a date with this format? I put HTMLEncode = false and dataformatString = {0:d} so I can just have the date and drop the time. Now it's not updating in the database. Here's my stored procedure: CREATE PROCEDURE [UpdateRtnDate] @loanrequestid int, @returndate datetime AS Update LibraryRequest set[returndate]=@returndatewhere loanrequestid = @loanrequestid It doesn't go into the database what am I misisng?
I have a table called ppd2002, which has an id field a testdate field (datetime), and a previoustesdate field (datetime). The testdate field is filled in. I have another table called ppd which has an id field and a testdate field. I need to update the ppd2002 field called previoustestdate with the max(testdate) field from ppd, but that max date must be less than the current testdate in the ppd2002 field. The id field is the link field between the 2 tables. Tried to create a view, then a update but still stuck.
Startdate, rec_num , recursive_value, recursive_date 04/02/2014 3 d 04/02/2014 04/02/2014 3 d 04/02/2014 04/02/2014 3 d 04/02/2014
I will like to update recursive_date to emulate the recursive_number and recursive_value fields which specify every 3 days. The recursive_value field can also be w to specify weeks, m to specify month or y to specify years. So my value in the recursive_date field should be updated as followed
Startdate, rec_number , recursive_value, recursive_date 04/02/2014 3 d 04/02/2014 04/02/2014 3 d 04/05/2014 04/02/2014 3 d 04/08/2014
hi! i'm new to sql and working on my first database, I use microsoft sql server 2000 - in my table i want a row in which date and time is saved whenever the dataset is changed. I use getdate() as default value and it saves the time when I create a new entry, but not yet when I update an old one. I know that it must be a formula with something like update tbl set = getdate() but I don't know exactly how it works. any help on this?
I have a table with date,cost,Ltype columns I have to update the cost based on Ltype and count of rows in a singel day. the business rule is like this 1.LtypeA =$90 LtypeB=$30 2.50% discount($45 & $15) after 30th row in a singel day for each row over 30
i have data for 2006 year and updated every day with new rows ie i have to run a update for the historic data and run it everyday for the newonce.
I have a site and there is a ` current features` on the index which shows our newest pictures for movies. I use dreamweaver, so When you insert record a "picture", it takes place on the table, but when you insert another one, it doesn't replace the current one.
