How to detach properly using SQL Server Express?

It’s easy to attach a database file in the connection string using AttachDbFilename. And that file gets closed when all connections to that database are closed (one might have to clear the connection pool, or set the connection to use master). The database file can now be moved or deleted. Sounds good...

...but that database still shows up in the attached databases! (select * from sysdatabases). So over time one could end up with hundreds of attached databases (especially if an application uses the attached databases like document files that users open and close frequently).

Question 1: Is there a performance hit having hundreds of attached databases?

OK, one can try to detach the database when the application is done with a specific file, using sp_detach_db after checking if other connections to that database are open (SELECT count(*) FROM master.dbo.sysprocesses WHERE dbid = DB_ID()). But that still does not detach if the application exists unexpectedly, or if one does not have a connection available anymore (e.g. during finalization when exiting the application). There should be a better way.

Question 2: Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

Question 3: Is there a connection string property that auto-detaches when the connection is closed?

Thanks for any tips

--Ralf

Is there a performance hit having hundreds of attached databases?

Depends on how hard this databases are being used. Perhaps, it would be hard a little to navigate through them in Management Studio or other management tool, but I think it's all.

Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

It looks strange that application attaches database everytime it connected to the server and detaches database everytime it disconneted. Imho, it's unnecessary cycle - just keep databases attached.

especially if an application uses the attached databases like document files that users open and close frequently

Sounds not good. What's the reason to have multiple databases instead of one?

|||

The reason for having multiple databases that are attached and detached frequently is that the user uses our app to open a database just like he would use Word to open a word file. We need to store data acquired from an instrument in easily movable files.

I thought this would be a typical use case for SQL Server Express.

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?
Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>
|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel[vbcol=seagreen]
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema
> ?
>
|||Why not make "Education Certificate" a dimension all of its own and then have a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema[vbcol=seagreen]
> ?
>|||Why not make "Education Certificate" a dimension all of its own and then hav
e a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

How to deploy an report and connection string from some xml file

Suppose we are two developer.Who have SSRS install at there own system.
Our database server is intalled on our main db server.

1,First thing does it necessay to create a shared datasource.
I have a web application in which I want's to add a folder say My reports in which i will put my reports {that is .rdl file}
Then I want's to hit a url with render format as excel this will bring down my report in excel format.

2,How to configure a connection
I want's to pass the connection string in the rdl via some xml file let say myconnectionn xml file
which look like
<connection-sources>
<source name="Mycon1" default="true" >
Data Source=abc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName1
</source>
<source name="Mycon2" skip="true" >
Data Source=asdsadabc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName2
</source>
</connection-sources>

connection string would be where attribute default of source should be true.

1. Shared data sources are used to define a connection once, and share it among multiple reports. That way, if the connection information changes, you only have to update the shared data source, and not each report. You don't have to use shared data sources, though. Data sources can be defined at a report level as well.

2. You can create a connection string from an expression, which would allow you to change the connect string at runtime based on parameters passed to the report. However, as far as I am aware, you cannot use an XML file directly to set the connect string.

|||

Thanks jwelch.

Then any work around ?

|||Use an expression based connection and pass your connection string (or components of it) in as parameters.|||

What is an expression based connection and how can I pass my connection string (or components of it) in as parameters.

Please let it clear a little bit ?

|||

You can create an expression based connection by going to the data tab of your report. Open up the dataset properties (by clicking the ellipsis (...) beside the dataset dropdown), and then click the ellipsis next to the data source. If the Use shared reference option is checked, uncheck it. You should then be able to select the fx button next to the connection string box. You can enter an expression into the resulting dialog, such as:

Dynamic Connection String

="Data Source="+Parameters!Server.Value+";Initial Catalog="+Parameters!Database.Value

You'll need to create the parameters on the report to hold the values you want, and you may need to alter the connection string in my example to match your database provider.

Hope this helps.

How to deploy an report and connection string from some xml file

Suppose we are two developer.Who have SSRS install at there own system.
Our database server is intalled on our main db server.

1,First thing does it necessay to create a shared datasource.
I have a web application in which I want's to add a folder say My reports in which i will put my reports {that is .rdl file}
Then I want's to hit a url with render format as excel this will bring down my report in excel format.

2,How to configure a connection
I want's to pass the connection string in the rdl via some xml file let say myconnectionn xml file
which look like
<connection-sources>
<source name="Mycon1" default="true" >
Data Source=abc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName1
</source>
<source name="Mycon2" skip="true" >
Data Source=asdsadabc\yukon;User ID=sa;Password=as;Initial Catalog=MyDbName2
</source>
</connection-sources>

connection string would be where attribute default of source should be true.

1. Shared data sources are used to define a connection once, and share it among multiple reports. That way, if the connection information changes, you only have to update the shared data source, and not each report. You don't have to use shared data sources, though. Data sources can be defined at a report level as well.

2. You can create a connection string from an expression, which would allow you to change the connect string at runtime based on parameters passed to the report. However, as far as I am aware, you cannot use an XML file directly to set the connect string.

|||

Thanks jwelch.

Then any work around ?

|||Use an expression based connection and pass your connection string (or components of it) in as parameters.|||

What is an expression based connection and how can I pass my connection string (or components of it) in as parameters.

Please let it clear a little bit ?

|||

You can create an expression based connection by going to the data tab of your report. Open up the dataset properties (by clicking the ellipsis (...) beside the dataset dropdown), and then click the ellipsis next to the data source. If the Use shared reference option is checked, uncheck it. You should then be able to select the fx button next to the connection string box. You can enter an expression into the resulting dialog, such as:

Dynamic Connection String

="Data Source="+Parameters!Server.Value+";Initial Catalog="+Parameters!Database.Value

You'll need to create the parameters on the report to hold the values you want, and you may need to alter the connection string in my example to match your database provider.

Hope this helps.