How can I restore a database from a UNC?

It appears that Management Studio will not allow you to perform a restore from a UNC path. Is this true or am I missing the option to open the magic UNC door? I am very new to SQL2005 and this was buried in SQL2000, but it was possible to do in Enterprise Manager.

Have you tried using the raw TSQL query to start the restore, and not use the GUI Interface.

|||

Not to be snotty, but you didn't answer the question. Your point is taken, there are other ways to do this. I can use a script to restore from a UNC path. I am still interested in getting an answer to the question I posted, not to the underlying assumption that I am looking for any way to accomplish a restore from UNC.

As a DBA, I do many operations by script; however, there are times that, for a number of reasons, the UI is a better option. Since this was something that could be done in SQL2000 through the UI, I thought that perhaps Microsoft would also provide a means to do it through the UI in 2005. We have non-technical folks who need to do restores from different sources for demos. It is more practical to teach them how to use the UI to grab the backup they need for their laptop rather than scripting the numerous possibilities or having them alter a script.

So, if anyone knows if Management Studio can be used to do a UNC restore, please share with me how it can be done.

|||Hi,

this is sure possible: Select Restore Files and Filegroup --> Fome Device --> Add --> Enter a Full qualified name in the filename textbox like \\m2-jenss\SomeShare\Somebakfile.bak

and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You nailed it. I initially tried that, but permissions foiled me, but I misread it as a limitation of the UI because the Selected Path pointed to a local drive and the field was disabled. Apparently, anything you put in File name will override the Selected Path entry if using the UNC path. Thanks a ton!

How can I Remove the Screwed-up AdventureWorksDB in SQL Server Management Studio Express?

Hi all,

I downloaded AdventureWorksDB.msi Microsoft and struggled to install it in my SQL Server Management Studio Express 8 months ago. I forgot the AdventureWorks examples and other stuff were in my C:\ drive. Recently, I downloaded a new AdventureWork.msi and executed it to install without removing the old stuff from the "Add and Remove" of Control Panel. Now my AdventureWorks database is screwed-up in my termical PC that is linked with Microsoft NT 4 LAN System!!! Please kindly help and tell me how I can revome the scewed-up AdventureWorksDB in my terminal PC.

Thanks in advance,

Scott Chang

Hello Scott -

I'm not sure what is going on here - can you tell me where the software is installed and what you mean by "screwed up"? to help you, the readers need to know what you're seeing, and where. Be as detailed as you can.

Thanks!

Buck Woody

|||

Hi Buck,

I can tell you the following 2 things:

1) When I double-clicked on AdventureWorksDB.msi recently, it tried to install the stuff of Sample Databases for Express Editions in my C:\Program Files\Microsoft SQL Server\MSSQL1\MSSQL\. But it stated an error occurred in the last step of installation process. After it happened, I tried to remove "AdventureWorksDB" in the "Add and Remove" of Control Panel, I got the following error:

AdventureWorksDB Error. 1326 Error: getting file security: C:\Program Files\Microsoft SQL.Server\MSSQL 1

\MSSQL\GetLastError 5.

I think it was because I forgot to remove the old AdventureWorksDB, before I clicked the new AdventureWorks.msi!!

2) This morning, I entered my SQL Server Management Studio Express and tried to removed the "AdventureWorksDB" in the Databases by using 'Delete' after I right-clicked "AdventureWorksDB" and "Delete Object" appeared - I kept clicking "Delete Object" and it just kept going and doing the same thing again and again..... I gave up at this poit!!!

That is what I called "screwed-up" AdventureWorksDB. Please help and tell me how I can remove this "screwed-up" stuff.

Thanks again,

Scott Chang

|||I see. Try this: Right-click the database and select "All Tasks", then "Detach Database". Re-start the server, and then see if the database was released. If it is, then you can delete the Adventureworks database files from the hard drive.|||

Hi Buck, Thanks for your response.

I following your instructions and I got the following:

Database to detach:

Database Name Drop Update Keep Status Message

AdventureWorks V Failure Detach database failed to Server

|OK|

After I clicked on the |OK| button, the following appeared:

Microsoft SQL Server Management Studio Express

Detach database failed for Server 'myComputerID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional Information:

-> An exception occured while executing a Transct-SQL statement or batch. (Microsoft.SqlServer.Express.ConnecttionInfo)

-> EXECUTE permission denied on object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL

Server, Error: 229)

|OK|

I am completely lost now. Please help me again and give me more instructions to resolve/solve the proble.

Many Thanks and Regards,

Scott Chang

|||

Hi Scott,

The installer should have put something in add/remove programs for you to uninstall it. The new one does that.

|||

Hi,

Refer http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm for instruction on how to remove Adventure Works DB

HTH

Hemantgiri S. Goswami

How can I Remove the Screwed-up AdventureWorksDB in SQL Server Management Studio Express?

Hi all,

I downloaded AdventureWorksDB.msi Microsoft and struggled to install it in my SQL Server Management Studio Express 8 months ago. I forgot the AdventureWorks examples and other stuff were in my C:\ drive. Recently, I downloaded a new AdventureWork.msi and executed it to install without removing the old stuff from the "Add and Remove" of Control Panel. Now my AdventureWorks database is screwed-up in my termical PC that is linked with Microsoft NT 4 LAN System!!! Please kindly help and tell me how I can revome the scewed-up AdventureWorksDB in my terminal PC.

Thanks in advance,

Scott Chang

Hello Scott -

I'm not sure what is going on here - can you tell me where the software is installed and what you mean by "screwed up"? to help you, the readers need to know what you're seeing, and where. Be as detailed as you can.

Thanks!

Buck Woody

|||

Hi Buck,

I can tell you the following 2 things:

1) When I double-clicked on AdventureWorksDB.msi recently, it tried to install the stuff of Sample Databases for Express Editions in my C:\Program Files\Microsoft SQL Server\MSSQL1\MSSQL\. But it stated an error occurred in the last step of installation process. After it happened, I tried to remove "AdventureWorksDB" in the "Add and Remove" of Control Panel, I got the following error:

AdventureWorksDB Error. 1326 Error: getting file security: C:\Program Files\Microsoft SQL.Server\MSSQL 1

\MSSQL\GetLastError 5.

I think it was because I forgot to remove the old AdventureWorksDB, before I clicked the new AdventureWorks.msi!!

2) This morning, I entered my SQL Server Management Studio Express and tried to removed the "AdventureWorksDB" in the Databases by using 'Delete' after I right-clicked "AdventureWorksDB" and "Delete Object" appeared - I kept clicking "Delete Object" and it just kept going and doing the same thing again and again..... I gave up at this poit!!!

That is what I called "screwed-up" AdventureWorksDB. Please help and tell me how I can remove this "screwed-up" stuff.

Thanks again,

Scott Chang

|||I see. Try this: Right-click the database and select "All Tasks", then "Detach Database". Re-start the server, and then see if the database was released. If it is, then you can delete the Adventureworks database files from the hard drive.|||

Hi Buck, Thanks for your response.

I following your instructions and I got the following:

Database to detach:

Database Name Drop Update Keep Status Message

AdventureWorks V Failure Detach database failed to Server

|OK|

After I clicked on the |OK| button, the following appeared:

Microsoft SQL Server Management Studio Express

Detach database failed for Server 'myComputerID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional Information:

-> An exception occured while executing a Transct-SQL statement or batch. (Microsoft.SqlServer.Express.ConnecttionInfo)

-> EXECUTE permission denied on object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL

Server, Error: 229)

|OK|

I am completely lost now. Please help me again and give me more instructions to resolve/solve the proble.

Many Thanks and Regards,

Scott Chang

|||

Hi Scott,

The installer should have put something in add/remove programs for you to uninstall it. The new one does that.

|||

Hi,

Refer http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm for instruction on how to remove Adventure Works DB

HTH

Hemantgiri S. Goswami

How can I re-install the Northwind database in SQL Server Management Studio Express?Use SqlDataS

Hi all,

In my VB 2005 Express, I created a Windowds Form application "shcDataSet" that used 1 SqlConnection, 1 SqlDataSet and 3 SqlDataAdapters associated with the Northwind Database in my SQL Server Management Studio Express. The SqlConnection had "User Instance" in the following ConnectionString: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwnd.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True. The 3 SqlDataAdapters were for the Northwind files "Customers", "Orders" and "Order Details" used in the SQLDataSet "AllOrders" with a AllOrders.xsd file. I ran the "shcDataSet" applicatyion and it worked fine. After the execution of "shcDataSet", I checked the Northwind database in my SQL Server Management Studio Express and clicked on "+" in front of Northwind database-I did not see any file showed up and I got the following error message: Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum) Additional information: one or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error:5173). If I executed the "shcDataSet" application again, I got a new error: SqlException was unhandled - Cannot open user default database. Login failed. Login failed for user 'myPC##\myName' ->daCustomers.Fill(AllOrders11, "Customers"). I have 3 questions to ask: (1) How can I re-install the Northwind database in SQL Server Management Studio Express? (2) When I use the .Fill Method, do I have to tell the SQL Server Management Studio Express to load and/or unload the Northwind files "Customers", "Orders" and "Order Details"? (3) After I executed the "shcDataSet", should I Upload the 3 Northwind files back to the SQL Server Management Studio Express? Please help and advise. Thanks, Scott Chang

this is a very common problem in SQL Server Express user intance. YOu are using user instance feature of SQL Server 2005 Express. If you are not intended to do it.. then change the connection string.

Refer this blog :

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu

|||

Hi Madhu, Thanks for your response.

I am new in using the "User Instance" in SQL Server Express and VB 2005 Express. This is the first time I tried to learn it by copying the example from a book. I plan to apply the same technique to my own chemical database and applications for my data management. I do not understand what you said by "If you are not intended to do it.. then change the connection string". Please be specific and give me more details about your statement.

I read the blogs you mentioned and the MS Technical Articles "SQL Server 2005 Express Edition User Instances", "How to: Move Through a DataSet with the Windows Forms BindingNavigator Control", "Connecting to Data with Server Express/Database Express". etc. and I still do not get what you said and the User-Instances Connection, SSEUtil, instance_pipe_name, etc. Please answer my 3 questions stated in my last post in the easy terms for me.

Thanks,

Scott Chang

|||

Hi Madhu, Thanks for your response.

I am new in using "User Instance" and I do not understand what you meant by "If you are not intended to do it, then change the connection string". I want to use it and I just want to see what happened and whether the Northwind Database is in SQL Server Management Studio Express or not. This is the first time I learned the User Instance by doing the example of using User Instance example from a book. I plan to apply the User Instance tecjnique to my chemical database and data management applications in VB 2005 Express-ADO.NET 2.0 programming. If I loose my chemical database during the application of User Instance, it is not possible for me to retrieve the chemical database. I read the blogs you cited and the MS technical articles "SQL Server 2005 Express Edition User Instances", "Connecting to Data with Server Explorer/Database Explorer", "How to: Install and Troubleshoot Database Components for Samples", etc. I do not understand why SSEUtil and Instance_Pipe_Name are used in the connecting to User Instances. Please clarify what you meant by "If you are not intended to do it, then change the connection string" and answer my 3 questions in a easy-to-undersatnd way for me.

Thanks,

Scott Chang

|||

normally, Userinstance of sql server is used unknowingly. that is what i mentioned in my previous post. if you really want to use userinstance of sql server express ... then its ok.... This feature is only available in SQL Server Expess. This one kind of embeded datbase with your application. Handling of Normal and user instance database are totally different. Its already mentioned in above mentioned link. My question is, do you need to use this feature in your application ... this will ofcouse increase the managebility of your Visual studio application (no need to create the database explicitly when you distribute the application) but it has lot of limitations also

Madhu

|||

Hi Madhu,

I am lost completely now and do not want to do the User Instance any more - I just want to repair/restore the Northwind Database in my SQL Server Management Studio Express now. This morning, I downloaded the Northwind and Pubs Sample Databases from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en/ and saved SQL200SampleDb.msi in my PC. I double-clicked the SQL2000SampleDb.msi file to install the Northwind database - it ran, but the Northwind database in my SQL Server Management Studio Express had no files to show, when I clicked the '+' in front of the Northwind node!!!? What is wrong in my PC? Do I have to delete the User Instance=True of the Connection String of my "shcDataSet" project when executed? Please help me in restoring/repairing the Northwind Database in my SQL Server Management Studio Express?

Many Thanks,

Scott Chang

|||

post the connection string... if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005

And also ... sql server 2005 sample datbase is Adventureworks... Northwind still you can use , but many new features like schema and all will not be available in that. you can download Adventureworks database from the link mentioned below

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Madhu

|||

Hi Madhu, Thanks for your response.

1) I went to the CANtrol Panel => Add or Remove Program => removed the old Microsoft SQL Server 2005 Samples. Then I downloaded the new Microsoft SQL Server 2005 Samples from

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en (as you said).

I checked the SQL Server Management Studio Express and looked at the Northwind Database and I still did not see any files in the Northwind Database!!!? Please tell me how I bring the files of Northwind Database to the SQL Server Management Studio Express (i. e. how to repair it.).

2) I do not understand whay you mean by "if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005". Please elaborate this matter if you can. Pardon me for asking this thing.

Thanks again,

Scott Chang

how can I open a .mdf aspnetDB DataBase ?

Hello everybody,

I am working with SqlExpress and MSSQL Server Management Studio Express and do not manage to open the aspnetDB .mdf database in which I am storing all data about my users.

I woulds like to open it and to be able to request it in order to check some strange things.

Actually, I'm experiencing an isssue with that database since the request just below gives me two results for only one user:

SELECT Member.Password, Member.PasswordAnswerFROM aspnet_MemberShip Member, aspnet_Users UsersWHERE Member.UserId=Users.UserIdAND Users.UserName='Admin'

Using this request returns me a user whom password is clear and an identical user whom password is hashed whereas I set PasswordFormat="Clear" in my Web.Config

Could you explain me how I may proceed please?

Thanx for your help

If you still have your project open, it could be that the project has a lock on the database file. Close your project in order to use MS SSMS express.

Otherwise, you can right-click the ASPNET.MDF database file and choose 'Open'. This will allow you to navigate the database using the Server Explorer tab, much like you would with SSMS.

Your problem lies in your SQL, however. UserId is the only Unique column in the default Users table because it is the primary key. This means that the UserName is NOT unique, and could be duplicated.

Use System.Web.Security.Membership.Provider to get a reference to the app's default membership provider, and use that to do what you need. (See the System.Web.Security.MembershipProvider class for available functions.)

|||

Thanx for your answer ps2goat,

I finally used VisualStudio Servers's Explorer to do so and was able to see all the information stored in the .mdf file.

This raises some questions about the issue I was dealing about above, but at the time, it is a solution for that precise problem ;)

In fact, I had two identical usernames in the database, one with a password saved in clear and the other hashed and, as you tell it, with a different unique ID.

I believe this comes from the fact that I used this .mdf file in more than 3 versions of the current prooject, making of it some kind of mess where it becomes to difficult to find what you're looking for.

Thanks for your help.

|||

You can just use visual studio directly to navigate through it. Also you can get that into sql express or any other db and reconstruct all the db.

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I install SQL Server 2005 Developer Edition''s Management Studio on a desktop machine?

I have installed Visual Studio 2005 Prof Edition on a Vista machine and SQL Server 2005 Developer Edition on a Windows 2000 server. They will be used as a development environment for us. As we need to management the SQL Server 2005, we would like to install the 2005 Developer SSMS on the Vista machine such that we can manage the SQL Server 2005 developer instance installed on that Win2k machine. However, the system does not allow us to do it. We have the express edition autoamtically installed while we installed the Vistual Studio. Is there any workaround that i can still install the 2005 developer SSMS without uninstall the sql server 2005 Xpress version? Or, I have to uninstall the Xpress version before i can install the 2005 developer's SSMS? Please help!

All VS2005 pro comes with free SQL Server 2005 developer edition so you can install one in each Vista and do the needed configuration of enabling browser service, and enabling both TCP/IP and Named Pipes in configuration Manager and use the Surface Area Configuration tool to allow both local and remote and allow TCP/IP and Named Pipes. While you are at it also configure mixed Authentication so all developer can register each other SQL Server and access all in one place.

Another option is to uninstall Express and use Express Advanced that comes with Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en

|||

Thanks for the reply. My question probably not clear for what we're trying to do. Actually, we are trying to install an instance of SQL Server 2005 Developer Edition (whcih come with Visual Studio Developer Edition into a machine running Windows 2000 SP4). And then I installed the Visual Studio 2005 professional edition into my company which is running on a Vista version Ultimate. Both installation success without any big problem. However, I would like to be able to manage that SQL Server 2005 Developer edition which installed in that Windows 2000 server through my local computer (Windows Vista with Visual Studio 2005 Prof installed). The problem is, there is a SQL Server 2005 Express edition installed into my computer (Vista) when I installed teh Visual Studio. And now, I tried to install the Sql Server Mangement Studio from SQL SErver 2005 Developer edition into my Vista machine, the system doesnt allow me to do it. So, do i have to uninstall the express edition in my local computer (vista) and then install the SSMS from the SQL Server 2005 Developer edition disc? Or, there's somoe other work around that it will allow me to install the SSMS from SQL Server 2005 Developer edition and the same time, keep the 2005 Express edition running in my local computer system. My reason is, the SSMS is just a management tools, it shouldnt depends on what version of DB engine installed into my local computer. Anyone please help

|||

I am one of the people who got the SQL Server developer edition price down from $450 to $50 list so I don't think such strange uses is valid. However you can uninstall the standard Express and use the Advanced which is also free and comes with most features of the full product including Management Studio. But you still have to do what I have explained in my first post because Express and the Developer edition comes with most services disabled.

|||

ic.. so uninstall the pre-installed sql server express from my computer (which has the visual studio 2005 prof edition installed) wont affect my visual studio 2005, right? Just want to make sure before i uninstall anything sorry about the noob question..

|||

Actually Microsoft now let you download the Management Studio for standard Express so download it below and install it. This link also gives you features compare of the Advanced and the Standard Express. But remember you have to go back and use my remote connection instruction. Hope this helps.

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

How can I import data from Excel to SQL server 2005 Express

I would like to import an Excel file into my SQL Server 2005 Express db. I'm using SQL Server Management Studio Express to manage my db.

I appreciate your suggestions,

You need to use SQL server Business Intelligence Development studio and create SSIS package

Using simple Drag and Drop controls

|||

Sorry, I do not have these tools to work with.

|||

Ok, Right click on the Database then Task -> Import

select your Data source as Microsoft Excel file map the fields to destination click finish

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

|||

I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.

However, when accessing this server over a VPN connection, I can see and access the system databases.

I am a Sysadmin and would expect to be able to see everything.

And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.

My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.

Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).

"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu"

|||

Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.

Thanks

Laurentiu

|||

Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".

Will re-post in the Tools forum, thanks.

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.nameas grantee_name, p.state_desc, p.permission_name
FROMsys.server_permissionsAS p JOINsys.server_principalsAS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name ='VIEW ANY DATABASE';

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

|||

I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.

However, when accessing this server over a VPN connection, I can see and access the system databases.

I am a Sysadmin and would expect to be able to see everything.

And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.

My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.

Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).

"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu"

|||

Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.

Thanks

Laurentiu

|||

Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".

Will re-post in the Tools forum, thanks.

How can I have SQL 9.0 instance in management studio

I've installed SQL Server 2005 and SQL Server 2000 side by side.

Now when I open SQL Server Management Studio it runs the SQL version 8 that belongs to SQL Server 2000.

How can I start with SQL Server 2005 (SQL 9.0) to create new database under the new features?

thank you.

First, register the SQL 2005 instance...click 'view...registered servers'...then, in the registered servers window, register your SQL 2005 instance appropriately.

Once you've registered the server, right click on it and choose 'connect...object explorer'. If the Object Explorer pane isn't viewable, make it so by choosing 'view...object explorer' from the menus. Then, in Object Explorer simply expand the instance to view all objects, nodes, etc.

HTH

|||

Thanks Chad,

I have two Instances of SQL Server. one is ServerName which is based on SQL 8.0 (2000) and another one is ServerName/SQLEXPRESS based on SQL 9.0.

If I want to work with features in SQL Server 2005 I have to choose SQLEXPRESS instance.

I don't sure about SQL Server Express edition. only I know it has fewer features rather than SQL Server 2005 Enterprise edition.

I really want an instance of SQL Server 2005 Enterprise edition.

What can I do?

Thanks Chad.

|||

I see. Well, if you're going to use the instance for development use only, your best bet is to get a copy of the Developer Edition, which is exactly the same as the Enterprise Edition, however it is not licensed for commercial/production use, only developmental/test/demo use.

If you want an Enterprise Version, then you'd need to purchase it.

Alternatively, you could download a 180-day trial version for free as well.

Do you have an MSDN subscription? If so, you can download the developer edition and use it. If not, you'll have to purchase it as well.

Go here for more information on the developer edition, trial edition, and information on how to get them:

http://www.microsoft.com/sql/editions/developer/default.mspx

HTH

How can I get and use an OUTPUT parameter

Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?

ALTER PROCEDURE [dbo].[NumberCounter]-- Add the parameters for the stored procedure here@.InsertDatedatetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereINSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);SelectIDENT_CURRENT('tblNumberCounter')ENDPublic Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs) Dim ConAs SqlConnection Dim StrInsertAs String Dim cmdInsertAs SqlCommand Dim myNewReceiptNumberAs Integer Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString'Add row to receipt table, then get it for ReceiptNumberText field. cmdInsert = New SqlCommand cmdInsert.CommandText = "NumberCounter" cmdInsert.CommandType = CommandType.StoredProcedure cmdInsert.Connection = New SqlConnection(ConnectStr) cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString()) Try Con.Open() myNewReceiptNumber = cmdInsert.ExecuteScalar() 'Response.Write(myNewReceiptNumber) Catch objExceptionAs SqlException Dim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally Con.Close()End TryEnd Sub

Try this...

ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@.InsertDatedatetime,
@.InsertedInteger int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);Select @.InsertedInteger =IDENT_CURRENT('tblNumberCounter')
END

Public Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs)

Dim ConAs SqlConnection
Dim StrInsertAs String
Dim cmdInsertAs SqlCommand
Dim myNewReceiptNumberAs Integer
Dim ConnectStrAs String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)

cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString())

'Create a new parameter with direction set to output, and add it to the parameters collection
dim InsertedInteger as new SqlParameter("@.InsertedInteger", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
cmdInsert.Parameters.Add(InsertedInteger)
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
MessageBox.Show(InsertedInteger.Value)
End Sub

|||Use SCOPE_IDENTITY() to get the ID of the record just inserted. IDENT_CURRENT will give you the ID for any session and any scope. So if the proc gets executed multiple times in parallel your Id's could get mixed up.|||Both of you have help very much.|||Welcome. Now can you mark the post as answered? Doesnt matter whose post you choose (you can choose both too). This helps in a number of ways especially when people search for posts with similar problem like yous.|||

Well, I could use a little more help with this:

I need to use the ID that is output in a query string, however, when I try to use it I get an error 'Object reference not set to an instance of an object'

Anyone see why this might happen??

Try cmdInsert.Connection.Open() InsertedInteger = cmdInsert.ExecuteScalar()Catch objExceptionAs SqlExceptionDim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally cmdInsert.Connection.Close() Response.Redirect("../editnew.aspx?ID=" & InsertedInteger.Value)End Try

|||

You would use ExecuteScalar to capture the output from your SELECT statement and it only captures one value. If you are using output parameters you would use ExecuteNonQuery and create parameters of type OUTPUT and use them to retrieve the values. You can use the sample code below and modify it accordingly:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="usp_testproc"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))myCommand.Parameters("@.userid").Value = Trim(userid)myCommand.Parameters.Add(New SqlParameter("@.ttype",SqlDbType.VarChar,25))myCommand.Parameters("@.ttype").Value ="charge"'output parametermyParam = mycommand.CreateParameter()myParam.ParameterName ="@.result"myParam.Direction = ParameterDirection.OutputmyParam.SqlDbType = SqlDbType.bigintmycommand.Parameters.Add(myParam)TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Response.Write(convert.todouble(mycommand.Parameters("@.result").Value))Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

How can I generate xsd files from existing tables?

I would like to generate xml schema files for my data model for later use with testing tools such as ndbunit. I realize that Visual Studio datasets can be used to do this, however, Visual Studio does not automatically determine table realtionships. I do not want to manually specify these relationships. I also do not have the luxury of using TeamServer for DB professionals. The Database Diagrams feature of SQL Server Management Studio seems capable of determining the table relationships on its own. However, I don't see any way to simply export a databse diagram to an xml schema definition. I realize I could write code to do this myself using the nice APIs under the Microsoft.Sqlserver.management and System.xml namespace, but I'm trying to avoid doing this. Is there a way to do this with SQL Server Management Studio?

Would something like this work for you? It's not technically a "right-click" operation, but it does generate the schema:

SELECT *
FROM Customers
FOR XML RAW('Customer'), XMLSCHEMA('urn:example.com')
-

<xsd:schema targetNamespace="urn:example.com"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">
<xsd:import namespace=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
<xsd:element name="Customer">
...
</xsd:element>
</xsd:schema>
<Customer xmlns="urn:example.com" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" />
...

|||Thanks for the suggestion. This will work fine for my needs!|||

I should be a little clearer. If you want just the schema for the table, select something that returns all nulls or an empty set, but make sure you use the XMLSCHEMA option. You can find more about that in Books Online - past this in the "URL" bar of BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/04b35145-1cca-45f4-9eb7-990abf2e647d.htm

Buck

|||When using the XMLSCHEMA option, I cannot seem to get the generated schema to contain foreign key constraints. I looked over the MSDN documentation, but I have not found any specific options related to this. Is this a limitation or am I simply missing some parameters that will cause the contraints to be included?|||You're not missing anything - it definitely won't do that. I would suggest you live.com search for a "database documenter" script (there are tons out there) and add the XMLSCHEMA to the end of one of those. That should give you a database design in an XML document.

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards