PowerPivot cannot load from procedure with info messages

Data can be loaded into PowerPivot from a SQL Server stored procedure, but the procedure cannot contain:

• PRINT statements, like print 'lets start'
• informational RAISERROR events, like raiserror ('lets start', 0, 1)

Both return messages to the client. Tools like Management Studio can handle these messages and display them in a separate window. PowerPivot handles them like the returned data and fails reporting an error. The error is not very helpful:

OLE DB or ODBC error: lets start; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

The only solution is to remove these instructions from the procedure and any procedures it might call. There is no way to suppress this on the OLEDB connection during the call to the procedure.

It is also advisable to have the following two instructions at the start of the procedure:

• set fmtonly off
• set nocount on

LISTAGG / implode solution for SQL Server

Group multiple records into one and concatenate the values of the string/text values of a field:

ALTER view [dbo].[vcomments] as
with x as
(
select
cmt_domain,
cmt_indx,
cmt_seq,
concat(cmt_cmmt1,cmt_cmmt2,cmt_cmmt3,cmt_cmmt4,cmt_cmmt5,cmt_cmmt6,cmt_cmmt7,cmt_cmmt8,cmt_cmmt9,cmt_cmmt10,cmt_cmmt11,cmt_cmmt12,cmt_cmmt13,cmt_cmmt14,cmt_cmmt15) as cmt_cmmt,
cmt_lang
from
v_cmt_det_act
)
select
cmt_domain,
cmt_indx,
--FOR XML PATH returns a one record XML datatype in which all records are "unioned".
-- However, special characters are encoded as excape sequences, i.e. "<" becomes "<"
-- To reverse the encoding, get the entire root document/element as a value using the VALUE function.
-- The [1] is required because VALUE expects a singleton, i.e. exactly ONE element
rtrim(substring(
(
select char(10) + '[PAGE ' + cast(x2.cmt_seq as nvarchar(2)) + ']' + char(10) + x2.cmt_cmmt -- a field with no name gets no XML element to enclosed it. In short, the XML tag is removed
from x x2
where x1.cmt_domain = x2.cmt_domain and x1.cmt_indx = x2.cmt_indx
order by x2.cmt_seq
for XML PATH (''), type -- the brackets indicate the XML root element. Specifying an empty string as root element name removes it
).value('(/)[1]','nvarchar(max)')
,2,100000)) as cmt_cmmt
from
(
select distinct cmt_domain, cmt_indx
from x
) x1

Query Active Directory from SQL Server

Get active directory server and LDAP servers in a domain
nltest /dclist:sub.dom.com

results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server

Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1

Users in a group from command prompt
net group adtestgroup /domain

Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"

LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup

The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.

Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):

exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'

Select users in group "adtestgroup":

select * from openquery(ADSI,'
    SELECT objectCategory, cn, sn, mail, name, department, company
    FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
    WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    ORDER BY cn
')

Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:

alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
    declare @sql as nvarchar(max) = '
    select samaccountname as accountname, name, mail, department, title, company
    from openquery(ADSI,''
        SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
        FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
        WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
    x
    order by samaccountname'

    exec(@sql)
end

Select all BI_* and EDW_* groups from active directory:

select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
    SELECT cn, distinguishedname, whencreated, whenchanged
    FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    where CN=''bi_*'' or  CN=''edw_*''
') x
order by cn

Notes:

• In most cases, all domain users can query AD using the basic search method specified before. More advanced AD search methods might be disabled and require special rights

Create schema with separate autorization

Perform the following steps in SQL Server to create a schema called "bi" and give create view rights to user "domain\testuser":

CREATE SCHEMA [bi]
CREATE ROLE [db_bischema]
ALTER AUTHORIZATION ON SCHEMA::bi TO [db_bischema]
GRANT CREATE VIEW TO [db_bischema] -- "create view" includes the power to drop views
ALTER ROLE [db_bischema] ADD MEMBER [domain\testuser]

Advantages:

• If views are removed from or added to the schema, autorization does not need to be added to each individual view
• Give certain users the rights to (re)create the views inside the schema, without the risk of modification of the rest of the database

Pass table-valued parameter from VB.Net

1) Create table type itemtabletype for a table with one, unique clustered column called "item":

create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)

2) Create a procedure cst_testtabletype that has itemtable type as a table-valued input parameter:

create procedure cst_testtabletype

( @tv itemtabletype readonly )

as

begin

declare @itemcount int = 0

select @itemcount = count(*) from @tv

select concat('succesfully parsed item ',item) from @tv

end

The procedure returns all items provided in the table-valued parameter prefixed with the words "successfully parsed item ".

3) Give execution rights to user(s) and/or groups that are allowed to execute the procedure and table type:

grant exec on type::itemtabletype to [User]
grant exec on cst_testtabletype to [User]

4) Finally, create the following test routine  in VB.Net:

    Public Sub Start()

        'create data table
        Dim Table1 As DataTable

        'create a table named tmptbl
        Table1 = New DataTable("tmptbl")

        Dim row As DataRow

        Try

            'declare a column named item
            Dim item As DataColumn = New DataColumn("item")

            'setting the datatype for the column
            item.DataType = System.Type.GetType("System.String")

            'adding the column to table
            Table1.Columns.Add(item)

            'declaring a new row
            Dim i As Integer
            For i = 1 To 1000
                row = Table1.NewRow()
                row.Item("item") = i
                Table1.Rows.Add(row)
            Next

        Catch

        End Try

        'establishing connection. you need to provide password for SQL server
        Dim myConnection = New SqlConnection()

        myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"

        Try

            myConnection.Open()

            Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)

            ' the table-valued parameter is called @tv for cst_testtabletype
            Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
            'create parameter
            myCommand.CommandType = CommandType.StoredProcedure
            myparam.Value = Table1

            Dim dr As SqlDataReader = myCommand.ExecuteReader()
            While dr.Read()
                Debug.Print(dr.Item(0))
            End While

        Catch ex As SqlException

            MsgBox(ex.Message)

        End Try

    End Sub