SQL restores

Had to do some SQL Admin work and wanted to put this into my tip list.
The UI in SMSS will generate the script for you but I wanted it for my own
understanding.


RESTORE DATABASE [DCOPS_January]


FROM DISK = N'G:\SQL_Restores_PRIVWEB\DCOPS_OwnerJanuary18.bak' WITH FILE = 1,


MOVE N'DCOPS_Owner' TO N'D:\SQL_Data_PRIVWEB\DCOPS_January.mdf',


MOVE N'DCOPS_Owner_log' TO N'E:\SQL_Logs_PRIVWEB\DCOPS_January_log.ldf',


NOUNLOAD, REPLACE, STATS = 10



GO


This command lets you move the original file names which were backed up to your own name of choice.

Open Oracle dmp file and export to SQL DDL file

Problem:

Working a project to migrate a system off of Oracle and onto SQL Server.
The current host responded to my request for DDL with a dmp file!  The dmp
file is a binary format which is used by Oracle toolset to import and export database
content and structures.  The point being that without Oracle installed on a system
you cannot read this file.  Minor point we are not an Oracle shop and so do not
have the in-house expertise.  Say hello to Prof Google.

Solution:

1.) Download Oracle OracleXE112_Win64.zip.  Available on the Oracle site once you register.
2.) Install this onto your box
3.) Most likely will install at c:\oraclexe\app\oracle
4.) Get your dmp file (YourDBA.dmp)
5.) Place it into following location: c:\oraclexe\app\oracle\admin\xe\dpdump
Note: You can place this dmp file in another folder but you will be required to configure
the database settings.(https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL819)
6.) Start up a command prompt and navigate to directory: c:\oraclexe\app\oracle\product\11.2.0\server\bin
7.) Issue following command
impdp 'system/root AS SYSDBA' show=Y file=YourDBA.dmp


End result was the creation of the YourDBA.sql which gives us the file we wanted in the first place!

Reference:

1. https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL819
2. https://docs.oracle.com/database/122/SUTIL/instant-client-for-SQL-loader-export-import.htm#SUTIL-GUID-FF1B6F75-09F5-4911-9317-9776FAD15965
3. https://stackoverflow.com/questions/16961295/how-to-get-an-oracle-sql-text-from-a-dmp-file

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Problem:
I was trying to migrate data from one catalog to the other and hit this error. 


insert claim

select

*

from [MANNHEIM].dbo.CLAIM c

Inner Join Exercise e

on e.Exercise_ID = c.EXERCISE_ID

where e.EXERCISE_ID is not null

The problem was that the field in the source table had a different default format then
the target field.  The way around was to force the query to use a common default collation.




Solution:


insert claim
select
from [MANNHEIM].dbo.CLAIM c
Inner Join Exercise e
on e.Exercise_ID COLLATE DATABASE_DEFAULT = c.EXERCISE_ID COLLATE DATABASE_DEFAULT
where e.EXERCISE_ID is not null


Source:
http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

SQL Server count rows in a database

Problem:

I began analyzing a legacy database and need to get some statistics on what is being used.
I needed to get a feel for which tables were actually being used.

Solution:

select
schema_name(obj.schema_id) + '.' + obj.name AS [TableName],
row_count
from (
    select
        object_id,
        row_count = sum(row_count)
    from sys.dm_db_partition_stats
    where index_id < 2  -- heap or clustered index
    group by object_id
) Q
join sys.tables obj on obj.object_id = Q.object_id
where row_count > 0
order by [TableName]

Source:

http://stackoverflow.com/questions/428458/counting-rows-for-all-tables-at-once

SQL table count in your schema

Needed to get a count on a database that I am reviewing.  This query really helped.


use YourInstanceName

select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
and TABLE_NAME not like '$Foo%'
order by TABLE_NAME

Error 3355 Cannot connect to SQL Server. SQL Server not found.

Problem:
I ran into this issue due to a SQL being configured on a port other than 1433.
The configuration wizard kept failing with this error and this was even after
I had specified the correct path in the wizard to the SQL server.

Solution:
What was missing was the SQL alias on the WFE.
How do you get this?

1.) Open command prompt as administrator
2.) Type cliconfg
3.) Add server alias.

Connection string sytnax for IIS to SQL Server connection.

I always forget the syntax for connnection strings in my applications.  The guys at connectionsstrings.com do a great job but stumbled across another way to get at the correct syntax.

The steps below.

1.     Right-click an empty spot on the desktop and choose NEW, TEXT DOCUMENT from the context menu

2.     Save it with a .udl extension, and click yes when it asks are you sure.

3.     Double-click the new udl file you just created. It will open a dialogue. Go to the Provider tab, and choose the appropriate provider.

4.     Go to the Connection tab and fill in the server name and database name, and choose NT authentication (or use a specific username and password, which is SQL authentication). Now click Test Connection. If it works, you're ready to click OK and move on to the final step. If it doesn't you need to resolve permission issues, or you've mis-typed something.

5.     Now right-click the file on the desktop and open it in notepad. It will display the connection string that you can copy and paste to wherever you need it.