Transact SQL :: Syntax Check For Archiving A Table
May 12, 2015
I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).
CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.
My estimated execution plan shows a index scan.
Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?
I have two tables called A and B and C. Where A and C has the same schema A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved 1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status 1 A 7/7/2007 No3 B 2/4/2006 Yes Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column Pls provide me with full stored procedure code.
I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.
My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.
I'm getting the following error messages: Incorrect syntax near the keyword 'in'. Server: Msg 156, Level 15, State 1, Line 38 Incorrect syntax near the keyword 'group'.
/* create temp tables */ select distinct d_vst_id as 'DRW_ID' ,d_vst_instid as 'DRW_INSTID' into temp_tb1 from dnr_vst_db_rec where d_vst_instid = '' and d_vst_dontyp = 'WB' and d_vst_status = 'DN' and d_vst_date between 20020301 and 20030228 order by d_vst_id
Select distinct n_per_id as 'ID1' ,n_per_gender as 'GENDER' ,n_per_birth as 'BIRTH1' ,d_bty_abo + d_bty_rhesus as 'ABO1' ,n_adr_city as 'CITY1' ,n_adr_zip as 'ZIP1' into temp_tb3 from temp_tb1 right outer join nat_per_db_rec on DRW_ID = n_per_id right outer join dnr_bty_db_rec on DRW_ID = d_bty_id right outer join nat_adr_db_rec on DRW_ID = n_adr_id where DRW_INSTID = '' order by n_per_id
select distinct getdate() ,d_aaa_insthdg , case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as 'AGE' ,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1' ,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL' from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a where a.d_vst_instid = '' and a.d_vst_instid = d_aaa_instid and a.d_vst_id = ID1 and a.d_vst_instid = n_per_instid and a.d_vst_id = n_per_id and n_per_gender = 'M' and a.d_vst_btcdte between 20020301 and 20030228 and a.d_vst_btcdte = (Select max(b.d_vst_btcdte) from dnr_vst_db_rec b where b.d_vst_instid = '' and b.d_vst_status = 'DN' and b.d_vst_dontyp = 'WB' and b.d_vst_id = a.d_vst_id and b.d_vst_btcdte between 20020301 and 20030228)
group by d_aaa_insthdg ,case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as
First of all, hello and good morning, my question is, you can check SQL syntax in SQL Server with secondary button mouse or "Check SQL" button in toolbar (Microsoft Management Console 1.2). I´d like to know if there´s a way to use these Server tools from Visual Basic 6 SP6, something like APIs ... If there´s no solution, can anybody give me an idea of how to check SQL syntax in VB. The application wants the users to make their own SQL sentences, (they just can write whatever they want ?????)
when I try to create this SP I get: "incorrect syntax near @MyResult" I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon @UserName varchar(16), @Password varchar(16) , @MyResult varchar(3) OUTPUT As if exists(Select * From User_Table Where UserName = @UserName And Password = @Password) begin @MyResult = 1 end else begin INSERT INTO FailedLogons(UserName, Password) values(@UserName, @Password)
declare @totalFails int Select @totalFails = Count(*) From FailedLogons Where UserName = @UserName And dtFailed > GetDate()-1
if (@totalFails > 5) UPDATE User_Table Set Active = 0 Where UserName = @UserName
DECLARE @NAME AS varchar(1000) DECLARE @ADDRESS AS varchar(1000) Declare CUR_C Cursor For Select Rolename From DCJ_SecurityRole Open CUR_C Fetch Next From CUR_C into @NAME While @@fetch_status =0 Begin IF @NAME not in('All','PUBLIC') Begin SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com' INSERT INTO #UserDetail EXEC (' SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME, initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY (ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM ''''LDAP://DC=XYZ,DC=com'''' WHERE MemberOf=''''' + @ADDRESS +''''' '') ') END Fetch Next From CUR_C into @NAME END CLOSE CUR_C DEALLOCATE CUR_C
When I executes this code I am getting some syntax error like Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'DCJAppDev'.where APJPRD is one of the role, Can anyone suggest what dynamic sql part has error??
if(@b='1') begin select * into #example from example select * from #example drop table #example end else begin select * into #example from example select * from #example drop table #example end With syntax check, I always get "'#example already exist'" But why? Just because of two "select into temp table" operation? I am confused. Thank you.
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).
I am using Visual Web Developer Express 2005 and SQL Server Express 2005. I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine. I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed. How can I check for null values and skip a block of code within my Transact Sql trigger. Thanks.....
I have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).
ALTER PROCEDURE [dbo].[requestConnect] -- Add the parameters for the stored procedure here @requestor int, @requested int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
The query should return only cnsmr_id=2 since all the complaints is closed (blank/NULL) i have achieved this using having but is there is more performance way of doing this?
I'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something
I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?
Table A Table B dependant on Table A Table C dependant on Table B Table D dependant on Table C Table E dependant on Table D
I have a SQL Server 2005 DB in which a design change requires me to drop a column.Is there a way of identifying all references in the DB including functions & stored procs's that reference this column? I understand there are 3rd party products that can do this but was wondering if there was something baked into SQL Server itself.
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn Old Value New Value ReceivedDate  7/8/2015 5:00:00 AM ReceivedDate 7/8/2015 12:00:00 AM 7/9/2015 5:00:00 AM ReceivedDate 7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn ... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ... I tried adding CASE UpdateColumn ... WHEN 'ReceivedDate' IS NOT NULL THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ...
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
select row_number() over (partition by merrickid order by recorddate asc) as rn, merrickid, recorddate, allocestgasvolmcf, sum(allocestgasvolmcf) over (partition by merrickid order by recorddate asc) as xxxxxxxx from dbo.completiondailytb where merrickid=1965
I get the following message after I insert the sum() over function:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'order'.
Is there a simple command that can be executed to check if the current user has sysadmin privs? Â I just want to check to see if I have sysadmin privs and if so then execute a command, if not do nothing in .Net code. Â I just want to do this check once and set a variable in the .net code.
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date) go create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT AS BEGIN Â IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2) Â Â RETURN 1 Â Â RETURN 0 END go alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0) go insert into t1 values ('2015-01-01','2015-02-01') insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message:Â The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
