SQL Tips

How to identify your SQL Server version and edition

SQL version 2008, 2005, 2000:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

What's in my backup file? Or what is my datafiles logical or physical name?

RESTORE FILELISTONLY from DISK = 'c:\path\DatafileName.bak'

RESTORE HeaderOnly from disk  = 'c:\path\DatafileName.bak'

RESTORE LabelOnly from disk  = 'c:\path\DatafileName.bak'

RESTORE VERIFYONLY from disk  = 'c:\path\DatafileName.bak' WITH STATS = 1

/* JOIN */

SELECT i.itemgroupid, i.name, v.princIpialproductgroupid, v.productgroupname, n.nordicid
FROM inventitemgroup i
INNER JOIN VTNI_ProductPrincipalGroups v ON i.vtni_principialproductgroupid = v.principialproductgroupid
INNER JOIN VTN_NORDICPRINCIPALS n ON v.nordicid = n.nordicid
ORDER BY WHATEVER!

Make backup/copy of table data. Please remember if the table is ‘big’ you need space in the database.

SELECT * INTO DUMMYTABEL FROM SOURCETABLE 

TransLog

The transaction log for database 'DynamicsAx_PreProd' is full. To find out why space in the log cannot be reused, see the

log_reuse_wait_desc column in sys.databases

Transaction in translog:

SELECT dd.transaction_id,
ds.session_id,
database_transaction_begin_time,
CASE database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END database_transaction_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
END database_transaction_state,
database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved,
database_transaction_begin_lsn,
database_transaction_last_lsn
FROM sys.dm_tran_database_transactions dd
INNER JOIN sys.dm_tran_session_transactions ds
ON ds.transaction_id = dd.transaction_id
WHERE dd.database_id = 5

-- Finding when a table was last inserted / or deleted or whatever.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20
qs.last_execution_time
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1)
LIKE '%INSERT INTO dbo.Underlying%'
ORDER BY qs.last_execution_time DESC
 

Sometimes, we have a database containing hundreds of tables and we need to find a table containing a particular column name.
In the following SQL, just replace %email% by desired own column name to get the table(s) name in which such column may exist!

SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name,
type.name as column_datatype,
c.max_length AS column_Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
JOIN sys.types type ON c.system_type_id=type.system_type_id
WHERE c.name LIKE '%email%' AND type.name <> 'sysname'
ORDER BY schema_name, table_name;

 

SELECT

    SchemaName = s.name,

    ProcedureName = pr.name

FROM

    sys.procedures pr

INNER JOIN

    sys.schemas s ON pr.schema_id = s.schema_id

    order by s.name

SELECT  sys.objects.name, sys.schemas.name AS schema_name

FROM    sys.objects

INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

order by schema_name

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)

AS SchemaTable

FROM sys.tables

order by QUOTENAME(SCHEMA_NAME(schema_id))

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

SELECT s_tst.[session_id],
s_es.[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
s_tdt.[database_transaction_begin_time] AS [Begin Time],
s_tdt.[database_transaction_log_record_count] AS [Log Records],
s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
s_tdt.[database_transaction_log_bytes_reserved] AS [Log Rsvd],
s_est. AS [Last T-SQL Text],
s_eqp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions s_tdt
JOIN sys.dm_tran_session_transactions s_tst
ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s_es
ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec
ON s_ec.[session_id] = s_tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests s_er
ON s_er.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;
GO
session_id Login Name Database Begin Time Log Records Log Bytes Log Rsvd Last T-SQL Text Last Plan
———- —————– ——– ———————– ———– ——— ——– ———————————— ———
54 ROADRUNNERPR\paul foo 2010-02-01 15:28:48.560 2 236 8550 begin tran insert into t1 values (1) NULL
55 ROADRUNNERPR\paul foo 2010-02-01 16:38:18.373 3 356 8852 insert into t1 values (3) NULL
 

Shrink TempDB Data File Without SQL Restart

We can shrink the tempdb data file without restarting the SQL Server using the following steps.

First verify the space usage information from MDF file, If you find there is enough space to shrink then ensure there wont be any open transactions running on the tempdb and execute the below steps to shrink the TempDB Data File.


DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024)
GO
Posted by Kalyan Akula at 4/21/2012
 

Find Last Date Time Updated for Any Table

select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

List indexes in database

SELECT
so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY
so.name, si.type
 

Free diskspace on BI - Prop direkte ind i Excel graf

select drive, DateInserted, free

from JOM_drives

where DateInserted between '2014-01-13 00:00:00.000' and '2014-01-13 09:00:00.000'

order by drive, DateInserted

How's your statistics looking?

SELECT

      ss.name AS SchemaName

      , st.name AS TableName

      , s.name AS IndexName

      , STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'

      , s.rowcnt AS 'Row Count'

      , s.rowmodctr AS 'Number Of Changes'

      , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS

                  DECIMAL(28,2)) * 100.0)

                        AS DECIMAL(28,2)) AS '% Rows Changed'

FROM sys.sysindexes s

INNER JOIN sys.tables st ON st.[object_id] = s.[id]

INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]

WHERE s.id > 100

      AND s.indid > 0

      AND s.rowcnt >= 500

ORDER BY s.rowcnt desc

/*   When BI are running */

SELECT command, status, ((total_elapsed_time/1000)/60) as 'TotalTime-Minutes', ((cpu_time/1000)/60) as 'CPU-Minutes', wait_type, wait_time, last_wait_type, blocking_session_id, logical_reads,user_name(a.user_id),*

FROM sys.dm_exec_requests a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

WHERE session_id <> @@spid

ORDER BY total_elapsed_time DESC

Get query plan, but with sys.dm_exec_requests

--dette er en ny som jeg eksperimenterer med. Mister UserName?

SELECT percent_complete, cpu_time,logical_reads,user_name(a.user_id),*
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
WHERE session_id <> @@spid
ORDER BY total_elapsed_time DESC
 

Get Query plan. Search in Plan cach

/* denne af mig hvor jeg får plan med JOM

Afvikel denne på SQL 2008 og få Query plan med

*/
SELECT qp.query_plan,qt.text,*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%ledgertrans%' -- Search text
ORDER BY qs.last_execution_time DESC
 

LIST SCHEMA WITH TABLENAMES
-- you can save a lot of code by using the catalog views
-- along with the OBJECTPROPERTY() function
Select
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name as [tables without primary keys]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY [tables without primary keys]
 

-- varision /Jom

Select
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name as [tables without primary keys]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 AND OBJECT_SCHEMA_NAME(t.object_ID) = 'EXTRACT'
ORDER BY [tables without primary keys]
 

-- varision /Jom
Select
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name as [tables without primary keys]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 AND OBJECT_SCHEMA_NAME(t.object_ID) = 'EXTRACT' and t.name like '%axapta%'
ORDER BY [tables without primary keys]

sp_configure

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
 

Get system date:

SELECT GETDATE()

Record count in table

Select A.name TableName , B.rowcnt  from sysobjects A, sysindexes B where A.id = b.id and A.type = 'u' and indid <2

order by b.rowcnt desc

Size of datafiles

-- Remove WHERE and get size of alle databases

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB

FROM sys.master_files

order by sizeMB desc

WHERE DB_NAME(database_id) = 'AdventureWorks'

GO

Bulk INSERT

USE PreProd
GO
CREATE TABLE JOMNotItem
(ID INT)
GO

select * from JOMNotItem

BULK
INSERT JOMNotItem
FROM 't:\notitems.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
 

In NotePad.

123

456

1

3

Different:

select * from sys.tables order by name
select * from sys.objects where type like 'U' order by name
select * from sys.data_spaces
 

--

-- Processor / % Processor Time _Total

--

SELECT  objectname,

        CounterName ,

        InstanceName ,

        MachineName,

        CounterDateTime ,

        ROUND(CounterValue,0) CounterValue,       

        DisplayString

FROM    dbo.CounterDetails cdt

        INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID

        INNER JOIN DisplayToID d ON d.GUID = cd.GUID

WHERE   MachineName = '\\MachineName'

        AND cdt.CounterName = '% Processor Time'

        AND cdt.InstanceName = '_Total'              

ORDER BY MACHINENAME, OBJECTNAME, COUNTERNAME, INSTANCENAME, CounterDateTime

--

-- With Time BETWEEN  / Processor / % Processor Time _Total

--

SELECT
  objectname,
  CounterName ,
  InstanceName ,
  MachineName,
  CounterDateTime ,
  ROUND(CounterValue,0) CounterValue,
  DisplayString
FROM dbo.CounterDetails cdt
  INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
  INNER JOIN DisplayToID d ON d.GUID = cd.GUID
WHERE MachineName = '\\MachineName'
  AND cdt.CounterName = '% Processor Time'
  AND cdt.InstanceName = '_Total'
  and counterdatetime between '2011-03-12 12:00:00' and '2011-03-12 13:00:00'
ORDER BY MACHINENAME, OBJECTNAME, COUNTERNAME, INSTANCENAME, CounterDateTime
 

Locked

Select * from sys.sysprocesses where blocked <> 0

Dbcc inputbuffer (SPID)

sp_who2

sp_lock2 (fungere ikke på SQL 2005, men sp_lock)

Space used in database

exec sp_spaceused

exec sp_spaceused batchhistory

Space used in logfile

dbcc sqlperf (logspace)

Database Name        Log Size (MB) Log Space Used (%) Status

master                    1,242188 41,19497           0

tempdb                    500,4922 7,204919           0

model                     100,9922 18,09778           0

msdb                      2,492188 40,43887           0

Database01                663,6797 2,847524           0

ReportServer              2147,992 1,155308           0

ReportServerTempDB        10,11719 24,63803           0

To get recovery model
 

select name, recovery_model_desc from sys.databases

To get idea log backup is taken for which databases

select database_name,max(backup_finish_Date) from msdb..backupset where
type = 'l'
group by database_name
 

Backup/Restore database

-- Create full backup with Copy Only option
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'
WITH COPY_ONLY
GO

Backup ONLY

backup database DUMMY

to disk = 'c:\DUMMYonlybackup.bak' with copy_only

/* What is the status on my Backup/Restore job? */

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

/* How to display AD group members using PowerShell on Windows server 2008 */
PS C:\xy\kk> get-adgroupmember "gg-ADgroupNameYouWantToDisplay" | format-table name

Using the REPLACE Option

SQL Server 2008 R2 http://msdn.microsoft.com/en-us/library/ms191315.aspx

 

Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database name differs from the database name recorded in the backup set, the database is not restored. This is an important safeguard.

Caution
REPLACE should be used rarely and only after careful consideration.

The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:

• Restoring over an existing database with a backup taken of another database.

  With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

• Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

  With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

• Overwriting existing files.

  For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

See Also

---

Reference

RESTORE (Transact-SQL)

How to insert data into function

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
select * from #FreeSpace

C 40003
K 104220
L 47878
M 36955
T 6878
 

Exampel how to use xp..cmdshell

/* Benyt dobuel coute alm gåseøjen I mellem ‘’ som nedenstående I SQL 2005 */

exec master..xp_cmdshell 'NET STOP "DYNAMICS AX OBJECT SERVER 5.0$01-DYNAMICSAX_TEST"'

exec master..xp_cmdshell 'NET START "DYNAMICS AX OBJECT SERVER 5.0$01-DYNAMICSAX_TEST"'

Shows open transaktion.

DBCC OPENTRAN

Information about columns in table/database

/* SQL 2005 ->*/

SELECT * FROM information_schema.columns where column_name like '%zip%'

SELECT NAME FROM sysobjects where name like 'dummyName'

/* How many columns do we have with the name? */

SELECT Column_Name, count(*) FROM INFORMATION_SCHEMA.COLUMNS

GROUP BY Column_Name

HAVING COLUMN_NAME LIKE '%zip%'

ORDER BY 2 DESC

Query data through a LinkedServer
 

SELECT b.name, a.type, MAX(a.backup_finish_date) lastbackup
FROM YourLinkedServerName.msdb.dbo.backupset a
INNER JOIN YourLinkedServerName.master.dbo.sysdatabases b ON a.database_name COLLATE DATABASE_DEFAULT = b.name COLLATE DATABASE_DEFAULT
GROUP BY b.name, a.type
ORDER BY b.name, a.type

Select name From YourLinkedServerName.[Database_Name].[dbo].[ADDRESS]
Select name From YourLinkedServerName.Database_Name.dbo.ADDRESS

Index fragmentering in the database

SELECT OBJECT_NAME(dt.object_id), si.name,

dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent

FROM

(SELECT object_id, index_id, avg_fragmentation_in_percent,avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats (db_id('dynamicsax_preprod'),NULL,NULL,NULL, 'DETAILED')

WHERE index_id <> 0) as dt --does not return inforamtion about heaps

INNER JOIN sys.indexes si

ON si.object_id = dt.object_id

AND si.index_id = dt.index_id

ORDER BY dt.avg_fragmentation_in_percent DESC

SELECT OBJECT_NAME(dt.object_id), si.name,

dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent

FROM

(SELECT object_id, index_id, avg_fragmentation_in_percent,avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats (db_id('dynamicsax_preprod'),NULL,NULL,NULL, 'DETAILED')

WHERE index_id <> 0) as dt --does not return inforamtion about heaps

INNER JOIN sys.indexes si

ON si.object_id = dt.object_id

AND si.index_id = dt.index_id

ORDER BY dt.avg_page_space_used_in_percent DESC

Backup information

SELECT Database_Name, Backup_Size, Type, Backup_Start_Date, backup_Finish_Date from MSDB..BackupSet

ORDER BY backup_Finish_Date Desc 

Scriptet code when deleting a database from GUI 2005

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DatabaseNameWichIsGoingToBeDeleted'

GO

USE [master]

GO

/****** Object:  Database [SletmigJom]    Script Date: 03/04/2010 11:59:52 ******/

DROP DATABASE [DatabaseNameWichIsGoingToBeDeleted]

GO

SP_Help

EXEC SP_HELP 'USER.TABLENAME'

IS [NOT] NULL (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188795.aspx

Determines whether a specified expression is NULL.
Transact-SQL Syntax Conventions
Syntax
--------------------------------------------------------------------------------
expression IS [ NOT ] NULL
Arguments
--------------------------------------------------------------------------------
expression
Is any valid expression.
NOT
Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL.
Result Types
--------------------------------------------------------------------------------
Boolean
Return Code Values
--------------------------------------------------------------------------------
If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.
If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE.
Remarks
--------------------------------------------------------------------------------
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.
Examples
--------------------------------------------------------------------------------
The following example returns the name and the weight for all products for which either the weight is less than 10 pounds or the color is unknown, or NULL.
Copy USE AdventureWorks2008R2;
GO
SELECT Name, Weight, Color
FROM Production.Product
WHERE Weight < 10.00 OR Color IS NULL
ORDER BY Name;
GO
 

Show database name for all databases on the Db instance.

SELECT name FROM master..sysdatabases

----SQL SERVER 2005 System Procedures

EXEC sp_databases

EXEC sp_helpdb

----SQL 2000 Method still works in SQL Server 2005

SELECT name

FROM sys.databases

SELECT name

FROM sys.sysdatabases

----SQL SERVER Un-Documented Procedure

EXEC sp_msForEachDB 'PRINT ''?'''

Absolutly genius!

Thursday, March 30, 2006
Remove Carriage Return, Line Feed, Tab in TSQL

-- remove carriage return, line feed, and tab from a field in tsql
REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Posted by David Seruyange at 12:59 PM
 

Important. If SQL 2000 displays an error when exporting data via export wizard, then put a field name after the function. As shown below.

SELECT  inventbatchid, convert(varchar(10),expdate,103)ExpDate, configid, itemid,  convert(varchar(10),proddate,103)ProdDate, [description], convert(varchar(10),AAX_LastSalesDate,103)AAX_LastSalesDate, dataareaid, recid

FROM INVENTBATCH WHERE dataareaid = ‘con’

How to copy data from one table to another table

From http://www.sqlservercentral.com/Forums/Topic503363-8-1.aspx

Hi,

I have two tables emp and empcheck1 in the same database emp consits 50 records and empcheck1 consists of 25 records.
Both the tables have the same design, here empid is the unique key.
I want to insert all the datas from emp1 to empcheck1 in such a way that it should not rewrite or duplicate the original 25 records
Whatever 25 records are there in empcheck1 already exists in emp
So i want the rest of the 25 odd records to be copied to empcheck1 from emp

Can anyone let me know how to do that
i tried out
insert into empcheck1 select * from emp where emp.empid<>empcheck1.empid
But it gave me an error

Try this
insert into empcheck1
select * from emp
where emp.empid not in(select empid from empcheck1)
- Zahran -
 

This is another way of the same workaround with better performance:
insert into empcheck1
select emp.*
from emp
left join empcheck1
on emp.empid=empcheck1.empid
where empcheck1.empid is null
 

Backup database using osql

BACKUP DATABASE DatabaseName TO DISK = 'C:\FileName.BAK'
GO
EXIT

Restore database using osql - (SQL login)

USE MASTER
GO
DROP DATABASE DatabaseName
GO
RESTORE DATABASE DatabaseName FROM DISK = 'C:\FileName.BAK'
GO
USE DatabaseName
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'SQLLoginName', 'SQLLoginName'
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'SQLLoginName', 'SQLLoginName'
GO
EXIT
GO
 

Using bcp - SQL 2005

How to script a cmd file for export of your database.

SELECT 'bcp YourDatabaseName..' + name + ' out .\' + name + ' -N -T -SYourDataBaseServer' FROM sysobjects WHERE xtype = 'U' ORDER BY NAME

SELECT 'bcp YourDatabaseName.SchemaName.' + name + ' out .\' + name + ' -N -T -SYourDatabaseServer' FROM sysobjects WHERE xtype = 'U' ORDER BY NAME

or if you only want tables with data in. But please be aware that data in your table will change, so be carefull not to be using an old statistics.

SELECT 'bcp YourDatabaseName..' + A.name + ' out .\' + A.name + ' -N -T -SNDSERP02' , B.ROWCNT
FROM sysobjects A, SYSINDEXES B
WHERE A.ID = B.ID AND A.TYPE = 'u' AND INDID < 2 AND B.ROWCNT > 0
ORDER BY B.ROWCNT DESC

Getting wiser, this is the apporach when you are dealing with native format

C:\>bcp dynamicsAx_Preprod..sysdatabaselog out .\sysdatabaselog.txt -n -T

or even wiser the approach when dealing with UniCode format will be

C:\>bcp dynamicsAx_Preprod..sysdatabaselog out .\sysdatabaselog.txt -N -T

Use the same technic when  importing. ('in' instead of 'out')

/*  This one is OK for getting data exportet. */

C:\>bcp dynamicsAx_Preprod..sysdatabaselog out c:\sysdatabaselog.txt -c -T

/* And this one is OK to import data, and don't ask me why */

C:\>bcp dynamicsAx_Preprod..jomsysdatabaselog in c:\sysdatabaselog.txt -c -T

/* But this one dosent work */

C:\>bcp dynamicsAx_Preprod..sysdatabaselog out c:\sysdatabaselog.txt -c -t, -T -S

Starting copy...

1000 rows successfully bulk-copied to host-file. Total received: 1000

1000 rows successfully bulk-copied to host-file. Total received: 2000

1000 rows successfully bulk-copied to host-file. Total received: 3000

1000 rows successfully bulk-copied to host-file. Total received: 4000

1000 rows successfully bulk-copied to host-file. Total received: 5000

1000 rows successfully bulk-copied to host-file. Total received: 6000

1000 rows successfully bulk-copied to host-file. Total received: 7000

1000 rows successfully bulk-copied to host-file. Total received: 8000

1000 rows successfully bulk-copied to host-file. Total received: 9000

1000 rows successfully bulk-copied to host-file. Total received: 10000

1000 rows successfully bulk-copied to host-file. Total received: 11000

1000 rows successfully bulk-copied to host-file. Total received: 12000

1000 rows successfully bulk-copied to host-file. Total received: 13000

1000 rows successfully bulk-copied to host-file. Total received: 14000

1000 rows successfully bulk-copied to host-file. Total received: 15000

1000 rows successfully bulk-copied to host-file. Total received: 16000

C:\>bcp dynamicsAx_Preprod..jomsysdatabaselog in c:\sysdatabaselog.txt -c -t, -T -S

Starting copy...

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22003, NativeError = 0

Error = [Microsoft][SQL Native Client]Numeric value out of range

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22003, NativeError = 0

Error = [Microsoft][SQL Native Client]Numeric value out of range

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

SQLState = 22018, NativeError = 0

Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

BCP copy in failed

/**/

bcp data from all tables in database
Author Nigel Rivett http://www.mindsdoor.net/SQLTsql/BCPAllTables.html

This will execute a bcp on all tables in the database

set nocount on

create table #a (name varchar(128), id int identity)

insert #a (name)  select name from sysobjects where xtype = 'U'

declare @id int, @cmd varchar(2000)

select @id = 0

while @id < (select max(id) from #a)

begin

        select @id = min(id) from #a where id > @id

        select @cmd = 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'

        from #a where id = @id

        exec master..xp_cmdshell @cmd

        select @cmd

end

drop table #a

This will give a resultset of the bcp commands which you can copy to a query window and execute.

        select 'exec master..xp_cmdshell ''bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'''

        from sysobjects where xtype = 'U'

This will give a resultset of the bcp commands which you can copy to a .bat file and run.

        select 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.bcp" -S' + @@servername + ' -N -T'

        from sysobjects where xtype = 'U'

For bcp in change out to in.

For identity columns

        select 'exec master..xp_cmdshell ''bcp ' + db_name() + '..' + o.name + ' in "c:\bcp\' + o.name + '.bcp" -S' + @@servername + ' -N -T'

               + case when c.id is null then '' else ' -E' end + ''''

        from sysobjects o 

               left join (select distinct id from syscolumns where status & 0x80 <> 0) c

                       on o.id = c.id

        where o.xtype = 'U'

I don’t have my GUI.

C:\> osql –Uusername –Ppassword –DodbcDSN –iC:\SQLcommands.sql

1>

Orphant users using SQL login 

/*

Fix orphan user from database

*/

USE DatabaseName

GO

sp_change_users_login 'update_one', username, username

GO

If you just want to check for orphant users 

sp_change_users_login report

Restore database SQL 2005

/* For at se det logiske navn */

RESTORE FILELISTONLY from DISK = '\\backup\xxx\Din_Backup_Fil_20121015_0943.bak'

/* For at restore */

restore database XXXDeleteMe

from disk = '\\backup\xxx\Din_Backup_Fil_20121015_0943.bak'

with

MOVE N'DetLogiskeFilNavn' TO N't:\xxx.mdf', 

MOVE N'DetLogiskeLogNavn_log' TO N't:\xxx_log_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 1

GO

RESTORE DATABASE [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022113.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022130.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022200.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022230.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022300.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007022330.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030000.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030030.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030100.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030130.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030200.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030230.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030300.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030330.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030400.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030430.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030500.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030530.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

GO

/* HER STOPPER VI PÅ MILISEKUNDET*/

RESTORE LOG [NewRestoreTest] FROM  DISK = N'M:\JOMTestAfBackup\DynamicsAx_Prod_backup_201007030600.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10,  STOPAT = N'2010-07-03T05:56:20'

GO

http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

/* Benyt dobuel coute alm gåseøjen I mellem ‘’ som nedenstående I SQL 2005 */

exec master..xp_cmdshell 'NET STOP "DYNAMICS AX OBJECT SERVER 5.0$01-DYNAMICSAX_TEST"'

exec master..xp_cmdshell 'NET START "DYNAMICS AX OBJECT SERVER 5.0$01-DYNAMICSAX_TEST"'

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Activity Monitor SQL 2005 symbols

The following icons and status values are possible:

Windows Server commands – Tricks etc.

For how long have my server been up and running?

C:\net statistics server

WSUS

Login WSUS

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

PUBLIC_VIEWS.vComputerTarget

Returns one row for each computer that connected to this WSUS server including computers that have been rolled-up from downstream servers. The values of ComputerTargetId are unique.

select * from PUBLIC_VIEWS.vComputerTarget

order by lastreportedstatusTime

/*   Dynamics Ax 2009 */

Usually an AOS is started as a Windows Service with a command line like this:

%ProgramFiles%\Microsoft Dynamics AX\50\Server\AX9\Bin\Ax32Serv.exe 01

where 01 is the name of the AOS instance. If you want to try out AOS command line parameters described above you can also run an AOS as a console application (not service) with a command line like this:

%ProgramFiles%\Microsoft Dynamics AX\50\Server\AX9\Bin\Ax32Serv.exe -console 01

Thus you can check out how all these command line parameters work without the need to alter a Windows Service settings. Note that you need to run an AOS under an account that is granted access to the AX database.

To trace SQL queries with placeholders you can also add these command line parameters:

-internal=comments -internal=nocursorreuse

In that case in SQL trace you will see real values passed for the placeholders in queries, like

WHERE A.DATAAREAID=:IN1/*'dat'*/

BI - Business Intelligence

• Measure or facts = sales, daily sales values, daily quantity.

• Dimensions = time, by day, by hour, location, city, country.

Great Links

SQLBackupRestore.com