SQL Server 2014 :: Cursor To Delete Data?

Nov 5, 2013

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once

View 9 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Can Delete A Data-file Or File-group

Apr 27, 2015

In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?

View 3 Replies View Related

SQL Server Admin 2014 :: Cursor Stop Query At Specific Time

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;

[Code] ....

View 8 Replies View Related

SQL Server Admin 2014 :: Old Backups Will Not Delete

Jun 19, 2015

Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.

View 9 Replies View Related

SQL Server Admin 2014 :: How To Delete A Login Linked With Endpoints

Sep 1, 2015

I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "

Same problem facing on different servers.

Note : Environment is SQL 2012,SQL 2008 including cluster servers .

View 2 Replies View Related

Schedule Database Backup And Delete For Server Express 2014

Jul 30, 2015

I would like to auto daily back up for all databases on a SQL Server Express 2014, and keep back up of last 30 days.

View 3 Replies View Related

SQL Server 2014 :: Best Isolation Level To Use In A Long Delete Insert Query?

Sep 23, 2015

i use a single stored procedure to update many tables in sql server 2014 database, using defalut transaction isolation level we got random performance issues.

maybe it would better to use read uncommitted isolation level?

what's happens ,in the both cases( read committed, uncommitted) if the sp is called at the same time passing the same @key parameter?

this is a sample to show of the real stored procedure works:

CREATE PROCEDURE SP_Test
@Key int,
@Values1 Values1 readonly,
@Values2 Values2 readonly,
@Values3 Values3 readonly

[code]....

View 8 Replies View Related

SQL Server Admin 2014 :: Delete Orphans Users From Multiple Databases

Oct 21, 2015

I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.

How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .

declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @SQL as varchar (200)

[Code] .....

View 4 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Delete In Cursor

Dec 13, 2005

I have a cursor and when fetching a data, I want to delete some of them returned by the cursor.

My problem is that I get inexpecting result.

Can some how I should set properly my cursor.

Thanks

View 2 Replies View Related

Delete Cursor (must Change It !!!!)

Feb 3, 2005

Hi all,

The db that I took over is full of !@##$@, unnormalized tables, cursors, you name it and it has it :(.

There is this cursor that opens a temp table, fetches the key and then deletes from the production table using that key for every row in the temp table.

I want to change it to something like

delete from A
where exists (select 1
from B
where B.ID1 = A.ID1 and
B.ID2 = A.ID2)

Now, I'm thinking that this query would secuentially scan A and compare the key to what B has and that is a waste of time. Is there a way to do it the other way around ? Scan the rows on table B and then delete them from table A ?

I haven't really played with sql in some time, maybe the answer is trivial but I can't see it right now.

Thanks in advance

Luis Torres

View 3 Replies View Related

Insert And Delete In Cursor

May 1, 2007

I want to do insert and delete in a cursour but it's not working for me can any one check the below code and help me out to solve the problem,



DECLARE @LINKID AS INT

DECLARE @INITCOUNT AS INT

DECLARE @NumberOfDuplicates AS INT

SELECT @InitCount = (SELECT COUNT(*) FROM [SSDTemp])

Declare DedupeDataInSSDTempTableNewCur Cursor For

SELECT LINKID FROM SSD WHERE LINKID IS NOT NULL

OPEN DedupeDataInSSDTempTableNewCur

FETCH NEXT FROM DedupeDataInSSDTempTableNewCur

into @LINKID

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @CNT AS INT

SELECT @CNT = COUNT(LINKID) FROM SSD WHERE LINKID=@LINKID

if (@cnt =1) begin

INSERT INTO SSD (

[CallID],

[LinkID], [FirstName], [MiddleInit], [LastName],

[Suffix], [DateOfBirth], [SSN], [PhoneNumber],

[Addr1], [Addr2], [City], [State], [Zip] )

SELECT top 1

[CallID], [LinkID], [FirstName], [MiddleInit],

[LastName], [Suffix], [DateOfBirth], [SSN],

[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp

where linkid = @LINKID



DELETE top 1 from ssdtemp where linkid=@LINKID

end

if (@cnt =0) begin

INSERT INTO SSD (

[CallID],

[LinkID], [FirstName], [MiddleInit], [LastName],

[Suffix], [DateOfBirth], [SSN], [PhoneNumber],

[Addr1], [Addr2], [City], [State], [Zip] )

SELECT top 2

[CallID], [LinkID], [FirstName], [MiddleInit],

[LastName], [Suffix], [DateOfBirth], [SSN],

[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp

where linkid = @LINKID

DELETE top 2 from ssdtemp where linkid=@LINKID of DedupeDataInSSDTempTableNewCur

End

SELECT @NumberOfDuplicates = @InitCount - (SELECT COUNT(*) FROM [SSDTemp])



FETCH NEXT FROM DedupeDataInSSDTempTableNewCur

INTO @LINKID

END

CLOSE DedupeDataInSSDTempTableNewCur

DEALLOCATE DedupeDataInSSDTempTableNewCur

View 2 Replies View Related

SQL Server Admin 2014 :: Change Data Capture(CDC) For Data Warehouse / Reporting?

Aug 12, 2015

I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.

The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?

Is there any performance impact if we enable CDC on OLTP db?

Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?

What is the best way to implement CDC to take incremental changes for reporting.

View 0 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: Update Old Data With New Data

Jun 5, 2014

I got this script but all I get are the errors:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()

[code]...

View 8 Replies View Related

SQL Server 2014 :: How To Sum And Group Data

Jan 20, 2015

I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.

Is this possible?

This is my current SQL

SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Write A Cursor To Fetch Required Data From Table?

Oct 21, 2015

I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.

I have a temp table named getInvoice where I have five important columns

1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time

and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.

An example is below :

InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime

579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00

My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.

The query I wrote is below

declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0

[Code] .....

This query runs forever and doesn't stop.

View 6 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: How To Import Data To Server From Access Database Using Script

Sep 22, 2015

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO

[code]...

The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

Secondly I need to make 2 more changes to the code posted above.

1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.

2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

View 7 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

SQL Server 2014 :: Transform Data Into Column

Jan 8, 2014

I have data like this in the table :

IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40

Desired Output required:

IntRecpieID StrName 1 2 3

100 'A' 20 Null 40

101 'B' Null 30 Null

View 5 Replies View Related

SQL Server 2014 :: Insert Data From PDF Files

Mar 23, 2014

We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?

View 6 Replies View Related

SQL Server 2014 :: One To Many Relationship In Data Processing

Jun 24, 2014

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.

View 9 Replies View Related

SQL Server 2014 :: Un-Group Data From Report

May 28, 2015

We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.

Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department

If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data

--===== Create the test table with
SET ANSI_NULLS ON

[Code] .....

View 3 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

I am running an hotel application for all the bookings.

I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.

The application has an SQL database locally on my computer.

Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?

View 8 Replies View Related

SQL Server Admin 2014 :: Setup Performance Monitor Collector On Workstation Collecting Remote Server Data?

Mar 31, 2015

I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.

However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?

View 1 Replies View Related

SQL Server 2014 :: Data Profiling Tables Within Server

Jun 18, 2015

I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:

Null_Count
Null_Percentage
Total_Record_Count

I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Export All Data From Database

Dec 4, 2013

And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql

View 4 Replies View Related

SQL Server 2014 :: Enquiry On Retrieving Data From Database

Jul 18, 2014

I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options

Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.

I have no problem retrieving all the products which either only have auto option or manual option.

View 2 Replies View Related

SQL Server 2014 :: Bulk Insert Data Into Table

Jul 29, 2014

I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.

CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02

[Code] ....

I have attempted to bulk insert it into this table with no luck.

CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO

It does not like the double quotes. How to make this format work? Do I need a format file?

View 2 Replies View Related

SQL Server 2014 :: Combine Data From Historical Table?

Aug 13, 2014

Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.

View 9 Replies View Related

SQL Server 2014 :: Using Data From Two Tables In A Single Script?

Jan 20, 2015

Basically I've got two tables with a common column (in this case it's UserID)

The structure of the table Users is basically:

UserID FirstName LastName
1 Jim Smith
2 Nancy Orr
3 Ken Stephenson

And the structure of the classes table is

UserID ClassID
1 AAA
1 BBB
2 AAA
3 BBB

What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.

The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)

In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.

So far I've come up with

select UserId, count(*) as MultipleClasses
from classes
group by UserID
having count(*) > 1;

which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.

how would I do something like

Select firtname, lastname
from users
where Userid=

The output from that initial bit finding users with multiple classes.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved