actually I am using workbench and sqlyog for browsing the database.
executing insert or delete, ExecuteNonQuery return the expected number of rows to be affected on the console, but when i look on the database the affected rows is not there. even i will execute directly it to workbench or sqlyog i got the same behavior.
COD surname name CODCAU from from_time to to_time period
1 ROSSI MARCO 301 19/09/2005 0.00 23/09/2005 0.00 p1
1 ROSSI MARCO 301 09/12/2005 0.00 09/12/2005 0.00 p2
1 BIANCHI FABIO 301 12/01/2004 0.00 16/01/2004 0.00 p1
1 BIANCHI FABIO 301 02/04/2004 0.00 02/04/2004 0.00 p2
1 BIANCHI FABIO 301 02/05/2004 0.00 10/05/2004 0.00 p3
then to pivot:
--concatenate to create a period on unique column...SELECT surname,name,COD,from+' '+from_time+' '+to+' '+to_time asdate,period
) as s
--pivot aggregate by the columns different by 'date/period' -> (surname,name,COD)
FOR period IN ([p1],[p2],[p3])
Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database DevelopmentOLAP on the SPSQLCLSTR server. Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server . ; 08001.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DevelopmentOLAP', Name of 'Timesheet Line Status' was being processed.
Server: The operation has been cancelled.
The User/Account has dbo access. It creates the database but then doesn't seem to be able to do anything after that?
To err is human to really mess up you need a computer
As title says, I would like to know how to return the primary key(the column is always called id) when I perform an insert statement and the database complains about an unique constraint being violated. All my insert statements are in stored procedures. Would like that same stored procedure to return the value of that primary key column of the row that is being violated.
I find that a cleaner solution than responding to an exception in my application by searching the table for the unique values. This would be ok if it were one or a few tables, but not when you got many.
PS: using MSSQL 2012
Let me clarify:
Let's say I have a table called address with fields: id, street, streetnumber, busnumber, placeid.
id is the primary key of that table.
Now I insert a row in that table with data in the following fields: street, streetnumber, busnumber, placeid. id is generated by a sequence, but that part doesn't matter. The unique constraint is about those 4 fields.
Suppose I want to insert 2 people who live at the same address, but while this is unknown to me.
Instead of getting a unique constraint warning, I would like to get the id of the record that matches the data I'm trying to insert. I've thought about doing it in the same stored procedure, but I don't know that much about errorhandling inside a stored procedure. I am trying to avoid to having to handle it in my application.
Could you help me out with that?
My point is to link the two people to the same address, instead of making 2 duplicate addresses.
Checking the table every time I make an insert, makes the process slower than if I were to do it after, cause then I only need to check the database twice when the exception occurs instead of twice every time I do the insert.
I would like to know how to return the primary key(the column is always called id) ...All my insert statements are in stored procedures
Write a lot of code - duplicating every constraint that you have. In every stored procedure that does an insert. So if you have a unique name constraint in your customer table then your insert for the customer first checks to see that the name is unique. If it isn't then it throws an error. If it is then it proceeds to doing the actual insert. Course you might want to create a table transaction as well since someone could insert a different row with the name as well.
Naturally this isn't efficient. Nor performant. Applications should be coded to prevent this in the first place rather than relying on the database to provide error notification (versus error prevention.)
Here is one of the situations I have:
I insert a person and an address the person provided. Addresses are in a seperate table.
So brief table layout:
person table: firstname, lastname, addressid
address table: id, street, streetnbr, busnbr, placeid. There is a unique constraint on the combination of all 4 fields that aren't the id.
Person A comes along to one of my client's employees and gives his info and a certain address.
Person B comes along to another one of my client's employees, gives his info and the same address as person A.
here is what currently happens in my app:
app receives sqlexception with the error saying there was a unique constraint problem and I respond to it in app by doing a sql statement that selects the record that matches the unique address.
Here is what I would like to happen:
stored procedure for Address tries to insert the address, finds out it already exists(throws exception in db) cause of the unique constraint and instead of supplying the id of the newly inserted record, it supplies the id of the existing record. Imo, this is far faster than what I have now, as the problem is handled before it leaves the stored procedure.
Problem is, I know nothing about error handling in SQL Server.
Here is my current sql statement for my SP:
ALTERprocedure [dbo].[AddressInsert](@streetvarchar(255), @streetnumbervarchar(255), @busnumbervarchar(255), @placeidbigint, @newidbigintoutput) asbeginset@newid = next value for dbo.baseidseq
insertinto [Address](baseid, street, streetnumber, busnumber, placeid) values(@newid, @street, @streetnumber, @busnumber, @placeid) end
What statement(s) do I add to return the id of the row that matches the parameters provided?
I assume I have to add something like select * from address where street = @street, etc. But what else?
Last Visit: 31-Dec-99 18:00 Last Update: 7-Aug-22 21:30