db datatypes: dts

Showing posts with label dts. Show all posts

Monday, March 26, 2012

Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.

Monday, March 12, 2012

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regards
perhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regardsperhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx
.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with executing a SQL Server DTS Package from ASP

Could someone help. I am new at writing scripts to execute a DTS package using ASP. After I run my scripts, I get the following error message :

Step [DTSStep_DTSDataPumpTask_1] failed
Task "Import FITA Calendar"

Package [Import Fita Calendar] failed

Here is what my scripts look like :

<%
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sMessage, bStatus

Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "myserver","sa","$12eww",DTSSQLStgFlag_Default,"","","","Import FITA calendar"
oPkg.Execute()

bStatus = True

For Each oStep In oPkg.Steps
sMessage = sMessage & " Step [" & oStep.Name & "] "
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
sMessage = sMessage & " failed "
bStatus = False
Else
sMessage = sMessage & " succeeded "
End If
sMessage = sMessage & "Task """ & oPkg.Tasks (oStep.TaskName).Description & """"
Next

If bStatus Then
sMessage = sMessage & "Package [" & oPkg.Name & "] succeeded"
Else
sMessage = sMessage & "Package [" & oPkg.Name & "] failed"
End If

Response.Write sMessage
Response.Write "Done"

%>because I do that alot. And you can exec DTS inside SP. So you can place this inside your sp and just exec from asp. Hope that helps.

example

exec master..xp_cmdshell 'dtsrun /Ssql1 /NPublish /E'
This will execute the DTSRun command line utility with the parameters

/S = SQL Server Host Name
/N = DTS Package Name
You can either use /E for "trusted connection" or /U/P to supply username and passwords

Friday, March 9, 2012

Problem with dtsrun

I am trying to execute a DTS Package from within a stored procedure, however the Execute statement hangs while running the debugger.

The sql is EXECUTE @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /E /SBIGO /NPFW Data Load', NO_OUTPUT

The debugger hangs, SQL Query Analyser does not respond after exiting the debugger and MSSQLSERVER needs to be stopped & restarted.

The DTS Packages runs fine from the cmd prompt as can be seen below

C:\Documents and Settings\Greg>DTSRun /E /SBIGO /NPFW Data Load
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data] Step
DTSRun OnProgress: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data]
Step; 500 Rows have been transformed or copied.; PercentComple te = 0; ProgressCount = 500
DTSRun OnFinish: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Mon
thly_Load_Data] Step
DTSRun: Package execution complete.

C:\Documents and Settings\Greg>

Any help would be great.

Are debugging procedure in QA?

If so comment the dts package during debug there may be disconnect from DTSRun utility and debugger might be not able to handle situation properly...

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

THanks,
Tony

Problem with DTS schedule

Hi,

I have a little DTS problem here.
This package is programmed in an SQL Server v7.0 box and it executes OK when I run it manually.

The problem starts when I want to schedule the automatic execution of the DTS and in the Job History I got this error: "Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

That means that the user owner of the DTS must be a SysAdmin? The current role for the user is "Process Administrator" but not "System Administrator".

As I cannot give the user superpower in the server, do I have to recompile de DTS under other user that is System Administrator?

Regards,

GOne of the archived post refers
Expand Management, right-click SQL Server Agent, and then click Properties. Click the Job System tab.

Under Non-SysAdmin job step proxy account, clear the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box, and click Reset Proxy Account.

Type the user name, password, and domain of the user account to be used by SQL Server Agent when running jobs owned by users who are not system administrators.

Problem with DTS reading from spreadsheet

Has anyone encountered an issue with blank cells when DTS tries to read
a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I have
a spreadsheet where I read a range, and some of the cells show up as blank
even though there are values in them. If I alter the cell and resave the
sheet, it works fine. I suppose this could be worksheet corruption, but I
wanted to see if there were any other ideas about what to do. Thanks.
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************Are you seeing this?
Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)
Allan
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
> Has anyone encountered an issue with blank cells when DTS tries to read
> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
> have
> a spreadsheet where I read a range, and some of the cells show up as
> blank
> even though there are values in them. If I alter the cell and resave
> the
> sheet, it works fine. I suppose this could be worksheet corruption, but
> I
> wanted to see if there were any other ideas about what to do. Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************|||no IT JUST DOESNT FUCKING WORK.
THATS ONE OF THE REASONS I FUCKING HATE EXCEL SO MUCH
SHIT DOESNT WORK LIKE IT SAYS IT SHOULD
just the basics; microsoft
start fucking fixing bugs you piece of shit company
oh.. is $70bn of OUR hard-earned money not enough to fix a half dozen
bugs?
MS is too fat and lazy to succeed; plz boycott MS everywhere
they just dont fucking get it anymore|||Yes, thank you! It is amazing. This was driving me nuts. Excel will read
in the character data, the character mixed with number data, and ignore the
number data.
Your website is one of the best SQL sites out there. The looping example
alone was a lifesaver.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEUNVTM5FHA.3496@.TK2MSFTNGP10.phx.gbl...
> Are you seeing this?
> Excel Inserts Null Values
> (http://www.sqldts.com/default.aspx?254)
>
> Allan
> "Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
> news:#5c#mlL5FHA.472@.TK2MSFTNGP15.phx.gbl:
>> Has anyone encountered an issue with blank cells when DTS tries to read
>> a range from a spreadsheet using a SELECT * FROM [Sheetname$] query? I
>> have
>> a spreadsheet where I read a range, and some of the cells show up as
>> blank
>> even though there are values in them. If I alter the cell and resave
>> the
>> sheet, it works fine. I suppose this could be worksheet corruption, but
>> I
>> wanted to see if there were any other ideas about what to do. Thanks.
>>
>> --
>> *************************************************
>> Andy S.
>> andymcdba1@.noreply.yahoo.com
>> Please remove "noreply" before replying.
>> *************************************************
>

Problem With DTS Package - To Delay Process

Hello All SQL Experts.

Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

Please advise. Thank you.

Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

declare @.MainUpdate datetime
declare @.TempUpdate datetime

select @.MainUpdate=Main_Update_CET from APMEAPV_Compare
select @.TempUpdate=Temp_Update_CET from APMEAPV_Compare
--select @.MainUpdate, @.TempUpdate

if @.MainUpdate<>@.TempUpdate
begin
DECLARE @.commandK varchar(1000)
SET @.commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls"'
exec master..xp_cmdshell @.commandK, No_Output

ENDIf you don't have the ability to crate a JOB on the server that will run steps
and create each step as a different DTS or executable command.

You can do a couple of things technically.

Put a step in the DTS to loop a number 36000 = 1 second I think.

Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

opackage.Execute

opackage.UnInitialize

Set opackage = Nothing

I would ask what are you trying to accomplish and is this the best tool to do this.

Hope this helps.|||Hello rbackmann.

Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

Thank you.

problem with DTS package

have a DTS package which utilizes an "Execute Process
Task". This task calls a batch file on my server. I keep getting this error trying to run the DTS from sqlserver:

"CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1:

Does anyone know what this error mean? TIAWhat is the level of service pack on SQL & OS?|||Possibly the batch job you are calling is producing an error. Try executing your batch job from a command window and inspect its result.

kbk|||Also enable DTS package logging for more information on this behaviour.

problem with DTS and text file

Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.
Someone any idea what's wrong?does your comma separate decimals from the significant digits?|||Originally posted by troj
Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.

Someone any idea what's wrong?

Nothing's wrong...although I'm suprised it didn't blow up...comma's in numbers are just a presentation issue...unlike precision...

But for example...you can't do..

CREATE TABLE myTablefloat (col1 float)
INSERT INTO myTablefloat(col1) SELECT 1,203.45

But for some reason in a text file it allows it|||Originally posted by ms_sql_dba
does your comma separate decimals from the significant digits?

it are money values, for example 10,22
in sql table it becomes 1022|||Originally posted by troj
it are money values, for example 10,22
in sql table it becomes 1022

What's the collation, language of your sql server box?

Sounds like it doesn't handle comma as precision.

Load the data in to a work table that the column defined as varchar..

Then populate your final table by doing an insert and convert your data..something like

SELECT CONVERT(float,REPLACE(col1,',','.'))|||If changing datatype is a problem you can even use the ActiveX replace function in the DTS transformation to replace "," by "."

Problem with DTS

I am using a DTS package to import data from primary server to secondary
server to update the changes occured in few tables and both located in a
different location. The problem is that whenever this DTS fails to execute i
will loose even the existing datas in secondary server since it delete the
existing data before inserting, how to overcome this? OR Is there any better
ways to implement this?
Thanks in advanceHi
Well, I'd transfer the OLD data before deleting into a temporary table and
in case of failure ( in order to not loose the data) nove the data back.
What's error do you get when you run the DTS and it failed?
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
>I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
> i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any
> better
> ways to implement this?
> Thanks in advance
>|||It just showing "Job Failed". It happens whenever if there any problem with
internet or network.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Hi
> Well, I'd transfer the OLD data before deleting into a temporary table
and
> in case of failure ( in order to not loose the data) nove the data back.
> What's error do you get when you run the DTS and it failed?
>
>
> "imtiaz" <Imtiaz@.microsoft.com> wrote in message
> news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
execute
the
>|||Ok, so you can specify an OUTPUT file under Advanced Tab in the Step's
definition.It will give the error desciption
You will have to introduce some logic behind like if the job's step is
failed go to the next step and do soemthing
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:uFH027KfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> It just showing "Job Failed". It happens whenever if there any problem
> with
> internet or network.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> and
> execute
> the
>|||Can you put Delete and Insert into a single transaction? If one step fails,
just roll back the whole transaction.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>|||Try www.sqlscripter.com to transfer your data.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
mfg
Marc Eggenberger
Someone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:

> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
mfg
Marc EggenbergerSomeone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but
a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Membe
r.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is wha
t
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:

> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server doe
s
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Problem with DTS

Hi,
I was wondering if anyone has ever had this problem before.
I have a whole host of DTS packages that are executed from one DTS package.
The DTS packages essentially get data from a Sybase database and puts the
data into a text file, runs a simple Stored proc which etc, nothing really
complicated.
Problem is lately when I run the DTS package which is called from a Batch
file which is executed by scheduler, random packages seem to go into a
continuous loop and the package continues to insert data into the text file
until such time as my server runs out of space.
Any ideas anyone.
Thanks
KNever seen that problem.
Do you have the tasks set to "Close connection on completion"
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kathy Bezuidenhout" <Kathy.Bezuidenhout@.nospam.aig.com> wrote in message
news:ab2cnTWLr4q05gqiU-KYjQ@.is.co.za...
> Hi,
> I was wondering if anyone has ever had this problem before.
> I have a whole host of DTS packages that are executed from one DTS
package.
> The DTS packages essentially get data from a Sybase database and puts the
> data into a text file, runs a simple Stored proc which etc, nothing
really
> complicated.
> Problem is lately when I run the DTS package which is called from a Batch
> file which is executed by scheduler, random packages seem to go into a
> continuous loop and the package continues to insert data into the text
file
> until such time as my server runs out of space.
> Any ideas anyone.
> Thanks
> K
>

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
--
mfg
Marc EggenbergerSomeone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:
> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Monday, March 26, 2012

Problem with Insert trigger

Monday, March 12, 2012

Problem with execution of DTS

Problem with execution of DTS

Problem with executing a SQL Server DTS Package from ASP

Friday, March 9, 2012

Problem with dtsrun

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

Problem with DTS schedule

Problem with DTS reading from spreadsheet

Problem with DTS reading from spreadsheet

Problem with DTS reading from spreadsheet

Problem with DTS reading from spreadsheet

Problem With DTS Package - To Delay Process

problem with DTS package

problem with DTS and text file

Problem with DTS

Problem with DTS

Problem with DTS

Problem with DTS

Problem with DTS

db datatypes

Blog Archive

About Me