whateveris that



http://www.buyatt.qat/?cpid=1413

Force Hierarchy Permission in MDM 2012

In our organization we have been using MDM feature of MS SQL 2012, to maintain product data in central location. and all other system subscribe to this information.

This product (same entity) belongs to two different business unit with in our organization and we had to implement security model to achieve this.

In MDM 2012 there is out of box functionality for this. the closest feature that come close is Hierarchy segmentation.

Process starts with Declaring Explicit hierarchy for the entity in scope.

and than level entities are assigned to this Hierarchy, There is another way of creating hierarchy which is derived hierarchy and I am sure that would potentially work the same way.

once this assignment is completed next step is to assign permission on this Hierarchy rather than the entity itself.

In my experience I noticed that permission on entity does not readily get applied to leaf level. Sometimes there is small lag while other it does not even get applied at all.

The work around that to get implemented is running following code

DECLARE @Model_ID INT;

SELECT @Model_ID = ID FROM mdm.tblModel WHERE [Name] = N'ModelName';

EXEC [mdm].[udpSecurityMemberProcessRebuildModel] @Model_ID=@Model_ID, @ProcessNow=1;

GO

Upgrade MDM 2012 to SP1(SQL Server 2012 to SP1)

Effects on MDM while updating SQL 2012 to sp1.

Must: take back up of MDM Database:

Right after update MDM site will come down….

1.       You need to go to MDM configuration manager select MDM database again.

2.       Once you do so it will ask you to update the database and SP1 make some database changes.

3.       While doing so it will wipe of your custom Stored Proc so This is where having backup is big help.

SSIS: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

SSIS Error:
An error occurred in the requested FTP operation. Detailed error description: The password was not allowed:

Requirement: was to develop a SSIS package for reading MOST RECENT file from FTP server and load data to a Table in Database.

Solution consist of following steps:
1. Starting with script task which will read the file name listing from FTP server and identify latest file in the list of file names. (Script can be found at the end )
2. Check if file is already processed.
3. If it is new file use FTP task to move the file from FTP to local staging folder.
4. Move the existing data from destination table to a history table with the file name with the data.
5.Clear all the data from destination (an alternate approach have been to just to upsert to so that we don't have to truncate the whole table and just go with the update, in this scenario requirement was to reflect data from latest file only)

6. Load the data from this new file into destination table.

One of the challenge was to read FTP configuration only once and then use these values in script task as well as regular FTP task, It would have been easier to read it twice from configuration, but was not resulting in elegant solution and more over it could have been maintenance nightmare.

Approach was taken to create parameter  like FTP server name and FTP user name and  password and read  values from configuration only once.

upon further search found way to use these parameter to create connection string for FTP connetor for FTP task. (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3713e9a5-343a-4c5b-ac5d-1508cd5ab8be)

Connection string = @[User::FTPServer]+":21" + "." + @[User::FTPUser] + "." + @[User::FTPPass]

In my scenario since I was reading FTPServer value as = ftp://ftp1.servname.com

I had to use following expression
Connection string = REPLACE(@[User::FTPServer] ,"ftp://","")+":21"+"."+@[User::FTPUserName]+"."+ @[User::FTPPwd]

these program ran without any error in BIDS in debug mode, but when I deployed to prod server I started to get this error:

Error: 2013-05-07 16:06:15.06
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2013-05-07 16:06:21.56
   Code: 0xC001602A
   Source: pakage_name Connection manager "FTP Connection Manager"
   Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed.
End Error
Error: 2013-05-07 16:06:21.59
   Code: 0xC002918F
   Source: FTP Task Move the file to local folder FTP Task
   Description: Unable to connect to FTP server using "FTP Connection Manager".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

SOLUTION TIME:
Upon further search I found the solution (http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/912d43a5-975f-48b6-a9f1-660efeacc8ed/)


Job gets working by changing the ProtectionLevel on the FTP packages to EncryptSensitiveWithPassword.

:OM:

Following is code is used to read latest file name from FTP server:

        public void Main()        {                        string strFileNameLike= Dts.Variables["FileNameLike"].Value.ToString();            string strFTPServer= Dts.Variables["FTPServer"].Value.ToString();            string strFTPUserName= Dts.Variables["FTPUserName"].Value.ToString();            string strFTPPwd= Dts.Variables["FTPPwd"].Value.ToString();            string strFTPFolder= Dts.Variables["FTPFolder"].Value.ToString();            string strFTPFullPath=strFTPServer+"/"+strFTPFolder;               FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(strFTPFullPath);            request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;            request.Credentials = new NetworkCredential(strFTPUserName, strFTPPwd);            request.UsePassive = true;            request.UseBinary = true;            request.KeepAlive = false;             string[] list = null;            string[] listFilteredArray = null;             List<String> listFiltered = new List<string>();              using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())            using (StreamReader reader = new StreamReader(response.GetResponseStream()))            {                list = reader.ReadToEnd().Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);            }             string strStartsWith = strFileNameLike.Split(new string[] {"*"}, StringSplitOptions.RemoveEmptyEntries)[0].ToString();            string strEndsWith = strFileNameLike.Split(new string[] { "*" }, StringSplitOptions.RemoveEmptyEntries)[1].ToString();              foreach (string strname in list)            {
                if(strname.Substring(56, 25).StartsWith(strStartsWith) && strname.Substring(56, 25).EndsWith(strEndsWith) )                {                    listFiltered.Add(strname.Substring(56, 25));
                }         
            } 
            listFilteredArray= listFiltered.ToArray(); 
                        if (listFilteredArray.Length > 0)            {                Dts.Variables["FileName"].Value = listFilteredArray[listFilteredArray.Length - 1].ToString();            }
            Dts.TaskResult = (int)ScriptResults.Success;        }

SSIS: Value does not fall within the expected range

SSIS Error

[Execute SQL Task] Error: Executing the query "EXEC dbo.sp_name ?,?,? OUTPUT" failed with the following error:

 "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, 

"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When you are trying to execute a stored proc though execute SQL task:

you can call sp like 

            EXEC dbo.sp_name ?,?

when there is no out put parameter

and like 

           EXEC dbo.sp_name ?,?,? OUTPUT

where there is an output parameter.

If you try to assign parameter names like above in this case you will receive error that 

"Value does not fall within the expected range."

and it gives no clue whats so ever regarding the issue with the task

this can be fixed by replacing actual name of parameters in Parameter Name Column for Index location of the parameter that you have defined in your stored proc 

and  parameter mapping tab is going to look like and  Bingo your error is gone....

could not UPDATE table "[MSDASQL]"

--update any record

UPDATE ic SET

                        Display_Name__c =ISNULL(U.Display_Name__c ,'')

                        ,Display_order__C=ISNULL(U.Display_order__C ,0)

                        ,is_Active__c=U.IsActive__C

                        ,Name=ISNULL(U.Name ,'')

                        ,Parent__C=ISNULL(U.Parent__C,'')  

                        ,cost__C=ISNULL(U.cost__C,0)

                        

--DELETE FROM ic

--Select *

FROM

OPENQUERY(LINKEDSERVER,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__C

                        ,Name

                        ,Parent__C 

                        ,cost__C

                        

             from

                  item

                  ') ic

INNER JOIN #UPDATES U

 ON ic.id=U.Id

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "SetPos update return error".

Msg 7343, Level 16, State 4, Line 296

The OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" could not UPDATE table "[MSDASQL]".

And I get following error with as is_Active__C field in PostGres is boolean and OpenQuery unable to update , once remove this column and query works fine.

other alternative is delete this record and insert the whole record again.

DELETE Query:

DELETE FROM ic
--Select *FROMOPENQUERY(LINKEDSERVER,'Select
                        Id                        ,Display_Name__c                        ,Display_order__C                        ,is_Active__C                        ,Name                        ,Parent__C                         ,cost__C                                     from                  item                  ') ic
INNER JOIN #UPDATES U
 ON ic.id=U.Id

INSERT Query:

            INSERT INTO OPENQUERY(BUYBACK_STG,'Select

                        Id

                        ,Display_Name__c

                        ,Display_order__C

                        ,is_Active__c

                        ,Name

                        ,Parent__C 

                        ,cost__C

             from

                  item

                  ')

SELECT

                        Id

                        ,ISNULL(Display_Name__c ,'')

                        ,ISNULL(Display_order__C ,0)

                        ,IsActive__C

                        ,ISNULL(Name,'')

                        ,ISNULL(Parent__C,'')  

                        ,ISNULL(cost__C,0)

                        FROM #UPDATES 

conversion from Informatica to SSIS

While most Informatica components will directly port into SSIS, some will require 3rd party components. The Pragmatic Works Task Factory product for example gives SSIS more powerful tasks and data processing components. The below list shows a few of the common Informatica components and how they will port to SSIS. For a more complete list, please click here. Informatica SSIS Session Native Data Flow Command Task Factory Advanced Process Task Worklet Task Factory Advanced Execute Package Decision SSIS expressions Assignment SSIS expressions Database connectors Standard SSIS OLE DB \ ODBC \ ADO.net source and destinations Cloud connectors Many handled by Task Factory Aggregator Aggregate Transform Expression Task Factory Advanced Derived Column Transform Filter Task Factory Filter Transform Joiner Merge Join Lookup Lookup Transform (connected) or Task Factory Advanced Derived Column Transform (disconnected) Router Task Factory Advanced Conditional Split Sequence Generator Task Factory Surrogate Key Transform Sorter Sort Transform Update Strategy Task Factory Upsert Destination XML Creation Task Factory XML components Maplet Task Factory Data Flow Nugget

Source:http://www.pragmaticworks.com/services/informaticamigration

Indexes

States about SQL2012 release:

clustered Index per table 1
non clustered Index per table 999
xml Index per tabel 249
columns in normal table 1024
columns in wide table 30,000
rows in table : limited by memory
nested level in trigger 32
size of varchar 2^31-1
size of nvarchar 2^30-1
database size 524,272 terabytes
foriegn key per table 253
SQL instance per machine 50
partition per table/Index 1000 for 32 bit and 15000 for 64 bit
user connection 32767

If there are indexes like

Create Index IDX_Tab1_Col1Col2 on Table1 (Column1,Column2)

and if there is query using filters on both the column in a where clause, than the order of filter given does not matter.

but if there only one filer in where clause than it matter whether this filtered column is first or second in Index,

• If it is first column in index definition, query plan uses Index seek
• If it is second column in the index definition, query plan used Index scan which is more costly, hence performance is not the same.

also the Index seek operation will be applied on the Second column in Index when first column will is have equality operation(remember that order filter in where clause does not matter in this case)

 Where Column1=1 and Column2>1   (Index Seek on both the columns)
 Where Column1=1 and Column2=1   (Index Seek on both the columns)

 Where Column1>1 and Column2>1   (Index Seek on Column1 but Index Scan on Column2 )
 Where Column1>1 and Column2=1   (Index Seek on Column1 but Index Scan on Column2 )

Transaction Isolation Levels IN SQL Server 2012

SERIALIZABLE

·         Statements cannot read data that has been modified but not yet committed by other transactions.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

·         No Phantom reads

REPEATABLE READ

·         Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.(hence you can repeatedly read the same values)

·         Possibility for Phantom read

READ COMMITTED

·         Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction (not in any other transaction), resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

No other transactions can modify data that is being modified by the current transaction until the current transaction completes. but if it transaction just read the record  in its scan not modify, it open for action by other transactions. hence values of few records may change or new record inserted by other transactions when it is just being read by read commited transaction.
·         Possibility for Phantom reads

READ UNCOMMITTED

·         Specifies that statements can read rows that have been modified by other transactions but not yet committed.

·         Allow dirty read

SNAPSHOT

·         The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

·         The transaction can only recognize data modifications that were committed before the start of the transaction.

·         Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Usage of sp_executeSQL

Usage of sp_executeSQL

SET @sql = 'Select * into ' + @delim_delta_table + ' from ' + @table_server + '...'   

  + @delim_table_name + ' where ' + @timestamp_col_name + ' > @LastTimeIN'  + ' and '   

  + @timestamp_col_name + ' <= @TimeMax '   

 

     

    select @parmlist = '@LastTimeIN datetime, @TimeMax datetime'   

    exec sp_executesql @sql, @parmlist, @LastTimeIN=@last_time , @TimeMax=@time_max

DDL trigger

to keep the change log of Database activity for structure DDL trigger at Database level is used.

1. I ran across some permission issue: where  it is needed to make sure that owner of db where log table resided and source db should be same.
2. for user's activities to be logged he needs to be member of destination or EventLogDb. (Public Role is Ok)

trigger code:

CREATE TRIGGER [Backup_Objects]

on database

--WITH EXECUTE AS SELF

for

CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,

CREATE_TABLE, ALTER_TABLE, DROP_TABLE,

CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,

CREATE_ROLE,ALTER_ROLE,DROP_ROLE,

CREATE_SCHEMA,ALTER_SCHEMA,DROP_SCHEMA,

CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,

CREATE_VIEW,ALTER_VIEW,DROP_VIEW,

CREATE_USER,ALTER_USER,DROP_USER

as

--Created on Agt 11th 2011 by Abhishek Joshi

--refer:http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

SET NOCOUNT ON

declare @data xml

set @data = EVENTDATA()

insert into eventslogdb.dbo.changelog(databasename, eventtype,

    objectname, objecttype, sqlcommand, loginname)

values(

@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),

@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

INSERT INTO VS SELECT INTO PERFORMANCE

INSERT INTO VS SELECT INTO  PERFORMANCE DIFFERENCE


1.  SELECT INTO   hold lock on system table while it is creating table, while for  INSERT INTO there are no such lock as table is already there.  also these lock are hold until the query completes.
     
     these difference become very evident and affecting performance when query execution times are longer. and multiple users are trying to execute same piece of query as later request will have to wait for prior execution to finish before they can obtain lock on the resources again. there wait time can be avoided by creating table first and then inserting data into table that is by using INSERT INTO over SELECT INTO.
For instantaneous queries(small data) this will not be that much of problem.


2.  SELECT INTO is not a logged operation while  INSERT INTO  is

Get Top Biggest Table in SQL Database

CREATE TABLE #tmpTable

      (

      [name]      VARCHAR(255),

      [rows]      INT,

      [reserved] VARCHAR(100),

      [data]      VARCHAR(100),

      [index_size]      VARCHAR(100),

      [unused] VARCHAR(100)

      )

--_DEFAULT_VendorJournalAccts 4          16 KB 8 KB  8 KB  0 KB

EXEC sp_MSforeachtable @command1=" Insert into #tmpTable EXEC sp_spaceused '?'"

SELECT TOP 100 * FROM #tmpTable

ORDER BY Data DESC

DROP TABLE #tmpTable

Limitation and Capabilities of MS SQL Server

MS SQL SERVER 2008

Following are interesting facts about MS SQL SERVER

Object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Bytes per row8	8,060	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1,024	1,024
Columns per wide table	30,000	30,000
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions.	50 instances on a stand-alone server.
	SQL Server supports 25 instances on a failover cluster.	25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096	4,096
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	30,000	30,000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32,767	32,767
XML indexes	249	249

1Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

2The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

3Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process.

5This value is for static lock allocation. Dynamic locks are limited only by memory.

6If a stored procedure accesses more than 64 databases, or more than 2 databases in interleaving, you will receive an error.

7If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Index with Included Columns.

8SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online.

Source:
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx