SQL 2008 Change "Edit top 200 rows" to Open Table or Edit All

In SQL Server Management Console

Tools > Options > SQL Server Object Explorer > Commands

Change the value in options to 0 for any of the commands and you can open the entire table when you select them from the right-click menu.

Run Batch File from SQL or SQL from Batch File

Batch File from SQL
------------------------------------------------------------------------------
Enable xp_cmdshell
Option is in surface area manager, or run script

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Use the xp_cmdshell command
xp_cmdshell '"C:\dbs\IM.bat"'




SQL from batch file

Pre-Requisites:
SQL Management Studio must be installed on the machine you are trying to execute the batch file from.
OR
Install the command line utilities only if you do not want to install Management Studio.
http://www.microsoft.com/en-gb/download/details.aspx?id=36433
-------------------------------------------------------------------
sqlcmd -S localhost\dbinstance -U sa -P password -i "C:\Files\CreateDB.sql"

Dynamics GP - Remove ALL Session and Record Locks (Your previous transaction-level posting session has not finished process. Please allow time for it to finish.)

Also resolves issue:
ERROR: Your previous transaction-level posting session has not finished process. Please allow time for it to finish.
After running the script, check the dynamics..sy00500 table for the offending batch, and delete it.



Will clear out locks for anyone who is not logged in

delete from DYNAMICS..ACTIVITY where USERID not in (select loginame from master..sysprocesses)delete from tempdb..DEX_SESSION where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800 where USERID not in (select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
----------------------------------------------------------------------------------------
--View locks with this

select * from DYNAMICS..ACTIVITY
select * from DYNAMICS..SY00800
select * from DYNAMICS..SY00801
select *  from tempdb..DEX_SESSION
select * from tempdb..DEX_LOCK


--------------------------------------------------------------------------------------
--This script will clear out ALL locks and disconnect all users.
--Do not use this if people are actually logged into the system doing work.


delete from DYNAMICS..ACTIVITY
delete from tempdb..DEX_SESSION
delete from tempdb..DEX_LOCK
delete from DYNAMICS..SY00800
delete from DYNAMICS..SY00801

Dynamics GP - SQL Trigger - Create a trigger to update RM00101 with last payment date from RM00103

USE [AW]
GO

/****** Object:  Trigger [LastPmtDate]    Script Date: 11/14/2011 14:59:33 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_LastPmtDate]'))
DROP TRIGGER [dbo].[trg_LastPmtDate]
GO

USE [AW]
GO

/****** Object:  Trigger [dbo].[LastPmtDate]    Script Date: 11/14/2011 14:59:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_LastPmtDate]
   ON  [dbo].[RM00103]
   AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

update RM00101 set RM00101.CCRDXPDT = inserted.LASTPYDT
from INSERTED
where RM00101.CUSTNMBR = inserted.custnmbr


END

GO

price group header not found for item class group

The company has not been correctly converted from Standard pricing to Extended pricing.

1) Go to Tools>Setup>Sales>Extended Pricing
2) Enable Standard pricing
3) Logout, Login
4) Enable Extended Pricing
5) Logout, Login

Dynamics GP Restore a copy of live data into a test company

1) Backup live
2) Create test company
3) Restore over test
4) Run this

/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
  create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables

declare @cStatement varchar(255)

declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
  then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
  'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
  and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
   insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
   Exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

select [tableName] as 'Tables that were Updated' from ##updatedTables

5) Then run this

sp_changedbowner 'DYNSA'

--------------------------------------------------------------------------------

View
KB871973
for more information

SSAS How to calculate Variance, YTD, Prior Year in MDX

To use any of this code, go into your cube, click on Calculations Tab, then Script View, and just paste it in there. Or you can build it the long way through the form
 view.


SIMPLE VARIANCE
Example: Difference of Actual sales (A Value) from Budgeted Sales (B Value)
You run into problem with zero values on the Budget side if you're dividing by the budget to get your variance percentage.
--------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[B Var Value PC]
 AS case

when [Measures].[B Value] = 0
and [Measures].[A Value] > 0
then 1

when [Measures].[B Value] = 0
and [Measures].[A Value] < 0
then -1

when [Measures].[B Value] = 0
and [Measures].[A Value] = 0
then 0

else [Measures].[B Var Value]/[Measures].[B Value] end,
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [A Value] },
VISIBLE = 1;
--------------------------------------------------------------------------------

YTD CALCULATIONS
YTD Calculations require that your time periods are set up properly first.
Ensure that your Time dimension has a hierarchy for at least
Year>Month
You must also define your attribute relationships
Key>Month>Year

Go to Time dimension>Attribute Relationships>Right click Year Attribute>Properties
Ensure Type is set to Years
This defines the year level for this time hierarchy.

Now your YTD function will work without any errors
The YTD will represent all periods within the yr-month hierarchy up to the currentmember
The SUM will keep adding across each period to get a running total.

-----------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A YTD Case]
 AS sum(ytd([View B Time].[yr-month].currentmember),[Measures].[A Value]),
VISIBLE = 1;
-----------------------------------------------------------------------------

PRIOR YEAR YTD
To calculate a prior Year YTD uses almost the same formula.
Instead of using the currentmember, we have to use the PARALLELPERIOD of the currentmember one year ago.
PARALLELPERIOD(Year Set Containing your year numbers, 1 year ago, Month set containing the months.the currentmember year)

-------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A PYYTD Case]
 AS sum(YTD(
PARALLELPERIOD(
[View B Time].[Yr-Month].[Year Num],
1,
[View B Time].[Yr-Month].currentmember
)
),
[Measures].[A Value]
),
VISIBLE = 1  ;
---------------------------------------------------------------------------------