Database Discussion Boards

Re: Pruning unused tables in a live database.

Mycroft Holmes13-Jul-17 14:31

Mycroft Holmes

13-Jul-17 14:31

mbb01 wrote:
Apart from those of us who are hopelessly OCD about these sort of things

Sorry I fall into this category so no further justification is required.

Never underestimate the power of human stupidity
RAH

Re: Pruning unused tables in a live database.

Chris Quinn14-Jul-17 1:09

Chris Quinn

14-Jul-17 1:09

We use procedure that move tables into a recyclebin schema based on naming and date criteria (e.g. no live tables will contain digits in their names, but tables created/copied in response to support tickets will have the ticket number appended to the table name).

After a specified period (two weeks in our case) tables containing digits that were created prior to thespecified date are moved to the recycle bin schema. If someone screams that their table has gone, we move it back in to the original schema.

After another specified period (again two weeks), any table that has been in the recyclebin schema longer than that will be dropped.

This is all done automatically using an Agent job which calls a standard stored procedure we created.

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Pruning unused tables in a live database.

mbb0114-Jul-17 1:37

mbb01

14-Jul-17 1:37

Thanks Chris, I'll certainly put forward your good ideas to our DBAs.

But ... you answered how, not why? Implicit in your answer is the acceptance that the live database should reflect what it 'should' be from Dev.

What are the reasons (technical or business) why a live system should reflect exactly what is in Dev, when those superfluous tables are not really harming the operation of the live system?

Any insights would be appreciated.

Re: Pruning unused tables in a live database.

Chris Quinn14-Jul-17 1:48

Chris Quinn

14-Jul-17 1:48

Our procedures specify that is we are doing data fixes, or adding new functionality, we back up any data affected and any procedures etc to allow us to revert quickly in cases where something has gone wrong, or the client has asked for the wrong thing etc. All backups contain the ticket number - this allows other team members to find them quickly if the person who originally worked on the ticket is not available for any reason

We clear them after a set period to free space and keep the production systems as tidy as possible.

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Pruning unused tables in a live database.

Eddy Vluggen14-Jul-17 3:39

Eddy Vluggen

14-Jul-17 3:39

mbb01 wrote:
I'm more interested in the reasons why it should be done at all.

Pruning does not prove the table is not in use or required. Imagine all your clients going down due to a simple logging-table that is only used once in the entire application, in a not-often used function.

mbb01 wrote:
After a debate about how it could be done and whether it should be done, I was struck by my colleague's statement of 'a lot of effort and potential pain, for not much gain'. There is of course the obvious risk of removing a table that really is used and causing a live system to fail.

..and what is the gain? That some empty unused table is no longer present? How much space did you reclaim with that action, and how much does space cost these days? Now compare the potential gain to the potential risc.

I would strongly recommend creating some documentation; that would slow the development-proces, but it would also result in a stronger grip on your datastore. Someone wrote a SQL/Linq/EF-statement? Please update the docs and jot down which tables are impacted and when.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Pruning unused tables in a live database.

Tim Carmichael14-Jul-17 9:57

Tim Carmichael

14-Jul-17 9:57

What percent, by size, of the database is unused tables? If the percent is significant enough, then the savings may be using less disk space, offline backups, etc.

Is there a legal requirement on how long the data must be maintained, and, by extension, if the data is still available, can it be used in a legal sense against the company?

Are there valid reasons to prune? Yes, but they are dictated by the business needs.

Re: Pruning unused tables in a live database.

jschell15-Jul-17 10:33

jschell

15-Jul-17 10:33

mbb01 wrote:
are there any good reasons for running the risk of removing defunct tables from a live system?

Sure. If there is a table with 100 million rows then removing it saves that space in a number of contexts. Obviously not as much an issue with 3 rows.

Additionally there is a maintenance issue. If there is an old table that Bob knows is no longer in use and then Bob dies and they hire Sharon to replace him the only way she can figure out that the table is not in use is to go through the entire code base. Not so much a problem if the is 100 lines of code but a real problem with 100 million lines of code (and poorly organized code at that.)

Same if Bob is on vacation and a new table replaced the old one but the data still exists in the old one. Sharon needs to make an emergency fix and ends up looking at the old one, and the data sort of looks correct, so that is what gets fixed.

Obviously that latter problem won't be a problem in there is sufficient testing in place to adequately test the enterprise. But if that is true then removing old cruft isn't a problem either because that same testing will demonstrate problems with that as well.

mbb01 wrote:

Apart from those of us who are hopelessly OCD about these sort of things

Me I am in the camp that there better be enough testing in place, both automated and manual, that changes have a minimal risk because changes will be needed even if old information is not removed. And I can't see that insuring functionality with additions and modifications would not cover deletions as well.

Or there is not sufficient testing in place and that is a problem.

Re: Pruning unused tables in a live database.

Mycroft Holmes15-Jul-17 15:43

Mycroft Holmes

15-Jul-17 15:43

Everyone seem to think this about space and performance, it should be about support. Someone looking at the schema in the future should not have to chase down the usage of redundant tables.

Never underestimate the power of human stupidity
RAH

Re: Pruning unused tables in a live database.

Jörgen Andersson17-Jul-17 11:28

Jörgen Andersson

17-Jul-17 11:28

Indeed!

Oh, and the OCD as well. $Shucks | :-\$

Wrong is evil and must be defeated. - Jeff Ello

how to rertrieve only 3 columns out of 7 columns using queryForObject in jDBCTmplate????am unable use correct syntax to retrieve only 3 columns in JdbcStudentDAO

LOKENDRA YADAV12-Jul-17 22:09

LOKENDRA YADAV

12-Jul-17 22:09

What I am tring to do is to select the username, password, and role from studentstable using queryForObject.

In my JdbcTemplate syntax is

--------------Public static Object queryForObject(String sql,RowMAPPER mapper,Object ...args)----------------

<code>public class JdbcStudentDAO implements StudentDAO{

public String getLogin(StudentTO sto) {

String sql="select username,password,role from studentstable";

System.out.println(sql);
-----------------------------Below has something wrong with queryForObject-----------------------------------------------------------
Object obj=JdbcTemplate.queryForObject(sql,new
StudentRowMapper(),sto.getUsername(),sto.getPassword(),sto.getRole());

StudentTO sto1=(StudentTO)obj;

System.out.println(sto1);

return sto1.toString();

}

}</code>

-------------------------This is my RowMapper where I'm getting all rows of my database, as shown below--------------------

public class StudentRowMapper implements RowMapper{

public Object mapRow(ResultSet rs) throws SQLException {

StudentTO sto=new StudentTO();

sto.setSid(rs.getInt(1));
sto.setName(rs.getString(2));
sto.setUsername(rs.getString(3));
sto.setPassword(rs.getString(4));
sto.setEmail(rs.getString(5));
sto.setPhone(rs.getLong(6));
sto.setRole(rs.getString(7));
return sto;
}

}

-----------------------------------This is an abstract method in StudentDAO---------------------------

public interface StudentDAO {

public String getLogin(StudentTO sto);

}

-- modified 13-Jul-17 5:40am.

Re: how to rertrieve only 3 columns out of 7 columns using queryForObject in jDBCTmplate????am unable use correct syntax to retrieve only 3 columns in JdbcStudentDAO

Richard Deeming13-Jul-17 8:57

Richard Deeming

13-Jul-17 8:57

LOKENDRA YADAV wrote:
select username,password,role from studentstable

Not an answer to your question, but there's a more fundamental problem with your code: You're storing passwords in plain text.

NEVER do that. And don't use reversible encryption either. Store a salted hash of the password, using a unique salt per record.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Want to Import Data from Salesforce into SQL Server using SSIS

indian14312-Jul-17 11:35

indian143

12-Jul-17 11:35

Hi friends,

I need to Import Data from Salesforce into SQL Server and export from SQL table to Salesforce using SSIS, can anybody please help me what is the better option available like any .net drivers etc. Or if we can directly import into SQL table by using any query that's also fine. As I am not much familiar any help would be very great.

I am also reading articles about these from google, so far didn't find free drivers, any help would be greatly appreciated.

Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Want to Import Data from Salesforce into SQL Server using SSIS

Mycroft Holmes12-Jul-17 15:23

Mycroft Holmes

12-Jul-17 15:23

What you want would require a connection string and credentials to directly access their database, I can't see that happening.

Never underestimate the power of human stupidity
RAH

Re: Want to Import Data from Salesforce into SQL Server using SSIS

indian14317-Jul-17 9:02

indian143

17-Jul-17 9:02

Hi,

I have downloaded Easysoft ODBC driver, but getting hard time to setup my DSN and connect to it, any idea which is best and easy to use ODBC driver to connect to the Salesforce.com, any help or idea would be very helpful - thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Want to Import Data from Salesforce into SQL Server using SSIS

Richard MacCutchan17-Jul-17 10:48

Richard MacCutchan

17-Jul-17 10:48

This is the second question about this site that I have seen today, and I wonder (again) why you are not asking at Salesforce.com: The Customer Success Platform To Grow Your Business - Salesforce.com Canada[^].

Re: Want to Import Data from Salesforce into SQL Server using SSIS

Member 1362406015-Jan-18 7:13

Member 13624060

15-Jan-18 7:13

If you want to transfer Data from Salesforce to SQL Server, you can try Devart SSIS, but I bigger fan of cloud solutions, `cause they are easier for me (E.g. <a href="">https://skyvia.com/</a>Skyvia)

Get following Error when Loading the SSIS Package using C# Code

indian1436-Jul-17 15:04

indian143

6-Jul-17 15:04

Hi,

I am getting the following error when I am trying to load SSIS Package using C# code:

Microsoft.SqlServer.Dts.Runtime.Package, ErrorCode : -1073676264, Description : Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Name="PackageFormatVersion">6</DTS:Property>" from node "DTS:Property".
, SubComponent: , HelpFile, HelpContext0, IdofInterfaceWithError

I got this error through the Event Listener I passed into the Load method call. This code was working before but suddenly stopped Working. The changes that happened in this mean time are, I needed to change my laptop, as it is new Laptop I needed to install all Software, some of them I installed online Evaluation versions like SQL Server 2008 R2 and 2012 because they were not available in there.

Here is the code that I used to load the Package from FileSystem:

if (!string.IsNullOrEmpty(PackagePassword))
                            {
                                _app.PackagePassword = PackagePassword;
                            }

                            //DTSEventListner eventListner = new DTSEventListner();
                            _Pkg = _app.LoadPackage(packageFullPhysicalPath, Listerner);
                            _PkgLocation = packageFullPhysicalPath;
                            _Status = DTSPackageStatus.Loaded;

Any help would be very helpful. Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get following Error when Loading the SSIS Package using C# Code

Richard Deeming7-Jul-17 1:47

Richard Deeming

7-Jul-17 1:47

PackageFormatVersion 6 suggests the package was created for SQL 2012.

Are you trying to load it with SQL 2008 R2, or using the 2008 R2 libraries?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Get following Error when Loading the SSIS Package using C# Code

indian1437-Jul-17 9:44

indian143

7-Jul-17 9:44

But now I have installed SQL Server 2012, is it because of .Net version or SQL Server Version I have installed SQL Server 2016 first then installed 2009 R2 but now I uninstalled 2016 but installed the 2012 from online Evaluation version thinking I will apply the key from company after Package starts working. Did I do anything wrong, any help would be greatly helpful thanks my friend. It bothers more when a thing starts working and then stops.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get following Error when Loading the SSIS Package using C# Code

indian14310-Jul-17 10:37

indian143

10-Jul-17 10:37

Hi Rick,

I am getting the following Warnings, is this something can be helpful for me, any advice would be very helpful my friend. Thanks my friend.

Severity    Code    Description Project File    Line    Suppression State
Warning     There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SQLServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.   EncryptDecryptSecretStrings

<br />
Warning     There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SQLServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.   FileWatcherScheduleApp

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get following Error when Loading the SSIS Package using C# Code

Richard Deeming10-Jul-17 10:50

Richard Deeming

10-Jul-17 10:50

That suggests it's a 32/64-bit issue. You need to change your project to target "x86" instead of "Any CPU".
How to: Configure Projects to Target Platforms[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Get following Error when Loading the SSIS Package using C# Code

indian14310-Jul-17 12:17

indian143

10-Jul-17 12:17

Its nice to do it but what if I deploy the code in Production and there if its different OS or different version of OS like 32 bit or 64 bit etc. Should I ignore this as it is Warning only because if it messes in the Server its going put me into more troubles.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Re: Get following Error when Loading the SSIS Package using C# Code

Richard Deeming11-Jul-17 2:28

Richard Deeming

11-Jul-17 2:28

If you're referencing a 32-bit assembly, you don't have a choice; you have to target x86.

A 64-bit OS won't have any problems running a 32-bit application.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Get following Error when Loading the SSIS Package using C# Code

indian14312-Jul-17 8:38

indian143

12-Jul-17 8:38

Thanks my friend

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

I am not able to view my data in my oracle forms 6i database is 11g

Member 128515166-Jul-17 1:59

Member 12851516

6-Jul-17 1:59

i wish to actually view my data in a oracle form developed on 6i based on date parameter but if i try to work on my oracle forms it dosent give me data acoordingly here is the query whcih i am using

SELECT e."Deal_No" deal_no, e."Remarks" Counterparty, m."MaturityDate" Maturity_Date, m."PriRedem" Principal, 


(m."MaturityDate" - To_date) Days_to_Mat

FROM MM_T_BORROWING e, MM_T_BORROWING_PM_DETAIL m

Where e."DeskCode" in ('10','11','12','13') and e."Value_Date" <= to_date and e."Maturity_Date" > to_date and e."Status" not in ('C', 'D', 'Z', '0','X') 
and e."Deal_No" = m."Deal_No" and "PriRedem" > '0' and m."MaturityDate" > to_date ;

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.