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 hii. Show all posts
Showing posts with label hii. Show all posts
Thursday, March 29, 2012
Access Denied - SQLSERVER 2005 Integration Services
Hi:
I am getting access denied error "cannot connect to server" while trying to connect Integration Services. I am trying to connect from my laptop to the SQLSERVER database server.
I followed the document "Connecting to a Remove Intergration Services Server" , doc id: aa337083.
But I can connect to IS if I log remotely to the server.
Any thoughts on this ?
Thanks
-Rajesh
I am getting following errors.
===================================
Cannot connect to cvsslmdd1-vm.
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
Connect to SSIS Service on machine "cvsslmdd1-vm" failed:
Access is denied.
.
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
===================================
Connect to SSIS Service on machine "cvsslmdd1-vm" failed:
Access is denied.
.
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
Hi,
I have the same problem and I am not able to figure out why. Any help is appreciated.
Thanks.
Venu
|||Have you followed Books Online procedure:http://msdn2.microsoft.com/en-us/library/aa337083.aspx|||Yes I did. I am a local admin on that box and it is fine for me. But it doesn't work for my colleague even though I followed the procedure.|||See this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22311 and I think BOL actions are incomplete.|||
SQLusert wrote:
See this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22311 and I think BOL actions are incomplete.
Or as blogged here:
http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/sql
Access Denied - SQLSERVER 2005 Integration Services
Hi:
I am getting access denied error "cannot connect to server" while trying to connect Integration Services. I am trying to connect from my laptop to the SQLSERVER database server.
I followed the document "Connecting to a Remove Intergration Services Server" , doc id: aa337083.
But I can connect to IS if I log remotely to the server.
Any thoughts on this ?
Thanks
-Rajesh
I am getting following errors.
===================================
Cannot connect to cvsslmdd1-vm.
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
Connect to SSIS Service on machine "cvsslmdd1-vm" failed:
Access is denied.
.
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
===================================
Connect to SSIS Service on machine "cvsslmdd1-vm" failed:
Access is denied.
.
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
Hi,
I have the same problem and I am not able to figure out why. Any help is appreciated.
Thanks.
Venu
|||Have you followed Books Online procedure:http://msdn2.microsoft.com/en-us/library/aa337083.aspx|||Yes I did. I am a local admin on that box and it is fine for me. But it doesn't work for my colleague even though I followed the procedure.|||See this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22311 and I think BOL actions are incomplete.|||
SQLusert wrote:
See this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22311 and I think BOL actions are incomplete.
Or as blogged here:
http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/
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 databases using ADO.Net
Hi
I am using Visual Studio.Net 2003 and SQL 2000
Web applications run using the ASPNET user account.
I have to set up this account and grant it permissions before my Web application will have access to a SQL database.
How do I grant permissions ?.
Sunday, March 25, 2012
Access Data From Multiple SqlSever Instances
Hi
I am having two SQL Server instances installed at two different systems and want to fetch data from both of them in a sigle query with joins. Somebody suggested that I need to link up the sql server instances at the admin level. If you have an idea about the process of linkng multiple sql server instances kindly convey it to me.
I am having two SQL Server instances installed at two different systems and want to fetch data from both of them in a sigle query with joins. Somebody suggested that I need to link up the sql server instances at the admin level. If you have an idea about the process of linkng multiple sql server instances kindly convey it to me.
For Details of the discussion kindly follow the undergiven link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138563&SiteID=1
Thanks
How to add linked servers is described in the Books Online topic for sp_addlinkedserver and the related topics. See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fed3adb0-4c15-4a1a-8acd-1b184aff558f.htm.
Thursday, March 22, 2012
Access add record button grayed
Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't add
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
Hugo
The really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:
>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't add
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't add
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
Hugo
The really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:
>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't add
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo
Access add record button grayed
Hi:
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't ad
d
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
HugoThe really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:
>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't a
dd
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo
I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
Back-End. I have the problem that dependent tables in the E-R model can't ad
d
records, the add new record button is grayed, however the independent table
and catalog table have no problems, all tables have PK defined. Any ideas?
Thanks in advance
Regards
HugoThe really depends on how you have done the data sources for
your forms. It's not really a SQL Server issue but more of a
MS Access issue. You will probably want to post this in one
of the MS Access newsgroups instead.
-Sue
On Wed, 25 Jul 2007 14:46:00 -0700, ijazbof
<ijazbof@.discussions.microsoft.com> wrote:
>Hi:
> I have an MS ACCESS App that acts as a Front-End and SQL Server 2000 as
>Back-End. I have the problem that dependent tables in the E-R model can't a
dd
>records, the add new record button is grayed, however the independent table
>and catalog table have no problems, all tables have PK defined. Any ideas?
>Thanks in advance
>Regards
>Hugo
Friday, February 24, 2012
About SQL Profiler and query
Hi
i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction" but
the TextData is always empty and my application name is JSQL.
This transaction duration is : 10516
The question are :
1- It's a normal that the "textdata" is always empty ?
2- The application name "JSQL" maybe JBOSS on my Apache server?
3- The duration of this transaction is too much ?
Thanks.
What are you expecting to see in the TextData column for the SQLTransaction
event? It's documented behaviour is to display "The savepoint or rollback
name, if provided.", according to SQL Server Books Online:
http://msdn.microsoft.com/library/en..._perf_79tf.asp
Given that it's not common to name savepoints or rollbacks, it's probably a
little unusual to expect to see much in SQLTransaction:TextData. Perhaps you
should be profiling a different event - if you post back with what you're
hoping to see, we can perhaps help by recommending a different event to
profile?
Regards,
Greg Linwood
SQL Server MVP
<io.com> wrote in message news:eu8ETkNnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Hi
> i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction"
> but
> the TextData is always empty and my application name is JSQL.
> This transaction duration is : 10516
> The question are :
> 1- It's a normal that the "textdata" is always empty ?
> 2- The application name "JSQL" maybe JBOSS on my Apache server?
> 3- The duration of this transaction is too much ?
>
> Thanks.
>
i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction" but
the TextData is always empty and my application name is JSQL.
This transaction duration is : 10516
The question are :
1- It's a normal that the "textdata" is always empty ?
2- The application name "JSQL" maybe JBOSS on my Apache server?
3- The duration of this transaction is too much ?
Thanks.
What are you expecting to see in the TextData column for the SQLTransaction
event? It's documented behaviour is to display "The savepoint or rollback
name, if provided.", according to SQL Server Books Online:
http://msdn.microsoft.com/library/en..._perf_79tf.asp
Given that it's not common to name savepoints or rollbacks, it's probably a
little unusual to expect to see much in SQLTransaction:TextData. Perhaps you
should be profiling a different event - if you post back with what you're
hoping to see, we can perhaps help by recommending a different event to
profile?
Regards,
Greg Linwood
SQL Server MVP
<io.com> wrote in message news:eu8ETkNnEHA.2764@.TK2MSFTNGP10.phx.gbl...
> Hi
> i have enable SQL Profiler for my SQL2000 DB and view a "SQLTransaction"
> but
> the TextData is always empty and my application name is JSQL.
> This transaction duration is : 10516
> The question are :
> 1- It's a normal that the "textdata" is always empty ?
> 2- The application name "JSQL" maybe JBOSS on my Apache server?
> 3- The duration of this transaction is too much ?
>
> Thanks.
>
Sunday, February 19, 2012
about security of SQL Server
hi;
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...
Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...
|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:
> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...
Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...
|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:
> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>
about security of SQL Server
hi;
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:
> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:
> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>
Thursday, February 9, 2012
About automatic process at midnight
Hi
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>
Subscribe to:
Posts (Atom)