Duplicate records - no error message?

Hi,
I am in the process of moving my back end to the SQL server. I have a test
version in the SQLserver and the front end is in Access 2000 with linked
tables.
When the user enters a duplicate key (primary key) and the user tries to
save the record in Access (using a save button), an error message is
displayed by Jet.
The same kind of error message does not appear when the back end is in SQL
server. The duplicate record does not get stored as well.
Why is this happening?> The same kind of error message does not appear when the back end is in SQL
> server. The duplicate record does not get stored as well.
> Why is this happening?
Well, what does the statement look like? Did you use profiler to see what
is going into and coming out of SQL Server?|||Hi,
Thanks for responsing.
No...I am a newbie to SQL server and don't know how to do it.
Access (jet) gives an error message when I try to save a duplicate record. -
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship".
This message appears when the f/e and the b/e are Access.
When the b/e is SQL for the same table and Access is the front end..no error
message appears.
Do you suggest I learn how to use a profiler and figure it out?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, what does the statement look like? Did you use profiler to see what
> is going into and coming out of SQL Server?
>
>|||Hi
Since you have not provided your INSERT statement ,see Itzik Ben-Gan's
exanple to deal with dulicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Priya Henry" <PriyaHenry@.discussions.microsoft.com> wrote in message
news:3A756C75-F872-4168-A497-905031937F0B@.microsoft.com...
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate
> record. -
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no
> error
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Does your table in SQL Server have a unique index on the relevant column?
"Priya Henry" wrote:
> Hi,
> Thanks for responsing.
> No...I am a newbie to SQL server and don't know how to do it.
> Access (jet) gives an error message when I try to save a duplicate record.
-
> "The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key or relationship".
> This message appears when the f/e and the b/e are Access.
> When the b/e is SQL for the same table and Access is the front end..no err
or
> message appears.
> Do you suggest I learn how to use a profiler and figure it out?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Yes...My table does have an unique index. Is it possible that Jet is
stopping the message?
"NH" wrote:
> Does your table in SQL Server have a unique index on the relevant column?
> "Priya Henry" wrote:
>|||I havnt seen this before. Our system has a SQL Server backend and Access
front end and the error message do appear when trying to add in a duplicate
into a unique undexed column.
Have you checked the linked tables in Access, are they pointing to the
correct SQL Server database?
"Priya Henry" wrote:
> Yes...My table does have an unique index. Is it possible that Jet is
> stopping the message?
> "NH" wrote:
>sql

Duplicate Database

I need to make an exact duplicate of a database on the same instance of SQL
Server 2000; seems like it should be easy, but i'm stuck ...
Can anyone point me in the right direction on this ?
TIA
Liz
Liz wrote:
> I need to make an exact duplicate of a database on the same instance of SQL
> Server 2000; seems like it should be easy, but i'm stuck ...
> Can anyone point me in the right direction on this ?
> TIA
>
> Liz
>
Backup/restore, using the WITH MOVE option to create a new set of data
files.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In case you need more detailed information about Tracy's suggestion, see
this article:
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=221465
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
[vbcol=seagreen]
> Liz wrote:

> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL
docs; is this available on SQL 2000 ?
thanks ...
Liz
|||Yes, look up RESTORE in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OaT8oZMFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> In case you need more detailed information about Tracy's suggestion, see
> this article:
> Using WITH MOVE in a Restore
> http://www.support.microsoft.com/?id=221465
Thanks Arnie, Tracy ... got it; didn't realize it was a RESTORE option at
first ..
L
|||From Query Analyzer,execute below:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
Change the database name and file names based on the backup and database
name you have.
Thanks
Hari
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>
sql

Duplicate Database

I need to make an exact duplicate of a database on the same instance of SQL
Server 2000; seems like it should be easy, but i'm stuck ...
Can anyone point me in the right direction on this ?
TIA
LizLiz wrote:
> I need to make an exact duplicate of a database on the same instance of SQ
L
> Server 2000; seems like it should be easy, but i'm stuck ...
> Can anyone point me in the right direction on this ?
> TIA
>
> Liz
>
Backup/restore, using the WITH MOVE option to create a new set of data
files.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In case you need more detailed information about Tracy's suggestion, see
this article:
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=221465
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...

> Liz wrote:
[vbcol=seagreen]
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL
docs; is this available on SQL 2000 ?
thanks ...
Liz|||> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL docs; is this

> available on SQL 2000 ?
Yes. It is indeed documented in BOL, RESTORE DATBASE. In EM restore dialog,
you have it in the left
tab, where you can specify the desired physical filename for each file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Liz" <liz@.tiredofspam.com> wrote in message news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:456F4BDE.503
0604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQ
L docs; is this
> available on SQL 2000 ?
> thanks ...
> Liz
>[/vbcol]|||Yes, look up RESTORE in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OaT8oZMFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> In case you need more detailed information about Tracy's suggestion, see
> this article:
> Using WITH MOVE in a Restore
> http://www.support.microsoft.com/?id=221465
Thanks Arnie, Tracy ... got it; didn't realize it was a RESTORE option at
first ..
L|||From Query Analyzer,execute below:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
Change the database name and file names based on the backup and database
name you have.
Thanks
Hari
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>

Dumping sql server

BACKUP DATABASE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message news:eoAZqSDhHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way to dump all the sql server data into some sort of file that
> can be imported into a local sql server later? I have used sql server dumper
> but it is not very stable and crashes during sump.
> Thanks
> Regards
>
Hi John
"John" wrote:

> Remote, does not work for some reason possibly insufficient rights.
>
If you want to post the command you are using it may help? Also the full
error message would be useful. If you want the backup to be placed on a
different server you can use a UNC path. Make sure that you also have
directory permissions as well as server/database permissions sysadmin,
db_owner or db_backupoperator. See BACKUP in Books Online for more.
Databases can also be detached and attached, it is usually best to attach
the database to an instance at the same service pack/hotfix number, but SQL
2000 databases can be attached to SQL 2005 instances, although you may have
to change incompatibilities if you want to use the latest compatibility mode.
If you are changing instance you will need to script logins and possibly
resolved orphaned users see
http://support.microsoft.com/default.aspx/kb/314546 for more.
John

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>
>
|||Can't you backup the database to a file local on the SQL Server machine and then FTP the file to
your machine?
Other methods include script out all the objects and data and then re-create the objects locally and
then import that data. I doubt you will find something as such as rock solid as backup. I've listed
some tools to generate scripts and some with data at
http://www.karaszi.com/SQLServer/info_generate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%234nMS%23DhHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Remote, does not work for some reason possibly insufficient rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eQl9syDhHHA.392@.TK2MSFTNGP06.phx.gbl...
>

dumping backups to share in different domain

Hi,
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> Windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> Windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>

dumping backups to share in different domain

Hi,
I have a sql server running in domain abc.com. de sqlserver and the sqlagent
services are running on their own useraccounts SqlServerServ@.abc.com and
SqlAgentServ@.abc.com. This server is in our own server room which we use to
host servers on our own domain 123.com.
the following task needs to be done:
make backups of the databases in sqlserver.abc.com to the file share on
fileserver.123.com by creating jobs in sqlagent.
What can not be done:
Make a trust between abc.com and 123.com since both companies don't trust
each other.
extra info.
sqlserver.abc.com
sql server 2000 standard SP3 running on
windows 2000 Standard edition (patches unknown)
this box happens to be the DC for abc.com as well (it's a test enviorment)
fileserver.123.com
windows 2000 standard edition (patches unknown)
this box is not the DC for 123.com
a share is create \\fileserver\DBbackups
and a user called SQLbackup@.123.com which has full rights to this share
What is the best way to go arround:
1) try and make the backups work using SQLbackup@.123.com
2) or try and give SQLAgentServ@.abc.com access to the fileshare on
fileserver.123.com
Is any of these two option possible at all?
are there better solutions with this setup?(we cannot change the domain
setup and memberships, and we cannot at extra boxes)
Kind regards
Edward Dortland
please reply to newsgroup ONLYSuggestions:
Install FTP server on fileserver.123.com, then
1. Do local backup on abc.com
2. Transfer these backups to fileserver.123.com using -s:filename option.
OR try this one (not sure if it will work).
1. Create LOCAL account SQLAgentServ on both computers with same password.
From Log shipping:
"Local Network Account
You can use SQL Server to start under a locally-created network account. In
the situation where there is network access required by a SQL Server
process, which is the case if you have configured SQL Server to use log
shipping, you can use network pass-through security. With pass-through
security, all machines that will be accessed by SQL Server must have the
same network account with the same password and appropriate permissions,
configured locally. Additionally, when the SQL Server process requests
resources from the second computer, traditional network security is bypassed
if the same account (under which the requesting SQL Server service is
started) exists with the same password. As long the account on the second
computer is configured with enough permission to carry out the task that is
requested by calling SQL Server, the task will be successful. "
2. Try to do backup.
HTH
Igor Raytsin
"Edward Dortland" <edwardNOSPAMMMM@.solsol.nl> wrote in message
news:104jovuket4qk0f@.corp.supernews.com...
> Hi,
> I have a sql server running in domain abc.com. de sqlserver and the
sqlagent
> services are running on their own useraccounts SqlServerServ@.abc.com and
> SqlAgentServ@.abc.com. This server is in our own server room which we use
to
> host servers on our own domain 123.com.
> the following task needs to be done:
> make backups of the databases in sqlserver.abc.com to the file share on
> fileserver.123.com by creating jobs in sqlagent.
> What can not be done:
> Make a trust between abc.com and 123.com since both companies don't trust
> each other.
> extra info.
> sqlserver.abc.com
> sql server 2000 standard SP3 running on
> windows 2000 Standard edition (patches unknown)
> this box happens to be the DC for abc.com as well (it's a test enviorment)
> fileserver.123.com
> windows 2000 standard edition (patches unknown)
> this box is not the DC for 123.com
> a share is create \\fileserver\DBbackups
> and a user called SQLbackup@.123.com which has full rights to this share
> What is the best way to go arround:
> 1) try and make the backups work using SQLbackup@.123.com
> 2) or try and give SQLAgentServ@.abc.com access to the fileshare on
> fileserver.123.com
> Is any of these two option possible at all?
> are there better solutions with this setup?(we cannot change the domain
> setup and memberships, and we cannot at extra boxes)
> Kind regards
>
> Edward Dortland
> please reply to newsgroup ONLY
>
>sql

dtutil and xml configuration file

Hi,

Im loading the packages with a dutil-batch from the file-system into the sqlserver.

All the packages have xml-configuration-file.

for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET

When I try to run the packages in the sqlserver environment there is no configuration file found.

Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?

Thanks Gerd

Moving to a better forum|||Try using the DTSDeployment utility to deploy packages that reference configuration files. The deployment utility will allow you to specify where to put the config files and will update the packages accordingly.|||

Hi

I am also in the same boat. I dont want to use deployment utility as I want to create a batch file. adminstrator will use this batch file to deploy on prod server.

I have configuration file that needs to be deployed on server along with package. please tell me if it possible or please give me work around for this.

|||You can specify the location of the configuration file when you run the package by using the /ConfigFile switch of DTEXEC.

dtutil and xml configuration file

Hi,

Im loading the packages with a dutil-batch from the file-system into the sqlserver.

All the packages have xml-configuration-file.

for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET

When I try to run the packages in the sqlserver environment there is no configuration file found.

Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?

Thanks Gerd

Moving to a better forum|||Try using the DTSDeployment utility to deploy packages that reference configuration files. The deployment utility will allow you to specify where to put the config files and will update the packages accordingly.|||

Hi

I am also in the same boat. I dont want to use deployment utility as I want to create a batch file. adminstrator will use this batch file to deploy on prod server.

I have configuration file that needs to be deployed on server along with package. please tell me if it possible or please give me work around for this.

|||You can specify the location of the configuration file when you run the package by using the /ConfigFile switch of DTEXEC.

dtutil and xml configuration file

Hi,

Im loading the packages with a dutil-batch from the file-system into the sqlserver.

All the packages have xml-configuration-file.

for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET

When I try to run the packages in the sqlserver environment there is no configuration file found.

Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?

Thanks Gerd

Moving to a better forum|||Try using the DTSDeployment utility to deploy packages that reference configuration files. The deployment utility will allow you to specify where to put the config files and will update the packages accordingly.|||

Hi

I am also in the same boat. I dont want to use deployment utility as I want to create a batch file. adminstrator will use this batch file to deploy on prod server.

I have configuration file that needs to be deployed on server along with package. please tell me if it possible or please give me work around for this.

|||You can specify the location of the configuration file when you run the package by using the /ConfigFile switch of DTEXEC.

dtswiz sqlserver2005

Is there somthing like dtswiz (from sqlserver 2000) in sqlserver 2005?
I want to import a tab separated file into a table.
Sure. In Management Studio Object Explorer right click on a database and
select Tasks>Import Data
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Anonymous" <anonymous@.noemail.com> wrote in message
news:%231v1Y%23S5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Is there somthing like dtswiz (from sqlserver 2000) in sqlserver 2005?
> I want to import a tab separated file into a table.
>
|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaCH41V5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Sure. In Management Studio Object Explorer right click on a database and
> select Tasks>Import Data
> --
> HTH
> Jasper Smith (SQL Server MVP)
<snip>
Ok, so I now know I am not losing my mind, when I look through the
Management Studio, and feel like I can't do anything. When I right click my
DB, I don't see the Import Data option. I am trying to move from one ISP
using SQL 2000 to one using SQL 2005. I downloaded:
Microsoft SQL Server Management Studio Express 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
What am I doing wrong? Management Studio seems so crippled, that I can't do
anything.
BV.
|||I don't think that functionality is available with the
Express Edition.
The Express Edition of Management Studio doesn't not include
all the features available in the other versions of SSMS.
-Sue
On Mon, 16 Jan 2006 23:25:24 -0500, "BenignVanilla"
<bvanillaREMOVE@.tibetanbeefgarden.com> wrote:

>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>news:eaCH41V5FHA.3760@.TK2MSFTNGP14.phx.gbl...
><snip>
>Ok, so I now know I am not losing my mind, when I look through the
>Management Studio, and feel like I can't do anything. When I right click my
>DB, I don't see the Import Data option. I am trying to move from one ISP
>using SQL 2000 to one using SQL 2005. I downloaded:
>Microsoft SQL Server Management Studio Express 9.00.1399.00
>Microsoft Data Access Components (MDAC) 2000.085.1117.00
>(xpsp_sp2_rtm.040803-2158)
>Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
>Microsoft Internet Explorer 6.0.2900.2180
>Microsoft .NET Framework 2.0.50727.42
>Operating System 5.1.2600
>What am I doing wrong? Management Studio seems so crippled, that I can't do
>anything.
>BV.
>

DTS2000 error

Hi
When I try to use DTS 2000 Packages in SQL2005 CTP I obtain this error:

Could not load file or assembly 'Microsoft.SqlServer.Dts80, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Impossibile trovare il file specificato. (Microsoft.SqlServer.DtsObjectExplorerUI)

Anyone can help me?

ThanksWhich CTP version are you using?

Look in your DTS\binn folder (by default this is at "C:\program files\Microsoft SQL Server\90\DTS\Binn") and see if there are any files of the names "Microsoft.SQLServer.DTS8*.dll"

I don't have "Microsoft.SqlServer.Dts80.dll" but I do have "Microsoft.SQLServer.DTS8HelperObjectModel.dll", "Microsoft.SQLServer.DTS8HelperUtility.dll" & "Microsoft.SQLServer.Exec80PackageUtil.dll"

and in "C:\program files\Microsoft SQL Server\90\DTS\Tasks" I have "Microsoft.SQLServer.Exec80PackageTask.dll".

Do you have these files, or similarly named files, on your system?

I am on the June CTP by the way. All of these files are version number 9.0.1187.0 which leads me to think that you may be on an earlier version.
You have the choice of whether to install the DTS2000 runtime or not when you install SSIS. Do you remember if you chose to install it?

-Jamie

Dts works from SqlServer but not from ASP.net

I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql server
It works right. but when I execute it from my asp.net page I have this error.
Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:\Documents and Settings\luisvalen\Mis documentos\Visual Studio Projects\InventariosLentos\LibreraLentos\exec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:\AplicacionesWeb\InventariosLentos\generacionprocesomateriaprima.aspx.vb:line 60LibreraLentos

I have this on my ASP page
Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click
Try
objexecsp.ejecuta_SP_EXISTENCIASMP()
lblmensajes.Text = "Proceso generado satisfactoriamente"
Catch ex As Exception
lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString
End Try
End Sub
this on my Data Classs
Public Function ejecuta_SP_EXISTENCIASMP()
' call UpdatePrice using a parameter array of SqlParameter objects
Try
Dim ejecutardts As New cDTS
ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")
Catch ex As Exception
Throw ex
End Try
End Function
This is what executes the DTS
Imports System.Runtime.InteropServices
Imports System.Configuration.ConfigurationSettings
Imports DTS
Public Class cDTS
Public Sub EjecutarDTS(ByVal NombreDTS As String)
Dim pkg As New DTS.Package
Dim oStep As DTS.Step
Try
pkg = New DTS.Package
'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")
pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")
pkg.AutoCommitTransaction = True
pkg.Execute()
For Each oStep In pkg.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)
End If
Next
Catch ex As System.Runtime.InteropServices.COMException
Throw ex
Catch ex As Exception
Throw ex
Finally
pkg.UnInitialize()
pkg = Nothing
End Try
End Sub
End Class

This is the CODE of my dts
but as I told before it Works when I right Click on it in enterprise manager
Dim ConnSql
Dim ConnDb2
'* Función para Conexion a Base de Datos ASW en Med13nt
Function ConexionSql()
'On Error Resume Next
Dim strConexion
strConexion = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=True;" & _
"User ID=sa;Password=xx;" & _
"Initial Catalog=asw;" & _
"Data Source=Med20nt"
Set ConnSql = CreateObject("ADODB.Connection")
ConnSql.Open strConexion
If Err.Number <> 0 then ConexionSql = False
End Function
'* Función de Conexion a ASW as400
Function ConexionDb2()
'On Error Resume Next
Dim strConexion
strConexion = "DSN=asw1;User ID=CLAUDIMON;Password=xx"
Set ConnDb2 = CreateObject("ADODB.Connection")
ConnDb2.Open strConexion
If Err.Number <> 0 Then ConexionDb2 = False

' conexion por OLEDB
' Dim strconexion
' strconexion = "Provider=IBMDA400.DataSource.1; Data source=10.2.0.10;User Id=FVENTAS;Password=xx"
' Set ConnDb2 = CreateObject("ADODB.Connection")
' ConnDb2.Open strConexion
' If Err.Number <> 0 Then ConexionDb2 = False
End Function

'* Trasfiere los datos de ASW a Med20nt
Private Function Transferir()
'On Error Resume Next
Dim strSql
Dim producto
Dim strSql2
Dim strSql3
Dim strCero
Dim strcomilla
Dim RS
Dim RS2
Dim RS3
Dim Contador
Dim StrBodega
strBodega=" "
contador=1
strcomilla="'"
strCero="0"
Set RS = CreateObject("ADODB.Recordset")
Set RS2 = CreateObject("ADODB.Recordset")
Set RS3 = CreateObject("ADODB.Recordset")
strSql3 = "SELECT distinct producto from sumvenmpf"
ConnSql.Execute (strSql3)
RS.Open strSql3, ConnSql
' ConnSql.Execute (strSql3)

Do Until RS.EOF
Set RS2 = CreateObject("ADODB.Recordset")
strSql = "SELECT srprdc, srsrom, srplan,ctname,sum(srsthq), sum(srpurq), sum(srcusq), sum(srpicq) FROM HCB453AFIH.srbsro,HCB453AFIH.srbctlsd where ctsign=srplan and srprdc="&strcomilla&RS.Fields("producto").value&strcomilla &" and (srsthq>0 or srpurq>0 or srcusq>0 or srpicq>0) group by srprdc,srsrom,srplan,ctname"
contador=0
RS2.Open strSql, ConnDb2
contador=contador+1
Do until RS2.EOF
strSql2 = "INSERT INTO ASW.dbo.Existenciasmp (srprdc,srsrom,srplan,ctname,srsthq, srpurq, srcusq, srpicq ) values ('" & RS2.Fields("srprdc").value & "','" & RS2.Fields("srsrom").value & "','" & RS2.Fields("srplan").value & "','" & RS2.Fields("ctname").value & "','" & RS2.Fields(4).value & "','" & RS2.Fields(5).value & "','" & RS2.Fields(6).value & "','" & RS2.Fields(7).value & "')"
ConnSql.Execute (strSql2)
RS2.MoveNext
Loop
RS2.Close
Set RS2 = Nothing


RS.MoveNext
Loop
RS.Close
Set RS = Nothing
End Function
'* Inicio de la Interfaz
Function Principal()
'On Error Resume Next
Call ConexionSql
Call ConexionDb2
Call Transferir()
Principal = DTSTaskExecResult_Success
End Function

The strange thing here is that it always returns 864 rows.
when executing from asp.net and 5000 from enterprise manager

I have the same issue, which I set up a DTS package in the SQL Server. When I execute package from the Enterpise manager, it ran with no error.

However, when I run it from an asp.net page, it fails. I use the below code to validate the error of the steps. What could it be wrong?

Thanks!

Terrence

DimWithEvents oPkgAs DTS.Package

oPkg =New DTS.Package()

Dim vStepAs DTS.Step

ForEach vStepIn oPkg.Steps

'test if the activescripttask type of task is in this step, and dont have to the transaction if so

'use instr (return value of the position of string location)

IfNot InStr(1,CType(vStep.Name,String), "DTSActiveScriptTask", CompareMethod.Text) > 0Then

IfNot InStr(1,CType(vStep.Description,String), "Update HTMLText Table", CompareMethod.Text) > 0Then

vStep.JoinTransactionIfPresent =True

Else

vStep.JoinTransactionIfPresent =False'if the step is the 'Update HTMLText Table' step then dont join to transaction

EndIf

Else

vStep.JoinTransactionIfPresent =False'if the step is executing a script task, then dont join the transaction

EndIf

vStep.RollbackFailure =True

Next

'modify transaction properties of the package

oPkg.UseTransaction =True

oPkg.AutoCommitTransaction =True

'set the current time to later evaluate the execution time

sTimestart = Now()

'execute the package

OnErrorResumeNext

oPkg.Execute()

OnErrorGoTo 0

'evaluate total execution time of the package

Dim sTime

sTime = DateDiff("s", sTimestart, Now())

'error logging

'default the flag to true (successful execution) and set to false if error occurs

Dim flagAsString =True

Dim sErrAsString

Dim strLogAsString

Dim oDTSStepAs DTS.Step

ForEach oDTSStepIn oPkg.Steps

If oDTSStep.ExecutionResult = oDTSStep.ExecutionResult.DTSStepExecResult_FailureThen

.....

|||

DTS is SQL Server Agent dependent, your SQL Server Agent needs a service account. Try the links below. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

DTS transactions

Hi!
I am using SQLServer DTS object to manage my database. There is
BeginTransaction method. My question is:
1. In which database's context SQLServer.BeginTransaction starts
transaction?
2. How can I know/change current context in which SQLServer object works?Igor Solodovnikov, The DTS Object model has two hierarchies: 1) The DTS
Application hierarchy, which contains information about components registere
d
with the system and packages stored in SQL Serverand Meta Data Services, 2)
The DTS package hierarchy which contains all the the functional DTS elements
- tasks, steps, connections and global variables. Which hierarchy are you
using? Also, SQL Server 2000 Books online has a wealth of information about
the DTS Object model and it's methods and properties. If you have any furthe
r
questions, my email is frank_chang91@.hotmail.com.
"Igor Solodovnikov" wrote:

> Hi!
> I am using SQLServer DTS object to manage my database. There is
> BeginTransaction method. My question is:
> 1. In which database's context SQLServer.BeginTransaction starts
> transaction?
> 2. How can I know/change current context in which SQLServer object works?
>