Help: I can open my database in Management Studio, but can't connect it in Integration Services

Received error tip:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm using the Evaluation Edition of SQL Server 2005, Windows XP SP2 Home Edition.

I have disabled the firewall.

You need to enable remote connections:

http://www.sqlserver2005.de/Sharedfiles/remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, I have enable remote connections, but still wrong.

Can the Home Edition of Windows XP support SQL Server?

|||

Hi, only the developer edition and the express edition are supposed to work on Windows XP Home (as of the offical system requirements, didn′t tested that out yet). Let me hear some feedback if that worked for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I have installed SQL Server Evaluation Edition on Windows XP Home Edition. It can work. In the Management Studio, the database can be opened and executed. The only one problem is that in the reporting services, the database can't be connetcted.

HELP: How to avoid ridiculous system resource issues when connecting to a huge Excel file

Hi all,

I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.

I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:

Intel Centrino Duo 2.00 GHz

2GB RAM

XP Pro SP2

There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.

Sincerely,

Brian Pulliam

Ok, so it sounds like it wants to read the entire file, a nice feature, and probably unavoidable.

Personally I would be developing and testing against a smaller file that is easier to work with, and easier to manipulate for test cases. Then when the bulk of the dev is ready run a test with the real file.

You may think this is a big issue, and whilst it would be nicer if it behaved better, what if this was a SQL load, woud you expect to dev against a 100GB database? Whilst the metadata would be OK, the simple running of a test would be impractical, so I'd still go with smaller test data during development.

|||

Just one more idea to try...

If you are using "Excel Source" try to use "OLEDB Source" instead and specify Microsoft Jet 4.0 OLE DB Provider.

If you are using Excel 2007, you should use the new ACE provider.

Regards,

Yitzhak

|||

In case if you don't have MS Office 2007 installed on your machine,

here is the link to donwload the connectivity components:

2007 Office System Driver: Data Connectivity Components.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=ja

Regards,

Yitzhak

|||

Thanks much for the reply Darren. I agree that a small subset of data would be great to test with. Actually I would dev against a 100GB database, although I would not pull all the data in my tests.

But your analogy to running a test on a 100GB DB is not very applicable. Firstly, I am not loading an entire DB. My Excel connection is pointing at one worksheet in an Excel 2003 file. And I have not even tried executing this package yet, I am nowhere near that far. Simply renaming the connection to the Excel file takes about 5 minutes before BI Developer Studio is responsive again. Also note that when connecting to a 100GB database, BI does not attempt to load the entire data set in that scenario. Maybe it has something to do with using an Excel file, that SSIS goes all paranoid and wants to look at the whole dang file.

Do you think if I set up a linked server to the Excel file in Management Studio that it might not have this problem? Maybe that would fake it into trusting the data source enough to not to the validation load. I attempted to do this on Friday but did not have much luck. Has anyone done this for an Excel 11.0 workbook?

Thanks again,

Brian

HELP: Connecting to a secure file share using DTS

I have a dts package that works in connecting to a secure file share when I run it manually. When I schedule it to run on its own - it fails due to the lack of a log on. Is there something I can do to initiate the log in via the schedule rather than me manually doing it ?

Thank you!

When you run it manually, it's executing under your security context. When you schedule it, it will run under the security context of SQL Server Agent service account if the job is owned by a sysadmin. You need to grant the SQL Agent service account permissions to the share. The following article explains this issue:

INF: How to Run a DTS Package as a Scheduled Job

http://support.microsoft.com/kb/269074

-Sue

Help..Error connecting to SQL Server

Hi,

I am writing my first aspx file to connect to a standalone SQL Server which is separated from the IIS webserver. The code is

<HTML>
<HEAD>
<TITLE>Store Locator</TITLE>
<script runat="server">
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
if Not IsPostBack Then
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
DBConn = New OleDbConnection("Provider=sqloledb;" _
& "server=dailyplanet;" _
& "Initial Catalog=JAVSTORE;" _
& "User Id=javtrader;" _
& "Password=rage123;")
DBCommand = New OleDbDataAdapter _
("Select * " _
& "From JAVInventory " _
& "Where ID > ((Select COUNT(ID) " _
& "From JAVInventory ) - 100)" _
& "Order By ID DESC", DBConn)
DBCommand.Fill(DSPageData, _
"RecentJAVs")
' ddlZipCode.DataSource = _
' DSPageData.Tables("RecentJAVs").DefaultView
' ddlZipCode.DataBind()
End If
End Sub

I used Visual .Net and Web Matrix and was able to connect to the sql server with the Data Connection to view the tables. However, when I use IIS or the Visual .Net debugger to run the aspx file, it get the following error.. please help..

Server Error in '/javtrade' Application.
------------------------

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Source Error:

Line 21: & "From JAVInventory ) - 100)" _
Line 22: & "Order By ID DESC", DBConn)
Line 23: DBCommand.Fill(DSPageData, _
Line 24: "RecentJAVs")
Line 25: ' ddlZipCode.DataSource = _

Source File: c:\inetpub\wwwroot\javtrade\management.aspx Line: 23

Stack Trace:

[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.management_aspx.Page_Load(Object Sender, EventArgs E) in c:\inetpub\wwwroot\javtrade\management.aspx:23
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731Two suggestions, one is to supply a 'server' name of localhost. The second is to make sure that the ASP.NET user has access to the SQL server.

Josh