SQL 2015 - Migrate to new SQL sevrer - Capture logins error - Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 232

Updated script available here

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2014/05/02/capture-logins-script-from-kb-878449-generates-invalid-object-name-error.aspx

Myridas Trinity GP 2015 - Could not find ODBC drivers

• Create 64-bit odbc entries for Dynamics GP 2015
• Run installer as administrator

SQL - Move database files to a new location

• Moving the TEMPDB
• http://www.sqlteam.com/article/moving-the-tempdb-database

USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf') GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf') GO

• Moving a regular DB
• http://codesnip.net/move-data-files-sql-server-mdf-ldf/
• Detach db
• Move files to new location
• Attach db

use master
go
sp_detach_db MYDB
use master
go
sp_attach_db MYDB, 'D:\data\mydb.mdb', 'E:\log\mydb.ldb'

SQL - Find Long-Running Queries

Read this post

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/


-----------------------------------------------------------------------------------------------------------------
Using SQL Profiler

1. Run SQL Profiler; searching for queries which have a high number of "Reads". This mostly indicates "Index Scans" which could cause the long runtime. Check the "Execution PLan" of those queries to optimize e.g. Indexes etc.. According to this you should check the Statistics and Fragmentation degree of that table and fix it if necessary

2. Checking for Blocks. I proceed as described here: http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

3. Check "Wait Statistics" to find out if theres an I/O problem, e.g. a problem with network or disk-subsystem. Therefore I look into the "sys.dm_os_wait_stats" DMV    

Original Post
http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx




Run this to see all running processes
exec sp_who2

Look for any task marked as runnable, with large numbers

run this to kill the process with SPID  58
kill 58

SQL Server - Queries, Views or Stored Procedures run slow after upgrade

Read this article

http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

DBCC CHECKDB WITH DATA_PURITY;

DBCC UPDATEUSAGE(db_name);
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

Dynamics NAV - Trying to post General Journal, but keep getting out of balance errors

Ensure that the journal lines are sorted correctly to allow the logic to check the balances correctly.
Or create separate journals for each unique posting date, or document number

Sort by

• Posting Date
• Document Number

If these are out of order, it will only calculate up to the first change of value.

SQL 2014 Linked Server - Create a linked server that points to a different sql server - alias

EXEC sp_addlinkedserver

@server=N'ALIAS',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'MYSQLSERVER';

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=ALIAS',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='password'



Replace

• ALIAS with the name you want for the Linked server connection
• MYSQLSERVER with the name of the SQL server\instance you're linking to
• sa with the user name on the linked server
• password with the password on the linked server