bookmark row position

We have a SqlCeResult set and would like to bookmark or save a row when iterating through it. There is a method for ReadAbsolute, but no "GoToAbsolute". What is the technique or method call to save the row position?

Thanks,

Bryan

I’m not quite sure I understand your issue. What's wrong with ReadAbsolute? What it does not do what mysterious "GoToAbsolute" does?

You have a row number (simply store it in a variable and you've got a bookmark), then do ReadAbsolute(), access the row. Do another ReadAbsolute() with previous row number (store it in another variable) and you’re back.

Naturally you can’t access more than one row at a time as RS is a cursor, it only “points” to one row at a time. If you need to access two (or more) records at the same time use two RS or store row data in memory by calling ResultSet.GetValues().

|||

two questions then... how do you get the row number of the current position?

also, if you have the row number and other rows are added and deleted, then the saved position is not good, right? that's why i was hoping there was a bookmark.

bryan

Blocking issue

We are experencing some serious locking issue on sql server (sql2000). Basically a method in VB component opens a recordset which loops through a table A (about 100,000 times), inside the loop, it calls a stpred proc and does an update on table A. Each update in the loop is quite small, a few records at the most. Table A has an update trigger which insert the updated records to a history table then delete this record from table a. There are some f other queries running on table a. The big update loop on table A causes locking issues. With every update in the loop, there are hundred of locks on the delete job when it's blocking other jobs. I can't fingure out why continuous small updates opens so many locks(by looking at sp_lock) therefore blocks other process that query the same table. Any help is appreciated. thanks.Ouch. That's just asking for locking problems... Why do you this using a
cursor? Have you tried doing it in a set-based fashion?
Andrés Taylor
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.|||Tom,
Sounds like a very poor way in general to do updates and you should probably
look into redoing the task using as much set and server based approaches as
possible. But in this case I think the real issue might be that there is a
BEGIN TRAN issued (either implicitly or explicitly) at the beginning and
thus the whole operation is wrapped in one large transaction. You can use
sp_who2, sp_lock and profiler to see for sure what is going on.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:182071FF-981D-4139-A29F-11B81157BFD2@.microsoft.com...
> We are experencing some serious locking issue on sql server (sql2000).
Basically a method in VB component opens a recordset which loops through a
table A (about 100,000 times), inside the loop, it calls a stpred proc and
does an update on table A. Each update in the loop is quite small, a few
records at the most. Table A has an update trigger which insert the updated
records to a history table then delete this record from table a. There are
some f other queries running on table a. The big update loop on table A
causes locking issues. With every update in the loop, there are hundred of
locks on the delete job when it's blocking other jobs. I can't fingure out
why continuous small updates opens so many locks(by looking at sp_lock)
therefore blocks other process that query the same table. Any help is
appreciated. thanks.

BLOB or PIPING a Select

I posted this a few months ago, but at the time did not find a simple method
..
I would like to have a select statement output into a single result...
IE
select email from a.client
rather then return as:
user1@.user.com
user2@.user.com
user3@.user.com
I would like:
user1@.user.comuser1@.user.comuser1@.user.com
Basically treating the result as a single row rather then one for each...
anythoughts or ideas? Am I on crack?
Jordyhttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Jordy Boss" wrote:

> I posted this a few months ago, but at the time did not find a simple meth
od...
> I would like to have a select statement output into a single result...
> IE
> select email from a.client
> rather then return as:
> user1@.user.com
> user2@.user.com
> user3@.user.com
> I would like:
> user1@.user.comuser1@.user.comuser1@.user.com
> Basically treating the result as a single row rather then one for each...
> anythoughts or ideas? Am I on crack?
> Jordy

BLOB

Hello,
I am looking into storing images into SQL and want to use BLOB which I
understand to be the best method. Can someone supply me with a simple
example of how to import a file into the database?
Thanks in advance,
John
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>

BLOB

Hello,
I am looking into storing images into SQL and want to use BLOB which I
understand to be the best method. Can someone supply me with a simple
example of how to import a file into the database?
Thanks in advance,
John
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>
|||Hi John:
Can you please tel me how I can store documents like word and Excel to the SQL Server Database? If you can email me at doctor27_in@.yahoo.com, I would highly appriciate it.
Thanks,
Nikunj

Quote:

Originally posted by John A. Curry
Well it is amazing what you find after you post. After much searching I
found it on my own. For anyone else who may have wanted to know how to
populate a image type, there is an executable that will do it called
TextCopy.exe. A sample command line is below.
C:\MyDocs\My
Pictures>textcopy -I -Usa -PMyPassword -SMyServer -DMyDatabase -TMyTable -CI
mageColumn -W"where ID = 3" -Fsample.jpg /Z
TEXTCOPY Version 1.0
DB-Library version 8.00.194
debug: Final parameters:
debug: Server: MyServer
debug: Login: sa
debug: Password: MyPassword
debug: Database: MyDatabase
debug: Table: MyTable
debug: Column: ImageColumn
debug: Where clause: where ID = 3
debug: File: sample.jpg
debug: Direction: Into SQL Server from file.
debug: Chunk size: 4096 bytes
SQL Server 'MyServer' Message 5701: Changed database context to 'master'.
SQL Server 'MyServer' Message 5701: Changed database context to
'MyDatabase'. (Concerning line 1)
debug: Query: select ImageColumn from MyTable where ID = 3
debug: File 'sample.jpg' opened for read
debug: File is 9894 bytes long
debug: Read 4096 bytes from file
debug: Read 4096 bytes from file
debug: Read 1702 bytes from file
debug: File closed
Data copied into SQL Server image column from file 'sample.jpg'.
The other key I found is that the existing column must not be null. I
resolved this by running the following.
INSERT INTO [dbo].[MyTable] VALUES ('3', 0x0 )
If you don't do this you will get the following error when you run in debug
(-Z)
ERROR: Text or image pointer and timestamp retrieval failed.
There is also a stored procedure if you would like it let me know and I can
post that as well.
-John
"John A. Curry" <JohnCurry@.Captaris.com> wrote in message
news:uzYznuiXEHA.2408@.tk2msftngp13.phx.gbl...
> Hello,
> I am looking into storing images into SQL and want to use BLOB which I
> understand to be the best method. Can someone supply me with a simple
> example of how to import a file into the database?
> Thanks in advance,
> John
>

bizarre job behavior

Over the weekend I transferred our non Clustered SQL Server to a new
Clustered environment. I moved the jobs by method of scripting. Since then,
serveral of my jobs have been failing with different problems. The one thing
all of these jobs have in common is that they all call DTS Packages that
read/ write to files. (.txt, .xls, etc.)
For the record, the account that the SQL Agent runs in has full control over
these files. If I log directly onto the console with this account, I can
create/ edit/ drop anything I want.
Another fun fact is that I can run all of these DTS Packages manually.
Now here is all of the problems I'm having with these jobs.
1. Access denied for the account that SQL runs in to read/ write to the
file.
2. I start the job manually, it never runs. RClick/ Start Job... nothing
ever happens.
3. Although the job has a schedule, it shows (Date and time are not
available.) under the Next Run Date column.
4. Jobs have a staus of "Performing completion actions" forever. I found
this one in KB, but think its really a by product of the other issues.
Something is obviously very wrong here. Any ideas are greatly appreciated.
--
SQL2K SP3
TIA, ChrisRLook in the 'sysjobs' table of the msdb database. See if
the there is an entry for the jobs. If there is, check
the 'originating_server' column. It may still have the old
server name in it......
>--Original Message--
>Over the weekend I transferred our non Clustered SQL
Server to a new
>Clustered environment. I moved the jobs by method of
scripting. Since then,
>serveral of my jobs have been failing with different
problems. The one thing
>all of these jobs have in common is that they all call
DTS Packages that
>read/ write to files. (.txt, .xls, etc.)
>For the record, the account that the SQL Agent runs in
has full control over
>these files. If I log directly onto the console with this
account, I can
>create/ edit/ drop anything I want.
>Another fun fact is that I can run all of these DTS
Packages manually.
>Now here is all of the problems I'm having with these
jobs.
>1. Access denied for the account that SQL runs in to
read/ write to the
>file.
>2. I start the job manually, it never runs. RClick/ Start
Job... nothing
>ever happens.
>3. Although the job has a schedule, it shows (Date and
time are not
>available.) under the Next Run Date column.
>4. Jobs have a staus of "Performing completion actions"
forever. I found
>this one in KB, but think its really a by product of the
other issues.
>Something is obviously very wrong here. Any ideas are
greatly appreciated.
>
>--
>SQL2K SP3
>TIA, ChrisR
>
>.
>|||No, it has the correct server name since I scripted/ created the jobs
instead of moving them.
"Coskun" <anonymous@.discussions.microsoft.com> wrote in message
news:157b01c51ab8$685e91e0$a601280a@.phx.gbl...
> Look in the 'sysjobs' table of the msdb database. See if
> the there is an entry for the jobs. If there is, check
> the 'originating_server' column. It may still have the old
> server name in it......
>
> >--Original Message--
> >Over the weekend I transferred our non Clustered SQL
> Server to a new
> >Clustered environment. I moved the jobs by method of
> scripting. Since then,
> >serveral of my jobs have been failing with different
> problems. The one thing
> >all of these jobs have in common is that they all call
> DTS Packages that
> >read/ write to files. (.txt, .xls, etc.)
> >
> >For the record, the account that the SQL Agent runs in
> has full control over
> >these files. If I log directly onto the console with this
> account, I can
> >create/ edit/ drop anything I want.
> >
> >Another fun fact is that I can run all of these DTS
> Packages manually.
> >
> >Now here is all of the problems I'm having with these
> jobs.
> >
> >1. Access denied for the account that SQL runs in to
> read/ write to the
> >file.
> >2. I start the job manually, it never runs. RClick/ Start
> Job... nothing
> >ever happens.
> >3. Although the job has a schedule, it shows (Date and
> time are not
> >available.) under the Next Run Date column.
> >4. Jobs have a staus of "Performing completion actions"
> forever. I found
> >this one in KB, but think its really a by product of the
> other issues.
> >
> >Something is obviously very wrong here. Any ideas are
> greatly appreciated.
> >
> >
> >
> >--
> >SQL2K SP3
> >
> >TIA, ChrisR
> >
> >
> >.
> >