How can I check if SQL Server Management Studio is installed?

How can I check if SQL Server Management Studio is installed?

[Start] \ [All Program] \ [Microsoft SQL Server 2005] \ [SQL Server Management Studio]

It is either there or not.

The default install does NOT include the client tools. If if is not listed, then using the install media, run Setup again, and select the Client Tools.

|||

Sorry.

How can I check programmatically if SQL Server Management Studio is installed?

|||

Either look in this location

"{installLocation}\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlwb.exe"

or check this Registry entry using xp_regread

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Applications\sqlwb.exe

sql

How can I change the default Save-As/Save directory

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.

I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?

Thanks,

Nanci

Goto [Tools], [Options], [Query Results]

There you will be able to change the default query results storage location.

|||

I have changed that setting, but it only works for the results of the query, not saving the query itself. Any other suggestions?

Nanci

sql

How can I change the column width?

I'm creating a C# program in Visual Studio. When I query the sql database, the results are put into a data grid view. However, the column widths are always too small when displayed. I have text that is not shown because the column width is not large enough. I tried going into Sql Server Management Studio Express and trying to modify the size property of the columns, but it won't let me change it (its grayed out). How can I fix this problem?Why would you want changing size on the back end server when you need changing size of your grid cells.
You can provide functionality to popup a full text when grid cell double-clicked.

If you'll change backend column size to accommodate your grid size (which is unheard of) you will just truncate existing data in a database and have part of data instead of full set of data.

To limit number of characters returned from a backend to a front end you can use Substring function.

For more help describe your intentions in more detailed and it would be a wise posting it on .NET forum.

Good Luck.

How can i change security mode of SQL Server Express 2005

Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?

Any idia?

Thread moved to the appropriate forum|||

hi,

SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..

the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..

regards

|||

Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.

Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

i have also tried this :

Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.

|||

hi,

the exception is correctly related to not enabled SQL Server authenticated connections..

but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...

please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?

regards

|||

Yes i connecting to correct SQLExpress instance.

but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.

How can i change security mode of SQL Server Express 2005

Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?

Any idia?

Thread moved to the appropriate forum|||

hi,

SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..

the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..

regards

|||

Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.

Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

i have also tried this :

Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.

|||

hi,

the exception is correctly related to not enabled SQL Server authenticated connections..

but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...

please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?

regards

|||

Yes i connecting to correct SQLExpress instance.

but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

sql

How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

How can I access SQL Server Compact Edition with SQL Server Management Studio Express?

Hello,

I just installed SQL Server Compact Edition, since I am considering using it instead of SQL Server Express for a local database in my application. The documentation mentioned that I could use SQL Server Management Studio Express to connect to the Compact Edition and create and manipulate databases.

To try to connect, I run Management Studio and bring up the "Connect to Server" dialog. Unfortunately, the pull-down list of "Server name"s does not include the SQL Server Compact Edition server. I do not know how to type in the server name manually, so I cannot connect.

To install Compact Edition, I downloaded it and ran "SQLServerCE31-EN.msi". This installed, and I assume registered, a number of DLLs in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\".

Is there perhaps an additional step that I left out to complete the installation?

Might I need an upgrade to some other components? My Management Studio Version is:

- Microsoft SQL Server Management Studio Express 9.00.2047.00

I would appreciate any help you can provide.

Thank you,

WTW

The support for SQL Server 2005 Compact Edition was added in Microsoft SQL Server Management Studio Express (SSMSE). SSMSE is available for download at:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

SSMSE shipped with SQL Server 2005 SP2. SQL Server 2005 SP2 information is at

http://technet.microsoft.com/en-us/sqlserver/bb426877.aspx#3

Regards

Ambrish

|||

WTW,

I've written a blog post about this, you can see it at http://arcanecode.wordpress.com/2007/04/11/create-a-sql-server-compact-edition-database-using-sql-server-2005-management-studio/ . I've also written an article that describes all the components you'll want to install to make everything work right, http://arcanecode.wordpress.com/2007/04/24/sql-server-2005-compact-edition-important-component/ .

You might also want to check the Arcane Lessons page for more SSCE tutorials.

Arcane

|||

Thanks for your reply. I uninstalled SSMSE and downloaded and installed it again. However, I still cannot access a SQL Server 2005 Compact Edition database.

When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it. I've tried most of the menus and buttons, and I cannot find a setting for this.

I am missing some other component or configuration option?

Thanks for your help.

WTW

|||

Thanks for your very helpful reply. Thanks to your blog, I now have all of the proper components installed to make Compact Edition work properly. Your blog was the only place where I have found a complete list of all of the necessary components.

I have even purchased the E-book that you recommended, and it is very helpful.

However, I still am not able to access a Compact Edition database through SQL Server Management Studio Express. When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it to Compact Edition or something similar.

The E-book demonstrates how to access a Compact Edition database through Management Studio. However, it shows a "Connect" menu in Management Studio that does not exist on my version. According to the E-book screen shots, the "Connect" menu should allow you to select "SQL Server Mobile", which will then bring up a "Connect to Server" dialog that shows a "Server Type" of "SQL Server Mobile". The next line allows selection of a "Database file" instead of a "Server name".

My version of Management Studio does not have the "Connect" menu. I even tried reinstalling Management Studio from the Microsoft website. However, I discovered that I already had the latest version, 9.00.2047.00.

I would appreciate any additional help you can provide.

Regards,

WTW

|||In typical microsoft style, the documentation is horrible on this. I was only able to get it to work by installing SP2 of SSMSE (of which there is no mention on the regular SSMSE download page) at:

http://www.microsoft.com/downloads/details.aspx?familyid=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en

and the SQL CE SDK found at:

http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en

You will know you have SP2 of SSMSE installed if you version is 9.00.3042.00
|||

SP2 did the trick. Thank you so much.

Regards,

WTW

how can attack database from sqlserver management studio express?

I have created a database named s_office,now i want add file data.MDF and log.LDF into it. .How can i do it?Could you show me the script that attack file .MDF and .LDF into database.thankxhttp://msdn2.microsoft.com/en-us/library/ms179877.aspx