SQL Linked Server to an External SQL Server

You can use the aliases in the SQL Configuration Manager to setup user-friendly SQL Server names

Provider: Sql server native client 10 or 11
Product Name: SQLSERVER
Data source: www.publicaddress.com\SQLSERVERNAME,port
example: test.mydomain.com\TEST,1433

Port 1433 has to be open through the public address, and forwarded to the sql server

Security: tick be made using this security context, enter remote sa password

Management Reporter - Cannot connect to server

http://support.microsoft.com/kb/2862020

Note:
The server and clients must all be on the same time zone with the same time (within 5 mins) or the client will not be able to connect to the server.

How to Install LS One

LSONE
LS One requires SQL server. The installs with "No SQL Server" do not include an sql server install.

1. Site Manager - Central data server that all clients will connect to
1. Connect to sql and create database (use shared memory, or enable tcp/ip and named pipes in sql  network config)
2. Register Site Manager
3. Login using admin:1234
1. Import default data
4. LS One site Service - Must be installed on all clients that will connect to the Site Manager
1. Setup site service profile>connection
2. USe full SQL server name as server host
3. Click Send configuration to send to POS
5. LS POS - Actual POS interface - enter store and terminal, caps matters

Remote Desktop - Network Printers do not appear when connecting to Remote Desktop

This was resolved by installing different printer drivers on the local machine for the network printer.

The funny thing was that we used an older driver, not a newer driver, and the printer redirected successfully.

Dynamics GP - Historical Stock Status - Divide by zero error

There are corrupted item transaction records for that date range.
Solution here

http://support.microsoft.com/kb/855945


In my case, there was a single record for 0 qty and no value in the TRXSORCE column.
There were some transactions with no UofM, but these were all cost adjustments, and had no impact on the report.


I deleted the single blank record from the iv30300, and the issue was resolved.

SQL - SSAS - Time dimension table and Auto-fill procedure. Auto Generate date and time index values.

CREATE TABLE [dbo].[DimTime](
[TransactionDate] [datetime] NOT NULL,
[DateText] [varchar](10) NULL,
[CalenderYear] [int] NULL,
[CalenderQuarter] [varchar](50) NULL,
[CalenderMonth] [varchar](50) NULL,
[CalenderMonthNumber] [int] NULL,
[CalenderWeek] [varchar](50) NULL,
[CalenderWeekNumber] [int] NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [varchar](50) NULL,
[FiscalPeriod] [varchar](50) NULL,
[FiscalPeriodNumber] [int] NULL,
[Day] [int] NULL,
[DayOfWeek] [tinyint] NULL,
 CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TransactionDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[buildDimTime]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @date datetime
declare @EndDate datetime
--set @date = '01/01/2006'
 set @date = DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-3, 0) --Only calculates last 3 years
set @EndDate = (DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 2, 0)))
delete DimTime
while @date <= @EndDate
begin
insert into dimTime
select @date as TransactionDate,convert(varchar(10),@date,111),
DatePart(year,@date) as Year,
cast(DatePart(year,@date) as varchar(4)) + ' ' + 'Q' + convert(CHAR,DatePart(quarter,@date)) as QuarterNumber,
cast(DatePart(year,@date) as varchar(4)) + ' ' + convert(CHAR,DateName(month,@date)) as MonthName,
DatePart(month,@date) as MonthNumber,
'Week' + convert(CHAR, DATEPART(wk, @date)) as CalenderWeek,
DATEPART(wk, @date) as CalenderWeekNumber,
--'Week' as WeekNumber,
--DateName(day,@date) as DayNumber,
DatePart(year,@date) as FiscalYear,
'Q' + convert(CHAR,DatePart(quarter,@date)) as FiscalQuarter,
convert(CHAR,DateName(month,@date)) as FiscalPeriod,
DatePart(month,@date) as FiscalPeriodNumber,
(select datediff(d, (select DATEADD(yy, DATEDIFF(yy,0,@date), 0)),@date)) as DayNumber,
DATEPART(dw, @date) as DayOfWeek
set @date = @date + 1
end
--update dimTime set DateText = convert(varchar(10),TransactionDate,111)
--from dimTime

END

Dynamics GP - SQL View - Field Service Invoices

SELECT        dbo.SVC00200.SRVTYPE, SVCHist.SRVRECTYPE, SVCHist.CALLNBR, SVCHist.SOPTYPE, SVCHist.SOPNUMBE, SVCHist.Invoiced_Amount, SVCHist.Amount_To_Invoice, SVCHist.CREATDDT, SVCHist.CREATETIME,
                         dbo.SVC00200.CUSTNMBR, dbo.SVC00200.CUSTNAME, dbo.SVC00200.PORDNMBR, dbo.SVC00200.Customer_Reference, dbo.SVC00200.COMPDTE, dbo.SVC00200.COMPTME, SOP.DOCDATE AS FinalDocDate,
                         SOP.DocAmt AS FinalDocAmt
FROM            (SELECT        SRVRECTYPE, CALLNBR, SOPTYPE, SOPNUMBE, Invoiced_Amount, Amount_To_Invoice, CREATDDT, CREATETIME, DEX_ROW_ID
                          FROM            dbo.SVC00220
                          UNION
                          SELECT        SRVRECTYPE, CALLNBR, SOPTYPE, SOPNUMBE, Invoiced_Amount, Amount_To_Invoice, CREATDDT, CREATETIME, DEX_ROW_ID
                          FROM            dbo.SVC30220) AS SVCHist INNER JOIN
                         dbo.SVC00200 ON SVCHist.CALLNBR = dbo.SVC00200.CALLNBR INNER JOIN
                             (SELECT        SOPTYPE, DOCID, SOPNUMBE, DOCDATE, CASE WHEN soptype = 4 THEN docamnt * - 1 ELSE docamnt END AS DocAmt
                               FROM            dbo.SOP10100
                               UNION
                               SELECT        SOPTYPE, DOCID, SOPNUMBE, DOCDATE, CASE WHEN soptype = 4 THEN docamnt * - 1 ELSE docamnt END AS DocAmt
                               FROM            dbo.SOP30200) AS SOP ON SVCHist.SOPNUMBE = SOP.SOPNUMBE