Connecting to SQL Mobile within VS-2005

I am getting an error when I run my project within VS2005. I am trying to connect to a SQL Mobile (sdf) file thru code using a connection string. As soon as run my project within the VS2005 environment, I get the following error when the code tries to connect:
"The file is not a valid database file. An internal error has occurred. [,,,Databasename,,]"
The source of the error is: Microsoft SQL Server 2000 Windows CE Edition
Native Error of: 25011
I am am deploying to POcket PC 2003 SE Emulator.
This code worked fine in VS2003 with SQLCE 2.0. But I have now updated the projects to VS2005 and recreated the database sdf file within the VS2005 environment.
Could someone please help me ASAP?
Thanks.

There is no database compatibility between SQL CE 2.0 and SQL Mobile. Database created through one cannot be opened by other. In you case, since you have upgraded the project and re-created the database, you have SQL Mobile database. But your application still has references to SQL CE 2.0 provider. So now your application is trying to open a SQL CE 3.0 Database using SQL CE 2.0 provider; that’s the reason why in your error string you can see

The source of the error is: Microsoft SQL Server 2000 Windows CE Edition
Native Error of: 25011

In you application you need to remove the reference from 2.0 provider and add reference to 3.0 provider.

Thanks

-Mani

Connecting to MDF file over Workgroup

Hi

I've build a client management (Windows) application in VS2005 which works fine on the local development machine but falls over when trying to connect to the SQL Server Express file located on a second machine on the same Workgroup. Several of the apllications will connect to the database file from networked computers.

The error I get when testing the connection or trying to connect via the App is:

-------------------
The file "Z:\BushBreaks.MDF" is on a network path that is not supported for database files.
An attempt to attach and auto-named database for file z:\bushbreaks.mdf failed. A database with the same name exists, or specified file cannot be opened or it is on a UNC share.
-------------------

How can I configure this database and/or connection to be able to allow connections to the MDF file? Please note that security is not an issue if that is at all relevant.

Many Thanks
John

Current you can't use remote database file in your SQL connection. Here is a instruction about AttachDBFileName property in connection string from MSDN:

The name of the primary database file, including the full path name of an attachable database. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string.

Note:

Remote server, HTTP, and UNC path names are not supported.

For more information, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

connecting to a linked server using VS2005

hi,

i've currently created a linked server using sql server management studio express (it connects to an oracle database to draw data), i can query it all fine using the query analyzer with sql server etc.

how can i start using the linked server data with visual studio 2005? i can't seem to find the linked server anywhere inside the server explorer, am i suppose to import it / connect to it any specific way so i can start using the data retreived from the linked server?

thanks in advance!

create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,

Hope this helps

|||

Ramzi.Aynati:

create a view in SQL that reads from this linked server, and in ur server explorer, browse ur view,

Hope this helps

thanks, but how do i create a view?

|||

if you would like to hit linked server in you code just add database name to each object your would like to access. You can run query on server to which you are connected and server will forward your query to linked server. for example if your SQLServer2 is linked server in SQlServer1 you can run query like this om SQLServer1 ( if user you used to link servers have rights to objects in your query)

select * from [SQLserver2].[database].dbo.[tablename]

you can connect results from multiple servers

select * from [SQLserver2].[database].dbo.[tablename]
left join [SQLserver1].[database1].dbo.[tablename1]
ON tablename.aa=tablename1.bb

If you only use linked server in your query you can use OpenQuery which will process data on the linked server and returns only results to you.

SELECT * from OPENQUERY([SQLServer2,' select * from [database].dbo.[tablename]')

Thanks

|||

In your SQL, go to ur query analyzer

and type

Create View View_name as

SELECT *
FROM TEST1.Northwind.dbo.Orders

//where test is ur linked server

This is it, u got urself a view accessible from ur server explorer

|||

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

|||

jpazgier:

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

thanks, i might try both options.. would you happen to know of any tutorials that would put me to the right direction in creating a stored procedure to do this? thanks

|||

Dear Frank

As long as the table ur querying is less than 10000 records, u wont feel a pinch, the difference will be in milli seconds,

The topic jpazgier is raising is for really advanced SQL programming and for huge amount of records over 1 million

|||

thanks, have given it a go and i get this error message, any idea what i'm doing wrong?

OLE DB error trace [Non-interface error].

Msg 7312, Level 16, State 1, Procedure OracleComDir, Line 3

Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

|||nevermind, worked it out, thanks :)|||

Dear Frank

Please dont forget to mark the post that helped u the most as answered for sake of future readers

Glad to be of help my friend

|||

jpazgier:

I would not recommend to create view like this because it will work very slow. To create view SQL server will try to load all data required to generate view from linked server to your server so it will generate very heavy network traffic and will be very slow. The better way is to get exactly what you need from your linked server or create view on linked server and access it from your main server.

Best solution will be to use Stored procedure to do this or table returned function (but not everything will work in function)

Thanks

turns out there's about 500,000 records in the database, so results are generating really slow.

i've been attempting to create a stored procedure, but it's still taking 20 odd seconds to bring up the results, here's a basic summary of my stored procedure:

ALTER PROCEDURE GetOracleData
AS

SELECT
oracle_table1.field1,
oracle_table1.field2,
oracle_table2.field1,
oracle_table2.field2

from
OracleCD..oracle_database1.table1,
OracleCD..oracle_database2.table2

WHERE
(oracle_table1.field1 = oracle_table2.field1) AND
(oracle_table1.field2 = oracle_table2.field2)

OracleCD = name of the linked server i created, i'm thinking the fact that i'm calling the data from the linked server is the reason why it's taking soo long for the data to load? what's the best way to call upon data from different databases in a stored procedure?

thanks in advance!

Connect to SQL Server Database with custom port

I have a SQL server database that I am trying to connect to from VS2005 in the Server Explorer.

I am not using the standard port for my SQL server listening port and I don't see where I can tell the Server Explorer to use a different port?

Can someone point me in the right direction for connecting to my database?

Thanks.

There are 3 ways to do it. Here's an article detailed all of them:http://www.databasejournal.com/features/mssql/article.php/3689846

Make sure you read carefully in section: "Connecting Clients When using Specific Part Assignments" as the first 2 methods are for client tool connection and the 3rd is specific for the ConnectionString in you config file.

Hope it helps.

|||

Thank you very much. This was extremely helpful!

Connect to SQL Server 2005 Problem

Hi all,

I have created an Outlook Add-in using VS2005, which basically populates a custom WinForm with some data retrived from the backend SQL Server 2005 database.

The SQL Server is installed on the same computer as the development environment (VS 2005).

The add-in code compiles OK and I have created a setup package for this app, which installs the required files with no problem. The package has been tested working on the development machine. But as soon as I install it on another computer (installation was no problem, and outlook launched the add-in as expected) and click on the add-in button I made on the menu bar, the add-in hangs up.

I have checked the tables on the sql server and found that no operations was performed whatsoever. Suspecting there are errors connecting to the server, I also threw in some Try - Catch pairs to catch the exceptions, but still nothing pops up as if the app couldn't even start while it runs just fine on the development machine.

Below is how I connect to the SQL Server 2005 in my code (VB)

Dim cn As ADODB.Connection = New Connection
Dim rst As Recordset, query As String
cn.ConnectionString = "Provider=SQLNCLI;Server=Development;Database=Project1;Trusted_Connection=yes;"
cn.Open()

query = "SELECT * FROM Sample_Tab;"
''' Do something with the connection
Try
rst = cn.Execute(query)
Catch ex As Exception
MsgBox("ADODB Error: " & ex.ToString)
Exit Sub
End Try

cn.Close()

Any help will be greatly appreciated.

-rahx

Did you enable NP and/or TCP on your SQL Server? You need make sure that your sql can accept remote connection. Do you have firewall? Please follow the instruction and give us me info http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||Problem solved - my mistake. I used sql native client in my code but forgot to include a sql native client msi in the installation package.

After installing the native client on the test computer, the connection was established successfully.