Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>sql
Showing posts with label stuff. Show all posts
Showing posts with label stuff. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
archiving
Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
Neil
Neil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
Neil
Neil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
archiving
Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
Architecture question grouping different kinds of stuff...
Hi all,
I'm hoping somebody here might be able to point me in the right
direction.
Basically, I'm looking for a "good" way to build tables such that
different kinds of stuff can grouped.
For example, let's say I have "rooms" and they can contain "people"
and/or "books" and/or "furniture" -- each of those objects (rooms,
people, books, furniture, etc) may have their own attributes -- but the
idea is that a room may contain 0 or more of each of the others.
I'll need to do lots of selects to retrieve the contents of specific
rooms.
Is there a standard / good way to go about this sort of thing?
Somehow giving people, books, furniture a room_ID (foreign key) seems a
bit clumbsy -- ie, do I have to run a select for each?
thanks kindly, -ScottFollowing up on my own question above, let's say I have these tables:
Rooms
.. . RoomID
People
.. . PersonID
.. . Name
Books
.. . BookID
.. . Title
Furniture
.. . FurnitureID
.. . FurnitureType
I could group stuff into rooms with something like:
RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
(where ContentType could be person, book, furniture, etc)
But it seems like if there were 20 different kinds of things, I'd have
to run a select for each.
I'm hoping this is a standard SQL architecture problem, but I lack the
vocabulary to know what to search for.
thanks, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Following up on my own question above, let's say I have these tables:
> Rooms
> . . RoomID
> People
> . . PersonID
> . . Name
> Books
> . . BookID
> . . Title
> Furniture
> . . FurnitureID
> . . FurnitureType
> I could group stuff into rooms with something like:
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> (where ContentType could be person, book, furniture, etc)
> But it seems like if there were 20 different kinds of things, I'd have
> to run a select for each.
I would look into gathering all these contents into one supertable.
If then there are specicic attributes for person, books etc you can
can have sub tables for this.
This contents table would have a type field specifying the type of object.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland, thanks -- are you suggesting something like:
Rooms
.. . RoomID
RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . PersonName
.. . BookTitle
.. . FurnitureType
or even
RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . VariableText1
.. . VariableText2
(where 'VariableText' would store different kinds of stuff, depending
on the ContentType)
Since some of these objects may have a few attributes, such a
supertable could have lots of nulls -- is that "ok"?
Thanks again for your help, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Hi Erland, thanks -- are you suggesting something like:
> Rooms
> . . RoomID
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . PersonName
> . . BookTitle
> . . FurnitureType
> or even
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . VariableText1
> . . VariableText2
> (where 'VariableText' would store different kinds of stuff, depending
> on the ContentType)
>
> Since some of these objects may have a few attributes, such a
> supertable could have lots of nulls -- is that "ok"?
Nah, rather I was thinking you would keep the existing tables, and
just add this RoomContents table. Probably you should move some columns
into this table, for instance a string that represents a name of some
sort. (That is "sofa" for furnitures.)
This would make it easier to write a simple query that lists all the
contents in the room. If you need furniture-specific information you would
go to that table.
In the end it may be a trade-off where you place things. If some attributes
are common to most contents, it may be better to have it as a nullable
column in the main table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The trouble is that each time I "load a room" I'll need at least a few
of the attributes of the things in that room.
So, if I understand correctly, I would keep all of those various
objects' attributes in one "supertable," and perhaps keep some
additional attributes (those not needed when the room loads) in the
extra object-specific tables.
Does that sound about right?
Thanks again, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> The trouble is that each time I "load a room" I'll need at least a few
> of the attributes of the things in that room.
> So, if I understand correctly, I would keep all of those various
> objects' attributes in one "supertable," and perhaps keep some
> additional attributes (those not needed when the room loads) in the
> extra object-specific tables.
> Does that sound about right?
Yes, that was my thought.
In an object-oriented language, this is a little easier since you would
have "Contents" a base class (possibly virtual) and then have Furtinure
etc to inherit from that group. In SQL you cannot do this very well.
You could have a view that unites all tables, and then have an INSTEAD
OF trigger on the view, so when you inserts into the view, things fall
down in their respective places, although I am not sure that this would
be worth the effort.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
Append to a field in a database
Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?In update statement you should use:
Personally, I would create a notes table, and add records. This way, you can also store things like date/time, user id, etc. You can then just display them in sequential order. I do this with ASP all the time. Just did it yesterday, as a matter of fact. The client loved it.
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?In update statement you should use:
Update table_name set
column_name = column_name + new_data
where ....|||
Quote:
Originally Posted by nikhil86
Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?
Personally, I would create a notes table, and add records. This way, you can also store things like date/time, user id, etc. You can then just display them in sequential order. I do this with ASP all the time. Just did it yesterday, as a matter of fact. The client loved it.
Good luck,
Michael C. Gates
Thursday, February 16, 2012
append backups and other mtn plan stuff
Hello:
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas o
n
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribut
e
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the en
d
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?
> I am editing a backup maintenance plan. For some reason, the plan is doin
g
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas
on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.
> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.
> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attrib
ute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.
> I temporarily disabled my maintenance plans and re-enabled them this AM.
In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the
end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.
> Thanks!
> childofthe1980s
HTH
John
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas o
n
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribut
e
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the en
d
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?
> I am editing a backup maintenance plan. For some reason, the plan is doin
g
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas
on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.
> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.
> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attrib
ute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.
> I temporarily disabled my maintenance plans and re-enabled them this AM.
In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the
end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.
> Thanks!
> childofthe1980s
HTH
John
append backups and other mtn plan stuff
Hello:
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribute
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?
> I am editing a backup maintenance plan. For some reason, the plan is doing
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.
> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.
> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attribute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.
> I temporarily disabled my maintenance plans and re-enabled them this AM. In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.
> Thanks!
> childofthe1980s
HTH
John
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribute
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?
> I am editing a backup maintenance plan. For some reason, the plan is doing
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.
> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.
> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attribute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.
> I temporarily disabled my maintenance plans and re-enabled them this AM. In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.
> Thanks!
> childofthe1980s
HTH
John
Sunday, February 12, 2012
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?
Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>
|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it? what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com
|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it? what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?
Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>
|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it? what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com
|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it? what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API curso
r
the same as a server cursor? does an API cursor/ server cursor hold any lock
s
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch
n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor
'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes an
d
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL
.
> What exactly is the difference between a Tsql cursor and an API cursor? Wh
en
> I access a db from a .NET application is this an API cursor? is an API cur
sor
> the same as a server cursor? does an API cursor/ server cursor hold any lo
cks
> on the db? does a .NET application? BOL says to use a 'server' cursor as t
he
> prefered methos to give a web app user a result set from an aribtrary quer
y
> defined by the user which might be unpredicably large so that you can fetc
h n
> rows at a time without bringing the entire result set in and without holdi
ng
> locks. But I cant find anywhere else in BOL what exactly this 'server curs
or'
> is how and if it differs from a Tsql cursor called by a stored procedure o
r
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NE
T
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but thi
s
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteri
a
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database
.
I suppose that since this is using ADO it is not an API cursor, but then wha
t
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .
NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but t
his
> takes time and resources and it is likely that the user might want to chan
ge
> their search after seeing that hundreds of properties have met their crite
ria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if t
he
> user decides to keep looking fetches the next 10 rows etc. I understand ho
w
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the use
r
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the databa
se.
> I suppose that since this is using ADO it is not an API cursor, but then w
hat
> is it' what is going on in SQL when an outside application runs a cursor
in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isn
t
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API curso
r
the same as a server cursor? does an API cursor/ server cursor hold any lock
s
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch
n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor
'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes an
d
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL
.
> What exactly is the difference between a Tsql cursor and an API cursor? Wh
en
> I access a db from a .NET application is this an API cursor? is an API cur
sor
> the same as a server cursor? does an API cursor/ server cursor hold any lo
cks
> on the db? does a .NET application? BOL says to use a 'server' cursor as t
he
> prefered methos to give a web app user a result set from an aribtrary quer
y
> defined by the user which might be unpredicably large so that you can fetc
h n
> rows at a time without bringing the entire result set in and without holdi
ng
> locks. But I cant find anywhere else in BOL what exactly this 'server curs
or'
> is how and if it differs from a Tsql cursor called by a stored procedure o
r
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NE
T
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but thi
s
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteri
a
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database
.
I suppose that since this is using ADO it is not an API cursor, but then wha
t
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
Message posted via http://www.droptable.com|||CharlesT via droptable.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .
NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but t
his
> takes time and resources and it is likely that the user might want to chan
ge
> their search after seeing that hundreds of properties have met their crite
ria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if t
he
> user decides to keep looking fetches the next 10 rows etc. I understand ho
w
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the use
r
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the databa
se.
> I suppose that since this is using ADO it is not an API cursor, but then w
hat
> is it' what is going on in SQL when an outside application runs a cursor
in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isn
t
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.droptable.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
API cursors?
Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)