Thursday, March 22, 2012
connecting to server outside network
ISP. I'm using the dts wizard. For the source, I put in the IP address of our
ih-house server. When I click "refresh" to get a list of the databases I get
this error:
Error source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Context: Error during intialiaztion of the provider.
I am able to ping the in-house sever from the ISP server. I can log in to
the in-house using query analyzer and the login and password that I'm putting
in the dts wizard.
Any idea what might be wrong?
Thanks,
Dan D.
Problem solved. It was a setting in our firewall.
Dan D.
"Dan D." wrote:
> I'm trying to move some files from our in-house server to a server at our
> ISP. I'm using the dts wizard. For the source, I put in the IP address of our
> ih-house server. When I click "refresh" to get a list of the databases I get
> this error:
> Error source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Context: Error during intialiaztion of the provider.
> I am able to ping the in-house sever from the ISP server. I can log in to
> the in-house using query analyzer and the login and password that I'm putting
> in the dts wizard.
> Any idea what might be wrong?
> Thanks,
> --
> Dan D.
connecting to server outside network
ISP. I'm using the dts wizard. For the source, I put in the IP address of our
ih-house server. When I click "refresh" to get a list of the databases I get
this error:
Error source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Context: Error during intialiaztion of the provider.
I am able to ping the in-house sever from the ISP server. I can log in to
the in-house using query analyzer and the login and password that I'm putting
in the dts wizard.
Any idea what might be wrong?
Thanks,
--
Dan D.Problem solved. It was a setting in our firewall.
--
Dan D.
"Dan D." wrote:
> I'm trying to move some files from our in-house server to a server at our
> ISP. I'm using the dts wizard. For the source, I put in the IP address of our
> ih-house server. When I click "refresh" to get a list of the databases I get
> this error:
> Error source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Context: Error during intialiaztion of the provider.
> I am able to ping the in-house sever from the ISP server. I can log in to
> the in-house using query analyzer and the login and password that I'm putting
> in the dts wizard.
> Any idea what might be wrong?
> Thanks,
> --
> Dan D.
connecting to server outside network
ISP. I'm using the dts wizard. For the source, I put in the IP address of ou
r
ih-house server. When I click "refresh" to get a list of the databases I get
this error:
Error source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does
not exist or access denied.
Context: Error during intialiaztion of the provider.
I am able to ping the in-house sever from the ISP server. I can log in to
the in-house using query analyzer and the login and password that I'm puttin
g
in the dts wizard.
Any idea what might be wrong?
Thanks,
--
Dan D.Problem solved. It was a setting in our firewall.
--
Dan D.
"Dan D." wrote:
> I'm trying to move some files from our in-house server to a server at our
> ISP. I'm using the dts wizard. For the source, I put in the IP address of
our
> ih-house server. When I click "refresh" to get a list of the databases I g
et
> this error:
> Error source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serv
er does
> not exist or access denied.
> Context: Error during intialiaztion of the provider.
> I am able to ping the in-house sever from the ISP server. I can log in to
> the in-house using query analyzer and the login and password that I'm putt
ing
> in the dts wizard.
> Any idea what might be wrong?
> Thanks,
> --
> Dan D.
Tuesday, March 20, 2012
Connecting to Oracle RDB from SSIS
Hi Gurus,
I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.
Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.
Provider is unable to funtion until these components are installed.
I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.
Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)
Thanks,
SK
sk3636 wrote:
Hi Gurus,
I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.
Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.
Provider is unable to funtion until these components are installed.
I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.
Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)
Thanks,
SK
I think you should use the ADO.Net connection manager to connect to ODBC sources.
-Jamie
|||There is an existing post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640502&SiteID=1
which could help you out.
Overall, first, you need to install Oracle client. Second, you need to configure tnsnames.ora file.
Regards,
Yitzhak
Hi,
I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.
The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Thanks,
SK
sk3636 wrote:
Hi,
I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.
The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Thanks,
SK
So when you said "SSIS is not letting me change the data type on the output column" that's not actually true is it? It HAS let you change it and at execution-time you are now getting these errors.
The error is pretty self explanatory. You are trying to push a value into a column in the pipeline that is not wide enough for it. Hence, you should widen the column.
-Jamie
|||Hi Jamie,
It HAS let you change it and at execution-time you are now getting these errors.
Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.
Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.
Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Thanks,
SK
sk3636 wrote:
Hi Jamie,
It HAS let you change it and at execution-time you are now getting these errors.
Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.
Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.
Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)Thanks,
SK
Oh OK, sorry. In that case you will have to use a Data Conversion component or Derived Column component to convert to the datatype that you wish to convert to.
-Jamie
|||I did try to use the Data conversion transformation and I got the same error. The error is coming at the output column from the data source reader. So the data flow component didn't traverse upto Data Conversion component to check whether the conversion is taking place or not.
I found similiar threads related to the same issue that I am facing.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=392224&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278744&SiteID=1
SK
|||Have you or anyone else found a resolution for this? I'm experiencing the same problem.Connecting to Oracle RDB from SSIS
Hi Gurus,
I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.
Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.
Provider is unable to funtion until these components are installed.
I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.
Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)
Thanks,
SK
sk3636 wrote:
Hi Gurus,
I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.
Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.
Provider is unable to funtion until these components are installed.
I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.
Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)
Thanks,
SK
I think you should use the ADO.Net connection manager to connect to ODBC sources.
-Jamie
|||
There is an existing post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640502&SiteID=1
which could help you out.
Overall, first, you need to install Oracle client. Second, you need to configure tnsnames.ora file.
Regards,
Yitzhak
Hi,
I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.
The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Thanks,
SK
sk3636 wrote:
Hi,
I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.
The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Thanks,
SK
So when you said "SSIS is not letting me change the data type on the output column" that's not actually true is it? It HAS let you change it and at execution-time you are now getting these errors.
The error is pretty self explanatory. You are trying to push a value into a column in the pipeline that is not wide enough for it. Hence, you should widen the column.
-Jamie
|||
Hi Jamie,
It HAS let you change it and at execution-time you are now getting these errors.
Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.
Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.
Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Thanks,
SK
sk3636 wrote:
Hi Jamie,
It HAS let you change it and at execution-time you are now getting these errors.
Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.
Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.
Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)Thanks,
SK
Oh OK, sorry. In that case you will have to use a Data Conversion component or Derived Column component to convert to the datatype that you wish to convert to.
-Jamie
|||
I did try to use the Data conversion transformation and I got the same error. The error is coming at the output column from the data source reader. So the data flow component didn't traverse upto Data Conversion component to check whether the conversion is taking place or not.
I found similiar threads related to the same issue that I am facing.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=392224&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278744&SiteID=1
SK
|||Have you or anyone else found a resolution for this? I'm experiencing the same problem.Sunday, March 11, 2012
Connecting to external data source
work. I need to connect to an external data source. I have a user DSN named
"OMD" which does not require a username or password. My connection will be
read-only. My intent is to use SQLConnect, although I am open to any
suggestions. I have tried various methods of connecting to Access databases,
figuring that would be easier and then I could connect to my OMD source, but
I have not been able to connect to any other data source. Any words of
encouragement would be appreciated.
How exactly are you trying to connect to external data sources? Linked
servers? Openrowset? Opendatasource? Openquery?
What steps did you take to set this up with SQL Server?
What errors are you getting? What specific types of data sources are
you trying to connect to - e.g. Access, FoxPro, Oracle, Sybase, etc?
I'm not real clear on what it is you are trying to connect to from
where and what the errors are.
-Sue
On Tue, 19 Oct 2004 09:29:12 -0700, Phil T
<PhilT@.discussions.microsoft.com> wrote:
>I have found several pages demonstrating this but I still cannot make it
>work. I need to connect to an external data source. I have a user DSN named
>"OMD" which does not require a username or password. My connection will be
>read-only. My intent is to use SQLConnect, although I am open to any
>suggestions. I have tried various methods of connecting to Access databases,
>figuring that would be easier and then I could connect to my OMD source, but
>I have not been able to connect to any other data source. Any words of
>encouragement would be appreciated.
|||I am trying to connect the the source any way I can.
I have tried Openrowset, openquery, sp_addlinkedserver, sqlconnect,
sqldriverconnect.
I have an ODBC connection named OMD that connects using the Transoft OMD
driver. (Although I have not successfully connected to any type of dtabase;
not even other SQL servers)
It seems to me that sqlconnect is the easiest way to connect. Since there
is no username or password required by the ODBC driver, I thought simply using
declare @.mycode int
@.mycode=sqlconnect(dsn='OMD')
should work. It does not. I would like the proper syntax required.
Somehow I am misinterpreting the examples in the Help files. Please excuse
my ignorance.
Since the code will not compile, I am not getting to the point of errors.
Thanks for replying.
Phil
"Sue Hoegemeier" wrote:
> How exactly are you trying to connect to external data sources? Linked
> servers? Openrowset? Opendatasource? Openquery?
> What steps did you take to set this up with SQL Server?
> What errors are you getting? What specific types of data sources are
> you trying to connect to - e.g. Access, FoxPro, Oracle, Sybase, etc?
> I'm not real clear on what it is you are trying to connect to from
> where and what the errors are.
> -Sue
> On Tue, 19 Oct 2004 09:29:12 -0700, Phil T
> <PhilT@.discussions.microsoft.com> wrote:
>
>
|||If your code won't compile then your first issue is more of
an issue with whatever language you are using and how to
code data access routines. There is no one generic response
on how to do this with any data source and in any language.
In any case, are you trying to code this using the ODBC
APIs? If so, you need to use SQLConnect("DSN-Name") .
It would probably be easier to help you out if you could
post what language you are trying to do this in, what syntax
errors you receive, what the code is that is failing and
what examples you are trying to follow.
-Sue
On Mon, 1 Nov 2004 09:44:05 -0800, Phil T
<PhilT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am trying to connect the the source any way I can.
>I have tried Openrowset, openquery, sp_addlinkedserver, sqlconnect,
>sqldriverconnect.
>I have an ODBC connection named OMD that connects using the Transoft OMD
>driver. (Although I have not successfully connected to any type of dtabase;
>not even other SQL servers)
>It seems to me that sqlconnect is the easiest way to connect. Since there
>is no username or password required by the ODBC driver, I thought simply using
>declare @.mycode int
>@.mycode=sqlconnect(dsn='OMD')
>should work. It does not. I would like the proper syntax required.
>Somehow I am misinterpreting the examples in the Help files. Please excuse
>my ignorance.
>Since the code will not compile, I am not getting to the point of errors.
>Thanks for replying.
>Phil
>"Sue Hoegemeier" wrote:
Connecting to external data source
work. I need to connect to an external data source. I have a user DSN name
d
"OMD" which does not require a username or password. My connection will be
read-only. My intent is to use SQLConnect, although I am open to any
suggestions. I have tried various methods of connecting to Access databases
,
figuring that would be easier and then I could connect to my OMD source, but
I have not been able to connect to any other data source. Any words of
encouragement would be appreciated.How exactly are you trying to connect to external data sources? Linked
servers? Openrowset? Opendatasource? Openquery?
What steps did you take to set this up with SQL Server?
What errors are you getting? What specific types of data sources are
you trying to connect to - e.g. Access, FoxPro, Oracle, Sybase, etc?
I'm not real clear on what it is you are trying to connect to from
where and what the errors are.
-Sue
On Tue, 19 Oct 2004 09:29:12 -0700, Phil T
<PhilT@.discussions.microsoft.com> wrote:
>I have found several pages demonstrating this but I still cannot make it
>work. I need to connect to an external data source. I have a user DSN nam
ed
>"OMD" which does not require a username or password. My connection will be
>read-only. My intent is to use SQLConnect, although I am open to any
>suggestions. I have tried various methods of connecting to Access database
s,
>figuring that would be easier and then I could connect to my OMD source, bu
t
>I have not been able to connect to any other data source. Any words of
>encouragement would be appreciated.|||I am trying to connect the the source any way I can.
I have tried Openrowset, openquery, sp_addlinkedserver, sqlconnect,
sqldriverconnect.
I have an ODBC connection named OMD that connects using the Transoft OMD
driver. (Although I have not successfully connected to any type of dtabase;
not even other SQL servers)
It seems to me that sqlconnect is the easiest way to connect. Since there
is no username or password required by the ODBC driver, I thought simply usi
ng
declare @.mycode int
@.mycode=sqlconnect(dsn='OMD')
should work. It does not. I would like the proper syntax required.
Somehow I am misinterpreting the examples in the Help files. Please excuse
my ignorance.
Since the code will not compile, I am not getting to the point of errors.
Thanks for replying.
Phil
"Sue Hoegemeier" wrote:
> How exactly are you trying to connect to external data sources? Linked
> servers? Openrowset? Opendatasource? Openquery?
> What steps did you take to set this up with SQL Server?
> What errors are you getting? What specific types of data sources are
> you trying to connect to - e.g. Access, FoxPro, Oracle, Sybase, etc?
> I'm not real clear on what it is you are trying to connect to from
> where and what the errors are.
> -Sue
> On Tue, 19 Oct 2004 09:29:12 -0700, Phil T
> <PhilT@.discussions.microsoft.com> wrote:
>
>|||If your code won't compile then your first issue is more of
an issue with whatever language you are using and how to
code data access routines. There is no one generic response
on how to do this with any data source and in any language.
In any case, are you trying to code this using the ODBC
APIs? If so, you need to use SQLConnect("DSN-Name") .
It would probably be easier to help you out if you could
post what language you are trying to do this in, what syntax
errors you receive, what the code is that is failing and
what examples you are trying to follow.
-Sue
On Mon, 1 Nov 2004 09:44:05 -0800, Phil T
<PhilT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am trying to connect the the source any way I can.
>I have tried Openrowset, openquery, sp_addlinkedserver, sqlconnect,
>sqldriverconnect.
>I have an ODBC connection named OMD that connects using the Transoft OMD
>driver. (Although I have not successfully connected to any type of dtabase
;
>not even other SQL servers)
>It seems to me that sqlconnect is the easiest way to connect. Since there
>is no username or password required by the ODBC driver, I thought simply us
ing
>declare @.mycode int
>@.mycode=sqlconnect(dsn='OMD')
>should work. It does not. I would like the proper syntax required.
>Somehow I am misinterpreting the examples in the Help files. Please excuse
>my ignorance.
>Since the code will not compile, I am not getting to the point of errors.
>Thanks for replying.
>Phil
>"Sue Hoegemeier" wrote:
>
connecting to database in runtime using visual basic 6
i design a crystal report outside visual basic 6. what i did is just calling the crystal report filename. how can i change the source database of my crystal reports in runtime. im using microsoft sql server. the two database had the same table structure.
thanksWhich version of CR are you using?
If it is CR9 then
cr.Database.LogOnServer "p2ssql.dll", "Server", "Database", "UserName,"Password"
connecting to database error
Hi there
i have a problem connecting creating a data source to SQL server express.
I am running winXP with service pack2
My machine is APOLLO\SQLEXPRESS
i AM USING AN ODBC CONNECTION.
i GET THE FOLLOWING ERRORS:
SQL SERVER ERROR 1231
AND
SQL SERVER ERROR 17.
Having done a google search and looked at the local security settings i am not sure what to add to the object names.
Can anybody please help.Gino
Gino,
You need to add authentication to your connection string. An application has it's own identity that SQL Server will not recognize.
Adamus
|||Hi there again
Could you please state step by step procedure to perform this authentication. Is this authentication code based or windows based as i have mentioned previously? If this is code based could you please give some hints.
Gino
Connecting to data source rs2005sbsDW
If you installed SQLExpress as the server then it probably installed as a named instance so you need ot connect to (local)\sqlexpress|||SQL Express Server is installed. But I have recently installed the Business Intelligence SQL 2005 program. I have reinstalled the workstation components for the Business Intelligence Install. I'm not sure how to proceed with your advice.
|||How do I connect to (local)\sqlexpress? If this is what I need to do.
|||Put (local)\sqlexpress in the connection string or the server name in the connection dialog.
connecting to data source issue
http://localhost/reports. I can connect from the computer I developed the
reports but when I goto another computer it gives me this error message
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection)
I've created the same exact odbc connection in the systems tab as on the
computer I've created the project with. I've looked at there online help
but it didn't have any suggestions.
Is there anything else I need to do on the other computer in order for this
to work?
TIA,
JacksonHi Jackson
I am having the exact same problem. Did you find a solution? Should I find
one I will post it here.
Tina - MSFTE
"Jackson" wrote:
> I'm having a problem connecting to an access database from
> http://localhost/reports. I can connect from the computer I developed the
> reports but when I goto another computer it gives me this error message
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection)
> I've created the same exact odbc connection in the systems tab as on the
> computer I've created the project with. I've looked at there online help
> but it didn't have any suggestions.
> Is there anything else I need to do on the other computer in order for this
> to work?
>
> TIA,
> Jackson
>
>|||Jackson and Tina
I am having the same problem and have posted everywhere trying to find a
fix. This supposedly works. I have turned it over to the Network Mgr and he
is reviewing it. Hopefully, it will solve the problem. If it works, please
let me know or, if you find another solution I wuld really appreciate hearing
about it.
Roy
1. Kerberos is enabled
2. The servers are trusted for delegation
3. The SPN's are configured
"http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp"
4. If you have several domain controllers wait for replication to complete.
If you don't wait you will pull your hair out because nothing you try will
works and all of sudden a few hours latter it starts working and you can't
figure out why.
5. Make sure all users have the option 'Enable Integrated Windows
Authentication' enabled in internet explorer, without it they will still get
the authentication error.
"Jackson" wrote:
> I'm having a problem connecting to an access database from
> http://localhost/reports. I can connect from the computer I developed the
> reports but when I goto another computer it gives me this error message
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection)
> I've created the same exact odbc connection in the systems tab as on the
> computer I've created the project with. I've looked at there online help
> but it didn't have any suggestions.
> Is there anything else I need to do on the other computer in order for this
> to work?
>
> TIA,
> Jackson
>
>|||It took a while but I finally got a work around.
I'm currently taking daily snapshots of the access reports. So now I don't
even need to create odbc connections on other user computers.
"Tina (MSFTE)" <Tina (MSFTE)@.discussions.microsoft.com> wrote in message
news:212D3E5F-C8D8-412F-9493-EBB9EACADA1A@.microsoft.com...
> Hi Jackson
> I am having the exact same problem. Did you find a solution? Should I find
> one I will post it here.
> Tina - MSFTE
> "Jackson" wrote:
>> I'm having a problem connecting to an access database from
>> http://localhost/reports. I can connect from the computer I developed
>> the
>> reports but when I goto another computer it gives me this error message
>> An error has occurred during report processing. (rsProcessingAborted)
>> Cannot create a connection to data source 'DataSource1'.
>> (rsErrorOpeningConnection)
>> I've created the same exact odbc connection in the systems tab as on the
>> computer I've created the project with. I've looked at there online help
>> but it didn't have any suggestions.
>> Is there anything else I need to do on the other computer in order for
>> this
>> to work?
>>
>> TIA,
>> Jackson
>>|||Roy,
Your post put me on the right track. The IIS server we deployed to is not in
a domain, neither was any of the users. I did not even think of looking for
this scenario, I assumed we were on the domain.
1) I requested a user to be created on the domain.
2) I gave this user Data reader rights on all the SQL and AS servers that
forms part of my solution
3) In RS I used the full username and password to be saved securely in RS
4) Selected "Use as WIS credentials to make the connection"
5) Selected "Impersonate user after connection created"
It works like a charm. Due to the client's unique network configuration -
servers on the network, but not in the domain - this is the only way the
solution will work.
"Roy" wrote:
> Jackson and Tina
> I am having the same problem and have posted everywhere trying to find a
> fix. This supposedly works. I have turned it over to the Network Mgr and he
> is reviewing it. Hopefully, it will solve the problem. If it works, please
> let me know or, if you find another solution I wuld really appreciate hearing
> about it.
> Roy
> 1. Kerberos is enabled
> 2. The servers are trusted for delegation
> 3. The SPN's are configured
> "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp"
> 4. If you have several domain controllers wait for replication to complete.
> If you don't wait you will pull your hair out because nothing you try will
> works and all of sudden a few hours latter it starts working and you can't
> figure out why.
> 5. Make sure all users have the option 'Enable Integrated Windows
> Authentication' enabled in internet explorer, without it they will still get
> the authentication error.
> "Jackson" wrote:
> > I'm having a problem connecting to an access database from
> > http://localhost/reports. I can connect from the computer I developed the
> > reports but when I goto another computer it gives me this error message
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> >
> > Cannot create a connection to data source 'DataSource1'.
> > (rsErrorOpeningConnection)
> >
> > I've created the same exact odbc connection in the systems tab as on the
> > computer I've created the project with. I've looked at there online help
> > but it didn't have any suggestions.
> >
> > Is there anything else I need to do on the other computer in order for this
> > to work?
> >
> >
> > TIA,
> >
> > Jackson
> >
> >
> >|||I ran into the same issue. I have modified security to report howeve
I noticed that it is using the users credentials who is accessing th
report to initiate Oracle connection. It looks like the use
requires elevated permissions on C:\Program Files\Oracle and possibl
other directories
Still investigating...
Rya|||If you creaye a secure use in RSthat user can have the elevated priviledges
and not your real life users - better from a security point of view - that is
why I selected the options I did. See if that works to Oracle as well.
Cioa
Tina
"rjohnstone" wrote:
> I ran into the same issue. I have modified security to report however
> I noticed that it is using the users credentials who is accessing the
> report to initiate Oracle connection. It looks like the user
> requires elevated permissions on C:\Program Files\Oracle and possibly
> other directories.
> Still investigating...
> Ryan
>
Thursday, March 8, 2012
Connecting to Analysis Services cubes from Reporting Services
I have created cubes using Analysis Services. Could I please know how to create a data source to connect to those cubes in Reporting Services to generate reports out of them. When I choose to create a data source in Reporting Services, I am able to see options only to connect to Sql Server, ODBC and Oracle. I do not see an option like 'OLAP Services' to connect to cubes in Analysis Services. I would be happy if anyone could help by giving me the steps to connect to the cubes in Analysis Services and to generate report/pivot table out of it.
Thanks,
Raj.Information on the integration of Reporting Services (RS) 2000 and Analysis
Services (AS) 2000 is provided at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
It describes how to connect to AS 2000 cubes and use multidimensional data
in RS 2000. The data provider is OleDB provider for OLAP 8.0.
You might also want to check out some samples on parameterized MDX available
for download:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
Note: The upcoming SQL 2005 Beta 2 will contain graphical MDX and DMX query
designers, which will work best against AS 2005 cubes, but they also support
AS 2000 cubes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:66825BE3-5342-4C34-AD06-589B73491B01@.microsoft.com...
> Hi,
> I have created cubes using Analysis Services. Could I please know how to
create a data source to connect to those cubes in Reporting Services to
generate reports out of them. When I choose to create a data source in
Reporting Services, I am able to see options only to connect to Sql Server,
ODBC and Oracle. I do not see an option like 'OLAP Services' to connect to
cubes in Analysis Services. I would be happy if anyone could help by giving
me the steps to connect to the cubes in Analysis Services and to generate
report/pivot table out of it.
> Thanks,
> Raj.
Connecting to an Access Database
I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?
Hi,
Create a system ODBC data source for your access db from Control Panel Administrative tools.
Create a shared ODBC data souce in RS and select the data source you just created in Control panel.
After that it is business as usual. You may have to finetune some quirks for deployment, the host server should have the ODBC data source as well. An idea would be to try to use an UNC when defining the ODBC datasource and post your access db in a share in some server. That would give more portability.
Philippe
Connecting to an Access Database
I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?
Hi,
Create a system ODBC data source for your access db from Control Panel Administrative tools.
Create a shared ODBC data souce in RS and select the data source you just created in Control panel.
After that it is business as usual. You may have to finetune some quirks for deployment, the host server should have the ODBC data source as well. An idea would be to try to use an UNC when defining the ODBC datasource and post your access db in a share in some server. That would give more portability.
Philippe
Wednesday, March 7, 2012
Connecting to a remote data source.
Hi all,
I have deployed a report onto the Report Server on a remote machine.
When i call the report an error is displayed as follows:
An error has occurred during report processing.
Are you using integrated security for your data source? Most likely the issue is double-hop.
http://blogs.msdn.com/jgalla/archive/2006/03/16/553314.aspx
Connecting to a Datacom DB data source
a number of disparate data sources -- except a CA Datacom/DB
(mainframe) data source.
Any ideas on how to connect to the mainframe data so that it has the
look and feel of an ODBC database? And if so, what are the products
involved?
Thanks,
RBollinger
I'm using CA-Visual Express to hit Datacom through the Client ODBC Driver. It's a bit tricky with the authorities. You have to make sure they're set at the ODBC level if you want more than read-only access. We've not quite worked out the details on this yet. However, you can import any datacom table through the same ODBC driver using MS Access and perform all queries through there. (Not graceful, granted, but it does work!)
Good Luck,
rrowe.
Connecting to a Datacom DB data source
a number of disparate data sources -- except a CA Datacom/DB
(mainframe) data source.
Any ideas on how to connect to the mainframe data so that it has the
look and feel of an ODBC database? And if so, what are the products
involved?
Thanks,
RBollingerI'm using CA-Visual Express to hit Datacom through the Client ODBC
Driver. It's a bit tricky with the authorities. You have to make sure
they're set at the ODBC level if you want more than read-only access.
We've not quite worked out the details on this yet. However, you can
import any datacom table through the same ODBC driver using MS Access
and perform all queries through there. (Not graceful, granted, but it
does work!)
Good Luck,
rrowe.
rrowe
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message535356.html
Saturday, February 25, 2012
connecting through C#
Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ProcBuilder.mdf;Integrated Security=True;User Instance=True
This works fine on my machine, but when I try to run it on another computer it tells me:
"An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections."
It should not be trying to connect remotely, it's a local DB. Do I
have to install SQL Express on the machine I'm installing this on for
it to work?
hi,
data is not local to the client machine but to the machine providing the service, the machine with SQL Server (SQLExpress) installed.. and, of course, the relative database files have to be stored on that pc...
so, if you have to connect to a remote SQLExpress instance, you should modify the connection string accordingly to the "remote" SQLExpress instance..
if .\SQExpress works for a local connection, a remote one should "point" to the computer name, thus ComputerName\SQLExpress, and this is a "general" thumb for SQL Server connections...
but you are also dealing User Instance facilities, and http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic8 points out only "local connections" are supported.. you can overcome this hacking the pipe the service is listening on, but you should go for a more "traditional" use of a SQL Server engine..
or, you just install SQLExpress (and your user database(s)) on every each machine and continue using User Instance feature "locally"..
regards
Friday, February 10, 2012
Connect to SQL 6.5 from Win XP Pro
a server that is a domain controller in a different domain in preparation
for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5 SP6.
I have an account on that domain but I can't get authenticated even though I
have administrative privileges and we have a trusted connection between the
two domains. What am I doing wrong?
thanks
chuck
Charles,
Check the SQL 6.5 Servers authentication mode. In 6.5 it can be set to
Standard, Integrated, or Mixed. Authentication using trusted connections
will fail if it's set to Standard.
Jon Jahren
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.co m...
> I need to create a system DSN SQL connection (Data Source) on my machine
to
> a server that is a domain controller in a different domain in preparation
> for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5
SP6.
> I have an account on that domain but I can't get authenticated even though
I
> have administrative privileges and we have a trusted connection between
the
> two domains. What am I doing wrong?
> thanks
> chuck
>
|||If by C# program, you mean a managed program using datasets, datareaders and
the like; I doubt that you will be able to do so for SQL Server 6.5; as the
..NET environment doesn't offer support for ODBC or OLEDB drivers version 2.5
and less. (I'm not sure about the exact number version, however SQL 6.5 is
quite probably older than the minimum requirement for an SQL-Server OLEDB
driver.)
However, if you intention is to use ADO and interoperability, then of course
you will have no problem.
S. L.
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.co m...
>I need to create a system DSN SQL connection (Data Source) on my machine to
>a server that is a domain controller in a different domain in preparation
>for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5 SP6.
>I have an account on that domain but I can't get authenticated even though
>I have administrative privileges and we have a trusted connection between
>the two domains. What am I doing wrong?
> thanks
> chuck
>
|||Oups!
After verification, I'm wrong. The framework 1.1 now allows connection to
ODBC and SQL-Server 6.5. See:
http://www.able-consulting.com/dotne...anagedProvider
S. L.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ejszvxayEHA.2540@.TK2MSFTNGP09.phx.gbl...
> If by C# program, you mean a managed program using datasets, datareaders
> and the like; I doubt that you will be able to do so for SQL Server 6.5;
> as the .NET environment doesn't offer support for ODBC or OLEDB drivers
> version 2.5 and less. (I'm not sure about the exact number version,
> however SQL 6.5 is quite probably older than the minimum requirement for
> an SQL-Server OLEDB driver.)
> However, if you intention is to use ADO and interoperability, then of
> course you will have no problem.
> S. L.
> "Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
> news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.co m...
>
Connect to SQL 6.5 from Win XP Pro
a server that is a domain controller in a different domain in preparation
for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5 SP6.
I have an account on that domain but I can't get authenticated even though I
have administrative privileges and we have a trusted connection between the
two domains. What am I doing wrong?
thanks
chuckCharles,
Check the SQL 6.5 Servers authentication mode. In 6.5 it can be set to
Standard, Integrated, or Mixed. Authentication using trusted connections
will fail if it's set to Standard.
Jon Jahren
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.com...
> I need to create a system DSN SQL connection (Data Source) on my machine
to
> a server that is a domain controller in a different domain in preparation
> for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5
SP6.
> I have an account on that domain but I can't get authenticated even though
I
> have administrative privileges and we have a trusted connection between
the
> two domains. What am I doing wrong?
> thanks
> chuck
>|||If by C# program, you mean a managed program using datasets, datareaders and
the like; I doubt that you will be able to do so for SQL Server 6.5; as the
.NET environment doesn't offer support for ODBC or OLEDB drivers version 2.
5
and less. (I'm not sure about the exact number version, however SQL 6.5 is
quite probably older than the minimum requirement for an SQL-Server OLEDB
driver.)
However, if you intention is to use ADO and interoperability, then of course
you will have no problem.
S. L.
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.com...
>I need to create a system DSN SQL connection (Data Source) on my machine to
>a server that is a domain controller in a different domain in preparation
>for a C# program I'm writing. It is running NT 4.0 server with SQL 6.5 SP6.
>I have an account on that domain but I can't get authenticated even though
>I have administrative privileges and we have a trusted connection between
>the two domains. What am I doing wrong?
> thanks
> chuck
>|||Oups!
After verification, I'm wrong. The framework 1.1 now allows connection to
ODBC and SQL-Server 6.5. See:
r" target="_blank">http://www.able-consulting.com/dotn...rovide
r
S. L.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ejszvxayEHA.2540@.TK2MSFTNGP09.phx.gbl...
> If by C# program, you mean a managed program using datasets, datareaders
> and the like; I doubt that you will be able to do so for SQL Server 6.5;
> as the .NET environment doesn't offer support for ODBC or OLEDB drivers
> version 2.5 and less. (I'm not sure about the exact number version,
> however SQL 6.5 is quite probably older than the minimum requirement for
> an SQL-Server OLEDB driver.)
> However, if you intention is to use ADO and interoperability, then of
> course you will have no problem.
> S. L.
> "Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message
> news:HjQkd.7644$L92.1112@.newssvr16.news.prodigy.com...
>