Creating Additional Instances

How do I create additional instances of any MSSQL 2005 service(specifically database and analysis) given that a default instance is present?

You just need to run setup.exe again but this time do not install it as the default instance. In stead, you install it as a named instance. Under UI, you need to choose a different instance name. If you use a command line, the parameter INSTANCENAME=<NEW INSTANCE NAME> should be use.

|||

During the instance selection screen of the install, you can also click on the button to show currently installed instances to make sure that you don't pick the same instance name again.

Thanks,
Sam Lester (MSFT)

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATEPROCEDURE test

AS

BEGIN

print'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATE PROCEDURE test

AS

BEGIN

print 'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a newsgroup database with mssql

Need some idea about designing a model for newsgroup databaseI reccomend you start by learning normalization, lots of links out there, here's one that may help. http://www.sims.berkeley.edu/courses/is202/f98/Lecture12/sld001.htm

Might not be the answer you were looking for but will help you in the long run.

HTH|||Originally posted by rhigdon
I reccomend you start by learning normalization, lots of links out there, here's one that may help. http://www.sims.berkeley.edu/courses/is202/f98/Lecture12/sld001.htm

Might not be the answer you were looking for but will help you in the long run.

HTH

Thank you for the link

creating a folder on the server

Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
You can use xp_cmdshell to run internal commands such as MD, bat files and exe files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"robert" <rob@.ms.com> wrote in message news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how to
> call that.
> Any help would be appreciated
> Thanx
>
|||Another alternative is to do this as an ActiveX script task inside a DTS
package, which can be kicked off from your ASP page.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"robert" <rob@.ms.com> wrote in message
news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a script that runs on an IIS webserver. The webserver is on a
different domain as the MSSQL server. I have an asp page that uses DSNless
connection to connect to the SQL server and execute stored procedures to
create a database locally. Everything works fine and both the MDF and LOG
files get created. The problem is that I want the files to be created in a
new folder everytime with name passed to it by the asp page.
How can i do this, i can't create it through the asp page because the IIS
server cannot access the MSSQL's servers harddrive.
Is there a way to create a folder locally ?
I was thinking of using a batch file and execute that, but don't know how to
call that.
Any help would be appreciated
Thanx
|||Thanks will try it out.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ekf8gz6rFHA.464@.TK2MSFTNGP15.phx.gbl...
> Another alternative is to do this as an ActiveX script task inside a DTS
> package, which can be kicked off from your ASP page.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "robert" <rob@.ms.com> wrote in message
> news:%232bE672rFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a script that runs on an IIS webserver. The webserver is on a
> different domain as the MSSQL server. I have an asp page that uses DSNless
> connection to connect to the SQL server and execute stored procedures to
> create a database locally. Everything works fine and both the MDF and LOG
> files get created. The problem is that I want the files to be created in a
> new folder everytime with name passed to it by the asp page.
> How can i do this, i can't create it through the asp page because the IIS
> server cannot access the MSSQL's servers harddrive.
> Is there a way to create a folder locally ?
> I was thinking of using a batch file and execute that, but don't know how
> to
> call that.
> Any help would be appreciated
> Thanx
>

creating a database in SQL 2005 - using windows 2003

when i try to create a new database i get a error: The server could not
load DCOM

what is wrong and how can i fix it?

i have updated mssql/win 2003 with the lates updates

Rgds

BobbyHi Bobby

This usually has something to do with user permissions. Please ensure
that your database login has permissions to create new databases. Also
ensure that you have sufficient Windows user permissions to the
directory where SQL Server is trying to create the .mdf and the .ldf
file.

Cheers
Louis

Creating a database backup

Hi,

I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this?

Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything.

Thanks for any help.

I have fixed the problem now.

creating a back up [create and insert statement] for a table in mssql server 2005

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

Quote:

Originally Posted by bhakti

hi all,
i have a php application connecting to mssql server 2005. i want to generate sql script for backing up a table with create statement and complete inserts. i dont want to use data publishing wizard. how can i do it.

Thanks

or you can do a SELECT ... INTO NewTableName from MyTable...