SQL Server Field Type Designation
Dec 29, 2004Is 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
ADVERTISEMENT
Informix Date Type Field To SQL Server Datetime Field Error
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?
DB Design / Designation...
Jan 11, 2005I'm setting up an ASP.NET application using SQL Server 2000 which will be used by hundreds of clients. Among the tables in the database, each client should only have access to his own records.
Here's where I'm stuck...
Should I
1. Create a separate database for each client and duplicate the table set. In this case, the connection string would be slightly different based on the client logging in (i.e. the DB name will change, but that should be it). While all tables would contain a fairly small amount of records (e.g. 15,000 - 250,000), this would require several hundred databases, one for each client, on the server. Note, as clients come and go, DBs would have to be deleted / created on the fly programmatically.
or...
2. Create a single database and in each table have a field which uniquely identifies the client (e.g. clientId)? This would require that all queries include a "clientId" specification so as to retrieve/update/delete the proper rows.
I know that some tables will contain several million records in total across the hundreds of client. Others will contain perhaps only 100,000 in total.
I am unaware of the performance issues in SQL Server 2000 and wanted to get advice on this issue.
I do figure SQL Server 2000 will tackle large datasets, but is it better to keep all data within a single database with large numbers of records, or separate them as indicated above?
Thanks for any advice.
Mark
Finding Designation Of An Employee On A Given Date
Jul 23, 2005Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder
View 2 Replies View RelatedHow To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type
May 17, 2015I 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.
Error Of Date Type Field In SQL Server 2000 && Mshflexgrid
Jan 7, 2007Hi
this is my problem.
mshflexgrid converts date value from dd.mm.yyyy to yyyy-mm-dd or mm.dd.yyyy
When i use JetOLEDB & .mdb file
I can show correctly date type field in mshflexgrid with str() function.
For example
rst.open"Select str(MyDateField) from table
set mshflexgrid.datasource = rst
i can see dd.mm.yyyy ' namely good
when i dont use str() function i see mm.dd.yyyy ' bad version
Now, i'm using SQL server 2000 & mdf file.
mshflexgrid don't show correctly date field ( i want it to be like dd.mm.yyyy ) because i cant use str() function with SQL sever 2000 & mdf file
For example
rst.open"Select str(MyDateField) from table... occur error
When i want to use Convert function in SQL server 2000 & mdf file
it automaticly converts it like Jun 18 2004 12:00AM but i dont want this
I want see this 18.06.2004 in Mshflexgrid.
What can i do. help me pls.
How To Insert A UTF-8 Encoding Type Character Into A Sql Server Text DataType Field ?
Dec 4, 2007I want to insert a value which has the UTF-8 encoding into a field of the database which has the "text" data Type
but it saves like this : " ?????????????? "
I would be thankful if u tell me how should I save it ?
here is the SqlComand I wrote :
string comand = " Insert into Table1 ( title , Body ) values ( ' " + textBox1.text + " ' , ' " + textBox2.text + " ' ) " ;
Table1
=============
title nvarchar(1000)
body text
What Should B The Field Type
Nov 7, 2004I'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.
About SQL Field Type
Jul 23, 2005Hi 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 RelatedSum Of A Field That Has A Type Varchar
Mar 28, 2007Hi 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.
Updating A Field Of Type Bit
Aug 4, 2004Hi,
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
Contains Keyword For Integer Field Type
Feb 17, 2007I 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 RelatedSave SQL XML Field Type To A Xml File
Jul 14, 2007Hi, 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 RelatedSQL Datetime Field Type Command Help??
Jan 15, 2008I 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 RelatedDuration Data Field Type
Mar 17, 2008Hi, 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 RelatedWord Doc In Image Field Type
Feb 25, 2005Hi,
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
About The Type Field : Datetime And Smalldatetime
Feb 4, 2006hi 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 RelatedTable Field Data Type
Jul 9, 2004I have a field with exam scores. 77, 89.5 ect. range :0-100
What datatype shall I use?
Someone Can Give A Litgh? - Field Type
May 17, 2004Hi 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
T-SQL (SS2K8) :: Case For Field Type
Jun 7, 2014Is 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 RelatedWhat Should Be Data Type Of Password Field
Sep 8, 2013What should be data type of password field?
View 2 Replies View RelatedZipped Data In Sql Field: What Type?
Jan 31, 2007I 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
Data Type For Comments Field?
Mar 3, 2007Hi,
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
How To Specify Data Type For XMLDatasSource Field?
Apr 11, 2007I'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?
Testing For Field Type/Status
Feb 22, 2008Hello,
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'.
Does The IDENTITY Field Type In SQL Have A Maximum Size To It?
Mar 11, 2007Does the IDENTITY field type in SQL have a maximum size to it?
You know like int only goes so high up,
Filling Image Data Type Field
Mar 18, 2008Hi
I am currently working on an application that uses a stored procedure to retrieve data from a database and then display it in a web page. My problem is that some of the data in the database will be images, I am currently putting in test data to test my code/procedures my problem is how do I put in test data for images, when I am finished I am going to add an admin section that will allow me to add images that way but how do I go about adding them to the database until then? I have set the field to the image data type but have no idea how to relate this to an image on my server?
Thanks, Adam
What Type Of Field Should I Use To Store An Array Or Datatable?
Feb 25, 2005Hi - I'm using Session variables to store information (sort of webshop).
The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.
I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?
I'm using SQL Server 2000.
Thanks for any help,
Mark
How To Update An Empty Date-type Field
May 23, 2006Hi,
I have update function that updating sql database table.
In the table I have fields like: name, address, phone, …, install_date
updateScreen(txtSName.text, txtSLocation.text, txtSPhone.text, txtSAddress.text, txtSPostal.text, ddSCity.SelectedItem.text, ddSCountry.SelectedItem.text, txtSOwner.text, txtSInstall.text )
The function work fine except the part whent install_date field is empty then I have the following message:
System.InvalidCastException: Conversion from string "" to type 'Date' is not valid.
Ii would like to use the same update function to make an updates even when date field is empty.
If someone has any idea how make it work, I would appreciate that
Alex
How Do I Make The Field A Number Data Type?
Feb 8, 2007Hi there!
I'm using a Switch statement in my SQL as follows:
Code:
SELECT symbol,
Switch(timestamp Is Null,Null,
timestamp <= 005959, 0,
timestamp<=235959,23) AS period
INTO averageprice
FROM stocktrades;
Now here's my problem. The Data Type stored in the 'period' field of this new table I've created, dubbed averageprice, is a Text field and I want it to be a Number field. I've tried my best to figure this out and I'm still looking so any helpful hints or solution would be greatly appreciated. Thanks!!!
2 Structural Questions For Picking Field Type
Jul 25, 2006Hi 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
T-SQL (SS2K8) :: How To Select Decimal Type Field
Feb 2, 2015I have a table with fields:
Id int
Pay numeric(19,3)
value records:
id pay
1 1.000
2 2.250
3 3.445
4 6.000
I want select of table to form:
id pay
1 1
2 2.25
3 3.445
4 6
if value decimal pay field Greater of zero then
value select= value field
else
delete value decimal and show
Only 2 Decimal Places In Field Of Type Money
Mar 29, 2007I have a table in SQL 2005 with a field that has a value of type 'money'. When values are added, the field has 4 decimal places. Is there a way that I can make it only have 2 decimal places right away? Thanks!!!
View 1 Replies View Related