database project

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TS - 18 Jul 2007 14:22 GMT

I have created a DB project in VS 2005. i have connected it to a sybase DB.
This is a project that is in source control and shared in my dev team.

When i registered the project in server explorer, i had to register with my
personal credentials to the DB. It seems like this info is stored in source
control so that anyone that opens this project will use these same
credentials. Our application has a generic application login but it doesnt
have any create permissions, so i couldn't use it to register the server.

The last time i used DB projects i don't remember having this issue; i don't
remember if i used a generic account that had create permissions in the dev
DB.

in this scenario, is it possible to have each person use their own
credentials for this DB project's default DB reference?

thanks

Reply to this Message

TS - 18 Jul 2007 17:24 GMT

also, am i to assume that in this scenario when you open up the solution, is
a client license for the DB being used?

I notice that when i first open the project, the data connection in server
explorer has a red x next to it, so it may be that the client license isn't
used until you are prompted and enter valid DB credentials.

which one or both is right?

> I have created a DB project in VS 2005. i have connected it to a sybase DB.
> This is a project that is in source control and shared in my dev team.
[quoted text clipped - 13 lines]
>
> thanks

Reply to this Message

TS - 18 Jul 2007 17:27 GMT

Also, I notice that .sql files when opened in IDE from this db project are
color coded like working in query analyzer. Other file extensions aren't. Is
there a way to make it so other file extensions are treated as .sql files?

thanks

> I have created a DB project in VS 2005. i have connected it to a sybase DB.
> This is a project that is in source control and shared in my dev team.
[quoted text clipped - 13 lines]
>
> thanks

Reply to this Message

WenYuan Wang [MSFT] - 19 Jul 2007 11:11 GMT

Hello TS,

1)    DataBase project's Database reference is stored in .dbp file.
Such as:

Begin DBRefFolder = "Database References"
Begin DBRefNode = "sha-dng-wywang.testVisio.dbo"
ConnectStr = " Data Source=.;Initial Catalog=testVisio;Integrated
Security=True"
Provider = "{91510608-8809-4020-8897-FBA057E22D54}"
Colorizer = 6
End
End

VS 2005 IDE used ConnectStr to connect Database. Personal Credentials is
aslo included in ConnectStr. "Integrated Security=True" means using Windows
Authentication. It works fine for SQL database. With this setting, each
person uses their own credential (windows account).

However, I'm not sure whether Sybase DB supports Windows Authentication. If
not, we would have to define user credential for the connection such as
"...;UserId=Your_Username;Password=Your_Password;".
This setting will store in .dbp file and keeped by Visual SourceSafe.
Therefore, each member in your team will use the same user credential for
DB Project's Database reference.

One approach is to modify the .dbf file, delete user credentials from its
connection string. Such as Begin DBRefFolder = "Database References"
...
ConnectStr = " Data Source=.;Initial Catalog=testVisio;"
...
End

When a member checked-out solution from VSS, connect to underlying database
by double-clicking DB project's DB reference, VS IDE will found the connect
string is lack of user credential. VS IDE will prompt a dialog for user to
enter his own credential. This information will be stored in User's
Document (not in .dbf file). Therefore, the credential will not be shared
by other user. Each one should use their own credential.

For this method, note that the VS 2005 IDE will register a new Database
connection for each time you double-click the DB project's DB reference.
(sha-dng-wywang.testVisio.dbo, sha-dng-wywang.testVisio.dbo1,
sha-dng-wywang.testVisio.dbo2..). You may delete them if you feel they are
unnecessary.

2)    About the connection license:
The data connection in server explorer has a red x, which means VS 2005 IDE
hasn't connected to the underlying Database. Therefore, I think the client
license isn't used.

3)    For the issue about how to color code the file extensions in VS IDE.
In SQL Server Management Studio, we could approach this by adding the
extension in Tools|Options|Text Editor|File Extension.
But, under my test, in VS IDE, there is no SQL editor in this option.
I think I need perform more research on this and reply you if I get any
information.

Hope this helps. Please let me know if you have any more concern. I'm glad
to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TS - 19 Jul 2007 15:30 GMT

thanks for answering each question

I tried your recommendation:

>When a member checked-out solution from VSS, connect to underlying database
>by double-clicking DB project's DB reference, VS IDE will found the connect
>string is lack of user credential. VS IDE will prompt a dialog for user to
>enter his own credential. This information will be stored in User's
>Document (not in .dbf file). Therefore, the credential will not be shared
>by other user. Each one should use their own credential.

what user document are you referring to, the .suo file? I don't see anywhere
where my credentials are stored locally and then when i then open up the
project, i still have to log in again.

I will also await your answer to the sql file thing as well

thanks!

> Hello TS,
>
[quoted text clipped - 61 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TS - 19 Jul 2007 21:12 GMT

also, why can't i drag files from C# projects into folders in the database
project?

> Hello TS,
>
[quoted text clipped - 61 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TS - 19 Jul 2007 21:40 GMT

Also, in database project i don't get "exclude from project" option in right
click. is this something to do with a dB project? how to i accomplish this?

> Hello TS,
>
[quoted text clipped - 61 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 23 Jul 2007 08:09 GMT

Hello TS,
Sorry for delay, due to weekend.

1)    For the issue about edit non .sql file.
There is no option in VS 2005 IDE.
Under my investigation, one approach is to edit Windows Registry.
(Note: Please backup your registry before editing.)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0Exp\Languages\File
Extensions\.sql
{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630} is related to the (T-SQL) editor
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0Exp\Services\{FA6E5E79
-C8EE-4D37-B79A-5067F8BD5630}

In order to allow VS open non .sql file with T-SQL editor, we would have to
add a new key and associate it with T-SQL editor.

By the way,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\Languages\File
Extensions\.sql seems like a shadow key for VS 2005 IDE. It is necessary to
edit also.

The following code could add ".txtsql" file extension.
=============================
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0Exp\Languages\File
Extensions\.txtsql]
@="{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630}"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\Languages\File
Extensions\.txtsql]
@="{FA6E5E79-C8EE-4D37-B79A-5067F8BD5630}"
=============================

2)    For the issue about DataBase project reference credentials.
The credential for DB project reference is stored in .dbp file (which is
the file in your project folder).

3)    For two issues about draging files from C# projects and "exclude from
project" option in DB project.
I'm afraid this is by design. No way could approach this. DB project file
is totally different from the WinForm/WebForm application. As you can see,
DB project is listed in "Other Project Types" in left panel of "New
Project" dialog. We could not drag any file from C# project into "Other
Project types" project. If we need add an existing from C# project. Please
use the "Add Existing Item.." in context menu. If we need remove an item
form the current project, please also check "delete.." in the context menu.

Hope this helps. Let me know if anything unclear. We are glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TS - 23 Jul 2007 15:49 GMT

thanks for the post

> 2) For the issue about DataBase project reference credentials.
> The credential for DB project reference is stored in .dbp file (which is
> the file in your project folder).

In the previous post you said:

>This information will be stored in User's
>Document (not in .dbf file). Therefore, the credential will not be shared
>by other user. Each one should use their own credential.

I believe when you referred to .dbf you meant .dbp. I thought you were
saying that there was another file besides the .dbp that stores credentials
by user and is not a shared file, which .dbp is? How are specific user
credentials stored that ARE NOT SHARED (& not in source control) and where
are they stored?

thanks

> Hello TS,
> Sorry for delay, due to weekend.
[quoted text clipped - 7 lines]
> Extensions\.sql
> {FA6E5E79-C8EE-4D37-B79A-5067F8BD5630} is related to the (T-SQL) editor

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0Exp\Services\{FA6E5E79

> -C8EE-4D37-B79A-5067F8BD5630}
>
[quoted text clipped - 37 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 24 Jul 2007 13:15 GMT

Hello TS,
Thanks for your reply,

As you seen, the ".dbf" file in my previous post means ".dbp" file.
I'm truly sorry for this mistake.

There are two different DB reference in VS 2005 IDE. I'm like to check
which connection you are talking about.

1)    The DB reference (Data Connections) in "Server Explorer" in VS 2005 IDE.
As far as I know, each connection is encrypted and stored on local for each
user. If you connected database before, this information will be stored in
VS until you remove it explicit.

2)    Another DB reference is (Database References) in DataBase Project.
If you double-click this DB reference, VS IDE will check if this reference
has been added in Server Explorer before. I'm talking about this DB
reference. This information is stored in .dbp file which could be shared
with other users.

For detailed information, would you please follow the below steps.

The .dbp file is the Database Project file. If you create a Database
project in VS 2005, VS 2005 IDE will create a project file on disk. This
project file is .dbp file
1)    Click File|New|Project in VS 2005
2)    Select "Other Project Types"|Database|Database Project in left panel on
"New Project" dialog.
3)    Type "DBProjectTest" in Name textbox.
4)    Type "your profile\Document\Visual Studio 2005\project\" in Location
textbox.
5)    Click "OK" button.
6)    Select your database reference in "Add Database Reference" dialog when
VS 2005 IDE creates the new project for you.

Then VS 2005 IDE will create a Database project for you on "your
profile\Document\Visual Studio 2005\project\" folder.
1)    Please check there should be a file named "DBProjectTest.dbp" in "your
profile\Document\Visual Studio 2005\project\" folder.
2)    Please open "DBProjectTest.dbp" by Notepad.exe
("C:\Windows\notepad.exe").

This file should be something like as below:
# Microsoft Developer Studio Project File - Database Project
Begin DataProject = " DBProjectTest "
MSDTVersion = "80"
DefDBRef = "sha-dng-wywang.testDB.dbo"
Begin Folder = "Change Scripts"
End
Begin Folder = "Create Scripts"
End
Begin DBRefFolder = "Database References"
Begin DBRefNode = "sha-dng-wywang.testDB.dbo"
ConnectStr = "Data Source=.;Initial Catalog=testDB;
Uid=myUsername;Pwd=myPassword"
Provider = "{91510608-8809-4020-8897-FBA057E22D54}"
Colorizer = 0
End
End
Begin Folder = "Queries"
End
End

=============================================================
ConnectStr = "Data Source=.;Initial Catalog=testDB;
Uid=myUsername;Pwd=myPassword"
=============================================================
ConnectStr is used by VS IDE to connect underlying database.
Uid=myUsername;Pwd=myPassword is the user credential.

If you specify the UID and PWD, VS use this user name and password to
connect database. Thus this User Credential will be shared with each team
member.

In order to make sure each member in your team should use his own username
and password to connect DataBase, please remove the Uid and Pwd from the
ConnectStr
For example:
ConnectStr = "Data Source=.;Initial Catalog=testDB"

After you modify the .dbp file, please reopen DB project in VS 2005 again.
If you double-click the DB reference in "Database References" of Database
project, VS 2005 IDE will prompt a dialog. You should have to type your
userid and password in this dialog. After that, VS 2005 IDE will encrypt
and stored connection string on local profile and then create a new
DataConnections in Server Explorer.

Please check the above method and let me know if you have any issue or
anything unclear. I'm glad to assist you.
Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TS - 24 Jul 2007 16:32 GMT

Thanks WenYuan.

I am clear on everything and have followed all your instructions. The new
data connection was created and i entered credentials. 2 things occur:

1. When I open a sql file and highlight a query and right click - run
selection, i am prompted for credentials (no user name or password appears).
After entering credentials the query runs. The next time I try though, i am
prompted again.

2. When i close and reopen my environment, i have my existing connection but
the password is no long there. I also have another one named xxxxx2 which
has no credentials specified.

So i still have two issues:
1. my credentails aren't saved after re-opening the IDE.
2. Even if i save my original connection with username/password and try 'run
selection' i am prompted (no username or password). If i then delete the
other connection (no username or password exists) i still get same prompt.

it seems my password will not persist and the correct connection isnt' used
when i want to run script

thanks

2.

> Hello TS,
> Thanks for your reply,
[quoted text clipped - 6 lines]
>
> 1) The DB reference (Data Connections) in "Server Explorer" in VS 2005

IDE.

> As far as I know, each connection is encrypted and stored on local for each
> user. If you connected database before, this information will be stored in
[quoted text clipped - 81 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 25 Jul 2007 13:56 GMT

Hello TS,
Thanks for your reply.

I'm afraid the issue which you meet is by design.

1) When we execute a query by right-click in Database Project, VS IDE will
open the default Database reference. Because there is no credential in
connection string, VS IDE will prompt a dialog for UserID and Password. But
this information will not be stored in project. Each time we run selected
query from a sql script file, this dialog will prompt.

2) We has discuss this behavior in my first reply

>For this method, note that the VS 2005 IDE will register a new Database

connection for each time you >double-click the DB project's DB reference.
(sha-dng-wywang.testVisio.dbo, >sha-dng-wywang.testVisio.dbo1,
sha-dng-wywang.testVisio.dbo2..).

VS IDE will create a new Data Connection in "Server Explorer" window, each
time we double-click Database Reference in "Solution Explorer" window.

This is because, when you open the Database Reference from DB project
database reference in "Solution Explorer" windows, VS IDE will check if
this connection exists in "Server Explorer" (Note: Check by connection
string). After we typed UserName and Password, the connection in "Server
Explorer" will be created and includes UserName and Password.

Next time, when we double-click the database Reference from DB project
database reference in "Solution Explorer" again, VS IDE will check all the
connections in "Server Explorer". But, there is no same connection string
in "Server Explorer". (The connection created in previous time includes the
userID and Password. However, the connection string in DB database
reference doesn't include userID and Password. Therefore, they are
"different".) VS IDE will create a new connection in "Server Explorer"
again, named "XXXX2".

Hope this helps. If you have anything unclear, please let me know. We are
glad to assist you.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 27 Jul 2007 12:27 GMT

Dear TS,

This is Wen Yuan again. I just want to check if there is anything unclear?
If you have any more concerns on this, please feel free to let me know.
We are glad to assist you.

Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

database project

Free Magazines