|
You caqn not have more that one identity column per table. The way to handle what you are attempting may be to insert the new record, get the identity using the SCOPE_IDENTITY() function then updating the record with that value
|
|
|
|
|
If I have 2 INSERT statements in my stored procedure, one to add a member to the member table, and one to add the member's role to the MemberInRoles tables.
Let say the second INSERT fails, and I do a ROLLBACK TRANSACTION, will it remove the row added in the first INSERT statement?
How far does ROLLBACK TRANSACTION go? Will it reverse everything?
|
|
|
|
|
ma se wrote: will it remove the row added in the first INSERT statement?
It depends where you put the BEGIN TRANSACTION
ma se wrote: How far does ROLLBACK TRANSACTION go?
Back to where you put BEGIN TRANSACTION.
ma se wrote: Will it reverse everything?
It will reverse everything on that connection to the point that BEGIN TRANSACTION was called. Other users of the database are unaffected (unless they are reading uncommitted changes [aka dirty read]- in which case they are declaring they realise some of the changes they are reading may not be valid)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Ok thanks!!
When I do a COMMIT TRANSACTION, do I need to do it for both INSERTS, or does using it once commit both INSERTS?
|
|
|
|
|
ma se wrote: When I do a COMMIT TRANSACTION, do I need to do it for both INSERTS, or does using it once commit both INSERTS?
The COMMIT TRANSACTION will commit all changes since the BEGIN TRANSACTION. So, do it once after both inserts.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hey Colin!!
Thanks for all the information, it is greatly appreciated!!
So how long have you been in databases? You probably know everything there is to know on SQL Server. Hows it going with SQL Server 2005? Do you do any other databases?
Cheers.
|
|
|
|
|
ma se wrote: So how long have you been in databases?
Commercially? About 12 years.
ma se wrote: Hows it going with SQL Server 2005?
I've played with it a little, but I've not had a chance to work with it yet.
ma se wrote: Do you do any other databases?
To the same level as SQL Server? No. But I do know various other databases, but since I've been using SQL Server almost exclusively for the last 3 years it would take a wee while to pick some of it up again. However, most databases I've used have some varient of SQL so it isn't that hard to go from one to another.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I have another question.
I have a stored procedure that inserts the member, and a stored procedure that inserts a roles for that member. So when the member is added successfully, then it calls the other stored procedure passing it the parameters of memberID and roleID.
Now comming back to the COMMIT TRANSACTION and ROLLBACK TRANSACTION. If a ROLLBACK TRANSACTION is done now after this call, will it go and remove that record as well (which was inserted from the call to the "outside" stored procedure)?
|
|
|
|
|
i think Colin is the best one to answer SQL questions over CodeProject, but i can give it a try 
transactions can be seen for what you write on the database as an atomic statement. this means that when you begin a transaction, everything you write inside the transaction is actually buffered. when you commit the transaction, the database is physically altered by writing you datas. if you rollback the transaction, everything you did inside the transaction is lost, and the database state (as it was before your transaction) is not modified.
|
|
|
|
|
hi guys
i have question in SQL
select * from user where id='aaa' and pass='hello';
the problem is who can i make it case sensitive
like if in my table AAA exist it's different then aaa how can i select
the exacte word
Regards,
Charbel
|
|
|
|
|
hi,
First of all if your user table has an identity column PK so you can get the data by The PK.
Second here is a function that may come in handy
//For small a at the begining
Select * from user<br />
Where ASCII( id ) = 97
//for Big a at the begining
Select * from user<br />
Where ASCII (id ) = 65
ASCII Function:
Returns the ASCII code value of the leftmost character of a character expression.
|
|
|
|
|
Cast them to varbinary:
SELECT
*
FROM
user
WHERE
CAST(id AS VARBINARY(50)) = CAST('aaa' AS VARBINARY(50)) AND
CAST(pass AS VARBINARY(50)) = CAST('hello' AS VARBINARY(50))
|
|
|
|
|
Not an issue unless the database is set to be case sensitive. Other wise you can do this
select *
from user
where UPPER(id) = UPPER('aaa')
and UPPER(pass)= UPPER('hello');
|
|
|
|
|
Hi All,
I have the question about DataAdapter.Fill Method.
1. When I use Fill(DataSet), ADO.NET fills all data into DataSet with sql.
2. When I use Fill(DataSet, beginRecord, maxRecord, TableName), ADO.NET only fills maxRecord row into DataSet with the same Query.
But I wonder whether difference is in processing data of SQL Server when calling two methods:
1. When calling method 2, does SQL Server process more faster?
2. Or SQL Server process like method 1, it will send all data to ADO.NET and then ADO.NET filter neccessary rows?
Thanks,
Tran Thai Nam
|
|
|
|
|
Hi,
When I fetched 700000 record from the SQL db table from analyse it took somewhere aroung 36 second when i tried same thing using SQL Dataadapter (Methos 2)where my Select statement wes "Select * From Table" with maxRecord 700000 it took almost same time.........even in SQL Profiler it runs the same query............it seems like sqlDataAdapter send some hidden command to SQL server (Just like select Top N *..........
Regards,
Ritesh
|
|
|
|
|
I think you should try to use method 2 with query "select * from table" and maxRecord=10(just get 10 record) to see difference between two methods.
I want to know clearly time SQL Server process method 2 is or not difference method 1.
I don't talk about time SQL Server send this data to ADO.NET (because method 2 sure faster method 1).
Thanks,
Tran Thai Nam
|
|
|
|
|
 Hi my gurus,
I follow step by step to do merge replication function at http://www.codeproject.com/database/MergeReplication.asp[^] but after executting, in Publistion database automatic adding conflict_... tables and MSmerge_... tables, sysmerge... tables in Subcriber database. These destroyed my database structure, so they can't to use for my application program. Tell me the way to merge but don't create anything in the database except datas. Thanks !!! Or anyOne to know another way to synchronize data between different locations, pls tell me. Thanks a lot !!!
|
|
|
|
|
Those tables are needed by the merge process to keep track of merge progress and problems.
Your application should not care about those tables and should gracefully ignore them. Any application that can't tolereate tables that it has no concern with is badly designed.
Yor data structures should have remained untouched (i.e. no changes to your application tables). It is not unusual for system proccesses tocreate additional tables in a database to support that process, and as long as no existing structures are changed, that should be transparent to any applications using the database. Sounds like you made an unfounded design assumption when you designed your application...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
The application is SAP B1, I'm not design. When I use Snapshot function, it's ok, but merge it can't. Do you know why ??? 
-- modified at 3:06 Thursday 9th March, 2006
|
|
|
|
|
Hi
I have a strange problem when trying to register SQL Server 2000 with other pc that same network with mine. i have problem related to SQL Server
registration problems.
Doing a SQL Server registration on localhost works.
i have create a new user at pc A with sql authentication. then at pc B i try new sql server registration there.
When trying to register the same SQL Server on an Enterprise Manager
installed on pc B the registration fails with message:
"Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection"
but when i do it the same way at my University lab , it work. when i do at my own house it fail
Any ideas out there what causes this? Thanks a lot!
chung
|
|
|
|
|
Are the "lab" pc's in a domain and the 'home' ones not?
if in a workgroup, does the accout you are using exist on both machines and have exactly the same password?
Integrated security expects the logon account to be either a trusted domain account, or if in workgroups to be identical on all machines (same username spelling, same password)
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I install local at my home pc. i think is that problem lah,cause the the username and password is exactly same.
I will try reinstall my sql server and other pc also.
thank Rob Graham
chung
|
|
|
|
|
hi all,
I am using Sql Server 2000.
using 'SQL Query Analyser' I have try to convert DateTime ..But It is failed..
please kindly help me.
this is the code part I tried:-
declare @stdate VARCHAR(20)
set @stdate='12/05/05'
select convert(DATETIME,@stdate,103)
Error:-Syntax error converting datetime from character string.
I have received data like @stdate ,So I need to convert it to DateTime and insert for DateTime Column in SQL Server Table.
So How I avoid Error and do Conversion?
thanks in advance..
|
|
|
|
|
Hi,
why ur not sending stdate as '12/05/2005' from ur application??
anyway it's ur choice........i think this may work for u........(though this will assume ur current year>=2000)
declare @stdate VARCHAR(20)
set @stdate='12/05/05'
select convert(DATETIME,SUBSTRING(@stdate,1,6) + '20' + SUBSTRING(@stdate,7,2),103)
Regards,
Ritesh
|
|
|
|
|
Try:
Select cast(@stdate as varchar(20)) as datetime
instead of using convert
Jonathan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
