Issues with connecting from .NET to Sybase ASE 12

If anyone out there has been successful in connecting to Sybase ASE 12
from Visual Studio .NET 2003 either in a Windows Forms or Reporting
Services project, can you please help me.
...
In summary, I wish to:
(a) create a Windows Forms application using VB.NET (Visual Studio
.NET 2003 and .NET Framework 1.1) that will connect to Sybase ASE 12.0
to execute stored procedures.
(b) create a SQL Server Reporting Services report using Visual Studio
.NET 2003, .NET Framework 1.1 and SQL Server Reporting Services that
will connect to Sybase ASE 12.0 also via stored procedures.
I have Sybase 12 Client installed on my development machine and am
able to connect to a test server from this machine using SQL
Advantage.
My code for (a) is:
>>
Dim sybaseconn As System.Data.OleDb.OleDbConnection
Dim sybaseconnstring As String = "Provider=Sybase.ASEOLEDBProvider;Server Name=XXXXXX;Initial
Catalog=YYYYYY;User ID=UUUUUU;Password=PPPPPP;"
Try
sybaseconn = New System.Data.OleDb.OleDbConnection(sybaseconnstring)
sybaseconn.Open()
Catch ex As Exception
MsgBox(ex.Message)
Finally
Select Case sybaseconn.State
Case ConnectionState.Broken, ConnectionState.Closed
Case Else
sybaseconn.Close()
sybaseconn.Dispose()
End Select
sybaseconn = Nothing
End Try
>>
When I run this, I receive the following message on the Open() line:
"The 'Sybase.ASEOLEDBProvider' provider is not registered on the local
machine."
and this is the exception:
?ex
{System.InvalidOperationException}
[System.InvalidOperationException]:
{System.InvalidOperationException}
HelpLink: Nothing
InnerException: {System.Data.OleDb.OleDbException}
Message: "The 'Sybase.ASEOLEDBProvider' provider is not registered
on the local machine."
Source: "System.Data"
StackTrace: " at
System.Data.OleDb.OleDbConnection.CreateProviderError(Int32 hr)
at System.Data.OleDb.OleDbConnection.CreateProvider(OleDbConnectionString
constr)
at System.Data.OleDb.OleDbConnection.Open()
at Sybase_Stuff.Form1.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\allchini\My Documents\Visual Studio
Projects\Sybase Stuff\Form1.vb:line 98"
TargetSite: {System.Reflection.RuntimeMethodInfo}
My problem with (b) is as follows:
In my Reporting Services project in Visual Studio .NET 2003 I attempt
to set up a shared data source by selecting Add New Datasource. This
displays the Data Link Properties dialog. On the Provider tab, I
select "Sybase ASE OLE DB Provider". When I press Next or OK, I
receive the following message:
Microsoft Data Link Error
Provider is no longer available. Ensure that the provider is
installed properly.
Can you tell me how to achieve (a) and/ or (b)?
Thanks
Ian AllchinFurther to my earlier posting, I contacted Sybase who were helpful. I
have resolved the first issue. My Sybase installation was corrupted,
so I uninstalled completely. I then installed Sybase ASE 12.5.2 PC
Client which includes the ASE ADO .NET Provider. I have been able to
write a .NET Windows Forms application using this provider in very
much the same way as the SQL Server .NET Provider.
Information about the Sybase ASE .NET Data Provider can be found at:
http://sybooks.sybase.com/onlinebooks/group-adonet/asnetg0110e/adonet
I AM STILL LOOKING FOR HELP ON HOW TO CONNECT TO SYBASE ASE 12 FROM A
SQL SERVER REPORTING SERVICES PROJECT. PLEASE RESPOND IF YOU HAVE ANY
INFORMATION.
Ian Allchin
Alpha Ventures Limited, UK|||I have not done it myself, but this should get you started with using Sybase
ASE 12 in Reporting Services:
* First, you have to make sure the ASE data provider is installed correctly,
so that the report designer in VS.NET and report server inside ASP.NET are
able to load the dlls of the data provider at runtime. Note: there could be
file system permissions issues which prevent ASP.NET from accessing the ASE
data provider dlls. You might want to try to copy the dlls directly into the
report designer and report server installation directories.
* close down all VS.NET sessions before modifying rsReportDesigner.config
* backup the existing rsReportDesigner.config and rsReportServer.config
* Add these entries to rsReportDesigner.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
... and in the Designer section:
<Extension Name="Sybase"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
* Stop the ReportServer Windows service
* Add this entry to rsReportServer.config in the Data section:
<Extension Name="Sybase"
Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
* Restart ReportServer Windows service
* Reset IIS/ASP.NET by running iisreset from the command line
There are several people on this newsgroups who have indicated they are
using Sybase. Not sure if they have tried the managed Sybase provider.
You might also want to read this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=f02812e9-65de-4007-a142-b689522d4c93&sloc=en-us
As indicated in the thread, it should work on the report server, but I'm not
sure about the report designer. It will depend on how the Sybase ASE
provider is implemented internally.
Documentation on the config files:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ian Allchin" <ian.allchin@.talk21.com> wrote in message
news:1f2dd58b.0408050554.4a18275d@.posting.google.com...
> Further to my earlier posting, I contacted Sybase who were helpful. I
> have resolved the first issue. My Sybase installation was corrupted,
> so I uninstalled completely. I then installed Sybase ASE 12.5.2 PC
> Client which includes the ASE ADO .NET Provider. I have been able to
> write a .NET Windows Forms application using this provider in very
> much the same way as the SQL Server .NET Provider.
> Information about the Sybase ASE .NET Data Provider can be found at:
> http://sybooks.sybase.com/onlinebooks/group-adonet/asnetg0110e/adonet
> I AM STILL LOOKING FOR HELP ON HOW TO CONNECT TO SYBASE ASE 12 FROM A
> SQL SERVER REPORTING SERVICES PROJECT. PLEASE RESPOND IF YOU HAVE ANY
> INFORMATION.
> Ian Allchin
> Alpha Ventures Limited, UK|||I have used both the ODBC and OLEDB providers. I have not used the dotnet
managed provider. Correct me if I am wrong but it would only use the
provider if you used the generic designer. At runtime it would use it but
during development only the oledb provider?
Bruce L-C
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%233M8BkweEHA.2028@.tk2msftngp13.phx.gbl...
> I have not done it myself, but this should get you started with using
Sybase
> ASE 12 in Reporting Services:
> * First, you have to make sure the ASE data provider is installed
correctly,
> so that the report designer in VS.NET and report server inside ASP.NET are
> able to load the dlls of the data provider at runtime. Note: there could
be
> file system permissions issues which prevent ASP.NET from accessing the
ASE
> data provider dlls. You might want to try to copy the dlls directly into
the
> report designer and report server installation directories.
> * close down all VS.NET sessions before modifying rsReportDesigner.config
> * backup the existing rsReportDesigner.config and rsReportServer.config
> * Add these entries to rsReportDesigner.config in the Data section:
> <Extension Name="Sybase"
> Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
> ... and in the Designer section:
> <Extension Name="Sybase"
>
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> ngServices.Designer"/>
> * Stop the ReportServer Windows service
> * Add this entry to rsReportServer.config in the Data section:
> <Extension Name="Sybase"
> Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
> * Restart ReportServer Windows service
> * Reset IIS/ASP.NET by running iisreset from the command line
> There are several people on this newsgroups who have indicated they are
> using Sybase. Not sure if they have tried the managed Sybase provider.
> You might also want to read this thread:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=f02812e9-65de-4007-a142-b689522d4c93&sloc=en-us
> As indicated in the thread, it should work on the report server, but I'm
not
> sure about the report designer. It will depend on how the Sybase ASE
> provider is implemented internally.
> Documentation on the config files:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Ian Allchin" <ian.allchin@.talk21.com> wrote in message
> news:1f2dd58b.0408050554.4a18275d@.posting.google.com...
> > Further to my earlier posting, I contacted Sybase who were helpful. I
> > have resolved the first issue. My Sybase installation was corrupted,
> > so I uninstalled completely. I then installed Sybase ASE 12.5.2 PC
> > Client which includes the ASE ADO .NET Provider. I have been able to
> > write a .NET Windows Forms application using this provider in very
> > much the same way as the SQL Server .NET Provider.
> >
> > Information about the Sybase ASE .NET Data Provider can be found at:
> >
> > http://sybooks.sybase.com/onlinebooks/group-adonet/asnetg0110e/adonet
> >
> > I AM STILL LOOKING FOR HELP ON HOW TO CONNECT TO SYBASE ASE 12 FROM A
> > SQL SERVER REPORTING SERVICES PROJECT. PLEASE RESPOND IF YOU HAVE ANY
> > INFORMATION.
> >
> > Ian Allchin
> > Alpha Ventures Limited, UK
>|||If you register any managed data provider in report designer and select it
in the data source dialog, the designer will only enable the generic query
designer. The toggle icon for the visual query designer will be disabled
(because the visual query designer is not capable of working with managed
data providers right now as discussed in the referenced thread below).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O$iOYRxeEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I have used both the ODBC and OLEDB providers. I have not used the dotnet
> managed provider. Correct me if I am wrong but it would only use the
> provider if you used the generic designer. At runtime it would use it but
> during development only the oledb provider?
> Bruce L-C
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%233M8BkweEHA.2028@.tk2msftngp13.phx.gbl...
> > I have not done it myself, but this should get you started with using
> Sybase
> > ASE 12 in Reporting Services:
> >
> > * First, you have to make sure the ASE data provider is installed
> correctly,
> > so that the report designer in VS.NET and report server inside ASP.NET
are
> > able to load the dlls of the data provider at runtime. Note: there could
> be
> > file system permissions issues which prevent ASP.NET from accessing the
> ASE
> > data provider dlls. You might want to try to copy the dlls directly into
> the
> > report designer and report server installation directories.
> >
> > * close down all VS.NET sessions before modifying
rsReportDesigner.config
> > * backup the existing rsReportDesigner.config and rsReportServer.config
> >
> > * Add these entries to rsReportDesigner.config in the Data section:
> > <Extension Name="Sybase"
> > Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
> > ... and in the Designer section:
> > <Extension Name="Sybase"
> >
>
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> > ngServices.Designer"/>
> >
> > * Stop the ReportServer Windows service
> > * Add this entry to rsReportServer.config in the Data section:
> > <Extension Name="Sybase"
> > Type="Sybase.Data.AseClient.AseConnection,Sybase.Data.AseClient"/>
> > * Restart ReportServer Windows service
> > * Reset IIS/ASP.NET by running iisreset from the command line
> >
> > There are several people on this newsgroups who have indicated they are
> > using Sybase. Not sure if they have tried the managed Sybase provider.
> >
> > You might also want to read this thread:
> >
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=f02812e9-65de-4007-a142-b689522d4c93&sloc=en-us
> > As indicated in the thread, it should work on the report server, but I'm
> not
> > sure about the report designer. It will depend on how the Sybase ASE
> > provider is implemented internally.
> >
> > Documentation on the config files:
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
> >
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Ian Allchin" <ian.allchin@.talk21.com> wrote in message
> > news:1f2dd58b.0408050554.4a18275d@.posting.google.com...
> > > Further to my earlier posting, I contacted Sybase who were helpful. I
> > > have resolved the first issue. My Sybase installation was corrupted,
> > > so I uninstalled completely. I then installed Sybase ASE 12.5.2 PC
> > > Client which includes the ASE ADO .NET Provider. I have been able to
> > > write a .NET Windows Forms application using this provider in very
> > > much the same way as the SQL Server .NET Provider.
> > >
> > > Information about the Sybase ASE .NET Data Provider can be found at:
> > >
> > > http://sybooks.sybase.com/onlinebooks/group-adonet/asnetg0110e/adonet
> > >
> > > I AM STILL LOOKING FOR HELP ON HOW TO CONNECT TO SYBASE ASE 12 FROM A
> > > SQL SERVER REPORTING SERVICES PROJECT. PLEASE RESPOND IF YOU HAVE ANY
> > > INFORMATION.
> > >
> > > Ian Allchin
> > > Alpha Ventures Limited, UK
> >
> >
>|||First of all thanks to Robert and Bruce for your comments. The DBA
and I have followed the instructions regarding modifying the designer
and server installations, have made some progress on this issue but
are still experiencing some problems ...
On the server, the Sybase .NET provider does not appear in the list of
available connection types within the New Data Source page. We still
just have: Microsoft SQL Server, OLE DB, Oracle and ODBC.
On the client, I have some mixed results:
Sybase IS available as a type in the shared datasource dialog. This
looked encouraging to start with.
I have set up a shared datasource using a connection that I know works
with the ASE .NET provider (tested in a Windows Forms project) and
then tried to used that shared datasource.
When I create a new dataset, with the shared dataset, the only command
type that is available is text (whereas for SQL Server there are 3
options including Stored Procedure).
Within the Generic Query Designer, the Stored Procedure command type
is available.
When I try to create a dataset using a simple text command such as
"SELECT field1 FROM dbo.table1", field1 appears in the field list and
if I press the exclamation mark button field1 is retrieved from the
database OK. However, if I add field1 into a table on the layout tab
and then try to switch to the preview tab, I receive the following
error message:
An error has occurred during report processing.
Operation is not valid due to the current state of the object.
I have also attempted various ways of executing a Sybase stored
procedure with a single integer parameter, using both text and stored
procedure command types, none of which will preview. The most common
message I receive is:
The procedure expected paramater <@.param_name> which was not provided.
Since it is recognising it is a procedure, and picking up the expected
parameter, this also seems promising, but there appear to be mapping
problems.
Ultimately, I want to re-use existing Sybase stored procedures (with
parameters of various data types) within reporting services, so I need
to resolve this.
Any help would be greatly appreciated.
Ian Allchin
Alpha Ventures Limited, UK|||Hi Santiago
Your posting sounds as if you are having some success as you say "it
works". I am also having some issues as described in my most recent
posting on 20 August 2004. For me, reports where the dataset is built
from even text commands and stored procedures with or without
parameters are not working (although if I press the exclamation mark
button, the rows are returned in the grid at the bottom of the generic
query designer). Are you experiencing the same problems or have you
managed to successfully preview or deploy any reports using the Sybase
.NET Provider? If you have, I would be really grateful if you would
describe exactly how you have done it.
If I have any more news, I will post back.
Many thanks
Ian Allchin
Alpha Ventures Limited, UK|||If you can't get the dotnet provider working then I suggest trying the ODBC
or the OLEDB provider. I had an issue with the OLEDB provider, if you setup
multiple OLEDB sources it always wants to use the first one. The Sybase site
had a workaround but it did not work for me (maybe I didn't implement the
workaround correctly). I didn't want to waste anymore time so I went with
the ODBC. The ODBC has worked well and consistantly for me. The only issue I
have had is with stored procedure with parameters. I am currently going
against 11.9.2 backend with a 12.5.2 client. I am upgrading the server to
12.5.2 shortly and hope that the stored procedure problem goes away (I
believe someone that had posted previously had stored procedures working
with ODBC against a 12.5 server). Anyway, I suggest giving the ODBC driver a
chance.
Wait a minute, I just re-read your posting there. You say you get rows
returned. Have you tried the refresh fields button (it looks like the
refresh a web page button in IE). Try that and see if you get your field
list. If you are returning rows then things should be OK.
Bruce L-C
"Ian Allchin" <ian.allchin@.talk21.com> wrote in message
news:1f2dd58b.0408212332.32a40752@.posting.google.com...
> Hi Santiago
> Your posting sounds as if you are having some success as you say "it
> works". I am also having some issues as described in my most recent
> posting on 20 August 2004. For me, reports where the dataset is built
> from even text commands and stored procedures with or without
> parameters are not working (although if I press the exclamation mark
> button, the rows are returned in the grid at the bottom of the generic
> query designer). Are you experiencing the same problems or have you
> managed to successfully preview or deploy any reports using the Sybase
> .NET Provider? If you have, I would be really grateful if you would
> describe exactly how you have done it.
> If I have any more news, I will post back.
> Many thanks
> Ian Allchin
> Alpha Ventures Limited, UK|||Bruce
Thanks for the information regarding ODBC and OLEDB. I have already
tried OLEDB with some success (ie I did manage to call stored
procedures and preview reports), although I had some minor issues with
using 12.5.2 client against 12.0 server. I will revisit this and ODBC
if I cannot resolve the current issues with the ADO .NET route, which
I what Sybase recommended.
My current situation with the ADO .NET provider is that I can connect
to a database and execute a non parameterised text command within the
generic query designer - eg SELECT * FROM tablename. My field list
appears without me pressing refresh fields and if I press the
exclamation mark button I see my rows returned to the grid. However,
when I add fields to a table control on the layout tab and then try to
switch to the preview tab, I receive an error message:
An error has occurred during report processing.
Operation is not valid due to the current state of the object.
I have also tried EXEC storedprocedurename 1 (where 1 is a hard coded
argument) as a text command. Reporting services brings back my
expected field list and returns rows in the grid. However, when I add
fields to a table control on the layout tab and then try to switch to
the preview tab, I receive the same error message.
The reason I am using text commands is that this appears to be the
only option that is fully available and other attempts to deal with
stored procedures have caused different error messages (see my posting
20 August 2004).
Stop Press:
I am able to build and deploy the report. When selecting the report
on the portal, I receive a more detailed error message:
An error has occurred during report processing. (rsProcessingAborted)
An attempt has been made to use a data extension 'Sybase' that is not
registered for this report server. (rsDataExtensionNotFound)
OK, so that may explain the "invalid state of the object".
Does anyone know what we have to do to register it correctly? Also,
should the stored procedure option be available as a command type like
with SQL Server?
Thanks
Ian Allchin
Alpha Ventures Limited UK|||Sorry I can't help anymore with this. I would like to use the dotnet
provider for Sybase as well if it can be made to work.
Bruce L-C
"Ian Allchin" <ian.allchin@.talk21.com> wrote in message
news:1f2dd58b.0408230016.50afd7ac@.posting.google.com...
> Bruce
> Thanks for the information regarding ODBC and OLEDB. I have already
> tried OLEDB with some success (ie I did manage to call stored
> procedures and preview reports), although I had some minor issues with
> using 12.5.2 client against 12.0 server. I will revisit this and ODBC
> if I cannot resolve the current issues with the ADO .NET route, which
> I what Sybase recommended.
> My current situation with the ADO .NET provider is that I can connect
> to a database and execute a non parameterised text command within the
> generic query designer - eg SELECT * FROM tablename. My field list
> appears without me pressing refresh fields and if I press the
> exclamation mark button I see my rows returned to the grid. However,
> when I add fields to a table control on the layout tab and then try to
> switch to the preview tab, I receive an error message:
> An error has occurred during report processing.
> Operation is not valid due to the current state of the object.
> I have also tried EXEC storedprocedurename 1 (where 1 is a hard coded
> argument) as a text command. Reporting services brings back my
> expected field list and returns rows in the grid. However, when I add
> fields to a table control on the layout tab and then try to switch to
> the preview tab, I receive the same error message.
> The reason I am using text commands is that this appears to be the
> only option that is fully available and other attempts to deal with
> stored procedures have caused different error messages (see my posting
> 20 August 2004).
> Stop Press:
> I am able to build and deploy the report. When selecting the report
> on the portal, I receive a more detailed error message:
> An error has occurred during report processing. (rsProcessingAborted)
> An attempt has been made to use a data extension 'Sybase' that is not
> registered for this report server. (rsDataExtensionNotFound)
> OK, so that may explain the "invalid state of the object".
> Does anyone know what we have to do to register it correctly? Also,
> should the stored procedure option be available as a command type like
> with SQL Server?
> Thanks
> Ian Allchin
> Alpha Ventures Limited UK|||I have now had a response from Microsoft technical support. They said
that the Sybase ADO .NET Provider is not a Reporting Services Data
Extension. These are two different things. Sybase would need to
provide a Reporting Services Data Extension.
The Microsoft technical support person recommended trying OLEDB and
only using ODBC if OLEDB was unsuccessful. I intend to follow this
advice, but I will also contact Sybase and ask if they have any plans
to provide a Reporting Services Data Extension and post back.
Ian Allchin
Alpha Ventures Limited, UK|||Interesting. I thought all that was needed was a dotnet data provider.
Didn't know that it was more than that. Thanks for posting back on this.
Bruce L-C
"Ian Allchin" <ian.allchin@.talk21.com> wrote in message
news:1f2dd58b.0408240942.262fd3a1@.posting.google.com...
> I have now had a response from Microsoft technical support. They said
> that the Sybase ADO .NET Provider is not a Reporting Services Data
> Extension. These are two different things. Sybase would need to
> provide a Reporting Services Data Extension.
> The Microsoft technical support person recommended trying OLEDB and
> only using ODBC if OLEDB was unsuccessful. I intend to follow this
> advice, but I will also contact Sybase and ask if they have any plans
> to provide a Reporting Services Data Extension and post back.
> Ian Allchin
> Alpha Ventures Limited, UK|||I have contacted Sybase. They are now considering whether to
implement a Reporting Services Data Extension. A decision is expected
within the next few months and I will post back if I receive any news
on this. In the meantime, I will be going back to try OLEDB and ODBC.
Ian Allchin
Alpha Ventures Limited, UK|||Thanks for the update. If you have any problems with either OLEDB or ODBC
post back here. I have used ODBC extensively with Sybase and am now
migrating to 12.5.2. Others have used OLEDB successfully (and I will be
giving that a try shortly as well).
Thanks again, now I won't waste any time with with the dotnet provider.
Bruce L-C
"Ian Allchin" <ian.allchin@.talk21.com> wrote in message
news:1f2dd58b.0409060212.3f64dffb@.posting.google.com...
>I have contacted Sybase. They are now considering whether to
> implement a Reporting Services Data Extension. A decision is expected
> within the next few months and I will post back if I receive any news
> on this. In the meantime, I will be going back to try OLEDB and ODBC.
> Ian Allchin
> Alpha Ventures Limited, UK

Issues using parameterised reports connecting to Oracle using ODBC and Microsoft OLE DB Provider

I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?

Thanks.

It could have something to do with the syntax you are using to pass the parameters. Oracle uses : (colon) instead of @. for SQL Server

See this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=679346&SiteID=1

|||

Hi,

Cheers. Unfortunately that's not the issue. Have query like this:

"select unique change_id from change

where (change.change_id like :changeCategory)"

Any other ideas?

Thanks.

|||

I'm not sure, but I think that depending on which provider you connect with you may need to change the named parameter syntax to unnamed. I think you replace you variable with a question mark (?)

You can give it a go. Other than that I can't really help - not my area of expertise.

Issues connecting to SQL Server 2005 from SQL Server Mgmt Studio - Please help!!!

Hi Everyone,

I am running into a problem and have been browsing the web and reading nonstop for answers with no success. My company just installed SQL Server 2005 and I am trying to connect to it via SQL Server Management Studio from my network computer in the same office building.

When I browse for Local or Network Servers in the initial popup box at SQL Management Studio Startup, it doesn't show any servers at all. Is there something I need to enable on the Server using the Configuration Manager or one of the other tools to allow my network computer to "see" the SQL Server?

Any insight would be MUCH appreciated. Even a link to a how-to would suffice. Thank you.

Is your server and your computer on the same network? To enumerate SQL server on your network, you must turn on SQL Browser on your server machine. Make sure UDP port 1434 is not blocked by any firewall on your server. Keep in mind you are not guaranteed to see all network instance as the enumration is UDP based and is not realiable. So, in this case, you'd better type your server name into the box directly. such server1 or server1\inst1, etc.

|||

read these article

http://msdn2.microsoft.com/en-us/library/ms165724.aspx

http://support.microsoft.com/default.aspx/kb/914277

http://support.microsoft.com/kb/287932/en

Madhu

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody ha
s
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal
.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my deskto
p
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file
,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody h
as
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great dea
l.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through N
T
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through
NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa'
,
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server
)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
Christine
A few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>
|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>
|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:
>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:
> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have a linked server setup to connect to an access file from a filer
> >server. If I am on the server computer logged in using Windows NT
> >authencation, the connections works. However, if I use a native SQL login
> >(with sys admin rights) it gave the following error:
> >
> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
> >
> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> >
> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
> >open the file '\\filer-la03p\Mbs\Credit Mortgage
> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
> >exclusively by another user, or you need permission to view its data.]
> >
> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> >IDBInitialize::Initialize returned 0x80004005: ].
> >
> >The linked server has security mapping as remote user:"admin" with password
> >null.
> >I also have noticed that if I connect to the server using EM from my desktop
> >using NT authencation, the error ocurrs as well. In this case I will say it
> >is a "double-hopping" issue. But why when using a native SQL account will
> >have a problem?
> >
> >From my understanding when using linked server to connect to an access file,
> >it is using the service account that starts SQL Server. I had setup all
> >privilages required for the SQL Service account. And as I inicated earlier,
> >it connects ok if I am at the server using NT authencation to log in.
> >
> >I rearch online and it seems like I am not the only one who has problems
> >with linked server for access from other computers. Unfortunately, nobody has
> >given the resolutions that works for the requester.
> >
> >Someone please help, we are a microsoft shop and everything is microsoft.
> >This problem has stopped our development for the business units a great deal.
> >Our clients were just bought in to convert a lot of their stuff to SQL
> >Server. Now they are going to back off!
> >
> >Thanks if someone can respond quickly.
> >
> >Christine
> >
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>> A few suggestions:
>> --Make sure that the Access mdb (and mdw) reside on a file share that
>> the Windows account has read/write/delete ACLs on for the files and
>> folders.
>> --Make sure that the Access mdb is totally unsecured (check the owner
>> property -- it should NOT say 'unknown').
>> --Make sure that the login you are using is Admin with an empty string
>> (''), not null.
>> --Use filemon (www.sysinternals.com) to check to see if SQLS is
>> getting access to the files/folders.
>> FWIW, linked servers to Access are a real PITA. You'd have a much
>> easier time if you worked it the other way around, linking to SQL
>> Server from the Access .mdb.
>> --Mary
>> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
>> C@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have a linked server setup to connect to an access file from a filer
>> >server. If I am on the server computer logged in using Windows NT
>> >authencation, the connections works. However, if I use a native SQL login
>> >(with sys admin rights) it gave the following error:
>> >
>> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>> >
>> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>> >
>> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>> >open the file '\\filer-la03p\Mbs\Credit Mortgage
>> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>> >exclusively by another user, or you need permission to view its data.]
>> >
>> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>> >IDBInitialize::Initialize returned 0x80004005: ].
>> >
>> >The linked server has security mapping as remote user:"admin" with password
>> >null.
>> >I also have noticed that if I connect to the server using EM from my desktop
>> >using NT authencation, the error ocurrs as well. In this case I will say it
>> >is a "double-hopping" issue. But why when using a native SQL account will
>> >have a problem?
>> >
>> >From my understanding when using linked server to connect to an access file,
>> >it is using the service account that starts SQL Server. I had setup all
>> >privilages required for the SQL Service account. And as I inicated earlier,
>> >it connects ok if I am at the server using NT authencation to log in.
>> >
>> >I rearch online and it seems like I am not the only one who has problems
>> >with linked server for access from other computers. Unfortunately, nobody has
>> >given the resolutions that works for the requester.
>> >
>> >Someone please help, we are a microsoft shop and everything is microsoft.
>> >This problem has stopped our development for the business units a great deal.
>> >Our clients were just bought in to convert a lot of their stuff to SQL
>> >Server. Now they are going to back off!
>> >
>> >Thanks if someone can respond quickly.
>> >
>> >Christine
>> >
>>

ISSUE WHILE CONNECTING TO ORACLE SOURCE WITH 64 BIT processor SQL SERVER SSIS

Hello All,

I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.

The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:

"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"

Regards,

Raju

Hello All,

The above package is running fine with sql server 32 bit development but not running in 64 bit production server.

The oracle client that has been installed on the production server is a 32 bit, because our source database is an oracle 32 bit not 64 bit which is residing in another server.

Hope this additional information will help you people get it resolved.

Regards,

Raju

|||

Hi Raju,

If you want to connect to Oracle using a 32bit driver, then you should execute the SSIS package using the 32bit version of DTEXEC.

Regards,

Christian

Issue connecting to a remote SQL Server 2000

I get the following error message in quotes. I have a web application written in ASP.NET 2.0 through which I am trying to connect to a remote SQL Server 2000. My operating system is Windows Vista .

I looked at many different sites to find a solution for this but without any luck. I am sure someone would have seen this issue here and I expect them to shed some light.

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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

The connection string is located in my web.config file which looks like

<add name="AMSConn" connectionString="Data Source=xxxx;
Initial Catalog=testdb;User ID=sa;Password=xxxx" providerName="System.Data.SqlClient" />

I want to get this issue resolved and I need you guys help.

Please let me know if you need any more information.

Thanks

Bharat

Hello my friend,

First try to access the database from the actual database server using the username and password within your connection string. If this fails, you are using the wrong username and password.

If this works, then it is a remote issue. Try and ping the database server from the computer running your application and see if you get a response or request timed out.

Kind regards

Scotty

|||

Hi Scotty,

First, thanks for the reply. I already tried your first suggestion. It's working fine in the database server with the same credentials. So, not a problem with username and password.

But, when I tried Pinging the server as you indicated I am getting the request timed out error. I don't know what is the reason. I don't have any problems logging into the machine using remote desktop.

Any more suggestions.

Thanks

Bharat

|||

hi Scotty,I have also same problem.

I can ping to remote sql server and can connect with sql query analyser with same credentials.

But can't connect from my aspx application.

Issue connecting through firewall using jdbc connector.

We have an app in development. The front end of the app is web-based running
tomcat.
This app sits in our DMZ.
This app communicates to a sql 2000 server on our corporate network. The
connection is via microsofts jdbc 2000 connector.
We have allowed the server hosting the app to initiate connections to the
internal sql server on port 1433 only. The sql server can communicat any/any
back to the dmz.
I am seeing some interesting behavior - The front end initiates a connection
to the DB server on 1433 which is successful, but then also tries to
initiate a connection on 137 (named pipes/nbname), of course the firewall
blocks this and it eventually times out after it times out it opens
addtional pool connection on 1433. This causes the app to load very slowly
as the pages are data driven. If we open up 137 then the app runs at a
normal speed.
I dont understant why the front end is trying to communicate to the server
on anything other than 1433. The server is configured for tcp/ip only. The
JDBC driver and app on the front end is doing everything by IP address.
I've thought of trying a host file on the front end resolving the back end.
and vice versa, but this shouldnt matter as everything is IP and not name.
I am now trying to figure out if this is an issue with the microsoft jdbc
driver config - though it looks like you dont have the ability to specify
protocols in it, an issue witht he sql server - perhaps the response to the
initial connection is telling the client to talk named pipes, or if its
just an OS issue.
Leaving 137 open from the dmz app to the internal sql server is not an
option.
Thanks in advance.
Bill
I think I have ran into the same problem. I am developing an application that must reside within DMZ and must have access to a SQL server on the internal network. I think the problem is the MS odbc and jdbc drivers. They try to use NetBIOS over TCP instea
d of just pure TCP.
My case is as follows:
* Web application(ASP) in DMZ
* MS ODBC driver
* Firewall between DMZ and internal network(port 1433 open)
* SQL Server on internal network
The problem is that the ODBC driver seems to try to connect to the database through SMB, Server Message Block, wich is a windows service for file and printer sharing. SMB relies on the NetBIOS service wich in turn relies on TCP or SPX or whatever it is se
t up to use. Now, NetBIOS uses udp port 137. This port is not opened in our firewall, as in your case. Therefore the application in the DMZ can not establish a connection with the sql server.
I think there are two possible solutions for this problem:
* Find a driver that uses only TCP and not TCP via NetBIOS
* Disable NetBIOS and SMB in the web server
The second solution works very godd, I think. Not a solution for us, though, since the web master has set up a Microsoft network within the DMZ. And you have to disable Microsoft network to disable SMB.
Sorry for my bad english. Hope that what i have written helped a little, at least.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.