Formula Field In SQL Server 2000
Oct 20, 2004Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..
Thx
ADVERTISEMENT
Counting Field Totals As A Formula
Jul 23, 2005Hi,I'm trying to count field login totals for users which updates another fieldin another table. Which is the most efficient method?I don't want to use a standard query as it will take too long if there are1000 users per company each with 1000 plus logins.I was thinking in terms of either a function, or a formula (using the built-in formula field within mssql).the query though (as its the only way which i'm familiar) is:SELECT SUM(NumberOfLogons) AS TotalLogonsFROM EmployerProfileDB39WHERE (CompanyName = x) AND (EmployerID = y)how would i write this as a formula or as a function?
View 1 Replies View RelatedTrying To Get The Remainder Of A Decimal By Using Formula On A Field
Dec 22, 2005Hello Everyone,
Here is my situation.
I have a field in a table (named QuestionPosition)(type Decimal) that contains values like this:
1 2 3 4 5.1 5.2 5.3 ...
I want to have another field in the same table that stores just the remainder (in the above case a 0 for integer values and the 1 of the 5.1, 2 of the 5.2 etc.
I created the field in the table (named QuestionSubPosition) and tried to set the formula for it to QuestionPosition % 1 but it does not accept the formula.
The part that i do not understand is that i can set the formula to QuestionPosition / 1 it works (but not the results i want).
Any ideas on how i can get the results i want?
Corey
How To Convert String To Upper Case Using Field's Formula Property
Jul 20, 2005Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
View 3 Replies View RelatedColumn Formula - SQL 2000
Jul 29, 2004Can anyone please explain what a valid column formula would look like please?
I am wondering if it would be applicable to a problem i have and cannot seem to enter any formula that will be accepted by SQLEM
tia
fatherjack
Reporting Services :: Add Formula To Formula Bar In Excel
Sep 1, 2015In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.
View 3 Replies View RelatedEncrypting A Field In SQL Server 2000
Jul 20, 2006Hi,
I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks
How To Copy The Value Of One Field Into Another Using SQL Server 2000
Oct 20, 2006I am a little embarrased to be asking this question because I know that the answer must be simple but I am still a nube at SQL. I am stuck with a simple query that copies one field of data into another. Below is the script that I am trying to execute.
UPDATE table
SET field2 = field1
I get an error from SQL Server that says that the String or binary data would be truncated. I am assuming that the SQL server is trying to concatenate all the value of field1 into field2. All I am trying to do is copy the values of one field into another for each and every row in the table. Thanks in advance.
SQL Server 2000 Filling A Key Field
Jul 20, 2005I have a table that has a int field that contains unique nubers.if I need to insert 4000 or so new records into this table using a SQLcommand how would I be able to fill this field with the next availablenumber?This will be a manual procedure done maybe once a year so the nextnumber will be known ahaed of time. but the insert command need toincrement this number by one everytime it puts a new row in.
View 2 Replies View RelatedSum Of Time Field In SQL Server 2000 - Very Urgent
Feb 13, 2007Hello all. I have a nvarchar field in the format dd:hh:mm:ss I need to get a sum of these fields in the same format. So for example if the values are 01:23:00:00 and 00:01:00:00 then the sum of these fields should be 02:00:00:00. Can someone help me? Thanks.
View 12 Replies View RelatedInserting A Checkbox Value Into Bit Field Sql Server 2000
Dec 17, 2004Edited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs)
Dim myCommand As SqlCommand
Dim insertCmd As String
' Build a SQL INSERT statement string for all the input-form
' field values.
insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);"
' Initialize the SqlCommand with the new SQL string.
myCommand = New SqlCommand(insertCmd, myConnection)
' Create new parameters for the SqlCommand object and
' initialize them to the input-form field values.
myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50))
myCommand.Parameters("@SectionName").Value = Section_name.Value
myCommand.Parameters.Add(New SqlParameter("@SectionLink", SqlDbType.nVarChar, 80))
myCommand.Parameters("@SectionLink").Value = Section_link.Value
myCommand.Parameters.Add(New SqlParameter("@Title", SqlDbType.nVarChar, 50))
myCommand.Parameters("@Title").Value = Section_title.Value
If New_window.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 1
End If
If Latest_news.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 1
End If
If Partners.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 1
End If
If Support.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 1
End If
myCommand.Connection.Open()
' Test whether the new row can be added and display the
' appropriate message box to the user.
Try
myCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & insertCmd
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try
myCommand.Connection.Close()
BindGrid()
End Sub
Any response would be appreciated
Updating Varchar Field In SQL Server 2000
Oct 4, 2004I would like to update a varchar field with an update statement that appends information to what is currently stored in the field, for example in the Statement field I may currently have a name (Mark) and I want to add a unique identifier to the end of the name so that it may look like, Markq1572, is there a way to do this in an update statement?
View 1 Replies View RelatedChanging Field Names Is SQL Server 2000
May 23, 2007Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.
Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
Password Field Encryption In Sql Server 2000
Dec 4, 2007
What is best way to encrypt password field in sql server 2000?.
Is there any system stored procedure to encrypt password?.
What kind of algorithm password encryption method uses?.
Cant Connect To Sql 2000 From Client With All Field Available I.e Server Ip And Port
Apr 25, 2007cant connect to sql server 2000 from c# 2003
View 1 Replies View RelatedRetrieval Of The Description Of A Field In A SQL Server 2000 Based Table
Jun 1, 2006I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.
I have an example table with the following structure:
Table Name: Autioneer's (translated from Portuguese)
'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code
'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name
'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number
I need a way to programatically extract the Fields Description Property from the table
example sintaxe:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'
Thanks for any help that can be offered here
Neil Ramkeerat.
Replies can be sent to neilramkeerat@hotmail.com
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.
DTS Import Of MDB In SQL Server 2000 Drops Memo Field Data
May 22, 2006I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
Formula In Sql Server
Nov 17, 2007hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application
thanx
SQL Server 2008 :: Getting Formula Of A Computed Column
May 25, 2011I'm trying to write a query that will display the formula for a computed column in SQL Server 2008R2.
I have looked here: [URL] ....
and it say (at least I think) that I can look at the Formula property of COLUMNPROPERTY like this:
SELECT COLUMN_NAME ,
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsComputed'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'Formula'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsDeterministic')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Event' AND COLUMN_NAME = 'CurrentAttendance'
I know the column is computed and I can see the formula in SSMS. I wanted to do this in T-SQL. How to get this value?
SQL Server 2008 :: How To Make Query To Calculate A Dynamic Formula
Oct 16, 2015I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
[Code] ....
Table2: TransactionFormula
Only has 2 fields, No & Amount
the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9
Here's the example result for TransactionFormula records
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
Problem With Formula Input With SQL Server Management Studio Express
Aug 10, 2006Hi all,
I'm creating a database using SQL Server Management Studio Express and have a problem. I've got 4 columns: Surface, Rent, MonthlyIncome and AnnualIncome. Surface and Rent are inputed by user, MonthlyIncome is straight calculation Rent*Surface using Computed Column Formula. But, when I want to calculate AnnualIncome SQL SMSE doesn't allow me to input formula like this MonthlyIncome*12. Where I can read about limitations in formula field?
TIA.
Przemek
How To Store Image In Image Field In Sql Server 2000
Jul 12, 2007
hi all,
i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.
how can i store any image ? what are the steps???????
thanx in advance
Calculated Field In SQL 2000
Sep 12, 2007I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton
View 6 Replies View RelatedProblem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.
Jul 11, 2005I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedInformix 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?
Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat
Jul 23, 2005Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies View RelatedUpdate A Text Field In T-SQL 2000.. How ??
Feb 19, 2004First i use openxml to get my data to update the other server with webservices.
my prob is that i cant update text Fields because i got an error
================
OLE DB provider 'OpenXML' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'OpenXML' IRowset::RestartPosition returned 0x80004005: The provider did not give any information about the error.].
================
what would be my best shot here...
thanx
=======================================
XEC sp_xml_preparedocument @handle OUTPUT, @data
begin transaction
SELECT * FROM TblEvenement WHERE idEvenement = 95
UPDATE TblEvenement SET
TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement),
TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenement),
TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescriptionCourte),
TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescriptionLongue),
TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle),
/*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/
TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste),
TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAfficherInternet),
TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletLimite),
TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivraisonCourrier),
TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTypeRepresentation),
TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInternet),
TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique),
TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmissionGenerale),
TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEvenementEnVente),
TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur),
TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementDemo),
/*TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA),*/
/*TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722),*/
TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDescriptionCourte_En),
/*TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDescriptionLongue_En),*/
TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetailInternet_En)
FROM OPENXML (@handle, N'//TblEvenement')
WITH
(
idEvenement int,
sNomEvenement nvarchar (100),
sDescriptionCourte nvarchar (100),
sDescriptionLongue text,
IdTypeSalle int,
imgSalle image,
sNomArtiste nvarchar (100),
bAfficherInternet bit,
nNbBilletLimite int,
bLivraisonCourrier bit,
IdTypeRepresentation int,
sDetailInternet nvarchar (100),
bHistorique bit,
bAdmissionGenerale bit,
bEvenementEnVente bit,
IdProducteur int,
bEvenementDemo bit,
sLogoBOCA text,
sLogoLP2722 text,
sDescriptionCourte_En nvarchar (100),
sDescriptionLongue_En text,
sDetailInternet_En nvarchar (100),
sNomEvenement_En nvarchar (100)
) iox
WHERE TblEvenement.idEvenement = iox.IdEvenement /*and WRITETEXT (TblEvenement.sDescriptionLongue @ptrval 'Salut')*/
SELECT * FROM TblEvenement WHERE idEvenement = 95
rollback
EXEC sp_xml_removedocument @handle
Shorttime Field In SQLServer 2000
Dec 4, 2001Hi,
I am converting a MS Access database to SQLserver. Now in MS access I could change the format of the dateTime datatype so it only had HH:MM. (Shorttime)
How do I do this in SQL server 2000?? If I choose DATATIMe as datafield and I copy the records from MSAccess (eq. 09:00) to SQL server (eg. 1899-12-30 16:00:00.000) there is a date added to the contents of the field.
Or is thre another sollution???
Thanks
GERT
Access Memo Field To SQL Server Text Field
Nov 19, 2006Hi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
SQL Server 2008 :: Selecting Field That Contains Data From Another Field
May 28, 2015We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
MS Access Memo Field To SQL Server Text Field
Aug 20, 2006Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
SQL 2000 Table Field String Wrap
Dec 27, 2007A string needs to be stored in a SQL 2000 table varchar(255) fileld in such a way that when emailed or printed out, it will display as below:
Name: John DoePhone: 213-444-5555Email:jdoe@test.comCity: New YorkCountry: USA
How can such a string be constituted?Thanks