problem with joins

Hi

i have 2 tables called locationcode and emp.

tables are having so many records.

suppose the tables having the sample data like this:

locationcode table:

locationcode(field name)

1

2

3

emp table

empno empname locationcode (field names)

----------------------

2344 aaaa 1

2345 bbbb 1

2567 cccc 2

1234 dddd 3

16789 eeee 4

9890 fffff 4

i have to delete records from emp table the locationcode which is not matching with locationcode in locationcode table.

(in this sample data i have to delete records which are having location code as 4 from emp table)

i used joins,but i am not getting.

please help me.

Thanks.

Hello

Use left outer join

select

t1.empno, t1.empname, t1.locationcodefrom emp t1
leftouterjoin locationcode t2on t2.locationcode= t1.locationcode
where t2.locationcodeisnull

HTH

|||

hi,

i think this query can help you out.

DELETE FROM emp

where locationcode in

(SELECT locationcode from emp e, locationcode lc where e.locationcode <> lc.locationcode)

i've not tried this but i think it will work.

Happy Coding.

regards,

Muppidi.

|||

hi,

i think this query can help you out.

DELETE FROM emp

where locationcode in

(SELECT locationcode from emp e, locationcode lc where e.locationcode <> lc.locationcode)

i've not tried this but i think it will work.

Happy Coding.

regards,

Muppidi.

Problem with installation of 'Adventureworks' sample db provided by Microsoft

I was trying to install the 'Adventureworks' sample db to the following
path: D:\MSSQL\Data. After I run the installer, I tried to run the following
query at the Sql Server Management Studio as stated in the documentation:
exec sp_attach_db @.dbname=N'AdventureWorks',
@.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf ',
@.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
However, when I tried to execute this script, I got the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Any idea how can I resolve this?
Did you have 'Adventureworks' install on the server already?
Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
BOL?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67m2u$4no$1@.mawar.singnet.com.sg...
>I was trying to install the 'Adventureworks' sample db to the following
>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>following query at the Sql Server Management Studio as stated in the
>documentation:
> exec sp_attach_db @.dbname=N'AdventureWorks',
> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf ',
> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>
> However, when I tried to execute this script, I got the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 9, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> Any idea how can I resolve this?
>
|||Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
DATABASE ... FOR ATTACH option and what's a BOL?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Did you have 'Adventureworks' install on the server already?
> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
> BOL?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>
|||I meant I have installed the 'Adventureworks' db (the installation runs
smoothly so I assumed that). However, it does not appear in the Sql Server
Management Studio. I referred to the documentation and it says that I need
to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
that, I got the error.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67qus$585$1@.mawar.singnet.com.sg...
> Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
> DATABASE ... FOR ATTACH option and what's a BOL?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>
|||BOL -is Books On Line
Copied from BOL
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67rjb$58l$1@.mawar.singnet.com.sg...
>I meant I have installed the 'Adventureworks' db (the installation runs
>smoothly so I assumed that). However, it does not appear in the Sql Server
>Management Studio. I referred to the documentation and it says that I need
>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
>that, I got the error.
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67qus$585$1@.mawar.singnet.com.sg...
>
|||Hi Uri,
I tried to execute the script that you gave and got the following error:
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'Archive' does not exist. Use sp_helpdb to show available
databases.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'master.sys.master_files'.
Any idea what went wrong?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
> BOL -is Books On Line
> Copied from BOL
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>
|||Hi
Sorry, my mistake. I did not tell you changde the name of database. It was
taken from BOL, so please modify it for your needs.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f687ge$5q0$1@.mawar.singnet.com.sg...
> Hi Uri,
> I tried to execute the script that you gave and got the following error:
> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
> The database 'Archive' does not exist. Use sp_helpdb to show available
> databases.
> Msg 208, Level 16, State 1, Line 3
> Invalid object name 'master.sys.master_files'.
> Any idea what went wrong?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>
|||Well, I did change the db name but the error is still there. Anyway, here's
my script.
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
FOR ATTACH');
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi
> Sorry, my mistake. I did not tell you changde the name of database. It was
> taken from BOL, so please modify it for your needs.
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>
|||Hi
Do you have SQL Server 2000 installed on thr machine? Check out that you do
not connect to SQL Server 2000 when you run the script
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f688kd$5si$1@.mawar.singnet.com.sg...
> Well, I did change the db name but the error is still there. Anyway,
> here's my script.
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
> FOR ATTACH');
> GO
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>
|||Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I tried
to stop the Sql Server Service Manager, but it stopped both versions of the
sql server instead).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
> Hi
> Do you have SQL Server 2000 installed on thr machine? Check out that you
> do not connect to SQL Server 2000 when you run the script
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f688kd$5si$1@.mawar.singnet.com.sg...
>
sql

Problem with installation of 'Adventureworks' sample db provided by Microsoft

I was trying to install the 'Adventureworks' sample db to the following
path: D:\MSSQL\Data. After I run the installer, I tried to run the following
query at the Sql Server Management Studio as stated in the documentation:
exec sp_attach_db @.dbname=N'AdventureWorks',
@.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
@.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
However, when I tried to execute this script, I got the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Any idea how can I resolve this?Did you have 'Adventureworks' install on the server already?
Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
BOL?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67m2u$4no$1@.mawar.singnet.com.sg...
>I was trying to install the 'Adventureworks' sample db to the following
>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>following query at the Sql Server Management Studio as stated in the
>documentation:
> exec sp_attach_db @.dbname=N'AdventureWorks',
> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>
> However, when I tried to execute this script, I got the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 9, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> Any idea how can I resolve this?
>|||Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
DATABASE ... FOR ATTACH option and what's a BOL?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Did you have 'Adventureworks' install on the server already?
> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
> BOL?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index ID
>> 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>|||I meant I have installed the 'Adventureworks' db (the installation runs
smoothly so I assumed that). However, it does not appear in the Sql Server
Management Studio. I referred to the documentation and it says that I need
to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
that, I got the error.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67qus$585$1@.mawar.singnet.com.sg...
> Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
> DATABASE ... FOR ATTACH option and what's a BOL?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
>> BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>|||BOL -is Books On Line
Copied from BOL
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67rjb$58l$1@.mawar.singnet.com.sg...
>I meant I have installed the 'Adventureworks' db (the installation runs
>smoothly so I assumed that). However, it does not appear in the Sql Server
>Management Studio. I referred to the documentation and it says that I need
>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
>that, I got the error.
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>|||Hi Uri,
I tried to execute the script that you gave and got the following error:
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'Archive' does not exist. Use sp_helpdb to show available
databases.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'master.sys.master_files'.
Any idea what went wrong?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
> BOL -is Books On Line
> Copied from BOL
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql Server
>>Management Studio. I referred to the documentation and it says that I need
>>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I
>>did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the following
>>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>>following query at the Sql Server Management Studio as stated in the
>>documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1, index
>> ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>>
>|||Hi
Sorry, my mistake. I did not tell you changde the name of database. It was
taken from BOL, so please modify it for your needs.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f687ge$5q0$1@.mawar.singnet.com.sg...
> Hi Uri,
> I tried to execute the script that you gave and got the following error:
> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
> The database 'Archive' does not exist. Use sp_helpdb to show available
> databases.
> Msg 208, Level 16, State 1, Line 3
> Invalid object name 'master.sys.master_files'.
> Any idea what went wrong?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>I was trying to install the 'Adventureworks' sample db to the
>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>run the following query at the Sql Server Management Studio as stated
>>in the documentation:
>> exec sp_attach_db @.dbname=N'AdventureWorks',
>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>
>> However, when I tried to execute this script, I got the following
>> error:
>>
>> Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>> aborted.
>> Msg 602, Level 21, State 50, Line 1
>> Could not find row in sysindexes for database ID 9, object ID 1,
>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>
>> Any idea how can I resolve this?
>>
>>
>>
>>
>|||Well, I did change the db name but the error is still there. Anyway, here's
my script.
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
FOR ATTACH');
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi
> Sorry, my mistake. I did not tell you changde the name of database. It was
> taken from BOL, so please modify it for your needs.
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Did you have 'Adventureworks' install on the server already?
>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in
>> the BOL?
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>>run the following query at the Sql Server Management Studio as stated
>>>in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>
>>
>>
>>
>>
>|||Hi
Do you have SQL Server 2000 installed on thr machine? Check out that you do
not connect to SQL Server 2000 when you run the script
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f688kd$5si$1@.mawar.singnet.com.sg...
> Well, I did change the db name but the error is still there. Anyway,
> here's my script.
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
> FOR ATTACH');
> GO
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation runs
>>smoothly so I assumed that). However, it does not appear in the Sql
>>Server Management Studio. I referred to the documentation and it says
>>that I need to run the "exec sp_attach_db @.dbname=N'AdventureWorks'"
>>script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried to
>>>run the following query at the Sql Server Management Studio as
>>>stated in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I tried
to stop the Sql Server Service Manager, but it stopped both versions of the
sql server instead).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
> Hi
> Do you have SQL Server 2000 installed on thr machine? Check out that you
> do not connect to SQL Server 2000 when you run the script
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>I meant I have installed the 'Adventureworks' db (the installation
>>runs smoothly so I assumed that). However, it does not appear in the
>>Sql Server Management Studio. I referred to the documentation and it
>>says that I need to run the "exec sp_attach_db
>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>I was trying to install the 'Adventureworks' sample db to the
>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>to run the following query at the Sql Server Management Studio as
>>>stated in the documentation:
>>>
>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>
>>>
>>> However, when I tried to execute this script, I got the following
>>> error:
>>>
>>>
>>> Msg 1813, Level 16, State 2, Line 1
>>>
>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>> aborted.
>>>
>>> Msg 602, Level 21, State 50, Line 1
>>>
>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>
>>>
>>>
>>> Any idea how can I resolve this?
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but I
am still getting the same error. What else am I missing?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f689ud$m6m$1@.reader01.singnet.com.sg...
> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
> tried to stop the Sql Server Service Manager, but it stopped both versions
> of the sql server instead).
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that you
>> do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show available
>> databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>> BOL -is Books On Line
>> Copied from BOL
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in the
>>>Sql Server Management Studio. I referred to the documentation and it
>>>says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use the
>>> CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>> Did you have 'Adventureworks' install on the server already?
>>>
>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see details
>>> in the BOL?
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>>to run the following query at the Sql Server Management Studio as
>>>>stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the following
>>>> error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>>> aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi
Now, you connect to SSMS and make sure that you are connected to SQL Server
2005 instance. Run the script again
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f68b6b$m7s$1@.reader01.singnet.com.sg...
> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but
> I am still getting the same error. What else am I missing?
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that you
>> do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database. It
>> was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>> Hi Uri,
>> I tried to execute the script that you gave and got the following
>> error:
>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>> The database 'Archive' does not exist. Use sp_helpdb to show
>> available databases.
>> Msg 208, Level 16, State 1, Line 3
>> Invalid object name 'master.sys.master_files'.
>> Any idea what went wrong?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in the
>>>Sql Server Management Studio. I referred to the documentation and it
>>>says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I tried
>>>>to run the following query at the Sql Server Management Studio as
>>>>stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE is
>>>> aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID 1,
>>>> index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Nope, it didn't work. Still got the same error
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hi
> Now, you connect to SSMS and make sure that you are connected to SQL
> Server 2005 instance. Run the script again
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager but
>> I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Sorry, my mistake. I did not tell you changde the name of database.
>> It was taken from BOL, so please modify it for your needs.
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>I meant I have installed the 'Adventureworks' db (the installation
>>>runs smoothly so I assumed that). However, it does not appear in
>>>the Sql Server Management Studio. I referred to the documentation
>>>and it says that I need to run the "exec sp_attach_db
>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the
>>>error.
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>tried to run the following query at the Sql Server Management
>>>>Studio as stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>> is aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID
>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Hi
Hmmm, I have no idea any more. The last one is permission issue. Do you
have full permissions on the folders where database files are located?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f6a03o$nd0$1@.reader01.singnet.com.sg...
> Nope, it didn't work. Still got the same error
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Now, you connect to SSMS and make sure that you are connected to SQL
>> Server 2005 instance. Run the script again
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager
>> but I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>> Well, I did change the db name but the error is still there. Anyway,
>> here's my script.
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>> FOR ATTACH');
>> GO
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>>> Hi
>>> Sorry, my mistake. I did not tell you changde the name of database.
>>> It was taken from BOL, so please modify it for your needs.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>> BOL -is Books On Line
>>> Copied from BOL
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>>I meant I have installed the 'Adventureworks' db (the installation
>>>>runs smoothly so I assumed that). However, it does not appear in
>>>>the Sql Server Management Studio. I referred to the documentation
>>>>and it says that I need to run the "exec sp_attach_db
>>>>@.dbname=N'AdventureWorks'" script. When I did that, I got the
>>>>error.
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>tried to run the following query at the Sql Server Management
>>>>Studio as stated in the documentation:
>>>>
>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>
>>>>
>>>> However, when I tried to execute this script, I got the
>>>> following error:
>>>>
>>>>
>>>> Msg 1813, Level 16, State 2, Line 1
>>>>
>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>> is aborted.
>>>>
>>>> Msg 602, Level 21, State 50, Line 1
>>>>
>>>> Could not find row in sysindexes for database ID 9, object ID
>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>
>>>>
>>>>
>>>> Any idea how can I resolve this?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||Yup, I do have the full permision for the folders where the database files
are kept. Think I'll just uninstall the database and install it again with
its default path to see if it works. Anyway, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eoF$JqHvHHA.3748@.TK2MSFTNGP04.phx.gbl...
> Hi
> Hmmm, I have no idea any more. The last one is permission issue. Do you
> have full permissions on the folders where database files are located?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f6a03o$nd0$1@.reader01.singnet.com.sg...
>> Nope, it didn't work. Still got the same error
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oq8P7LGvHHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Now, you connect to SSMS and make sure that you are connected to SQL
>> Server 2005 instance. Run the script again
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f68b6b$m7s$1@.reader01.singnet.com.sg...
>> Ok, I managed to disconnect Sql Server 2000 in the Enterprise Manager
>> but I am still getting the same error. What else am I missing?
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f689ud$m6m$1@.reader01.singnet.com.sg...
>> Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I
>> tried to stop the Sql Server Service Manager, but it stopped both
>> versions of the sql server instead).
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> Do you have SQL Server 2000 installed on thr machine? Check out that
>> you do not connect to SQL Server 2000 when you run the script
>>
>>
>>
>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>> news:f688kd$5si$1@.mawar.singnet.com.sg...
>>> Well, I did change the db name but the error is still there. Anyway,
>>> here's my script.
>>>
>>> USE master;
>>> GO
>>> sp_detach_db Archive;
>>> GO
>>> -- Get the SQL Server data path
>>> DECLARE @.data_path nvarchar(256);
>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>> FROM master.sys.master_files
>>> WHERE database_id = 1 AND file_id = 1);
>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>> EXEC ('CREATE DATABASE Archive
>>> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
>>> FOR ATTACH');
>>> GO
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>>> Hi
>>> Sorry, my mistake. I did not tell you changde the name of database.
>>> It was taken from BOL, so please modify it for your needs.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>>> Hi Uri,
>>>
>>> I tried to execute the script that you gave and got the following
>>> error:
>>>
>>> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
>>> The database 'Archive' does not exist. Use sp_helpdb to show
>>> available databases.
>>> Msg 208, Level 16, State 1, Line 3
>>> Invalid object name 'master.sys.master_files'.
>>>
>>> Any idea what went wrong?
>>>
>>>
>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>>>> BOL -is Books On Line
>>>> Copied from BOL
>>>>
>>>> USE master;
>>>> GO
>>>> sp_detach_db Archive;
>>>> GO
>>>> -- Get the SQL Server data path
>>>> DECLARE @.data_path nvarchar(256);
>>>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>>>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>>>> FROM master.sys.master_files
>>>> WHERE database_id = 1 AND file_id = 1);
>>>> -- Execute CREATE DATABASE FOR ATTACH statement
>>>> EXEC ('CREATE DATABASE Archive
>>>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>>>> FOR ATTACH');
>>>> GO
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>>>>I meant I have installed the 'Adventureworks' db (the
>>>>installation runs smoothly so I assumed that). However, it does
>>>>not appear in the Sql Server Management Studio. I referred to the
>>>>documentation and it says that I need to run the "exec
>>>>sp_attach_db @.dbname=N'AdventureWorks'" script. When I did that,
>>>>I got the error.
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67qus$585$1@.mawar.singnet.com.sg...
>>>> Yup I have. Anyway, pardon me for my ignorance but how do I use
>>>> the CREATE DATABASE ... FOR ATTACH option and what's a BOL?
>>>>
>>>>
>>>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>>>> Did you have 'Adventureworks' install on the server already?
>>>>
>>>> Have you tried CREATE DATABASE ... FOR ATTACH option , see
>>>> details in the BOL?
>>>>
>>>>
>>>>
>>>>
>>>> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
>>>> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>>>>>I was trying to install the 'Adventureworks' sample db to the
>>>>>following path: D:\MSSQL\Data. After I run the installer, I
>>>>>tried to run the following query at the Sql Server Management
>>>>>Studio as stated in the documentation:
>>>>>
>>>>> exec sp_attach_db @.dbname=N'AdventureWorks',
>>>>> @.filename1=N'D:\MSSQL\Data\AdventureWorks_Data.mdf',
>>>>> @.filename2=N'D:\MSSQL\Data\AdventureWorks_log.ldf'
>>>>>
>>>>>
>>>>> However, when I tried to execute this script, I got the
>>>>> following error:
>>>>>
>>>>>
>>>>> Msg 1813, Level 16, State 2, Line 1
>>>>>
>>>>> Could not open new database 'AdventureWorks'. CREATE DATABASE
>>>>> is aborted.
>>>>>
>>>>> Msg 602, Level 21, State 50, Line 1
>>>>>
>>>>> Could not find row in sysindexes for database ID 9, object ID
>>>>> 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
>>>>>
>>>>>
>>>>>
>>>>> Any idea how can I resolve this?
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Problem with installation of 'Adventureworks' sample db provided by Microsoft

I was trying to install the 'Adventureworks' sample db to the following
path: D:\MSSQL\Data. After I run the installer, I tried to run the following
query at the Sql Server Management Studio as stated in the documentation:
exec sp_attach_db @.dbname=N'AdventureWorks',
@.filename1=N'D:\MSSQL\Data\AdventureWork
s_Data.mdf',
@.filename2=N'D:\MSSQL\Data\AdventureWork
s_log.ldf'
However, when I tried to execute this script, I got the following error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Any idea how can I resolve this?Did you have 'Adventureworks' install on the server already?
Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
BOL?
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67m2u$4no$1@.mawar.singnet.com.sg...
>I was trying to install the 'Adventureworks' sample db to the following
>path: D:\MSSQL\Data. After I run the installer, I tried to run the
>following query at the Sql Server Management Studio as stated in the
>documentation:
> exec sp_attach_db @.dbname=N'AdventureWorks',
> @.filename1=N'D:\MSSQL\Data\AdventureWork
s_Data.mdf',
> @.filename2=N'D:\MSSQL\Data\AdventureWork
s_log.ldf'
>
> However, when I tried to execute this script, I got the following error:
>
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
> Msg 602, Level 21, State 50, Line 1
> Could not find row in sysindexes for database ID 9, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
>
> Any idea how can I resolve this?
>|||Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
DATABASE ... FOR ATTACH option and what's a BOL?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Did you have 'Adventureworks' install on the server already?
> Have you tried CREATE DATABASE ... FOR ATTACH option , see details in the
> BOL?
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67m2u$4no$1@.mawar.singnet.com.sg...
>|||I meant I have installed the 'Adventureworks' db (the installation runs
smoothly so I assumed that). However, it does not appear in the Sql Server
Management Studio. I referred to the documentation and it says that I need
to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
that, I got the error.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67qus$585$1@.mawar.singnet.com.sg...
> Yup I have. Anyway, pardon me for my ignorance but how do I use the CREATE
> DATABASE ... FOR ATTACH option and what's a BOL?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23%23CvUU8uHHA.1164@.TK2MSFTNGP02.phx.gbl...
>|||BOL -is Books On Line
Copied from BOL
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f67rjb$58l$1@.mawar.singnet.com.sg...
>I meant I have installed the 'Adventureworks' db (the installation runs
>smoothly so I assumed that). However, it does not appear in the Sql Server
>Management Studio. I referred to the documentation and it says that I need
>to run the "exec sp_attach_db @.dbname=N'AdventureWorks'" script. When I did
>that, I got the error.
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67qus$585$1@.mawar.singnet.com.sg...
>|||Hi Uri,
I tried to execute the script that you gave and got the following error:
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'Archive' does not exist. Use sp_helpdb to show available
databases.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'master.sys.master_files'.
Any idea what went wrong?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
> BOL -is Books On Line
> Copied from BOL
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f67rjb$58l$1@.mawar.singnet.com.sg...
>|||Hi
Sorry, my mistake. I did not tell you changde the name of database. It was
taken from BOL, so please modify it for your needs.
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f687ge$5q0$1@.mawar.singnet.com.sg...
> Hi Uri,
> I tried to execute the script that you gave and got the following error:
> Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
> The database 'Archive' does not exist. Use sp_helpdb to show available
> databases.
> Msg 208, Level 16, State 1, Line 3
> Invalid object name 'master.sys.master_files'.
> Any idea what went wrong?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23eT0cN9uHHA.3444@.TK2MSFTNGP04.phx.gbl...
>|||Well, I did change the db name but the error is still there. Anyway, here's
my script.
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
FOR ATTACH');
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi
> Sorry, my mistake. I did not tell you changde the name of database. It was
> taken from BOL, so please modify it for your needs.
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f687ge$5q0$1@.mawar.singnet.com.sg...
>|||Hi
Do you have SQL Server 2000 installed on thr machine? Check out that you do
not connect to SQL Server 2000 when you run the script
"Silvertype" <rogerlek@.singnet.com.sg> wrote in message
news:f688kd$5si$1@.mawar.singnet.com.sg...
> Well, I did change the db name but the error is still there. Anyway,
> here's my script.
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'AdventureWorks_Data.mdf'')
> FOR ATTACH');
> GO
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e1I3FW%23uHHA.1212@.TK2MSFTNGP05.phx.gbl...
>|||Well, I do have Sql Server 2000. Anyway, how do I disconnect that? (I tried
to stop the Sql Server Service Manager, but it stopped both versions of the
sql server instead).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OyOJso%23uHHA.4720@.TK2MSFTNGP06.phx.gbl...
> Hi
> Do you have SQL Server 2000 installed on thr machine? Check out that you
> do not connect to SQL Server 2000 when you run the script
>
>
>
> "Silvertype" <rogerlek@.singnet.com.sg> wrote in message
> news:f688kd$5si$1@.mawar.singnet.com.sg...
>

Problem with Forms Security Extension Sample - RS 2005

I am attempting to deploy the sample Security Extensions. When I navigate to <machinename>/reports in IE, it works fine and I am able to register the administrator. The user exists in the table correctly, but I am unable to login as the user. It keeps going back to the same uilogon.aspx redirect page.

It appears to be a similar error as http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=289636&SiteID=17

However, I am using the machine name the entire name, never referring to it as localhost. Any ideas? Thanks!

*bump* Any help would be much appreciated. I've gone through all of the config code changes time and time again and everything is as it should be from the sample code.

Problem with Forms Security Extension Sample - RS 2005

I am attempting to deploy the sample Security Extensions. When I navigate to <machinename>/reports in IE, it works fine and I am able to register the administrator. The user exists in the table correctly, but I am unable to login as the user. It keeps going back to the same uilogon.aspx redirect page.

It appears to be a similar error as http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=289636&SiteID=17

However, I am using the machine name the entire name, never referring to it as localhost. Any ideas? Thanks!

*bump* Any help would be much appreciated. I've gone through all of the config code changes time and time again and everything is as it should be from the sample code.

Problem with Forms Authentication

I've followed the Using Forms Authentication in Reporting Services
sample at
MSDN(http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx)
and have the following problem:
After I'm "authenticated" in the UILogon.aspx page, it posts back to
itself...so, I'm always stuck at
http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx.
Any suggestions?Try accessing using your machine name instead of localhost
Example:
http://MyServer/Reports
"S0103" wrote:
> I've followed the Using Forms Authentication in Reporting Services
> sample at
> MSDN(http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx)
> and have the following problem:
> After I'm "authenticated" in the UILogon.aspx page, it posts back to
> itself...so, I'm always stuck at
> http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx.
> Any suggestions?
>|||I had that problem. The solution for me was to turn off my anti-virus system
scan. It actually caused a lot of other problems too. My final solution was
to exclude the Reporting Services folders from the scan. After that the
redirect worked fine.
Grahame
"S0103" wrote:
> I've followed the Using Forms Authentication in Reporting Services
> sample at
> MSDN(http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx)
> and have the following problem:
> After I'm "authenticated" in the UILogon.aspx page, it posts back to
> itself...so, I'm always stuck at
> http://localhost/Reports/Pages/UILogon.aspx?ReturnUrl=http%3a%2f%2flocalhost%2fReports%2fPages%2fFolder.aspx.
> Any suggestions?
>|||That was it! I've got to stop accessing this group through Google...My
post didn't show up until 5 hours after I posted!|||Glad I was able to help... Happy Dot netting
"S0103" wrote:
> That was it! I've got to stop accessing this group through Google...My
> post didn't show up until 5 hours after I posted!
>

problem with filtering data

Hello,

I have a simple table containing adresses.
A sample view of the table is

id name city
----------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...

The view is ordered by (name,city)

Now my problem:

I want to see only the second half of the view
starting from "Olson" in "Dublin"

Using the constraint

where (name>="Olson") and (city>="Dublin")

does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do

where name+"~"+city>="Olson~Dublin"

not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.

This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.

Does anyone has an idea or suggestion?

-Hubert> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.

What index? Please post table DDL and INSERT statements.

> The view is ordered by (name,city)

Is name/city unique? If not, you should add ID to the ORDER BY and your
criteria so that you can skip rows with identical values in those columns.

> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.

The example below should perform well with proper indexing. The column
value concatenation method prevents the efficient use of indexes.

CREATE TABLE Addresses
(
ID int NOT NULL
CONSTRAINT PK_Addresses PRIMARY KEY ,
Name varchar(20) NULL,
City varchar(20) NULL
)

ALTER TABLE Addresses
ADD CONSTRAINT UQ_Addresses UNIQUE (Name, City)

INSERT INTO Addresses SELECT 100, 'Meier', 'New York'
UNION ALL SELECT 101, 'Meier', 'Tokyo'
UNION ALL SELECT 110, 'Olson', 'Amsterdam'
UNION ALL SELECT 110, 'Olson', 'Dublin'
UNION ALL SELECT 111, 'Paul', 'Berlin'
UNION ALL SELECT 200, NULL, NULL
UNION ALL SELECT 201, 'n', NULL
UNION ALL SELECT 202, NULL, 'b'
GO

DECLARE @.Name varchar(20)
DECLARE @.City varchar(20)

SET @.Name = 'Olson'
SET @.City = 'Dublin'

SELECT ID, Name, Address
FROM Addresses
WHERE
(Name > @.Name OR (@.Name IS NULL AND Name IS NOT NULL)) OR
((Name = @.Name OR (Name IS NULL AND @.Name IS NULL)) AND
(City >= @.City OR
(@.City IS NULL AND City IS NULL) OR
(@.City IS NULL AND City IS NOT NULL)))
ORDER BY Name, City

--
Hope this helps.

Dan Guzman
SQL Server MVP

<hubmei75@.web.de> wrote in message
news:1137254101.865303.137270@.g44g2000cwa.googlegr oups.com...
> Hello,
> I have a simple table containing adresses.
> A sample view of the table is
> id name city
> ----------
> 100 Meier New York
> 101 Meier Tokyo
> 110 Olson Amsterdam
> 110 Olson Dublin
> 111 Paul Berlin
> ...
> The view is ordered by (name,city)
> Now my problem:
> I want to see only the second half of the view
> starting from "Olson" in "Dublin"
> Using the constraint
> where (name>="Olson") and (city>="Dublin")
> does not the right thing. It eliminates i.e. row 111.
> The only idea I have is to concatenate the fields to
> simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.
> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.
> Does anyone has an idea or suggestion?
> -Hubert|||(comp.databases.mysql removed from groups list)

On 14 Jan 2006 07:55:01 -0800, hubmei75@.web.de wrote:

(snip)
>The only idea I have is to concatenate the fields to
>simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
>not to forget to struggle with null fields to get
>the right results. This slows down the simple query
>dramatically.
>This seems to be a complicated solution for a simple
>problem, given the fact, that the index is already
>available at the server.
>Does anyone has an idea or suggestion?

Hi Hubert,

Here's a version that makes optimal use of existing indexes on name or
name + city columns:

WHERE name >= 'Olson'
AND ( name > 'Olson OR city > 'Dublin' )

--
Hugo Kornelis, SQL Server MVP|||hubmei75@.web.de (hubmei75@.web.de) writes:
> The view is ordered by (name,city)

Views are by definition unordered. Yes, you can add TOP 100 PERCENT
and ORDER BY, but it does not really mean anything.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hubmei75@.web.de wrote:
> Hello,
> I have a simple table containing adresses.
> A sample view of the table is
> id name city
> ----------
> 100 Meier New York
> 101 Meier Tokyo
> 110 Olson Amsterdam
> 110 Olson Dublin
> 111 Paul Berlin
> ...
> The view is ordered by (name,city)
> Now my problem:
> I want to see only the second half of the view
> starting from "Olson" in "Dublin"
> Using the constraint
> where (name>="Olson") and (city>="Dublin")
> does not the right thing. It eliminates i.e. row 111.
> The only idea I have is to concatenate the fields to
> simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.
> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.
> Does anyone has an idea or suggestion?
> -Hubert

WHERE (name = 'Olson' AND city >= 'Dublin') OR name > 'Olson'

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@.attglobal.net
==================|||Thanks to all! You helped me very much.
Sometimes I am thinking too complicated
to see the straight solutions :)

-Hubert