Friday, March 30, 2012
How to autenticate to local SQL Express server
How to attach/deattach a Express database to a fullblown SQL Server 2005
You have two options both are covered in the thread below, if you have more questions post again. Hope this helps
http://forums.asp.net/1194348/ShowPost.aspx
How to attach a database from other server
Hi
I have created a test enviroment, by installing SQL server express and SSRS on my laptop.
I also have a production server, and i am trying to make a copy of one of its database and linked it to my test server.
How do i do it?
Thanks
Hello,
If you can take the database offline:
Take the database offline, manually copy the data and log files to your test server, then attach the database back on your production server, then on the test server.
If you can not take the database offline:
Create a backup of the database on your production server, then restore it to your test server.
Hope this helps.
Jarret
|||It works. Thanks1how to attach a .mdf file?
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>> You also need the ldf file. Are you clicking the Add button in the att
>> dialog box to browse for the file you want to attach?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>>
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> i installed sqlserver express 2005.
>> I also use the sql server management studio express. I can connect to
>> (myserver\sqlexpress), but when i want to attach any existing .mdf file
>> on my disc, the window remains empty and i have not the opportunity to
>> specify a path.
>> Any idea how to fix this?
>> Thanks
>> Bob
>sql
Wednesday, March 28, 2012
how to attach a .mdf file?
i installed sqlserver express 2005.
I also use the sql server management studio express. I can connect to
(myserver\sqlexpress), but when i want to attach any existing .mdf file on
my disc, the window remains empty and i have not the opportunity to specify
a path.
Any idea how to fix this?
Thanks
BobYou also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hello,
In the SQL Server management studio express; go to query window and execute
SP_ATTACH_DB if you have both MDF and LDF files. If you have only MDF file
try sp_attach_single_file_db command. Take a look into books online for
usage.
Thanks
Hari
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>|||Hi, thanks for replying. I thing i become crazy. I desinstalled and
reinstalled it 2 times, but it still doen't work.
Yes, i have the ldf file.
When i click on ADD button, i get an error telling me that "cannot access
the sprecified path etc ...Verify that you have specific privileges ..."
I tried to add "myserver\aspnet" as account (server properties,
permissions), but then, it's always un recognized ...
I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
could not open a connection to SQL Server"
And when i start an asp.net application, i get the error: "
An attempt to attach an auto-named database for file C:\myapp\my.mdf failed.
A database with the same name exists, or specified file cannot be opened, or
it is located on UNC share.
The only way it works is when doing ctrl-f5 in an application within VWD.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> You also need the ldf file. Are you clicking the Add button in the att
> dialog box to browse for the file you want to attach?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/d...>
userinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Hi, thanks for replying.
I started from the beginning but still with problems.
I post a new thread therefore.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schreef in bericht
news:e08sUAfLHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
> In the SQL Server management studio express; go to query window and
> execute SP_ATTACH_DB if you have both MDF and LDF files. If you have only
> MDF file
> try sp_attach_single_file_db command. Take a look into books online for
> usage.
> Thanks
> Hari
>
> "Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
>
How to assign primary key or forign key to existing tables
I am new to SQL server. I have one doubt.
I am using SQL server 2005 Express Edition
I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George
Quote:
Originally Posted by sijugeo
Hi,
I am new to SQL server. I have one doubt.
I am using SQL server 2005 Express Edition
I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George
Hi
You can do by searching in sql books online.
Ok......add primary key constraint on one column in one table using alter table add constraint.add refference constraint on one column in other table
example:
[code]
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GOsql
How to Assign Identity Key values in Replication
This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||thanks for your reply.
i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..
is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.
Monday, March 26, 2012
How to apply SQL Server 2005 Express SP1 to the version of SQL Server 2005 Express which install
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @.@.version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
http://msdn.microsoft.com/vstudio/express/sql/download/
Thank you,
Bashman
Follow the instructions on the readme file in the setup package. SQL Express SP1 is not a Service pack fix, its a full product. The instructions in the readme are very detailed to get through the process.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Its easy to just say go read the Readme!
Which Readme you talking about?
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005Advanced.htm
Neither of the two above Readme(s) tells me anything related to the VD2005 SQL 2005 Express that I am talking about.
If you know of any other Readme(s), please list the links!
Thanks for NOTHING!
|||Since I didn't get any meaningful answers, I decided to take a chance and see what happens if I just run the upgrade setup as is.
I made sure I had backups first, then I stopped the SQLEXPRESS service.
Run the setup and followed the instructions on the screen using the default answers for all except for selecting which instance to upgrade.
All went well.
So for anyone looking at this thread, the quick answer is backup and install as is you'll be fine!
Again, thanks for nothing!
|||Hi,its http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm
3.1 Prepare for a SQL Server Express SP1 Installation
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de|||
That's exactly the section I followed (Backup & Stop the service).
Other than that, the rest of the Readme didn't apply to my original question.
Also, good thing that you read the Readme now that I listed the link readly for your reading enjoyment!
Enjoy everyone,
Bashman
|||I lost you, what does "Also, good thing that you read the Readme now that I listed the link readly for your reading enjoyment!" mean ? Which readme do you expect if you are struggeling with SQL Server Express SP1 and you have problems with SQL Server Express SP1. The section if was talking about in my previous post was just about that, for making that clearer I posted you the link to the relevant readme.You asked the question which should be investigated at 06:08, at 06:12 you answered that you did not get any meaningful explanations, what do you expect the response times to be in a public forum, thats not a monitored chat or a paid support forum. So, you did not receive any answer in 4 minutes ? Thats really "horrible".
HTH, Jens SUessmeyer.
http://www.slqserver2005.de|||
Methinks the lady protests too much!
I always knew that my PC was fast, but I didn't know that it was that fast. Where I actually can backup my data & run a lengthy SQL server installation and post a reply in 4 minutes or less!
I realy have a FAST computer. If anyone is interested, I can list my PC specs for you!
Back to the subject, my reply to your "read the Readme" post was 24 hours after my original Post.
I see your answers on this board and they are usually typical of "read the Readme" kind of answers.
That's fine if you list or point to the Readme link and even better if you would point to the section of the Readme related to the answer of the question.
I did read the Readme(s) that I listed and found the section most apply to my question and took a chance on it.
Then I came back to the board and posted a reply to you and listed the two links (it took me about 4 minuts to write, slow typies but fast PC).
Four minutes later, I then listed what I did to get the job done.
I hope in the future when you answer with "Read the Readme(s)", you would try to point the link or the section most apply to the question/answer.
You know, I could just have gone and ignured this board "because I didn't get any answers from it".
But I came back and listed the Readme(s) and listed what I did so that the next time someone looking for this question/answer, would find something meaningful other than "Read the Readme(s)".
I don't know how long this is talking me to type, but I am the slow typest with a fast PC,
Bashman
|||From you post it seems that you didn′t know where to look at: "Neither of the two above Readme(s) tells me anything related to the VD2005 SQL 2005 Express that I am talking about." My assumptions was that you are smart enough to figure out that I ment the SQL Server Express SP1 readme, sorry for that. So I gave you another hint to look into that special readme under the mentioned section. Nevertheless, good that you fixed your problem.HTH, Jens Suessmeyer.|||
You want the Credit, you got the Credit!
Yes, whithout your hint I was lost but then I was found! I realy didn't found my why without your reply!
I need your hand holding to lead me to the right Readme file.
By listing the two links, I showed you how loast I was!
But once your hint gave my the way, I managed to resolve my own problem in less than 4 minutes using my fast PC!
Thank you, Thank you, Thank you!
|||OK, you didn′t get it yet. Its just about being polite which you weren′t.How to apply SQL Server 2005 Express SP1 to the version of SQL Server 2005 Express which ins
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @.@.version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
http://msdn.microsoft.com/vstudio/express/sql/download/
Thank you,
Bashman
Follow the instructions on the readme file in the setup package. SQL Express SP1 is not a Service pack fix, its a full product. The instructions in the readme are very detailed to get through the process.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Its easy to just say go read the Readme!
Which Readme you talking about?
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm
http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005Advanced.htm
Neither of the two above Readme(s) tells me anything related to the VD2005 SQL 2005 Express that I am talking about.
If you know of any other Readme(s), please list the links!
Thanks for NOTHING!
|||Since I didn't get any meaningful answers, I decided to take a chance and see what happens if I just run the upgrade setup as is.
I made sure I had backups first, then I stopped the SQLEXPRESS service.
Run the setup and followed the instructions on the screen using the default answers for all except for selecting which instance to upgrade.
All went well.
So for anyone looking at this thread, the quick answer is backup and install as is you'll be fine!
Again, thanks for nothing!
|||Hi,its http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm
3.1 Prepare for a SQL Server Express SP1 Installation
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de|||
That's exactly the section I followed (Backup & Stop the service).
Other than that, the rest of the Readme didn't apply to my original question.
Also, good thing that you read the Readme now that I listed the link readly for your reading enjoyment!
Enjoy everyone,
Bashman
|||I lost you, what does "Also, good thing that you read the Readme now that I listed the link readly for your reading enjoyment!" mean ? Which readme do you expect if you are struggeling with SQL Server Express SP1 and you have problems with SQL Server Express SP1. The section if was talking about in my previous post was just about that, for making that clearer I posted you the link to the relevant readme.You asked the question which should be investigated at 06:08, at 06:12 you answered that you did not get any meaningful explanations, what do you expect the response times to be in a public forum, thats not a monitored chat or a paid support forum. So, you did not receive any answer in 4 minutes ? Thats really "horrible".
HTH, Jens SUessmeyer.
http://www.slqserver2005.de|||
Methinks the lady protests too much!
I always knew that my PC was fast, but I didn't know that it was that fast. Where I actually can backup my data & run a lengthy SQL server installation and post a reply in 4 minutes or less!
I realy have a FAST computer. If anyone is interested, I can list my PC specs for you!
Back to the subject, my reply to your "read the Readme" post was 24 hours after my original Post.
I see your answers on this board and they are usually typical of "read the Readme" kind of answers.
That's fine if you list or point to the Readme link and even better if you would point to the section of the Readme related to the answer of the question.
I did read the Readme(s) that I listed and found the section most apply to my question and took a chance on it.
Then I came back to the board and posted a reply to you and listed the two links (it took me about 4 minuts to write, slow typies but fast PC).
Four minutes later, I then listed what I did to get the job done.
I hope in the future when you answer with "Read the Readme(s)", you would try to point the link or the section most apply to the question/answer.
You know, I could just have gone and ignured this board "because I didn't get any answers from it".
But I came back and listed the Readme(s) and listed what I did so that the next time someone looking for this question/answer, would find something meaningful other than "Read the Readme(s)".
I don't know how long this is talking me to type, but I am the slow typest with a fast PC,
Bashman
|||From you post it seems that you didn′t know where to look at: "Neither of the two above Readme(s) tells me anything related to the VD2005 SQL 2005 Express that I am talking about." My assumptions was that you are smart enough to figure out that I ment the SQL Server Express SP1 readme, sorry for that. So I gave you another hint to look into that special readme under the mentioned section. Nevertheless, good that you fixed your problem.HTH, Jens Suessmeyer.|||
You want the Credit, you got the Credit!
Yes, whithout your hint I was lost but then I was found! I realy didn't found my why without your reply!
I need your hand holding to lead me to the right Readme file.
By listing the two links, I showed you how loast I was!
But once your hint gave my the way, I managed to resolve my own problem in less than 4 minutes using my fast PC!
Thank you, Thank you, Thank you!
|||OK, you didn′t get it yet. Its just about being polite which you weren′t.Wednesday, March 21, 2012
How to add/(connect to) database in SQL Server 2005 Express?
I just downloaded and installed the Visual Web Developer 2005 Express which is part of the new issued Visual Studio 2005 Express. It includes SQL Server 2005 Express, so I also installed it.
My problem is I can't connect to the database supported by SQL Server Express. The log in is failed.
I hope anyone who is using SQL Server Express can give me a help and tell me what wrong I've done.
What I've done are:
1. In the Database Explorer, right-click the Data Connections, I create a new database named "lavender", so it's "lavender.mdf".
2. Then I go to Tools->"connection to database...",
(1) then in the pop-up window, I chose the "Data Source" as "MS SQL Server Database File(SqlClient)". I ever tried to select "MS SQL Server", but it couldn't pass through the following step(4), not sure if SQL Server Express can only be chosen under "MS SQL Server Database File"?;
(2) Then database file name, which is to attach to the SQL Express Server, is "C:\Inetpub\wwwroot\lavender\App_Data\lavender.mdf";
(3) Then "log on to Server", I use "Windows authentication";
(4) Then I "test the connection", it works.
3. Then I create some table in the lavender.mdf database under the Database Explorer.
But when I run the connection.Open() code and some other sql codes inside a vb file in this lavender project, it can "successfully build" Website without error, but can't show me results in IE6, only shows me the following error:
Server Error in '/lavender' Application.
Cannot open database "lavender" requested by the login. The login failed.
Login failed for user 'HENRYPC\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: Cannot open database "lavender" requested by the login. The login failed.
Login failed for user 'HENRYPC\ASPNET'.
Source Error:
Line 8: Dim command As New SqlCommand("GetDepartments", connection) Line 9: command.CommandType = Data.CommandType.StoredProcedure Line 10: connection.Open() Line 11: Return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Line 12: End FunctionSource File: c:\inetpub\wwwroot\lavender\App_Code\Catalog.vb Line: 10
Stack Trace:
[SqlException (0x80131904): Cannot open database "lavender" requested by the login. The login failed. Login failed for user 'HENRYPC\ASPNET'.] I tried many different ways to connect to my "lavender" database before I seek help here. Really feel tired and don't know which point I've configured wrong.
I have windows IIS, and the Website can show up if there is no these sql codes or no need to connect to database. I am still not clear if my lavender database is already located in the SQL Server Express. If already there, how can I connect to it? Why log in fail while I am the only user of my computer and using Windows Authentication?
Thank you very much for your help! Have a good week!
Henry LiHenry,
I don't pretend to be a sql guru, but something that looks odd, is that in your error, it is indicating attempting to use windows authentication with the ASPNET user account. Even though you are the one logged onto your box, IIS uses the ASPNET to run .Net applications, not your userid. You will need to give the ASPNET account access to your database if you want to use windows authentication. I would imagine that you have set your database up to just allow your userid access.
Incidentally the reason, it works in Visual Studio is that Visual Studio authenticates using the credentials of the person logged into the box. Hence it is using your user account.
Either add ASPNET to your database or create a new login and password in SQL Server and don't use windows authentication.|||Hello Jon,
Really thanks for your quick help! I reckon you've already found out where I'd done wrong.
I just wish you could give me a detailed instructions how to add the ASPNET into my database account. And sorry as I am a beginner just setting up the SQL Server (Express) and VS Express, also a beginner handling with database or server.
And when you said "ASPNET account access to my database", do you mean my whole database or just my lavender.mdf data file(it's a single file holding a lot of forms & stored procedures inside it)?
If you mean my whole database, do you mean my SQL Server Express, are these two the same thing? I am also curious whether my lavender.mdf is already in my SQL Server database.
What I want to know most is how and where to set up the ASPNET account in my database (or in my SQL Server Express?). Could you please kindly tell me in details? Thank you in advance. I am not going to set it as SQL authentication with User ID & password at this moment, just because I am not so sure if I can do it well.
By the way, how to set the paramenters in the web.confi file? Is the code below correct?
<appSettings>
<add key="ConnectionString" value="Server=.\SQLEXPRESS;Integrated Security=True;Database=lavender.mdf" />
</appSettings>
Thank you very much indeed!
Henry Li|||Henry,
At the present time, I'm having issues with my SQL Server install as well, so I'm afraid I can't give you a clear walk through of adding the necessary security permissions to your server.
However, for the moment, I can answer some of your questions.
First off, when I referred to database, I was referring to your lavendar.mdf database. SQL Server Express is an engine that can contain multiple databases. Each *.mdf file is considered an individual database that SQL Server Express understands.
Second, while it is possible to add security to all your databases, if you're going to use windows authentication, it would be best to add just the individual account to the individual database. That way you don't open up holes to all your other databases you might have or create in the future.
Thirdly, I'm very familiar with how to add security to a database in SQL Server 2000, but I'd hesitate to tell you that is how it is done in SQL Server Express. As soon as I can get mine installed and working correctly, I'll let you know detailed steps.
On a side note, your connection string looks fine to me and the error you were getting also indicates that your web application can find the database just fine. It just can't authenticate to him.|||Hello Jon,
It's very nice of you. Thank you indeed!
Your reply gives me a better view of my problem.
Let me share how I install SQL Server Express.
I installed the SQL Server Express as a component of Visual Web Developer 2005 Express (440MB), free downloaded from microsoft.com. I don't know what problem you met with your installation, and as I am not a professional, I am happy to tell what I've done but not sure if it will help.
Visual Web Developer 2005 Express is part of recent release of the Visual Studio 2005 Express, it's a single file WEB.img(440MB), I downloaded it and then used the Nero Burn Room CD burning software to burn it into a CD, then it became scores of files. Then it started automatically the installation, which includes 4 components, the Windows installer 3.1, the VWebDep Express, MSDN Express, and SQL Server 2005 Express.
As I just upgraded my computer from XP Home to XP professional 4 days ago, and just set it up as a local server with the help of IIS 3 days ago, I don't know how to configure the database and the connection. I also got the MSQL Desktop Engine(MSDE) installed in my computer 3 days ago before I installed the SQL Express, they get on well with each other very well.
--
I wish you could give me a hint how you configure your SQL Server 2000, so that I can have some idea how to deal with the Express.
What I've done with SQL Express is: (but can't go throught):
I opened the "SQL Server Configuration Manager" and tried to find where I can configure database log on. Under the "SQL 2005 Server Service" -> "SQL Server(SQLEXPRESS)", right-click it for properties, it gives me 3 tabs.
One tab is "Log on as", there are 2 choices, one is "built-in account" which include 3 choices, local system, local service, and nextwork service.
The other choice is "this account" with account name and password and confirm password for me to enter. I don't know what it means. Is it for the system admin to log in or for creation of endusers' log-in? It has a "browse" button beside account name to browse some names.
But when I enter ASPNET as an account name, and give it a new password, it refresh the Server Express then pop me a window "The specified network password is not correct". Then I don't know how to deal with it.
The other two tabs are "service" about binary path and starting mode etc, and "advanced" about clustered, startup parameters, registry root, etc. No idea about it.
I hope you will work on well with you installation, and hope you could give me a hint about your log-in configuration of Server 2000 as your explanation I find is always very clear!!
Thank you in advance, have a nice day!
Henry Li|||Hello Jon,
I just installed the MS SQL Server Management Studio Express (CTP), and I found there are plenty of options inside to set up security and add new databases!
Thanks a lot!
I added a HENRYPC\ASPNET user besides other Buildin\users and sa, etc. Then I found under the DATABASES, I just got system databases, then I created a "EXPRESSDATA" for my own use.
Then I modify the connection in the Visual Web Developer Express under the Database Explorer. It connects successfully to the EXPRESSDATA under the .\SQLEXPRESS data source!
And I still got one error when I run the project in the IE6 browser: can't find the stored procedures "GetDepartments". So it seems it connects! but maybe there is still some issue related to these tedious connection, because I have the stored procedure there under the "lavender.mdf" database, but can't see it in the Management Studio Express.
I am still trying to solve it. Thanks a lot for your help! Now I have a clearer understanding of these complicated connections.
Best wishes,
henry Li|||Henry,
I'm glad you've figured your connection stuff out. I'm glad to know that what you were looking for was in the Management Studio. I kind of thought it might be, but I still haven't been able to get my install working to verify it.
The reason it can't find your stored procedure is that you hooked up to a new database called ExpressData instead of your lavender database. Your stored procedure resides in lavender instead of ExpressData.
The best thing to do would be to try to get Management Studio express to see your lavender database. You should be able to do this by right clicking on the databases tab or folder and there should be an option to attach or import an existing database. Again, I don't have it installed, but that is how it functions in SQL Server 2000.
If you can get the lavender database seen in Management studio, then you'll want to change your configuration file to look at that database again instead of ExpressData. In addition, you'll want to add the aspnet account to have access to the lavender database.
Again, once I can get my install working I'll be able to provide you with specific steps. Good luck!
Monday, March 12, 2012
How to add new user whit pass for new database
I have question reagrding to SQL Server Authentication, I use SQL express and Microsoft SQL Server Management Studio Express. For creating and editing databases, I login whit Windows Authentication and it works fine, but as I need this new empty database for ASP script on my localhost ISS, I dont know what to enter for Username and Password as SQL Server Authentication.
Ex. here is this connection string where I need to enter U/P but what to enter if I use Windows Authentication?
strCon = "Provider=SQLOLEDB;Connection Timeout=90;Server=AMD\SQLEXPRESS;User ID=?;Password=?;Database=test_database;"
Connectionstrings can be found on www.connectionstrings.com , for Windows Authentication you would have to do something like this:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
More details on this can be found on the site mentioned above.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Have a look at this post from SQL Server Central on the Security and login functions for SQL Server 2005.
|||Hy again,
thanx, I was able to create new SQL login whit pass, but now when I try to login whit that U/P I get this error:
Login failed for user 'test'. The user is not associated whit a trusted sql server connection.
Microsoft SQL server, Error: 18452
Then I search for microsoft for this error and try this http://support.microsoft.com/kb/269587/en-us but I still get the same error.
|||
hi,
in order to accept the changes, SQL Server service must be restart after you modify this security setting...
do you still have torubles?
regards
|||I have restarted ISS before, but I tryied to restart my computer and it works now finaly. Thanx to all!Wednesday, March 7, 2012
How to add a new .mdf with ONLY sql authentication in SQL Express?
Hi all.
I am going around in circles all day on this.
I have a clean new install of win XP SP2, VS 2005 Pro RTM,
and a NAMED instance of Sql Express with mixed authentication mode specified at setup.
The sa pw is 123456
I would have liked just SQL authentication but no such option.
I am trying to add a new mdf file. If I just use the "Add New Item" in VS web app project it just adds a new .mdf file with windows authentication tied to the current profile I am logged in the os with. I am trying to avoid that as this projects is to be zipped and downloaded as a sample app. I am also trying to avoid embedding an instance of SSE as the recipients of the zip file already have SSE running iwth thei VS IDE.
So most logical is to create the file with SQL authentication.
I have to do it by the Add Connection GUI
and for data source: "Microsoft SQL Server Database File (SqlClient) "
for data file name: C:\WebSites\Website1\App_Data\MyDatabase1.mdf (not yet existing file)
Use SQl server authentication:
user: sa, password: 123456 (the ones specified at set up of the SSE)
I keep getting
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.
I keep getting the same error no matter what username I use or password, the current login, etc. It just does not let me use sql authentication.
Please help anyone. This is a nightmare.
"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."
This seems that you didn′t activate Mmixed Authentication, its an error you are getting if you only specified the Windows Authentication. Try setting the authentication mode for the express instance:
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:
Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>
HTH, Jens Suessmeyer.
|||Thanks Jens,
1. I made sure during the install I selected mixed mode.
2. This article essentially says the same. However, I cannot find in the registry any "LoginMode" subkey for either the default SQLEXPRESS, or MySSE named instance.
Actually I did a complete search and there is no Key, Value or Data that contains "LoginMode" in the whole registry.
|||
You can interrogate the database engine with the CTP SQL Express Mamnagement Suite downloadable from Microsoft.
Click select the database engine..... right click and select export
You'll get a file like this:
<?xml version="1.0" encoding="utf-8"?>
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<ServerType id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
<Server name="Shhhh\sqlexpress" description="Local instance - 'bliss\sqlexpress'">
<ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>Shhhh\sqlexpress</ServerName>
<AuthenticationType>0</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions />
</ConnectionInformation>
</Server>
</ServerType>
</Export>
You'll see that ny authentication type is 0 which is windows authentification.
I ust went through this and there is a way to attach this programattically.
Use the Sql Management Classes in your code....
Create an XP UserGroup and user using the SMO classes. Then create the same user group in and user in the database and Attach it. It never fails and yes... I know how frustrating this is. I lost a lost of sleep over this this weekend.
Check out the thread in the first forum of this board for more information.
Renee
|||
Thanks Rene,
I learned something. I don't need the SSMSE becuase I have the full version installed too.
However I still can't fix the problem. This is what I have done so far:
I exported a .regservr and the xml looks just like you show. authetication 0. So I went back and looked in the registry again. I found a LoginMode subkey but in a different key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer
It didn't quite make sense, because I am trying to change a NAMED instance. As expected it did not work. The exported .regsrvr still shows the same authentication mode 0.
I am scared to start adding registry keys that do not even exist.
The nightmare continues.
Carl,
You are doing this programmatically?
|||I meant to ask you about a link to the thread you mentioned.
What do you mean programmatically?
Create a c# app that could access and modify these registry keys in code? in which case NO.|||
Not true.... I spent the weekend working on this and a friend really helped my understand. You can do excellent work with SSE.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=223272&SiteID=1&PageID=0
post an email adress and I will send you the solution.
|||http://msdn2.microsoft.com/microsoft.sqlserver.management.smo.server.attachdatabase.aspx
http://msdn2.microsoft.com/ms160723.aspx
|||oh yeah. . . you can't do SQL authentication only.
its either windows or mixed mode
How to add a image to the ReportVeiwer
Good day all
I have the vwd 2005 express and am playing around with the report veiwer.
How can i put the image on my report ( as a header) . I have draged the image control from the toolbox onto my Report.rdlc form , but it,s not the same as the image control in the toolbox for the "aspx" forms. I cannot right click and set a image URL.
There is a backgroundImage property but the wording is unfamilier to me eg, Source.. External , Value... MIMEType etc.
Can anyone help with getting the image into the report?
Thanks
Rob
Hi Rob,
The only way to add image in the report is by using the image control, and yes its different from the one for the aspx pages.
When you place a image control on the form an image wizard will be opened where you can choose whether you want the image to be embadded into the report itself/use a common image for a project/image stored in database/use an image hosted on net.
Just select the appropriate option and select the image. For more information check these links -
http://msdn2.microsoft.com/en-us/library/ms156388.aspx
http://msdn2.microsoft.com/en-us/library/ms156482.aspx
Sunday, February 19, 2012
how to access sql server express from other computer on the netwrok
Hi experts
I use a sqlserver on a local computer. i want to access the sqlserver from other computers on the network using the VWD. When trying to connect to the database i get the error:
"An error has occured 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I enabled the TCP/IP and the Named Pipes protocols on the server and still the same error.
Thanks
check windows firewall enbale or not..
if yes, then let sql 2005 port pass through
|||I just found the sollution.
1. when accessing from other computers, the servers 'Guest' account is used, so this accoun should have the correct permissions (not recommended) or access using another privileged acount
2. enable remote connection in the "SQL Serve surface area Configuration" tool
Thanks for the attention anyway