2 Structural Questions For Picking Field Type
Jul 25, 2006
Hi all,
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY
View 1 Replies
ADVERTISEMENT
Jun 20, 2001
Hello,
I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:
CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int)
AS
SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item]
FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#]
WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM"
GO
TIA,
Bruce Wexler
View 2 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Mar 27, 2007
I have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
View 3 Replies
View Related
Dec 8, 2007
1) Unforgiveablity - this is an advantage and disadvantage of service broker. The advantage is that it forces developers to be precise with the formation and decomposition of messages as well as forcing disciplined use of transactions. The disadvantages lie in the same. With the slightest error in dialog management, the transaction log grows out of control. With any solution that is incorporating SQL Express (and thanks to the limitations for surfacing the problems), the transaction log, invariably - at some point - grows uncontrollably, especially if you have a web application monitoring queues. There really needs to be better checkpoints and obeyance of normal SQL Server contraints. It seems that sometimes service broker has a mind of its own and takes over not only the SQL instance, but the disk and everything else.
2) Administration/Management capabilities - as far as I am concerned, these are non-existent. I find it hard to believe that I can't even script my service broker objects using the native management studio scripting tool. Its ridiculous that - in an enterprise system - I have to manually script out scores of service broker objects. The operations group agrees with me and this does nothing to enhance the reputation of SQL Server as an enterprise player. You can't produce a SP that provides these capabilities? I have had to build in diagnostics and recovery stats by way of user tables that collect and analyze service broker message activity.
3) Internal Activity - what the hell is going on in these internal tables? I can't find any documentation about the use of tempdb or anything else. Please provide something explaining where all the activity occurs and where space is consumed. This is kind of important when operating on restricted platforms, like SQL Express.
4) Locking - seems like you need a more granular locking strategy. This is just conjecture, but it seems when service broker wants; service broker takes. How about letting the application architect/developer decide when service broker takes?
5) Dialog Pool Management - this is very complex to implement and almost all "real" applications need it. You need to provide an out-of-the-box, starter implementation because this leads to problems mentioned in #1. Remember how the VS application wizard killed the Borland dev environment? Well, SQL Server needs an equivalent. Stop making us write the same thing over and over again; and stop making me write the same queries over and over again.
6) Evangelists - Most people don't even know what service broker is, and it makes it a very hard sell. Even experienced SQL developers are not sure how and why it should be used. I have interviewed over 20 pretty strong SQL candidates over the last year and I can't name one that new ANYTHING about service broker.
7) Guidelines for Conversation Cleanup - well, it's a fact of life - we have to cleanup bad conversation handles from time to time. OK, maybe this wouldn't happen if we didn't have careless or error-prone programming, but we do. What is the proper way to cleanup dialogs between two endpoints? How about some sample queries to run on the initiator and the target? Or even an english language description of how to go about cleaning up and starting over with conversations that have gotten into a "bad" state?
8) UDP/Broadcast/Multicast Messages - This is a pretty big problem in my opinion. I can't believe this has not been added to 2008 (or at least, I did not see it in the feature list). Roger Wolter said it would be in the next version when the 2005 articles were written. So, I am supposed to keep cursoring through message endpoints and tricking up the TRANSPORT route to achieve what a broadcast implementation could achieve?
I guess thats enough for now. I look forward to the responses.
View 2 Replies
View Related
Jul 25, 2006
Hi all,
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY
View 1 Replies
View Related
Mar 29, 2007
Hi
Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.
I need to create a publication and Merge subscription with the below structural changes
User table with the fields of Uname varchar(25) and pwd carchar(30).
The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).
Is it possible? If you, please give the details.
Thanks.
View 7 Replies
View Related
May 25, 2007
Hi there,
I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.
One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.
An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.
Many tables aren't that big, so the values will fit in an int datatype.
1. Is iot a good option to change the decimals columns to an int column ?
2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?
3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?
Thanks in advance,
Greetz,
Patrick de Jong
View 4 Replies
View Related
Mar 31, 2007
I have a requirement.
I have a CUBE in SQL 2000. I need to change the structure of Fact Table and i need to add one more dimension to my CUBE.
What are the problems will arise if i do this. i need to Fully process the CUBE?
PLS help me
View 1 Replies
View Related
Nov 7, 2004
I'm using Microsoft SQL Server 2000 on windows 2000 Professional OS.
I want to make a field having capacity of 100,000 characters. What datatype should be best for this requirement.
View 1 Replies
View Related
Jul 23, 2005
Hi FriendsI have question.I connected oracle and MS access tables(via Delphi7).I wanna get type of field in indicated table. For example if it isinteger or boolean e.t.cPlease tell me the SQL function or whatever that retrievs type of field.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 28, 2007
Hi guys
I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message.
I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
Thanks
-Sarah
Select SUM(Convert(int, calculatedValue))
from monitor.dbo.monHistory
where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
This is the error message I recieve:
Conversion failed when converting the varchar value '274.2' to data type int.
View 6 Replies
View Related
Aug 4, 2004
Hi,
I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??
CREATE PROCEDURE PublishSchedule
(
@SiteCode smallint,
@YearMonth int
)
AS
DECLARE @Active bit
IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 )
BEGIN
UPDATE CabsSchedule
SET Active=1
WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth
END
GO
Thanks
View 4 Replies
View Related
Feb 17, 2007
I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.Something like a CONTAINS keyword...If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.
View 1 Replies
View Related
Jul 14, 2007
Hi, I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file? Thanks Bones
View 6 Replies
View Related
Jan 15, 2008
I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.
View 1 Replies
View Related
Mar 17, 2008
Hi, I want to store a time duration such as 1:30 (mm:ss), 1:00, or 1:23 in a SQL 2005 database. What is the best data field type for this data? DateTime or TimeStamp? Thanks
View 2 Replies
View Related
Feb 25, 2005
Hi,
I was wondering if someone can help me with is problem.
I have uploaded word docs to the db which is fine. The problem is viewing. I can view then as word documents but the boss does not want the files opened in word.
Is it possable to retrieve the file from from the db and put into say a textbox or lable.
I can see the letter P using this code
Dim Doc() As Byte = New Byte(Convert.ToInt32(0)) {}
Dim bytesReceived As Long = DBContent.GetBytes(0, 0, Doc, 0, Doc.Length)
Dim encoding As ASCIIEncoding = New ASCIIEncoding
lblTest.Text = encoding.GetString(Doc, 0, Convert.ToInt32(bytesReceived)).ToString
Can any help
Thanks
View 4 Replies
View Related
Feb 4, 2006
hi all, the field type :datetime and smalldatetime, i still can't understand.everytime when i inserted the data to the db, i also get the error message "System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."i must change the field type to string, so that i can insert data my codetxt_datetime.text = '5/2/2006'insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason???thank you!!
View 4 Replies
View Related
Jul 9, 2004
I have a field with exam scores. 77, 89.5 ect. range :0-100
What datatype shall I use?
View 2 Replies
View Related
Dec 29, 2004
Is there a data type in SQL Server for entering hyperlinks to websites? MS Access has a hyperlink data type but I can't find a corresponding SQL Server data type which I can use.
View 4 Replies
View Related
May 17, 2004
Hi Everybody,
In a table there´s a field for store real values,
so...in the database, using the SQL Server Enterprise Manager
and execute query as 'select * from' and it return the value
'15,35' just like stored before, why using Delphi via an ADO conection
in my result set there is a '15,3500003814697' value if in the record time
the inputed value was '15,35'?
What I have doing wrong?
Thanks for attention
Leonardo Almeida
View 2 Replies
View Related
Jun 7, 2014
Is there an easy way to send back to my C# program the pascal case of a field type? If you get the parameters or columns for a table or procedure the type is in all lowercase (varchar instead of VarChar). But in C#, it is in an enum (SqlDbType) that has the variables as mixed case so you can't compare them and just pass the type. You need to figure out what the case should be - varchar should be VarChar and bigint should be BigInt.
View 1 Replies
View Related
Sep 8, 2013
What should be data type of password field?
View 2 Replies
View Related
Jan 31, 2007
I want to zip up some HTML and store it a field. 2 questions:
1: Are there any sample scripts available for this, I haven't found any?
2: What Type should the storage field be for that?
Thanks,
--PhB
--PhB
View 6 Replies
View Related
Mar 3, 2007
Hi,
I am using SQL Server 2005 Express. I am creating a table, which will have a comments field. Whats an appropriate data type that should be used for a "comments" field? So far, I have unlimited text and characters can be entered, but that a limitation can be placed.
Thanks
Chirs
View 1 Replies
View Related
Apr 11, 2007
I'm using XmlDataSource to build reports based on WebService output (as plain XML text). (SSRS2005)
I have several fields which contain Float/Decimal/Integer value, but RS reads them as text and I have to use CDbl() to convert text to numeric value.
In MSDN I found some info about "Type" attribute(?) for XmlElement.
My xml is:
...<Field1 Type="Float">34567.89</Field1>...
But RS still reads data from this field as text.
Is there any way to specify field's (xmlelement's) data type instead of using CDbl() function everywhere?
View 3 Replies
View Related
Feb 22, 2008
Hello,
We are trying to test for a field in our 'setup' table and no matter what we do it's has a personality of its own. It's the wackiest thing. The field has two steps in this update script - first we are checking to make sure the field 'set_fsm' is still in the table, if so then we are blanking out the field by updating the value to a pair of ' '. Next, after the field now has just a blank value we are going to change the data type from char(10) to a bit field. However, when we try to check the code the first time it works but every other time the update script runs it doesn't work. The code below are two approaches that we have tried with our general update tools that is run on each version / product release we have. Assistance is greatly appreciated.
The record doesn€™t exit yet it still falls into the BEGIN.
IF exists (select * from dbo.syscolumns where name = 'set_fsm'
and id = (select id from dbo.sysobjects where name = 'setup'))
BEGIN
PRINT €˜I am here€™
UPDATE setup SET set_fsm = ''
END
It won€™t print the €œI am here€? but still tries to update the table!!!
Here€™s the results:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'set_fsm'.
Someone else said try this€¦
IF EXISTS (SELECT 1
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
WHERE o.name = 'setup'
and c.name = 'set_fsm'
and o.xtype = 'U') -- because index columns are also stored in the sysobjects table
begin
print 'Found column'
update setup set set_fsm = ''
END
else
begin
print 'set_fsm column not found in table setup'
end
Same results:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'set_fsm'.
View 3 Replies
View Related
Mar 19, 2008
Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?
ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195- 11111111- MA1E2- Annual - 1- 2006- 34
1205- 11111111- MA1E2- Supplemental- 1- 2006- 40 (S)
1194- 11111111- MA1E1- Annual -1- 2006- 35
1204- 11111111- MA1E1- Supplemental- 1- 2006- 40 (S)
Here is the SQL I'm using to get all the records from the studentresults table, for first years only:
SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1
How do I change this to pick out only the supplemental exam results?
Regards,
sabatier
View 1 Replies
View Related
Feb 5, 2013
The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.
CREATE TABLE Feeding_Options
(license_nbr INTEGER NOT NULL PRIMARY KEY,
animal_name VARCHAR (10) NOT NULL,
food_brand VARCHAR(15) NOT NULL);
INSERT INTO Feeding_Options
VALUES
(6401715, 'rat', 'IAMS'),
[code]....
To frame the problem better, her are the values in each column:
animals = {'rat', 'dog', 'cat', 'fish', 'fox'}
food = {'IAMS', 'Sci Diet', 'Purina', 'Alpo'}
In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.
View 2 Replies
View Related
May 14, 2008
Hi,
Slightly knotty and hard-to-articulate problem here, so please bear with me.
As part of a series of transactions, I have a table which contains data which looks like this:
AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId)
as valueId, adWeeks, clientId, adID, AdBookingID
into #worktable
from #tmp
group by adWeeks, clientId, adID, AdBookingID
having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need?
Cheers,
Matt
View 7 Replies
View Related
May 16, 2007
I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O
i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5
Please suggest me a query for this
View 10 Replies
View Related
