|
You are not setting your variable:
set @variable=scope_identity() <br />
IF @@ERROR <> 0 <br />
BEGIN -- Rollback the transaction <br />
ROLLBACK <br />
Select <big>@variable =</big> -9 <br />
END<br />
COMMIT<br />
select @variable
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am saying it is committing the records not giving any error and rollback. It is not returning the value in case of committing the records.
|
|
|
|
|
Without the rest of your code its kinda hard to say then.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi All,
I have an existing database with a table schema that I need to change. However it seems that there is a conflict in the conversion. I'm trying to change a ntext column to a nvarchar column. I'm using C# Express 2008.
I've tried doing it on an empty table with the connection closed but I still see this error. What's the best way to tackle this schema change? I don't like the idea of having to recreate the table from scratch. As I understand it there is no way to script an SqlCe database ...
Any pointers would be great.
Thanks,
|
|
|
|
|
wow, you still can't do this in sql2008?!
One approach is to create a whole new column with the required type, then copy the content from the old column into it & delete the old column. Of course, the column names cannot be the same, so you'll need to use sp_rename at some point.
Unfortunately, this can get very tricky when there are other constraints in the database that rely on your column (default value is probably most relevant here). Also, be aware of the 4000 character limit of nvarchar.
There's lots of tools out there that will assist with this kind of change. We use xSqlObject here, which is excellent (www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx[^])
Best of luck!
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for this. I came to the same conclusion. Also I'm using SQLCE ... is the nvarchar limit 4000 characters for this version? I think it's actually 255 ...
|
|
|
|
|
A quick google suggests the limit is still 4000... don't know though, i've never used CE
|
|
|
|
|
A Real Work at Home Opportunity as an Independent Advertising Executive. We offer home workers the opportunity to earn extra money from the comfort of their own home. Visit Us at http://www.clicknearn.net/idevaffiliate/idevaffiliate.php?id=2449_9
|
|
|
|
|
|
Hi, a lot of PTC sites on net , you can post your ads in the right place.
Thanks
|
|
|
|
|
Hello
I have two Database.
DB1 And DB2 there are tbl1 And tbl2 two tables in corresponding database.
if any value change in tbl1 table the corresponding value will also update in tbl2 tables.
Can any one help me in this regard.
Thanks in advance
Sujit
|
|
|
|
|
|
hi all,
i am trying to install SQL express 2005 from following code
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=mypassword DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)
it works well for first time. When i install from above code and uninstall from Control panel and then once again i execute the above code then it does not install the SQL server Express
I think Uninstall from control panel does not completely remove the SQL server Express.Its require some more steps
Whether anybody can suggest me that how can i completely uninstall the SQL server Express from my machine ?
|
|
|
|
|
How i can show relationship between tables as a diagram in MYSQL
|
|
|
|
|
Hello All,
I've been googling and based on many articles I belive what I'm asking of sql2000 triggers is not possible for the particular type of error i'm getting.
I am trying to integrate with an existing application on sql 2000 without affecting it's behaviour. When it inserts/updates/deletes in a few tables I need to perform an I/U/D in a sql 2005 DB through a DB link. This is all set up fine, as well as the trigger to do the insert. I need to be able to put error handling in the trigger so that it does not affect I/U/D of the existing product.
Right now, MSDTC is having a fit because I don't have it configured properly on purpose. I did configure it correctly and then reverted so it would raise errors so I could test error handling.
Nothing I've tried so far will let me handle this error in an insert trigger on sql 2000:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
Execution stops immediately so I can't even get to an "IF @@ERROR" statement and the transaction is rolled back. I know how to fix this error but I need to be able to handle this type of error so I can use xp_logevent or something similar to monitor. Is it possible - Any ideas?
Does anyone know of a way in sql 2000 to handle this type of error in a trigger on sql 2000?
|
|
|
|
|
Hi Guys,
I am having problem when running the Following SQL script.It takes Pretty long time to return result.
I am having about 500,000 records in my database.
select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators
On CR_StudioOperatorID=SO_ID
where datename(month,cr_callstart)='April'
and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31')
group by SO_name desc
Is there any way i can write this script?
Thanks you very much.
|
|
|
|
|
In SQL Server Management Studio, place this query in the workspace and click on the icon that has a description of "display estimated execution plan". This will give you an idea of where there may be table scans and where you might be able to add an index to improve your performance.
You should also check to see if your statistics are updated on the tables and indexes involved in this query before running the "display estimated execution plan" as described above.
David
|
|
|
|
|
Thank you so much.It works
|
|
|
|
|
Hi there I am trying to update a field in our contact table. I have a view which contains all of the contacts that i need to run the update for, my question is:
How do i update a contact table but only update the contacts that are in my custom view?
|
|
|
|
|
This example is based on the AdventureWorks database;
UPDATE HumanResources.Employee
SET Title = 'updateFromView'
WHERE EmployeeID IN (SELECT EmployeeID FROM HumanResources.vEmployee)
The select statement gets a list of ID's in the view. You can then update all the records in the table that have this particular ID. You'll need something to identify each record individually for this to work. If there isn't, then try to add the primary key to the view. If there is no primary key defined in the base-table, add an identity to fullfill this role.
Good luck 
I are troll
|
|
|
|
|
Hi,
I have a table which looks like the following.
OriginalTable
OriginalTable_Id(pk) OriginalTable_Name(unique)
---------------------------------------------------
1 NewColumnName1
2 NewColumnName2
...
I need to make it look like this table below dynamically:
NewTable
NewTable_Id(pk) NewColumnName1 NewColumnName2 ...
---------------------------------------------------------------
I know I need to create a new tempory Table (using tempDB?), but I don't know how to get the values in the table to be the new column names.
I have this at the moment:
CREATE TABLE NewTable
(
NewTable_Id INT PRIMARY KEY,
???
)
???:
Would it be something like:
SELECT OriginalTable_Name FROM OriginalTable
Any tutorials or advice would be greatly appreciated.
Thanks in advance.
hmmm pie
|
|
|
|
|
Have a look at pivot clause here[^]
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Brilliant, that's exactly what I was looking for thanks alot.
But how can I get the name of the columns dynamically.
eg.
The value is '198' so how can I make it the column name without knowing the value?
Edit:
Thanks again for your help.
Two minutes after posting this reply and Googleing 'Dynamic Pivots' I found this[^] which solved my problem.
Sorry for the inconvenience.
hmmm pie
modified on Wednesday, April 22, 2009 2:15 PM
|
|
|
|
|
Hi
I have a stored procedure that takes an XML as a parameter. The problem is that anyone could call it passing in any XML. How do i write the stored procedure so that the XML that it takes in as a parameter is based on a specific schema?
Thanks
Neil
|
|
|
|
|
Hi
I Made Setup from my C# program (that work's with Oracle 10g)
when I try to run this program - in different Computer, i got this error message:
unable to load dll 'OraOps10.dll'
what it can be ?
thank's in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
