Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback.

Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution.

Here is the table design I have so farhttp://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list...

Thoughts please. TIA

I would suggest the following as it pertains to the business logic of your task.

Create a table for the "week" with weekId and date (no time reqd). This is a central table that links most of the others together

Create a table formoney input or "Subscription" with a link to your dateId and user

You have a table formoney output - winnings which links to dateId.

You might also need to store more user details - maybe more on address, more phone numbers etc.

|||You are in the right direction about your table design while the previous advice not about table design, you can use Microsoft provided membership for your users and just add DRI(declarative referential integrity) to team, schedules and subscription fee tables, money you need to use Decimal or Numeric because you can set precision and scale. Hope this helps.

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:
> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>sql

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have key
s
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIA
Well, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic business rules question

How do you represent business rules in SQL Server? I need the database to return an error to a .net app if a business rule fails.

Thank you! :)

I'm not sure if this is what you mean, but you can use constraints (like CHECK CONSTRAINTS and foreign key constraints) and triggers to enforce business rules.|||Thanks. Yes, I mean something that allows me to create an elaborate procedure to verify compliance with a business rule. Are triggers the only way? I've heard negative things about triggers.|||If you can use constraints then you should because they're more optimal in performance (I think) - and you can use user-defined functions in the constraints to have fairly complex logic. But if not (because the rules are elaborate) I don't see what's wrong with triggers - they are a standard way of enforcing business rules. What negative things did you hear?|||Thanks, mostafae. I can't remember specifically what I heard; I just remember hearing that it's best to avoid them for some reason. But I don't see what the problem is, either. Thanks, again.|||

triggers can cause problems and many people (Self included) recommend against them

1. if the trigger contains much logic, it can hurt performance

2. if you're trouble shooting a slow stored procedure, you will NOT see the trigger code in any query plan. in fact, you wont even know the trigger exists unless you explicitly go look for it. This can cause you to spin your wheels trouble shooting the sproc, when the performance issue is in the trigger all along. (Maintenance nightmare)

3. it's preferable to use constraints

4. IF you must use triggers, be sure to kee them SIMPLE and FAST.

Cheers

|||

1. ... on the other hand, much logic might legitimately be needed for a particularly complex business rule. Is there another way to represent such a rule?

2. How disappointing.

3. Can constraints handle complex business rules that require some process to execute?

4. In what situations might you have no choice but to use Triggers?

:)

|||

well.....

therein lies the question and the topic of debate right...?

You may be forced to use triggers IF for some reason users are accessing your DB without going through a Business Tier.

If users can access the system and circumvent the Application, you could be hosed. If you want to be 100% certain that business logic ALWAYS Fires, regardless of how the DB is accessed, then you may be forced to use triggers (you poor soul).

However, if you're in an environment that allows users to access the DB directly, you have more problems than fiddling with triggers anyway.

The performance problem is not really triggers, it's "business logic" on the Database server. it would be the same thing with containing tons of logic in a sproc. You dont want to have a ton of business logic in a sproc (in my opinion) you want your sprocs to be "Primitive" in nature (Simple CRUD ops). The reason for this is not JUST performance, it's also scalability.

if you take complex logic in a sproc and emulate the same progic in compiled code (C# for example), it's going to perform better due to the fact that it's compiled. Furthermore, it's MUCH easier to scale application servers than DB Servers. In a SQL Cluster you can never have more then 3 boxes, so if your SQL Servers are doing a lot of Business processing you "could" hit the wall quite quickly (think LARGE scale applications like on-line banking, etc). IF you move most or all business logic to business tier (Not just logically, but physically to app servers), you have to ability so scale out as much as you need. You can have as many applications servers as you can afford to purchase.

so, Triggers are not necessarily evil, they are just a trade off in architectural decision making....

I hope this makes sense. Constraints are only going to allow you to implement "Simple rule Logic"

Cheers

|||

Thanks, pdxJaxon. That addresses my concerns, very well, actually.

sql

Barcode export to pdf solutions

Hey all,
I'm trying to decide on a barcode product for Reporting Services (SQL Server 2000 and .Net 1.1) so that I can export reports to pdf and retain the barcodes.
I've found IDAutomationhttp://www.idautomation.com/fonts/tools/reporting_services/ and NeoDynamicshttp://www.neodynamic.com/Products/BCRS/BarcodeRS.aspx?tabid=78&prodid=7 as a solution.
I was wondering if anyone has ever used either of these products to create reports for pdf export w/ barcodes? Or possibly another solution? If so, please let me know how it went.
Thanks,
Mike

Go forNeodynamic. Its product supports SSRS 2000 and in addition it's SSRS 2005 fully integrated :-)sql

bad query plan when using parameterised queries on partitioned vie

Recently I found rather a nasty consequence by using parameterised queries o
n
a partitioned view.
I have a .net app that sends parameterised queries to SQL Server 2000. SQL
Server obliges by caching the query plan to increase efficiency of subsequen
t
queries.
In my case the query is running against a partitioned view, partitioned by a
date constraint.
A query is run when the cache is cleared and the query optimiser correctly
creates a query plan specifying only the correct partition and ignoring the
other partitions, so far so good…
Subsequent queries run fine and use the cached query plan until we get a
query where the date range selected now relates to a different underlying
partition – blam, full table scan against all partitions! As you might gue
ss
the query execution times blow out by several orders of magnitude! It seems
it tries to use a bad query plan from the cache and when it realises it can
t
get the data it needs it is not smart enough to re-evaluate the query plan
properly.
Currently I am forcing a recompile on these guys go make sure the cached
plan is not used as it obviously cannot be trusted! This workaround does the
trick but I’m wondering if anyone has experienced something similar before
?
Or is there a better way to deal with this? It seems to me to be in fact a
bug as the optimiser should handle this a lot better than it does.
Thoughts anyone?
Cheers,
Chrissounds like parameter sniffing problem. Sure there are ways better than
WITH RECOMPILE. Search for "parameter sniffing" in this group or google.

Bad day with .net

Hi

I am new to ASP.net and trying to connecting with SQL Server in Visual Studio by following instruction are given in below url page.

http://www.codeproject.com/aspnet/SQLConnect.asp?

I have done all steps upto where below code is shown in page

*********************************************************

Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection1.Open()
If SqlConnection1.State = ConnectionState.Open Then
Label4.Text = "SQL Connection is open"
Else
Label4.Text = "SQL Connection is closed"
End If
End Sub

******************************************************

But I am getting error !

Server Error in '/WebService1' Application.

---

Login failed for user 'COMP3\ASPNET'.

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.SqlClient.SqlException: Login failed for user 'COMP3\ASPNET'.

Source Error:

Line 126: 'Put user code to initialize the page hereLine 127:Line 128:SqlConnection1.Open()Line 129:If SqlConnection1.State = ConnectionState.Open ThenLine 130: Label1.Text = "SQL Connection is open"

Source File:c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb Line:128

Stack Trace:

[SqlException: Login failed for user 'COMP3\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 WebService1.SqlConnect.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb:128 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

Note : All steps are finely executed by me !

One thing more As one instruction in Help Page that "right click theSQLConnection1 object to study its properties."

But i am not able to see the properties of it becoz properties tag is disable in right click and i am strange with this experience in Visual Studio that i am not able to edit the code in Editor area ( Where Below code in seen)

Private Sub Page_Load(ByVal sender As System.Object, _ByVal e As .EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

End Sub

So I am totally confused or unable to find out the problem that in my Visual Studio Or Connectivity?

PLEASE HELP ME TO RESOLVE this issue!!

Check your connection string and see if you are providing the correct credentials to access the database.|||

Connection String is automatically generated by Visual Studio.Net aftter dragging table 'employee' from server explorer

So I think it not changeable.

Suggest me other way to connecting with database

|||

See "SQL Server" in this document:How To: Use the Network Service Account to Access Resources in ASP.NET.

In your case, though, you are using the ASPNET account, not NETWORK SERVICE, but all the same steps still apply.

The connection string is still configurable, though.

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backups

Hi,

I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?

Any suggestions?

Thanks,

Dale

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

Thanks for that. In answer to your question, the easiest way I know to backup the site (not just the database) is to copy the entire site using FTP, included in the site is the database file database.mdf under App_Data which is auto-mounted.

Given that ASP.NET 2.0 comes with this database xcopy feature and the App_Data directory it seemed such a nice solution that I didn't consider the backup implications until now. Now I am wondering if anyone has used the App_Data directory for a live site and if so how they manage backups.

Thanks,

Caddre:

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Thanks for the comprehensive reply - I do agree with you although in this case its nothing as important as transaction data. BTW - using the App_Data directory you still get a mdf & ldf file, Its a normal SQL Server 2005 Express database, its just that its physically located in the website and its automatically mounted when access is required. In all other respects its the same.

I was assuming (probably a bad move) that because Microsoft has developed this architecture it was therefore a *good* way to do things. So far it has proved to be *good* but now maybe with the exception of taking a backup. Therefore I was also assuming (another bad move) that there was a way to backup such an architecture.

Caddre:

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.|||

Thanks again, so just to sumamrise you're recommending that I convert my 'user instance' database into a 'real' database? Can you clarify, are you also suggesting that I move the physical location of the database from the website to the SQL data directory?

You've been very helpful, I appreciate it.

Caddre:

Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.

|||

No I am not recommending you change the location just receate it through restore or reattach it. User instance is a database that is independent of the SQL Server engine with known issues that is covered by the SQL Server team in the blog entry below. Your database located in the App_Data directory is a User instance. What I am suggesting is Backup the User instance and Restore at the same folder location but this time it is not a user instance because when you choose the Restore location SQL Server engine maps it as the physical location of the database. Microsoft have also provided a guide to use Attach to change the location you are use the method that suits you purpose.

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||Thank you! Much appreciated!|||

CoolKiwiBloke:

Thank you! Much appreciated!

I am glad I could help.

Backup to Share with SMO fails, but works to local hard disk

I have a short program (vb.net) which backs up an SQLExpress database. It works fine if the destination is a file on the local drive. However, it always fails if the destination file is a network share or a mapped share. The same program verifies the share is available by trying to read and write a small file before performing the backup.

This is a stripped down version of the code

Dim svr As Server = New Server("Servername")
svr.ConnectionContext.LoginSecure = false
svr.ConnectionContext.Login = TextBoxBULogin.Text
svr.ConnectionContext.Password = TextBoxBUPwd.Text
Dim bkp As Backup = New Backup()
bkp.Action = BackupActionType.Database
bkp.Database = TextBoxBUDatabase.Text
Dim x As New Microsoft.SqlServer.Management.Smo.BackupDeviceItem(destfilepath, DeviceType.File)
bkp.Devices.Add(x)
bkp.SqlBackup(svr)

The exception generated is an SMO.FailedOperationException and reads "System.Data.SqlClient.SqlError: Cannot open backup device 'Z:\v1tbcontacts00.bak'. Device error or device off-line."

Where "Z:\" is a network share. If the backup device string is set to "C:\v1tbcontacts00.bak" the backup succeeds.

Have you tried to use Transact-SQL to backup to the share? In any case you may find it better to back up to the local drive, then copy the backup file using operating system commands to the network share for performance purposes.|||

Henry,

using SQL authentication will make SMO to use the SQL Server Sevice account to authenticate on the network share, so although you might be able to check the network share for existance within your application, the credentials used for this is not used for making the backup on the file share.

Client application (Windows token) --> SQL Server using Windows authentication and connected with Windows Authentication --> SMO uses the Windows credentials of the connected client to access the share

Client application (Windows token) --> SQL Server using Mixed Authentication and connected with SQL Server Authentication --> SMO uses the SQL Server Service account credentials of to access the share

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The practical solution to my issue is to perform a database backup to the local hard disk and then copy the backup to a share.

Better still. the nugget of information about how authentication works with SMO explains why the problem occured in the first place.

Thanks