|
Hi,
I am working on a forgot password module in which i have to send the mail having password to the user.I am using UserID,Email,IsApproved,IsVisible fields in my table.The module is working fine with sending mail on the basis of existing email of user.But i have to apply checks on the stored procedure that if the IsApproved and IsVisble fields are set to 0 by admin then the password would not be sent to the user and if IsApproved is 0 and IsVisible is 1 then do not send mail and if IsApproved is 1 and IsVisible is 0 then send mail.
I am using SqlServer2003 stored procedure.
Vivek Rathore
|
|
|
|
|
You can use IF condition in your stored procedure.
Best Regards,
Apurva Kaushal
|
|
|
|
|
vivek_r wrote: I am using SqlServer2003 stored procedure
I very much doubt that.
vivek_r wrote: I am working on a forgot password module in which i have to send the mail having password to the user
Personally, I think that is a bad idea. You should never store the password in a form where it can be sent via email. It is better to store the password as a salted hash which means that it can never be retrieved. Then, if the user forgets the password, it has to be regenerated.
vivek_r wrote: if the IsApproved and IsVisble fields are set to 0 by admin then the password would not be sent to the user and if IsApproved is 0 and IsVisible is 1 then do not send mail and if IsApproved is 1 and IsVisible is 0 then send mail.
SELECT columns
WHERE Email = @email
AND IsApproved = 1
The logic is very simple. You have stated that if IsApproved is 0 then do not send the email, and IsVisible is unimportant (reread what you wrote if you think that is an incorrect interpretation)
IsVisible IsApproved Send Email
0 0 No
0 1 Yes
1 0 No
1 1 Doesn't say: likely yes
|
|
|
|
|
Dear Colin,
You are saying very much right that the IsVisible field does not mean at all.
A short while ago i got the same answer as urs was.
Thank you very much for your suggestion.
Thanks and Regards
Vivek Rathore.
Vivek Rathore.
|
|
|
|
|
Dear friends,
How can I take the backup of SQL server database through coding, and I want to restore it through coding. The database must be kept as a text file. And shuld be able to restore it.
And how can I import and export the SQL server data in to MS-Access mdb file. Please advise me, or give me some code snippet if possible.
-- modified at 3:06 Friday 29th December, 2006
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
indian143 wrote: How can I take the backup of SQL server database through coding, and I want to restore it through coding.
You can issue virtually any command you like through SqlCommand including the BACKUP and RESTORE commands.
indian143 wrote: The database must be kept as a text file
Why? That is a very inefficient format to store a backup of a database in.
|
|
|
|
|
i want to know how to find size of database
used space in files,used space in logs,free space for files,
free space for logs,unused space
thanx
manish
|
|
|
|
|
I have to fill a survey form with data from an oracle database.
We debate two ideas: use a dataset object or a XML file.
For XML file I would use forward only method of reading the file and not DOM, faster.
Is it more efficient generating the XML from database and then parsing the XML on the application server then using a data set returned from the database ?
Thank you
|
|
|
|
|
Hi,
I am working in ASP.NET. I need to get the list of Servers registered on the local machine. How can I get this??
regards
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
I haven't seen a pure ADO.NET solution for this (I guess this is what you're looking for) but if you can't find such a solution there as an alternative way..
SQLDMO object! Here SQLDMO.Application you'll find a method called ListAvailableSQLServers! Just add a reference to your project and create an instance of it and you've got your list.
|
|
|
|
|
thanks,
I tried it but it is returning 0 servers. Why is this so? I am using Windows XP and SQL Server 2000.
regards
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
There are several possible reasons for this..
The database might be hidden - to check open sql manager and select the properties by right clicking on the protocols node of the db instance. Look for "HideInstance"..
It could also be that you're running into a latency problem, if your server isn't responding fast enough you won't see it. But it would say this is unlikely on "localhost"!
If this still doesn't help, you might want to use a workaround. You could easily enumerate this registry key: SOFTWARE\Microsoft\Microsoft SQL Server - InstalledInstances
|
|
|
|
|
Thanks for your quick help...
i tried SOFTWARE\Microsoft\Microsoft SQL Server - InstalledInstances, but it doesnt contains Server Information registered in a SQL Server Group. How can I get that  ?
regards
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
Can this be done running in the normally restricted rights of an IIS client?
|
|
|
|
|
Problem Description:
ADO .Net transaction fails for distributed databases and also even for a simple SQL Query that uses Dblink to Connect Oracle 8i.
When we fetch (query) data in SQL Server 2000 using dblink and connect to Oracle 8i and if this entire query is with-in ADO .Net transaction, then it fails. But the same query works fine without transaction.
Sample SQL query using DBLink in SQL Server.
Oracle Server Name for example: ORADB
Stored Procedure Name: UseDBLinkSP
SELECT * FROM [ORADB].SchemaName.TableName WHERE condition
Sample code in C#:
SqlConnection sqlCon = GetConnection ();
sqlCon.Open();
SqlTransaction trans = sqlCon.BeginTransaction();
try
{
DataAccessClass.getRecords(UseDBLinkSP, trans);
trans.Commit();
}
catch(Exception ex )
{
trans.Rollback();
throw ex;
}
finally
{
if (sqlCon != null && sqlCon.State != ConnectionState.Closed)
{
sqlCon.Close();
}
}
.
l Please note that we are only fetching information from the oracle database, but no updations or deletions.
l This is a sample code, in reality above and below dblink query we have couple of SQL Server Stored procedures that will insert/update/delete data in SQL Server also.
When we run the above code in C# or even in SQL Query Analyzer with Begin and CommitTrans it gives this error.
Error Message:
サーバー : メッセージ 7391、レベル 16、状態 1、行 2
OLE DB プロバイダ 'OraOLEDB.Oracle' は分散トランザクションを開始できなかったので、要求した操作は実行されませんでした。
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB エラー トレース [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a]
Environment:
1. .NET 1.1
2. ADO .NET 1.1
3. Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) 0
4. Oracle 8i on Unix box.
|
|
|
|
|
wrote: This is a sample code, in reality above and below dblink query we have couple of SQL Server Stored procedures that will insert/update/delete data in SQL Server also.
Do you get the same problem with the sample code also? If not then the code above is not valid and does not help solve your problem.
wrote: Error Message:
サーバー : メッセージ 7391、レベル 16、状態 1、行 2
OLE DB プロバイダ 'OraOLEDB.Oracle' は分散トランザクションを開始できなかったので、要求した操作は実行されませんでした。
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB エラー トレース [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a]
I don't fully understand the erros message, but it appears to be with Oracle not SQL Server. There is no oracle specific code in the "sample" C# code you have provided. This suggests that the sample is not representative of the problem and therefor invalid.
If the link to Oracle is being made within SQL Server, rather than your C# application. You could attempt to BEGIN and COMMIT TRANSACTION within the stored procedure.
|
|
|
|
|
Appreciate Mackay for your quick and valuable response.Now I totally understood the concept.Can you please help me out for the following query.
-->In Oracle we have the following concept.
SET TRANSACTION READ ONLY;
SELECT * FROM ORDERS;
SELECT * FROM BALANCES;
COMMIT;
If the above thing is correct.Can we handle the same in C# application.If so how can we do that.Awaiting for your response
shaikshavali
-- modified at 12:00 Wednesday 27th December, 2006
shaikshavali.M
|
|
|
|
|
Hi,
I am writing a stored procedure.
I want to update the records in a table.
till i complete the updation of records I want to lock the table and once i complete the updation i want to release the lock on the table.
during the process of updation of records i don't want any other methods to access this table.
How to achieve this
Regards,
Uma
|
|
|
|
|
It happens automatically. As soon as you start making changes the table will get locked until the transaction is completed. Since SQL Server will wrap each statement in its own transaction, if you are updating over several SQL statements use Begin Transaction at the start and Commit Transaction when successfully finished.
|
|
|
|
|
Hi! consider a simple sql query:
SELECT empName FROM tblEmployee
i want the color of text due to result of this query become RED.
i.e.
ram
tom
jenny
bablu
..etc
please help me.
|
|
|
|
|
The database doesn't handle doing text colors. This can only be done by your app after it does the query to the database.
"I'd like to help but I don't feel like Googling it for you."
|
|
|
|
|
i am using vb.net with sql2000
ihave used adodb.connection and adodb.recordset
then i have used a dataadapter and dataset to show resords in datagrid
now i want to update changes made to the data in the datagrid
can any one help me
bhaskar varma
|
|
|
|
|
can be easily found in the MSDN! everything can be done by using the DataAdapter.
example from MSDN:
' Assumes connection is a valid SqlConnection.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM Categories", connection)
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Categories SET CategoryName = @CategoryName " & _
"WHERE CategoryID = @CategoryID", connection)
adapter.UpdateCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Dim parameter As SqlParameter = adapter.UpdateCommand.Parameters.Add( _
"@CategoryID", SqlDbType.Int)
parameter.SourceColumn = "CategoryID"
parameter.SourceVersion = DataRowVersion.Original
Dim dataSet As DataSet = New DataSet
adapter.Fill(dataSet, "Categories")
Dim row As DataRow = dataSet.Tables("Categories").Rows(0)
row("CategoryName") = "New Category"
adapter.Update(dataSet, "Categories")
-walter
|
|
|
|
|
Dear friends,
I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Imtiaz
|
|
|
|
|
Imtiaz Murtaza wrote: I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
With SQL Server 2000:-
Insert Into t4 (f1,f2) Select f1, f2 From t4 Where f1=2
Here f2 field is of type text in t4 table. This query is successfully executing. I guess you are using functions with text type field, which may be a reason for the error.
Imtiaz Murtaza wrote: Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Try to follow this technique:
Insert Into t4 (f1, f2) Select f1, Substring(f2,1,8000) + Substring(f2,8001,8000) from t4 where f1=3
Hope this may help.
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
