Hi!
I have stored procedure that executes BULK INSERT command inside.
Recently I migrated my sql 7 database to sql 2000 SP2.
Now when running this stored procedure from both application and Query
Analyzer I'm getting this message:
Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
not be opened. Operating system error code 5(Access is denied.).
I found article on Microsoft web site that this is a known bug and it
only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
Why am I getting this error in SQL Server 2000?
Should I apply the latest SP to SQL 2000 to fix this security issue?
Thank you,
YelenaI checked all permissions.
SQL Server service is running under domain account and this user has
access to the share where the file is located.
Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
as sa.
I can't figure out why I'm getting Access Denied error.
Please help.
enaglozman@.yahoo.com (Lena) wrote in message news:<fc1e8071.0403160709.2dfad366@.posting.goo
gle.com>...
> Hi!
> I have stored procedure that executes BULK INSERT command inside.
> Recently I migrated my sql 7 database to sql 2000 SP2.
> Now when running this stored procedure from both application and Query
> Analyzer I'm getting this message:
> Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
> because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
> not be opened. Operating system error code 5(Access is denied.).
> I found article on Microsoft web site that this is a known bug and it
> only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
> Why am I getting this error in SQL Server 2000?
> Should I apply the latest SP to SQL 2000 to fix this security issue?
> Thank you,
> Yelena|||Does it fail if you are logged on as the account running MSSQLServer
service? Also, that account will need read permissions to the share/file
that you reference:
'\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close'
Steve
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:fc1e8071.0403230855.57f26b9a@.posting.google.com...
> I checked all permissions.
> SQL Server service is running under domain account and this user has
> access to the share where the file is located.
> Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
> as sa.
> I can't figure out why I'm getting Access Denied error.
> Please help.
> enaglozman@.yahoo.com (Lena) wrote in message
news:<fc1e8071.0403160709.2dfad366@.posting.google.com>...sql
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Access Deleting from MSDE - Help!
Hi
I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.
Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.
Can anyone help with this please?
Regards
Tom
hi Tom,
Tom wrote:
> Hi
> I am a newbie to MSDE and am having a problem deleting records from
> an MSDE backend using an Access query front end.
> Our main data is stored on a UNIX server (informix type database) and
> we link to it using ODBC. We load records to be updated into an
> Access database and then use the Access table as a reference for
> updating the MSDE database. This means I have a reference table in
> Access that is joined to the MSDE table. The records that exist in
> the Access table are the ones to be deleted from MSDE. When I use the
> Access UI to create a 'delete' query, I get an error message (cannot
> delete records from this table) when I try to run the query. I can
> delete the records individually.
> Can anyone help with this please?
when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.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
In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cadgbF6mrrenU1@.individual.net...
> hi Tom,
> Tom wrote:
> when defining the linked table in Access, did you identify the column(s)
> uniquelly idenitying each row (primary key)?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tom,
Tom wrote:
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is
> called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
> [WORKS_ORDER], which is a works order number. It joins to the MSDE
> table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
> name, [WORKS_ORDER]. The join is set to select all from
> 'WORKS_ORDER_TO_DELETE', and only those that match on the join in
> 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
> set the query as a 'delete', so the only field in the UI grid is
> labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
> is set to 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing
> to do with MSDE: it seems to be when you are identifying the records
> to delete through a join. I have also tried specifying all the
> columns individually in the records to be deleted, with the same
> result. I have also added the single field in the table
> 'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
> 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it
> best to give all the details rather than to try and guess at what you
> meant.
unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )
INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )
SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID
DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2
is a valid T-SQL syntax...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Tom,
I don't know if this will help....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):
'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)
'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK
'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null
Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.
OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).
If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)
gary
"Tom" wrote
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is called
> 'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
> a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
> where it joins to a field of the same name, [WORKS_ORDER]. The join is set
> to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
> the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
> query as a 'delete', so the only field in the UI grid is labelled
> 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
> 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing to do
> with MSDE: it seems to be when you are identifying the records to delete
> through a join. I have also tried specifying all the columns individually
> in the records to be deleted, with the same result. I have also added the
> single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
> it's delete option as 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it best
> to give all the details rather than to try and guess at what you meant.
> Regards
> Tom
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3cadgbF6mrrenU1@.individual.net...
>
|||For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default...b;en-us;207761
"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.
To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "
|||You are a star, Gary.
All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!
Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.
Regards
Tom
"Gary Walter" <garylwplease@.nospamwamego.net> wrote in message
news:eLGCHSoQFHA.1476@.TK2MSFTNGP09.phx.gbl...
> For "Access part,"
> ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
> http://support.microsoft.com/default...b;en-us;207761
> "Delete Queries
> *****************************************
> When a delete query contains more than one table,
> such as a query that deletes duplicate records from one of the tables,
> the UniqueRecords property must be set to Yes for all versions of
> Microsoft Access.
> ******************************************
> However, because the default value for UniqueRecords is No in Access 2000,
> you must set the value of this property manually when you create a new
> delete query in Access 2000.
> To do so, follow these steps:
> Open the delete query in Design view.
> If the property sheet is not already open, on the View menu, click
> Properties.
> Click an empty area in the upper half of the query window so that the
> property sheet displays "Query Properties" in the title bar.
> Set the UniqueRecords property to Yes.
> Save the query, close it, and then run the query. "
>
|||Hi Andrea
Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.
However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).
Many thanks for that.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cc9f6F6dtrtmU1@.individual.net...
> hi Tom,
> Tom wrote:
> unfortunately I'm not an Access guy and I do not feel very confortable
> with Access UI... perhaps you'll have better luck posting in Office NG
> hierarchy...
> apologise
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||:D
found another way...
modifying the SQL statement to
DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID
did the trick, even with no primary key
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.
Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.
Can anyone help with this please?
Regards
Tom
hi Tom,
Tom wrote:
> Hi
> I am a newbie to MSDE and am having a problem deleting records from
> an MSDE backend using an Access query front end.
> Our main data is stored on a UNIX server (informix type database) and
> we link to it using ODBC. We load records to be updated into an
> Access database and then use the Access table as a reference for
> updating the MSDE database. This means I have a reference table in
> Access that is joined to the MSDE table. The records that exist in
> the Access table are the ones to be deleted from MSDE. When I use the
> Access UI to create a 'delete' query, I get an error message (cannot
> delete records from this table) when I try to run the query. I can
> delete the records individually.
> Can anyone help with this please?
when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.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
In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cadgbF6mrrenU1@.individual.net...
> hi Tom,
> Tom wrote:
> when defining the linked table in Access, did you identify the column(s)
> uniquelly idenitying each row (primary key)?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tom,
Tom wrote:
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is
> called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
> [WORKS_ORDER], which is a works order number. It joins to the MSDE
> table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
> name, [WORKS_ORDER]. The join is set to select all from
> 'WORKS_ORDER_TO_DELETE', and only those that match on the join in
> 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
> set the query as a 'delete', so the only field in the UI grid is
> labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
> is set to 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing
> to do with MSDE: it seems to be when you are identifying the records
> to delete through a join. I have also tried specifying all the
> columns individually in the records to be deleted, with the same
> result. I have also added the single field in the table
> 'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
> 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it
> best to give all the details rather than to try and guess at what you
> meant.
unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )
INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )
SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID
DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2
is a valid T-SQL syntax...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Tom,
I don't know if this will help....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):
'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)
'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK
'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null
Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.
OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).
If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)
gary
"Tom" wrote
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is called
> 'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
> a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
> where it joins to a field of the same name, [WORKS_ORDER]. The join is set
> to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
> the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
> query as a 'delete', so the only field in the UI grid is labelled
> 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
> 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing to do
> with MSDE: it seems to be when you are identifying the records to delete
> through a join. I have also tried specifying all the columns individually
> in the records to be deleted, with the same result. I have also added the
> single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
> it's delete option as 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it best
> to give all the details rather than to try and guess at what you meant.
> Regards
> Tom
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3cadgbF6mrrenU1@.individual.net...
>
|||For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default...b;en-us;207761
"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.
To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "
|||You are a star, Gary.
All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!
Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.
Regards
Tom
"Gary Walter" <garylwplease@.nospamwamego.net> wrote in message
news:eLGCHSoQFHA.1476@.TK2MSFTNGP09.phx.gbl...
> For "Access part,"
> ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
> http://support.microsoft.com/default...b;en-us;207761
> "Delete Queries
> *****************************************
> When a delete query contains more than one table,
> such as a query that deletes duplicate records from one of the tables,
> the UniqueRecords property must be set to Yes for all versions of
> Microsoft Access.
> ******************************************
> However, because the default value for UniqueRecords is No in Access 2000,
> you must set the value of this property manually when you create a new
> delete query in Access 2000.
> To do so, follow these steps:
> Open the delete query in Design view.
> If the property sheet is not already open, on the View menu, click
> Properties.
> Click an empty area in the upper half of the query window so that the
> property sheet displays "Query Properties" in the title bar.
> Set the UniqueRecords property to Yes.
> Save the query, close it, and then run the query. "
>
|||Hi Andrea
Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.
However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).
Many thanks for that.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cc9f6F6dtrtmU1@.individual.net...
> hi Tom,
> Tom wrote:
> unfortunately I'm not an Access guy and I do not feel very confortable
> with Access UI... perhaps you'll have better luck posting in Office NG
> hierarchy...
> apologise
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||:D
found another way...
modifying the SQL statement to
DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID
did the trick, even with no primary key
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Access Database in other server
Hi.
I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?
kojoh:
I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?
as long as you SQL Server that has the stored procedured has the link server configured, it would work.
|||Thank's for replay , I will give you an example to explane what I want.
CREATE PROCEDURE [dbo].[MySP]ASinsert into memberselect *from member1
the table member in the current server and the table member1 in other server.
Regards.|||
As long as you have configured the link server to the member1 table then it would work as you need linked servers to run distributed queries.
Here are some articles on linked servers and distributed queries
http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms188279.aspx
http://www.databasejournal.com/features/mssql/article.php/3085211
|||Thank's alotjimmy
I try to implement the setpes but I can't get it .
CREATE PROCEDURE [dbo].[link]ASexecsp_addlinkedserver @.server ='Srv' , @.srvproduct ='' , @.provider='SQLOLEDB' ,@.datasrc ='Srv'select invcompfrom [Srv].[DB].glcomGO
when I run the script it give me
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Procedure link, Line 4
Invalid object name 'Srv.DB.glcom'.
I use sql server 2000.
Sunday, March 25, 2012
Access crashes when updating a stored procedure
Hello,
I am having a problem when using access xp as a frontend for sql server
2000.
I have been trying to update a number of stored procedures (Just simple
adding fields etc) which results in access crashing with event ID 1000 and
1001.
Does anyone have any ideas as to what could be the problem?
Thanks in advance..On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>Hello,
>I am having a problem when using access xp as a frontend for sql server
>2000.
>I have been trying to update a number of stored procedures (Just simple
>adding fields etc) which results in access crashing with event ID 1000 and
>1001.
>Does anyone have any ideas as to what could be the problem?
>Thanks in advance..
Access could have corrupted metadata stored in the extended attributes of the
stored procedure. Try copying the SQL from the stored procedure, deleting the
procedure, and creating it again. Paste the SQL back in to recover the old
procedure definition (whic will not include the old extended attributes).|||Thanks for the advice. I have done this in the past when things go wrong,
unfortunately this time Access crashes again when I try to save the new
stored procedure.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).|||Thanks for your help, I have tried this in the past when things have gone
astray. However on trying this Access crashes when I try to save the new
stored procedure.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).sql
Access Crashes Filtering a Linked SQL Table on a date field
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?
The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.
There are many many other alternatives that would work better and be much much faster.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?
The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.
There are many many other alternatives that would work better and be much much faster.
Access ComboBox Wont Populate
I'm using SQLServer2000/Access 2000 .adp. The .adp has a form with 2 combo boxes. The 1st combo box is bound to a stored procedure and loads fine when the form loads. I then set the 2nd combo box's RowSource passing the value of the first into the stored proc:
Me.secondComboBox.RowSource = "EXEC dbo.proc_My2ndProc '" & Me.cboFirstComboBox & "'"
Me.cbosecondComboBox.reQuery
Why won't it work? I've set a form's RecordSource using this methof, and it works great?
Thanks,
CarlDummy me, I didn't have the RowSourceType set to Table/View/StoredProc, all's well. I figured it out by creating a new form, and it was working, so I went to see what was different.
Me.secondComboBox.RowSource = "EXEC dbo.proc_My2ndProc '" & Me.cboFirstComboBox & "'"
Me.cbosecondComboBox.reQuery
Why won't it work? I've set a form's RecordSource using this methof, and it works great?
Thanks,
CarlDummy me, I didn't have the RowSourceType set to Table/View/StoredProc, all's well. I figured it out by creating a new form, and it was working, so I went to see what was different.
Access Combo Box Linked to Stored Proc
Does anyone for code code to link a stored procedures to a combo box in Access?A list of available stored procedures to execute or something else?
Thursday, March 22, 2012
Access another SQL Server inside of a stored procedure
Is it possible to access another SQL server when inside a stored
procedure? I would like to select some data in one database and then
insert that data into another database on a different SQL server. Is
that possible? Examples?
Thanks!
-JohnYes, it is possible. See "sp_addlinkedserver" in BOL.
AMB
"John Baima" wrote:
> Is it possible to access another SQL server when inside a stored
> procedure? I would like to select some data in one database and then
> insert that data into another database on a different SQL server. Is
> that possible? Examples?
> Thanks!
> -John
>
procedure? I would like to select some data in one database and then
insert that data into another database on a different SQL server. Is
that possible? Examples?
Thanks!
-JohnYes, it is possible. See "sp_addlinkedserver" in BOL.
AMB
"John Baima" wrote:
> Is it possible to access another SQL server when inside a stored
> procedure? I would like to select some data in one database and then
> insert that data into another database on a different SQL server. Is
> that possible? Examples?
> Thanks!
> -John
>
Access another database from sp
I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.
If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.
Option 1:
Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.
Option 2:
Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.
|||As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).
Refer to Books Online, Topic: "EXECUTE AS'
|||
Arnie,
I am not sure EXECUTE AS will help here.
As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.
I created 2 users,
user1 only have access on db1
user2 only have access on db2
Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.
Am i missing something here?
|||Mani,
As in the Example 
And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.
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
>
>.
>
Sunday, March 11, 2012
abt Stored Procedure ...
hello guys ..
hi,
You can use any sql statement inside your stored procedure...
hi
you can do this as follows
today i came with a Common question...
i am developing a website in that, I am using Stored procedure for all queries ( select, insert, update, delete )
is it good to run all queries in stored procedure ... or we should run only the SELECT queries by SP ... i need ur valuable suggestion ...
cheers ...i am waiting for some experienced person's answer ... is anybody here ??|||
Quote:
Originally Posted by rameshcse2005
i am waiting for some experienced person's answer ... is anybody here ??
hi,
You can use any sql statement inside your stored procedure...
According to me if you use stored procedures in your applications it will be good, if you need any database change just manipulate your sp, no need of rebuild or recompile your application.. all you need is organizing your application and stored procedures. better the design you have better the result you get|||You can use all the DML statments inside a procedure.|||Thank you very much for ur valuable suggestion ... :-)
and i have another doubt ?
how to pass multiple value in same parameter in SQL stored procedure ??
for example i have to write stored procedure for this query
DELETE FROM tableName where ID in (id1,id2,id3) ?|||You need to pass a refcursor to the procedure to dynamically pass any number of parameters for the IN operator..|||Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)|||
Quote:
Originally Posted by rameshcse2005
Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)
hi
you can do this as follows
send the id as a string seperated by ,(comma)
in your stored procedure use the following query
DELETE FROM tableName where ID in
( select id from tableName
where (','+isnull(@.parameter,convert(varchar(10),Id) )+',') like
('%,'+convert(varchar(20),Id)+ ',%')
)
In the above query if you pass null value to the @.Parameter then all the records will be deleted, if you send the id values seperated by coma then specific records only will be deleted
Saturday, February 25, 2012
About Stored Procedure
hi there,
I am using SQL after a long time and want to use stored procedure ofor the following purpose:
i have to db of all the names of agents all over US.i am using a formulata to calculate zip to zip distance so that when the user gets a order and hits"find agents" ..it calculates the distances by using latitude and longitude calculation and displays all the agents who are around 50 mile in radius with tthe zip code specified in the order.
I wanted to know if I could store this formula in a stored procedure so that all teh calculations are done by SQL and stored in a temp table..beucase the same thing i did in Access(obviously access doesnt have Stored proicedure and i wrote a program called calculate.asp to calculate diostances) its running slow and thats the only reason i am using SQL.
i dont know where to start from...any help/links/ideas on how to do it ,whether its possible to do it thru stored procedures...if so then how to write stored procedures?
hi,
yes, you can put that "solving" code in a stored procedure...
for instance, assuming you have "areas" indicated in an x,y cartesian table of 3x3 cells as the following trivial sample, you can get the related agent based on the join of the area and agents tables...
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.Area (
Id int NOT NULL IDENTITY PRIMARY KEY,
Name varchar(10) NOT NULL,
X int NOT NULL,
Y int NOT NULL
);
CREATE TABLE dbo.Agent (
Id int NOT NULL IDENTITY PRIMARY KEY,
Name varchar(10) NOT NULL,
IdArea int NOT NULL
);
INSERT INTO dbo.Area VALUES ( 'a', 1 , 1 );
INSERT INTO dbo.Area VALUES ( 'b', 2 , 1 );
INSERT INTO dbo.Area VALUES ( 'c', 3 , 1 );
INSERT INTO dbo.Area VALUES ( 'd', 1 , 2 );
INSERT INTO dbo.Area VALUES ( 'e', 2 , 2 );
INSERT INTO dbo.Area VALUES ( 'f', 3 , 2 );
INSERT INTO dbo.Area VALUES ( 'g', 1 , 3 );
INSERT INTO dbo.Area VALUES ( 'h', 2 , 3 );
INSERT INTO dbo.Area VALUES ( 'i', 3 , 3 );
INSERT INTO dbo.Agent VALUES ( 'agent a', 1 );
INSERT INTO dbo.Agent VALUES ( 'agent b', 2 );
INSERT INTO dbo.Agent VALUES ( 'agent c', 3 );
INSERT INTO dbo.Agent VALUES ( 'agent d', 4 );
INSERT INTO dbo.Agent VALUES ( 'agent e', 5 );
INSERT INTO dbo.Agent VALUES ( 'agent f', 6 );
INSERT INTO dbo.Agent VALUES ( 'agent g', 7 );
INSERT INTO dbo.Agent VALUES ( 'agent h', 8 );
INSERT INTO dbo.Agent VALUES ( 'agent i', 9 );
DECLARE @.x int, @.y int;
SELECT @.x = 3, @.y = 1
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id
WHERE a.X = @.x AND a.Y = @.y
PRINT 'all agents and related areas';
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id;
DROP TABLE dbo.Agent, dbo.Area;
--<
AreaName AgentName
- -
c agent c
all agents and related areas
AreaName AgentName
- -
a agent a
b agent b
c agent c
d agent d
e agent e
f agent f
g agent g
h agent h
i agent i
the procedure related code could be as easy as the following related trivial code
CREATE PROCEDURE dbo.GetAgentByArea (@.x int,
@.y int
)
AS BEGIN
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id
WHERE a.X = @.x AND a.Y = @.y;
END;
GO
EXEC dbo.GetAgentByArea 1,1;
EXEC dbo.GetAgentByArea 3,2;
GO
DROP PROCEDURE dbo.GetAgentByArea
or as complicated as necessary..
regards
About stored proc compilation...
Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
JayNeither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay|||I sure appreciate the quick response. This is very helpful. A search in Book
s
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by t
he
> SQL engine. I do not know of any public documentation on the internal pla
n
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
there documentation on this somewhere?
Thanks,
JayNeither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay|||I sure appreciate the quick response. This is very helpful. A search in Book
s
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by t
he
> SQL engine. I do not know of any public documentation on the internal pla
n
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
About stored proc compilation...
Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
Jay
Neither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay
|||I sure appreciate the quick response. This is very helpful. A search in Books
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by the
> SQL engine. I do not know of any public documentation on the internal plan
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
there documentation on this somewhere?
Thanks,
Jay
Neither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay
|||I sure appreciate the quick response. This is very helpful. A search in Books
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by the
> SQL engine. I do not know of any public documentation on the internal plan
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
Friday, February 24, 2012
About SQL SERVER 2005(Stored Procedure) & Dot Net 1.1
I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:\Documents and Settings\mmm\My Documents\SQL Server Management Studio Express\Projects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command
"exec InsertValueIntoTable ;" BUT when i want to execute with clicking the execute button in the then gives an error "Could not find stored procedure 'InsertValueIntoTable'."
Malay Roy wrote:
I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:\Documents and Settings\mmm\My Documents\SQL Server Management Studio Express\Projects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command
What do you mean by this? The *.sql file is just the script, you must put all the contents in the sql file to SQL_SERVE before you can execute the SP.
|||Hi,
Please post your question in the following forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1
Thank you,
Bhanu.
About Sql agent
Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put databas
e
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue coul
d
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper a
ccount for SQL Server
service. No, they don't need to use the same account. But watch out for the
mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, t
he
> database backup files were stored on local disk. We are think to put datab
ase
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue co
uld
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put databas
e
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue coul
d
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper a
ccount for SQL Server
service. No, they don't need to use the same account. But watch out for the
mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, t
he
> database backup files were stored on local disk. We are think to put datab
ase
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue co
uld
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
About Sql agent
Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
Lionel
The one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
Lionel
The one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
About Sql agent
Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>
About sp_detach_db and sp_attach_db
Hello there,
Our database data and log files were stored on UNC directory on different
server, we are thinking to use sp_detach_db and sp_attach_db to move data and
log files from UNC to local disk. Becasuse we are not familiar about
application, any minor issue could stop applicaiton. Is there any issue could
happen on sp_detach_db and sp_attach_db?
Thanks very much
Lionel
I haven't had any problems using them properly (that is, attaching databases
with sp_attach_db that were detached using sp_detach_db), but that doesn't
mean there is no possibility of issues. As with anything else, back up
first just to be on the safe side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> Hello there,
> Our database data and log files were stored on UNC directory on different
> server, we are thinking to use sp_detach_db and sp_attach_db to move data
and
> log files from UNC to local disk. Becasuse we are not familiar about
> application, any minor issue could stop applicaiton. Is there any issue
could
> happen on sp_detach_db and sp_attach_db?
> Thanks very much
> Lionel
>
|||Thanks for your reply, One of the further questions is that I noticed one of
our database owner is "unknow", I wonder why it would be happed.
when we use sp_attach_db to change the database owner from "unknow" to "sa",
any issue could be?
Thanks again
Lionel
"Adam Machanic" wrote:
> I haven't had any problems using them properly (that is, attaching databases
> with sp_attach_db that were detached using sp_detach_db), but that doesn't
> mean there is no possibility of issues. As with anything else, back up
> first just to be on the safe side.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "lionel" <lionel@.discussions.microsoft.com> wrote in message
> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> and
> could
>
>
|||You can restore user and owner settings on the target server using
sp_change_users_login and sp_changedbowner.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
"sa",[vbcol=seagreen]
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
databases[vbcol=seagreen]
doesn't[vbcol=seagreen]
different[vbcol=seagreen]
data[vbcol=seagreen]
issue[vbcol=seagreen]
|||Hi,
Before detaching, I recommend you to do a Full database backup of your
database. So even if Attach wont work you
could use the backup to recover the database to local hard disk using
RESTORE DATABASE command.
After that as Adam suggested you could use sp_changedbowner to change the
database owner
Thanks
Hari
SQL Server MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, One of the further questions is that I noticed one
> of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
> "sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
Our database data and log files were stored on UNC directory on different
server, we are thinking to use sp_detach_db and sp_attach_db to move data and
log files from UNC to local disk. Becasuse we are not familiar about
application, any minor issue could stop applicaiton. Is there any issue could
happen on sp_detach_db and sp_attach_db?
Thanks very much
Lionel
I haven't had any problems using them properly (that is, attaching databases
with sp_attach_db that were detached using sp_detach_db), but that doesn't
mean there is no possibility of issues. As with anything else, back up
first just to be on the safe side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> Hello there,
> Our database data and log files were stored on UNC directory on different
> server, we are thinking to use sp_detach_db and sp_attach_db to move data
and
> log files from UNC to local disk. Becasuse we are not familiar about
> application, any minor issue could stop applicaiton. Is there any issue
could
> happen on sp_detach_db and sp_attach_db?
> Thanks very much
> Lionel
>
|||Thanks for your reply, One of the further questions is that I noticed one of
our database owner is "unknow", I wonder why it would be happed.
when we use sp_attach_db to change the database owner from "unknow" to "sa",
any issue could be?
Thanks again
Lionel
"Adam Machanic" wrote:
> I haven't had any problems using them properly (that is, attaching databases
> with sp_attach_db that were detached using sp_detach_db), but that doesn't
> mean there is no possibility of issues. As with anything else, back up
> first just to be on the safe side.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "lionel" <lionel@.discussions.microsoft.com> wrote in message
> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> and
> could
>
>
|||You can restore user and owner settings on the target server using
sp_change_users_login and sp_changedbowner.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
"sa",[vbcol=seagreen]
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
databases[vbcol=seagreen]
doesn't[vbcol=seagreen]
different[vbcol=seagreen]
data[vbcol=seagreen]
issue[vbcol=seagreen]
|||Hi,
Before detaching, I recommend you to do a Full database backup of your
database. So even if Attach wont work you
could use the backup to recover the database to local hard disk using
RESTORE DATABASE command.
After that as Adam suggested you could use sp_changedbowner to change the
database owner
Thanks
Hari
SQL Server MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, One of the further questions is that I noticed one
> of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
> "sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
About sp_detach_db and sp_attach_db
Hello there,
Our database data and log files were stored on UNC directory on different
server, we are thinking to use sp_detach_db and sp_attach_db to move data and
log files from UNC to local disk. Becasuse we are not familiar about
application, any minor issue could stop applicaiton. Is there any issue could
happen on sp_detach_db and sp_attach_db?
Thanks very much
LionelI haven't had any problems using them properly (that is, attaching databases
with sp_attach_db that were detached using sp_detach_db), but that doesn't
mean there is no possibility of issues. As with anything else, back up
first just to be on the safe side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> Hello there,
> Our database data and log files were stored on UNC directory on different
> server, we are thinking to use sp_detach_db and sp_attach_db to move data
and
> log files from UNC to local disk. Becasuse we are not familiar about
> application, any minor issue could stop applicaiton. Is there any issue
could
> happen on sp_detach_db and sp_attach_db?
> Thanks very much
> Lionel
>|||Thanks for your reply, One of the further questions is that I noticed one of
our database owner is "unknow", I wonder why it would be happed.
when we use sp_attach_db to change the database owner from "unknow" to "sa",
any issue could be?
Thanks again
Lionel
"Adam Machanic" wrote:
> I haven't had any problems using them properly (that is, attaching databases
> with sp_attach_db that were detached using sp_detach_db), but that doesn't
> mean there is no possibility of issues. As with anything else, back up
> first just to be on the safe side.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "lionel" <lionel@.discussions.microsoft.com> wrote in message
> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> > Hello there,
> >
> > Our database data and log files were stored on UNC directory on different
> > server, we are thinking to use sp_detach_db and sp_attach_db to move data
> and
> > log files from UNC to local disk. Becasuse we are not familiar about
> > application, any minor issue could stop applicaiton. Is there any issue
> could
> > happen on sp_detach_db and sp_attach_db?
> >
> > Thanks very much
> >
> > Lionel
> >
> >
>
>|||You can restore user and owner settings on the target server using
sp_change_users_login and sp_changedbowner.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
"sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
> > I haven't had any problems using them properly (that is, attaching
databases
> > with sp_attach_db that were detached using sp_detach_db), but that
doesn't
> > mean there is no possibility of issues. As with anything else, back up
> > first just to be on the safe side.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "lionel" <lionel@.discussions.microsoft.com> wrote in message
> > news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> > > Hello there,
> > >
> > > Our database data and log files were stored on UNC directory on
different
> > > server, we are thinking to use sp_detach_db and sp_attach_db to move
data
> > and
> > > log files from UNC to local disk. Becasuse we are not familiar about
> > > application, any minor issue could stop applicaiton. Is there any
issue
> > could
> > > happen on sp_detach_db and sp_attach_db?
> > >
> > > Thanks very much
> > >
> > > Lionel
> > >
> > >
> >
> >
> >|||Hi,
Before detaching, I recommend you to do a Full database backup of your
database. So even if Attach wont work you
could use the backup to recover the database to local hard disk using
RESTORE DATABASE command.
After that as Adam suggested you could use sp_changedbowner to change the
database owner
Thanks
Hari
SQL Server MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
> of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
> "sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
>> I haven't had any problems using them properly (that is, attaching
>> databases
>> with sp_attach_db that were detached using sp_detach_db), but that
>> doesn't
>> mean there is no possibility of issues. As with anything else, back up
>> first just to be on the safe side.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "lionel" <lionel@.discussions.microsoft.com> wrote in message
>> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
>> > Hello there,
>> >
>> > Our database data and log files were stored on UNC directory on
>> > different
>> > server, we are thinking to use sp_detach_db and sp_attach_db to move
>> > data
>> and
>> > log files from UNC to local disk. Becasuse we are not familiar about
>> > application, any minor issue could stop applicaiton. Is there any issue
>> could
>> > happen on sp_detach_db and sp_attach_db?
>> >
>> > Thanks very much
>> >
>> > Lionel
>> >
>> >
>>
Our database data and log files were stored on UNC directory on different
server, we are thinking to use sp_detach_db and sp_attach_db to move data and
log files from UNC to local disk. Becasuse we are not familiar about
application, any minor issue could stop applicaiton. Is there any issue could
happen on sp_detach_db and sp_attach_db?
Thanks very much
LionelI haven't had any problems using them properly (that is, attaching databases
with sp_attach_db that were detached using sp_detach_db), but that doesn't
mean there is no possibility of issues. As with anything else, back up
first just to be on the safe side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> Hello there,
> Our database data and log files were stored on UNC directory on different
> server, we are thinking to use sp_detach_db and sp_attach_db to move data
and
> log files from UNC to local disk. Becasuse we are not familiar about
> application, any minor issue could stop applicaiton. Is there any issue
could
> happen on sp_detach_db and sp_attach_db?
> Thanks very much
> Lionel
>|||Thanks for your reply, One of the further questions is that I noticed one of
our database owner is "unknow", I wonder why it would be happed.
when we use sp_attach_db to change the database owner from "unknow" to "sa",
any issue could be?
Thanks again
Lionel
"Adam Machanic" wrote:
> I haven't had any problems using them properly (that is, attaching databases
> with sp_attach_db that were detached using sp_detach_db), but that doesn't
> mean there is no possibility of issues. As with anything else, back up
> first just to be on the safe side.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "lionel" <lionel@.discussions.microsoft.com> wrote in message
> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> > Hello there,
> >
> > Our database data and log files were stored on UNC directory on different
> > server, we are thinking to use sp_detach_db and sp_attach_db to move data
> and
> > log files from UNC to local disk. Becasuse we are not familiar about
> > application, any minor issue could stop applicaiton. Is there any issue
> could
> > happen on sp_detach_db and sp_attach_db?
> >
> > Thanks very much
> >
> > Lionel
> >
> >
>
>|||You can restore user and owner settings on the target server using
sp_change_users_login and sp_changedbowner.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
"sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
> > I haven't had any problems using them properly (that is, attaching
databases
> > with sp_attach_db that were detached using sp_detach_db), but that
doesn't
> > mean there is no possibility of issues. As with anything else, back up
> > first just to be on the safe side.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "lionel" <lionel@.discussions.microsoft.com> wrote in message
> > news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> > > Hello there,
> > >
> > > Our database data and log files were stored on UNC directory on
different
> > > server, we are thinking to use sp_detach_db and sp_attach_db to move
data
> > and
> > > log files from UNC to local disk. Becasuse we are not familiar about
> > > application, any minor issue could stop applicaiton. Is there any
issue
> > could
> > > happen on sp_detach_db and sp_attach_db?
> > >
> > > Thanks very much
> > >
> > > Lionel
> > >
> > >
> >
> >
> >|||Hi,
Before detaching, I recommend you to do a Full database backup of your
database. So even if Attach wont work you
could use the backup to recover the database to local hard disk using
RESTORE DATABASE command.
After that as Adam suggested you could use sp_changedbowner to change the
database owner
Thanks
Hari
SQL Server MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
> of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
> "sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
>> I haven't had any problems using them properly (that is, attaching
>> databases
>> with sp_attach_db that were detached using sp_detach_db), but that
>> doesn't
>> mean there is no possibility of issues. As with anything else, back up
>> first just to be on the safe side.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "lionel" <lionel@.discussions.microsoft.com> wrote in message
>> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
>> > Hello there,
>> >
>> > Our database data and log files were stored on UNC directory on
>> > different
>> > server, we are thinking to use sp_detach_db and sp_attach_db to move
>> > data
>> and
>> > log files from UNC to local disk. Becasuse we are not familiar about
>> > application, any minor issue could stop applicaiton. Is there any issue
>> could
>> > happen on sp_detach_db and sp_attach_db?
>> >
>> > Thanks very much
>> >
>> > Lionel
>> >
>> >
>>
Subscribe to:
Posts (Atom)