|
I'm assuming SQL Server 2000, and that you have no usable key data (for example, it's possible for every field in two different records to be identical, apart from a key field).
If you're generating your own primary keys, there shouldn't be an issue. What you'd normally do is keep some form of incrementing identifier in a separate table. You'd read the next value into a variable, update the stored value, then use that key information in both places.
If you're using automatic numbering (using an IDENTITY column), you can retrieve the last inserted identifier using the SCOPE_IDENTITY() function. You need to retrieve this straight after the first INSERT, otherwise it may not be the correct identifier.
Depending on your requirements, you may want to put both INSERTs inside a transaction. Do this if you want to be sure that either both, or neither, INSERT completes. Be aware that if an error occurs, the transaction is not automatically rolled back. See SQL Server Transactions and Error Handling[^] in the Articles section.
|
|
|
|
|
Apologies if this is a blonde question but does anyone know of a tool that specifically monitors bandwidth used by MSSQL server? i.e. We have a SQL database server box and we want to monitor and tally all calls to the SQL port.
Our sys admin tells me that the normal bandwidth monitoring software he is using cannot track SQL usage even though in my mind it is just another port.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
|
|
|
|
|
I guess you could use packet sniffing software such as WinPCap.
Have you tried the SQL Profiler? I know you can set a number of filter options and log them to a file.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
This may be a silly question but I'm a bit stuck and am asking for clever ideas 
When I insert text into the DB i use a function called PrepSQL which just replaces ' with ''. All very well, but I now have a problem when the content is edited. Each individual ' get replaced so I end up with "it'''''''''''''s". Any ideas how to check for this and resolve it 
|
|
|
|
|
Use parameters on your command objects (ADO) or on your SQLExecute (ODBC). If you do that, you shouldn't need any reserved character escaping codes - and your statements may run faster too.
SQL string construction is basically brain-dead, unless you have no alternative, or you want to keep your code database independent. Even then, parameters are usually a better choice.
|
|
|
|
|
If you're going to stick w/ doing the PrepSQL function on the way in, then I'd recommend using an UnprepSQL method on the way back out (i.e., when you pull data out of the database and display it). So, going in ' would become ''. Coming back out, '' would become '. That will resolve the problem.
Good luck!
Sean McCormack
|
|
|
|
|
Respected!
also i hav got mi problem's solution from respected Mr Heisen but i still couldnt solve mi prob...now i m pasting the code of deleting the row from related tables....plz check mi code n identify the mistake if u find...
SqlDataAdapter DeleteDA = createTeacherDA Conn);//function call
SqlDataAdapter DeleteDetail = createTeacherDetailDA(Conn);//function call
DataRowCollection rc = TeacherDetail.Rows;
rc[0].Delete();
//d.Delete();
//Teacher.Rows.Remove(rc.Find(1,);
//DeleteMemberDs.Teacher.Rows[0].Delete();
AddMember am = new AddMember();
DeleteDA.Fill(am.AddTeacherDs,"Teacher");
DeleteDetail.Fill(am.AddTeacherDs,"TeacherDetail");
DeleteDA.Update(am.AddTeacherDs,"Teacher");
DeleteDetail.Update(am.AddTeacherDs,"TeacherDetail");
DeleteMemberDs.AcceptChanges();
Meroo
|
|
|
|
|
Hi everyone,
I am trying to do a select sub-query, but for some reason it is not working out.
I have the following stored procedure:
CREATE MyProcedure
@newID int,
@oldID int
AS
INSERT INTO MyTable(ExperimentID, Threshold, Energy) VALUES(@newID, Select Threshold, Energy from OldTable where ExperimentID = @oldID)
This mixing of a given value with a subquery does not seem to work. However, there must be a mechanism to do this. Does anyone has some experience with something like this and can maybe help me.
Thanks a lot.
Cheers,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
|
 i am using MSDE and i'm trying to create new database but the connection security option is always Integrated Security is that means that anybody can get access to my data and if ues how can i protect my data.
|
|
|
|
|
If Integrated Security is set to SSPI, MSDE uses Windows Authentication - it takes your system logon credentials as being the credentials to log on to the database with. If you don't set Integrated Security, you must provide a username and password.
In the Integrated Security mode, MSDE uses the local system's Windows password database if the connecting process is using a local account, or contacts the domain controller if using a domain account. In the Mixed Mode ('Use a specific user name and password'), the user names and passwords are stored in MSDE's msdb database. The MSDE install defaults to Windows Authentication mode - an attempt to connect without using Integrated Security fails.
Integrated Security is generally considered to be more secure than specifying a username and password in a connection string.
Even this isn't the whole story, because logins (how you connect to the server) are mapped to server roles on a per-server basis (which control things like being able to BULK INSERT, adding other users, shut down the server), and to users at a database level. Users can be granted permissions to create tables, select data, insert data, execute stored procedures, etc. SELECT, INSERT, UPDATE and DELETE permission is granted on a per-table basis; EXECUTE permission is on a per-procedure basis.
To simplify managing users, databases also have roles, which are the equivalent of Windows groups. Roles and users can be either be Granted permission, explicitly Denied permission, or neither. If a user or one or more of their roles has Grant permission, and neither the user nor any roles have Deny permission, permission is granted. This allows you to say 'Grant all members of the public role permission, except Bill'.
The system comes initially configured with BUILTIN\Administrators (the Administrators group on the local machine) and sa logins, which are both made members of the Server Administrators server role. They are both mapped to the 'dbo' user in the master and model databases; dbo is a member of the public and db_owner roles in these databases. By default public has no permissions granted or denied; db_owner has all permissions granted (this cannot be altered).
If you have the full SQL Server, I recommend using Enterprise Manager to edit configurations. Otherwise, you need to look at the sp_addlogin and sp_grantlogin system stored procedures, for starters.
|
|
|
|
|
i m a beginner in .net, especially in ado.net..i want to del a row from a table having one parent table and one child table...i m trying through delete method of datarow class but couldnt do...
one exception occurs wen i use delete method that the table has no row at given index...plz tell me wat to do??????
meroo rana
|
|
|
|
|
It means that you specified an index which is bigger than DataTable.Rows.Count. If you want to delete the last row in the collection you have to use the index of DataTable.Rows.Count - 1. Remember that the Count tells you how many rows you have, but the collection starts from 0 and not 1.
The other thing, when perfoming a certain operation on a table which is in a relationship with another table, you have to follow a little guidline:
1. When inserting, insert first to the parent table and then the child table.
2. When updating, update the parent table first and then the child table.
3. When deleting, delete from the child table first and then from the parent table.
Regards,
Husein
|
|
|
|
|
hi there i am working whit SQL DB
i have a data grid whit lines of kategorys
i want to stand on the line in the data grid
an then i want to prees the delBtn
an i want the line that i am standing on going to be dlete
how can i do it ?
icq 47305886
|
|
|
|
|
I am using MSDE.
I don't have TextCopy utility when I am using MSDE. What I should do to insert and retrieve image data/binary data from the database ?
|
|
|
|
|
Microsoft SQL server version 7.0
---------------------------------
I have to find out a way..where in the login id and passwords can be stored
in more secured way..
I browsed the netb and found out the following information..does this apply to sql server 7.0 ?
"
Passwords are stored in the sysxlogins table in encrypted form.SQL Server uses an undocumented function, pwdencrypt() to produce a hash of the user's password, which is stored in the sysxlogins table of the master database.
When a user attempts to authenticate to SQL Server several things happen to do this. Firstly SQL Server examines the password entry for this user in the database and extracts the "salt" - 84449305 - in the example. This is then appended to the password the user supplies when attempting to log in and a SHA hash is produced. This hash is compared with the hash in the database and if they match the user is authenticated - and of course if the compare fails then the login attempt fails.
The user's password is converted to it's UNICODE version if not already in this form.The salt is then appended to the end. This is then passed to the crypt functions in advapi32.dll to produce a hash using the secure hashing algorithm or SHA. The password is then converted to its upper case form, the salt tacked onto the end and another SHA hash is produced."
This methodos storing is adopted by SQL server..
in this method...hacking the passwords sounds so easy!
so..
Can someone suggest a better way for storing the passwords and the login information..
storing it in teh registry is also not safe .,I guess!
thank u..
ranjani
|
|
|
|
|
|
Unless someone has found a way of generating passwords from their salted hash value, I don't understand what the problem is....
Steve S
|
|
|
|
|
while i executed a sql string to insert a new record, one of the fileds of this record had a length of 500, this field in the ORCAL database had been design to have 1000 VARCHAR2(1000),i executed this command string in TOAD,it had no problem, but when i used ado execute method of command object, the function didn't return, and did't report any error, so ,what is the problem ?
the sql string is :
YW_SAVEFCXXWKJL ( '200311270004',10653.000000,500.000000,'hfghfghghhghfgherytrtyttrytryughfghjnnghbbghtyghvnbnghfghfghghhghfgherytrtyttrytryughfghjnnghbbghtyg','hfghfghghhghfgherytrtyttrytryughfghjnnghbbghtyghvnbngfghghfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff444444444444444444444444444444444444444444442333333333333333333333333333333333333333333333333333333333333332222222222222222222222222222222222222222222222222222222222222222222222233333333333333333333333333333333333336666666666666666666666666666666666666666666666666666666666777777777777777777777777777777777777777774444444444444444444444777777779','','zy1','',TO_DATE('','yyyy-mm-dd')
|
|
|
|
|
|
I don't think you can change the sequence in the DataView. DataView "binds" to the DataTable and DataTable forms the columns based on the SQL string or stored procedure. You should change the order in you SQL string or stored procedure.
|
|
|
|
|
The following is the way i want to implement this.
that is..to check iff
permissiosn()&2=0x2....permissions()&32=0x20 and so on...
CREATE PROCEDURE balli
@ioparm int output,
@oparm int output AS
BEGIN
SET NOCOUNT ON
IF PERMISSIONS() &@ioparm=0x@oparm
SELECT 1
ELSE
SELECT 0
END
The above sql proc gives syntax error..how do i change it??
ranjani
|
|
|
|
|
A couple of problems: your parameters should not be specified as output, because you're passing the values into the stored procedure. The parameters are also badly named - indeed, I'm not sure you need two parameters, because you're just trying to see if a particular flag is set.
The syntax error is coming from 0x@oparm. The prefix 0x tells SQL Server that the following characters should be interpreted as a hexadecimal number literal. If you want to do that, specify hex when assigning a value to the parameter, don't include it in the query text. @ isn't a valid character in a hex literal, so SQL Server complains.
With that in mind, I would just SELECT PERMISSIONS() and perform the mask operation in client-side code. Testing each bit separately is a waste of network and server resources.
In Visual C++, you use the & operator to mask, then == to compare. == has a higher precedence than &, so you need to use brackets to ensure that the AND operation is done first. In VB, use And to mask and = to compare.
|
|
|
|
|
The following is the way i want to implement this.
that is..to check iff
permissiosn()&2=0x2....permissions()&32=0x20 and so on...
CREATE PROCEDURE balli
@ioparm int output,
@oparm int output AS
BEGIN
SET NOCOUNT ON
IF PERMISSIONS() &@ioparm=0x*@oparm
SELECT 1
ELSE
SELECT 0
END
The above sql proc gives syntax error..how do i change it??
ranjani
|
|
|
|
|
I have a field called validfrom and validto inside my Table I want a user to be able to enter two dates and then my page pulls back all the data where there dates fall inside the dates in the DB.
something like
SELECT * from table where form.date1 and form.date2 fall between "VALIDDFROM" and "VALIDTO"
So I have a validfrom date January and a validto date of december in the db in same record
but my user types in feb to march
as
feb to march falls inside the jan - dec period
that result is pulled to the broweser?
Working with MsAccess and ASP
Thanks
- Michael Fasani
www.michaelfasani.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
