With SQL Server, when we forget or we lose the password of the DBA user 'sa', and we are the administrators of the database, we have a little problem.
It's easy to forget this password because probably we have our own user administrator and don't use to log in with the sa account. We also could have inherited the administration of a SQL Server database, with any documentation about the sa account or we coul'd have installed a database and deleted the login with admin privileges without knowing the password of the sa user.
For all this situations there is a better solution than reinstalling the database..
If you have to change the type of a key column on SQL Server, and the new column type has to be integer, and even identity, you can do it by using some criteria in order to get the rows ordered (PK= index clustered= order by in physical disk by this column)..
We will change the type of the PK column of a table in a sample with 2 typical invoice tables master-detail where the key columns are nchar. 'invheader.invoiceid' is the primary key of the master table and 'invlines.lineid' is the primary key of the detail table..
The broken external links from our web is a major problem because, besides subtracting useful publication, depending on the importance of the external source to supplement the informations of our post, penalize post, and our domain if we have a lot of face to the search engines like Google.
As broken links without significant and is an easy thing to detect for any robot or spider, few SEO tools not report any broken links on our website when making our typical SEO site audit. In this post we will explain how to use the Semrush tool to detect and correct these broken links.
The operator to concatenate in SQL Server is '+', but this operator is also used to add values.
Being the same operator for both, the query analyzer makes a concatenation or a sum based on the type of data fields being treated. If the fields are of 'string' type, the operator concatenate, and if it has numeric fields, two integers, for example, it add the two values.
In many cases, especially in ETL processes or data loading to a data warehouse, for example, we would rather use a single sentence or the checking of the existence of a register and its updating. If register doesn't exist we would want to insert it. This combination has been nicknamed UPSERT, although in SQL there is a specific statement to make it, which is MERGE.
In a LinkedIn debate about how to update from a select with summarized registers in SQL Server, Sergio Romero has shared an interesting tip about new summarize functions on Oracle 11g R2.
A very powerful way to do a table's update in a SQL Server database is to link it with another table with a join, and updating the fields of everyone of its registers using the field's values of the registers linked with the another one. This technique had been discussed in the forum post Update with join or update from select in SQL Server..
The operation of databases from different manufacturers is similar, but there are many differences between them, even at level of SQL syntax.
For example, if you knows Oracle, and one day you have to do a date conversion with MySQL, you will wonder the following:
- How to do a TO_DATE () / TO_CHAR () with MySQL?
I open this blog entry to discuss different ways of doing things with each database type.
Here I link the SQL Tips Bristle Software , which explains quite well how to do some things with MySQL, Oracle and SQL Server, and I find especially useful the chapter on differences between Oracle and SQL Server
In order to access to the same database where are you working outside the database server you must activate the service called listener, it has to be listening.
It can happens that the database is properly raised and can not connect from other servers, which are also set correctly (correct TNSNAMES, etc.)..
In these cases could be that the listener has a problem, or simply has not been initiated.
To Check the status, start or stop it is very simple. Just open a command line session (console terminal, etc..) with the user that has installed the database, and run the lsnrctl command with the following parameters:
Check your state:
> lsnrctl status
Stop the listener:
> lsnrctl stop
Start the listener:
> lsnrctl start
Keep in mind that when you stop the listener, the connections that are already in the database won't be closed, so a short stop is not very traumatic, only connections trying to enter while the listener is stopped are rejected, should not affect anyone who already has an opened session.
If you have the system user who installed the database you can enter SQL Plus as DBA user, without entering a password as follows:
1. Enters the system with this user.
2. From the command line, go into SQLPlus typing:
> sqlplus "/as sysdba"
If you need to enter using this way because you forgot the password of a user, you can easily change it:
SQL> alter user user_name identified by new_password;
It can be more than one Database installed on the server, so you have to validate that the environment variables of the Oracle's user are pointing to your database.
For verifying that you has login into the correct database you can execute this statement:
SQL> select name from v$database;