Recent Changes

Thursday, December 6

  1. page Lookup Table SQL edited ... SQL DECLARE @PricingTable int = (select id from PricingTable where PricingTableName = 'Test')…
    ...
    SQL
    DECLARE @PricingTable int = (select id from PricingTable where PricingTableName = 'Test')
    IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL drop table #temp;
    IF OBJECT_ID('tempdb.dbo.#columns', 'U') IS NOT NULL drop table #columns;

    DECLARE @XMLData XML = (select TableElementStr from PricingTable where ID = @PricingTable )
    DECLARE @RowVariable varchar(50) = (select v.VariableName from PricingTable pt join variable v on v.id = pt.RowParam1ID where pt.id = @PricingTable)
    ...
    SELECT * INTO #Temp FROM (
    SELECT
    ...
    = Node.Data.value('(Row1ParamValue)[1]', 'INT')'varchar(200)')
    , ColParamValue = ISNULL(Node.Data.value('(ColParamValue)[1]', 'VARCHAR(200)'),'Value')
    , ElementValue = ISNULL(CAST(Node.Data.value('(ElementValue)[1]', 'VARCHAR(100)') as decimal(10,4)),0)
    (view changes)
    10:46 am
  2. page Lookup Table SQL edited ... Row and Column Titles will be shown in the first result set. You will need to replace the tab…
    ...
    Row and Column Titles will be shown in the first result set.
    You will need to replace the table name on line 1 of the query with the Lookup Table name in Control.
    Not compatible with SQL Server version 2008 or prior.
    Risk of Data Corruption if Run Improperly
    None. This is a selection query and no data is modified in the running of it.
    (view changes)
    10:00 am
  3. page Lookup Table SQL edited ... SET @statement = 'select v1.VariableName as [Row Parameter], ISNULL(v2.VariableName, ''Value''…
    ...
    SET @statement = 'select v1.VariableName as [Row Parameter], ISNULL(v2.VariableName, ''Value'') as [Column Parameter]
    from PricingTable pt
    ...
    = pt.RowParam1ID
    LEFT

    LEFT
    OUTER join
    ...
    = pt.ColParamID
    where

    where
    pt.id = '
    SET @statement += (select cast(@PricingTable as nvarchar(50)))
    SET @statement += '; '
    ...
    BEGIN
    SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
    ...
    = 1
    SET

    SET
    @Column =
    ...
    as int))
    ELSE
    SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
    (view changes)
    9:49 am
  4. page Lookup Table SQL edited Lookup Table SQL Explanation of SQL This query retrieves the XML for a Lookup Table and displa…

    Lookup Table SQL
    Explanation of SQL
    This query retrieves the XML for a Lookup Table and displays the results in table format.
    Notes about SQL
    Row and Column Titles will be shown in the first result set.
    You will need to replace the table name on line 1 of the query with the Lookup Table name in Control.
    Risk of Data Corruption if Run Improperly
    None. This is a selection query and no data is modified in the running of it.
    SQL
    DECLARE @PricingTable int = (select id from PricingTable where PricingTableName = 'Test')
    DECLARE @XMLData XML = (select TableElementStr from PricingTable where ID = @PricingTable )
    DECLARE @RowVariable varchar(50) = (select v.VariableName from PricingTable pt join variable v on v.id = pt.RowParam1ID where pt.id = @PricingTable)
    DECLARE @ColVariable varchar(50) = (select v.VariableName from PricingTable pt join variable v on v.id = pt.ColParamID where pt.id = @PricingTable)
    SELECT * INTO #Temp FROM (
    SELECT
    Row1ParamValue = Node.Data.value('(Row1ParamValue)[1]', 'INT')
    , ColParamValue = ISNULL(Node.Data.value('(ColParamValue)[1]', 'VARCHAR(200)'),'Value')
    , ElementValue = ISNULL(CAST(Node.Data.value('(ElementValue)[1]', 'VARCHAR(100)') as decimal(10,4)),0)
    FROM @XMLData.nodes('/TableElements/TableElement') Node(Data)
    ) T
    SELECT distinct ColParamValue
    INTO #Columns
    FROM #Temp
    ORDER BY ColParamValue DESC
    DECLARE @Column varchar(max) = NULL
    DECLARE @Columns nvarchar(max) = ''
    DECLARE @statement nvarchar(max)
    DECLARE @i int = 0;
    DECLARE @count int
    SET @count = (SELECT count(distinct ColParamValue) FROM #Temp)
    SET @statement = 'select v1.VariableName as [Row Parameter], ISNULL(v2.VariableName, ''Value'') as [Column Parameter]
    from PricingTable pt
    join variable v1 on v1.id = pt.RowParam1ID
    LEFT OUTER join variable v2 on v2.id = pt.ColParamID
    where pt.id = '
    SET @statement += (select cast(@PricingTable as nvarchar(50)))
    SET @statement += '; '
    SET @statement += 'SELECT Row1ParamValue,'
    DECLARE @ColumnTable Table (col nvarchar(100))
    WHILE (@i < @count)
    BEGIN
    SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
    IF ISNUMERIC(@Column) = 1
    SET @Column = (SELECT top 1 CAST(ColParamValue as int) FROM #Columns Order By CAST(ColParamValue as int))
    ELSE
    SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
    IF @i = 0
    SET @Columns = '[' + @Column + ']'
    ELSE
    SET @Columns += ',' + '[' + @Column + ']'
    DELETE FROM #Columns WHERE ColParamValue = @Column
    SET @i += 1
    END
    SET @statement += @Columns
    SET @statement += ' FROM
    (SELECT Row1ParamValue, ElementValue, ColParamValue
    FROM #Temp) AS SourceTable
    PIVOT
    ( max(ElementValue)
    FOR ColParamValue in ('
    SET @statement += @Columns
    SET @statement += ')
    ) as Test Order by Row1ParamValue'
    EXEC sp_executesql @statement
    DROP TABLE #Columns
    DROP TABLE #Temp
    Version Information
    Entered : 12/6/2018
    Version : Control 6.0+
    Related SQLs
    Backlinks

    (view changes)
    9:48 am

Wednesday, November 28

  1. page Setup Control to Use Replicated Database to Provide Data for Reports edited ... Troubleshooting Error: SQL Server is unable to connect to server 'SERVER\Instance' {2015-05…
    ...
    Troubleshooting
    Error: SQL Server is unable to connect to server 'SERVER\Instance'
    {2015-05-01 19_54_14-winxp-vm - Remote Desktop Connection.png}
    You may receive this error when configuring the Distribution agent, and the "actual server name" at the bottom of the error does not match the server name mentioned at the top of the error message (see example below).
    Resolution:Resolution 1:
    Execute the following in SQL:
    select name from sys.servers
    Open a CMD prompt type HOSTNMAME and press Enter.
    Compare the two results. SQL Should be updated if they are different
    Use the following SQL to change the Name in sys.servers:
    -- replace sql instance in quotes with the Name in sys.servers
    exec sp_dropserver 'CYRIOUS46\CYRIOUS'
    -- replace the sql instance in quotes with correct server name and instance name.
    exec sp_addserver 'CYRIOUS\CYRIOUS'
    Re-start SQL, Cyrious Services, SSLIP, etc.
    Resolution 2:

    Un-install SQL Server.
    Re-install SQL Server.
    {2015-05-01 19_54_14-winxp-vm - Remote Desktop Connection.png}
    Pending Tracer
    If the tracer from the Publisher to Distributor finishes in seconds but says Pending for Distributor to Subscriber, check permissions on the Distributor Agent.
    (view changes)
    1:03 pm
  2. msg Is there a way to move a contact from one company to another? message posted Is there a way to move a contact from one company to another? Is there a way to move a contact from one company to another?
    Is there a way to move a contact from one company to another?
    Is there a way to move a contact from one company to another?
    11:45 am

Tuesday, November 27

  1. page How To Use The Same Table For Different Variables edited ... You can not use Local Variables for the Table Row Number Expression. You can use formulas wit…
    ...
    You can not use Local Variables for the Table Row Number Expression.
    You can use formulas with existing variables. For example, LookupTableValue("VinylCutTimes",CHARCOUNT(Text)) did return the correct value for the number of letters in the Text variable.
    ...
    for columns namenamed "P" or
    ...
    - E.g. "T""T", but did
    Steps
    Create/Identify the varibles to capture the letter height and number of letters for each size.
    (view changes)
    11:11 pm
  2. page How To Use The Same Table For Different Variables edited ... You can not use Local Variables for the Table Row Number Expression. You can use formulas wit…
    ...
    You can not use Local Variables for the Table Row Number Expression.
    You can use formulas with existing variables. For example, LookupTableValue("VinylCutTimes",CHARCOUNT(Text)) did return the correct value for the number of letters in the Text variable.
    ...
    that in 6/0+6.0+ you need
    Steps
    Create/Identify the varibles to capture the letter height and number of letters for each size.
    (view changes)
    11:10 pm

More