Best Practices for Insert/Update/Delete

for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...

My 2 cents... SPs are a great way to interface with a database. For me, I will never access a DB in any other way. In fact, I would say it is good practice to secure the database so that only the defined stored procedures can be Executed against the DB. No direct table reads or writes. This will ensure that no one (other than an errant DBA) can do anything other than what is inteded by the interface provided through the stored procedures. It is essentially just another layer in the application model. Also, it provides a level of reusability, and design hiding... generally considered good things. An application will essentially only need to make "function calls" on the database, rather than some nasty select statement that, oh, by the way, I split this table out into two seperate ones, so now you have to go rewrite all your queries in every application that was ever written that uses my database because I didn't just write stored procedures in the first place, which, looking back would have been smart because then I would only have to fix things in two places.

// of course, you probably could create a view with the same name as the old table to fix it, but, just making a point

|||

I think you should use Stored Procedures whenever possible. But for making sure that the value in a column is unique you should always use unique constraints etc. For insert/update/delete always use SPs.

sql

Best Practice: Procedures: (Insert And Update) OR JUST (Save)

I have a Product Table.

And now I have to create its Stored Procedures.

I am asking the best practice regarding the methods Insert And Update.

There are two options.

1. Create separate 2 procedures like InsertProduct and UpdateProduct.

2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his articlehttp://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419

Can any one explain the better one.

Waiting for helpful replies.

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419

a

There's no "best practice" for this one. Imar presumably likes his "Save" approach because whether you are adding a new record or amending an existing one, generally software applications ask you to click the Save button - so he likes to make his programming logic analogous.

Personally, I prefer theKISS principal, and create 2 separate procedures. It's clear from the interface which one to call as a result of user action. I also see the decision as to whether to Insert or Update as being a business logic decision, and I'm uncomfortable about putting business logic in a stored procedure. The reason for this is that the business logic may not be transferable to another database platform.

|||

I do not understand your last point regardgin Business Logic.

I understand that it should be better in your opinion to create 2 separate procedures.

But what about Business Logic Methods.

|||

zeeshanuddinkhan@.hotmail.com:

I do not understand your last point regardgin Business Logic.

Well, I suppose it depends on how you define "Business Logic". And this illustrates one of the problems with layering an application. The reason why there are so many books and theories on architecture is because there is no "right" way to do it, and definitions of what belongs in which layer are different. Some things so obviously belong in certain layers, but other things might or might not - depending on what you are used to, how you think, what you are told to do by your team leader etc. There is for example, a huge debate about whether stored procedures are a bad thing altogether, because they can be viewed as placing business logic in a database and not in the BLL.

It also depends on how atomic (how much you like to break functionality down into discrete parts - methods, classes, procedures etc) you want your application. Imar would no doubt suggest that the action of the user defines that a Save() method be called, and that while the Save() method can include two alternative actions (Insert or Update), both lead to a row being saved to the database, so it's essentially the same action. The procedure decides whether an existing row is updated or a new one created. I see the difference between Insert and Update as being too different to be combined into one method. Consequently, I break the procedures apart into separate atomic constructs. I view the difference between the 2 as a business logic thing - because I can - and something in my gut tell me it is.

That's purely my view and is neither right or wrong. Others may not agree, and they will no doubt have valid justification for their view. It's right for me but wrong for Imar. And that's why I said at the beginning that there is no Best Practice for Insert or Update v Save. It's purely down to your personal preference. Imar's solution has a certain appeal, in that it contains a certain "cleverness". Some people like that. Nothing wrong with that at all.

Quite often the difference between two alternatives is purely philosophical, and has nothing to do with performance, maintainability or re-useability, which are the three items that Best Practice should be concerned with.

[Edit]

Just re-read my first response and having rambled on above, I see I may have missed your point. If you were asking about transferable business logic, it may be that you have to move the application to a different database system which doesn't support stored procedures, but may support basic INSERT, UPDATE, SELECT and DELETE saved queries. In this case, it wouldn't be too difficult to copy and paste the SQL form each part of the proc, but if you make procs do too much in terms of massaging data, or deciding on a course of action, you will create a load more work in your migration.

You are also perfectly free to ignore this on the basis that "it will never happen". Only you know best.

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005â?¦ we as
developers can build and test in our place.
After successfully testing the codesâ?¦ web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice â?¦ if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005â?¦ we as
> developers can build and test in our place.
> After successfully testing the codesâ?¦ web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> What is the best practice ? if the situation is that we have to carry the
>> table and its data inside table physically to the host location and login
>> to
>> the server and connect USB flash drive and update tables in SQL Server..
>> What is the best practice to perform update by going physical to the host
>> company for make SQL Server 2005 Database changes?
>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005… we as
developers can build and test in our place.
After successfully testing the codes… web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice … if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005… we as
> developers can build and test in our place.
> After successfully testing the codes… web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Best practice

i have two databases on two different servers, one which is a live server.
What i want to do is run a query to update a table in the second server wit
h
records from the first database. would this be a select if not exists ?It can be an insert followed by a subquery which is based on a NOT exists, a
ssuming that you want to
add the rows that doesn't exist (based on some key column).
Or, it can be an update based on a JOIN (or an update with a number of corre
lated subqueries in SET,
as well as an EXISTS), if you want to update rows that already exists in the
other table, picking
column values from that other table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B13DDBFC-4697-4DA6-B9C0-5F7A575BC8DF@.microsoft.com...
>i have two databases on two different servers, one which is a live server.
> What i want to do is run a query to update a table in the second server w
ith
> records from the first database. would this be a select if not exists ?
>

Best option to update every Jan 1st

I have a vacation request app I'm designing, and it has a VacationData Table with TotalVac, UsedVac, VacLeft, VacationCarriedOver, and VacCompleted.

I need to take the VacLeft and divide by 2 and place that data in the following two spots, one in the VCO and add it to the TotalVac, which is pulled from another table w/hire date and other info.

I only need to run this on Jan 1 of every year, any suggestions?

Write a console application and run it as a scheduled task.|||

You can schedule a job to run on SQL Server using the SQL Agent on Jan 1. You can also schedule it through the command-line "AT" command. A third option is to detect the year change in your application like in session start. A fourth option is to detect it in the stored procedures you are using to retrieve that data.

Take your pick.

IMHO however, I suspect it would probably be best to track when vacation days are earned, and when they are spent. Having this data already, those two fields are now redundant, but could be emulated as computed fields using a user defined function.

Best MSSQL management tools

Hi everyone.

I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.

Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)

Cheers~

gmefmax:angel:You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.

Example:
osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'|||You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).

It's not impossible, but I admit that I'm not up to the challenge right now.

Regards,

hmscott|||I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?|||I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.

basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.|||Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?|||I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?

Then use osql.exe instead.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.

I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?

I keep meaning to get into powershell, but haven't yet.

ps combined with SMO would be a nice combo:

http://www.google.com/search?q=smo+powershell|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.

I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.

Best Method to update table...

Hy everyone.
I've got a little question regarding the speed of an update query...

situation:
I've got different tables containing information wich i want to add to one big table trough a schedule (or as fast as possible).

Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Does anybody know a faster solution? The union queries block the table for those 20 Seconds...

Thanks for any reply!RE: situation: I've got different tables containing information which i want to add to one big table trough a schedule (or as fast as possible).
Bigtable size:
est. 180000 records with 25 fields (most varchar).

Currently I've tried two different methods:
delete all rows in the big table and add the ones from the little tables again. (trough union all query)
-> Speed ~ 15 Seconds

refresh all changed rows (trough timestamp <>) and add new titles (trough union all query)
-> Speed ~ 20 Seconds

Q1 Does anybody know a faster solution? The union queries block the table for those 20 Seconds... Thanks for any reply!

A1 Maybe.

As with many things, it depends on the requirements. For example, some possible considerations may include various permutations and combinations of any of the following: (not an exhaustive list)
a using a lower isolation level for the union queries, and conditionally unioning only updated tables
b implementing triggers to update the target as dml is commited at the source tables
c a create, populate, and rename table scheme (dropping the old table)

Best method for running several queries?

I have an SQL file saved from QA. It has several queries used for testing.
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>

Best approach with DTS

Let me see if I can explain this.

I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.

Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.

Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.

Step 3
The returned rows here, need to be limited to key values returned from step 2

Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.

What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.

I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.

What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.

I'm open to ideas for alternatives

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
it
inside
>
>.
>

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
--
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
>> Hi,
>> This could be a basic question. But still I want to get
it
>> clarified.
>> Normally if an ALTER or UPDATE command been tried with a
>> non existing column, SQL Server throws error. To avoid
>> this, the practice is to place the alter statements
inside
>> an IF EXISTS() block. Only if the column exists/not
>> exists, the alter would get executed.
>> I tried using the same technique for an UPDATE. I still
>> get the error inspite I have an exists() check.
>> In the below given example, The statement1 (ALTER) does
>> not throw any error while the statement2(Update)
>> throws error.
>> Could somebody please explain the reason behind this?
>> Regards,
>> J.P. Job
>>
>> Eg:.
>> USE PUBS
>> go
>> --Statement1
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> ALTER TABLE AUTHORS DROP COLUMN DUMMY
>> go
>> --Statement2
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> Update AUTHORS
>> SET DUMMY = 'TEST'
>> go
>
>.
>

Behavior question about updateable resultsets...

I have a question regarding a certain behavior of updateable
resultsets. If I update a column using any of the updateXXX methods and
then try to use the getXXX methods from the same column to see if it
updated the results locally and not on the server, I get the same old
value. I have to call updateRow() but that updates the underlying
database and still gives me the old value until I execute the same
query again and get a new resultset. Maybe the code below will clarify
my question more..
Connection con = null;
Statement stmt;
ResultSet rst;
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
System.out.println("Getting connection.");
con = DriverManager.getConnection(url);
System.out.println("Connection successful.");
String st = "select age,sname,snum FROM student;";
stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
rst = stmt.executeQuery(st);
rst.last();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
rst.updateInt(1,23);
rst.updateRow();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
The output is:
Getting connection.
Connection successful.
25 Edward Baker 578875478
25 Edward Baker 578875478
If I were the run the same code again, I get:
Getting connection.
Connection successful.
23 Edward Baker 578875478
23 Edward Baker 578875478
Any/all help is appreciated
Thanks
Devansh Dhutia
University of Iowa
This is a bug and it does not have a trivial fix. I would like to encourage
you to file this using the product feedback website (below).
The problem here is that there are two mutually exclusive places where
column values transit through the driver. The first, used only by getters,
is through the columns array (lives on the statement). The second, used
only by setters, is through the colParam array (also lives on the
statement). The columns array is read only the colParam array is write
only...
Note that the JDBC spec provides (in section 27.1.22, p. 718 - 719 JDBC API
Tutorial and Reference, Third edition, (Fisher, Ellis, Bruce)) that a result
set's own updates need not be visible to it. Obviously we would not like
this to be the default behavior but it is going to take a lot of work and it
would help to have customer feedback that clarified why this behavior should
be changed.
Entering a bug:
Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
Product/Technology:
SQL Server 2005
Category:
JDBC Driver
Make sure to add JDBC SqlServer 2005 to the but title.
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
<devansh.dhutia@.gmail.com> wrote in message
news:1128188361.813553.300740@.g47g2000cwa.googlegr oups.com...
>I have a question regarding a certain behavior of updateable
> resultsets. If I update a column using any of the updateXXX methods and
> then try to use the getXXX methods from the same column to see if it
> updated the results locally and not on the server, I get the same old
> value. I have to call updateRow() but that updates the underlying
> database and still gives me the old value until I execute the same
> query again and get a new resultset. Maybe the code below will clarify
> my question more..
> Connection con = null;
> Statement stmt;
> ResultSet rst;
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> System.out.println("Getting connection.");
> con = DriverManager.getConnection(url);
> System.out.println("Connection successful.");
> String st = "select age,sname,snum FROM student;";
> stmt =
> con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
> rst = stmt.executeQuery(st);
> rst.last();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> rst.updateInt(1,23);
> rst.updateRow();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> The output is:
> Getting connection.
> Connection successful.
> 25 Edward Baker 578875478
> 25 Edward Baker 578875478
> If I were the run the same code again, I get:
> Getting connection.
> Connection successful.
> 23 Edward Baker 578875478
> 23 Edward Baker 578875478
> Any/all help is appreciated
> Thanks
> Devansh Dhutia
> University of Iowa
>

Before Update/Delete Trigger

Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, JeremyRead about INSTEAD OF triggers in BOL|||Hi JCScoobyRS,

Originally posted by JCScoobyRS
Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, Jeremy

h, why don't you revoke the user the UPDATE and DELETE permission?|||Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy|||Originally posted by JCScoobyRS
Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy

In this case you should go for INSTEAD OF triggers|||Okay...that sounds good. Here is an example of what I need to do:

I'm trying to prevent the UPDATE and DELETE on a table after a certain field has been entered(not null). Here is the trigger right now:

CREATE TRIGGER TRANSACTION_REPORTEE ON [dbo].[TRAVAUX_COMMANDE]
FOR UPDATE, DELETE
AS
IF [dbo].[TRAVAUX_COMMANDE].[Id_Transaction_GL] IS NOT NULL
BEGIN
RAISERROR ('Impossible de modifier une ligne reporte',10,1)
ROLLBACK TRAN
END

This is what my buddy has. Is there anyway to take what he has here and revise it with your idea in it for testing? Thanks alot, Jeremy