Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Tuesday, March 27, 2012

clear sql error

i am using sql 2000 and calling a sp from vb.net code.
I want when a error occur in sp i want to do some processing in sp and do
not want to throw that error to front end. how can i achieve that as after
checking @.@.error and doing processing, still error gets thrown to front endHave a look at
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||Hi
http://www.sommarskog.se/error-handling-II.html
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||You can configure this in your web.config file
regards
Thakkudu
Vikram wrote:
> i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front end[/color
]|||:)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4JapKTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
> Hi
> http://www.sommarskog.se/error-handling-II.html
>
>
> "Vikram" <aa@.aa> wrote in message
> news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||There's a setting in web.config that automagically adds error-handling to
stored procedures? :)
ML
http://milambda.blogspot.com/|||Also note that when you get a chance to upgrade to SQL Server 2005 this gets
much better because SQL Server 2005 introduces TRY...CATCH for Transact-SQL,
and also the ability to retrieve the error information (not just the error
number in @.@.ERROR) in Transact-SQL.
http://msdn2.microsoft.com/en-us/library/ms179465(en-US,SQL.90).aspx
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||> There's a setting in web.config that automagically adds error-handling to
> stored procedures? :)
yep:
[BestPractices]
SafeProgramming=True

clear identical submissions

i want to clear from my db the doubles and trebles submisions and keep a unique.my db looks like:

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
2 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

i want to look like

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

thank you .angeloOracle 9i,

delete from table
where NOT (rowid = (select min(rowid) from table group by KEY));

KEY is defined as a candidate key that is not enforced.|||dear sir
thank you for your reply.

message returned: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

thank you in advance|||Select distinct columns into #temp from tableA
delete from tableA
insert into tableA select * from #temp
drop table #temp
alter table tableA add primary key (columns)|||I'd use something like:DELETE FROM table
WHERE EXISTS (SELECT *
FROM table AS b
WHERE b.id < table.id
AND b.code = table.code
AND b.lastname = table.lastname
AND b.firstname = table.firstname
AND b.phone = table.phone)

Note that I used the php tag to get a monospaced font, this is just standard SQL-92 syntax that should run on any SQL based product.

-PatP

Thursday, March 22, 2012

Class for DDL

I want to create, alter and delete tables (not records) from ASP code. Does
anybody know of a class (preferably ASP or DLL) that hides the complexity of
the Data Definition Language?
CedricTake a look at the DMO API. However, first consider your reasons for wanting
to do this. DMO is a reasonable choice if you want to retrieve info about DB
objects or if you are developing a general-purpose database utility
(something like Enterprise Manager for example). For deployment of a
database application however, TSQL DDL is far more concise and much easier
to deploy in most environments.
At runtime, in a business process application, there should of course be no
general need to create and change database objects at all. Not if you have
the correct design anyway.
Hope this helps.
David Portas
SQL Server MVP
--|||Look at SQL-DMO in Books Online.
Paul
"Cedric" wrote:

> I want to create, alter and delete tables (not records) from ASP code. Doe
s
> anybody know of a class (preferably ASP or DLL) that hides the complexity
of
> the Data Definition Language?
> Cedric
>
>|||>I want to create, alter and delete tables (not records) from ASP code
Now I re-read that I am pretty alarmed! Why would you want to alter and
delete tables from ASP? Are you really writing a replacement for Enterprise
Manager? If so, you should first take a look at some of the similar third
party tools already available.
David Portas
SQL Server MVP
--

Thursday, March 8, 2012

CHILD functions?

How can I pick out a "top child" in SQL Server 2000?

Example:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
----------

I want to pick out the record with the latest date for every employee so I end up with something like this:

----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
----------

Any ideas?
Thanks in advance!

-GeorgeV??:
SELECT Employee, code, MAX([action date])
FROM YourTable
GROUP BY Employee, code|||Hmm, nice idea but it doesn't want to work.

Any more suggestions?

-GeorgeV|||SELECT Employee, code, [action date]
FROM YourTable YT
WHERE [action date] = (SELECT MAX([action date])
FROM YourTable
WHERE Employee = YT.Employee)

Friday, February 24, 2012

Checking The Users Server Role

I would like to determine if a particular user has sysadmin server
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.

Is it possible to fetch a user's server role to determine if it has a
sysadmin server role?Look for the IS_SRVROLEMEMBER function in Books Online.

Razvan|||Thanks.

checking the error code

Question.
I am sending a list of comma separated codes as a parameter to stored
procedure. As I parse the list, I put them in the table variable (or temp
table) using insert statement.
For example if the parameter is 1001,3233,4444,2223,4452,5523, after the
parsing, the table variable will contain 6 rows with the respective code.
Now, do I need to check @.@.ERROR after each insert statement whether it was
successful or not even though I am dealing with table variable?
Of course I am trying to avoid following approach
WHILE EndOfListCondition
BEGIN
BEGIN
INSERT tblCode(Code) VALUES(@.ParsedCodeID)
SET @.ErrorCheck = @.@.ERROR
IF @.ErrorCheck <> 0 GOTO ERR_HANDLER
END
END
and use
WHILE EndOfListCondition
BEGIN
BEGIN
INSERT @.tableVariable(Code) VALUES(@.ParsedCodeID)
-- ? Do I need error check here?
END
END
INSERT tblCode(Code)
SELECT Code
FROM @.tableVariable
Thanks
JJustin (jyang@.ioutsource.info) writes:
> I am sending a list of comma separated codes as a parameter to stored
> procedure. As I parse the list, I put them in the table variable (or temp
> table) using insert statement.
> For example if the parameter is 1001,3233,4444,2223,4452,5523, after the
> parsing, the table variable will contain 6 rows with the respective code.
> Now, do I need to check @.@.ERROR after each insert statement whether it was
> successful or not even though I am dealing with table variable?
Now that is a good question. Fortunately, the answer in SQL 2005 is
simple: set up a TRY-CATCH handler that embraces the entire procedure
as a matter of routines.
In SQL 2000, it's worse. My own approach is that I always check
@.@.error after every INSERT, DELETE and UPDATE although that I know
that some of them just can go wrong. Then again, I'm not checking
error after SELECT, despite a lot of our client code is running with
SET LOCK_TIMEOUT 29000. (Which is a stpuid idea.)
What I do to make this easier, is that I have some clips in my editor
(Textpad), so that I can easily add error checking. I'm also formatting
it compactly, so that it does not take up too much space.
Error handling in SQL Server 2000 is a boring business. For more
advice, my article http://www.sommarskog.se/error-handling-II.html
could be of interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME


I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet


IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

checking password

i used this code to retrieve customer_ID from a database given a userid and password
how to retrieve the customer_ID
my code:
public static int CheckPassword(string userid,string pass)
{

SqlConnection conn=new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
String strCheckPassword=@."Select Customer_ID from Customers Where UserID='" +userid+ "' "+"AND Password='"+pass+"'";
//name of the stored proceedure and command type set to StoredProceedure
SqlDataAdapter adp=new SqlDataAdapter(strCheckPassword,conn);
DataSet ds=new DataSet();
conn.Open();
adp.Fill(ds);
result=//this must retrieve customer_id from dataset ds; //help here

conn.Close();
return result;
}solved using Datareder get- method;

Sunday, February 19, 2012

checking if no record is returned

hi,

I basically want the .net version of rs.eof
does anyone know what the code would be using the execute scalar method?

Thanks>>does anyone know what the code would be using the execute scalar method?

executeScalar returns only one record. so theres no such thing as EOF. you are prbly talking of datareader...you just loop through the datareader using


while dr.read()
...
end while

if you want more info, you need to give us more details on what xactly it is that you are trying to do.

hth|||or,


if(dr.HasRows)
{
while(dr.read())
{
// Read some stuff
}
}
else
{
// no results were found
}

Checking for stored procedure return code in SQL Agent job

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

I would think that it could be a bit more 'robust' to have a DDL trigger that would set off your alert process upon an ALTER TRIGGER event...

Otherwise, use RAISERROR when a row with disabled trigger if found.

Checking for Row Existence

Can someone show me some C# code for detecting if a SQL row exists or not? This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step. In my code I'm either going to INSERT or UPDATE a record. I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response. Apparently ExecuteNonQuery does not work with SELECT. I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.

So...can anyone share the typical code they use to identify if a row exists or not within a database. I guess I was execting there to be some method available to do this sort of thing.

There are two methods i use

Method A)

Which uses the Execute Scalar to return the results of a SQL COUNT

i.e.

 cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
Int32 count = (Int32) cmd.ExecuteScalar();

Here is a nice example i found for you

http://www.java2s.com/Code/CSharp/Database-ADO.net/GetrowcountbyExecuteScalar.htm

Method B)

Using a datareader and has.rows

Add ; to make it c# as you see fitStick out tongue

Dim objTransport As SqlDataReader
objTransport = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

If objTransport.HasRows Then
Return ("True")
Else
Return ("False")
End If
myConnection.Close()

Method A is more efficient as your only passing around a very small number between the webserver and database.

Hope that helpsSmile

Sunday, February 12, 2012

checkbox - using in update statement as bit field

hi
I have a bit field in sql server represented by a checkbox ...
I am updating the database in code ( ie not using formview generated update .. )
the line that is falling over is
.Parameters.Add("@.archive", SqlDbType.Bit).Value = txtarchive.Checked
it falls over saying
failed to convert string parameter to boolean
thevalue of txtarchive.checked is either true or false - how do i convertthis to 1 or 0 or something that sql is happy with ...
thanks

you can use IIF. Give this a try:

IIF(txtarchive.Checked = TRUE, 1,0)

|||

NDinakar's solution should work, but I'm confused as to the problem since I have pretty much the same thing you did, and it works fine.

The only difference is how the parameter was set up, I not only told it that it was a bit, but also a size of 1. (Actually, the sql command/parameters were set up by using a designer, and I copied the code)

check what table are lock by application

Dear All,


Sometimes the application cannot write record into SQL Server, but the source code of this application cannot be seen. Therefore, I wanna verify the MSSQL status. I have to check what table are locked by application. I wanna know the following information.

locked_table, locked_by_application, locked_status

Can I write some t-sql to get the information? I tried using Profiler, but it generates some useless information. Could you give me some suggestions?

Alex

Lock granularity is much wider than only a tables. That's for the first. Second is that SQL Server doesn't require any information about application and situation, when application doesn't provide it, is common. And, for the third, I would recommend you to solve your locking and blocking problems rather than prevent connections to get blocked.|||

In my opinion Profiler would be the best choice for You. The only thing what You have to do is to establish the appropriate configuration in outgoing parameters.

|||

Take a look at: How to monitor SQL Server 2000 blocking http://support.microsoft.com/default.aspx?scid=kb;en-us;q271509

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

thank SQL_Menace

I will try it

Alex

|||Hi Alex
here is a solution that microsoft does not recommend because it depends on the system objects
create table #Locks (SPID smallint , DBID smallint ,objID int ,indID smallint , Type nchar(4),resource nchar(16), mode nvarchar(8), status varchar(8))
insert into #locks execute sp_lock
select distinct object_name (objID) , Hostname , program_name,nt_userName , loginame , object_name (objID), mode
from master..sysprocesses s inner join #locks l on s.spid = l.spid
drop table #locks

find the values and the meanings of mode in the MSDN
HTH