How To Identify An Array
I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.
CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)
I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.
I am now looking at a table that is similar in nature - but different.
CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]
The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.
I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.
I will appreciate your input.
View Complete Forum Thread with Replies
Related Forum Messages:
How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
View Replies !
Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array: Public GroupIRRArray(-1) As Double Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal) Dim g As Integer g = uBound(GroupIRRArray) + 1 ReDim Preserve GroupIRRArray(g) if g=0 then GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1 else if g=1 then GroupIRRArray(g) = (BAB-CFB-EAB+EMV) else GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV) GroupIRRArray(g) = (BAB-CFB-EAB+EMV) end if End Function It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array? Thanks, Jam
View Replies !
How To Identify Hot Tables
Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
View Replies !
Identify Neg And Pos Values
How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit l've written it in layman's terms .Is it wise to use substring or they is a better method...... select case when amount is (negative) then 'C' when amount is (positive) then 'D' else end from table_Tran
View Replies !
SHOWCONTIG - Need A Bit Of Help To Identify If
Greetings. I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched. However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) ) Tipps, suggestions, help, all is very wellcome! :-) DBCC SHOWCONTIG scanning 'TInsurance' table... Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17 TABLE level scan performed. - Pages Scanned................................: 1275 - Extents Scanned..............................: 225 - Extent Switches..............................: 224 - Avg. Pages per Extent........................: 5.7 - Scan Density [Best Count:Actual Count].......: 71.11% [160:225] - Extent Scan Fragmentation ...................: 74.67% - Avg. Bytes Free per Page.....................: 520.0 - Avg. Page Density (full).....................: 93.58% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View Replies !
Identify Value In A Column Nullified
hi, I have a huge db with many services ,users and applications hitting the db. Suddenly one of our column is nullified , we are not able track who /how it is done, Can any one tell be whatz the best way to identify this???? trace(what events to select ), trigger or what???? Thanks, srini
View Replies !
Using A Variable To Identify A Column Name
I am trying to update a table, where the column name is made up of a character and a number. i.e Table column name = W9501. declare @abc char(5) select @abc = 'W9501' Update Table set @abc = 1 where row = something The code fails to update the table, yet if I replace @abc with 'W9501' the table is updated. Why?
View Replies !
Union All - Identify Input
Hello, I have a Union All component with 7 inputs (and it will grow to 13 inputs shortly). Is there any way to correlate each of the €œUnion All Input X€? with actual inputs? When I get an incompatible datatype message for one of the input, how do I know which input stream to pick from? Is this a silly question or it€™s a feature request? Kapil Kelaiya
View Replies !
How To Identify A Dialog As Encrypted?
I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption. How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?
View Replies !
Identify Queue That Activated Sp ?
Hi there I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario). My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate. Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue. I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me. How do i determine within an activated sp which queue caused it to activate? Thanx
View Replies !
How To Identify What Consumes Cpu In Sql Server
Hi all I use 64 bit 2005 server with 8cpu and 8G of memory. This server is accessed by large number of intensive or not so intensive programs. I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good. But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it. If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good. Task manager shows that all of it 75% attributed to sql server. So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves. Thank you, Gene.
View Replies !
Need To Identify Column In Resultset
Okay, I now have some dynamic SQL working. This is the SQL statement I have for a report in Reporting Services: DECLARE @SQL nvarchar(4000) SET @SQL=(SELECT AdHocSQL FROM RptValueTypeMap WHERE RptValueTypeMap.SectionCd in ('ITEM0010')) EXECUTE (@SQL) We have a table set up that actually holds different SQL statements based on the report items. This is reading the SQL statement from AdHocSQL for the Report item #0010 and it is returning the results. However, it does return the correct value, but under (No Column Name). I have tried to incorporate an "AS", but I get errors when I try this. I am familiar, but new to SQL statements and I would like this to return a field so I can use this value in the report. What do I need to do? Thanks for the information.
View Replies !
How To Format Identify Column In SQL Server?
I don't wnat SQL's Identify column's format ( which is 1�2�3 ...)I want my Prikey column is looks like starts in 0000000001�0000000002�0000000003....I set the Prikey columns type is Char(10) not nullis it possible to setting my identify column as I want?
View Replies !
How To Identify Fragmented Index For Rebuild
Hi,I want to find out what is a good way to identify indexes that needrebuilding. I tried to run DBCC showcontig and identify them based onthe scan density %. And according to a Microsoft Tech Net - All AboutSQL Server Indexes discussion, the expert stated that based on theDensity (below 20%), I can pick out those indexes associated to thetable and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX torebuild all indexes associated to the specific table. The strange partis that some tables, which have 20% or below scan density do not haveany indexes. So, I am confused!Thanks in advance.
View Replies !
How Identify Locking Store Procedure
:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database Thanks for your help Alfredo
View Replies !
Remove Identify Property From A Column
Hi, I want to know how to remove identify property from a column without recreating the whole table... When I do it in Enterprise Manager, it actually drop and recreate the table in background. I just like to know if there is other way without recreating the tables. Thanks! Xiao Tan
View Replies !
Identify Update Columns In Triggers
Hi all, I've been making use of SQL7's new capabilities for tables with lots of columns and in recent development it turns out one of my tables has 376!! I'd like to implement an Audit database which copies all changes within the update trigger to a mirrored table, however as well as copying the changed row, I'd like to record which specific columns were changed. I've pondered over writing the script for each "if update(column)" but consider this untidy. I'd like to use one cursor and loop around each column in the table, something I've tried with the "if column_updated" statement. This works well and pulls out only the updated columns, however only up to column 31, then an arithmetic overflow occurs, due to the bitmask testing of power(2,31)! So my question, does anyone have any other way of testing for a specific column update, without physical referencing the column name? Many thanks, Stephen Kemsley.
View Replies !
Use Replication To Identify Updated Data?
I am new to SSIS (though have a decent amount of experience with SQL Server 2000), and am trying to design a data warehouse and eventually a comprehensive reporting solution. Here is our setup: - We have ~150 studios all operating software which we designed (ie. we have control over the system that will be the Data Warehouse source). - All studios use merge replication back to Head Office (we maintain a duplicate of each of their databases). From my limited understanding of Data Warehousing methodology, I believe that I should prepare a 'staging area' which contains all needed data from these databases. My question is how to do this in an efficient manner (without resorting to software-maintained timestamps in all the source tables - this would require significant modification to our software). Since replication is already identifying which rows are new or updated, I'm wondering if there is some way to use this information to limit the information processed during the Data Warehouse updating process. I realize that I would still need to determine whether the row was updated or new, but it would cut down immensely on the number of rows processed. I feel like I could be missing out on a simple way to do my data extraction from the source DBs. Does anyone have any advice? Thanks in advance for your help.
View Replies !
Is There A Way To Identify The Subtotal Field In A Matrix?
What I need to do is to calculate the Average time in the subtotals of a matrix containing max-time values. Amount last sold at Item A 5 18:00 Item B 10 19:00 Total 15 18:30 In my understandig I have to create a function that counts all max-time values and calculates their average. The return value would depend on the filed-type. If it´s a normal field, the max-time value would be returned, else the calculated average. My first problem is to figure out, if the actual filed is a normal or a subtotal field. Does anybody know a way to solve that? Regards LG
View Replies !
How To Identify All Parameter Values Selected
I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?
View Replies !
Need To Identify Student With Multiple Id Numbers
My working table: CREATE TABLE [dbo].[Name_ID4]( [id_num] [decimal](18, 0) IDENTITY(1,1) NOT NULL, [student_last_name] [varchar](30) NULL, [student_first_name] [varchar](30) NULL, [student_middle_init] [varchar](1) NULL [local_student_id] [bigint] NULL, I need to identify only the students with more than one local_student_id and the associated local_student_id's. I can identify the students with two id's but not the associated id's SELECT COUNT(*) AS Dup_Num, rtrim(student_last_name), rtrim(student_first_name), rtrim(student_middle_init), from Name_ID4 group by student_last_name, student_first_name, student_middle_init, having (count(*) > 1) order by student_last_name, student_first_name, student_middle_init Thanks
View Replies !
How Do I Identify The Base Table For A PageID?
Hi, Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either. Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???
View Replies !
How Can I Search A Schema To Identify Table
I am trying to find a needle in a haystack. One of the tables in this database has a column named column_xyz. There are over 800 tables, each with numerous columns. If I know that the column I am looking for is "column_xyz" is there an easy query I can do to identify all tables that contain that column? I hope. Thanks
View Replies !
How To Identify The Base Table For A PageID ?
Hi, Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either. Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???
View Replies !
Identify Change Points In A Column
DECLARE @EffLevels TABLE (ChangePoint int, Value Int) INSERT@EffLevels SELECT'1000', '767' UNION ALL--Changed SELECT'1000', '675' UNION ALL SELECT'1001', '600' UNION ALL--Changed SELECT'1001', '545' UNION ALL SELECT'1001', '765' UNION ALL SELECT'1000', '673' UNION ALL--Changed SELECT'1002', '343' UNION ALL--Changed SELECT'1002', '413' UNION ALL SELECT'1002', '334' UNION ALL SELECT'1001', '823'--Changed -- My Result should be -- ChangePointPrevChangePointValue -- 1000Null767 -- 1001 1000 675 -- 1000 1001 765 -- 1002 1000 343 -- 1001 1002 823 Any suggestion ?
View Replies !
How Do I Identify Column Name When Error Occurs?
Hey all I am importing data from a CSV file into an SQL table. All the successful rows get inserted into that table and any error rows get redirected to the derived column data flow transformation tool which then puts the errors into an .xls file. All this works fine but the error file doesn't have the error description and row identifier. How do I insert the row identifier (say PK is PersonNo and PersonNo 58's details are incorrect) and the error description? I tried the errorcolumn but that gave a random number 96 when the error was in record 58. I hope I'm making sense. Any help will be highly appreciated.. Thanks Rupa
View Replies !
|
TRACKER
