creating a server instance on SQL server 2005 enterprise

I got sql 2000 installed on my machine. I got a sql 2000 database instance on my computer and I installed sql 2005 server enterprise but I have got no server instance from sql 2005 like what i have in sql 2000 when i first install, i can have an own server instance on my computer.

is it becos when i have a previous sql 2000 server instance on my computer?

Your SQL server 2000 is probably installed as the 'default' instance. You can keep the default instance at version 2000, and install a 'named' instance for 2005. Or upgrade your default instance to 2005.

Look for the screen during setup that asks (radio buttons)

1) default instance, or

2) named instance. (to create a new named instance you need to type in a name for the instance)

|||how do i install a named instance? i need to uninstall sql 2005?|||i tried upgrading but the sql 2000 instance has errors when upgrading...|||i dun have the adventure works database....only northwind in sql 2000|||

You can create several named instances on the same computer, so you don't need to uninstall. (you can have 50 named instances with enterprise version)

To create a named instance from the command line you can type this: (this is a single command)

start /wait setup.exe /qb INSTANCENAME=brohans ADDLOCAL=SQL_Engine,Client_Components,SQLXML SAPWD=b2r0o0h6ans

Or use the setup interface and select the radio button for a named instance then type in brohans for the instance name.

|||

i install the adventureworks database....in my sql 2005

this article is helpful.

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

Creating a local copy of a database

Hi all,

I'm having a problem right now. I'm trying to create an offline copy of a database(live) in my local machine. The database is maintained in a different server machine and I want a copy of the particular DB as an offline copy.

The problem is there's DTS feature in SQL Server 2000 but I don't find any such tool in SQL Server 2005 edition. Can anyone please guide me on how to do this process?

Thanks & Regards,

ThunderRock

hi thunder,

Dts is now known as SSIS and it is found on VS2005

for the solution.

1. you can detach the database and copy the datafiles and attach it to the destination servers

2. you can use the database import export wizrd

|||

Hi Joeydj,

Thanks for the reply.

Now I've already done that but I wanted to know if there was some other method by which the complete Db can be imported. Because if we go by that method, we don't get the stored procedures as well as triggers and functions present in the source DB. So I want to know if there is any method by which the SPs, triggers as well as functions can be imported. We can write a query script and do it manually. But is there any features provided in Sql Server 2005 by which we can do it directly?

Another thing I noticed in the new database is that the data which get imported into the local machine(i.e the target DB) doesn't reflect the dependencies of the DB Objects present in the DB. So how do we go about solving this issue also?

Can you please guide on this? Anyone who have done this before and have some idea, please guide.

Thanks,

ThunderRock

|||

ThunderRock,

I just completed setting up database replication scenario that uses an SQL2005 db as a 'gold standard', and replication to a subscriber to an SqlExpress database. The subscriber db can be used offline for inquires in our case. We use the database snapshot to provide inquiry services for the subscriber when there is a network or preferred application downtime.

With replication options setup appropriately, you can have the replicated database include whatever objects you wish. Our case is only using snapshot replication, as that is all we require, but from what I understand other types of replication would work in like manner.

We get our source data from a foreign host system as a huge text file, and we use a custom app to transform it, and bulk copy it into our Sql2005 db. Then, we've setup jobs to perform the replication. On the subscriber end, we installed SqlExpress with Advanced Tools, and set it up as a subscriber to the Sql2005 db. We developed a simple windows form application to use for inquiry on the local database, and deployed the app to the subscriber desktop. It is functioning as intended and allows our users to inquire on the mission critical data during those odd periods of network or preferred application downtime.

Now, there may be some issues causing the issues you mention, there are some limitations on the amount of data, and supported memory configs, but our db has about 500,000 records, which is not huge, and not very many stored procedures at this point. So, we have not run into any showstoppers at this point.

Creating a Linked Server to Access DB

I'm trying to create a linked server to an Access database that resides on a separate machine.

On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.

If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.

The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.

I create the linked server using the following command:
exec sp_addlinkedserver
@.server = 'AccLinkedServer',
@.provider = 'Microsoft.jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\DatabaseServer\AccessDatabase.mdb'

If I run the SQL statement:

SELECT * FROM AccLinkedServer...AnyTableYouLike

I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\DatabaseServer\AccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.

Anyone got any other advice, before I go totally insane with this.

Cheers.But the linked server is registered with the sql server instance...

This doesn't make sense...are you sure you're pointing to the same instance...

can you see the linked server in EM?|||Originally posted by Brett Kaiser
But the linked server is registered with the sql server instance...

This doesn't make sense...are you sure you're pointing to the same instance...

can you see the linked server in EM?

A bit more detail may help explain. We have a server (MainServer) which runs SBS and SQL. We have another PC which we use as a server (DatabaseServer).

I'm developing on my PC (DevelopmentPC) with a copy of SQL Developer and VB6.

I have created on my PC a linked server (AccLinkedServer) to the DatabaseServer, which allows me full access to this.

I can also create a linked server (AccLinkedServer) on the MainServer to the same data, and also have full access to the data from the MainServer.

If I try to run the command select * from AccLinkedServer...tblDataTable (where AccLinkedServer is the MainServer version) from Query Analyser on my PC I get the error shown in my original post.

I would like to be able to access the data we store on DatabaseServer from within SQL/VB - it would save a lot of time for people.

Creating a DB User *not* on current Machine

During my development phase, I publish updates to my testing server
environment by literally detaching and copying my SQL Server DB files to tha
t
machine and re-attaching them. This works fine and is very convenient. The
only problem is that each time I do it, I have to create a user in my DB
after I have copied it to the destination server which matches the username
under which IIS is running web access. This is a user which does not exist o
n
the instance where I do my development.
I was hoping that there was a way to create a user in the database with a
windows login which deosn't exist on that machine, but it fails when I try t
o
do that, saying that that user doesn't exist. Is there *any* way to get a
Windows Authentication-style DB user to exist in my database where that user
doesn't exist in on the current server so that when I publish the database
files, I won't have to go in and create that user in the database each time?
Does this question make sense?
AlexOn Jun 11, 10:11 am, Alex Maghen <AlexMag...@.newsgroup.nospam> wrote:
> During my development phase, I publish updates to my testing server
> environment by literally detaching and copying my SQL Server DB files to t
hat
> machine and re-attaching them. This works fine and is very convenient. The
> only problem is that each time I do it, I have to create a user in my DB
> after I have copied it to the destination server which matches the usernam
e
> under which IIS is running web access. This is a user which does not exist
on
> the instance where I do my development.
> I was hoping that there was a way to create a user in the database with a
> windows login which deosn't exist on that machine, but it fails when I try
to
> do that, saying that that user doesn't exist. Is there *any* way to get a
> Windows Authentication-style DB user to exist in my database where that us
er
> doesn't exist in on the current server so that when I publish the database
> files, I won't have to go in and create that user in the database each tim
e?
> Does this question make sense?
> Alex
If windows login is a domain user , you can create that login in your
database server. If not , create a windows login in your database
server with the same name and (may be same password).Provide proper
acces to login .
Database user should also map to proper windows login .
After restoring the database run the sp on the restored database
exec sp_change_users_login 'auto_fix','database username'
this will link login with user|||Hello Alex,
From your description, I understand that you'd like to create a DB user to
exist in your database matched to a not existing Windows login before you
detach/attach the database to a new server.
I'm afraid this option is not available due to the behavior of SQL
Server.When you move a database from one server that is running SQL Server
to another server that is running SQL Server, a mismatch may occur between
the security identification numbers (SIDs) of the logins in the master
database and the users in the user database.
By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide
the sp_change_users_login system stored procedure to map these mismatched
users. However, you can only use the sp_change_users_login stored procedure
to map standard SQL Server logins and you must perform these mapping for
one user at a time.
In SQL Server 7.0 or later versions, you can maintain the mapping between
the logins in the master database and the users in the user database by
using the SIDs. This mapping is required to maintain correct permissions
for the logins in the user databases. When a login on the new server does
not have matched SID of one of the user on the database moved, you have to
create a new database base user to map them. Also, it's not possible to
create a local user on a different machine.
The best option is that you use a domain user so that the login exist on
the both SQL Servers. After you move the database, the SID is matched
automatically and you don't need to re-create the DB user for the login.
You may want to see the following article for more details:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
If anything is unclear or you have further comments, please feel free to
post back. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Just one bit of confusion I have about all of this: Creating a user in the
database which is based on a Windows User is no problem: I can create the
same username on both my Dev and Staging machines. But I *think* the problem
is that the username that you need to provide in SQL Server has the machine
name built into it:
<Machine Name>\<User Name>
So even if I have exactly the same user name on both machines, will I be
able to do what I want? By the way, this is SQL Server 2005.
Thanks again.
Alex
""Peter YangMSFT]"" wrote:

> Hello Alex,
> From your description, I understand that you'd like to create a DB user to
> exist in your database matched to a not existing Windows login before you
> detach/attach the database to a new server.
> I'm afraid this option is not available due to the behavior of SQL
> Server.When you move a database from one server that is running SQL Server
> to another server that is running SQL Server, a mismatch may occur between
> the security identification numbers (SIDs) of the logins in the master
> database and the users in the user database.
> By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide
> the sp_change_users_login system stored procedure to map these mismatched
> users. However, you can only use the sp_change_users_login stored procedur
e
> to map standard SQL Server logins and you must perform these mapping for
> one user at a time.
> In SQL Server 7.0 or later versions, you can maintain the mapping between
> the logins in the master database and the users in the user database by
> using the SIDs. This mapping is required to maintain correct permissions
> for the logins in the user databases. When a login on the new server does
> not have matched SID of one of the user on the database moved, you have to
> create a new database base user to map them. Also, it's not possible to
> create a local user on a different machine.
>
> The best option is that you use a domain user so that the login exist on
> the both SQL Servers. After you move the database, the SID is matched
> automatically and you don't need to re-create the DB user for the login.
> You may want to see the following article for more details:
>
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL Serve
r
> http://support.microsoft.com/?id=314546
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/kb/246133
> If anything is unclear or you have further comments, please feel free to
> post back. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello Alex,
As I mentioined, the database user is mapped to the SQL login by SID. Even
the user name on both machines are the same, the SIDs are the different.
Therefore, I don't think it could meet your requriement.
However, if you use a domin user such as domain1\user, you could add the
user as login on both server, and map to the database user at one server
for a database could be mapped to properly if the database is moved to
another database.
You could find the SIDs of logins and database users from the following
queries:
select * from master..syslogins
select * from sysusers
As Srinivas mentioned, sp_change_users_login could help in the situation if
you don't want to map the user/login yourself.
Please keep in mind that sp_change_users_login 'auto_fix' corrects an
orphan user by either:
1. If a matching login does not exist -> create it with a blank password.
2. If a matching login exists -> update SID
If you have further questions or comments on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>