I am writing an inventory / asset tracking application, backed by MSSQL 2005 and need to allow end users to add/remove custom fields to track. What is the best way to go about this?
I thought about modifying the table structure at run time, but quickly decided that was a terrible idea. How does say, Share Point Services track custom columns?
Thanks
Joe
I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.
-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )
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.
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_Name
hi all(happy raksha bandhan day)we have one of Automation software for sales running for acustomer.He was cool for the first month of product, but later poppedwith adding some extra fields.no problems i added in database , put aseperate code in my application for that field.but later every 2 dayshe was adding new fields.....so i thought to put in some inbuilt logicuser defined fields.second his user defined fields are like shoudl benumeric,string , length validation.But do not know whats the best wayto acheive this.I mean should i make seperate table where i definefield name, data types , validation and then in my application code ageneral logic for it in my application code.Any one has prooven designfor user defined fields,just thinking if i can even get a idea.....When i die i die programming........
View 2 Replies View RelatedIs it possible to set up a report so that there's a column where the user can insert data of their own, which then gets saved to the underlying database?
What I'm thinking of is the ability to have a drop-down box at the end of each row of data in the report, whereby the user can select an option to specify the action they have taken in response to the data. Is this possible, and if so, how exactly would I achieve this?
Hi everyone,
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 this correct???
Cm
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?
Thanks for any hints!
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement
SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu,
user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id,
user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex,
user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group
SET user_group.description = Consultants.description
FROM user_group
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
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?
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View Related
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
Do I have other option beside using IF..ELSE IF? TIF
-- GET INFORMATION OF THE JOB
DECLARE @JOBIDAS Char(10)
DECLARE @VRUSERVICEHRSAS Decimal(18,2)
DECLARE @VRUSERVICEMINAS Decimal(18,2)
DECLARE @BILLEDFLATAS Decimal(18,2)
DECLARE @BILLREGRATEAS Decimal(18,2)
DECLARE @MIN_HRSAS Decimal(18,2)
DECLARE @COUNT_GREATER_MINTinyInt
DECLARE @COUNT_LESS_MINTinyInt
SET @VRUSERVICEMIN = 46
SET @BILLEDFLAT = 0
-- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
IF @BILLEDFLAT = 0
BEGIN
IF @VRUSERVICEMIN BETWEEN 0 AND 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN = 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN = 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN = 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN > 45
BEGIN
SET @VRUSERVICEMIN = 1
END
END
PRINT @VRUSERVICEMIN
Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?
Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses' and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in advance!
I test my the now function and it is getting the right date. When I try to send that to the sql database I have it turns it into 1/1/1900. Does anyone know why this is happening, I have tried everything Here is my code:
sql = "Insert into tblguestbook(date, name, city, state, email, Url, Comments)Values ('"
sql = sql & Request.Form(Now) & "','"
sql = sql & Request.Form("nametxt") & "','"
sql = sql & Request.Form("citytxt") & "','"
sql = sql & Request.Form("statetxt") & "','"
sql = sql & Request.Form("emailtxt") & "','"
sql = sql & Request.Form("urltxt") & "','"
sql = sql & Request.Form("commentstxt") & "');"
Hi,
I have a table with one field set at nvarchar (4000)
This is sometimes not big enough and I get an error that the max row size has been reached.
How do people handle the error when the max row size is reached and gracefully inform the user?
Also, should I really be using Ntext instead, would this be better - is there a performance penalty?
Much obliged.
RG
Hi,
I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).
If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?
Thanks
I don't know what's wrong with SQL2000 setup, the problem is:
whenever I execute a query with a date/time such as 02/02/200,
SQL give me an error message saying that the date is "Out of Range".
Any ideas, thanks in advance.
I'm having trouble with something and I was hoping that you could point me in the right direction.
Here's the scenario:
I have a VB application that clients use to add records to a SQK 2K DB. The info they have added that day is shown in a grid. They have the ability to edit items in the grid, and then update those changes to the database. The problem is that sometimes they change the values to something they shouldn't. To combat this I've started experimenting with check constraints. In query analyzer I test the constraint by trying to update an entry to an 'illegal' value. When I do this, I get an error saying: "Server: Msg 547, Level 16, State 1, Line 1" and the change is not made. What I'd like to do is to give the user a dialog box notifying him of the error. Is there a way to have a sub-routine or stored procedure be triggered when a message of this type is generated?
My specs are: W2K pro clients, SQL2K on an NT4 sp6a server. Application is written in VB6.
Any help is greatly appreciated.
thanks,
-scott
I have a select statement that works, but I know there has to be a better
way( I apologize for being TSQL brain dead today). Here is the statement
SELECT PATIENT_ACPT_STATUS, DISP_NOTES, RECORD_ID, modified_by, last_name, ddate
FROM PATIENT_MEDICATION_DISPERSAL_
Where (ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = CURRENT_USER) and
rec_status = 1) or
(ddate = convert(char(10),getdate(),101) and
(PATIENT_ACPT_STATUS = 1)) OR
(ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = 'open'))
Hi All,
We have got a new HP server and SQL is preinstalled, But when we are trying to start SQL, it gives an error "The handle is invalid".
We tried to reinstall the SQL, but , being a server t doesn't allow to do so
Please assist.
regards,
Jatin
I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".
My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.
However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.
Is even using a Trigger to Stored Proc the best way?
What about Transaction Replication - which I've never attempted - is that a better way?
Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.
Thanks as always for your input,
Peter