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

 
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 noteCaution
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.

 

 

 

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:\> osqlUusernamePpasswordDodbcDSNiC:\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"'

 

 

SQL Server 2005: Get full information about transaction locks

Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...

With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:

 

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

 

Let's look at it one DMV at a time from top to bottom:

sys.dm_tran_locks:

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.

sys.partitions:

Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID'  and 'Key'. With this join we get the object_id of our locked table.

sys.objects:

Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.

sys.dm_exec_sessions:

Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.

sys.dm_tran_session_transactions:

Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.

sys.dm_tran_active_transactions:

Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.

sys.dm_exec_connections:

Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.

sys.dm_exec_sql_text:

Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.

 

By applying the filter in the where clause you get the answers to questions like:

- What SQL Statement is causing the lock?

- Which user has executed the SQL statement that's holding the locks?

- What objects/tables are being locked?

- What kinds of locks are being held and on which pages, keys, RID's?

- etc...

Activity Monitor SQL 2005 symbols

The following icons and status values are possible:

Icon

Term

Definition

Running

running

The process is currently performing work.

Runnable

runnable

The process has a connection and has successfully run in the past. It currently has no work to perform.

Sleeping

sleeping

The process has work to perform, but is waiting for something, such as a lock or user input.

Background

background

A background process that wakes up periodically to execute work.

Suspended

suspended

The process has work to perform but has been stopped. The status field does not contain the reason the process was suspended. The Wait Type field may contain information about why the process is suspended.

Other

other

Process is not in one of the other statuses.

 

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

 

 

Great Links

SQLBackupRestore.com