Write String Default Value Function

Jun 3, 2008

I have a datetime column and I set the Default value of the column to: getDate().

How can I set a nvarchar columns Default value to write string data (such as: "test")?

View 5 Replies


ADVERTISEMENT

Read And Write A Constraint Or Default Value

Nov 20, 2007

Okay, maybe I'm getting ahead of myself.
Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.
I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

View 6 Replies View Related

How To Write This Function

Dec 27, 2006

How can I write a function that accepts a data as a parameter and if the date is

Last Monday in May or
First Monday in September or
4th Tuesday in November
 
returns true otherwise returns false.

View 1 Replies View Related

Help Me Write A Search Function Please

Jun 8, 2007

Hi all,I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item... For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item. Any help would be greatly appreciated,Thank you all,Kenny. 

View 15 Replies View Related

How To Write An Aggregate Function Which Can Used Like MAX(filedname)

Jul 20, 2005

Recently, I will write a database application to get the median valueaccording to the grouped condition.maybe, just like the following.SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_tableAny suggestion?

View 2 Replies View Related

Should I Use A Clr Function Or Write A Windows Service

Jul 9, 2007

hi all.

please help with my dilemma.

this is my task:

i should write a service that will run every 1 hour.

this service should check a folder.

inside this folder there are some xml files, all of them in the same format exactly.

when the service encounters with this xml file, it should open it,pull some data out of it, and update a table in the db.

NOW - I HAVE 2 OPTIONS, AND THIS IS THE DILEMMA:

should i write a clr function to do this task, or should i just write a standard c# windows service, that will open a connection to the db when it should perform update?

what are the advatages?

View 3 Replies View Related

How To Write A Function For MS SQL 2005 To Save Value After Underscore?

Apr 14, 2008

Hi Gurus,

How do I write a T-SQL function for a string before a underscore is found?


Thanks Gurus.

View 2 Replies View Related

[howto] Write A Function That Process Row Collection

Mar 31, 2004

something like max,min?
help please!!!

View 5 Replies View Related

How Do I Write An OUTER APPLY Without A Table Function?

Nov 1, 2006

I'd like to select the last unshipped order, or if none is open, the last shipped order, for each customer. I noticed that all the examples on line for OUTER APPLY involve table-valued functions, but I have read (in a book I don't have here) that a subquery can work. The following shows what I want, but it isn't valid syntax:
SELECT c.CustomerName, oa.OrderNumber, oa.Status
  FROM Customers c
  OUTER APPLY (
    SELECT TOP 1 CustomerID, OrderNumber,
    CASE WHEN ShipDate IS NULL
         THEN 'due ' + CAST(DueDate AS VARCHAR)
         ELSE 'shipped ' + CAST(ShipDate AS VARCHAR)
         END AS Status,
    CASE WHEN ShipDate IS NULL
         THEN DueDate + 1000 -- Give open orders priority.
         ELSE ShipDate
         END AS SortOrder
    ORDER BY SortOrder
              ) AS oa ON oa.CustomerID = c.CustomerID

 

View 1 Replies View Related

Can't Write Longer String In The Record

Jun 8, 2004

I have a field which was varchar before but I changed it to text.

But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.



it says <Long Text> and i can't enter any more characters.

View 2 Replies View Related

How To Write A Function With Integer Parameter Accepting Null

Jan 19, 2007

I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code  Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required.
How can I get around this?

View 3 Replies View Related

How To Write A Function That Will Return True If User Is In A Certain Role?

Jul 20, 2005

I have a SQL database with an Access front end. In the database Ihave a read only and a read write role. When a read only user opensthe database I want all the fields on the form to be locked so thatthe user will not try to change data and get an error from the server.Right now I am doing that with a table. But it's a hassle to have tomaintain a table when if I could answer the question is the currentlylogged in user in the read write role?My server is running SQL Server 2000. So I was wondering if I couldwrite a function to do this? The function would take the role beingchecked as a text parameter and return true if the currently logged inuser is in that role or false if he/she isn't.

View 1 Replies View Related

Transact SQL :: Not Allowed To Write Delete Statements In A Function?

Jun 25, 2015

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

View 8 Replies View Related

Convert Function In Default Constraint

Aug 23, 2006

I have two fields CourseID and Erpid in table.

CourseID has identity property with integer datatype.

I need to add a default value for Erpid column which will show a value like 'A' + CourseID column. Erpid is Varchar column.

How can I use Convert function in default constraint?

Thanks!

View 4 Replies View Related

How Can I Use Default Schemas Of Users In A Function ?

Aug 2, 2007

Hi,

I've a Problem with a Function called by different users.
I want the different callers to query a table in their own default Schema.

So if i refence the full Path [Schema].[Tablename] it works Properly.
If i just Use the [Tablename] for reference it fails.

The function was created with EXECUTE AS CALLER option ;

if I call it with dynamic SQL i retreive the error-message

"Only functions and extended stored procedures can be executed from within a function."

does anybody know a solution for this problem?

thanks in advance
Raimund

View 2 Replies View Related

SQL Server 2012 :: Possible To Tell Whether Function Passed A Value Or Used Default Value

Apr 22, 2015

I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.

However, I don't know how to tell if the date value in the function came from an input or was the default.

View 4 Replies View Related

Default Template For User Defined Function

Jan 6, 2006

How can I restore the original User Defined Function Template? I madean error and saved one of my UDFs as a template but would like theoriginal template restored?TIA

View 3 Replies View Related

How To Specify An Empty String As A Default Column Value?

Dec 6, 2007

In the Column Properties window's 'Default Value or Binding' property, how do you specify an empty string?

Thanks.

View 5 Replies View Related

Default Empty String Value For Varchar Columns

Jun 4, 2008

Hi guys,
Is there a way to declare a default value of empty string '' for a varchar table column?
Thanks,Kevin

View 4 Replies View Related

Setting Default Value (concatenated String) Of Column Using UDF

Oct 23, 2005

Hello,

I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).

So basically the SysInvNum column for this row should read '200500015'

When I run a basic query using the CAST or CONVERT functions like this:

SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform

OR

SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform

I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.

I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:


CREATE FUNCTION GetSysInvNum()
RETURNS varchar
AS
BEGIN
DECLARE @maxcaseid Int
DECLARE @sysinvnum varchar

SELECT @maxcaseid = max (caseid) from caseform
SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid
RETURN @sysinvnum
END


The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".

Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?

Thanks in advance

'Wale

View 10 Replies View Related

How Do I Change The Default Collation String Of An Installation

Mar 22, 2006

HiWhen we installed SqlServer2000 we left the default collation name(Sql_Latin1_General_CPI_CI_AS).The user defined databases we created afterwards were defined with adifferent collation name in order to be able to accept the character setwe use, Hebrew.We are looking into switching DTSs that we use to copy data from ourmain system , that uses an Ingres database, into OSQL scripts. Althoughthe DTSs successfully copy the Hebrew letters when I copy data with OSQLit comes over as jibberish.After looking into the matter I came to the conclusion that while DTSrefers to specific databases and uses the destination database'scollation name , OSQL refers to the remote server and destination serverand therefore uses the collation name of the server and not of thedatabase. In order for it to successfully copy the Hebrew I need tochange the default collation name of the installation.Is "rebuild master" the way to do such a thing ? (this is a productionserver so we are wary of doing a "rebuild master")Has anyone else run into similar problems when transfering data betweenservers using OSQL ?ThanksDavid Greenberg

View 3 Replies View Related

DB Design :: What Is Default Database Value For Empty String

Sep 10, 2015

What is the format for the SQL Server empty string default value?

View 4 Replies View Related

Do Uploaded Reports Default Paths Not Usable Url String?

Mar 27, 2008

I am trying to send a parameter to an uploaded report, It's default path is:

http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report

I tried:

http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report&rs:Command=Render&PONo=15818

It did not run and when I manually added the parameter it changed to:
http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report&rs%3aCommand=Render&PONo=15818

I also tried to change the properties parameters settings has default unchecked and prompt unchecked

do I need to place the file in a folder on the server instead of uplading it?


View 1 Replies View Related

‘default To Local Cursor’ Causes Errors In String-built Declares

Sep 20, 2000

Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

View 1 Replies View Related

String Or Binary Data Would Be Truncated When Using Default Column Definition

Nov 22, 2007



When I set a column to have a default definition that uses a UDF, I am receiving the "String or binary data would be truncated" error.

The UDF:




Code BlockALTER FUNCTION GetDefaultClientTier
(
@ClientAssets decimal(15,2)
)
RETURNS char(1)
AS
BEGIN
DECLARE @Result char(1)
-- Get the first result in case of overlaps.
SET @Result = CAST((SELECT TOP 1 ClientTier FROM ClientTiers WHERE @ClientAssets BETWEEN ClientAssetsFloor AND ClientAssetsCeiling) AS char(1))
RETURN @Result
END





ClientTier is defined as char(1) in the table. I simply have (isnull([dbo].[GetDefaultClientTier]([ClientAssets])),(null))) as the definition. I can't use a computed column because I the values need to be editable. When inserting with SSIS, the insert works fine but the column has a value of null for each row.

When putting a character as the default (like 'A') the insert works fine.

The cast is there only because I have tried everything I can think of to get around this.

Is there something simple I am overlooking?

(SQL2005 SP2)

Thanks!

View 3 Replies View Related

Default To NULL Instead Of Blank/empty String During Flat File Import

Aug 15, 2006

Hi,

In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.

The table may contain column that are NULLable BUT there is NO DEFAULT for them.

If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?

I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:

1- set the DEFAULT(NULL) for EVERY column that needs this behaviour

2-set up some Derived Column option in the package to return NULL if the value is missing.

Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.



Thanks

Anatole

View 9 Replies View Related

STRING FUNCTION

May 14, 2001

Hi!

Is there any fucntion avaible in SQL for search a character position in a string.

e.g. 'ABC-DEF'

- is located at the position of 4

I tried the charIndex, Is it same as Instr in ORACLE. Is there any INSTR function available

View 1 Replies View Related

A String Function

Aug 25, 1999

does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4

View 3 Replies View Related

Right And String Function

Jun 21, 2001

I need to take the character from a text field. I need the character which
is the second one from the end(right). Like out of '12345' or '99821'
I would need the 4 from the first and the 2 from the second. Can I combine
a string and a right statement to do this?
Thanks

View 4 Replies View Related

SQL String Function Help

Mar 13, 2006

Here is the scenario.

I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number.

So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was.

The way I am going about getting the next sequential number is the following:

SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1

This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement.

I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value.

Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly.

I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking.

Any help or guidence would be greatly appreciated.

View 5 Replies View Related

String Function

Jun 23, 2006

Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn.
i checked few string functions but no use

any help appreciated

View 5 Replies View Related

SUM Function For String????

Aug 16, 2007

hi kristen or anybody there,

is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values.

data details:
table 1
pk_transactionid transactiondate and so on
1 1/1/2007 *********
2 1/2/2007 *********
3 1/3/2007 *********

table 2
pk_rowid fk_transactionid description
1 1 record1
2 2 record2
3 3 record3
4 1 record4
5 2 record5
6 1 record6

result set should be like this
pk_transactionid transactiondate description --> this is concatenated
1 1/1/2007 record1, record4, record6
2 1/2/2007 record2, record5
3 1/3/2007 record 6

thanks

SlayerS_`BoxeR` + [ReD]NaDa

View 12 Replies View Related

String Function

Dec 19, 2006

select distinct(f.agency+y.year+s.type) as SDNFIn this select clause, I try to use the last 2 digit of year for y.yearvalue. which function should i apply to it?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved