Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:
> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:
> HI
> "Bahman" wrote:
> > Hi!
> >
> > The SQL Server Express run several versions of an Archive file, such as
> > Archive #1, etc. under 'SQL Server Logs'.
> >
> > What is generating these archives? Also, How can I see what has been
> > archived? Can I stop the server from doing this or needed for to operate?
> >
> > Thank you very much!
> >
> > -Bahman
> >
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>sql

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-Bahman
HI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John
|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created whe
n
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG
.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is i
n
> Books online.
> John
>

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Applying SQL 2005 SP1 to a local copy of Workstation Components

I have installed the Workstation components on my local PC, which also contains VS 2005 and SQL Express. I installed the WS components so that I could run the full blown version of SQL Management Studio from my PC, to remove SQL 2005 servers.

I tried to update the Workstation components to SP1, but the SP1 package stops at the Setup Support Files, step and terminates claiming the package does not support SQL Express. I realize the package is not intended for Express, I simply need to update Management studio.

I tried running the Post SP1 Tools hotfix and it complains that it will not update SP0 of the tools.

I have successfully updated Express to SP1 and applied the Post hotfixes as well. Is there any way to patch just Management studio on a system with Express installed?

Thank,
Jim

Jim, were you able to get this installed? Your scenario sounds valid for sure and I'd like to hear if you got this resolved.

Thanks,

Sam

|||Sam,

No I never got this installed.... I ended up removing the Workstation components and installing Management Studio Express. I wanted to keep the build of mgt. studio inline with the build of SQL Server 2005. I'm hurting now as the management tools are missing from the express version.

I'm sorry about the delay in responding to this. I stopped watching the post when I didn't get any answers. I would really like to get this problem fixed....

Jim

Applying SP1 on Sql Server Express production environment

How do you apply Service Pack 1 to Sql Server Express 2005?Hi,

this can be directly read from the setup instructions:

http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).

So, my cuestion is: How can I tell Report Server to use the application role to display reports?

Thank you for your time and help.

Giber

Your report application should also set the application role, which should have access to the database.

Thanks
Laurentiu

|||

I develop my Server Reports in SQL Server Business Intelligence (VS2005)

I can't seem to find where or how to tell BI how to connect to the DB using the application role.

To connect im using just: "Data Source=localhost\SQLEXPRESS;Initial Catalog=ConsultasSql" in the data view seccion of the report (rdl) designer.

Greetings

|||

I am not familiar with Server Reports or SQL Server Business Intelligence, but hopefully explaining how application roles work may be useful.

Application roles or approles are database-scoped principals that require no permissions (other than being able to access the database where they are defined) to be set as the execution context at run time, but require the knowledge of a password. Approles can only be set at ad-hoc level, this means that the call to sp_setapprole cannot be part of a stored procedure body or within a user defined transaction. More detailed information is available on BOL: Sp_setapprole http://msdn2.microsoft.com/en-us/library/ms188908.aspx

If the Server Reports definition allow you to define ad-hoc statements, it may be possible to call sp_setapprole as the first statement. I would recommend asking for information on how to customize these reports in the SQL Server Integration Services forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1).

An alternative could be to use the new impersonated mechanism (EXECUTE AS) or digital signatures in a stored procedure or user defined function to retrieve the data from the database without granting permissions beyond executing the SP. You can find more information on this topic in BOL, a good starting point is Context switching: http://msdn2.microsoft.com/en-us/library/ms188268.aspx

-Raul Garcia

SDE/T

SQL Server Engine

|||

Im putting this thread under SQL Reporting Services Forum.

Also Im going to check that Execute As clause.

Tnx a lot

Giber

Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user computer account (domain/user) to read the database. No user (besides admin and application role) has access permissions in the database.

So, my cuestion is: How can I tell Report Server to connect to the DB using the application role in the DB?

Thank you for your time and help.

Giber

I don't see how you could do this. You could try experimenting by placing the call to sp_setapprole within the query window just before the select to see if it would execute the query in the correct context. I'm not sure whether RS reuses connections to execute multiple dataset queries so you may need to put this in every query. Like I said, never tried it, so you'll need to experiment.|||

All my reports use stored procedures to populate the DataSet.

I put Execute As in the stored procedure to impersonate the user:

ALTER PROCEDURE [dbo].[usp_Cheque]

@.CRS nvarchar(50)

EXECUTE AS 'dbo'

AS

BEGIN

DECLARE @.Temp AS nvarchar(50)

SET NOCOUNT ON;

CREATE TABLE #Temp(CRS BigInt)

WHILE LEN(@.CRS) > 0

IF CHARINDEX(',',@.CRS) = 0

BEGIN

SET @.Temp = @.CRS

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = ''

END

ELSE

BEGIN

SET @.Temp = LEFT(@.CRS,CHARINDEX(',',@.CRS)-1)

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = RIGHT(@.CRS,LEN(@.CRS)-LEN(@.Temp)-1)

END

END

SELECT

Nombre,

NomCheque,

NumCR,

FechaCR,

Importe

FROM

CatProveedores INNER JOIN ContraRecibos

ON CatProveedores.CveProveedor = ContraRecibos.CveProveedor

WHERE

NumCR IN (SELECT CRS FROM #Temp)

But Im still getting that EXECUTE PERMISSION denied on usp_Cheques .....dbo' Error when trying to display the report

I have to give users Domain\Administrator Windows Role so they view the reports and then change their users back to Domain\Users when reporting is done...

|||

Let me try to explain it again (my english is not so good)

When a user try to log in the database (throught my application), the application run a setapprole. So the user is not the one connecting directly to the database, if fact, no user can connect to the database (only domain\administrators). Is the application role (that as all privileges) the one that give access to tables, sp, view, etc...

How can I do so in Reporting Services? RS tries to enter the database as the computer user, but as I said before, no computer user can enter the database, I need to tell RS that when trying to connect to the database, run the set approle command.

How do I do that?

Thank's for your attention.

Giber

|||

Hi Giber,

What are the credentials you are using for your datasource for stored procedures?

If you use sa or some admin role for data source credentials, the report should be able to run the stored procedure in the database. You can try running the report(preview) in local mode.

Also, if you are trying to control access for your reports, you can create different roles and groups and assign groups to these roles in SQL Server 2005 Reporting services. Or you can control the access for each report by giving appropriate permissions directly to that particular user or group. You will have to manuaaly ensure that the groups you make for Reporting Services match with the ones in SQl Server database.

Hope this will solve your problem.

Regards

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application not connecting to DB on restart of PC?

Hi,

i am stuck in a strange situation.
I

have successfully built my application setup through InstallSheild 12.

Which first installs SQL Express user define instance as a

pre-requisite and then install my application files. First time when i

run my application (without restarting the pc), it connects

successfully with user define instance.
But as i restart my PC and try to open my application, it is not connecting with database.
i am using the following command line to install SQL Express 2005 user define instance:

"/qn ADDLOCAL=SQL_Engine INSTANCENAME=MyInstance SECURITYMODE=SQL SAPWD="test" AUTOSTART=1"

if

i change Remote connection to "using both TCP/IP and named pipes"

through "SQL Server Surface Area Configuration" and then restart my pc

again. My application connects successfully with Database.

Please

guide me where i am getting wrong in building setup. do i have to add

something in my command line to not get this error

Please provide more information...

What error are you getting?

How are you connecting to SQL Express? (i.e. connection string)

What is different from the first time you connect at installation and the second time you connect after re-boot?

Are you using ODBC, OLEDB or Shared Memory to connect?

Mike

|||Hi,
thanks for the reply.
i am installing my installer on WinXP machine.
i am using the following connection string to connect with database.

Connection String:
--
Connection.ConnectionString = "packet size=4096;user id=sa;pwd=test; data source = (local)\\MyInstance;initial catalog = MyDB";

Error getting when connecting to SQL Mangmnt Studio:
-
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: Shared Memory Provider, error: 40 -
Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

when first time i install , In "SQL Server Surface Area Configuration" it is checked on "Local Connection Only" and connects successfully.
When i restart my pc and open application it doesnt connect .I open "SQL Server Surface Area Configuration", still "Local Connection Only" is checked but when i check on "using both TCP/IP and named pipes".
and then start my applicaion it connects successfully.|||Hi,
i am using following namespace for connection object.
using System.Data.SqlClient;

application failed. when clicked on the sqlexpr.exe file.

Hello

I have the sql server 2005 express edition. the software stops during the setup and gives me a "failed" message. All other software of express edition are working fine. I am getting the problem only when I am dealing with sql server 2005 express edition. I made sure to deleate all the old version of sql server. uninstalled all the sql server files which were in my computer prior to download the new one. However I am still in troulbe.

As someone suggested that one should uninstale the sqlmsi file so I did that too. Still samething. Any help please......

Hi,

The event log should give you a clue what happened, eventually during the initial startup of the service.

Make sure that the SQL Server Service could start up properly. In the near püast a often and common error was that the rights provided within the SQL Server service was not priviledged to aquire the sufficient permissions for starting. So either make sure that you are using a account for starting the service which is priviledged to start as a service. Normally, Local System should be enough to start (commonly used), a (for me) better way is (if you have a domain context) is to use a domain account.

Some of the advantages are:

-There is much more security granularity over a domain account than over a system account
-If you later need network ressources you won′t get them with a Local System Account and have to switch to a domain account.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you very much sir thanks a lot its working now. :) i appreciated your support

Appending Records to the Existing MS SQL EXPRESS SERVER Table.

Dear All,

I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.

Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,

Now I want to append 1 more access Table(5500 records) to the existing table having same fields.

How to do this.Can any body tell me?

Thanks and Regards

mukesh

Hi mukesh,

You can append the imported records into the existing table using SQL Server import & Export Wizard.

1.Select Your database from SQL Server Management Studio

2.Right Click on Database and go to the Task->Import Data menu Item

3. SQL Server Import & Export Wizard will be open., choose ur data source. as MicrosoftAccess, and select the MDB file

4.Press Next to Move onChoose a Destination Page, and select your Database Name from dropdown

5.Press Next to Move onSpecity Table Copy or QueryPage, and selectcopy data from one or more tables or viewradio button.

6. Press Next to Move onSelect Source Table and View , Select your Source Table , and Destination Table and then pressEdit button,Column Mappings dialog box will be open, chooseAppend rows to the destination tabelradion button option ( it will append the new records with existing records, in your case ur new 5500 records will be apended with existing 10,000 records )

7Press Next, and then Press Finish.Import Process will be started.

Thanks

Best Regards,

Muhammad AKhtar Shiekh

SQL Server Import & Export Wizard

|||

Hi mukesh,

The way you imported the first table just load the second table but with a different name i.e Table2 to the same database and then you can use the query

Lets Table_1 is having 10000

Table_2 is having 5500

----------------

Insert into Table_1

Select * from Table_2

----------------

the simplest way to do the stuff...

Satya

|||

Thanks Mr.Akhhttar

But sir in management studio I can't find "Import and Export option" there r these option,"detach","shrink","backup","restore"& generate scripts.

thanks and regards

mukesh

|||

Thanks a lot,Mr.Satya

append data from a text file

How can I append data to an existing SQL Express table from a text file? The file contains over 50MBs of data. This will be a recurring task so naturally I would like to automate this. I am familiar with programming VC++ and VB but am not sure where to begin.

Since it's a larger file, you could use the BULK INSERT command:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

Or you could use bcp from a command line:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

Hope this helps,
Josh Lindenmuth

App_Data new DB fails

Hello,

I'm trying to create a new SQL Express database in VWD, and I get the error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

What is this error all about? I don't understand it.

Thanks.

The db when added in VS has no owner and is somehow bound to the profile. So many things can go wrong with that, so instead of guessing, wipe it out and let the IDE recreate it.

This blog saved me days of heartache. Unfortunately, I still wasted time till I came across it.

http://www.sqljunkies.com/WebLog/ktegels/archive/2005/11/15/17401.aspx

1. Disconnect all dbs, close down the IDE, close down SSMS(if any) on your machine.
2. Stop all services and processes related to sql server.
3. Rename(safer than deleting) the whole folder:
c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

4.restart the IDE/SSE.
5. this song-n-dance may not work the first time. Try more than once.

I am really disappointed at the flaky architecture of SSE and have stopped using it since.

HTH!

APP_DATA directory

If I already have SQL2005 installed, can I create SQL express databases for distribution in my app, or do I need to install SQLExpress to run side-by-side?

I actually had SQLExpress originally but upsized it to the full version. Now I want to be able to create portable DBs with my application.

And if this is possible, how do I go about creating the DB?

Thanks!

You can refer to the upgraded SQL2005 instance just as you did to SQL Express. One thing to note is that if you have set "User Instance" attribute to true in your connection string, the connection to SQL2005 may fail with error message saying "User Instance can only be used with SQL Express..." (not exact, but something like this)|||

Thanks for the answer.. that helps on that error that I have received...

I think I might have been a bit unclear though...

On my local dev machine, I have the full SQL2005 version, but I want to create an application that can be distributed with a SQLExpress database in its app_data directory. This application will not require that the user attaches the .mdf through sql2005, but only that they have SQLexpress running as the DB will live locally in the APP_DATA directory.

Question is: Can I create the DB through my version and then just drop the .mdf in the APP_DATA directory and will it be compatible with SQLExpress?

Also, can I use a SQLExpress connection string / DB on my system during development without having to attach the DB to my sqlserver instance?

Sorry if my questions seem ignorant, but I am just trying to wrap my hands around the whole thing.

Thanks!

|||

NevermindIf you do not want to attache the database file at run time, you have to use a database in your SQL Server. That's because what your application needs is not only a database file, but also needs a SQL Server instance. So if you want to switch between SQL Express and SQL 2005 (means different SQL instances) without attaching the database file at run time, you have to change your connection to SQL, and move database as well. There are some options you can choose to move database:

How to move database using detach/attach:

http://msdn2.microsoft.com/en-us/library/ms187858(d=ide).aspx

And copy database with backup/restore:

http://msdn2.microsoft.com/en-us/library/ms190436(d=ide).aspx

This article shows a good torturial for changing SQL connections in web application:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Anyways, I recommend attaching the database file at run time--then you just need to change connection to SQL without moving database