Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts

Sunday, March 11, 2012

DTS and DBCC

Question 1:

When I setup DTS package, what kind of privilege I need to get.??
Only SA can setup or there is other server privilage can be add.

Question 2:

How do I setup a database maintain job like ( backup, DBCC reindex, update statistics ) , using DTS or JOBs ? it there any suggest scripts?

Thanks in advance.

GY1) In general DTS package contains one or more steps that are executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version. So normal user privileges are enough to accomplish the task and it depends purely on level of privilege for that user on the database.

Then coming to DTS security, if you set an owner password, the package user needs the password to edit or run the package. If you set a user password, you also must set an owner password.

2) You can setup Maintenance Jobs to perform backup, DBCC checks and optimization tasks, from Enterprise Manager goto Management and there you can find DB Maint.plan to setup.

By all means refer to BOOKS ONLINE for more information.|||Thanks Satya.

That means any use can create DTS package only if he or her has the privilege for the database objects ( tables, views , etc). SA is not the only id we can create DTS

GY|||1. Why not try it and see. Any user can create a package - remember dts is a client utility which can connect to sql server.
It's usually easier to run as sa though.

2. You can schedule a scheduled job with t-sql tasks and put any statement(s) in it you wish. Usually better to put individual operations as separate steps or call an SP to do them.
I'm not a fan of maintenance plans - would rather code the steps myself.|||True, whatever referred by Nigel (Thanks).
It works as my developers and support staff can do it.
With DTS you can do it... :)|||Thanks nigelrivett and satya

Wednesday, March 7, 2012

DTS "Execute Process Task" fails...

I have a DTS package which utilizes an "Execute Process
Task". This task calls an EXE local to my machine. When I
initially setup the DTS routine it worked. A few days
later I tried to run it again and the executable failed
on one of its steps.

I tested the executable thoroughly and decided that the
problem was with the DTS routine. I found that when I
perform the following steps the task runs properly:

1. Edit the DTS routine
2. Edit the properties of the Execute Process Task
3. Click on the "..." button to navigate to my EXE (even
though the path supplied is already correct)
4. Run it

However, after saving the package and then closing and re-
opening SEM, the same DTS routine fails when I execute it
manually without performing the steps above.

Any ideas?
Thanks.All I can think is that the pathing for the EXE is not the same for the server as it is on your local machine. Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. I have something similar here and I have to run some DTS packages via terminal services to ensure correct pathing.|||I know what you are saying (I've run into that before). I don't think that's the case here though because I am running everything on my local machine. So, if I understand you right, I am running the job on the "server" (my machine).

Thanks for the input though. Any other ideas?|||Ok, cools, so the local machine is the server. What error message are you recieving?|||I'v been tinkering with DTS packages and found myself getting caught out with the server path vs client path. Some times it looks on the server i.e Browse folder will actually present a view of C:\Win... of the machine on which your instance resides and sometimes you're looking at your local machine. Confusing but makes sense sometimes.|||The error message:
Step Error Source: Microsoft Data Transformation Services (DTS)Package
Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900

I thought it was some kind of permissions issue, but I've tried several things relating to DTS package permissions to no avail.|||if you set the package as a job on the server then it is a problem with the path name. The automatied job will run on the server thinking that the server is "god" not the local machine. I would say to share the local drive, map it to the server and set the path that way. It will work if you are running a job.
Jim|||1. The server machine is my local machine so there is no issue with executable paths.

2. The DTS package is not running as a job. This issue occurs when I run it manually.

More information:
When I run the DTS package and it gets to the "Execute Process Task" step it does run the EXE every time. However, the EXE fails if I don't perform the steps listed in my initial post.

Thanks for the input.|||So it does not error out in the dts step it actualy dies when the exe is run? what is the EXE?
Jim|||The EXE is a C++ Console application which serves as a wrapper around a 3rd party DLL.

The DLL basically performs an import into SQL server while encapsulating necessary business rules.

It is a very simple application.

Step 1: Connect
Step 2: Import (it fails here)
Step 3: Disconnect|||The only thing that I can think of is that a paramiter is lost or not saved in the dts package. But why that would need a path for an exe im not sure. Does the exe have varialbles in it? Jim|||JDionne! You da man!! You got me thinking about the parameters and...
I just figured it out!!

I've been so busy looking at possible security resolutions that I missed an obvious one.

The problem was with the parameters to the EXE. One of the parameters is a filepath to an import file. Since the EXE is in the same directory with this file I had just been supplying the file name (with out specifying a path) to the parameter. This works when the current working directory is the path to my EXE. So that's why it would always work after performing the steps in my initial post. These steps changed the current working directory to be that of my EXE.

The solution was to add the path to my filepath parameter.

Thanks JDionne and everyone for the help!|||Im glad that I could help. A fresh mind is always a good thing.
If you ever have any other problems dont hesitate to ask. you can private msg me too if youd like.
Regards Jim

Sunday, February 26, 2012

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do?
Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
Is this the right exe file to use?
|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:

> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
> Is this the right exe file to use?
>
|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.
|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:

> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:
> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:
> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

Friday, February 24, 2012

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mysql DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE
\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:

> The mysql DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE
\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:

> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

Sunday, February 19, 2012

DTC on ACTIVE ACTIVE cluster - urgent

Greetings, we have 4 node cluster setup. 3 nodes are active and one is
passive. As I understood, MS DTC will run only one node and hence will be
available to the SQL instances running on that node. Can I run DTC on
multiple nodes in a cluster?
I have created one DTC resource prior to the SQL install. I can not add
addional DTC resource. Our apps need to have DTC on most of the instances
which we can not put them on one node. Any suggestions?
Thanks in advance.
The single DTC instance will support all SQL instances in the cluster,
regardless of their current host node. You do not need a unique DTC
instance for each SQL instance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mintz" <Mintz@.discussions.microsoft.com> wrote in message
news:8DBA18AB-FA21-474D-B630-78F0CA8B910D@.microsoft.com...
> Greetings, we have 4 node cluster setup. 3 nodes are active and one is
> passive. As I understood, MS DTC will run only one node and hence will be
> available to the SQL instances running on that node. Can I run DTC on
> multiple nodes in a cluster?
> I have created one DTC resource prior to the SQL install. I can not add
> addional DTC resource. Our apps need to have DTC on most of the instances
> which we can not put them on one node. Any suggestions?
> Thanks in advance.
|||I agree with Geoff but the key to DTC working properly is to enable DTC for
network access. You can do this from Control panel/Add remove programs/add
remove components/Application Server. You have to do this on all nodes in
your cluster.
Also refer to the following articles
http://support.microsoft.com/default...b;en-us;301600
http://support.microsoft.com/default.aspx?kbid=817064
"Geoff N. Hiten" wrote:

> The single DTC instance will support all SQL instances in the cluster,
> regardless of their current host node. You do not need a unique DTC
> instance for each SQL instance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Mintz" <Mintz@.discussions.microsoft.com> wrote in message
> news:8DBA18AB-FA21-474D-B630-78F0CA8B910D@.microsoft.com...
>
>
|||i stuck at step 4. so my questions are
1. do i need an separate IP address? can i use the ip in cluster group
2. for physical disk, do i have to create another physical disk which
is shared by both nodes? can i use drive "Q:\"?
right now i did not follow the doc and created MSDTC in cluster group
using ip, physical disk and network name in that group (created
automatically). only one node can see msdtc which is not what i
expect.
thanks,
jj
"Rony Thomas" <rony_p_thomas@.hotmail.com> wrote in message news:<4C07737F-248A-42DE-87DA-F1D1590C44E8@.microsoft.com>...[vbcol=seagreen]
> I agree with Geoff but the key to DTC working properly is to enable DTC for
> network access. You can do this from Control panel/Add remove programs/add
> remove components/Application Server. You have to do this on all nodes in
> your cluster.
> Also refer to the following articles
> http://support.microsoft.com/default...b;en-us;301600
> http://support.microsoft.com/default.aspx?kbid=817064
>
> "Geoff N. Hiten" wrote:
|||try creating another group with resources for DTC and make sure you enable
network access
"jj" wrote:

> i stuck at step 4. so my questions are
> 1. do i need an separate IP address? can i use the ip in cluster group
> 2. for physical disk, do i have to create another physical disk which
> is shared by both nodes? can i use drive "Q:\"?
> right now i did not follow the doc and created MSDTC in cluster group
> using ip, physical disk and network name in that group (created
> automatically). only one node can see msdtc which is not what i
> expect.
> thanks,
> jj
> "Rony Thomas" <rony_p_thomas@.hotmail.com> wrote in message news:<4C07737F-248A-42DE-87DA-F1D1590C44E8@.microsoft.com>...
>

Friday, February 17, 2012

DTC

Does anyone have any instructions on how to setup the MSDTC properly and
make it work with all the security updates. I am trying to communicate
between to windows 2000 machines and I am getting:

Error 8524
The current Transaction could not be Exported to the Remote Provider.

The distributed transaction works fine on to instances of SQL Server 2000 on
the same machine.

RandySorry Correction

I am running on XP SP2 and trying to establish a transaction with Windows
2000 Server SP4.

"Randal" <rfraser@.novaflow.com> wrote in message
news:X8q5g.497$VV2.39662@.news20.bellglobal.com...
> Does anyone have any instructions on how to setup the MSDTC properly and
> make it work with all the security updates. I am trying to communicate
> between to windows 2000 machines and I am getting:
> Error 8524
> The current Transaction could not be Exported to the Remote Provider.
> The distributed transaction works fine on to instances of SQL Server 2000
> on the same machine.
> Randy