Do pass thru query connections persist?

I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi

Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.

BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.

HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.

I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?

Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?

Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.

Here is my code behind the report.

Private Sub Report_Open(Cancel As Integer)

Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset

conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"

conGlob.CursorLocation = adUseClient

conGlob.Open

With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With

rst.Open "usp_GetStates", , , , adCmdStoredProc

Set Me.Recordset = rst

rst.Close
conGlob.Close

End Sub|||Your best bet would be to use an access data project and use stored procedures

First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections

1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.

Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.

If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.

Do not have instance of SQL Express 2005 installed

I am attempting to install SQL Express 2005 on a laptop that already has MS Sql Server installed.

I downloaded the SQLEXPR_TOOLKIT.EXE from Microsoft's website and let it go through its installation routine accepting all the default answers.

When I open SQL Server Management Studio Express, I can only connect to the SQL 2000 instance. I have issued the 'SELECT @.@.VERSION' command, and When I try to connect to the instance COMPUTERNAME\SQLEXPRESS, the following error message is displayed:
Cannot connect to YOUR-F8A010A9D0\SQLEXPRESS.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure my 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) (Microsoft SQL Server)

When I go to remove SQL Express 2005 using the add/remove programs, there is a check box that says
"Remove SQL Server 2005 instance components"
and in the box underneath where it lists all the instances it says: "No instances have been installed"

How do I create an instance for SQL Server 2005?

Thank you
SusanYou go to the incorrect location to install SQL Express. Instead, you choose to install tools only. Please go to the directory ...\SQLEXPR\setup.exe. Note, if .NET Framework is still not installed on the machine, you need to install it first.

Do I want the Developer version?

I've downloaded 2005 Express but I want to do SSIS and don't see any way to do that with Express. The Developer Edition apparently has all features, but says you can upgrade to Enterprise, which I don't need (Standard would probably be fine).

Here are my questions:

1) Is there any way to develop SSIS packages in Developer and run them in Express?

2) Is it possible to upgrade Developer to something less than Enterprise, such as Workgroup or Standard?

3) This page is titled "How to Obtain SQL Server 2005 Developer Ediiton" but doesn't explain how to obtain it. Instead it talks about which edition to select, trial software, blah blah:

http://www.microsoft.com/sql/editions/developer/howtobuy.mspx

Are there any catches or preconditions to buying Developer, or is it just a simple $50 (or less, depending on where you buy it)? I want to explore more 2005 features than Express offers but I don't want a trial version that will expire.

Thanks for any clarification that can be provided.

Hi,

1)

SQL Server 2005 Editions and Integration Services
(http://www.sqlis.com/default.aspx?31)

Microsoft SQL Server: SQL Server 2005 Features Comparison
(http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx)

2) Yes, sure but the developer edition has the exact same features as the enterprise edition, so you have to keep in mind only to use the standard feature while developing if you want to deploy them later on a standad licenced server.

3) The only limitation to Developer Edition is that you can use it in production due to the agreed licence agreement during the setup process.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I understand the answers to 2 and 3 but still not clear on 1.

Is there a way to run 2005 integration packages created in Developer Edition under Express Edition? I've browsed the links and they don't give me a clear answer to this question.

|||

I'm going to move your thread to the Integration Services forum where I think you'll get a quicker response.

-Jeffrey

|||

I am pretty sure you can NOT run SSIS packages using only installable components from the sql server express edition. Basically, there is no free runtime available to run SSIS packages, you have to pay...

|||

Ken is correct, you can't run packages using Express Edition.

Also, just to correct one thing that Jens said. Functionality in Enterprise and Standard is NOT the same. You get extra functionality in Enterprise.

-Jamie

|||Thats not hat I said, if you read my post you will recognize that I compaed developer against the enterpise edition (not the standard edition) :-)

-Jens Suessmeyer.|||

Pardon me Jens, you did indeed. My apologies!!! :)

Do I need to shrink my database?

Hello,

I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.

Basically I have a webpage that when I create a new item it populates a new ID, which is the primary key and read only when I go into the database backend so i cannot manually change this.

When I have been doing some testing adding fields to the table and deleting this has obviously given me new ID's such as 113, 114 etc. But on my live site I need these ID's to be sequential, so on my live site the last ID is 108, but now when I add another item the ID is 115 because the number between this and 108 have been taken up.

So my question is really do I need to shrink my database or files to take this down to 109, if so is database shrink or file the best to do? Also i have had someone add a new live item and this has been assigned 113, would I need to delete this, shrink and then re-enter?

Any suggestions would be greatley appreciated.

Many thanks.

shrinking a database does not effect IDENTITY values, you need to look at the following

DBCC CHECKIDENT (Transact-SQL)

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

you could also look at running a SET IDENTITY INSERT ON;

and then updating the ID values manually to your desired values.

Derek

|||

Thank you very much for you reply. Guess I need to run a query on something like the follwoing from the link you sent:

USE Assets;
GO
DBCC CHECKIDENT ('Assets.ID', RESEED, 30);
GO

But how do you specific which row to replace?

Also how does the SET IDENTITY INSERT ON command work, sorry quite new to SQL.

|||

the easiest way for you to do this (assuming you have some form of consistency to your IDs) would be to run the following code:

SET IDENTITY_INSERT dbo.Table ON

GO

UPDATE dbo.Table SET ID = ID -30 --this is the consistency I am referring to, if your IDs are all over the place it going to not be fun lol

GO

SET IDENTITY_INSERT dbo.Table OFF

|||

You cannot use IDENTITY column if you want to ensure no gaps in the values. For example, if a particular transaction rolls back an insert then the generated identity value will be consumed and a subsequent insert will get the next higher value. If you want guaranteed sequential numbers generation then you need to do it using a sequence table yourself.

Please take a look at the link below for an implementation that shows how to use sequence table.

http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx

|||Thank you for your help I have now sorted the problem. Much appreciated.

Do I need SQL Express

I am trying out a Starter Kit which uses SQL Express. I have SQL 2005 Developer and .Net 2.0 framework installed on my XP machine. Do I need to install SQL Express as well? Can't the application work with the full blown version of SQL?

This is my connection string:

<add name="ClubSiteDB" connectionString="initial catalog=ClubDB;Data Source=.\SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/>

This is the error I get: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)

Thanks

You can use your installed SQL but you'll need to modify the connection string and initialize the membership tables in it.

Jeff

|||

Thanks for the response Jeff. What would be the Connection string?

|||

NO. you don't need to install sql express.

Make sure your club.mdf has been attached into your sql server 2005 developer version and then modify your connection string like this:

<add name="constrname" connectionString="Data Source=(local);Initial Catalog=club;Integrated Security=True"
providerName="System.Data.SqlClient" />

Hope my suggestion helps

Do I need multiple versions of Northwind?

I am learning SQL Server 2005 Express, and want to use the Northwind data base as used in many code examples. I wanted to attach the Northwind data base via SQL Management Studio, but cannot find a Northwind.mdf file.

When I do a search using 'Northwind', I see there are already a couple of different versions of Northwind already loaded. One appears to be associated with SQL Server Mobile Edition (Samples folder) and another seems to be associated with Visual Studio 8\SDK\v2.0\Quickstart.

Can I use one of these existing Northwind databases (none have .mdf extention) for SQL Server 2005 or do I need to download yet another version?

You can either download a copy of the Northwind database here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

It says it's for 2000, but it works fine for express. I use pubs a lot to this day, myself.

Buck Woody

|||

Here are links to the Northwind, Pubs, and AdventureWorks sample databases. It is worth having all three of them since many books, magazine articles, and web code is based upon them.

Databases -AdventureWorks
http://msdn2.microsoft.com/en-us/library/ms124659.aspx

Databases -Northwind and Pubs
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034

Do I need CAL for a customized program connect to SQL Server 2005 Express Edition?

Hi all,

I am developing a program to connect to SQL 2005 Express. I don't know if I need any CAL license in order to make the connection. If it is not necessary for the Express Edition case, how about if my client upgrate to SQL 2005 in the future? Do I need CAL license for SQL 2005?

Thanks for any advice given.

You do not need a CAL to connect to SQL Server Express. Each cient does need a CAL for all other editions.

Buck Woody

Do I need CAL for a customized program connect to SQL Server 2005 Express Edition?

Hi all,

I am developing a program to connect to SQL 2005 Express. I don't know if I need any CAL license in order to make the connection. If it is not necessary for the Express Edition case, how about if my client upgrate to SQL 2005 in the future? Do I need CAL license for SQL 2005?

Thanks for any advice given.

You do not need a CAL to connect to SQL Server Express. Each cient does need a CAL for all other editions.

Buck Woody

Do I have to install SQL Express?

I'm using the login/roles from Visual Studio 2005 in a website.

Is MS SQL Express mandatory? I already have MS SQL 2000 on the box. Is there any way to use that instead?

And if so, will I need to install MS SQL on the box I'm building the webiste on?

Thanks,

oilcan

You dont't have to have MS SQL 2005 express on your developer machine.

There is a build-in database schema behind the memebership function in ASP.NET 2.0. By default, if you have MS SQL 2005 express installed, you will get a database named as ASPNETDB.MDF installed under App_data folder in your application when you start using membership.

It would be easy to play with samples and test simple things. However, you need to know how to move this database to another database if you want to host your application somewhere. For example, SQL 2005 or SQL 2000.

You can mannuly install this database by using a tool in this location(most likely): C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

You can pick where you want to install this database from this tool's interface.

After you install this database, you need change your connection string to point to your new database which includes all tables from ASPNETDB.MDF

You may need to read through some articles from searching this topic about membership.

This is just a start and you will get there. If you have problems, you will easily get answers from here.

Enjoy the journey.

|||Any idea where I might find some of those articles you mentioned? I don't feel real comfortable just experimenting, it is a live web server.|||

Hello there:

This article talks about how to merge this ASPNETDB.MDF to your database.

http://aspnet.4guysfromrolla.com/articles/040506-1.aspx

Please follow this article, you will bepretty close. I think you better to test on your developer machine before you move on.

Also I came across this one from DisccountASP forum to show users how to move the starter kits to their server. Just for you as a reference.

http://community.discountasp.net/default.aspx?f=25&m=8785

Let me know if I can help more.

Limno

Do I HAVE to have IIS installed to use SQL Server Express 2005? and do I have to install SQLE2K5

Hi,

I'm an absolute beginner trying to learn SQL and Visual Basic/Visual Web developer.

I've downloaded SQL Express 2005 and want to install it on my laptop (Dell XPS, 2gb Ram) so that I can start doing the tutorials (for SQL and VB/VWS). I dont want any connection to the internet - i only want to have it on my local pc.

I'm confused as I 'm sure I read that I dont need to have the IIS service running, and that it wont require a server...however, I keep reading that IIS, and a server is a MUST, and for installing SQLE2005.

I'm so confused, and frustrated that I dare'nt install SQLE2005...

...Can anyone put me out of my misery?

All advice greatly appreciated.

SQLEB

Hi,

If you have downloaded SQL Express edition with Advance Services then only you require to have IIS installed on your system Refer http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005Advanced.htm

and if you have installed SQL Express w/o Advance Services you don't require to have IIS Refer http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/RequirementsSQLEXP2005.htm

HTH

Hemantgiri S. Goswami

|||No, IIS is NOT required for the use and operation of SQL Server.sql

Do any ISP's really host SQL 2005 Express

Hi, I've been a SQL Server dba for about 10 years, so i know SQL quite well. I've recently started using VWD express, and as it says, its quick and easy to create a website, which I've done. It all works great on my local machine. I've just signed up with an isp and duly ftp'd the project files plus the mdf and ldf that lives in App_Data into their content area, and rather naively I expected it all to work. The pages come up fine until some database access is called for, then i get the seemingly infamous error:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

I've researched this for 2 solid days now, examined forums and posts, followed advice and enabled protocols and so on. Support at the isp in question does not really have a clue about databases and connections strings. Then I started looking closely at the words used by the isp's, none of the isp's explicitly say they support sql 2005 express. They do say SQL 2005, but not express. They do say VWD Express, but not SQL 2005 Express. So does anyone know what is/is not possible with regard to deployment of SQL 2005 express. Does anyone have experience of running SQL express on a production server, or a shared hosters machine? Its been out now for nearly a year I think, so hopefully someone has the answer.

Thanks if you can help! Charlie

I agree I havn't seen may sites that do actually say that they support SQL 2005 Express, but you might want to look at www.asp.net as there was a list of hosting sites that were using .net 2.0... maybe you might find one there.

|||Thanks Glenn, you know I think thats where I started, several weeks ago, I suppose I ought to grind through all the small print again, maybe easier to change to SQL 2005!|||Webhost4Life claims to be hosting sql2005express|||Thanks for the suggestion. In the meantime, I''ve found www.titaninternet.com which successfully hosts sql2005express and has excellent tech support who helped me to get my website running in just a couple of days which i felt was quite good going. Cheers.|||Charlie many sympathies with ur problem as my colleague an I have been from host to host with the same problem.Can you confirm that u are using sql server management studio express and VWD express. Also since u've been with titaninternet can you confirm your login controls (aspnetdb.mdf) work fine as this is currently a MAJOR stumbling block for our database!!|||

I've just found this www.aspdotnet.sk don't really know how good it really is as I've only just found it.

Terms and conditions are not in english so I'm a bit dubious about using it, but then again at just over ￡12 per year for their Mini Hosting package could I really go wrong?

I'll leave it with you, hope you find it useful,

Marco

|||I'm not using the login controls at the moment. But my guess is that will be ok, since the staff at Titan seem honest and free of bullshit. They seem to know their way around the problems and issues with VWD express. I really recommend them. Good luck!

Do any ISP's really host SQL 2005 Express

Hi, I've been a SQL Server dba for about 10 years, so i know SQL quite well. I've recently started using VWD express, and as it says, its quick and easy to create a website, which I've done. It all works great on my local machine. I've just signed up with an isp and duly ftp'd the project files plus the mdf and ldf that lives in App_Data into their content area, and rather naively I expected it all to work. The pages come up fine until some database access is called for, then i get the seemingly infamous error:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

I've researched this for 2 solid days now, examined forums and posts, followed advice and enabled protocols and so on. Support at the isp in question does not really have a clue about databases and connections strings. Then I started looking closely at the words used by the isp's, none of the isp's explicitly say they support sql 2005 express. They do say SQL 2005, but not express. They do say VWD Express, but not SQL 2005 Express. So does anyone know what is/is not possible with regard to deployment of SQL 2005 express. Does anyone have experience of running SQL express on a production server, or a shared hosters machine? Its been out now for nearly a year I think, so hopefully someone has the answer.

Thanks if you can help! Charlie

I agree I havn't seen may sites that do actually say that they support SQL 2005 Express, but you might want to look at www.asp.net as there was a list of hosting sites that were using .net 2.0... maybe you might find one there.

|||Thanks Glenn, you know I think thats where I started, several weeks ago, I suppose I ought to grind through all the small print again, maybe easier to change to SQL 2005!|||Webhost4Life claims to be hosting sql2005express|||Thanks for the suggestion. In the meantime, I''ve found www.titaninternet.com which successfully hosts sql2005express and has excellent tech support who helped me to get my website running in just a couple of days which i felt was quite good going. Cheers.|||Charlie many sympathies with ur problem as my colleague an I have been from host to host with the same problem.Can you confirm that u are using sql server management studio express and VWD express. Also since u've been with titaninternet can you confirm your login controls (aspnetdb.mdf) work fine as this is currently a MAJOR stumbling block for our database!!|||

I've just found this www.aspdotnet.sk don't really know how good it really is as I've only just found it.

Terms and conditions are not in english so I'm a bit dubious about using it, but then again at just over ￡12 per year for their Mini Hosting package could I really go wrong?

I'll leave it with you, hope you find it useful,

Marco

|||I'm not using the login controls at the moment. But my guess is that will be ok, since the staff at Titan seem honest and free of bullshit. They seem to know their way around the problems and issues with VWD express. I really recommend them. Good luck!

Do any ISP's really host SQL 2005 Express

Hi, I've been a SQL Server dba for about 10 years, so i know SQL quite well. I've recently started using VWD express, and as it says, its quick and easy to create a website, which I've done. It all works great on my local machine. I've just signed up with an isp and duly ftp'd the project files plus the mdf and ldf that lives in App_Data into their content area, and rather naively I expected it all to work. The pages come up fine until some database access is called for, then i get the seemingly infamous error:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

I've researched this for 2 solid days now, examined forums and posts, followed advice and enabled protocols and so on. Support at the isp in question does not really have a clue about databases and connections strings. Then I started looking closely at the words used by the isp's, none of the isp's explicitly say they support sql 2005 express. They do say SQL 2005, but not express. They do say VWD Express, but not SQL 2005 Express. So does anyone know what is/is not possible with regard to deployment of SQL 2005 express. Does anyone have experience of running SQL express on a production server, or a shared hosters machine? Its been out now for nearly a year I think, so hopefully someone has the answer.

Thanks if you can help! Charlie

I agree I havn't seen may sites that do actually say that they support SQL 2005 Express, but you might want to look at www.asp.net as there was a list of hosting sites that were using .net 2.0... maybe you might find one there.

|||Thanks Glenn, you know I think thats where I started, several weeks ago, I suppose I ought to grind through all the small print again, maybe easier to change to SQL 2005!|||Webhost4Life claims to be hosting sql2005express|||Thanks for the suggestion. In the meantime, I''ve found www.titaninternet.com which successfully hosts sql2005express and has excellent tech support who helped me to get my website running in just a couple of days which i felt was quite good going. Cheers.|||Charlie many sympathies with ur problem as my colleague an I have been from host to host with the same problem.Can you confirm that u are using sql server management studio express and VWD express. Also since u've been with titaninternet can you confirm your login controls (aspnetdb.mdf) work fine as this is currently a MAJOR stumbling block for our database!!|||

I've just found this www.aspdotnet.sk don't really know how good it really is as I've only just found it.

Terms and conditions are not in english so I'm a bit dubious about using it, but then again at just over ￡12 per year for their Mini Hosting package could I really go wrong?

I'll leave it with you, hope you find it useful,

Marco

|||I'm not using the login controls at the moment. But my guess is that will be ok, since the staff at Titan seem honest and free of bullshit. They seem to know their way around the problems and issues with VWD express. I really recommend them. Good luck!sql

Do any ISP's really host SQL 2005 Express

Hi, I've been a SQL Server dba for about 10 years, so i know SQL quite well. I've recently started using VWD express, and as it says, its quick and easy to create a website, which I've done. It all works great on my local machine. I've just signed up with an isp and duly ftp'd the project files plus the mdf and ldf that lives in App_Data into their content area, and rather naively I expected it all to work. The pages come up fine until some database access is called for, then i get the seemingly infamous error:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

I've researched this for 2 solid days now, examined forums and posts, followed advice and enabled protocols and so on. Support at the isp in question does not really have a clue about databases and connections strings. Then I started looking closely at the words used by the isp's, none of the isp's explicitly say they support sql 2005 express. They do say SQL 2005, but not express. They do say VWD Express, but not SQL 2005 Express. So does anyone know what is/is not possible with regard to deployment of SQL 2005 express. Does anyone have experience of running SQL express on a production server, or a shared hosters machine? Its been out now for nearly a year I think, so hopefully someone has the answer.

Thanks if you can help! Charlie

I agree I havn't seen may sites that do actually say that they support SQL 2005 Express, but you might want to look at www.asp.net as there was a list of hosting sites that were using .net 2.0... maybe you might find one there.

|||Thanks Glenn, you know I think thats where I started, several weeks ago, I suppose I ought to grind through all the small print again, maybe easier to change to SQL 2005!|||Webhost4Life claims to be hosting sql2005express|||Thanks for the suggestion. In the meantime, I''ve found www.titaninternet.com which successfully hosts sql2005express and has excellent tech support who helped me to get my website running in just a couple of days which i felt was quite good going. Cheers.|||Charlie many sympathies with ur problem as my colleague an I have been from host to host with the same problem.Can you confirm that u are using sql server management studio express and VWD express. Also since u've been with titaninternet can you confirm your login controls (aspnetdb.mdf) work fine as this is currently a MAJOR stumbling block for our database!!|||

I've just found this www.aspdotnet.sk don't really know how good it really is as I've only just found it.

Terms and conditions are not in english so I'm a bit dubious about using it, but then again at just over ￡12 per year for their Mini Hosting package could I really go wrong?

I'll leave it with you, hope you find it useful,

Marco

|||I'm not using the login controls at the moment. But my guess is that will be ok, since the staff at Titan seem honest and free of bullshit. They seem to know their way around the problems and issues with VWD express. I really recommend them. Good luck!

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
Error
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)
OK
Any idea what I'm missing here?
Thanks,
Sarah
DMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah
|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>
|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
--
Error
--
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)
--
OK
--
Any idea what I'm missing here?
Thanks,
SarahDMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>>I have an applications that uses DMO to access SQL Server 2000.
>> When I tried to run that app against SQL Server 2005 Express CTP,
>> I got the below error:
>> --
>> Error
>> --
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> To connect to this server you must use SQL Server Management Studio or
>> SQL Server Management Objects (SMO)
>> --
>> OK
>> --
>> Any idea what I'm missing here?
>> Thanks,
>> Sarah
>|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
>> DMO is not supported on SQL2005. You need to use SMO.
>> Chris Wood
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
>> DMO is not supported on SQL2005. You need to use SMO.
>> Chris Wood
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

DMO doesn't work with SQL Server 2005 (Express)?

I have an applications that uses DMO to access SQL Server 2000.
When I tried to run that app against SQL Server 2005 Express CTP,
I got the below error:
Error
--
[Microsoft][ODBC SQL Server Driver][SQL Server]
To connect to this server you must use SQL Server Management Studio or SQL S
erver Management Objects (SMO)
--
OK
--
Any idea what I'm missing here?
Thanks,
SarahDMO is not supported on SQL2005. You need to use SMO.
Chris Wood
"Sarah" <SarahBram@.HotMail.com> wrote in message
news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>I have an applications that uses DMO to access SQL Server 2000.
> When I tried to run that app against SQL Server 2005 Express CTP,
> I got the below error:
> --
> Error
> --
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)
> --
> OK
> --
> Any idea what I'm missing here?
> Thanks,
> Sarah|||Hello Chris,
If I complie in SMO will it be backward compatible with SQL 2000 or do I
need to include both in my applications?
Regards,
John
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OZiB6lzrFHA.248@.TK2MSFTNGP14.phx.gbl...
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
> "Sarah" <SarahBram@.HotMail.com> wrote in message
> news:%23xZE7FzrFHA.904@.tk2msftngp13.phx.gbl...
>|||Chris Wood wrote:
> DMO is not supported on SQL2005. You need to use SMO.
> Chris Wood
Chris,
There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
Microsoft decided they didn't want to make upgrading DMO apps any more
difficult than they needed to be. The new SQL-DMO is supposed to work
with SQL 2000 and 7. I'm not sure when it will be available for
download if you're not a beta member.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Hopefully the new version of DMO comes with the September CTP and before the
RTM (Gold) build.
Thanks
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David,
Did this make it with the September CTP?
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:Om36c$zrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Chris Wood wrote:
> Chris,
> There is a SQL Server DMO in Beta now for SQL Server 2005. I'm guessing
> Microsoft decided they didn't want to make upgrading DMO apps any more
> difficult than they needed to be. The new SQL-DMO is supposed to work with
> SQL 2000 and 7. I'm not sure when it will be available for download if
> you're not a beta member.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

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

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

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

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of Application with SQL Server DB

If I create a Window's application that uses a MS Sql Server DB created with the express edition, do I need to get permission or submit royalties for distribution of my application? If so, where can I find the required info? I have been Googling without success and want to understand what is involved.

My projects are mostly for fun and my own amazement at this point, but I thinking about creating something that may actually become a product and need some guidance before I get much further along with it.

You may freely distribute SQL Server Express Edition.

See this link to register and for more details.

SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx

The following may help you customize your own installer for SQL Server.

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

|||Thanks Arnie. Very encouraging info.