we have one of Automation software for sales running for a
customer.He was cool for the first month of product, but later popped
with adding some extra fields.no problems i added in database , put a
seperate code in my application for that field.but later every 2 days
he was adding new fields.....so i thought to put in some inbuilt logic
user defined fields.second his user defined fields are like shoudl be
numeric,string , length validation.But do not know whats the best way
to acheive this.I mean should i make seperate table where i define
field name, data types , validation and then in my application code a
general logic for it in my application code.Any one has prooven design
for user defined fields,just thinking if i can even get a idea.....
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I would like to get some clarification about what these two concepts are.
This is what I think: lets say that you want the user to choose two columns to add together and then that produces a calculated result. This would go in a computed field???
And user-defined functions are functions whereby users have for example to which which two columns they were going to allow to be processed for the calculation.
Is there a syntax to create indexes on user-defined type's fields and methods? Can I index UDT-fields?
I tried but only get syntax error.
Code Snippet
-- put an index on the picture luminance
CREATE INDEX myIdx ON myTbl(picMetaData.Brightness) -- !! error
GO
Error message:
Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
According to books online (BOL), section User-defined types requirements says In the SQL Server 2005 RTM version, CLR UDTs with user-defined serialization were allowed to have their fields indexed as part of non-persisted computed columns or views. In such situations, non-deterministic UDT serialization/deserialization could lead to index corruption, and therefore has been removed from SQL Server 2005 SP1. In SQL Server 2005 SP1, UDT fields must use native serialization or be persisted in order to be indexed. Any existing indexes on UDT fields should continue to function as before.
What are BOL trying to say about index on UDT fields?
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
- First_Name - Last_Name - Email - User_Field_2 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
I'm strugeling for quite soem time and hoping to get your assistance:
facts:
We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record
sample record 1:
David | 1 | Rodeo Drive | 456 |
sample record 2:
Jeff | 2 |
sample record 3:
John | 3 | Sunny rd | 111 | Marvell str | 6
expected output in the table:
David | 1 | Rodeo Drive | 456
Jeff | 2 | null | null
John | 3 | Sunny rd | 111
John | 3 | Marvell str | 6
question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?
I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays. It works perfect except for one issue. It doesn't check to see if today's date is a holiday. I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)" It then moves ahead two business days and returns that date. Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date. I really don't want to rewrite the whole script . Code--------------------------------------------------------- SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO --DROP FUNCTION GetStartDate --declare function receiving two parameters ---the date we start counting and the number of business days CREATE FUNCTION GetStartDate (@startdate datetime, @days int) RETURNS datetimeASBEGIN --declare a counter to keep track of how many days are passingdeclare @counter int /*Check your business rules. If 4 business days means you count starting tomorrow, set counter to 0. If you start counting today, set counter to 1*/set @counter = 1 --declare a variable to hold the ending datedeclare @enddate datetime --set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @startdate /*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days BEGIN --for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate) --If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from tFederalHoliday where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END --end the while loopEND --return the end dateRETURN @enddate --end the functionEND GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO ---------------------------------------------------------------------------------------------
I've a view in which one of the fields were DATEDIFF(day,contract date,received) AS AgeOfAccount. I changed it to DATEDIFF(day, m.received, CASE WHEN m.clidlp > m.clidlc THEN m.clidlp ELSE m.clidlc END) * - 1 AS AgeOfAccount. When I execute the view I'm getting an error. But the results are getting displayed properly. That's what's puzzling me. Could someone tell me what the error is.
The following errors were encountered while parsing the contents of the sql pane. Your entry cannot be converted to a valid date time value.
I have a search returning many 'car' records (regno, mileage, color, etc)
In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.
Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.
I have written a function as such (with some help!):
ALTER Function getAccs(@regNo varChar(20)) RETURNS varChar AS BEGIN Declare @List varchar(1000) SELECT @List = COALESCE(@List + ', ', '') + accessory FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno return @List END
I was hoping that I could simply use this in the 'car' SELECT statement.
ie:
SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList FROM tableBla etc
I'm not even sure if the function works - How can I test in SQL analyzer?
Hi everyone, I am tring to pass acomma delimited string to a function and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this Select * from tableA where userID in(1,2,3,4) It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA My function looks like this. How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1)) RETURNS @results TABLE (id varchar(100)) AS BEGIN declare @i1 varchar(200) declare @i2 varchar(200) declare @tempResults Table (id varchar(100)) while len(@p_text) > 0 and charindex (@p_Delimeter, @p_text) <> 0 begin select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1) insert @tempResults select @i1 select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text)) end insert @tempResults select @p_text insert @results select * from @tempResults return END Thanks
I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.
I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.
I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.
all schedulled task on server failed for 18450 Process Exit Code 1. ...t Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 18450: [SQL Server] Login failed- User: sqlexec Reason: Not defined as a valid user of a trusted SQL Server connection. Any information on how to solve it
Is it possible to change the owner of a User Defined Dataype? I've recently had to change the owner of one of my databases and the tables from a specific user to 'dbo'. The UDD's are the only objects I am unable to change the ownership of.
Any suggestions anyone? Thanks for any advice! Toni Eibner
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?
CREATE FUNCTION fnShipments () RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10)) AS BEGIN INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID) SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth =
(SELECT MIN(EffectiveMonth) FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)), (SELECT TerritoryID FROM vwAccountTransfers WHERE Shipments.AccountID = vwAccountTransfers.AccountID AND vwAccountTransfers.EffectiveMonth Is Null ) ) AS Territory FROM Shipments
ORDER BY Shipments.AccountID, Shipments.DateInvoice; RETURN END
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00' I want the UDF to do the following for me Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00') So the UDF will return to me 20. But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00 until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated 1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.
I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.
This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.
Are you with me so far?
So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.
I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.
It works, I get the results that I want.
But, alas, the performance has taken a search severe hit.
I am running a procedure that declares a table with a user defined variable called ZipCodes8000. This datatype is on the systypes table as a valid datatype that we created. Here is the code and the error that is returned. Can anyone help me understand why I receive the error that I do.
Code:
DECLARE @SortPlanBinZipCodeRanges TABLE ( SortPlanBinZipCodeRangeIDINT IDENTITY (1, 1) NOT NULL, SortPlanBinID INT NULL, SortPlanID INT NULL, BinTypeID TINYINT NULL, BinFlagTypeID TINYINT NULL, BinNumber INT NULL, ZipCodeRanges ZipCodes8000 NULL )
error: Msg 2715, Level 16, State 7, Line 1 Column or parameter #7: Cannot find data type ZipCodes8000.
I've been doing some reading on UDT's and have a question...
It was suggested in one of the pieces I read that you could create a UDT for, in their example, cities. And every table that had a reference to city could share this datatype. Now, my initial thought was "wow, what a great idea! I wouldn't have to remember the exact datatype for my primary keys (was it a char(5) or char(6)?) and have a "central depository" for my key datatypes.
So the first question is; what are the disadvantages of such a design?
And the second is; How do you update a UDT? If business requirements change and udt_city needs to be changed from varchar(30) to varchar(60), for example, what would be the way of echoing the change thoughout your database?
My gut reaction for the answers are 1) performance will decrease as effectively the dbms has to "parse" every insert/update to a UDT field in a different method.
2) create a new udt and alter any tables referencing the old one before dropping it.
Is there a way that I can create a user defined variable in my database? I have a fairly large chunk of xml that I would like to store in the database that can be updated by clients ( in rare cases ). I could use other mechinisms to allow access to it, but all other application related information is stored nicely in this one place, I hate to vary for this single piece.
When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code: CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10)) RETURNS CHAR(10) -- DROP FUNCTION GetLastShipDate BEGIN DECLARE @v_last_ship_date CHAR(10)
SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '') FROM Asn,Asn_Do WHERE Asn_Do.Asn_Number=Asn.Asn_Number AND Asn_Do.DO_Number = @DO_No AND Asn.ASN_STATUS in ('SENT','RESENT')
Iam trying to create a geo-database. For that i need to have a spatial data type called polygon which takes in values as int. so it can be declared as: POLYGON(0 0, 100 0, 100 100, 0 100, 0 0)
How do i do that. plz help.
--- Iam not real any more Iam just an Illusion ---
Hi, Iam new to sql server. I want to create a user defined function to get date in a year(starting day of a week-i.e sunday date) when we give week number as argument. can any body help on this issue?
Hi, Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.
Is there a way to call a SQL user-defined function in a DTS, perhapsin an ActiveX transformation? I want the destination table to have avalue that was modified by a pre-defined function.-Noelle
