Unlimited Attributes On A Table
Nov 23, 2006
Bit of a design question as I'm interested to know if anyone's done anythign like this...
This is my main table (ish)
Thing(ThingId, Ref)
I then need to be able to give this "Thing" any number of attributes.
Thing1 - Type:Red, Location:London
Thing2 - Type:Blue, Height:400, Width: 300
Thing3 - Height:500, Location:Norwich
But I have no idea how to model this in the database - it needs to be in such a way that I can add a Thing and all its attributes in one database hit basically (is there a stored procedure you could pass an array into?)
My initial thoughts were to have
Thing(ThingId, Ref)
Attribute(AttributeId, ThingId*, AttributeTypeId*, Value)
AttributeType(AttributeTypeId, Description)
Is that completely mad? It seems like quite a lot of data accesses to enter a Thing
It could be Thing(ThingId, Ref, Type, Location, Height, Width) but then when "Thing - Color:White" comes along the model is stuffed
Any ideas? (hope that makes sense)
View 3 Replies
ADVERTISEMENT
Feb 21, 2008
When trying to process my minding model (using Association Rules) i receive the following errors
Errors and Warnings from Response
Error (Data mining): The 'Items Recommendations' mining model has 64675 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.
Errors related to feature availability and configuration: The 'Unlimited data mining attributes.' feature is not included in the 'Standard Edition' SKU.
I searched through the internet and found that a hotfix can be installed to make it work (http://support.microsoft.com/kb/932609). I emailed microsoft and they provided me back with the hotfix and a password.
I tried to install the fix but i couldnt. It needed a prerequisit fix. So i installed SQL Server 2005 Service Pack 2 from (http://support.microsoft.com/?kbid=921896)
Then tried to install the fix for my unlimited attributes problem.
I tried to process my module after installing the fix and restarting my computer, but am still gettin the same error.I created another new model but the problem still exists.
Am i missing something here? Any idea to make it work?
Is there any alternative? I need to use all the elements in my datasource view.
View 3 Replies
View Related
Jan 24, 2014
Reformatting data in a PIVOT Table or find a better way to display.
--ORDERDETAIL TABLE
SKUOÂ Â QTYOÂ Â Â Â ORDERIDO
KUMÂ Â Â Â 1Â Â 12345
KUSÂ Â Â Â 2Â Â 12345
SUKÂ Â Â Â 1Â Â 12345
KHNÂ Â Â Â 4Â Â 12345
DREÂ Â Â Â 1Â Â 12345
[Code] ....
Number of SKU's in order could be over 1000.
Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.
Data I am looking to get.  MAX of 15 SKUS Per line.
ORDERIDÂ Â Â Â SKU1Â Â Â Â QTY1Â Â Â Â SKU2Â Â Â Â QTY2Â Â Â Â SKU3Â Â Â Â QTY3Â Â Â Â SKU4Â Â Â Â QTY4Â Â Â Â SKU5Â Â Â Â QTY5Â Â Â Â SKU6Â Â Â Â QTY6Â Â Â Â SKU7Â Â Â Â QTY7Â Â Â Â SKU8Â Â Â Â
QTY8Â Â Â Â SKU9Â Â Â Â QTY9Â Â Â Â SKU10Â Â QTY10Â Â SKU11Â Â QTY11Â Â SKU12Â Â QTY12Â Â SKU13Â Â QTY13Â Â SKU14Â Â QTY14Â Â SKU15Â Â QTY15Â Â
12345Â Â KUM 1Â Â KUS 2Â Â SUK 1Â Â KHN 4Â Â DRE 1Â Â HGF 2Â Â FDE 1Â Â CDS 1Â Â GYT 1Â Â POI 3Â Â LKH 2Â Â TTT 4Â Â JHG 8Â Â YUI 2Â Â WQE 1Â Â
12345Â Â PMN 1Â Â BVC 1Â Â ABD 1Â Â
[Code] ....
CURRENT PIVOT ONLY GOES TO 150 - BELOW
SELECTÂ Â Â Â PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
                      [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
[Code] ....
View 6 Replies
View Related
Jun 30, 2015
I am working in SQL Server Master Data Services  Version 11.0.5058.0 (SP 2).
I have been asked to group all the financial attributes together. Â When I move one of the attributes up using the arrows, it works good jumping over one attribute at a time. Â Then I reach a section of attributes where it leap frogs over 24 attributes.
It appears these 24 attributes are in a subgroup but there are no attribute groups and I removed the subscription view from the entity. Â If I move one of the 24 attributes in the group, it moves it outside of the 24 attributes.
This is under leaf member attributes. Â There are no collection or consolidated groups.
View 2 Replies
View Related
May 24, 2015
I'm using a DW from Northwind database to build a cube to do some analitical taks. I already create the cube and now I am "cleaning" the dimensions. I'm having some difficults to understand the logical off this part. The reason is that When I create the Data Source View, I only import the Foreign Keys that connect the Dimensions to Fact_Table. I have to drag the attributes of Dimension from Data Source View to the tab attributes?Â
Imagine this:
I have the following dimension:
Dim_Customer:
Customer_ID
Name_Customer
Job_Function
Date_of_Birth
Contact
Address
City
Country
When I create the cube only Customer_ID appears in attributes tab, it's normal?Â
One more question:
I don't want to create a hierarchy like:
Customer ID -> Name_Customer
Customer ID -> Date_of_Birth
Customer ID -> Address
Customer ID -> City
Customer ID -> Country
My idea is to create the following hierarchy:Â
Name_Customer ->Â Date_of_Birth ->Â Â Address ->Â Â City ->Â Country
But the first hierarchy that I show is always appears to me. Do you know what is happens?
View 2 Replies
View Related
Nov 11, 2004
Hello all-
I have a specification table that has some attributes defined.
SpecId - Id of the specification
Attribute - Attribute of the spec. (Like Color, HP etc)
Value - Is the value of the attribute
Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.
The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"
Any help is greatly appreciated.
DECLARE @Specification TABLE
(SpecId VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
DECLARE @Car TABLE
(CarName VARCHAR(10),
AttributeVARCHAR(100),
ValueVARCHAR(100))
INSERT INTO @Specification VALUES ('S1', 'Type', 'Sedan')
INSERT INTO @Specification VALUES ('S1', 'Transmission', 'Auto')
INSERT INTO @Specification VALUES ('S1', 'HP', '220')
INSERT INTO @Specification VALUES ('S2', 'Type', 'SUV')
INSERT INTO @Specification VALUES ('S2', 'Transmission', 'Manual')
INSERT INTO @Specification VALUES ('S2', 'HP', '300')
INSERT INTO @Car VALUES ('Accord', 'Type', 'Sedan')
INSERT INTO @Car VALUES ('Accord', 'Transmission', 'Auto')
INSERT INTO @Car VALUES ('Accord', 'HP', '220')
INSERT INTO @Car VALUES ('Accord', 'Color', 'Black')
INSERT INTO @Car VALUES ('Escape', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Escape', 'Transmission', 'Manual')
INSERT INTO @Car VALUES ('Escape', 'HP', '300')
INSERT INTO @Car VALUES ('Explorer', 'Type', 'SUV')
INSERT INTO @Car VALUES ('Explorer', 'Transmission', 'Manual')
SELECT DISTINCT Spec.SpecId, Car.CarName
FROM @Specification Spec
INNER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
FROM @Specification Spec
LEFT OUTER JOIN @Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Car.CarName IS NULL)
View 2 Replies
View Related
Apr 7, 2008
In query analyzer, what is the command to tell me the attributes of the entities in a table? In oracle I can use the describe command. I know their is a way to do it in Query analyzer but I can't remember how. Also I can look visually by expanding the node of the table. But if I can do this through the command line in query analayzer, it is sometimes quicker.
Example. I want to find out about a table named "Employee"
What command would I type that would tell me all of the columns/attributes in that table, and the data types which they are?
Bill
View 2 Replies
View Related
Aug 1, 2000
. When I copy tables from one database to another (Using DTS Wizard) I lose my settings .. primary keys + default values !!
Any help would be appreciated..
.
Thanks
View 2 Replies
View Related
Aug 8, 2007
I must be missing something somewhere...
I have a simple table with three fields: ID, LastName, FirstName. The ID is defined as the PK. In the table is a record of "12345, Smith, John". The incoming flat file has a record of "12345, Smith, Johnny".
In the SCD transform, the ID is the business key, and Last Name and First Name are defined as historical attributes.
During the load, the SCD transform correctly sends the data down the right path, but the insert fails with a primary key violation - as I would expect since it's trying to create a new current record.
How do I get around this problem without removing the PK ???
thx
View 9 Replies
View Related
Oct 29, 2014
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
View 2 Replies
View Related
Jan 24, 2005
Is there a way to make a string field that has an unlimited amount of characters?
View 1 Replies
View Related
Aug 17, 2006
Is there any way to write procedure with ulimited number of parameters?
Like in COALESCE function. You can pass one or more parameters.
View 8 Replies
View Related
Dec 7, 1998
Hi,
If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use?
Char, varchar and text have a max. limit of 255...
Will appreciate any suggestions.
Thanks,
Nishi
View 4 Replies
View Related
Feb 4, 2005
Hello,
I am developing a message board using ASP on IIS and SQL Server 2000, and I am running into a problem.
When a user enters their post, they enter their user name, subject, and of course all of their post content. Now the post content should be an unlimited number of characters since it could end up being multiple paragraphs.
I know I could just use an ASP file system object and write the text to a file, but I wanted to save all of these paragraphs as a field in a database table so I could pull it from the database so it could be edited by the user.
Is there a way to make a field be able to take in an unlimited amount of characters?
Or is this the wrong way to do things; is it bad to have an unlimited amount of characters in a table field? Should I stick to using the ASP System File Object?
View 1 Replies
View Related
Jan 26, 2007
Introduction
This MS SQL Store Procedure solves a problem which is not common
except when you have a table in a database which each row needs
to be uniquely identified and their are more rows in the table
than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to
8.6904152163272468261061026005374e+50 unique rows at best.
If you used a "Big Int" you would be limited to -2^63 (
-9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the
identity field.)
or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for
this invention is due to the need to track every email that an
application sends for Sarbanes/Oxley Requirements. With this
technique, every email sent out will be uniquely identified for a
very very very long time.
The purpose of this is to show how to set up an identity column with
a larger range than a big int or unique id. Try transaction logs
where you need to track every change, or determining click paths
through a website, etc.
The point is, that this method pretty much does what the title
says, "Create unlimited Unique ID's". What table you apply this too
and for what reason is up the the programmer.
Background
This design uses basic counting methods and handles the limitations
of MS SQL TSQL. First, you can use a varchar(4000) as the unique id
column but the issue with this is that as of MSSQL 2000, the largest
indexable field is 900 character. So if you need to be able to
quickly search the table by key, or clustered keys, you need to limit
your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a
temporary table, select the subset into it and search that.
Using the code
First, copy and paste all the TSQL into a Query Window and
compile it in the database you wish to use it in.
[Code]
/**********************************************************************************
Program: ALTER Unlimited Unique ID's (Auto Increment)
Programmer: Vince Gee
Date: 9/28/2005
Parameters:
@TABLE_NAME - The name of the table to establish the auto incrementing field in
@COLUMN_NAME - The column name in the table to establish the auto incrementing field in
@DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with
the same name.
Theory:
A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description:
The purpose of the sql procedure is to automate the creation of
auto updating identities on a sql table without the trouble of
writing the trigger each time.
So what does this do? Well for example lets say we have the following
table which you will have many many many rows in.
ALTER TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
myKey is the unique identifier for each row. We can set it's size really
to anything, This proc will look for the column specified and determine it's
size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated
together exceed the storage compacity w/ the trigger code of 4000 characters. If this
is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc:
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output:
When data is inserted into the table, the auto incrementing field will look like
0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
000000000A
000000000B
000000000C
000000000D
000000000E
000000000F
000000000G
000000000H
000000000I
000000000J
000000000K
000000000L
with how many 0's set up etc. It goes 0-9, then A-Z
***********************************************************************************/
ALTER PROC SP_SET_UNIQUE_FIELD
@TABLE_NAME VARCHAR(255),
@COLUMN_NAME VARCHAR(255),
@DROP_EXISTING_TRIGGER BIT =0
AS
DECLARE
@EXECSQLSTRING nvarchar (4000),
@counter int,
@COLUMN_NAMES varchar(4000),
@tCOLUMN_NAME varchar(4000),
@MAXORDINAL int,
@KEYLENGTH int
--If the trigger
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
begin
IF @DROP_EXISTING_TRIGGER = 0
BEGIN
-- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
RETURN
END
ELSE
BEGIN
--CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT.
set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME
--EXECUTE THE SQL
EXEC SP_EXECUTESQL @EXECSQLSTRING
END
end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS
create table #temp
(
TABLE_QUALIFIER varchar(255),
TABLE_OWNER varchar(255),
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255),
DATA_TYPE int,
[TYPE_NAME] varchar(255),
[PRECISION] int,
LENGTH int,
SCALE int,
RADIX int,
NULLABLE int,
REMARKS varchar(255),
COLUMN_DEF varchar(255),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB varchar(255),
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(255),
SS_DATA_TYPE int
)
--POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE
insert into #temp
exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING
--FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD.
SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP
SET @COUNTER = 1
SET @COLUMN_NAMES = ''
WHILE @COUNTER <= @MAXORDINAL
BEGIN
select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
if (@tCOLUMN_NAME <> @COLUMN_NAME)
begin
SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ','
end
else
begin
select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
end
SET @COUNTER = @COUNTER +1
END
--CLEAN UP
drop table #temp
IF @KEYLENGTH > 900
Begin
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
END
SET @EXECSQLSTRING = '
CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
INSTEAD OF INSERT
AS
BEGIN
DECLARE
@VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@REVERSED_VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@COUNTER INT,
@LEFT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@RIGHT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@CHAR_VALUE CHAR
select @VALUE = ISNULL(MAX(' + @COLUMN_NAME + '),REPLICATE (' + "'0'" + ',' + CONVERT(VARCHAR(900),@KEYLENGTH) + ') ) from ' + @TABLE_NAME + '
SET @REVERSED_VALUE = REVERSE(@VALUE)
SET @COUNTER = 1
WHILE @COUNTER <= LEN(@REVERSED_VALUE)
BEGIN
SET @CHAR_VALUE = SUBSTRING(@REVERSED_VALUE,@COUNTER,1)
IF ASCII(@CHAR_VALUE) <> 122
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
IF ASCII(@CHAR_VALUE) + 1 = 58
SET @CHAR_VALUE = CHAR(97)
ELSE
SET @CHAR_VALUE = CHAR(ASCII(@CHAR_VALUE) + 1)
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
BREAK
END
ELSE
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
SET @CHAR_VALUE = CHAR(48) --SET THE CURRENT POSITION TO ZERO AND WE INCREMENT THE NEXT DIGIT.
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
END
SET @COUNTER = @COUNTER +1
END
SET @VALUE = REVERSE (@REVERSED_VALUE)
INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAMES + @COLUMN_NAME + ')
SELECT
' + @COLUMN_NAMES + '@VALUE
FROM
inserted
END'
if len(@EXECSQLSTRING) <4000
begin
EXEC SP_EXECUTESQL @EXECSQLSTRING
end
else
begin
print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.'
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Create TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on.
Column Name - Name of the column to use as the key.
Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int
set @t = 0
while @t <= 40000
begin
insert into countertest
select '','s','s','s'
set @t = @t + 1
end
GO
Once this completes, you can inspect the unique id by selecting it from the table
SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
View 20 Replies
View Related
May 3, 2008
hi
am using vs2005 with sqlserver2000 .
i want to store word file/rtf/txt content in a table.which datatype i have to select.
which datatype supports unlimited characters?it has to support all type of characters(including html tags).
Thanks
shobha
View 4 Replies
View Related
Oct 20, 2014
We have a project developed on asp.net 3.5 with SQL Server 2008 R2 as back end database. One of our client is facing issue of system performance in the afternoon time only. Mostly the tables are getting locked for unlimited time and system gets very slow. Need a professional SQL Server DBA to identify the root cause.
View 1 Replies
View Related
Mar 11, 2004
Hi All,
Database size is not increasing automatically ,however I have set it as unlimited growth. Any idea about this ?
thanks for in advance,
Sedat Duztas
Probil
View 1 Replies
View Related
Feb 17, 2008
We have 300+ databases on one sinlge server. If I need to change log size to "unlimited" for all of them, is there any way to do so? Please advice.
-Julie
View 9 Replies
View Related
Jan 22, 2003
I want to access all attributes of a record in a table without knowing the name of those attributes,how can I do by using transact-sql?
Thanks.
View 1 Replies
View Related
Aug 24, 2004
I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Thanks,
_z0
View 5 Replies
View Related
Aug 27, 2007
Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"
suggestions??
thansk in advance
km
View 3 Replies
View Related
May 24, 2006
I have a few tables that have an disabled attribute using a BIT datatype. A lot of my queries on the front end look like:
SELECT *
FROM TableA
WHERE disabled <> 1
There's usually some other constraints on the query (get TOP 10 and greater than a certain date for example). Right now my tables are very small (only a couple thousand rows). I don't anticipate these tables having more than 100,000 rows.
Right now let's say there's only a CLUSTERED INDEX on the date field, and regular INDEXES on the identity field and perhaps some other UNIQUE name in the table.
Unless I am doing ranged queries on the CLUSTERED INDEXED field, I'm going to be performing table scans almost every time, right?
This sort of goes along with another question:
Say you run the following (SQL Server):
CREATE TABLE TestA (
[id] INT IDENTITY (1, 1) PRIMARY KEY,
disabled BIT DEFAULT 0
)
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('0')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('1')
GO
INSERT INTO TestA (disabled) VALUES ('0')
Since [id] is a PK there will be a CLUSTERED INDEX placed on it. My question is; what does the optimizer do when you perform the following query?
SELECT TOP 3 *
FROM TestA
WHERE disabled <> '1'
My assumption is that since there's a CLUSTERED INDEX it will simply iterate through every tuple and check to see if disabled is not '1'. If my assumption is correct then these kind of boolean fields aren't a big deal if TOP queries are performed on a CLUSTERED INDEX.
So I guess what I am getting at is: Are bit attributes a sign of bad design? As tables get larger will performance degrade significantly? Would a better design be to have a seperate table of disabled items (which may result in large NOT IN subqueries)?
Any information on his would be greatly appreciated.
View 1 Replies
View Related
May 14, 2008
I have a question about storing the history of particular objects in a database. For example, if I had a table of "People" which had fields "PersonId", "Name", "PhoneNumber", "Height", "Weight", "Proffession" the data in every field stored for each person can change over time, except for the "PersonId", of course, which is why it is included.
I would like to be able to view a persons attributes at any point in time and therefore need to maintain a history. The currenct approach in place is to archive images of the whole table at certain points in time, which is unacceptable as it misses some changes, is not very accessible and also stores data which does not change.
My solution would be to created seperate tables for each changing attribute and have corresponding date for each change. For example, for phone numbers have a table "PeoplePhoneNumbers" with fields "PersonId","PhoneNumber" and "ChangeDate". A few shortcomings I can see in this approach is that firstly there will be many tables, one for each changing attribute, which can be in far greater number than those mentioned. Secondly, joins will have to created between every attribute table to get the orignal single table form, although I don't see this as a very important issue.
I am wondering; is there a more elegent way to structure for objects of this changing nature, or is having seperate tables for each changing attribute the best solution? I'm sure this is a very common issue. Thanks very much for the help,
Mike
View 18 Replies
View Related
Apr 7, 2014
What are attributes in columns that are not types?
View 1 Replies
View Related
Mar 3, 2008
We have an entity such as a documentSearchKey that contains attribtes about a particular document. This document can have 1-N number of search keys or attributes. The classic Employee Table is a good example for a horizontal listing of attributes (fname, lname, SS#, address, etc.) because the employee entity has a "fixed" number of attributes so we can add columns across.
For the documentSearchKey entity attributes can be considered search keys or where clause values.
The documentSearchKey entity has variable number of attributes (docType A has 5 keys, docType B has 15 keys, etc) For this example each docType lives inside its on table so there is not a problem with mixing a variable number attributes inside the same table i.e. we will assume this table has 20 keys vertical or 20 columns horizontal as defined below.
The problem is whether or not to add 20 columns across or to add 3 columns and create a non-normalized DB so additional keys can be added at will.
The proposed table now contains 3 columns (docID, KeyID, KeyValue). Of course, 10 keys for 1 million records create 10 million rows Versus the traditional table with 1 million records always has 1 million records(keys are cols) where some columns contain blanks or nulls.
Which design is better in terms of searching and performance?
Also, books and links are welcome as well. This is a specific question to a production issue.
Thank You Very Much !
View 2 Replies
View Related
Aug 24, 2005
I apologize ahead of time for the long post...Background:Working on a CRM type custom application. The application is for anevent management company. The company will provide the application forother organizations to manage their own events. The events includeconferences, corp meetings, sales meetings, etc...An event planner will define what information is needed for an attendeeto register for an event. We will be providing a standard list ofattributes for the event planner to select from. This list includespersonal information (name, address, phone numbers), air travelinformation (preferred carriers, departure airports, etc...), hotelinformation, etc...we've included all of the information available tous from the business's previous experience. As far as the databasegoes, all of the standard information given to use will be normalized.The problem is each event may have unique information that needs to becollected that is not part of the standard list of attributes. Forexample, if McBurgers is planning an event, the event planner may wantto collect an attendee's McBurger employee code.Depending on the uniqueness of the event, there may be up to 200 uniqueattributes defined for it. This number comes from researching eventsplanned in the last 5 years. The number of attendees for an event rangefrom 100 to 10,000. The company expects about 3000 events per year.Database DesignI've done a fair amount of research and found a couple of options tomeet our requirements, more specifically the need for event planners todefine custom attributes for an event.1-)DynamicColumns:Add an Event specific custom attributes table. The table would looksomething like this:Event_McBurger05AttendeeID | McBurgerEmployeeCode | HiredDate | SomeOtherAttribute-Join Bytes! | AxEt356 | 01/01/2004 | Other val 22-)EAV:Add an EAV (entity, attribute, value) table. The table would looksomething like this:Event_AttributesEventCode | AttendeeID | Attribute | Value-McBurger05 | Join Bytes! | McBurgerEmployeeCode | AxEt356McBurger05 | Join Bytes! | HiredDate | 01/01/2004McBurger05 | Join Bytes! | SomeOtherAttribute | Other val 2The Value attribute would be a character (probably varchar) datatype.3-)Stronger Typed EAVHave an EAV table for each data type. The tables would look somethinglike this:Event_CharAttributesEventCode | AttendeeID | Attribute | CharValue-McBurger05 | Join Bytes! | McBurgerEmployeeCode | AxEt356McBurger05 | Join Bytes! | SomeOtherAttribute | Other val 2Event_DateAttributesEventCode | AttendeeID | Attribute | CharValue-McBurger05 | Join Bytes! | HiredDate | 01/01/2004There would be one Event_[DataType]Attribute table for each of thedatatypes allowed.Pros/Cons1-)DynamicColumnsPros:-Data integrity can be enforced-Simpler queries for reporting-Clearer data model for understanding data storedCons:-Row size limitation of 8k must be managed (probably need to addanother table if run out of room.-Stored procedures for CRUD operations would need to dynamicallycreated ORNeed to use dynamic SQL on the database or application.-Adding/Removing columns on the fly can be very error prone2-)EAVPros-Static CRUD stored procsCons-No data integrity-Complex queries for reporting-Worse performance than option 1.-Table can get BIG...fast.3-)Stronger Typed EAVPros-Static CRUD stored procs-Better data type integrity than EAVCons-Complex queries for reporting-Worse performance than option 1-Table can get BIG...fast.If you are still reading this...thank you!The Questions:-Are there other options other than the 3 described above? Or are thesepretty much it with slight variants.-Does anyone see any missing Pros/Cons for any of the options thatshould be considered?-Is there a "preferred" method for what I am trying to do?I suspect this will come down to the lesser of three devils. Justtrying to figure out which of the three it is.We have prototyped the three options and are leaning towards option 1and 3.Any comments/suggestions are appreciated.Thx
View 3 Replies
View Related
Dec 6, 2006
Hi, all here,
Thank you very much for your kind attention.
I am having a question about Microsoft Clustering algorithm here. When we train the clustering model, we gain the clusters based on the model training. So what are the relationship among all attributes within each cluster? When we sumarize the characteristics for each cluster, for example, based on criteria attribute A=X, we got the darker cluster for this criteria, along with this characteristics (A=X), we also got other characteristics, so what is the exact relationship among all these chracteristics? It seemed they dont have any relationship to each other at all? (A=X dose not mean most likely B=Y if A=X?, what it means only is within this cluster, most likely A=X and B=Y etc. and A=X has the largest population within this cluster). I therefore cant see these chracteristics really interested.
Looking forward to any guidance and advices for that.
With best regards,
Yours sincerely,
View 5 Replies
View Related
May 29, 2008
Hi,
the query below (from Adventure Works) displays the sales amount for three products and a custom member "aggregation" which is the aggregate of these three products, and it cross joins with the attribute "colour".
Code Snippet
with member [Product].[Product Categories].[Subcategory].&[31].[aggregation] as 'AGGREGATE({ [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220] })'
SELECT { [Date].[Calendar].[All Periods] } ON COLUMNS ,
NON EMPTY { { { [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220],[Product].[Product Categories].[Subcategory].&[31].[aggregation] } * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )
Can someone please explain me why I'm getting this result:
All Periods
Sport-100 Helmet, Red
Red
39328.1586
Sport-100 Helmet, Black
Black
12098.0788
Sport-100 Helmet, Blue
Blue
13331.5816
aggregation
Black
64757.819
aggregation
Blue
64757.819
aggregation
Red
64757.819 (note that 64757.819 is the total of the three products)
instead of something like this:
All Periods
Sport-100 Helmet, Red
Red
39328.1586
Sport-100 Helmet, Black
Black
12098.0788
Sport-100 Helmet, Blue
Blue
13331.5816
aggregation
Black
12098.0788
aggregation
Blue
13331.5816
aggregation
Red
39328.1586
and also if anyone knows of a possible way of getting the second type of result?
please note that if I create a custom member that aggregates members of any other level of the Product Category hierarchy, the problem doesn't exist (see code and results below)
Code Snippet
WITH MEMBER [Product].[Product Categories].[Category].&[4].[Aggregation] as
'AGGREGATE({ [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32] })'
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
NON EMPTY { { { [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32],
[Product].[Product Categories].[Category].&[4].[Aggregation]} * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )
All Periods
Helmets
Black
87915.3689
Helmets
Blue
91052.8681
Helmets
Red
79744.6953
Hydration Packs
Silver
65518.7485
aggregation
Black
87915.3689
aggregation
Blue
91052.8681
aggregation
Red
79744.6953
aggregation
Silver
65518.7485
View 1 Replies
View Related
Aug 23, 2006
Is there a way to change the password or encryption settings with SQL or do I need to use Compact from code to do so?
I'm trying to work around the issue that USE does not accept a password.
View 3 Replies
View Related
Nov 28, 2007
hi
i have a problem
i am writing a stored procedure which contains "EXECUTE" statment which excutes the query and retrieves the attributes what i want.
by using that procedure it is working fine and i am able to get the result
but i am not able to build the report why because this dataset not listing the attributes.
my procedure is like this :
--------------------------------------------
USE [HOST_BPM_COVLTCP]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PB_Report_GetProjectAttributes]
@intProjectId INT
AS
BEGIN
DECLARE @STR_ATTRIBUTES VARCHAR(8000), @SRC_ATTRIBUTE_COLUMNS VARCHAR(8000), @SRC_ATRIBUTE_NAMES VARCHAR(8000)
SELECT
@STR_ATTRIBUTES = COALESCE(@STR_ATTRIBUTES + ',', '') + 'dbo.FindAndReplace(MAX(CASE WHEN ATTRIBUTE_ID = ' + CONVERT(VARCHAR, IDX) + ' THEN ISNULL(ATTRIBUTE_VALUE, '''') ELSE '''' END)) [' + CONVERT(VARCHAR, LABEL) + ']',
@SRC_ATTRIBUTE_COLUMNS = COALESCE(@SRC_ATTRIBUTE_COLUMNS + ',', '') + 'SRC.[' + CONVERT(VARCHAR, LABEL) + ']',
CONVERT(VARCHAR, IDX)
@SRC_ATRIBUTE_NAMES = COALESCE(@SRC_ATRIBUTE_NAMES + ',', '') + '''' + LABEL + ''' ATTR_' + CONVERT(VARCHAR, IDX)
FROM
PB_ATTRIBUTE
SELECT
@SRC_ATTRIBUTE_COLUMNS = (CASE WHEN @SRC_ATTRIBUTE_COLUMNS IS NULL THEN '' ELSE ',' + @SRC_ATTRIBUTE_COLUMNS END),
@STR_ATTRIBUTES = ISNULL(@STR_ATTRIBUTES, ''''' DUMMY_COL'),
@SRC_ATRIBUTE_NAMES = ISNULL(@SRC_ATRIBUTE_NAMES, ' '''' WHERE 1 <> 1')
EXEC
(
'
SELECT
DP.IDX PROJECT_ID, dbo.FindAndReplace(DP.CODE) [Project Code], dbo.FindAndReplace(DP.NAME) [Project Name], dbo.FindAndReplace(DP.LABEL) [Project] ' + @SRC_ATTRIBUTE_COLUMNS + ',
ISNULL(DP.CREATED_BY,'''') AS CREATED_BY, ISNULL(DP.MODIFIED_BY,'''') AS MODIFIED_BY,
DP.CREATED_DATE, DP.MODIFIED_DATE
FROM
DIM_PROJECT DP,
(
SELECT
' + @STR_ATTRIBUTES + ', PROJECT_ID
FROM
PB_PROJECT_ATTRIBUTE_VALUE
WHERE
PROJECT_ID = ' + @intProjectId + '
GROUP BY
PROJECT_ID
) SRC
WHERE
DP.IDX = ' + @intProjectId + ' AND
DP.IDX *= SRC.PROJECT_ID
ORDER BY DP.LABEL
'
)
----------
actually this procedure should result the following attributes
PROJECT_ID,
[Project Code],
[Project Name],
[Project],
[Nature of Change],
[Department Priority #],
[Project Start Date],
[Project End Date],
[Project Status],
[Project Justification],
[Project Request Sub-Type],
[Project Request Type],
[Project Request 2nd Sub-Type],
[Requesting Department],
[Capital Fund Number],
but i am not getting the attribute list in the dataset
anyone help me out
View 1 Replies
View Related
Feb 28, 2008
Hi,
I am a novice data miner, working primarly in the BI field. I want to learn more about Data Mining so I am doing some experimenting.
I have a question regarding input attributes. I am particurlary wondering about the Neural Network algorithm, but also for Data Mining in general. What I am thinking about is if, and if so to what extend, I should create derived attributes for the algoritms. I´ll try to clarify with an example:
Lets say I am analysing sales performance for departments in a large company. Some of those departments has a high staff turnover, which might affect sales negatively (although I don't know that...). The high staff turnover could be detected, by the algorithm and humans, by looking at each sales, and which salesperson that handled it. If there are a lot more different salespersons in different departments by the same size and during the same time period, this is a sign of a high staff turnover.
Now is this info enough for the algorithm? Or should I add a column in the case dataset, where I discretesize the staff turnover as "High,Medium,Low"? Does this help the algorithm or can it affect the performance?
I hope you'll get the idea of my question, otherwise ask me!
Cheers,
AL
View 1 Replies
View Related
Jun 6, 2008
This is kind of tricky. I have a table that is structured like this (where the StaffID is the primary key):
StaffID, Date1, Amount1, Date2, Amount2, Date3, Amount3
123, 1/1/08, 100, 2/1/08, 200, 3/1/08, 300
You can already see that whoever made this table wasn't thinking prudently. Anyway, I would like to make the table look like this:
StaffID, Date, Amount
123, 1/1/08, 100
123 2/1/08, 200
123 3/1/08, 300
Where the StaffID and Date are concatenated keys.
Is there a way to either create a View or create a Query Table that converts the data like that for me? I need the new view/query to update when the original table updates, so a new table isn't an option.
Thanks ahead of time!
Reuben
View 2 Replies
View Related
