DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
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|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

DTS Execute SQL Task failing in SQLServer 2005

Hello All,
We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
Enterprise Edition 32-bit SP2.
The new version is working fine and the performance is also comparable. For
the DTS packages, I download and installed the DTS Runtime components. All
the DTS packages work except for one. The package, calls a SP residing in the
user database in "Execute SQL" Task and then copies the data into a flat file
via DataPump Task on a remote share. The remote share is available all the
time.
Last night the Execute SQL task had failed and this morning I troubleshooted
it and found out that this is happening randomly and whenever it fails I see
this in log that it writes to. I tested it once after the migration and I ran
it once again now and in both the cases it worked fine.
This process never used to fail in SQLServer 2000. So, I was wondering if
this has anything to do with SQLServer 2005.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks,
rgnHi
Error number 80004005 is a generic error and has quite a wide range of
causes for instance:
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
http://tinyurl.com/2s9qqm
In the long term you should be considering moving over to SSIS.
John
"rgn" wrote:
> Hello All,
> We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
> SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
> Enterprise Edition 32-bit SP2.
> The new version is working fine and the performance is also comparable. For
> the DTS packages, I download and installed the DTS Runtime components. All
> the DTS packages work except for one. The package, calls a SP residing in the
> user database in "Execute SQL" Task and then copies the data into a flat file
> via DataPump Task on a remote share. The remote share is available all the
> time.
> Last night the Execute SQL task had failed and this morning I troubleshooted
> it and found out that this is happening randomly and whenever it fails I see
> this in log that it writes to. I tested it once after the migration and I ran
> it once again now and in both the cases it worked fine.
> This process never used to fail in SQLServer 2000. So, I was wondering if
> this has anything to do with SQLServer 2005.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
> Thanks,
> rgn

DTS Errors with Access?

Hi,

I have a DTS package that when executed through Enterprise Manager works just fine.
I have an Access app on a different computer that executes the DTS package via VB using the syntax below. This process causes the DTS package to return an error of 'SQL Server Does Not Exist or Access Denied'.
I have tried both authentication types, not making a difference. I have used the SA login from Access and still get the error. All the necessary DTS drivers are installed on the second box and I can connect to the SQL tables succesfully also...
Any ideas? Thanks!

Private Sub cmd_MPSDTS_Click()

Dim oPackage As New DTS.Package
On Error GoTo eh

oPackage.LoadFromSQLServer "SVRName", "sa", "pwd", DTSSQLStgFlag_Default, "", "", "", "Pkg_Name", 0
'Execute the Package

oPackage.Execute
'MsgBox oPackage.Description, vbInformation, _
"Re-import Excel sheet."

'Clean up.
MsgBox ("Ran DTS Package")
Set oPackage = Nothing
Exit Sub
eh:
MsgBox Err.Description, vbCritical, _
"Error Running Package"

End SubI assume you have tried pinging the server from the machine with the access db? or rather from the machine that opens the access db.|||You should also check that the user you are executing as from Access is the same as the user that is executing from Enterprise Manager or at least has the same privileges.|||Not sure why that would make a difference (Not saying it wouldn't, I'm just not sure why it would). The error they are receiving makes it look as though they can't reach the server at all...|||Found the problem,

The DTS package imported information from an Informix database. The necessary drivers weren't installed on the second machine so the job failed even though I could connect to the SQL server just fine. I got around it by changing the code to execute a SQL Job which in turn executed the DTS package. This way it ran on the SQL server.

Thanks!

DTS Design Error - Access is denied

I am pretty new to MS SQL Server ...

I have installed SQL Server Enterprise Manager Version 8.0. I am getting the following error while try to create a new DTS Package.

"DTS designer Error ... Access Denied"

getting the DTS packge design window but there is no TASK icon visible
either in the tool bar or menu....

Enterprise Manager Version 8.0

OS : Windows XP pro with SP1If you go to the msdb database, the user you are using needs to have EXEC permissions on the procs that have dts in the name. This is by default granted to public, so I'm not sure why your user wouldn't have that. ?|||See whether this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;814113 is any help to you.

DTS / Export Data in Sql2005 Mgm Studio

Dear all,

I have a SQL 2000 and SQL 2005 servers separately, but by whatever reason, I can't use SQL 2000 Enterprise Mgr anymore now so I have to use SQL 2005 Mgm studio to open up my SQL 2000 databases now.

However, I'd like to export data from Sql 2000 to another Sql 2000 server, but via a Sql 2005 mgm studio interface right now. How can I do so? I can't find "Export Data" from the "Task" context menu in Sql 2005 mgm studio.

Thanks,

Hi,

if you want to use the SQL Server MS for that you have to right click on whatever database on choose Tasks..Export data. That should be straight forward for you like in SQL 2k days.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

DTS

What is the easiest way to give a user the abilitiy to run a DTS package
without having the Enterprise Manager on their workstation?I'm no expert my any stretch...I think there's an option to save your DTS
package as a Visual Basic executable file. If I remember correctly, this
option is provided while you go through the import/export wizard.
"Johnny" wrote:

> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?|||Hi
Does the user have QA on his/her workstation?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:5EAD6100-98E2-4378-B38D-D3BB8F059CE0@.microsoft.com...
> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?|||> What is the easiest way to give a user the abilitiy to run a DTS package
> without having the Enterprise Manager on their workstation?
They need to have the dts runtime on their computer, then you can call
dtsrun.exe from the command line, for more info look in BOL.
If you wish to call a dts package from a sql script you can use
xp_cmdshell..

DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
--
David Portas
SQL Server MVP
--|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>|||docsql wrote:
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
Are you sure the account running the DTS script has access to the UNC
you provide?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
>> I am using the UNC path
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
>> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

dtexec

I have saved a copy of a package to a 64-bit server running Windows 2003 Enterprise Edition SP1. I changed the Run64BitRuntime to true before saving the package. When I try to use DTExec to execute the package which does have a script task in it I receive the below error.

Error: 2005-08-17 16:52:32.89
Code: 0x00000005
Source: Script Task 1
Description: The script files failed to load.
End Error

Any help is appreciated.

Thanks.In your script task set PreCompileScriptIntobinaryCode=TRUE

This is a known issue with 64-bit machines. The default setting for this property (as of the next CTP) has been set to TRUE because of it.

-Jamie|||Jamie is correct that the default value of this property has been changed from False to True. The Integration Services team opted for 64-bit convenience at the cost of increased package size. 32-bit users may want to measure the impact of this setting if package size is ever a concern.

-Doug|||Thanks you guys - I have been able to get the packages scheduled.

DTC restart shutdown SQL server, why?

SQL 2000 SP4 standard on Windows 2003 Enterprise.

On Friday our DTC service restarted on a couple of servers (we think cause of a config change made by the network guys) but on one server it also shutdown the sql server... We cannot find anything that indicates why. I have looked and cannot find any reason why SQL would be a require DTC to be running expecially since none of the other servers shut down.

The only difference in this one server as compared to the others is it is a publisher for merge replication and the other servers are either a subscriber or publisher of transactional. Would this have anything to do with it?

Also is SQL actually dependant on DTC for any reason that would cause it to be shutdown when DTC is restarted?

A down MSDTC shouldn't cause your sqlserver to stop running. There must be something else in the loop. What can you find in sql logs (i.e. errorlog.*).

DTC issue or SQL Syntax issue

Hi All,
The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
server is SQL 2000 Enterprise SP4.
I am executing this sql in query analyzer:
create table #StoreInfoTraits (
[record type] varchar(3),
[site id] int,
[current effective date] smalldatetime,
[trait id] varchar(50),
[trait value] int
)
DECLARE @.cmd as varchar(200)
SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
insert into #StoreInfoTraits exec(@.cmd)
DROP TABLE #StoreInfoTraits
And getting this error:
OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
"The transaction manager has disabled its support for remote/network
transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "co-dbdev-01" was unable to begin a distributed transaction.
I’ve checked the DTC services per the following link and both DTC services
are running under a domain account and configured properly.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
This executes and returns the recordset:
Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
Since the remote stored proc returns the records when executing the stored
proc, is it a DTC issue or is this syntax correct ?
insert into #StoreInfoTraits exec(@.cmd)
Hi
I think Bill's post covered most things in
http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
work OK then dtc itself can be ruled out.
Your syntax should work (well my test does on mine) providing dtc is running
correctly!
If you don't want to start a distributed transaction you could try
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
storeinfo.dbo.pr_traitassignments' )
DECLARE @.cmd varchar(400)
SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
effective date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
storeinfo.dbo.pr_traitassignments'' ) '
EXEC ( @.cmd )
John
"brymer28303" wrote:

> Hi All,
> The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> server is SQL 2000 Enterprise SP4.
> I am executing this sql in query analyzer:
> create table #StoreInfoTraits (
> [record type] varchar(3),
> [site id] int,
> [current effective date] smalldatetime,
> [trait id] varchar(50),
> [trait value] int
> )
> DECLARE @.cmd as varchar(200)
> SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> insert into #StoreInfoTraits exec(@.cmd)
> DROP TABLE #StoreInfoTraits
> And getting this error:
> OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> "The transaction manager has disabled its support for remote/network
> transactions.".
> Msg 7391, Level 16, State 2, Line 1
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "co-dbdev-01" was unable to begin a distributed transaction.
> I’ve checked the DTC services per the following link and both DTC services
> are running under a domain account and configured properly.
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> This executes and returns the recordset:
> Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> Since the remote stored proc returns the records when executing the stored
> proc, is it a DTC issue or is this syntax correct ?
> insert into #StoreInfoTraits exec(@.cmd)
>
>
|||Thanks John. I tried this
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
storeinfo.dbo.pr_TraitAssignments' )
and got this:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
the object has no columns or the current user does not have permissions on
that object.
I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
and it returns:
TVA5008-30-2006ACQUISITION0
TVA5108-30-2006ACQUISITION0
TVA5208-30-2006ACQUISITION0
TVA5308-30-2006ACQUISITION0
TVA5408-30-2006ACQUISITION0
And, we apparently have a dtc issue I found when I ran the dtctester.exe --
unable to connect.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I think Bill's post covered most things in
> http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> work OK then dtc itself can be ruled out.
> Your syntax should work (well my test does on mine) providing dtc is running
> correctly!
> If you don't want to start a distributed transaction you could try
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> storeinfo.dbo.pr_traitassignments' )
> DECLARE @.cmd varchar(400)
> SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> effective date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> storeinfo.dbo.pr_traitassignments'' ) '
> EXEC ( @.cmd )
> John
> "brymer28303" wrote:
|||Hi
The user for the remote server will be determined by how the linked server
was created.
The dtctester error looks like you are not even making it to the remote
server! If you go through Bill's post and check out the networking issues it
may show something. You could also try DTCPing.
John
"brymer28303" wrote:
[vbcol=seagreen]
> Thanks John. I tried this
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
> storeinfo.dbo.pr_TraitAssignments' )
> and got this:
> Msg 7357, Level 16, State 2, Line 1
> Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
> DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
> the object has no columns or the current user does not have permissions on
> that object.
> I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
> and it returns:
> TVA5008-30-2006ACQUISITION0
> TVA5108-30-2006ACQUISITION0
> TVA5208-30-2006ACQUISITION0
> TVA5308-30-2006ACQUISITION0
> TVA5408-30-2006ACQUISITION0
> And, we apparently have a dtc issue I found when I ran the dtctester.exe --
> unable to connect.
> "John Bell" wrote:

DTC issue or SQL Syntax issue

Hi All,
The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
server is SQL 2000 Enterprise SP4.
I am executing this sql in query analyzer:
create table #StoreInfoTraits (
[record type] varchar(3),
[site id] int,
[current effective date] smalldatetime,
[trait id] varchar(50),
[trait value] int
)
DECLARE @.cmd as varchar(200)
SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
insert into #StoreInfoTraits exec(@.cmd)
DROP TABLE #StoreInfoTraits
And getting this error:
OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
"The transaction manager has disabled its support for remote/network
transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "co-dbdev-01" was unable to begin a distributed transaction.
Iâ've checked the DTC services per the following link and both DTC services
are running under a domain account and configured properly.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
This executes and returns the recordset:
Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
Since the remote stored proc returns the records when executing the stored
proc, is it a DTC issue or is this syntax correct ?
insert into #StoreInfoTraits exec(@.cmd)Hi
I think Bill's post covered most things in
http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
work OK then dtc itself can be ruled out.
Your syntax should work (well my test does on mine) providing dtc is running
correctly!
If you don't want to start a distributed transaction you could try
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
storeinfo.dbo.pr_traitassignments' )
DECLARE @.cmd varchar(400)
SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
effective date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
storeinfo.dbo.pr_traitassignments'' ) '
EXEC ( @.cmd )
John
"brymer28303" wrote:
> Hi All,
> The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> server is SQL 2000 Enterprise SP4.
> I am executing this sql in query analyzer:
> create table #StoreInfoTraits (
> [record type] varchar(3),
> [site id] int,
> [current effective date] smalldatetime,
> [trait id] varchar(50),
> [trait value] int
> )
> DECLARE @.cmd as varchar(200)
> SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> insert into #StoreInfoTraits exec(@.cmd)
> DROP TABLE #StoreInfoTraits
> And getting this error:
> OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> "The transaction manager has disabled its support for remote/network
> transactions.".
> Msg 7391, Level 16, State 2, Line 1
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "co-dbdev-01" was unable to begin a distributed transaction.
> Iâ've checked the DTC services per the following link and both DTC services
> are running under a domain account and configured properly.
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> This executes and returns the recordset:
> Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> Since the remote stored proc returns the records when executing the stored
> proc, is it a DTC issue or is this syntax correct ?
> insert into #StoreInfoTraits exec(@.cmd)
>
>|||Thanks John. I tried this
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
storeinfo.dbo.pr_TraitAssignments' )
and got this:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
the object has no columns or the current user does not have permissions on
that object.
I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
and it returns:
TVA 50 08-30-2006 ACQUISITION 0
TVA 51 08-30-2006 ACQUISITION 0
TVA 52 08-30-2006 ACQUISITION 0
TVA 53 08-30-2006 ACQUISITION 0
TVA 54 08-30-2006 ACQUISITION 0
And, we apparently have a dtc issue I found when I ran the dtctester.exe --
unable to connect.
"John Bell" wrote:
> Hi
> I think Bill's post covered most things in
> http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> work OK then dtc itself can be ruled out.
> Your syntax should work (well my test does on mine) providing dtc is running
> correctly!
> If you don't want to start a distributed transaction you could try
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> storeinfo.dbo.pr_traitassignments' )
> DECLARE @.cmd varchar(400)
> SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> effective date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> storeinfo.dbo.pr_traitassignments'' ) '
> EXEC ( @.cmd )
> John
> "brymer28303" wrote:
> > Hi All,
> >
> > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > server is SQL 2000 Enterprise SP4.
> >
> > I am executing this sql in query analyzer:
> >
> > create table #StoreInfoTraits (
> > [record type] varchar(3),
> > [site id] int,
> > [current effective date] smalldatetime,
> > [trait id] varchar(50),
> > [trait value] int
> > )
> > DECLARE @.cmd as varchar(200)
> > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > insert into #StoreInfoTraits exec(@.cmd)
> > DROP TABLE #StoreInfoTraits
> >
> > And getting this error:
> >
> > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > "The transaction manager has disabled its support for remote/network
> > transactions.".
> > Msg 7391, Level 16, State 2, Line 1
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> >
> > Iâ've checked the DTC services per the following link and both DTC services
> > are running under a domain account and configured properly.
> > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> >
> > This executes and returns the recordset:
> > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> >
> > Since the remote stored proc returns the records when executing the stored
> > proc, is it a DTC issue or is this syntax correct ?
> > insert into #StoreInfoTraits exec(@.cmd)
> >
> >
> >|||Hi
The user for the remote server will be determined by how the linked server
was created.
The dtctester error looks like you are not even making it to the remote
server! If you go through Bill's post and check out the networking issues it
may show something. You could also try DTCPing.
John
"brymer28303" wrote:
> Thanks John. I tried this
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
> storeinfo.dbo.pr_TraitAssignments' )
> and got this:
> Msg 7357, Level 16, State 2, Line 1
> Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
> DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
> the object has no columns or the current user does not have permissions on
> that object.
> I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
> and it returns:
> TVA 50 08-30-2006 ACQUISITION 0
> TVA 51 08-30-2006 ACQUISITION 0
> TVA 52 08-30-2006 ACQUISITION 0
> TVA 53 08-30-2006 ACQUISITION 0
> TVA 54 08-30-2006 ACQUISITION 0
> And, we apparently have a dtc issue I found when I ran the dtctester.exe --
> unable to connect.
> "John Bell" wrote:
> > Hi
> >
> > I think Bill's post covered most things in
> > http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> > work OK then dtc itself can be ruled out.
> >
> > Your syntax should work (well my test does on mine) providing dtc is running
> > correctly!
> >
> > If you don't want to start a distributed transaction you could try
> >
> > INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> > date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> > storeinfo.dbo.pr_traitassignments' )
> >
> > DECLARE @.cmd varchar(400)
> > SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> > effective date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> > storeinfo.dbo.pr_traitassignments'' ) '
> >
> > EXEC ( @.cmd )
> >
> > John
> >
> > "brymer28303" wrote:
> >
> > > Hi All,
> > >
> > > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > > server is SQL 2000 Enterprise SP4.
> > >
> > > I am executing this sql in query analyzer:
> > >
> > > create table #StoreInfoTraits (
> > > [record type] varchar(3),
> > > [site id] int,
> > > [current effective date] smalldatetime,
> > > [trait id] varchar(50),
> > > [trait value] int
> > > )
> > > DECLARE @.cmd as varchar(200)
> > > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > > insert into #StoreInfoTraits exec(@.cmd)
> > > DROP TABLE #StoreInfoTraits
> > >
> > > And getting this error:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > > "The transaction manager has disabled its support for remote/network
> > > transactions.".
> > > Msg 7391, Level 16, State 2, Line 1
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> > >
> > > Iâ've checked the DTC services per the following link and both DTC services
> > > are running under a domain account and configured properly.
> > > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> > >
> > > This executes and returns the recordset:
> > > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> > >
> > > Since the remote stored proc returns the records when executing the stored
> > > proc, is it a DTC issue or is this syntax correct ?
> > > insert into #StoreInfoTraits exec(@.cmd)
> > >
> > >
> > >

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.

DTA (Database Tuning Advisor) Malfunction, no error message

Hello, I am having an extremely odd problem with the Database Engine Tuning Advisor, on SQL Server 2005 Enterprise x64. It was working fine, and I was tuning the queries for a bunch of database views we have created. About 2/3 of the way through the list DTA began getting stuck on "Submitting Configuration Information" every time it was run.

I get no error messages, it just sits and spins on Submitting Configuration Information for days and days at a time. When other users view my session, its status says ready as though it had never started, but I am unable to start nor stop it until I close out of it and simply delete it out of the Session Monitor on the left. Other users are able to run sessions without issue, and nothing has changed on the server. I get the same behavior whether I am tuning a query or a workload file. **Edit** A workload file eventually died on the first step with the nebulous "Tuning process exited unexpectedly." There are no entries in dbo.DTA_tuninglog. In dbo.DTA_progress, only TuningStage 0 is present for that SessionID.

I have tried using a different SQL user, rebooting, uninstalling all SQL Server components from my PC then re-installing them, and deleting out all previous DTA session data with no luck. We have also recently installed SP2 for SQL Server 2005 on the server, and I updated my local installation with Microsoft Update. The problem persists. I would appreciate any input you could provide!

Thanks,
Konrad Willmert
Indiana Wesleyan University

I am having the Same Problem, I have no Idea what to do to fix it.

I get this error even after a complete Uninstall (Per the Book) and ReInstallation (Per the Book).

I have tried 3 times.

|||I am having a similar problem. I can get it to start consuming the workload, but it errors out every time. My SQL version is 9.0.3042 so I don't think KB 917131 applies. Was there ever a resolution?|||Shelly,

I have neither found nor received any resolution to this problem--other than asking my co-workers to tune things for me.
|||I gave up as well. I am just using other systems to do the testing.