db datatypes: mssql

Showing posts with label mssql. Show all posts

Monday, March 26, 2012

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

Problem with Install of MSSQL 2005 Standard x64 on two node cluster

Hi:
I'm trying to install MS SQL 2005 Standard x64 to a two node Windows Server
2003 R2 SP2 cluster.
I've setup the cluster as per MS requirements and tested failover, The
cluster seems to be working well.
When I go through the Install and select failover clustering for the
Database and Analysis services, all seems to go well until it gets to the
end, I get a failure on the second (passive) node.
On the passive node I find that there is a Task Scheduler job set, SQL
Server Remote Setup.job that is reporting this error:
"SQL Server Remote Setup .job" (setup.exe) 12/4/2007 12:40:14 PM ** ERROR
**
Unable to start task.
The specific error is:
0x80070005: Access is denied.
Try using the Task page Browse button to locate the application.
The only MS info, I've found is not to be in RDP on the second node. I've
tried that and even did an install from the console on the first node (No
Remote Desktop sessions at all). Same error.
I've made sure that the account used is part of the local administrators
group on both nodes and has the following rights:
Act as part of operating system
Logon as a batch job
Logon as a Service
I have been going around on this for several days and cannot come to a
solution.
Is it that the second node doesn't have access to the setup files? I tried
installing from the DVD on the primary node and copied the files to a
directory on the primary and shared it. Same error.
Thanks
Usually, the last thing you tried, moving the setup files, fixes this type
of issue.
Have you tried running the cluster install but only for the first node, then
adding the node via add/remove programs? I sometimes find mirroring the
regular cluster setup logic (build a single-node cluster and add on) helps.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Hons" <Paul.Hons@.unt.edu> wrote in message
news:4755520A.B695.00E7.0@.unt.edu...
> Hi:
> I'm trying to install MS SQL 2005 Standard x64 to a two node Windows
> Server
> 2003 R2 SP2 cluster.
> I've setup the cluster as per MS requirements and tested failover, The
> cluster seems to be working well.
> When I go through the Install and select failover clustering for the
> Database and Analysis services, all seems to go well until it gets to the
> end, I get a failure on the second (passive) node.
> On the passive node I find that there is a Task Scheduler job set, SQL
> Server Remote Setup.job that is reporting this error:
> "SQL Server Remote Setup .job" (setup.exe) 12/4/2007 12:40:14 PM ** ERROR
> **
> Unable to start task.
> The specific error is:
> 0x80070005: Access is denied.
> Try using the Task page Browse button to locate the application.
> The only MS info, I've found is not to be in RDP on the second node. I've
> tried that and even did an install from the console on the first node (No
> Remote Desktop sessions at all). Same error.
> I've made sure that the account used is part of the local administrators
> group on both nodes and has the following rights:
> Act as part of operating system
> Logon as a batch job
> Logon as a Service
> I have been going around on this for several days and cannot come to a
> solution.
> Is it that the second node doesn't have access to the setup files? I tried
> installing from the DVD on the primary node and copied the files to a
> directory on the primary and shared it. Same error.
> Thanks
>
>
|||Geoff N. Hiten wrote:
> Usually, the last thing you tried, moving the setup files, fixes this
> type of issue.
> Have you tried running the cluster install but only for the first
> node, then adding the node via add/remove programs? I sometimes find
> mirroring the regular cluster setup logic (build a single-node cluster
> and add on) helps.
>
One other thing to try - which I had to do to get around this issue.
Have you logged into both systems with the service account that you will
be using to run SQL Server? If not, make sure you log into both
machines with that account to create the user profile. For whatever
reason - without the user profile the task scheduler jobs cannot be started.
At least that is what worked for me.
BTW - before you restart your install make sure you uninstall the setup
files or you will get another error.
Jeff

problem with inserting or updating dates in mssql 2000

Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.

I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @.date1, sqldbtype date =texbox1.textYou can get the date as;

DateTime myDate = DateTime.ParseExact(txtDate.Text, "dd.M.yyyy", System.Globalization.CultureInfo.InvariantCulture);

Now that you have your text box's date as a DateTime variable you can supply that to a SQL parameter query in any format you want, such as dd MMM yyyy

|||I understand that.
I wanna know how to change format of date wich is inserted as d.M.yyyy in texbox to yyyy.M.d because it is passed to sp parameter and it must be yyyy.M.d to do successful insert/update?

Tuesday, March 20, 2012

Problem with Full Index Search

I have implemented Full Text Search for my MSSQL Database and got stuck with this problem:

When i try to search for the exact word 'Function 1' using the CONTAINS keyword, the query retrieving the following results too...

1. Function 1
2. Function 2
3. Function 3
4. Function 4

Where i want the first answer alone. But it works good with the strings (ex: Broken Wire) where both are strings.

Wont it work with the text which is a combination of a string and a number like 'Function 1'

Kindly guide me!!

Mumbaimacroothis is the query i have used

select distinct dStep2.DFMEA_NO REGNO,dStep2.ID,dStep3.Step2_ID,dStep3.FUNC_DESC DESCRIPTION from
FMEA_DFMEA_STEP2 dStep2, FMEA_DFMEA_STEP3 dStep3 WHERE dStep2.ID=dStep3.Step2_ID
AND CONTAINS(dStep3.FUNC_DESC, '"function 1"')

help to do a search for character + number combination|||Try this:

Friday, March 9, 2012

problem with duplicate keys

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Thank you for any suggestions.

Pawel BanysOn Thu, 5 Feb 2004 09:13:28 +0100 in comp.databases.ms-sqlserver,
"Pawel Banys" <voland@.dmz.com.pl> wrote:

>Hello,
>There is a program which performs some scripted actions via ODBC on tables
>in some database on mssql 2000. Sometimes that program tries to insert
>record with key that is already present in the database. The error comes up
>and the program stops.
>Is there any way to globally configure the database or the whole mssql
>server to ignore such attempts and let the script continue without any error
>when the script tries to insert duplicate-key records?

Maybe you should change your script to check for the keys first,
instead of blindly whacking stuff into them e.g.

insert into table1 (pk, column)
select pk, column from table2
where table1.pk not in (select pk from table2)

--
A)bort, R)etry, I)nfluence with large hammer.|||"Pawel Banys" <voland@.dmz.com.pl> wrote in message news:<bvsu0g$3fp$1@.atlantis.news.tpi.pl>...
> Hello,
> There is a program which performs some scripted actions via ODBC on tables
> in some database on mssql 2000. Sometimes that program tries to insert
> record with key that is already present in the database. The error comes up
> and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any error
> when the script tries to insert duplicate-key records?
> Thank you for any suggestions.
> Pawel Banys

Are you talking about error 2627 (violation of primary key
constraint)? If so, then the current batch should continue anyway,
unless of course the code has error handling to stop immediately on an
error.

Assuming you have control of the code, you should really avoid the
error instead:

insert into dbo.Destination (col1, col2, ...)
select col1, col2, ...
from dbo.Source s
where not exists (select * from dbo.Destination d
where d.PrimaryKey = s.PrimaryKey)

If you don't control the code, then I don't think there's much you can
do - the error can't be suppressed, and needs to be handled on the
client. Perhaps you can clean up the data somehow before passing it to
the program?

Simon|||Pawel Banys (voland@.dmz.com.pl) writes:
> There is a program which performs some scripted actions via ODBC on
> tables in some database on mssql 2000. Sometimes that program tries to
> insert record with key that is already present in the database. The
> error comes up and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any
> error when the script tries to insert duplicate-key records?

For a unique index - but not a PK or UNIQUE constraint - you can include
the option IGNORE_DUP_KEY. With this setting, SQL Server does not find
it an error condition if you try to insert a duplicate, but leaves @.@.error
unchanged. Unfortunately, though, the message that is raised, incorrectly
has severity level 16 and not 10, so a client may still belive that
things went wrong.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.

insert into server2.database.dbo.table select * from
server1.database.dbo.table

Adding a where clause would take much MUCH longer to perform vs. a
straight insert.

I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?

================
Jordan Bowness
================

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jordan Bowness (jb_at_bizeworld_dot_ca) writes:
> I too would like to be able to have it ignore errors thrown on an
> insert. I'm doing the insert over a WAN, so checking for all records on
> the destination server takes forever.
> insert into server2.database.dbo.table select * from
> server1.database.dbo.table
> Adding a where clause would take much MUCH longer to perform vs. a
> straight insert.
> I've been deleting all records from the destination table, but that's
> not a great way of doing it. Any way of having the insert simply ignore
> errors and continue?

There is the option that I discussed in the article you replied to. That
is, replace the primary key with a unique index with IGNORE_DUP_KEY.

Another possibility is to insert the data into a staging table on the
target server, and then call a stored procedure on the target server
which performs an INSERT targettbl SELECT * FROM staging WHERE NOT EXISTS.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm having a similar issue here, i'm using a Insert Trigger to add a row to an EXCEPTIONS table when a condition is met during the insert process. The condition could be met multiple times, but I only need 1 record to go into the EXCEPTIONS table per PK combination. I could limit this using a WHERE clause however the EXCEPTIONS table has a composite primary key and I'm not sure how to check for that.

INSERT INTO STAFFING_EXCEPTIONS ...
SELECT ...
FROM INSERTED I

INNER JOIN STAFFING_3W_REQ REQ ON (I.PROJECT_CODE = REQ.PROJECT_CODE AND I.LOB_CODE = REQ.LOB_CODE AND I.ROW_DATETIME = REQ.ROW_DATETIME AND I.FTE_GOAL != REQ.FTE_GOAL)

Monday, March 26, 2012

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

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

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

Problem with Install of MSSQL 2005 Standard x64 on two node cluster

problem with inserting or updating dates in mssql 2000

Tuesday, March 20, 2012

Problem with Full Index Search

Friday, March 9, 2012

problem with duplicate keys

db datatypes

Blog Archive

About Me