Saturday, April 13, 2013

[T-SQL] Odd behaviour from sys.dependencies

[T-SQL] Odd behaviour from sys.dependencies

Odd behaviour from sys.dependencies

Posted: 03 Apr 2013 08:46 PM PDT

(SQL Server 2008 R2 SP1)Hi all, I have an SSIS package that dynamically builds a set of SQL statements based on the dependencies in a set of procs. The SSIS package makes use of the dm function [url=http://msdn.microsoft.com/en-us/library/bb677185(v=sql.105).aspx]sys.dm_sql_referenced_entities[/url] to build up a distinct list of columns required from the source system. The code is run against every proc in the database into a working table, then a distinct list is used to pick up data from the source system.We've been testing this for weeks and weeks in UAT, the day we're going live, UAT has fallen over with a really odd issue :crazy:. Basically, very randomly, the values in the ImportColumn are presented with some characters returned with unexpected characters, only ever the first 4 digits, it can be different rows from the resultset and just to make it more complicated how often it returns is completely random. As the SSIS package iterates through each table, I've lifted one of the scripts it builds to explain, this is extracting dependencies from a proc called staging.uspInstrumentHolding.[code="sql"]SELECT DISTINCT Ref.referenced_database_name AS ImportDatabase ,Ref.referenced_schema_name AS ImportViewSchemaName ,SUBSTRING(Ref.referenced_entity_name, (CHARINDEX('_', Ref.referenced_entity_name) + 1), LEN(Ref.referenced_entity_name)) AS TableName ,SUBSTRING(Ref.referenced_entity_name, 1, (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName ,Ref.referenced_minor_name AS ImportColumnFROM sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding', 'OBJECT') AS RefWHERE 1 = 1 AND Ref.referenced_minor_name IS NOT NULL AND ref.referenced_schema_name = 'import'[/code]The values that return in a strange state are only from the ImportColumn. I can run the script 7-8 times accurately, then the results will appear like this:[code="plain"]UNPOSTEDꀨ⃗가⃗RLꀨ⃗가⃗ANPCCVRT䀨噠RE䀨噠RT䀨噠EF䀨啨UV䀨啨RT䀨䒰TION䀨䒰VT䀨䒰EF䀨䒰LT䀨䎰RL䀨䎰CTION䒰忰AL䀨亰UP䶸忰Fꀨ⃗ꗐ⃗TEꀨ⃗ꗐ⃗Tꓘ⃗뿰⃗TL[/code]Next time I run them, they look like this:[code="plain"]UNPOSTEDUNSERLUNTRANPCCVRTPCDIREPCINRTPCIREFPCORUVPCSRRTPDACTIONPDINVTPDIREFPDMULTPDSERLPDUACTIONPDUVALRPCOUPRPREFRBDATERBRATRBTITL[/code]As you can see, only SOME of the values have returned in this state (and which values are returned in this state changes), it is only ever the first 4 characters and how often it occurs changes.It's just so random, we're yet to identify a trend or reason for this, can anyone offer any suggestions?ThanksJJ

Help comparing a data set within a table.

Posted: 12 Apr 2013 08:22 AM PDT

The first data set contains a schedule with ten dates and I would like to pull the ScheduleID for data that matches these dates exactly. The First data set is know and the second data set is stored in a table. I have thought of using INTERSECT however this returns true when the dates match and there are additional dates attached to the ScheduleID. I am looking to only return if all dates for a ScheduleID match exactly. I am looking for a scalable solution, I do not want to set a president with my developers by running a cursor!With this example the result set would be:ScheduleID114201222---------------------------------------ScheduleID EndDate103 2009-01-03 00:00:00.000103 2009-01-17 00:00:00.000103 2009-01-31 00:00:00.000103 2009-02-14 00:00:00.000103 2009-02-28 00:00:00.000103 2009-03-14 00:00:00.000103 2009-03-28 00:00:00.000103 2009-04-11 00:00:00.000103 2009-04-25 00:00:00.000103 2009-05-09 00:00:00.000--------------------------------------ScheduleTableScheduleID EndDate114 2009-01-03 00:00:00.000114 2009-01-17 00:00:00.000114 2009-01-31 00:00:00.000114 2009-02-14 00:00:00.000114 2009-02-28 00:00:00.000114 2009-03-14 00:00:00.000114 2009-03-28 00:00:00.000114 2009-04-11 00:00:00.000114 2009-04-25 00:00:00.000114 2009-05-09 00:00:00.000201 2009-01-03 00:00:00.000201 2009-01-17 00:00:00.000201 2009-01-31 00:00:00.000201 2009-02-14 00:00:00.000201 2009-02-28 00:00:00.000201 2009-03-14 00:00:00.000201 2009-03-28 00:00:00.000201 2009-04-11 00:00:00.000201 2009-04-25 00:00:00.000201 2009-05-09 00:00:00.000222 2009-01-03 00:00:00.000222 2009-01-17 00:00:00.000222 2009-01-31 00:00:00.000222 2009-02-14 00:00:00.000222 2009-02-28 00:00:00.000222 2009-03-14 00:00:00.000222 2009-03-28 00:00:00.000222 2009-04-11 00:00:00.000222 2009-04-25 00:00:00.000222 2009-05-09 00:00:00.000

something like PIVOT?

Posted: 12 Apr 2013 07:52 AM PDT

I have a table that stores a tablename, the last time the table was accessed and when that data was put into the table:CREATE TABLE [dbo].[LastSelectCheck]( [TableName] [nvarchar](128) NULL, [LastSelect] [datetime] NULL, [RunDate] [datetime] NOT NULL, [Server] [nvarchar](128) NULL) ON [PRIMARY]GOIt's populated every night by a select from sys.dm_db_index_usage_stats.My question: I'd like to run a query that shows the last select over a week's time, like this:[code="other"]Tablename day1 day2 day3--------------------------------------------------------------------------------tblOrder 2013-04-08 NULL 2013-04-10tblOrder2 2013-04-08 2013-04-09 NULL[/code]I realize I can do this thru a series of CTE, temp tables or joins but it seems that there's something more elegant out there. I tried PIVOT but it doesn't look like it'll work here as I'm not aggregating and the columns are not items from the tables.Any advice? TIA.Sample data:[code="other"]insert into LastSelectCheck2 values ('tblOrder', NULL, '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-08', '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', '2013-04-08', '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder', '2013-04-10', '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-10', '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', NULL, '2013-04-10', 'MyServer1')[/code]

Trigger that collects data from two tables

Posted: 12 Apr 2013 08:35 AM PDT

Hi,Is it possible to create an After Update trigger on table A that can gather both the delete and insert data for the After Update from columns A.1, A.2, A.3 when A.3 is updated to NULL. But I need that plus the delete and insert data from columns B.1 and B.2 from table B as well when column A.3 is updated and set to NULL? I have the trigger below that will get me what I need from table A but is it possible to also get the data from table B from this trigger? In other words, I'm trying to figure out how to populate the Table B OldWorkFlowStepId and NewWorkFlowStepid columns that are created in the CONTAINER_DEBUG table and declared as variables. The OldWorkFlowStepId value needs to be taken when the delete.CurrentStatusId is taken from the first table and the NewWorkflowStepId value needs to be taken when the insert.CurrentStatusId is taken from the first table. How would I go about that? I am trying to troubleshoot a column that is getting set to NULL when it shouldn't and I'm trying to trap exactly where in the workflow this is happening.[code="sql"]DROP TABLE CONTAINER_DEBUGGOCREATE TABLE CONTAINER_DEBUG( ContainerId CHAR(16) ,ContainerName VARCHAR(256) ,OldCurrentStatusId CHAR(16) ,NewCurrentStatusId CHAR(16) ,OldLastCompletedTaskId CHAR(16) ,NewLastCompletedTaskId CHAR(16) ,OldWorkflowStepId CHAR(16) ,NewWorkflowStepId CHAR(16) ,LastActivityDate DATETIME )GODROP TRIGGER CONTAINER_TRG_BUGOCREATE TRIGGER CONTAINER_TRG_BUON CONTAINERAFTER UPDATE AS IF ( UPDATE (CurrentStatusId) )BEGIN -- DECLARE @OldCurrentStatusId CHAR(16); DECLARE @NewCurrentStatusId CHAR(16); DECLARE @OldLastCompletedTaskId CHAR(16); DECLARE @NewLastCompletedTaskId CHAR(16); DECLARE @OldWorkflowStepId CHAR(16); DECLARE @NewWorkflowStepId CHAR(16); -- SELECT @OldCurrentStatusId FROM deleted; -- SELECT @NewCurrentStatusId FROM inserted; -- IF ( ISNULL(@OldCurrentStatusId,'XXX') <> 'XXX' AND ISNULL(@NewCurrentStatusId,'XXX') = 'XXX' ) -- BEGIN -- INSERT INTO CONTAINER_DEBUG ( ContainerId ,ContainerName ,OldCurrentStatusId ,NewCurrentStatusId ,LastActivityDate ) SELECT i.ContainerId ,i.ContainerName ,d.CurrentStatusId ,i.CurrentStatusId ,i.LastActivityDate FROM Inserted i INNER JOIN Deleted d ON i.ContainerId = d.ContainerId -- RAISERROR ('CurrentStatusId is set to NULL - Aborting...', 16, 10); -- END; --END;GO[/code]

Any ideas on how to accomplish this one?

Posted: 12 Apr 2013 06:44 AM PDT

I've got data that can be similar for a specific account, yet different (one of those "it depends")I need to be able to extract the single account record with all of the distinct fuel types associated with it. In the case they are duplicated, I don't need the duplicated fuel type. In cases where the fuel types are different I need to be able to include it into the same stringConsider:[quote][b]MerchNum [/b]| [b]FuelType[/b]00000026104 Diesel;Gas;Other - Non Fuel;00000026104 Diesel0000000500444 Gas0000000500444 Biodiesel[/quote]What I need to see is:[quote][b]MerchNum [/b]| [b]FuelType[/b]00000026104 Diesel;Gas;Other - Non Fuel;0000000500444 Gas;Biodiesel[/quote]Here is some test data:[code="sql"]CREATE TABLE #Data (AcctNum varchar(20), FuelType varchar(128))INSERT INTO #Data VALUES ('00000026104', 'Diesel;Gas;Other - Non Fuel;')INSERT INTO #Data VALUES ('00000026104', 'Diesel')INSERT INTO #Data VALUES ('0000000500444', 'Gas')INSERT INTO #Data VALUES ('0000000500444', 'Biodiesel')[/code]Bearing in mind the TSQL for this may have to parse thousands of records, does anyone have an idea how I would go about getting the result?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog