Dts From Vb

Hi all,
I tryed posting this in VB section but no response, so I'm trying here.
How would I make my DTS connection async. When I exeute SQL DTS package from VB, the program locks up until the DTS is done, so how would I make it async for it to not lock up?
Here is my Sub:
Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

sMessage = sMessage & "<<<Executing import on " & FileName & ">>>" & vbCrLf & vbCrLf

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

sMessage = sMessage & "<<<Import on " & FileName & " is done.>>>" & vbCrLf & vbCrLf

End SubUse a timer to start another thread to run the package then your thread can carry on executing.|||Is there a way to do it without another thread? Like an async call with ADO?

DTS designer

64b SQL2005 EE and all clent tools installed on the same server. Logged as
local admin user Im trying to create a new connection in DTS Designer but
after succesful test of the connection it errors out with
The new connection manager couldnt be created. Additional info : Exeption
from HRESULT 0xC0010014
Do you know if the client tools are installed by default in x86 Program
files folder and is it possible uninstalling and reinstalling in Program
files will fix the issue?
Any idea whats the problem and how to fix it ?
In our case the problem was SP1 unfinished /unsuccesful instalation. After
sql2005 setup REBOOT and when is up stop all sql services from Control panel
and then install SP1. Ensure you dont get open files errors if so close the
guilty apps and then continue and if so reapply the SP1 once again.
"Ellie" wrote:

> 64b SQL2005 EE and all clent tools installed on the same server. Logged as
> local admin user Im trying to create a new connection in DTS Designer but
> after succesful test of the connection it errors out with
> The new connection manager couldnt be created. Additional info : Exeption
> from HRESULT 0xC0010014
> Do you know if the client tools are installed by default in x86 Program
> files folder and is it possible uninstalling and reinstalling in Program
> files will fix the issue?
> Any idea whats the problem and how to fix it ?
>

DTS connection to Oracle 8; To be upgrated to Oracle 9i. Any guida

I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
and I was wondering what I have to do to keep this all running. I think I
have to upgrade the Oracle client on the Sql Server box to 9i (since the
Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
of anything else I have to do.
Anyone done this before?
Thanks,
Michael
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael
|||You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael
|||Also, keep in mind that Oracle's new DATETIME data type has "known" issues
with respect to SQL Server.
Do a search on the net for issues and the workarounds.
Sincerely,
Anthony Thomas

"Amrith" <Amrith@.discussions.microsoft.com> wrote in message
news:5C126AC9-DF98-4B2A-8F8A-E5D887A502AB@.microsoft.com...
You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i
soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not
aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael

DTS connection to Oracle 8; To be upgrated to Oracle 9i. Any guida

I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
and I was wondering what I have to do to keep this all running. I think I
have to upgrade the Oracle client on the Sql Server box to 9i (since the
Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
of anything else I have to do.
Anyone done this before?
Thanks,
MichaelYou should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael|||"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael|||Also, keep in mind that Oracle's new DATETIME data type has "known" issues
with respect to SQL Server.
Do a search on the net for issues and the workarounds.
Sincerely,
Anthony Thomas
"Amrith" <Amrith@.discussions.microsoft.com> wrote in message
news:5C126AC9-DF98-4B2A-8F8A-E5D887A502AB@.microsoft.com...
You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i
soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not
aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael

DTS Connection Server

Hi everyone:

I'm making aDTS for SQL Server 2000 and I need to know if there is a way to set the Connection taskServer property to something like'local' or something like it so I don't have to change the Server property everytime I Install and run the DTS on a different server than the one where I built it.

Thanks.

To refer to local default SQL instance, you can use either '.' or '[local]; if you want to refer to named instance, you should add the instance name: '.\MyInstance'

|||Thanks for the reply Lori_Jay, but either '.' or '[local]' seem to work in the DTS Server Connection. To make it clearer I wrote '.' and '[local]' (without the quotes) in the Server TextBox (also a dropdown) and it didn't work cause the server was not found. It is only working with the exact name of the DataBase server.|||'.' or '[local]' that used in DTS Server Connection can only refers to SQL2000default instace. If you move the DTS package to a SQL2005 default instance, the connection will fail because the connection Provider used by the old DTS can not access SQL2005. In this case you can change the Data Source type to 'SQL Native Client' then try again.

DTS connection

Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?

Thanks in advanceHi Eisa,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ashsql

DTS - On Failure send mail does not work

Hi

Task A is a Execute Package task, and Task B is as Send Mail task. They are connected with a 'On Failure' connection.

The problem is that when task A fails, task B is not run. Why?

I have enabled and disabled the 'Fail package on step failure' for task A and it's 'child' in every way possible. I allowed transactions, and then tried again with them of...

Suggestions?

Thanks in advance.
__________________
Joo MartoHave you verified that the event is in fact failing ? Create an on success flow with the same step and see what happens.|||With on success it works... but the log status of the task is failled!

How can that be?|||does this happen when ur failure step is the mail task? or it doesnt matter?|||Check if you have On Success report Failure.|||Try making Task B an activeX script or sumthin else and c if that works...

also check in ur execute package task when the package fails ie, how many steps in the package need to fail for the package task to fail...i think the default is 4 steps but u shud check what it is for ur package|||Busy day - sorry for the delay. Modify the package's property to "fail package on first error" - I am referring to the package that you are executing in execute package.|||Originally posted by rnealejr
Busy day - sorry for the delay. Modify the package's property to "fail package on first error" - I am referring to the package that you are executing in execute package.

Thankyou very, very, much

DTS - How to make ActiveX script open a connection in the diagram?

I have a simple question but I'm having a really hard time finding an answer.

Let's say I'm designing a DTS package, and I have a connection to a SQL Server called ConnSQL. How do I reference this connection in my ActiveX script? (so I can run SQL queries and such)

ThanksThere is no way to reference such connections. This is because you can only reference COM objects from VBScript, and the DTS package is not a COM object, but itself a script. Scripting is included to allow you to access objects in the operating system environment.

If you want to run SQL queries against a DTS connection, you want to use a SQL Query task, which operates against whatevery connection you attach it to.

If your goal is moving data between files and/or tables, you want to use a transformation task between two DTS connections (select the source, holding SHIFT, select the second, then click the Transformation Task button on the toolbar.

Dts

Hi,
I have a dts package that I created which uses an odbc connection to go and
copy a dbf file using visual fox pro drivers to a sql server database. The p
ackage runs fine when I execute it manually. But whenever I try to use the s
ql server agent(schedule) to run it as a job, it doesn't work. I realy dont
know what to do...
Thanks for the helpCan anyone help me ?

dtctest fails and nobody here has any clue why

Hi,
My machine is unable to establish a connection for the purposes of
making distributed transactions. The problem appears to be that my
machine is blocking the machine to which I am trying to connect from
establishing RPC Binding with my machine. Here's a message from my
Event Viewer:
MS DTC is unable to communicate with MS DTC on a remote system. MS
DTC on the primary system established an RPC binding with MS DTC on
the secondary system. However, the secondary system did not create
the reverse RPC binding to the primary MS DTC system before the
timeout period expired. Please ensure that there is network
connectivity between the two systems. Error
Specifics:d:\nt_qxp\com\com1x\dtc\dtc\cm
\src\cso.cpp:1977, CmdLine:
C:\WINNT\System32\msdtc.exe, Pid: 1300
No Callstack (0)
What's curious is that my PC is the ONLY one in our network that has
this problem.
I've run dtctester againat various machines using various DSNs and
ALWAYS get the following:
Error:
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL
Server Driver]Distributed transaction error'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
Driver]Invalid cursor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
To my knowledge, my PC is the only one here (out of hundreds) that
flunks the dtctester.
From all appearances, my PC appears to be configured identically to
those of many other people here who are not experiencing any problems.
Specifically, I've checked all MSDTC registry settings of which I've
read, I've been into Component Services and checked the properties of
anything msdtc related, and I've ensured that the services I need are
actually running. I'm running XP, but I have the firewall turned off,
and have never installed any third party firewall. My network
connectivity is ok. I can see the target machine, and can even access
the database to which I am trying to connect and do whatever I want,
so long as I don't use distributed transactions. The version of msdtc
that I am using is identical to those of everyone else around here.
The latest advice I'm getting is to reinstall XP and hope for the
best. Seems pretty drastic to me, especially seeing how it's no
guarantee! Does anyone out there have any idea what could be causing
this? General consensus is that it is some stupid check box somewhere,
but we've pretty much run out of ideas where to look.
-Dave
I have read dozens of emails and solutions to this and similar issues
and tried them all, and nothing seems to work.Dave, I am having the same problem here. Have you found a resolution to
this. Just curious.
Cory
ccundy
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message724716.html

dtctest fails and nobody here has any clue why

Hi,
My machine is unable to establish a connection for the purposes of
making distributed transactions. The problem appears to be that my
machine is blocking the machine to which I am trying to connect from
establishing RPC Binding with my machine. Here's a message from my
Event Viewer:
MS DTC is unable to communicate with MS DTC on a remote system. MS
DTC on the primary system established an RPC binding with MS DTC on
the secondary system. However, the secondary system did not create
the reverse RPC binding to the primary MS DTC system before the
timeout period expired. Please ensure that there is network
connectivity between the two systems. Error
Specifics:d:\nt_qxp\com\com1x\dtc\dtc\cm\src\cso.c pp:1977, CmdLine:
C:\WINNT\System32\msdtc.exe, Pid: 1300
No Callstack (0)
What's curious is that my PC is the ONLY one in our network that has
this problem.
I've run dtctester againat various machines using various DSNs and
ALWAYS get the following:
Error:
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL
Server Driver]Distributed transaction error'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
Driver]Invalid cursor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
To my knowledge, my PC is the only one here (out of hundreds) that
flunks the dtctester.
From all appearances, my PC appears to be configured identically to
those of many other people here who are not experiencing any problems.
Specifically, I've checked all MSDTC registry settings of which I've
read, I've been into Component Services and checked the properties of
anything msdtc related, and I've ensured that the services I need are
actually running. I'm running XP, but I have the firewall turned off,
and have never installed any third party firewall. My network
connectivity is ok. I can see the target machine, and can even access
the database to which I am trying to connect and do whatever I want,
so long as I don't use distributed transactions. The version of msdtc
that I am using is identical to those of everyone else around here.
The latest advice I'm getting is to reinstall XP and hope for the
best. Seems pretty drastic to me, especially seeing how it's no
guarantee! Does anyone out there have any idea what could be causing
this? General consensus is that it is some stupid check box somewhere,
but we've pretty much run out of ideas where to look.
-Dave
I have read dozens of emails and solutions to this and similar issues
and tried them all, and nothing seems to work.
Dave, I am having the same problem here. Have you found a resolution to this. Just curious.
Cory

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

DTA (Database Engine Tuning Advisor) error when opening connection

Hi all,

I've got some problem to launch DTA, after the connection, I've got this message :

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.connectButton_Click(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

Any ideas ?

Information : SQL Server 2005 beta 2 Enterprise edition CTP June 2005

Thanks for your futurse answers.

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

|||

Manojas wrote:

Hello Herve
This was a known problem in Beta 2 Yukon bits. We have addressed this in the later bits of Yukon (Post Beta 2) -
Thanks
Manoj

I am using the evaluation copy of SQL2005 that I downloaded yesterday and having the same or similar problem. I can use the Management Studio to connect to the database without any problem.

The following is the details of the error:

===================================

Failed to open a new connection.

===================================

Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)

Program Location:

at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)
at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object sender, EventArgs e)

===================================

Failed to establish a connection to the server.

DSV in SSIS

Hi,
How can we make use of DSV in SSIS?

In the connection manager of a package i tried to add a link to it but couldn't.

I believe it has an importance since it is there. Could anyone tell me what is its benefits and how it could be implemented?

Thanks

Christina

A DSV allows you to create a metadata layer over an existing Data Source. Within a DSV, you can select a subset (or all) of the tables and views available from a Data Source, rename any of the tables/views and/or their columns, add new Named Calculations (which act like computed columns within a table/view), and add new Named Queries (which are complete select statements, which basically act like views).

To use a DSV within SSIS, you still create a Connection Manager that points to the Data Source that the DSV is based on. Then, when you select the Connection Manager, you should have the option to select the DSV that is based on it. For example, if you have a Data Source based on an OLE DB provider connecting to SQL Server, you could create a DSV based on the database that the Data Source is connecting to. Then, within a Data Flow task, if you add an OLE DB Source adapter, when you select the Connection Manager based on your OLE DB data source, you should be able to also select the DSV.

HTH,

Dave Fackler

|||

Thanks Dave.

Actually this is what I read about it in the books online. However when I come to add a data source in the connection manager i don't see the DSV among the possible connections.

Have you tried it? I could have missed something.

Thanks again

Christina

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube?
> RemoteRepositoryConnectionString
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube
?> RemoteRepositoryConnectionString[vbcol=s
eagreen]
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?[/vbc
ol]
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:
>

DSNLess connection with Failover to a SQL Server 2005 cluster

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

When you configure Mirroring without a witness, failover is not automatic. You must manually failover the database - you do this via alter database or using the button provided in the database mirroring properties.

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

DSNLess connection with Failover on SQL Server 2005 Cluster ?

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

Failover Partner is used in the case of database mirroring, not for the case of cluster. Check here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Cluster is transparent to client, i.e., client should be able to connect to the server without knowing the server is a cluster or not. In cluster case, use "server=your_virtual_server_name" in the connection string. Please check my blog at http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx and let us know if you have further problem.

DSN-less connection to SQL Server with sql 7 and sql 2k

Hi,
I have commercial asp webspace hosting an application that connects to
an SQL machine on my local network. THe machine has both SQL 7 and SQL
2k running on it. Webspace and SQL are both running on Win2k.
The connection string I am using is as follows:
Application("Application_ConnectionString") = "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=aaa.bbb.ccc.ddd,1433;" & _
"Initial Catalog=DBName;" & _
"User ID=xxx;" & _
"Password=yyy;"
It works fine when pointing to a database in the sql 7 instance. But
what I want to achieve is to connect to a database in the sql 2k
instance. Running locally, I can achieve this by substituting
aaa.bbb.ccc.ddd\servername_sql2k. But I cannot get it to connect to
the sql 2k instance from the commercial webspace. I get the following
message in this case:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBMSSOCN]General network error. Check your network documentation.
SO, my question is, how do you address a database in the sql 2k
instance via a dsn-less connection? Do you have to set SQL 7 and SQL
2k to listen on different ports? I have tried this but have not met
with success.
Any help gratefully received...
Thanks in advanceCan you make a connection from a DSN from the commercial webspace using
aaa.bbb.ccc.ddd\servername_sql2k as the server name?
Also can you connect via Query Analyzer form the commercial webspace using
that servername?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand
Thanks for your reply. Restricted access to the commercial web space
means that I DSN cannot be established, hence the DSN less connection.
For that reason I cannot try your suggestion of connecting Query
Analyser from that server. However, I have ascertained that both
instances of SQL Server can be reached from an external server by
connecting from a server where where I do have SQL and OS level
access.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<A1F7CV6gEHA.480@.cpms
ftngxa06.phx.gbl>...
> Can you make a connection from a DSN from the commercial webspace using
> aaa.bbb.ccc.ddd\servername_sql2k as the server name?
> Also can you connect via Query Analyzer form the commercial webspace using
> that servername?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.