Tag: SQL

SQL Server – Sql does not exist, returns 0 rows and should return one

After a while, I really do not know what's wrong here. (SQL is not my cup of tea I would say, but you still have to understand that hehe)

I have 2 tables, in different diagrams, that I need to pass the information from the "master" to "slaves" if the "slaves" do not have this line, c & # 39; is why I try to use it does not exist.

Here is the query that I use:

--Select everything from the master
to select
*
of
(to select
*
of 
         [serversqlexpress].xptoMaster.dbo.CcArtigos
or
Tipo_Doc in (& # 39; INE & # 39 ;, & # 39; INS & # 39;)
and Armazem = 2
and data_mov> = & # 39; 01-07-2018 & # 39;) as tmaster
where does not exist
- What does not exist in the slave
(select *
of 
           [serversqlexpress].xptoSlave.dbo.CcArtigos as tslave
or
tmaster.cod_art = tslave.cod_art
and Tipo_Doc in (& # 39; INE & # 39 ;, & # 39; INS & # 39;)
and Armazem = 2
and data_mov> = & # 39; 01-07-2018 & # 39;)

So from what I understood from what I found online (https://www.tutorialgateway.org/sql-not-exists-operator/), I try to select all the elements of the xptoMaster that do not exist on the xptoSlave BUT it returns 0 lines and according to the site, does not see what is the problem with the query, the logic of the site:

TO SELECT [Column Names]
OF [Source]
WHERE NOT EXISTS (Write a subquery to check)

The master test bases have 91 lines and the slave has 90. So I should get the master line that the slave has not, but I get 0 line: /

Edit:

select *
of [serversqlexpress].xptoMaster.dbo.CcArtigos
where does not exist
(select * from [serversqlexpress].xptoSlave.dbo.CcArtigos as tslave)

sql – exception java.lang.NullPointerException due to com.prepareStatement

I want to send files to the database and be able to recover it, but when I download the file, in my servlet, the error is due to com.prepareStatement

I tried to run it but I failed. I have tried to jdbc (prepareStatement). I used the PreparedStatement of java.sql.

Public class UploadServlet extends HttpServlet {

PrintWriter out = null;
Com connection = null;
PreparedStatement ps = null;
HttpSession session = null;

@Override
doPost void protected (request HttpServletRequest, response HttpServletResponse)
throws ServletException, IOException {
response.setContentType ("text / plain / charset = UTF-8");
try {
out = response.getWriter ();
session = request.getSession ();
String folderName = "resources";
String uploadPath = request.getServletContext (). GetRealPath ("") + File.separator + folderName;
File dir = new File (uploadPath);
if (! dir.exists ()) {
dir.mkdirs ();
}
Share filePart = request.getPart ("file"); // value of the text box for the name file
String firstName = request.getParameter ("first name");
String lastName = request.getParameter ("name");
String fileName = filePart.getName ();
String path = folderName + File.separator + FileName;
Timestamp added_date = new Timestamp (System.currentTimeMillis ());
System.out.println ("FileName:" + FileName);
System.out.println ("path:" + uploadPath);
System.out.println ("fristName:" + firstName);
InputStream is = filePart.getInputStream ();
Files.copy (is, Paths.get (uploadPath + File.separator + FileName), StandardCopyOption.REPLACE_EXISTING);
try {
com = DB.getConnectiion ();
System.out.println ("connection Done.");
String sql = "insert in employee1 (first name, last name, file name, path, date_added) values ​​(?,?,?,?,?)";
ps = com.prepareStatement (sql);
ps.setString (1, first name);
ps.setString (2, lastName);
ps.setString (3, FileName);
ps.setString (4, path);
ps.setTimestamp (5, added_date);
int status = ps.executeUpdate ();
if (status> 0) {
session.setAttribute ("FileName", FileName);
String msg = "" + FileName + "The file was loaded successfully ...";
request.setAttribute ("msg", msg);
RequestDispatcher rd = request.getRequestDispatcher ("/ sucess.jsp");
rd.forward (request, response);
System.out.println ("file successfully uploaded ...");
System.out.println ("Access path downloaded:" + uploadPath);
}
} catch (SQLException e) {
out.println ("the error is here" + e);
System.out.println ("Exception1 is" ​​+ e);
} finally {
try {
if (ps! = null) {
ps.close ();
}
if (com! = null) {
com.close ();
}
} catch (SQLException e) {
out.println (e);
}
}
} catch (IOException | ServletException | NullPointerException e) {
out.println ("Exception:" + e);
System.out.println ("Exception2:" + e);
}
}

}

SQL Server – The publication & # 39; My_publication & # 39; does not exist?

We try to test the success of transactional replication.
by googling, I found that I have to run in our editor this stored procedure:

sp_publication_validation

However, when I run:

exec sys.sp_publication_validation
@ publication = ReplicationFaouzi,
@ rowcount_only = 0,
@ full_or_fast = 0,
@ shutdown_agent = 0

I receive this error:

Msg 20026, Level 16, State 1, Procedure sp_MSpublication_validation Line 57
The publication & ReplicationFaouzi & # 39; does not exist?

When I run this sql:

SELECT publication
MSpublications
ORDER BY publication

I get as a result: 

RéplicationFaouzi

What's wrong?

acceptance tests – Tools to run SQL written tests on a database?

I have a data application, the results of which can be considered as tables in a database. I also have a number of test cases written in the form of SQL statements that run on these output tables. There are conventions in the SQL code that determine whether a test case was successful or failed.

We built a simple tool using TestNG that connects to the database and runs the SQL test cases. I was wondering if there were already open-source tools that allowed this. Any tool whose job is to automate the execution of SQL statements (ideally in .sql files) against a database and the collection of results would do the trick. A test-specific tool would be ideal.

Thank you!

networking – The network adapter could not establish the SQL developer connection

I have installed Oracle SQL Developer on my mac. He did not ask me to enter a username or password during the installation.
I tried to create a connection using the default user name and psw "SYS" and "SYSTEM" and it did not work. This gives me this error "The NIC could not establish the SQLdeveloper connection".

I also do not have these files "listener.ora and tnsnames.ora" to check the status of the listener or even to check the host and port variables.

Note: No Oracle product is installed on my computer, except for the one on which I have installed "Oracle SQL developer".

Please help me understand where is the problem?

sql – Get query pairs of values ​​from two different SELECTS

How could you do a INSERT in oracle (to be able to use PL-SQL) so that two pairs of values ​​are inserted Selected different (that is, getting the pairs of values ​​in a query to insert them later).

For example, we go out through two selected with the same number of lines, the following values, for example:

FIRST SELECTION: 2 3 3 4 9

SECOND SELECTION: 3 4 6 7 7

Is there a way to get a query with the pairs of values ​​(the first with the first, the second with the second, etc., so that they are inserted like this?
That is, get the following query:

FIRST LINE: 2, 3

SECOND ROW: 3, 4

THIRD ROW: 3, 6

etc.

Is there another way to insert them like that? I've also tried to use PL-SQL in Oracle through the controls, but I do not understand either.

thank you so much

wpf – Why does not my DataGrid show me records from my database (C #, SQL Server)?

I've been trying to display the data saved in my database in a DataGrid created in C # in Visual Studio 2015 in a WPF window and, until now, I can not find the data I've saved. ;fault. The data is stored normally, it does not show them. I have very similar projects, the only difference between these projects and this is the date and the amount of data to be stored. Otherwise, everything is almost identical. I do not know what to detail, it's the first time I overflow and I ask for more details on the amount of code. I was looking at yt because they showed data from a data grid and that its code is very different from mine, and I have a hard time adapting to it. Well, try it and nothing.

    public list Listordo ()
{
try
{
Listing IsDenuncia = new list();
DateTime d;
eDenuncia objDenuncia = null;
SqlConnection with = db.ConectaDb ();
SqlCommand cmd = new SqlCommand ("Select Codigo_Aleatorio, Name, DNI, Category, District, Province, Department, Date, Time_Table, Hour_minutes, Description, Status from Table_Denunciation", with);
SqlDataReader reader = cmd.ExecuteReader ();

while (reader.Read ())
{
objDenuncia = new eDenuncia ();
objDenuncia.Codigo_Aleatorio = (int) reader["Codigo_Aleatorio"];
objDenuncia.Number = drive (string)["Nombre"];
objDenuncia.DNI = drive (int)["DNI"];
objDenuncia.Category = drive (string)["Categoria"];
objDenuncia.Distrito = (string) player["Distrito"];
objDenuncia.Province = (string) player["Provincia"];
objDenuncia.Departamento = drive (string)["Departamento"];
d = player (DateTime)["Fecha"];
objDenuncia.Date_denunciation = d.ToShortDateString ();
objDenuncia.Horario_hora = (int) reader["Horario_Hora"];
objDenuncia.Horario_min = drive (int)["Horario_minutos"];
objDenuncia.Descipcion = (string) drive["Descripcion"];
objDenuncia.Estado = drive (string)["Estado"];
IsDenuncia.Add (objDenuncia);
}
reader.Close ();
returns IsDenuncia;
}
catch (Exception ex)
{
return null;

}
finally
{
db.DisconnectDb ();
}

}
// This is already in another layer
public list Listunits ()
{
return denunciadd.Listartodo ();
}

// and this is in the WPF window part

n Denuncianouncebusiness = new nDenunciation ();
public FrmDenounce ()
{
InitializeComponent ();
Showdentions ();
}

Private Showdemands ()
{
dataGrid.ItemsSource = denuncianegocio.Listardenuncias ();
}
Private Void Bt_Enviar_Click (Object Sender, RoutedEventArgs e)
{
Random rnd = new Random ();
string name;
int dni;
category of chain;
string district;
provincial chain;
string department;
Date and time date;
int hora_hours;
int hora_minutes;
description of the chain;
int random_code;
state of the chain;
Listing listing;

list = denuncianegocio.Listardenuncias ();
// random_code = denuncianegocio.generar_numero ();
name = textBox_Number.Text;
dni = Convert.ToInt32 (textBox_Dni.Text);
category = comboBox_Delito.Text;
district = CBDistrito.Text; // TO THAT
province = CBProvincia.Text; // TO THAT
department = CBDepartamento.Text; // TO THAT
date = (DateTime) dtfecha.SelectedDate;
hora_hora = Convert.ToInt32 (textBox_Hora.Text);
hora_minutes = Convert.ToInt32 (TextBox_Minutos.Text);
description = textBox_Descipcion.Text;
status = "unrevised";
random_code = rnd.Next (100 984);



string Disclaimer Message;
Message of denunciation = "COMPLAINT CODE:";

MessageBox.Show (MessageDisplay + (random_code.ToString ()));
MessageBox.Show (denuncianegocio.Registrardenuncia (code_random, name, ID, category, district, province, department, date, time, hour, minute, description, status));
Showdentions ();
}

SQL Server – Query to find multiple start and end times a day based on a Boolean value

I want to search for several start and end times in a single day based on the Boolean value, in which the data is divided into every interval of half an hour from one day to the next. So every day is divided into 24 hours / 2 = 48. There is a time slot.

Sample of data
Data example continued

Now, I want an exit like this: –

Exit

SQL Server – Can only specific SQL users be forced to use TLS?

My school would like to develop custom software for some built-in devices. In this context, it may be necessary for the devices to connect to a SQL Server instance.

Is it possible to force specific SQL users to use encryption when connecting to the server? We do not want to force it for the entire instance because of some important legacy software.

We are also evaluating other solutions (frankly, better), but I want to know what is possible.

database – changed the name of the computer – SQL server

I changed the name of the computer and entered these commands in SQL Server Management Studio (ssms)

sp_dropserver ;
sp_addserver , local;

I'm relying on this query 

select * from sys.servers

it's the result

Is it correct? the location, the provider chain, the catalog is NULL?

How can I tell if everything has changed based on the new server name?
Do I have to reinstall SSMS?