Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical fil
e
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restore
d to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
--
Senior SQL Server DBAHi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
--
Senior SQL Server DBA
"doller" wrote:

> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
>|||Seems like SQL Server cannot create the database file names you have specifi
ed. Perhaps the service
account don't have permissions, or that the file names are already in use by
some other database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA

Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical file
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
Senior SQL Server DBA
Hi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA
|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller
|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
Senior SQL Server DBA
"doller" wrote:

> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
>
|||Seems like SQL Server cannot create the database file names you have specified. Perhaps the service
account don't have permissions, or that the file names are already in use by some other database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STAN DBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA

Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical file
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
--
Senior SQL Server DBAHi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBA|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
--
Senior SQL Server DBA
"doller" wrote:
> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
> > I have a database i am m oving to another server, during the process I am
> > moving the data and log files to another drive.(Which I have done countless
> > times before with no problems)
> > The problem I am having is after I restore the database using the following
> > statement :
> > RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> > WITH STANDBY = 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> > ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\Databasename_Data.mdf'
> > ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\Databasename_Log.ndf'
> >
> > I get these errors when trying to restore transaction logs :
> >
> > [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> > name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> > may be incorrect.
> > [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> > 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> > WITH MOVE to identify a valid location for the file.
> >
> > Has anyone encountered the same problem? This has me stumped, although
> > restoring the tlog with move, and standby works...this is not how it should
> > happen.
> > --
> > Senior SQL Server DBA
>|||Seems like SQL Server cannot create the database file names you have specified. Perhaps the service
account don't have permissions, or that the file names are already in use by some other database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countless
> times before with no problems)
> The problem I am having is after I restore the database using the following
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databasename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical file
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restored to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it should
> happen.
> --
> Senior SQL Server DBAsql

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

Problem dropping replication support

I am working on establishing a merge replication process between SQL Server 2005 and SQL Mobile 2005. I started with a new SQL Server 2005 instance and went through the Sample for SQL Mobile merge replication. So far so good.

Today, I tried to drop all support for replication on my current SQL 2005 test instance, so that I can start from a fresh instance and do another. I used Management Studio to drop all publisher and distribution settings and had several errors occur, where some roles were not allowed to be dropped because they had membership in them.

I dropped all the users that I added to the SQL logins and tried again.

Now I am trying to run the following script:

use AdventureWorks
exec sp_replicationdboption @.dbname = N'AdventureWorks', @.optname = N'merge publish', @.value = N'false', @.ignore_distributor = 'true'

-- Dropping the distribution publishers
exec sp_dropdistpublisher @.publisher = N'XP-MIKED-LAPTOP'
GO

-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @.database = N'distribution'
GO

/****** Uninstalling the server XP-MIKED-LAPTOP as a Distributor. Script Date: 1/14/2006 2:16:29 PM ******/

use master
exec sp_dropdistributor @.no_checks = 1, @.ignore_distributor = 1
GO

The error I am getting from the first batch (sp_replicationdboption) is this:

Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.

To me it looks like all the publication objects have been already removed from AdventureWorks, but in sysdatabases, the category column still says 4 (merge publication). Since I can't just do this anymore:

UPDATE MASTER.DBO.SYSDATABASES
set category = 0
where dbid=8

I just don't know what I can do at this point. I can't even create a new publication in AdventureWorks because it thinks there is a sysmergepublications table in there and fails when there isn't.

try sp_removedbreplication.

|||

Greg Yvkoff wrote:

try sp_removedbreplication.

Yes, that was exactly it. I got to the end of the KB article 324401 and found that stored proc. It worked like a charm.

Problem deleting publication

Hi All,

I have a problem, a user deleted some tables from a couple db's dealing
with replication. I now get this error on those db's.

The process could not execute 'sp_repldone/sp_replcounters on
servername

I have deleted one of the database's and neither are listed under
publications but they still show up under replication monitor.

Any ideas on how to delete them would be great. When I try I get

Error 208: Invalid object name 'sysarticles'
Invalid object name 'sysschemaarticles'
Could not use view or function 'sysextendedarticlesview' because of
binding errors.

TIA
DaveI figured it out, hope this helps someone.

I went into the distribution db and went thru every table and removed
any reference to the databases that were giving me problems. That got
rid of the ghost entries in the replication monitor folder. Oddly I
still had the red X's on the folders them selves but nothing under the
folders had X's and a refresh didnt make them go away. A
sp_MSload_replication_status did though.

Now just the database of the two that were needed still had the repl
hand icon on the database. The good old sp_removedbreplication got rid
of that.

Thanks,
Dave