I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
Jon
Sounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:
>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>
sql
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Thursday, March 22, 2012
Access accessing SQL {}
Im using Access to access a SQL database. For one of the queries i see the following under column:
{ fn month(dbo.TableName.Date) }
Ive never used this structure before but im confused to what it is? Can anyone explain please?
Thanks
The curly braces and 'fn' server to denote a OBDC level function.
The T-SQL equilivent would be:
|||
month( YourDateColumn )
Hello
Thanks for that. Could you explain a bit more on that please? As far as im concerned i didnt think there would be an ODBC link as it was connecting straight to a SQL database (checking ODBC theres no link to a SQL database either). Maybe an example if you can on what this user could have been trying to achieve would be great?
Thanks
Access 97 linked to SQL 2000 large fields problems
Hi there,
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
Tim
Well, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim
|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:
>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Tim
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
Tim
Well, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim
|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:
>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Tim
Monday, March 19, 2012
Access 2000 & Stored Procedures
Hi All,
I've a problem that requires a fairly lengthy introduction
by me. If you've the time and the willpower I'd really
appreciate your help.
I'm running a application based on Access 2000 and SQL
Server 2000.
The system is designed to allocate a single record to a
callcentre agent based on it being the most relevant
record to call at the time (designated next call time,
previous result etc.
The agents Access db uses a pass-through query to run a
stored procedure with a variable of the agent's ID.
There stored procedure uses a query to get a single record
to call and then updates a locking field within that
record with the agent ID. The query will exclude any
records already containing an agent ID. The stored
procedure then passes the unique ID of the record back to
the Access db. Access then uses find first to go to the
correct record.
When the agent finishes with that record it sets the
locking field back to 0 and the process starts again!
The problem is that if several agents request a record at
the same time the SQL profiler shows different requests
being run for each agent, but they get the same unique ID
passed back to all of them, and the record is locked with
the agent ID of the first agent to make the request. The
agents will all get the same record on screen and attempt
to call the same company, which is obviously not ideal!
Thanks in advance
p.s In case it helps, here is the stored procedure I run:
CREATE PROC spLockNewRec
@.PHIDToLock smallint
AS
DECLARE @.CSIDToLock int
BEGIN
UPDATE tblClientFile SET tblClientFile.intPHIDLock = 0
WHERE tblClientFile.intPHIDLock = @.PHIDToLock
END
SET rowcount 1
BEGIN TRANSACTION
SELECT @.CSIDToLock = dbo.tblClientFile.CSID
FROM dbo.tblDecisionMakers RIGHT OUTER JOIN
dbo.tblClientFile ON
dbo.tblDecisionMakers.CSID = dbo.tblClientFile.CSID LEFT
OUTER JOIN
dbo.tblCallResult ON
dbo.tblDecisionMakers.CRID = dbo.tblCallResult.CRID
WHERE (dbo.tblDecisionMakers.dtmNextCallTime <= GETDATE
()) AND (dbo.tblClientFile.intPHIDLock = 0) AND (NOT
(dbo.tblCallResult.intCallbackType = 9) OR
dbo.tblCallResult.intCallbackType IS
NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL)) OR
(dbo.tblDecisionMakers.dtmNextCallTime IS NULL) AND
(dbo.tblClientFile.intPHIDLock = 0) AND (NOT
(dbo.tblCallResult.intCallbackType = 9) OR
dbo.tblCallResult.intCallbackType IS
NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL))
ORDER BY dbo.tblCallResult.intCallPriority,
dbo.tblDecisionMakers.dtmNextCallTime
UPDATE tblClientFile
SET intPHIDLock = @.PHIDToLock, dtmPHIDLock = getdate()
where tblClientFile.CSID = @.CSIDToLock
COMMIT TRANSACTION
SELECT @.CSIDToLock
GO
What you want to do is to re-write your stored procedure, creating an
explicit transaction (see "Explicit Transactions" and related topics
in SQL BOL). Inside the transaction you'll select the record to call ,
update the locking field, and store the record ID in a local variable,
all as one unit of work. If the update fails, then someone else got
there first, so you code the logic (see @.@.rowcount) to rollback and
try again. This way you won't get duplicates returned to simultaneous
callers.
--Mary
On Thu, 24 Jun 2004 08:43:52 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
>Hi All,
>I've a problem that requires a fairly lengthy introduction
>by me. If you've the time and the willpower I'd really
>appreciate your help.
>I'm running a application based on Access 2000 and SQL
>Server 2000.
>The system is designed to allocate a single record to a
>callcentre agent based on it being the most relevant
>record to call at the time (designated next call time,
>previous result etc.
>The agents Access db uses a pass-through query to run a
>stored procedure with a variable of the agent's ID.
>There stored procedure uses a query to get a single record
>to call and then updates a locking field within that
>record with the agent ID. The query will exclude any
>records already containing an agent ID. The stored
>procedure then passes the unique ID of the record back to
>the Access db. Access then uses find first to go to the
>correct record.
>When the agent finishes with that record it sets the
>locking field back to 0 and the process starts again!
>The problem is that if several agents request a record at
>the same time the SQL profiler shows different requests
>being run for each agent, but they get the same unique ID
>passed back to all of them, and the record is locked with
>the agent ID of the first agent to make the request. The
>agents will all get the same record on screen and attempt
>to call the same company, which is obviously not ideal!
>Thanks in advance
>p.s In case it helps, here is the stored procedure I run:
>CREATE PROC spLockNewRec
>@.PHIDToLock smallint
>AS
>DECLARE @.CSIDToLock int
>BEGIN
>UPDATE tblClientFile SET tblClientFile.intPHIDLock = 0
>WHERE tblClientFile.intPHIDLock = @.PHIDToLock
>END
>SET rowcount 1
>BEGIN TRANSACTION
>SELECT @.CSIDToLock = dbo.tblClientFile.CSID
>FROM dbo.tblDecisionMakers RIGHT OUTER JOIN
> dbo.tblClientFile ON
>dbo.tblDecisionMakers.CSID = dbo.tblClientFile.CSID LEFT
>OUTER JOIN
> dbo.tblCallResult ON
>dbo.tblDecisionMakers.CRID = dbo.tblCallResult.CRID
>WHERE (dbo.tblDecisionMakers.dtmNextCallTime <= GETDATE
>()) AND (dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>(dbo.tblCallResult.intCallbackType = 9) OR
> dbo.tblCallResult.intCallbackType IS
>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL)) OR
>(dbo.tblDecisionMakers.dtmNextCallTime IS NULL) AND
>(dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>(dbo.tblCallResult.intCallbackType = 9) OR
> dbo.tblCallResult.intCallbackType IS
>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL))
>ORDER BY dbo.tblCallResult.intCallPriority,
>dbo.tblDecisionMakers.dtmNextCallTime
>UPDATE tblClientFile
>SET intPHIDLock = @.PHIDToLock, dtmPHIDLock = getdate()
>where tblClientFile.CSID = @.CSIDToLock
>COMMIT TRANSACTION
>SELECT @.CSIDToLock
>GO
|||Thanks for this Mary. I implemented your suggestion but
unfortunately I've still got a problem and multiple users
can still get the same record.
I think it is if 2 or more users run the stored procedure
at the same / almost the same time they both manage to get
the same @.CSIDToLock (from my Stored Proc at the bottom of
this post) from the Select statement, each before the
other has run the update query. They both then run the
update query and both manage to update the single row so
@.@.RowCount = 1. The problem of course being that the
second person to do it overwrites the first persons
change. Is there a way to ensure that the 2 actions
(select and then update) run without anyone else being
able to run them at the same time?
David
>--Original Message--
>What you want to do is to re-write your stored procedure,
creating an
>explicit transaction (see "Explicit Transactions" and
related topics
>in SQL BOL). Inside the transaction you'll select the
record to call ,
>update the locking field, and store the record ID in a
local variable,
>all as one unit of work. If the update fails, then
someone else got
>there first, so you code the logic (see @.@.rowcount) to
rollback and
>try again. This way you won't get duplicates returned to
simultaneous[vbcol=seagreen]
>callers.
>--Mary
>On Thu, 24 Jun 2004 08:43:52 -0700, "David"
><anonymous@.discussions.microsoft.com> wrote:
introduction[vbcol=seagreen]
record[vbcol=seagreen]
to[vbcol=seagreen]
at[vbcol=seagreen]
ID[vbcol=seagreen]
with[vbcol=seagreen]
The[vbcol=seagreen]
attempt[vbcol=seagreen]
GETDATE[vbcol=seagreen]
IS[vbcol=seagreen]
IS
>.
>
|||How about defining a column of data type TimeStamp, retrieve the TimeStamp
value at at the time you select your record prior to the update. For the
Update statement, specify WHERE TimeStampColumn = SavedTSValue. The only way
this value will be equivelent is if the row were not updated since your
retrieval.
Steve
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:2184101c45ac8$56276d40$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for this Mary. I implemented your suggestion but
> unfortunately I've still got a problem and multiple users
> can still get the same record.
> I think it is if 2 or more users run the stored procedure
> at the same / almost the same time they both manage to get
> the same @.CSIDToLock (from my Stored Proc at the bottom of
> this post) from the Select statement, each before the
> other has run the update query. They both then run the
> update query and both manage to update the single row so
> @.@.RowCount = 1. The problem of course being that the
> second person to do it overwrites the first persons
> change. Is there a way to ensure that the 2 actions
> (select and then update) run without anyone else being
> able to run them at the same time?
> David
> creating an
> related topics
> record to call ,
> local variable,
> someone else got
> rollback and
> simultaneous
> introduction
> record
> to
> at
> ID
> with
> The
> attempt
> GETDATE
> IS
> IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for this Mary. I implemented your suggestion but
>unfortunately I've still got a problem and multiple users
>can still get the same record.
>I think it is if 2 or more users run the stored procedure
>at the same / almost the same time they both manage to get
>the same @.CSIDToLock (from my Stored Proc at the bottom of
>this post) from the Select statement, each before the
>other has run the update query. They both then run the
>update query and both manage to update the single row so
>@.@.RowCount = 1. The problem of course being that the
>second person to do it overwrites the first persons
>change. Is there a way to ensure that the 2 actions
>(select and then update) run without anyone else being
>able to run them at the same time?
>David
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for this Mary. I implemented your suggestion but
>unfortunately I've still got a problem and multiple users
>can still get the same record.
>I think it is if 2 or more users run the stored procedure
>at the same / almost the same time they both manage to get
>the same @.CSIDToLock (from my Stored Proc at the bottom of
>this post) from the Select statement, each before the
>other has run the update query. They both then run the
>update query and both manage to update the single row so
>@.@.RowCount = 1. The problem of course being that the
>second person to do it overwrites the first persons
>change. Is there a way to ensure that the 2 actions
>(select and then update) run without anyone else being
>able to run them at the same time?
>David
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.
>--Original Message--
>How about defining a column of data type TimeStamp,
retrieve the TimeStamp
>value at at the time you select your record prior to the
update. For the
>Update statement, specify WHERE TimeStampColumn =
SavedTSValue. The only way
>this value will be equivelent is if the row were not
updated since your
>retrieval.
>Steve
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.
>--Original Message--
>How about defining a column of data type TimeStamp,
retrieve the TimeStamp
>value at at the time you select your record prior to the
update. For the
>Update statement, specify WHERE TimeStampColumn =
SavedTSValue. The only way
>this value will be equivelent is if the row were not
updated since your
>retrieval.
>Steve
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>
I've a problem that requires a fairly lengthy introduction
by me. If you've the time and the willpower I'd really
appreciate your help.
I'm running a application based on Access 2000 and SQL
Server 2000.
The system is designed to allocate a single record to a
callcentre agent based on it being the most relevant
record to call at the time (designated next call time,
previous result etc.
The agents Access db uses a pass-through query to run a
stored procedure with a variable of the agent's ID.
There stored procedure uses a query to get a single record
to call and then updates a locking field within that
record with the agent ID. The query will exclude any
records already containing an agent ID. The stored
procedure then passes the unique ID of the record back to
the Access db. Access then uses find first to go to the
correct record.
When the agent finishes with that record it sets the
locking field back to 0 and the process starts again!
The problem is that if several agents request a record at
the same time the SQL profiler shows different requests
being run for each agent, but they get the same unique ID
passed back to all of them, and the record is locked with
the agent ID of the first agent to make the request. The
agents will all get the same record on screen and attempt
to call the same company, which is obviously not ideal!
Thanks in advance
p.s In case it helps, here is the stored procedure I run:
CREATE PROC spLockNewRec
@.PHIDToLock smallint
AS
DECLARE @.CSIDToLock int
BEGIN
UPDATE tblClientFile SET tblClientFile.intPHIDLock = 0
WHERE tblClientFile.intPHIDLock = @.PHIDToLock
END
SET rowcount 1
BEGIN TRANSACTION
SELECT @.CSIDToLock = dbo.tblClientFile.CSID
FROM dbo.tblDecisionMakers RIGHT OUTER JOIN
dbo.tblClientFile ON
dbo.tblDecisionMakers.CSID = dbo.tblClientFile.CSID LEFT
OUTER JOIN
dbo.tblCallResult ON
dbo.tblDecisionMakers.CRID = dbo.tblCallResult.CRID
WHERE (dbo.tblDecisionMakers.dtmNextCallTime <= GETDATE
()) AND (dbo.tblClientFile.intPHIDLock = 0) AND (NOT
(dbo.tblCallResult.intCallbackType = 9) OR
dbo.tblCallResult.intCallbackType IS
NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL)) OR
(dbo.tblDecisionMakers.dtmNextCallTime IS NULL) AND
(dbo.tblClientFile.intPHIDLock = 0) AND (NOT
(dbo.tblCallResult.intCallbackType = 9) OR
dbo.tblCallResult.intCallbackType IS
NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL))
ORDER BY dbo.tblCallResult.intCallPriority,
dbo.tblDecisionMakers.dtmNextCallTime
UPDATE tblClientFile
SET intPHIDLock = @.PHIDToLock, dtmPHIDLock = getdate()
where tblClientFile.CSID = @.CSIDToLock
COMMIT TRANSACTION
SELECT @.CSIDToLock
GO
What you want to do is to re-write your stored procedure, creating an
explicit transaction (see "Explicit Transactions" and related topics
in SQL BOL). Inside the transaction you'll select the record to call ,
update the locking field, and store the record ID in a local variable,
all as one unit of work. If the update fails, then someone else got
there first, so you code the logic (see @.@.rowcount) to rollback and
try again. This way you won't get duplicates returned to simultaneous
callers.
--Mary
On Thu, 24 Jun 2004 08:43:52 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
>Hi All,
>I've a problem that requires a fairly lengthy introduction
>by me. If you've the time and the willpower I'd really
>appreciate your help.
>I'm running a application based on Access 2000 and SQL
>Server 2000.
>The system is designed to allocate a single record to a
>callcentre agent based on it being the most relevant
>record to call at the time (designated next call time,
>previous result etc.
>The agents Access db uses a pass-through query to run a
>stored procedure with a variable of the agent's ID.
>There stored procedure uses a query to get a single record
>to call and then updates a locking field within that
>record with the agent ID. The query will exclude any
>records already containing an agent ID. The stored
>procedure then passes the unique ID of the record back to
>the Access db. Access then uses find first to go to the
>correct record.
>When the agent finishes with that record it sets the
>locking field back to 0 and the process starts again!
>The problem is that if several agents request a record at
>the same time the SQL profiler shows different requests
>being run for each agent, but they get the same unique ID
>passed back to all of them, and the record is locked with
>the agent ID of the first agent to make the request. The
>agents will all get the same record on screen and attempt
>to call the same company, which is obviously not ideal!
>Thanks in advance
>p.s In case it helps, here is the stored procedure I run:
>CREATE PROC spLockNewRec
>@.PHIDToLock smallint
>AS
>DECLARE @.CSIDToLock int
>BEGIN
>UPDATE tblClientFile SET tblClientFile.intPHIDLock = 0
>WHERE tblClientFile.intPHIDLock = @.PHIDToLock
>END
>SET rowcount 1
>BEGIN TRANSACTION
>SELECT @.CSIDToLock = dbo.tblClientFile.CSID
>FROM dbo.tblDecisionMakers RIGHT OUTER JOIN
> dbo.tblClientFile ON
>dbo.tblDecisionMakers.CSID = dbo.tblClientFile.CSID LEFT
>OUTER JOIN
> dbo.tblCallResult ON
>dbo.tblDecisionMakers.CRID = dbo.tblCallResult.CRID
>WHERE (dbo.tblDecisionMakers.dtmNextCallTime <= GETDATE
>()) AND (dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>(dbo.tblCallResult.intCallbackType = 9) OR
> dbo.tblCallResult.intCallbackType IS
>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL)) OR
>(dbo.tblDecisionMakers.dtmNextCallTime IS NULL) AND
>(dbo.tblClientFile.intPHIDLock = 0) AND (NOT
>(dbo.tblCallResult.intCallbackType = 9) OR
> dbo.tblCallResult.intCallbackType IS
>NULL) AND (NOT (dbo.tblClientFile.charTel IS NULL))
>ORDER BY dbo.tblCallResult.intCallPriority,
>dbo.tblDecisionMakers.dtmNextCallTime
>UPDATE tblClientFile
>SET intPHIDLock = @.PHIDToLock, dtmPHIDLock = getdate()
>where tblClientFile.CSID = @.CSIDToLock
>COMMIT TRANSACTION
>SELECT @.CSIDToLock
>GO
|||Thanks for this Mary. I implemented your suggestion but
unfortunately I've still got a problem and multiple users
can still get the same record.
I think it is if 2 or more users run the stored procedure
at the same / almost the same time they both manage to get
the same @.CSIDToLock (from my Stored Proc at the bottom of
this post) from the Select statement, each before the
other has run the update query. They both then run the
update query and both manage to update the single row so
@.@.RowCount = 1. The problem of course being that the
second person to do it overwrites the first persons
change. Is there a way to ensure that the 2 actions
(select and then update) run without anyone else being
able to run them at the same time?
David
>--Original Message--
>What you want to do is to re-write your stored procedure,
creating an
>explicit transaction (see "Explicit Transactions" and
related topics
>in SQL BOL). Inside the transaction you'll select the
record to call ,
>update the locking field, and store the record ID in a
local variable,
>all as one unit of work. If the update fails, then
someone else got
>there first, so you code the logic (see @.@.rowcount) to
rollback and
>try again. This way you won't get duplicates returned to
simultaneous[vbcol=seagreen]
>callers.
>--Mary
>On Thu, 24 Jun 2004 08:43:52 -0700, "David"
><anonymous@.discussions.microsoft.com> wrote:
introduction[vbcol=seagreen]
record[vbcol=seagreen]
to[vbcol=seagreen]
at[vbcol=seagreen]
ID[vbcol=seagreen]
with[vbcol=seagreen]
The[vbcol=seagreen]
attempt[vbcol=seagreen]
GETDATE[vbcol=seagreen]
IS[vbcol=seagreen]
IS
>.
>
|||How about defining a column of data type TimeStamp, retrieve the TimeStamp
value at at the time you select your record prior to the update. For the
Update statement, specify WHERE TimeStampColumn = SavedTSValue. The only way
this value will be equivelent is if the row were not updated since your
retrieval.
Steve
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:2184101c45ac8$56276d40$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for this Mary. I implemented your suggestion but
> unfortunately I've still got a problem and multiple users
> can still get the same record.
> I think it is if 2 or more users run the stored procedure
> at the same / almost the same time they both manage to get
> the same @.CSIDToLock (from my Stored Proc at the bottom of
> this post) from the Select statement, each before the
> other has run the update query. They both then run the
> update query and both manage to update the single row so
> @.@.RowCount = 1. The problem of course being that the
> second person to do it overwrites the first persons
> change. Is there a way to ensure that the 2 actions
> (select and then update) run without anyone else being
> able to run them at the same time?
> David
> creating an
> related topics
> record to call ,
> local variable,
> someone else got
> rollback and
> simultaneous
> introduction
> record
> to
> at
> ID
> with
> The
> attempt
> GETDATE
> IS
> IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for this Mary. I implemented your suggestion but
>unfortunately I've still got a problem and multiple users
>can still get the same record.
>I think it is if 2 or more users run the stored procedure
>at the same / almost the same time they both manage to get
>the same @.CSIDToLock (from my Stored Proc at the bottom of
>this post) from the Select statement, each before the
>other has run the update query. They both then run the
>update query and both manage to update the single row so
>@.@.RowCount = 1. The problem of course being that the
>second person to do it overwrites the first persons
>change. Is there a way to ensure that the 2 actions
>(select and then update) run without anyone else being
>able to run them at the same time?
>David
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||If I understand your schema correctly, the final UPDATE inside of the
transaction needs to have the same WHERE clause as the SELECT, with
the locking field/agent ID set to the same value. Then you need to
handle the conflict in the rollback. The first person there wins, but
the second person doesn't overwrite the first person because the
update's where clause prevents it.
--Mary
On Fri, 25 Jun 2004 08:23:15 -0700, "David"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for this Mary. I implemented your suggestion but
>unfortunately I've still got a problem and multiple users
>can still get the same record.
>I think it is if 2 or more users run the stored procedure
>at the same / almost the same time they both manage to get
>the same @.CSIDToLock (from my Stored Proc at the bottom of
>this post) from the Select statement, each before the
>other has run the update query. They both then run the
>update query and both manage to update the single row so
>@.@.RowCount = 1. The problem of course being that the
>second person to do it overwrites the first persons
>change. Is there a way to ensure that the 2 actions
>(select and then update) run without anyone else being
>able to run them at the same time?
>David
>creating an
>related topics
>record to call ,
>local variable,
>someone else got
>rollback and
>simultaneous
>introduction
>record
>to
>at
>ID
>with
>The
>attempt
>GETDATE
>IS
>IS
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.
>--Original Message--
>How about defining a column of data type TimeStamp,
retrieve the TimeStamp
>value at at the time you select your record prior to the
update. For the
>Update statement, specify WHERE TimeStampColumn =
SavedTSValue. The only way
>this value will be equivelent is if the row were not
updated since your
>retrieval.
>Steve
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>
|||Thanks very much to the both of you for your help.
I've implemented the timestamp column in the db. After
then resolving some deadlocking issues I've got the system
working correctly.
>--Original Message--
>How about defining a column of data type TimeStamp,
retrieve the TimeStamp
>value at at the time you select your record prior to the
update. For the
>Update statement, specify WHERE TimeStampColumn =
SavedTSValue. The only way
>this value will be equivelent is if the row were not
updated since your
>retrieval.
>Steve
>"David" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2184101c45ac8$56276d40$a401280a@.phx.gbl...
users[vbcol=seagreen]
procedure[vbcol=seagreen]
get[vbcol=seagreen]
of[vbcol=seagreen]
procedure,[vbcol=seagreen]
to[vbcol=seagreen]
really[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
the[vbcol=seagreen]
requests[vbcol=seagreen]
ideal![vbcol=seagreen]
run:[vbcol=seagreen]
LEFT[vbcol=seagreen]
dbo.tblCallResult.intCallbackType[vbcol=seagreen]
dbo.tblCallResult.intCallbackType
>
>.
>
Access .mdb link to SQL Server 2000
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
|||Thanks Steve - I'll give that a go and let you know how I get on.
"Steve Thompson" <stevethompson@.nomail.please> wrote in message news:OH$IIvOGFHA.936@.TK2MSFTNGP12.phx.gbl...
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
|||Thanks Steve - I'll give that a go and let you know how I get on.
"Steve Thompson" <stevethompson@.nomail.please> wrote in message news:OH$IIvOGFHA.936@.TK2MSFTNGP12.phx.gbl...
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
Thursday, March 8, 2012
Absolut Noob here.
Hi all,
I've been working with SQL Server 2000 for most of the development I do but
now we're looking to build a small database that can run locally on the
client machine along with the application with which it will be distributed.
The following are my questions regarding this.
What do I need to do to properly create a database that will be accessible
by MSDE on a client machine?What is the best way to access it once it's
there (ie creating an ODBC System DSN)?
Can I do all of the database design work in SQL Server 2000?
Thanks in advance for the help.
Regards,
Daniel Hobert
hi Daniel,
Daniel Hobert wrote:
> Hi all,
> I've been working with SQL Server 2000 for most of the development I
> do but now we're looking to build a small database that can run
> locally on the client machine along with the application with which
> it will be distributed.
as you are used to SQL Server, MSDE is the same engine (with some
limitations) and all you know about SQL Server is valid for MSDE as long as
you do not hit it's limitations...
> The following are my questions regarding this.
> What do I need to do to properly create a database that will be
> accessible by MSDE on a client machine?
you have all the SQL Server possibilities available, from detach-attach,
backup/restore and script/exec...
I am an estimator of the latter, where you get the DDL code from your source
control system to create the user's database(s) your application need, where
you inherit from destination model database all the available settings as
long as included objects/users...
just for your note, the best article I read about such a kind of
installation, including database schema support is
http://msdn.microsoft.com/msdnmag/is...einstaller/...
>What is the best way to access
> it once it's there (ie creating an ODBC System DSN)?
it really depends on your coding standard... personally I always use
DSN-less connections as ADO/Ado.Net support them very well...
> Can I do all of the database design work in SQL Server 2000?
yes and you should... MSDE is intended as a distribution platform and not as
a development paltform, with all of your dev tools available, like EM, Query
Analyzer, Index Tuning, Profiler and the like..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks a ton for the quick response and the link to the article
.
Cheers,
Daniel
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3h65jcFf4sp8U1@.individual.net...
> hi Daniel,
> Daniel Hobert wrote:
>
> as you are used to SQL Server, MSDE is the same engine (with some
> limitations) and all you know about SQL Server is valid for MSDE as long
> as you do not hit it's limitations...
>
> you have all the SQL Server possibilities available, from detach-attach,
> backup/restore and script/exec...
> I am an estimator of the latter, where you get the DDL code from your
> source control system to create the user's database(s) your application
> need, where you inherit from destination model database all the available
> settings as long as included objects/users...
> just for your note, the best article I read about such a kind of
> installation, including database schema support is
> http://msdn.microsoft.com/msdnmag/is...einstaller/...
>
> it really depends on your coding standard... personally I always use
> DSN-less connections as ADO/Ado.Net support them very well...
>
> yes and you should... MSDE is intended as a distribution platform and not
> as a development paltform, with all of your dev tools available, like EM,
> Query Analyzer, Index Tuning, Profiler and the like..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
I've been working with SQL Server 2000 for most of the development I do but
now we're looking to build a small database that can run locally on the
client machine along with the application with which it will be distributed.
The following are my questions regarding this.
What do I need to do to properly create a database that will be accessible
by MSDE on a client machine?What is the best way to access it once it's
there (ie creating an ODBC System DSN)?
Can I do all of the database design work in SQL Server 2000?
Thanks in advance for the help.
Regards,
Daniel Hobert
hi Daniel,
Daniel Hobert wrote:
> Hi all,
> I've been working with SQL Server 2000 for most of the development I
> do but now we're looking to build a small database that can run
> locally on the client machine along with the application with which
> it will be distributed.
as you are used to SQL Server, MSDE is the same engine (with some
limitations) and all you know about SQL Server is valid for MSDE as long as
you do not hit it's limitations...
> The following are my questions regarding this.
> What do I need to do to properly create a database that will be
> accessible by MSDE on a client machine?
you have all the SQL Server possibilities available, from detach-attach,
backup/restore and script/exec...
I am an estimator of the latter, where you get the DDL code from your source
control system to create the user's database(s) your application need, where
you inherit from destination model database all the available settings as
long as included objects/users...
just for your note, the best article I read about such a kind of
installation, including database schema support is
http://msdn.microsoft.com/msdnmag/is...einstaller/...
>What is the best way to access
> it once it's there (ie creating an ODBC System DSN)?
it really depends on your coding standard... personally I always use
DSN-less connections as ADO/Ado.Net support them very well...
> Can I do all of the database design work in SQL Server 2000?
yes and you should... MSDE is intended as a distribution platform and not as
a development paltform, with all of your dev tools available, like EM, Query
Analyzer, Index Tuning, Profiler and the like..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks a ton for the quick response and the link to the article
.Cheers,
Daniel
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3h65jcFf4sp8U1@.individual.net...
> hi Daniel,
> Daniel Hobert wrote:
>
> as you are used to SQL Server, MSDE is the same engine (with some
> limitations) and all you know about SQL Server is valid for MSDE as long
> as you do not hit it's limitations...
>
> you have all the SQL Server possibilities available, from detach-attach,
> backup/restore and script/exec...
> I am an estimator of the latter, where you get the DDL code from your
> source control system to create the user's database(s) your application
> need, where you inherit from destination model database all the available
> settings as long as included objects/users...
> just for your note, the best article I read about such a kind of
> installation, including database schema support is
> http://msdn.microsoft.com/msdnmag/is...einstaller/...
>
> it really depends on your coding standard... personally I always use
> DSN-less connections as ADO/Ado.Net support them very well...
>
> yes and you should... MSDE is intended as a distribution platform and not
> as a development paltform, with all of your dev tools available, like EM,
> Query Analyzer, Index Tuning, Profiler and the like..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Tuesday, March 6, 2012
About to put 1.5 GB of images into a DB
I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new server
- just moved from SQL 2000.
We want to load 10000 images into it - with a raw JPG size of about 1.5 GB's
on disk. In the past we were storing the file path to these images - but now
want them in the DB.
So that's doubling the size of the DB - that does not scare me or scare the
DBA in charge of things.
But it's making us think about using a different FILEGROUP for this new
StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatype
column in this new table.
Opinions?
Experiences?
Thanks!
The main benefit of another filegroup is the ability to put that filegroup
onto a different physical disk array than the one that the rest of the data
is sitting on, should I/O contention become an issue. It certainly wouldn't
hurt to prepare for this upfront by going ahead with your plan, but it
probably won't help a whole lot either, unless you have more disks ready to
go...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6FFC5611-5A7A-43D6-9C6F-24BC22783FDC@.microsoft.com...
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new
> server
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5
> GB's
> on disk. In the past we were storing the file path to these images - but
> now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare
> the
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX)
> datatype
> column in this new table.
> Opinions?
> Experiences?
> Thanks!
|||On Aug 7, 4:50 pm, Steve Z <Ste...@.discussions.microsoft.com> wrote:
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new server
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5 GB's
> on disk. In the past we were storing the file path to these images - but now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare the
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatype
> column in this new table.
> Opinions?
> Experiences?
> Thanks!
Steve-
I was faced with a similar issue, and although we opted to not store
the images in the DB, just pointers to the filesystem, we also thought
of using a seperate filegroup. Our rational, was that our "data" was
going to account for only 10% of the size of the database, with the
rest being images. In case of disaster recovery, the users wanted the
Data portion back ASAP, even if that meant they cound not update/
delete/insert new images. We were looking into being able to backing
up and restoring the file groups seperately, so that we could meet
that requirement.
That being said, I much happier having a million plus files sitting on
the OS, and a database size of 60GB, rather then having a database
size of 600-700 GB.
|||Thanks for the info! We obviously aren't nearly as large as the figures you
just stated!
The DBA has managed to shrink the images quite a lot - it appears that the
original 1.5 GB is down to around 150-200 MB - making the whole issue be a
lot less of a concern.
I'm still interested in hearing other experiences!
Thanks!
"walsham" wrote:
> I was faced with a similar issue, and although we opted to not store
> the images in the DB, just pointers to the filesystem, we also thought
> of using a seperate filegroup. Our rational, was that our "data" was
> going to account for only 10% of the size of the database, with the
> rest being images. In case of disaster recovery, the users wanted the
> Data portion back ASAP, even if that meant they cound not update/
> delete/insert new images. We were looking into being able to backing
> up and restoring the file groups seperately, so that we could meet
> that requirement.
> That being said, I much happier having a million plus files sitting on
> the OS, and a database size of 60GB, rather then having a database
> size of 600-700 GB.
>
- just moved from SQL 2000.
We want to load 10000 images into it - with a raw JPG size of about 1.5 GB's
on disk. In the past we were storing the file path to these images - but now
want them in the DB.
So that's doubling the size of the DB - that does not scare me or scare the
DBA in charge of things.
But it's making us think about using a different FILEGROUP for this new
StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatype
column in this new table.
Opinions?
Experiences?
Thanks!
The main benefit of another filegroup is the ability to put that filegroup
onto a different physical disk array than the one that the rest of the data
is sitting on, should I/O contention become an issue. It certainly wouldn't
hurt to prepare for this upfront by going ahead with your plan, but it
probably won't help a whole lot either, unless you have more disks ready to
go...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6FFC5611-5A7A-43D6-9C6F-24BC22783FDC@.microsoft.com...
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new
> server
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5
> GB's
> on disk. In the past we were storing the file path to these images - but
> now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare
> the
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX)
> datatype
> column in this new table.
> Opinions?
> Experiences?
> Thanks!
|||On Aug 7, 4:50 pm, Steve Z <Ste...@.discussions.microsoft.com> wrote:
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new server
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5 GB's
> on disk. In the past we were storing the file path to these images - but now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare the
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatype
> column in this new table.
> Opinions?
> Experiences?
> Thanks!
Steve-
I was faced with a similar issue, and although we opted to not store
the images in the DB, just pointers to the filesystem, we also thought
of using a seperate filegroup. Our rational, was that our "data" was
going to account for only 10% of the size of the database, with the
rest being images. In case of disaster recovery, the users wanted the
Data portion back ASAP, even if that meant they cound not update/
delete/insert new images. We were looking into being able to backing
up and restoring the file groups seperately, so that we could meet
that requirement.
That being said, I much happier having a million plus files sitting on
the OS, and a database size of 60GB, rather then having a database
size of 600-700 GB.
|||Thanks for the info! We obviously aren't nearly as large as the figures you
just stated!
The DBA has managed to shrink the images quite a lot - it appears that the
original 1.5 GB is down to around 150-200 MB - making the whole issue be a
lot less of a concern.
I'm still interested in hearing other experiences!
Thanks!
"walsham" wrote:
> I was faced with a similar issue, and although we opted to not store
> the images in the DB, just pointers to the filesystem, we also thought
> of using a seperate filegroup. Our rational, was that our "data" was
> going to account for only 10% of the size of the database, with the
> rest being images. In case of disaster recovery, the users wanted the
> Data portion back ASAP, even if that meant they cound not update/
> delete/insert new images. We were looking into being able to backing
> up and restoring the file groups seperately, so that we could meet
> that requirement.
> That being said, I much happier having a million plus files sitting on
> the OS, and a database size of 60GB, rather then having a database
> size of 600-700 GB.
>
Sunday, February 19, 2012
About server authentication
Hi,
I've enabled the SQL Server and Windows Authentication mode, and then I
restarted the SQL server. My ASP.NET program can be access the server, but I
found that I cannot login the SQL server using Microsoft SQL Server
Management Studio.
I checked the ERRORLOG on my server, which shows following information. I'm
sure my password is correct.
Logon Error: 18456, Severity: 14, State: 8.
Logon Login failed for user 'sa'. [CLIENT: <named pipe>]
Logon Error: 18456, Severity: 14, State: 8.
Logon Login failed for user 'sa'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 8.
Login failed for user 'sa'. [CLIENT: 192.168.200.1]
Any suggestion for it?
Many thanks~
Yes, I'm using SQL Server 2005, and enabled the "sa" account.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eA$s%23yWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Are you on 2005? If so, is the "sa" account disabled?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daatmor" <daatmor@.mail.hongkong.com> wrote in message
> news:OBphWWyWHHA.4028@.TK2MSFTNGP04.phx.gbl...
>
I've enabled the SQL Server and Windows Authentication mode, and then I
restarted the SQL server. My ASP.NET program can be access the server, but I
found that I cannot login the SQL server using Microsoft SQL Server
Management Studio.
I checked the ERRORLOG on my server, which shows following information. I'm
sure my password is correct.
Logon Error: 18456, Severity: 14, State: 8.
Logon Login failed for user 'sa'. [CLIENT: <named pipe>]
Logon Error: 18456, Severity: 14, State: 8.
Logon Login failed for user 'sa'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 8.
Login failed for user 'sa'. [CLIENT: 192.168.200.1]
Any suggestion for it?
Many thanks~
Yes, I'm using SQL Server 2005, and enabled the "sa" account.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eA$s%23yWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Are you on 2005? If so, is the "sa" account disabled?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daatmor" <daatmor@.mail.hongkong.com> wrote in message
> news:OBphWWyWHHA.4028@.TK2MSFTNGP04.phx.gbl...
>
About restoring
I′m using SQL server 2005.
I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".
Is there a log for this?
How can I cancel this operation?
thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,
but is there a way to cancel?
How can users access this database?
thanks!!!!|||In the log file viewer, there is a line with the following:
The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.
How can I to do to use or to enable this database?
thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||
OK. That helps to clarify the issue.
The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.
So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.
On the last log backup, you would use RESTORE LOG WITH RECOVERY.
Each of these is a separate restore statement.
The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:
Recovered (all non-committed transactions rolled back and the database ready for use)
or Restoring (No roll-back performed, ready to apply the next log backup)
In your example, you specify that one RESTORE command should do both, which is not possible.
|||
Madhu K Nair wrote:
while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu
ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...
Madhu
|||So, did the second RESTORE command succeed? If it did, the database would be online.
If it did not, what messages are in the SQL log and Event log?
|||Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.
|||
I′m using SQL server 2005. I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".Is there a log for this?
How can I cancel this operation?thanks!!!!
i'm having this problem, two databases are hung in the middle of a restore.
how do i cancel this?
appreciated,
m
About restoring
I′m using SQL server 2005.
I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".
Is there a log for this?
How can I cancel this operation?
thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,
but is there a way to cancel?
How can users access this database?
thanks!!!!|||In the log file viewer, there is a line with the following:
The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.
How can I to do to use or to enable this database?
thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||
OK. That helps to clarify the issue.
The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.
So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.
On the last log backup, you would use RESTORE LOG WITH RECOVERY.
Each of these is a separate restore statement.
The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:
Recovered (all non-committed transactions rolled back and the database ready for use)
or Restoring (No roll-back performed, ready to apply the next log backup)
In your example, you specify that one RESTORE command should do both, which is not possible.
|||
while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu
ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...
Madhu
|||So, did the second RESTORE command succeed? If it did, the database would be online.
If it did not, what messages are in the SQL log and Event log?
|||Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.
|||
I′m using SQL server 2005. I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".Is there a log for this?
How can I cancel this operation?thanks!!!!
i'm having this problem, two databases are hung in the middle of a restore.
how do i cancel this?
appreciated,
m
About restoring
I′m using SQL server 2005.
I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".
Is there a log for this?
How can I cancel this operation?
thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,
but is there a way to cancel?
How can users access this database?
thanks!!!!|||In the log file viewer, there is a line with the following:
The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.
How can I to do to use or to enable this database?
thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||
OK. That helps to clarify the issue.
The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.
So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.
On the last log backup, you would use RESTORE LOG WITH RECOVERY.
Each of these is a separate restore statement.
The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:
Recovered (all non-committed transactions rolled back and the database ready for use)
or Restoring (No roll-back performed, ready to apply the next log backup)
In your example, you specify that one RESTORE command should do both, which is not possible.
|||
while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu
ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...
Madhu
|||So, did the second RESTORE command succeed? If it did, the database would be online.
If it did not, what messages are in the SQL log and Event log?
|||Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.
|||
I′m using SQL server 2005. I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".Is there a log for this?
How can I cancel this operation?thanks!!!!
i'm having this problem, two databases are hung in the middle of a restore.
how do i cancel this?
appreciated,
m
Subscribe to:
Posts (Atom)