The script below returns the create statements for all roles in a database. It also creates the statements that add the role members to the roles and assigns the role rights to the objects.
It can be filtered for one or some specific roles, e.g. the role "db_servicecockpit".
select sql from
(
-- 1) create role
select 1 as ord,
r.name as rolename,
concat('CREATE ROLE [',r.name,']') as sql
from sys.database_principals r
union all
-- 2) add members
select 2 as ord,
r.name as rolename,
concat('EXEC sp_AddRoleMember ''',r.name,''', ''',u.name,'''') as sql
from sys.database_principals u
join sys.database_role_members l on l.member_principal_id = u.principal_id
join sys.database_principals r on r.principal_id = l.role_principal_id
union all
-- 3) apply role rights to objects
select 3 as ord,
r.name as rolename,
'GRANT ' + p.permission_name + ' ON ' +
CASE p.class_desc
WHEN 'SCHEMA' THEN concat('[',schema_name(major_id),']')
WHEN 'OBJECT_OR_COLUMN' THEN
CASE
WHEN minor_id = 0 THEN concat('[',OBJECT_SCHEMA_NAME(major_id),'].[',object_name(major_id),']') COLLATE Latin1_General_CI_AS_KS_WS
ELSE
(
SELECT concat(object_name(object_id),' (',c.name,')')
FROM sys.columns c
WHERE o.object_id = p.major_id
AND c.column_id = p.minor_id
)
END
ELSE 'other'
END + ' TO [' + r.name + ']' as sql
from sys.database_permissions p
join sys.database_principals r on p.grantee_principal_id = r.principal_id
left join sys.objects o on o.object_id = p.major_id
where p.major_id > 0
) x
where rolename = 'db_servicecockpit'
order by ord
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Reporting Services in SQL Server 2016
Installing SQL Server 2016 CTP2 (Community Technology Preview) to test Reporting Services (SSRS) is a bit of a deception: the installer does not contain the developer tools! They (SSDT-BI or Report Builder) are also not yet available as a separate download as of June 10th 2015.
This fact is not communicated anywhere by Microsoft, although there is a mention by the SQL Server Team on June 1st 2015 in reaction to user Dave's remark concerning this:
Dave, agree completely, the tools upgrade is coming."
Source:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx
Or here by another user:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fa8133bd-5cfa-4add-aa20-4c5442760d65/download-ssdtbi-samples-for-sql-server-2016?forum=ssdt
This prevents users from testing e.g. the new configurable parameter box.
For those still interested, the CTP can be found here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
This fact is not communicated anywhere by Microsoft, although there is a mention by the SQL Server Team on June 1st 2015 in reaction to user Dave's remark concerning this:
Dave, agree completely, the tools upgrade is coming."
Source:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx
Or here by another user:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fa8133bd-5cfa-4add-aa20-4c5442760d65/download-ssdtbi-samples-for-sql-server-2016?forum=ssdt
This prevents users from testing e.g. the new configurable parameter box.
For those still interested, the CTP can be found here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
Analyzing string or binary data would be truncated error
Based on the ideas in this post, I've made an improved and dynamic version to help in determining the violating records when inserting into a table:
http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx
create procedure cst_checktruncatederror as
begin
set nocount on
declare @src nvarchar(max) = 'sourcetable'
declare @tgt nvarchar(max) = 'targettable'
declare @cols nvarchar(max)
declare @sql nvarchar(max)
-- create temporary empty version of destination table (target proxy)
-- note: use a global temp table, because a normal one goes out-of-scope when the dynamic sql returns
if (object_id('tempdb..##target') is not null) drop table ##target
set @sql = concat('SELECT * INTO ##target FROM ',@tgt,' where 1=0')
print @sql
exec sp_executesql @sql
-- get a comma-separated string with block-quoted columnnames that are comparable
set ansi_padding on
set @sql = concat('
select @colsOUT =
substring
(
(
select concat('',['',column_name,'']'')
from information_schema.columns
where table_name = ''',@tgt,'''
and data_type <> ''geography''
for xml path(''''), type
).value(''.'',''nvarchar(max)'')
,2,100000
)
')
print @sql
exec sp_executesql @sql, N'@colsOUT nvarchar(max) OUTPUT', @colsOUT=@cols OUTPUT
print @cols
-- load source to target proxy
set @sql = concat('
INSERT INTO ##target (',@cols,')
SELECT ',@cols,'
FROM ',@src,'
')
print @sql
SET ANSI_WARNINGS OFF -- truncates fields instead of throwing "string or binary data would be truncated error" error!
-- 1. do not use * to insert, because the column order might be off
-- 2. wrap fieldnames in blockquotes [] because reserved words might have been used, e.g. STATE or UNIQUE
-- 3. exclude data types: geography
-- 4. TO DO: destination columns might be NULLable, while the source is not. That results in a different error
exec sp_executesql @sql
SET ANSI_WARNINGS ON
-- return violating records
set @sql = concat('
SELECT ',@cols,'
FROM ',@src,'
except
SELECT ',@cols,'
FROM ##target
')
print @sql
exec sp_executesql @sql
-- clean up
if (object_id('tempdb..##target') is not null) drop table ##target
end
http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx
create procedure cst_checktruncatederror as
begin
set nocount on
declare @src nvarchar(max) = 'sourcetable'
declare @tgt nvarchar(max) = 'targettable'
declare @cols nvarchar(max)
declare @sql nvarchar(max)
-- create temporary empty version of destination table (target proxy)
-- note: use a global temp table, because a normal one goes out-of-scope when the dynamic sql returns
if (object_id('tempdb..##target') is not null) drop table ##target
set @sql = concat('SELECT * INTO ##target FROM ',@tgt,' where 1=0')
print @sql
exec sp_executesql @sql
-- get a comma-separated string with block-quoted columnnames that are comparable
set ansi_padding on
set @sql = concat('
select @colsOUT =
substring
(
(
select concat('',['',column_name,'']'')
from information_schema.columns
where table_name = ''',@tgt,'''
and data_type <> ''geography''
for xml path(''''), type
).value(''.'',''nvarchar(max)'')
,2,100000
)
')
print @sql
exec sp_executesql @sql, N'@colsOUT nvarchar(max) OUTPUT', @colsOUT=@cols OUTPUT
print @cols
-- load source to target proxy
set @sql = concat('
INSERT INTO ##target (',@cols,')
SELECT ',@cols,'
FROM ',@src,'
')
print @sql
SET ANSI_WARNINGS OFF -- truncates fields instead of throwing "string or binary data would be truncated error" error!
-- 1. do not use * to insert, because the column order might be off
-- 2. wrap fieldnames in blockquotes [] because reserved words might have been used, e.g. STATE or UNIQUE
-- 3. exclude data types: geography
-- 4. TO DO: destination columns might be NULLable, while the source is not. That results in a different error
exec sp_executesql @sql
SET ANSI_WARNINGS ON
-- return violating records
set @sql = concat('
SELECT ',@cols,'
FROM ',@src,'
except
SELECT ',@cols,'
FROM ##target
')
print @sql
exec sp_executesql @sql
-- clean up
if (object_id('tempdb..##target') is not null) drop table ##target
end
SQL Server contained database feature
SQL Server 2012 has a "contained database" feature.
In the normal case the logins of the (SQL Server) server are coupled to the users in each of its databases. The logins are stored in its MASTER database. If a database gets restored to another server, then the mapping is lost and you end up with "orphaned users". Those accounts can no longer access the database.
For a contained database on a SQL Server:
In the normal case the logins of the (SQL Server) server are coupled to the users in each of its databases. The logins are stored in its MASTER database. If a database gets restored to another server, then the mapping is lost and you end up with "orphaned users". Those accounts can no longer access the database.
For a contained database on a SQL Server:
- The server logins are not used/required. Users can be created and used without a matching login on the server. These users are called "portable users"
- The database handles the autorization instead of the server. This is called "contained database authentication". Activate this delegation of functionality on the server:
EXEC sp_configure 'contained database authentication',1 GO RECONFIGURE
In SSMS, a new option appears in the->Security->Users->New User... screen: "SQL User with password" - Works for SQL and Active Directory Accounts, e.g. statement for both without logins:
CREATE USER [testdomain\testuser] CREATE USER [biuser]
- Partial containment: in 2012 only the "partial containment" functionality works. In the future a "full containment" option if foreseen
- Connect from SSMS: you need to specify the database, otherwise the connection attempt fails, because the database is now doing the authentication
- Security: once a portable user gains access to a contained database through contained database authentication, that user also ends up gaining guest access to all other databases on the host system
- Conversion: conventional existing users can be converted into portable users through the use of a special stored procedure: sp_migrate_user_to_contained. It provides an argument that you can use to specify whether or not to disable the server-level login -- something you'll typically want to do as a best practice to avoid ugly login problems that can occur when duplicate logins and users overlap each other
- Downsides: no cross-database joins, linked server use, database mail, possible tempdb colation issues. Use "select * from sys.dm_db_uncontained_entities" to check containment readiness
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:
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:
- PRINT statements, like print 'lets start'
- informational RAISERROR events, like raiserror ('lets start', 0, 1)
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
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:
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:
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)
)
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
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 ".
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
IsNumeric() stinks
Read the thoroughly insightful and funny post by Mike Teevee as a reaction to the technet documentation with regards to the useless ISNUMERIC() function introduced in SQL Server 2012:
http://technet.microsoft.com/en-us/library/ms186272.aspx
Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.
http://technet.microsoft.com/en-us/library/ms186272.aspx
Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.
Silverlight not working because of font issue
If Silverlight is installed correctly, but still not working, check for Windows Fonts with an ampersand in the name and remove them:
http://connect.microsoft.com/VisualStudio/feedback/details/719317/silverlight-5-error-1001-font-filename-issue
http://connect.microsoft.com/VisualStudio/feedback/details/719317/silverlight-5-error-1001-font-filename-issue
Analysis Services fails with "attribute key cannot be found" on Unicode field
ERROR
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'city', Value: ' '. The attribute is 'City'.
OR
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'customername', Value: 'ﾊﾟﾅｿﾆｯｸ（æ ª）'. The attribute is 'Customername'.
CAUSE
The problem is caused by the Unicode nature of the SPACE character in the text field in the database. In the field are for example Kanji characters and unicode has about 18 characters for SPACE:
http://www.fileformat.info/info/unicode/category/Zs/list.htm
There is no way to tell Analysis Services or SQL Server the difference between these spaces. In short: collation, case sensitivity, accent sensitivity, width sensitivity or Kana sensitivity have no influence.
WORKAROUND
Convert all the kind of SPACE characters to one SPACE character, e.g:
replace(ad_.ad_name,' ',' ') as ad_name
This appears to do nothing, but the first empty space finds ALL space characters, e.g. ASCII 32 and Unicode 12288 and the second parameter converts it to ONE space (Unicode 32)
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'city', Value: ' '. The attribute is 'City'.
OR
The attribute key cannot be found when processing: Table: 'dbo_viu_isb', Column: 'customername', Value: 'ﾊﾟﾅｿﾆｯｸ（æ ª）'. The attribute is 'Customername'.
CAUSE
The problem is caused by the Unicode nature of the SPACE character in the text field in the database. In the field are for example Kanji characters and unicode has about 18 characters for SPACE:
http://www.fileformat.info/info/unicode/category/Zs/list.htm
There is no way to tell Analysis Services or SQL Server the difference between these spaces. In short: collation, case sensitivity, accent sensitivity, width sensitivity or Kana sensitivity have no influence.
WORKAROUND
Convert all the kind of SPACE characters to one SPACE character, e.g:
replace(ad_.ad_name,' ',' ') as ad_name
This appears to do nothing, but the first empty space finds ALL space characters, e.g. ASCII 32 and Unicode 12288 and the second parameter converts it to ONE space (Unicode 32)
Openrowset fails with "network access was interruped"
A SQL Server openrowset query using ODBC to retrieve data from an Excel spreadsheet fails with the error:
"Your network access was interrupted. To continue close the database and then open it again."
Example Openrowset query (Excel 2010+):
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=C:\temp\test.xlsx;Uid=;pwd=;','SELECT * FROM [memberlist$]')
Solution:
"Your network access was interrupted. To continue close the database and then open it again."
Example Openrowset query (Excel 2010+):
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=C:\temp\test.xlsx;Uid=;pwd=;','SELECT * FROM [memberlist$]')
Solution:
- Disable all network connections and try again. Only works if the frontend (e.g. Management Studio) and the backend (SQL Server database) are on the same machine
- Change SQL Server service user to an NT account
- In some cases, activating the network connections again will prevent the error from occurring
- In some cases, changing the NT account to the "local service" account will prevent the error from occurring
- The MSDASQL keyword indicates that openrowset will use the ODBC driver in stead of an OLE DB driver
Packing intervals
Solution to the "packing intervals" problem by Itzik Ben-Gan:
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
Scroll to code listing 4 for the SQL code.
Registration is required to copy and paste the code. It can only be copy and pasted from the PDF that can be downloaded after registration.
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
Scroll to code listing 4 for the SQL code.
Registration is required to copy and paste the code. It can only be copy and pasted from the PDF that can be downloaded after registration.
Execute query under cursor
There is no way in SQL Server Management Studio (SSMS) to execute the query under the cursor. There is the option to execute the entire script with F5 or CTRL+E, but the script might contain multiple SQL statements.
The quickest solution is to install a freeware add-in:
The quickest solution is to install a freeware add-in:
- Download dbForge SQL Complete:
Note: registration is required - Install the add-in
The option for auto-capitalization and intellisense is enabled by default. This can be disabled, so only the "execute query under the cursor" (CTRL+SHIFT+E) feature remains:
- Start SMSS
- SQL Complete -> Options -> General -> uncheck "Enable SQL Complete"
Count of weekend days in date range
Use the formula in "calc_name" or "calc_df" to calculate the number of inclusive weekend dates in a date range. Inclusive means that if e.g. the start date is a Saturday, it is counted as 1. The code is pure SQL Server T-SQL, with no need for helper tables or cursors.
Note:
SELECT
x.*,
(DATEDIFF(wk, sd, ed) * 2)
+(CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END) as calc_name,
DATEDIFF(wk, sd, ed) * 2
+CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END
+CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as calc_df,
@@DATEFIRST as datefirst,
(DATEDIFF(wk, sd, ed) * 2) as weeks,
CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END as sun_start,
CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END as sun_start_df,
CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END as sat_end,
CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as sat_end_df,
DATEPART(dw, sd)+@@datefirst as sun_df,
DATEPART(dw, ed)+@@datefirst as sat_df
from
(
select 1 as correct, '2013/3/17' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/17' as ed union
select 3 as correct, '2013/3/16' as sd, '2013/3/23' as ed union
select 3 as correct, '2013/3/10' as sd, '2013/3/22' as ed
) x
Note:
- "Set datefirst" is not needed, but can be used to check the correct outcome of the "calc_df" calculation
- The "calc_df" function is more robust, since it does not rely on localized day names. But it is a bit more verbose and obfuse then "calc_name"
- Column "correct" contains the correct number of weekend dates that can be used as a reference for the calculation
- Subquery X contains some random test values for start- and enddates
SELECT
x.*,
(DATEDIFF(wk, sd, ed) * 2)
+(CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END) as calc_name,
DATEDIFF(wk, sd, ed) * 2
+CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END
+CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as calc_df,
@@DATEFIRST as datefirst,
(DATEDIFF(wk, sd, ed) * 2) as weeks,
CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END as sun_start,
CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END as sun_start_df,
CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END as sat_end,
CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as sat_end_df,
DATEPART(dw, sd)+@@datefirst as sun_df,
DATEPART(dw, ed)+@@datefirst as sat_df
from
(
select 1 as correct, '2013/3/17' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/17' as ed union
select 3 as correct, '2013/3/16' as sd, '2013/3/23' as ed union
select 3 as correct, '2013/3/10' as sd, '2013/3/22' as ed
) x
Flash 11.4 in SRWare IronPortable v22
To get Flash support in the portable version of Iron v22:
- Start IronPortable and type "about:plugins" in the URL bar
- Scroll to the section titled "Flash"
- If there is one enabled item within that section, then Flash should already work. Test it here: http://www.adobe.com/software/flash/about/
- If the section does not exist or there are no items in it, then
- Create a folder called "iron\plugins" in the IronPortable folder
- Download the file NPSWF32_11_4_402_278.dll from the internet or copy it from the system Flash installation folder and put it in the previously created folder.
Example system Flash installation folder: C:\Windows\SysWOW64\Macromed\Flash\NPSWF32_11_4_402_287.dll - If there are more items, then
- Disable all items but one for Flash v11.4
- Restart iron and type "about:plugins"
- If all Flash items are disabled, then enable one Flash item
Note: putting file gcswf32.dll in the root of the IronPortable folder does no longer work!
MS Access DSN-less connection
It is possible to create a DSN less linked table and/or pass-through query from Microsoft Access to SQL Server Express without deleting those objects first, as is often suggested, e.g. here:
http://www.accessmvp.com/djsteele/DSNLessLinks.html
This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.
One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.
Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.
Use this code to loop over all tables and queries to set the connection string:
Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String
Set db = DBEngine(0)(0)
strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"
' reconnect pass-through queries
For Each qd In db.QueryDefs
With qd
If Left$(.Connect, 4) = "ODBC" Then
' note: for a passthrough query the uid and pwd are always stored
.Connect = strODBC
.Close
End If
End With
Next qd
' reconnect linked tables
For Each td In db.TableDefs
With td
If Left$(.Connect, 4) = "ODBC" Then
' note: for a linked table the uid and pwd are only stored when this property is set!
.Attributes = dbAttachSavePWD
.Connect = strODBC
.RefreshLink
End If
End With
Next td
db.Close
Set db = Nothing
Notes:
http://www.accessmvp.com/djsteele/DSNLessLinks.html
This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.
One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.
Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.
Use this code to loop over all tables and queries to set the connection string:
Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String
Set db = DBEngine(0)(0)
strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"
' reconnect pass-through queries
For Each qd In db.QueryDefs
With qd
If Left$(.Connect, 4) = "ODBC" Then
' note: for a passthrough query the uid and pwd are always stored
.Connect = strODBC
.Close
End If
End With
Next qd
' reconnect linked tables
For Each td In db.TableDefs
With td
If Left$(.Connect, 4) = "ODBC" Then
' note: for a linked table the uid and pwd are only stored when this property is set!
.Attributes = dbAttachSavePWD
.Connect = strODBC
.RefreshLink
End If
End With
Next td
db.Close
Set db = Nothing
Notes:
- Use DBEngine(0)(0) in stead of CurrentDB(); the implementation is 1000x faster
- Use Left$ in stead of Left; the implementation is faster
- This particular code assumes that DAO is used, not ADO
- The connection uses a SQL Server Authentication instead of Windows Authentication
- The connection string value is assigned to variable strODBC
- The connection string has to start with the text "ODBC;" both for the linked table as the pass-through query
- If the connection is to a default instance of SQL Server, the string "\SQLEXPRESS" can be removed
- By default, the username and password are stored for a pass-through query, but not for the linked table. This appears to be a bug, it either should be set for both or for neither. Setting the uid/pwd parameters for a linked table does, however, not result in an error
- ... but if the code td.Attributes = dbAttachSavePWD is used, then the uid/pwd are also stored for a linked table. At least for MS Access 2010 on Windows 7 x64, this is enough. In some cases two registry settings need to be set for this to work:
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
"DisablePwdCaching"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
"DisablePwdCaching"=dword:00000000 - After the code has run, the connection string has been set for all the objects, but is not yet effective! For this, MS Access needs to be closed and reopened
- I found that the reconnecting loop using DRIVER=SQL Server in stead of
DRIVER={SQL Server}or
PROVIDER={SQL Server Native Client 10.0 ODBC Driver}
was much faster - Close and destroy the reference to the database object to prevent memory leaks
Related errors might include:
"login failed. The login is from untrusted domain and cannot be used with windows authentication"
Oracle OLE DB source in SSIS
An OLE DB source to Oracle can be added to a SSIS package.Example data
Server: locksnlatches.mydomain.com
Oracle instance/SID: dwh
Username/schema: scott
Password: tiger
An OLE DB source to Oracle can be added as follows:
- From the menubar, select SSIS -> New connection... -> OLEDB -> Add -> New
Provider: Oracle Provider for OLE DB
Server or filename: locksnlatches/dwh
User name: scott
Password: tiger
Notes:
- The "Oracle Provider for OLE DB" provider always exists in SSIS. It is a wrapper around the Oracle Client driver that is installed when installing Integration Services. However, the Oracle Client has to be installed separately. It can be downloaded from the Oracle website: instantclient-basiclite-nt-11.2.0.2.0. Install the 32 bit version of the driver to be able to use it in the design environment. The Visual Studio / SSIS IDE does not support 64 bit components
- It is possible to use the 64 bit Oracle Client driver during execution of the package, e.g. via dtexec, but it needs to be installed additionally. The SSIS package has to be configured to use this different version during runtime.
- If the server cannot be found, then use the fully qualified name. One reason might be that the Oracle server is located in a different (Windows) domain as the SSIS server.
Reference SQL Server table with spaces from Oracle
It is possible to select data from a SQL Server table that contains spaces by using a database link from Oracle.
The syntax is as follows:
SELECT * FROM "DBO$TABLE WITH SPACES"@MYDBLINK
with
SQL Server login/user: dbo
SQL Server table name: table with spaces
DB link: mydblink
The syntax is as follows:
SELECT * FROM "DBO$TABLE WITH SPACES"@MYDBLINK
with
SQL Server login/user: dbo
SQL Server table name: table with spaces
DB link: mydblink
Subscribe to:
Posts (Atom)