I have a table in which a non-primary key column has a unique index on it. If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
I am getting this error even though there should be data present. When I test the SQL statement in query analyzer, it returns 1 row. <%@ Import Namespace="System.Data.SqlClient" %><%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Blog" %> <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim strConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("currentConnection").ToString 'currentConnection is defined in web.config and works when used in other pages on the siteDim dbConn As SqlConnection = New SqlConnection(strConnection) dbConn.Open() Dim strSelectCommandFirstEntry As String = "SELECT MAX(blogEntryId) as maxID FROM site_Blog"Dim cmdFirstEntry As SqlCommand = New SqlCommand(strSelectCommandFirstEntry, dbConn)Dim rdrFirstEntryData As SqlDataReader = cmdFirstEntry.ExecuteReader()Dim strFirstEntryData As Int32 = rdrFirstEntryData(0) 'error occurs here, i have also tried rdrFirstEntryData("maxID") with same errorrdrFirstEntryData.Close()dbConn.Close() </script> When debugging this code and stopping on this line: Dim strFirstEntryData As Int32 = rdrFirstEntryData(0) rdrFirstEntryData has the following values:hasRows = True, FieldCount=1, Item=In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. and when i click the refresh button...Item = Overload resolution failed because no accessible 'Item' accepts this number of arguments. I suspect that the note for "Item" is my clue to the source of the problem, but I don't know what it means. Please, help.
I am having a problem implementing constrained delegation for SSRS. I have followed the (very good) instructions located here: http://sqlblogcasts.com/blogs/stevechowles/archive/2007/06/08/reporting-services-2005-for-the-dba-iis-security.aspx
I have chosen the option of running the application pool for SSRS under a domain user account. This is the same account that I use to run the SSRS service.
I have the authentication providers for the site set to "Negotiate,NTLM".
I also made sure that the application pool user account has rights on the ReportManager and ReportServer directories.
If browse to the URL while logged on to the SSRS server then I am able to access the site My problem is when I try to access the site from anywhere but locally on the SSRS server:
I get a logon prompt if I try to access the SSRS URL from a different workstation. After three tries to login I get: "You are not authorized to view this page". Even with an account that is local admin on the SSRS Server. If I set the authentication providers for the site to "NTLM" then I am able to access the site from a different workstation but of couse constrained delegation does not work. Have i overlooked something? What could be causing the login prompt?
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. Â To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.Â
This return me a data set containing rows where fieldname is exactly like ‘*value *’. The expected data set is set of rows where fieldname=”value 1”or fieldname=”the value 1” or fieldname=”dgdgvalue 04121”, for example.
Here JBuilder considers the character ‘*’ not a special one (idem for the character ‘?’).
This problem is also faced with any other programming language (If I don’t make an error!!)
Please help me to get returned the right data set returned.
I get this error when, i try to set a parameter for user input, it doesnt matter what i type in, it says this:
<<Query (1,408) The Restrictions imposed by the constrained flag in the STRTOSET function were violated>>
what exactly does this mean? and how do i fix it?
My parameter is for dates, but i have the datetype set to "String", when i change it to" DATETIME" , i get the same error when i select from the calendar. So far the only date option that is working right now. Is a long drop down list, that could go for days at a time. I just want users to be able to input dates, such as "Jan 9, 2000" or 10/10/1999. What can i do about this?
I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?
hi, I have a table that contain(custname,ordno,ordchange,ordlocation,reaso n) I want to determine the following: how many orders per customer,how many orders changed per customer,how many order location per customer, what reason for change per customer,
here what I wrote as a query, tell me if I am right. I thank you for your help
select custname, COUNT(DISTINCT ordno) , COUNT(DISTINCT ordchange) , COUNT(DISTINCT ordlocation) , COUNT( reason) from customers a, orders b where a.custname =b.custname group by custname
Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test @ContractCode varchar(10), @dtFrom datetime, @dtTo datetime, @Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0 set @option = 'c.contractcode' else set @option = 'cb.employercode'
select c.claimsno, c.attenddoctor, c.patientcode, p.sex, cb.employercode into #SavingsData1 from claimsa c inner join Patient p on c.patientcode = p.patientcode inner join claimsb cb on c.claimsno = cb.claimno where @option = @ContractCode and c.dateentered between @dtFrom and @dtTo and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
I'm having a problem in using MS SQL 2005 as an ODBC in Powerbuilder 10.
My query which seems to work in Powerbuilder 5 does not work in Powerbuilder 10.
When I execute my query, I got this message "multi-part identifier alias.fieldname could not be bound. I don't know what's seems wrong. I even tried putting no alias but still it won't work..
help me please...
select sc.transaction_code, case when :as_transtype = '' then drh.so_number else case when :as_transtype = 'SI' then drh.si_number else case when :as_transtype = 'DR' then drh.dr_number else :ad_documentnumber end end end document_number, case when :as_transtype = '' then drh.so_date else case when :as_transtype = 'SI' then drh.si_date else case when :as_transtype = 'DR' then drh.dr_date end end end transaction_date, pcd.item_number, pcd.serial_number, pcd.serv_tag, pcd.rev_no, space(10) supplier_name, c.customer_name, pcd.pcc_number, pcd.supplier_refdocno, pcd.ref_date, pcd.original_tag, pcd.item_description from pcc_detail pcd inner join pcc_header pch on pch.pcc_number = pcd.pcc_number inner join delivery_receipt_header drh on pch.so_number = drh.so_number inner join stock_card sc on sc.so_number = drh.so_number and sc.item_number = pcd.item_number inner join customer c on drh.customer_code = c.customer_code where sc.transaction_code in ('DR', 'SE') and sc.transaction_type <> 'RS' and sc.item_number like case when :as_itemnumber = '' then sc.item_number else '%' + :as_itemnumber + '%' end and pcd.serv_tag like case when :as_servicetag = '' then pcd.serv_tag else '%' + :as_servicetag + '%' end and pcd.serial_number like case when :as_serialnumber = '' then pcd.serial_number else '%' + :as_serialnumber + '%' end and pch.customer_name like case when :as_customer = '' then pch.customer_name else '%' + :as_customer + '%' end and :as_transtype in ('SO','SI','DR','PC','') and sc.transaction_code = case when :as_transtype = 'SI' then 'SE' else 'DR' end and ( pch.so_number = case when :ad_documentnumber = 0 then pch.so_number else :ad_documentnumber end or pch.si_number = case when :ad_documentnumber = 0 then pch.si_number else :ad_documentnumber end or pch.pcc_number like case when :ad_documentnumber = '' then pch.pcc_number else '%' + :as_documentnumber + '%' end or pch.dr_number = case when :ad_documentnumber = 0 then pch.dr_number else :ad_documentnumber end ) and pcd.item_description like case when :as_itemdesc = '' then pcd.item_description else '%' + :as_itemdesc + '%' end
Is it possible to rename a field-name from ( tabular powerpivot ) model to a different Presented-name for the end user ?
usualy the names in the source model are Understandable to the developer , and its Custom to his understanding and nees , but for the end user its not - its not understandable , to long and so far form the regular terms in his Business , and also the language Might be non-English..
I am looking for advice on how to handle the following situation.Data being saved to a database table. Unique index on column in table. If the constraint is violated, what is the best way in alerting the user? Catch the exception and display error message to user? How do I know the exception is because of a unique index violation?
How can I assign or retrieve a unique ID from a msSQL table where there isn't a unique column.
Is there a hidden 'raw id' i can retrieve from msSQL for each record?
I'm not allowed to change the current database at all, but the developers who created it managed to use it without Unique IDs.... which doesn't help me coming from a mySQL background where UID's are kind of mandatory.
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list. Can anyone shed any light on the problem? Thanks.
hi, In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote ( QuoteRevision_ID, DocumentSeq, DocumentDisplayName ) SELECT S.QuoteRevision_ID, IDENTITY (INT,1,1) AS DocumentSeq, T.Name, FROM Symp S INNER JOIN Trilogy T ON SQR.tril_gid = FQR.tril_gid
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
I just started with SQL Server Express and was playing around with the Management Studio.
I created a query to make a column unique:
ATLTER TABLE Table_Name ADD UNIQUE (Column_Name)
That worked perfect, if I fill in the same data in the next row of that column, I get an error as expected. But, how do I remove the UNIQUE property on that column? I can find it in the column properties within Management studio, neither I can't find the command to undo this action.
This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this: id text1 text2 int varchar varchar
1 abc ghi 2 abc ghi 3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well. For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
I am having problems with unique contraint, say I have a table called customer create table customer ( id int identity(1,1) not null, name nvarchar(50) not null, id_card_no nvarchar(20) unique, ); so when I do mutilple null inserts insert into customer(name, id_card_no) values("Customer 1", NULL); insert into customer(name, id_card_no) values("Customer 2", NULL); I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
I am testing out the performance benefits of utilizing include column(s) to avoid RID lookups and so far it looks very promising. I am able to reduce the cost of the query significantly and execution time is very good. However my non-clustered index is part of the primary key constraint and I am wondering if it is possible to create the include column as part of the primary key. Or do I have to drop the pk constraint and create unique non-clustered index with include column?
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition: Column Name Data Type Allow Nulls -------------------------------------------------------------------------------------------- Column1(PrimaryKey) int No Column2 nvarchar(MAX) No
