Changing the default database files path fails

i have the following code segment which works fine as is:

try

{

ServerConnection connection = new ServerConnection("localhost");

Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))

{

Database database = new Database(server, "SMO_TEST");

//FileGroup fileGroup = new FileGroup(database, "PRIMARY");

//database.FileGroups.Add(fileGroup);

//DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");

//dataFile.FileName = "D:\\SMO_TEST_Data.mdf";

//fileGroup.Files.Add(dataFile);

//LogFile logFile = new LogFile(database, "SMO_TEST_Log");

//logFile.FileName = "D:\\SMO_TEST_Log.ldf";

//database.LogFiles.Add(logFile);

database.Create();

}

}

catch (FailedOperationException ex)

{

Console.WriteLine(ex.Message);

}

when i uncomment the lines commented above it fails.

Any help?

Fails with which error ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

hi Jenz

thanks for your reply. the code posted earlier worked smoothly just after a computer restart.the error message was either

"Create failed for Database 'SMO_TEST'." or "Drop failed for Database 'SMO_TEST'." with whatever mistake i made, which provided no help in correcting the problem.

however another issue arised when i tried to use a transaction using either method connection.BeginTransaction(); or server.ConnectionContext.BeginTransaction();

(1) how to correct this problem? and

(2) what kind of transaction occures here? is it a distributed transaction?

also i would like to ask

(3)if there is any way to diable full text indexing other than using

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");?(see the code below) .

sql server management studio creates the database with full text indexing disabled by default. but when using smo it is enabled by default and the documentation claims the opposite.

(4) so where does smo take its default values from?

string sqlServerInstance = "localhost";

string databaseName = "SMO_TEST";

string dataFileLogicalName = databaseName + "_Data";

string logFileLogicalName = databaseName + "_Log";

string dataFilePhysicalName = dataFileLogicalName + ".mdf";

string logFilePhysicalName = logFileLogicalName + ".ldf";

string dataFilePath = "D:\\TEST_DIR\\";

string logFilePath = "D:\\TEST_DIR\\";

if (!Directory.Exists(dataFilePath))

{

Directory.CreateDirectory(dataFilePath);

//throw new DirectoryNotFoundException("The data file directory " + dataFilePath + " does not exists.");

}

if (!Directory.Exists(logFilePath))

{

Directory.CreateDirectory(logFilePath);

//throw new DirectoryNotFoundException("The log file directory " + logFilePath + " does not exists.");

}

string defaultFileGroupName = "PRIMARY";

ServerConnection connection;

connection = new ServerConnection(sqlServerInstance);

Server server = new Server(connection);

try

{

//connection.BeginTransaction();

server.ConnectionContext.BeginTransaction();

if (server.Databases.Contains(databaseName))

{

server.Databases[databaseName].Drop();

server.Alter();

}

Database database = new Database(server, databaseName);

FileGroup fileGroup = new FileGroup(database, defaultFileGroupName);

database.FileGroups.Add(fileGroup);

DataFile dataFile = new DataFile(fileGroup, dataFileLogicalName);

dataFile.FileName = dataFilePath + dataFilePhysicalName;

dataFile.GrowthType = FileGrowthType.KB;

dataFile.Growth = 1024d;

fileGroup.Files.Add(dataFile);

LogFile logFile = new LogFile(database, logFileLogicalName);

logFile.FileName = logFilePath + logFilePhysicalName;

logFile.GrowthType = FileGrowthType.Percent;

logFile.MaxSize = 2147483648d;

database.LogFiles.Add(logFile);

database.Create();

// disable full text indexing

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

//connection.CommitTransaction();

server.ConnectionContext.CommitTransaction();

}

catch (FailedOperationException ex)

{

//connection.RollBackTransaction();

server.ConnectionContext.RollBackTransaction();

Console.WriteLine(ex.Message);

}

finally

{

if (connection != null)

{

if (connection.IsOpen)

{

connection.Disconnect();

}

connection = null;

}

}

thanks again

|||<P align=left><FONT face=Arial size=2>1) Some commands cannot be used within a transaction, but the problem in your case is that the database seems to be in use during the dropping action, see the blog post on my site for more information about that:<BR><BR>2) local one<BR><BR>3) DId not see anything for that, SMO tell that this functionality is deprecated in further versions.</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>4) Which values do you mean ? Some are coded in the SMO library, some are taken from the db system.</P>
<P dir=ltr style="MARGIN-RIGHT: 0px" align=left><BR><BR>Jens K. Suessmeyer<BR><BR><BR><A href="http://www.sqlserver2005.de">http://www.sqlserver2005.de</A><BR></P>|||

Waleed,

You need to add the data and log files before you can assign the filename properties. Try the code here (I've adjusted your code):

try
{
ServerConnection connection = new ServerConnection("localhost");
Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))
{

Database database = new Database(server, "SMO_TEST");

FileGroup fileGroup = new FileGroup(database, "PRIMARY");
database.FileGroups.Add(fileGroup);
DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");
fileGroup.Files.Add(dataFile);
dataFile.FileName = "D:\\SMO_TEST_Data.mdf";
LogFile logFile = new LogFile(database, "SMO_TEST_Log");
database.LogFiles.Add(logFile);
logFile.FileName = "D:\\SMO_TEST_Log.ldf";
database.Create();
}
}
catch (FailedOperationException ex)
{
Console.WriteLine(ex.Message);
}

|||

dear MVPs,

I tried to loop through the inner exception of the failedOperationException and it turned out that the problem is that Database.Create(), Database.Drop() and

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

cannot be contained in a transaction

this answers my question about transactions

thank you very much

changing service account as per kb 283811

Has anybody been able to change a service account following this article:
http://support.microsoft.com/kb/q283811/
In particular,
- SQL2k0 SP4,
- non-administrative, i.e. plain-vanilla User Group, account,
- MSDE SP4 named instance, or
- named instance,
Thank you.
hi bill,
bill tie wrote:
> Has anybody been able to change a service account following this
> article: http://support.microsoft.com/kb/q283811/
> In particular,
> - SQL2k0 SP4,
> - non-administrative, i.e. plain-vanilla User Group, account,
> - MSDE SP4 named instance, or
> - named instance,
> Thank you.
283811 has not been updated for changes in sp4... I'm still trying to figure
it out what is missing...
so far I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

changing schema owner through SMO

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Changing programmatically the datasource of a report

I am having the following problem:
I have a shared data source and a report on the server.
I want to change the data source of the report programmatically but I am
receiving the following error:
â'The required field DataSource is missing from the input structureâ'
I am using the following code:
Dim reference As New ReportServer.DataSourceReference
reference.Reference = "/MyDS"
Dim dss As New ReportServer.DataSource
dss.Item = CType(reference,
ReportServer.DataSourceDefinitionOrReference)
dss.Name = "MyDS"
Dim dsList() As ReportServer.DataSource = New
ReportServer.DataSource(1){}
dsList(1) = dss
dsList(1).Name = "MyDS"
rs.SetReportDataSources("/Test report1", dsList)
The shared datasource and the report are on the root directory and the
report was created with a shared datasource with the name â'MyDSâ'
What could be the problem?where is a value for dsList(0) ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CalinV" <CalinV@.discussions.microsoft.com> wrote in message
news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>I am having the following problem:
> I have a shared data source and a report on the server.
> I want to change the data source of the report programmatically but I am
> receiving the following error:
> "The required field DataSource is missing from the input structure"
> I am using the following code:
> Dim reference As New ReportServer.DataSourceReference
> reference.Reference = "/MyDS"
> Dim dss As New ReportServer.DataSource
> dss.Item = CType(reference,
> ReportServer.DataSourceDefinitionOrReference)
> dss.Name = "MyDS"
> Dim dsList() As ReportServer.DataSource = New
> ReportServer.DataSource(1){}
> dsList(1) = dss
> dsList(1).Name = "MyDS"
> rs.SetReportDataSources("/Test report1", dsList)
> The shared datasource and the report are on the root directory and the
> report was created with a shared datasource with the name "MyDS"
> What could be the problem?
>|||Hi, Dear All,
I have the same problem when I tried to use setDataSource method.
Here is my code:
Dim reference As New DataSourceReference
reference.Reference = "/xxxx/" + DBName
Dim dataSources(1) As DataSource
Dim ds As New DataSource
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = DBName
dataSources(0) = ds
' Render arguments
Dim reportPath As String = "/xxx/yyy" +
Request.Params("NumberOfT").ToString()
Dim format As String = "PDF"
Dim historyID As String = Nothing
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
' Prepare report parameter.
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue
parameters(0).Name = "ReportID"
parameters(0).Value = Request.Params("ReportID").ToString()
parameters(1) = New ParameterValue
parameters(1).Name = "xxx"
parameters(1).Value = Request.Params("xxx").ToString()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.SessionHeaderValue = New SessionHeader
rs.SetReportDataSources(reportPath, dataSources) (error happend here)
Actually, I copied it from MSDN sample. I did not see how the DataSources(1)
could be set. Do I need to set DataSources(1)?
Thanks,
Henry
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> where is a value for dsList(0) ?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>>I am having the following problem:
>> I have a shared data source and a report on the server.
>> I want to change the data source of the report programmatically but I am
>> receiving the following error:
>> "The required field DataSource is missing from the input structure"
>> I am using the following code:
>> Dim reference As New ReportServer.DataSourceReference
>> reference.Reference = "/MyDS"
>> Dim dss As New ReportServer.DataSource
>> dss.Item = CType(reference,
>> ReportServer.DataSourceDefinitionOrReference)
>> dss.Name = "MyDS"
>> Dim dsList() As ReportServer.DataSource = New
>> ReportServer.DataSource(1){}
>> dsList(1) = dss
>> dsList(1).Name = "MyDS"
>> rs.SetReportDataSources("/Test report1", dsList)
>> The shared datasource and the report are on the root directory and the
>> report was created with a shared datasource with the name "MyDS"
>> What could be the problem?
>>
>|||I think it should be
Dim dataSources(0) As DataSource
instead of
Dim dataSources(1) As DataSource
Please let me know if this works for you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry Wang" <wangyh@.gmail.com> wrote in message
news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi, Dear All,
> I have the same problem when I tried to use setDataSource method.
> Here is my code:
> Dim reference As New DataSourceReference
> reference.Reference = "/xxxx/" + DBName
> Dim dataSources(1) As DataSource
> Dim ds As New DataSource
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = DBName
> dataSources(0) = ds
> ' Render arguments
> Dim reportPath As String = "/xxx/yyy" +
> Request.Params("NumberOfT").ToString()
> Dim format As String = "PDF"
> Dim historyID As String = Nothing
> Dim devInfo As String => "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> ' Prepare report parameter.
> Dim parameters(1) As ParameterValue
> parameters(0) = New ParameterValue
> parameters(0).Name = "ReportID"
> parameters(0).Value = Request.Params("ReportID").ToString()
> parameters(1) = New ParameterValue
> parameters(1).Name = "xxx"
> parameters(1).Value = Request.Params("xxx").ToString()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.SessionHeaderValue = New SessionHeader
> rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> Actually, I copied it from MSDN sample. I did not see how the
> DataSources(1) could be set. Do I need to set DataSources(1)?
>
> Thanks,
>
> Henry
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> where is a value for dsList(0) ?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>>I am having the following problem:
>> I have a shared data source and a report on the server.
>> I want to change the data source of the report programmatically but I am
>> receiving the following error:
>> "The required field DataSource is missing from the input structure"
>> I am using the following code:
>> Dim reference As New ReportServer.DataSourceReference
>> reference.Reference = "/MyDS"
>> Dim dss As New ReportServer.DataSource
>> dss.Item = CType(reference,
>> ReportServer.DataSourceDefinitionOrReference)
>> dss.Name = "MyDS"
>> Dim dsList() As ReportServer.DataSource = New
>> ReportServer.DataSource(1){}
>> dsList(1) = dss
>> dsList(1).Name = "MyDS"
>> rs.SetReportDataSources("/Test report1", dsList)
>> The shared datasource and the report are on the root directory and the
>> report was created with a shared datasource with the name "MyDS"
>> What could be the problem?
>>
>>
>|||On kinda same vien, I generate a RDL with a shared datasource. Once
published, it says the datasource is no longer valid. Any advice?
"Lev Semenets [MSFT]" wrote:
> I think it should be
> Dim dataSources(0) As DataSource
> instead of
> Dim dataSources(1) As DataSource
> Please let me know if this works for you.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Henry Wang" <wangyh@.gmail.com> wrote in message
> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> > Hi, Dear All,
> >
> > I have the same problem when I tried to use setDataSource method.
> >
> > Here is my code:
> > Dim reference As New DataSourceReference
> >
> > reference.Reference = "/xxxx/" + DBName
> >
> > Dim dataSources(1) As DataSource
> >
> > Dim ds As New DataSource
> >
> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >
> > ds.Name = DBName
> >
> > dataSources(0) = ds
> >
> > ' Render arguments
> >
> > Dim reportPath As String = "/xxx/yyy" +
> > Request.Params("NumberOfT").ToString()
> >
> > Dim format As String = "PDF"
> >
> > Dim historyID As String = Nothing
> >
> > Dim devInfo As String => > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> >
> > ' Prepare report parameter.
> >
> > Dim parameters(1) As ParameterValue
> >
> > parameters(0) = New ParameterValue
> >
> > parameters(0).Name = "ReportID"
> >
> > parameters(0).Value = Request.Params("ReportID").ToString()
> >
> > parameters(1) = New ParameterValue
> >
> > parameters(1).Name = "xxx"
> >
> > parameters(1).Value = Request.Params("xxx").ToString()
> >
> > Dim rs As New ReportingService
> >
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >
> > rs.SessionHeaderValue = New SessionHeader
> >
> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> >
> > Actually, I copied it from MSDN sample. I did not see how the
> > DataSources(1) could be set. Do I need to set DataSources(1)?
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Henry
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> where is a value for dsList(0) ?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
> >>I am having the following problem:
> >> I have a shared data source and a report on the server.
> >> I want to change the data source of the report programmatically but I am
> >> receiving the following error:
> >> "The required field DataSource is missing from the input structure"
> >> I am using the following code:
> >> Dim reference As New ReportServer.DataSourceReference
> >> reference.Reference = "/MyDS"
> >>
> >> Dim dss As New ReportServer.DataSource
> >>
> >> dss.Item = CType(reference,
> >> ReportServer.DataSourceDefinitionOrReference)
> >> dss.Name = "MyDS"
> >> Dim dsList() As ReportServer.DataSource = New
> >> ReportServer.DataSource(1){}
> >> dsList(1) = dss
> >> dsList(1).Name = "MyDS"
> >> rs.SetReportDataSources("/Test report1", dsList)
> >>
> >> The shared datasource and the report are on the root directory and the
> >> report was created with a shared datasource with the name "MyDS"
> >> What could be the problem?
> >>
> >>
> >>
> >>
> >
> >
>
>|||Could you email me the code?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
> On kinda same vien, I generate a RDL with a shared datasource. Once
> published, it says the datasource is no longer valid. Any advice?
> "Lev Semenets [MSFT]" wrote:
>> I think it should be
>> Dim dataSources(0) As DataSource
>> instead of
>> Dim dataSources(1) As DataSource
>> Please let me know if this works for you.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Henry Wang" <wangyh@.gmail.com> wrote in message
>> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> > Hi, Dear All,
>> >
>> > I have the same problem when I tried to use setDataSource method.
>> >
>> > Here is my code:
>> > Dim reference As New DataSourceReference
>> >
>> > reference.Reference = "/xxxx/" + DBName
>> >
>> > Dim dataSources(1) As DataSource
>> >
>> > Dim ds As New DataSource
>> >
>> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> >
>> > ds.Name = DBName
>> >
>> > dataSources(0) = ds
>> >
>> > ' Render arguments
>> >
>> > Dim reportPath As String = "/xxx/yyy" +
>> > Request.Params("NumberOfT").ToString()
>> >
>> > Dim format As String = "PDF"
>> >
>> > Dim historyID As String = Nothing
>> >
>> > Dim devInfo As String =>> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
>> >
>> > ' Prepare report parameter.
>> >
>> > Dim parameters(1) As ParameterValue
>> >
>> > parameters(0) = New ParameterValue
>> >
>> > parameters(0).Name = "ReportID"
>> >
>> > parameters(0).Value = Request.Params("ReportID").ToString()
>> >
>> > parameters(1) = New ParameterValue
>> >
>> > parameters(1).Name = "xxx"
>> >
>> > parameters(1).Value = Request.Params("xxx").ToString()
>> >
>> > Dim rs As New ReportingService
>> >
>> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> >
>> > rs.SessionHeaderValue = New SessionHeader
>> >
>> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
>> >
>> > Actually, I copied it from MSDN sample. I did not see how the
>> > DataSources(1) could be set. Do I need to set DataSources(1)?
>> >
>> >
>> >
>> > Thanks,
>> >
>> >
>> >
>> > Henry
>> >
>> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
>> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> >> where is a value for dsList(0) ?
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>> >>I am having the following problem:
>> >> I have a shared data source and a report on the server.
>> >> I want to change the data source of the report programmatically but I
>> >> am
>> >> receiving the following error:
>> >> "The required field DataSource is missing from the input structure"
>> >> I am using the following code:
>> >> Dim reference As New ReportServer.DataSourceReference
>> >> reference.Reference = "/MyDS"
>> >>
>> >> Dim dss As New ReportServer.DataSource
>> >>
>> >> dss.Item = CType(reference,
>> >> ReportServer.DataSourceDefinitionOrReference)
>> >> dss.Name = "MyDS"
>> >> Dim dsList() As ReportServer.DataSource = New
>> >> ReportServer.DataSource(1){}
>> >> dsList(1) = dss
>> >> dsList(1).Name = "MyDS"
>> >> rs.SetReportDataSources("/Test report1", dsList)
>> >>
>> >> The shared datasource and the report are on the root directory and
>> >> the
>> >> report was created with a shared datasource with the name "MyDS"
>> >> What could be the problem?
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||I got it, phew.
I tried a couple of things, to make sure it wasn't a bug. First I used a
known working RDL template, which uses a shared datasource. I deleted a node
and put it back in and and published it. I went ahead and set the datasource
to the newly published report. That seems to work, but I still wonder why it
doesn't work as seamless as when publishing from the Designer.
"Lev Semenets [MSFT]" wrote:
> Could you email me the code?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
> > On kinda same vien, I generate a RDL with a shared datasource. Once
> > published, it says the datasource is no longer valid. Any advice?
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> I think it should be
> >>
> >> Dim dataSources(0) As DataSource
> >> instead of
> >> Dim dataSources(1) As DataSource
> >>
> >> Please let me know if this works for you.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Henry Wang" <wangyh@.gmail.com> wrote in message
> >> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> >> > Hi, Dear All,
> >> >
> >> > I have the same problem when I tried to use setDataSource method.
> >> >
> >> > Here is my code:
> >> > Dim reference As New DataSourceReference
> >> >
> >> > reference.Reference = "/xxxx/" + DBName
> >> >
> >> > Dim dataSources(1) As DataSource
> >> >
> >> > Dim ds As New DataSource
> >> >
> >> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >> >
> >> > ds.Name = DBName
> >> >
> >> > dataSources(0) = ds
> >> >
> >> > ' Render arguments
> >> >
> >> > Dim reportPath As String = "/xxx/yyy" +
> >> > Request.Params("NumberOfT").ToString()
> >> >
> >> > Dim format As String = "PDF"
> >> >
> >> > Dim historyID As String = Nothing
> >> >
> >> > Dim devInfo As String => >> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
> >> >
> >> > ' Prepare report parameter.
> >> >
> >> > Dim parameters(1) As ParameterValue
> >> >
> >> > parameters(0) = New ParameterValue
> >> >
> >> > parameters(0).Name = "ReportID"
> >> >
> >> > parameters(0).Value = Request.Params("ReportID").ToString()
> >> >
> >> > parameters(1) = New ParameterValue
> >> >
> >> > parameters(1).Name = "xxx"
> >> >
> >> > parameters(1).Value = Request.Params("xxx").ToString()
> >> >
> >> > Dim rs As New ReportingService
> >> >
> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> >> >
> >> > rs.SessionHeaderValue = New SessionHeader
> >> >
> >> > rs.SetReportDataSources(reportPath, dataSources) (error happend here)
> >> >
> >> > Actually, I copied it from MSDN sample. I did not see how the
> >> > DataSources(1) could be set. Do I need to set DataSources(1)?
> >> >
> >> >
> >> >
> >> > Thanks,
> >> >
> >> >
> >> >
> >> > Henry
> >> >
> >> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> >> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
> >> >> where is a value for dsList(0) ?
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
> >> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
> >> >>I am having the following problem:
> >> >> I have a shared data source and a report on the server.
> >> >> I want to change the data source of the report programmatically but I
> >> >> am
> >> >> receiving the following error:
> >> >> "The required field DataSource is missing from the input structure"
> >> >> I am using the following code:
> >> >> Dim reference As New ReportServer.DataSourceReference
> >> >> reference.Reference = "/MyDS"
> >> >>
> >> >> Dim dss As New ReportServer.DataSource
> >> >>
> >> >> dss.Item = CType(reference,
> >> >> ReportServer.DataSourceDefinitionOrReference)
> >> >> dss.Name = "MyDS"
> >> >> Dim dsList() As ReportServer.DataSource = New
> >> >> ReportServer.DataSource(1){}
> >> >> dsList(1) = dss
> >> >> dsList(1).Name = "MyDS"
> >> >> rs.SetReportDataSources("/Test report1", dsList)
> >> >>
> >> >> The shared datasource and the report are on the root directory and
> >> >> the
> >> >> report was created with a shared datasource with the name "MyDS"
> >> >> What could be the problem?
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||Designer does more than just uploading a report.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:5960DE8A-37A4-4C62-A886-E8DDD3881F78@.microsoft.com...
>I got it, phew.
> I tried a couple of things, to make sure it wasn't a bug. First I used a
> known working RDL template, which uses a shared datasource. I deleted a
> node
> and put it back in and and published it. I went ahead and set the
> datasource
> to the newly published report. That seems to work, but I still wonder why
> it
> doesn't work as seamless as when publishing from the Designer.
> "Lev Semenets [MSFT]" wrote:
>> Could you email me the code?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:AC8BA5F5-2106-4203-9701-FE2AAC528F5F@.microsoft.com...
>> > On kinda same vien, I generate a RDL with a shared datasource. Once
>> > published, it says the datasource is no longer valid. Any advice?
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> I think it should be
>> >>
>> >> Dim dataSources(0) As DataSource
>> >> instead of
>> >> Dim dataSources(1) As DataSource
>> >>
>> >> Please let me know if this works for you.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Henry Wang" <wangyh@.gmail.com> wrote in message
>> >> news:OhB3Wf3uEHA.3200@.TK2MSFTNGP14.phx.gbl...
>> >> > Hi, Dear All,
>> >> >
>> >> > I have the same problem when I tried to use setDataSource method.
>> >> >
>> >> > Here is my code:
>> >> > Dim reference As New DataSourceReference
>> >> >
>> >> > reference.Reference = "/xxxx/" + DBName
>> >> >
>> >> > Dim dataSources(1) As DataSource
>> >> >
>> >> > Dim ds As New DataSource
>> >> >
>> >> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> >> >
>> >> > ds.Name = DBName
>> >> >
>> >> > dataSources(0) = ds
>> >> >
>> >> > ' Render arguments
>> >> >
>> >> > Dim reportPath As String = "/xxx/yyy" +
>> >> > Request.Params("NumberOfT").ToString()
>> >> >
>> >> > Dim format As String = "PDF"
>> >> >
>> >> > Dim historyID As String = Nothing
>> >> >
>> >> > Dim devInfo As String =>> >> > "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
>> >> >
>> >> > ' Prepare report parameter.
>> >> >
>> >> > Dim parameters(1) As ParameterValue
>> >> >
>> >> > parameters(0) = New ParameterValue
>> >> >
>> >> > parameters(0).Name = "ReportID"
>> >> >
>> >> > parameters(0).Value = Request.Params("ReportID").ToString()
>> >> >
>> >> > parameters(1) = New ParameterValue
>> >> >
>> >> > parameters(1).Name = "xxx"
>> >> >
>> >> > parameters(1).Value = Request.Params("xxx").ToString()
>> >> >
>> >> > Dim rs As New ReportingService
>> >> >
>> >> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>> >> >
>> >> > rs.SessionHeaderValue = New SessionHeader
>> >> >
>> >> > rs.SetReportDataSources(reportPath, dataSources) (error happend
>> >> > here)
>> >> >
>> >> > Actually, I copied it from MSDN sample. I did not see how the
>> >> > DataSources(1) could be set. Do I need to set DataSources(1)?
>> >> >
>> >> >
>> >> >
>> >> > Thanks,
>> >> >
>> >> >
>> >> >
>> >> > Henry
>> >> >
>> >> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
>> >> > news:%23f6R1FwkEHA.1652@.TK2MSFTNGP09.phx.gbl...
>> >> >> where is a value for dsList(0) ?
>> >> >>
>> >> >> --
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "CalinV" <CalinV@.discussions.microsoft.com> wrote in message
>> >> >> news:631AD51F-0AD1-4B7A-8C3A-47CE0264A5D5@.microsoft.com...
>> >> >>I am having the following problem:
>> >> >> I have a shared data source and a report on the server.
>> >> >> I want to change the data source of the report programmatically
>> >> >> but I
>> >> >> am
>> >> >> receiving the following error:
>> >> >> "The required field DataSource is missing from the input
>> >> >> structure"
>> >> >> I am using the following code:
>> >> >> Dim reference As New ReportServer.DataSourceReference
>> >> >> reference.Reference = "/MyDS"
>> >> >>
>> >> >> Dim dss As New ReportServer.DataSource
>> >> >>
>> >> >> dss.Item = CType(reference,
>> >> >> ReportServer.DataSourceDefinitionOrReference)
>> >> >> dss.Name = "MyDS"
>> >> >> Dim dsList() As ReportServer.DataSource = New
>> >> >> ReportServer.DataSource(1){}
>> >> >> dsList(1) = dss
>> >> >> dsList(1).Name = "MyDS"
>> >> >> rs.SetReportDataSources("/Test report1", dsList)
>> >> >>
>> >> >> The shared datasource and the report are on the root directory and
>> >> >> the
>> >> >> report was created with a shared datasource with the name "MyDS"
>> >> >> What could be the problem?
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>

changing logical filename

I have a database called test. it has 2 logical files : test_data,
test_log. they are in the following physical files: test_data.mdf,
test_log.ldf.
I make a backup of the database test.
I wish to make a copy of the test database with a different name on teh same
machine. I want to end up with a databse called test_new which has
test_new_data and test_new_log as logical files. It ahsould then have
test_new_data.mdf and test_new_log.ldf for physical files. I want to do
this using a dump fro the test database.
Does anyone know how to do this ? The reason for doing it this way is that
on one of my servers i created the copy of est databse called test_new. On
that test_new databse i had logical files of test_data and test_log. but
the physical files were called test_new_data.mdf and test_new_log.ldf.
since teh logical file names were the same there seems to be a problem in
which in order for me to access the test_new database, i must give access to
the test database as well as the test_new database to an id. otherwise i
get an access violation.
Any insight into this problem or my proprsed solution would be greatly
appreciated.
Aron
Create a new database test_new, then
when you will restore data from backup over this database
either use MOVE optin in RESTORE DATABASE command in QA
or choose Device names and paths on Options tab in EM
How to restore a database with a new name (Transact-SQL)
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
Regards.
"Aron E. Tekulsky" wrote:

> I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is that
> on one of my servers i created the copy of est databse called test_new. On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>
>
|||One way to deal with it is to restore the db into a different directory than
the original. The other is to use the WITH MOVE option of RESTORE to get a
new physical name. You can rename the Logical names with ALTER DATABASE
after you restore it.
Andrew J. Kelly SQL MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uaQkgQiYFHA.2768@.tk2msftngp13.phx.gbl...
>I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh
> same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is
> that
> on one of my servers i created the copy of est databse called test_new.
> On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access
> to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>
|||Thanks for the information. But that does not give the desired result. I
will still have logical file names that are the same as teh original
database. I am looking for a way to have diffrent logical file names as
well.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> Create a new database test_new, then
> when you will restore data from backup over this database
> either use MOVE optin in RESTORE DATABASE command in QA
> or choose Device names and paths on Options tab in EM
> How to restore a database with a new name (Transact-SQL)
>
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp[vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]
|||As Andrew mentioned already you can rename logical name with ALTER DATABASE
after it will restored.
Regards.
"Aron E. Tekulsky" wrote:

> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>
|||Hi,
For changing the Logical file name see the below command:-
ALTER DATABASE <DBNAME> modify file (NAME = 'old_MDF_NAME', NEWNAME =
'NEW_MDF_NAME')
do the same for LDF file as well.
Note:
We can not change the logical file name in SQL 7. The command will only work
in SQL 2000.
Thanks
Hari
SQL Server MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:%23BXP2giYFHA.796@.TK2MSFTNGP10.phx.gbl...
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> http://msdn.microsoft.com/library/de...ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>

changing logical filename

I have a database called test. it has 2 logical files : test_data,
test_log. they are in the following physical files: test_data.mdf,
test_log.ldf.
I make a backup of the database test.
I wish to make a copy of the test database with a different name on teh same
machine. I want to end up with a databse called test_new which has
test_new_data and test_new_log as logical files. It ahsould then have
test_new_data.mdf and test_new_log.ldf for physical files. I want to do
this using a dump fro the test database.
Does anyone know how to do this ? The reason for doing it this way is that
on one of my servers i created the copy of est databse called test_new. On
that test_new databse i had logical files of test_data and test_log. but
the physical files were called test_new_data.mdf and test_new_log.ldf.
since teh logical file names were the same there seems to be a problem in
which in order for me to access the test_new database, i must give access to
the test database as well as the test_new database to an id. otherwise i
get an access violation.
Any insight into this problem or my proprsed solution would be greatly
appreciated.
AronCreate a new database test_new, then
when you will restore data from backup over this database
either use MOVE optin in RESTORE DATABASE command in QA
or choose Device names and paths on Options tab in EM
How to restore a database with a new name (Transact-SQL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
Regards.
"Aron E. Tekulsky" wrote:
> I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is that
> on one of my servers i created the copy of est databse called test_new. On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>
>|||One way to deal with it is to restore the db into a different directory than
the original. The other is to use the WITH MOVE option of RESTORE to get a
new physical name. You can rename the Logical names with ALTER DATABASE
after you restore it.
--
Andrew J. Kelly SQL MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uaQkgQiYFHA.2768@.tk2msftngp13.phx.gbl...
>I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh
> same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is
> that
> on one of my servers i created the copy of est databse called test_new.
> On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access
> to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>|||Thanks for the information. But that does not give the desired result. I
will still have logical file names that are the same as teh original
database. I am looking for a way to have diffrent logical file names as
well.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> Create a new database test_new, then
> when you will restore data from backup over this database
> either use MOVE optin in RESTORE DATABASE command in QA
> or choose Device names and paths on Options tab in EM
> How to restore a database with a new name (Transact-SQL)
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
>
> Regards.
> "Aron E. Tekulsky" wrote:
> > I have a database called test. it has 2 logical files : test_data,
> > test_log. they are in the following physical files: test_data.mdf,
> > test_log.ldf.
> > I make a backup of the database test.
> >
> > I wish to make a copy of the test database with a different name on teh
same
> > machine. I want to end up with a databse called test_new which has
> > test_new_data and test_new_log as logical files. It ahsould then have
> > test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> > this using a dump fro the test database.
> >
> > Does anyone know how to do this ? The reason for doing it this way is
that
> > on one of my servers i created the copy of est databse called test_new.
On
> > that test_new databse i had logical files of test_data and test_log.
but
> > the physical files were called test_new_data.mdf and test_new_log.ldf.
> > since teh logical file names were the same there seems to be a problem
in
> > which in order for me to access the test_new database, i must give
access to
> > the test database as well as the test_new database to an id. otherwise
i
> > get an access violation.
> >
> > Any insight into this problem or my proprsed solution would be greatly
> > appreciated.
> > Aron
> >
> >
> >|||As Andrew mentioned already you can rename logical name with ALTER DATABASE
after it will restored.
Regards.
"Aron E. Tekulsky" wrote:
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> > Create a new database test_new, then
> > when you will restore data from backup over this database
> > either use MOVE optin in RESTORE DATABASE command in QA
> > or choose Device names and paths on Options tab in EM
> >
> > How to restore a database with a new name (Transact-SQL)
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
> >
> >
> > Regards.
> >
> > "Aron E. Tekulsky" wrote:
> >
> > > I have a database called test. it has 2 logical files : test_data,
> > > test_log. they are in the following physical files: test_data.mdf,
> > > test_log.ldf.
> > > I make a backup of the database test.
> > >
> > > I wish to make a copy of the test database with a different name on teh
> same
> > > machine. I want to end up with a databse called test_new which has
> > > test_new_data and test_new_log as logical files. It ahsould then have
> > > test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> > > this using a dump fro the test database.
> > >
> > > Does anyone know how to do this ? The reason for doing it this way is
> that
> > > on one of my servers i created the copy of est databse called test_new.
> On
> > > that test_new databse i had logical files of test_data and test_log.
> but
> > > the physical files were called test_new_data.mdf and test_new_log.ldf.
> > > since teh logical file names were the same there seems to be a problem
> in
> > > which in order for me to access the test_new database, i must give
> access to
> > > the test database as well as the test_new database to an id. otherwise
> i
> > > get an access violation.
> > >
> > > Any insight into this problem or my proprsed solution would be greatly
> > > appreciated.
> > > Aron
> > >
> > >
> > >
>
>|||Hi,
For changing the Logical file name see the below command:-
ALTER DATABASE <DBNAME> modify file (NAME = 'old_MDF_NAME', NEWNAME ='NEW_MDF_NAME')
do the same for LDF file as well.
Note:
We can not change the logical file name in SQL 7. The command will only work
in SQL 2000.
Thanks
Hari
SQL Server MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:%23BXP2giYFHA.796@.TK2MSFTNGP10.phx.gbl...
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
>> Create a new database test_new, then
>> when you will restore data from backup over this database
>> either use MOVE optin in RESTORE DATABASE command in QA
>> or choose Device names and paths on Options tab in EM
>> How to restore a database with a new name (Transact-SQL)
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp
>>
>> Regards.
>> "Aron E. Tekulsky" wrote:
>> > I have a database called test. it has 2 logical files : test_data,
>> > test_log. they are in the following physical files: test_data.mdf,
>> > test_log.ldf.
>> > I make a backup of the database test.
>> >
>> > I wish to make a copy of the test database with a different name on teh
> same
>> > machine. I want to end up with a databse called test_new which has
>> > test_new_data and test_new_log as logical files. It ahsould then have
>> > test_new_data.mdf and test_new_log.ldf for physical files. I want to
>> > do
>> > this using a dump fro the test database.
>> >
>> > Does anyone know how to do this ? The reason for doing it this way is
> that
>> > on one of my servers i created the copy of est databse called
>> > test_new.
> On
>> > that test_new databse i had logical files of test_data and test_log.
> but
>> > the physical files were called test_new_data.mdf and test_new_log.ldf.
>> > since teh logical file names were the same there seems to be a problem
> in
>> > which in order for me to access the test_new database, i must give
> access to
>> > the test database as well as the test_new database to an id. otherwise
> i
>> > get an access violation.
>> >
>> > Any insight into this problem or my proprsed solution would be greatly
>> > appreciated.
>> > Aron
>> >
>> >
>> >
>

changing logical filename

I have a database called test. it has 2 logical files : test_data,
test_log. they are in the following physical files: test_data.mdf,
test_log.ldf.
I make a backup of the database test.
I wish to make a copy of the test database with a different name on teh same
machine. I want to end up with a databse called test_new which has
test_new_data and test_new_log as logical files. It ahsould then have
test_new_data.mdf and test_new_log.ldf for physical files. I want to do
this using a dump fro the test database.
Does anyone know how to do this ? The reason for doing it this way is that
on one of my servers i created the copy of est databse called test_new. On
that test_new databse i had logical files of test_data and test_log. but
the physical files were called test_new_data.mdf and test_new_log.ldf.
since teh logical file names were the same there seems to be a problem in
which in order for me to access the test_new database, i must give access to
the test database as well as the test_new database to an id. otherwise i
get an access violation.
Any insight into this problem or my proprsed solution would be greatly
appreciated.
AronCreate a new database test_new, then
when you will restore data from backup over this database
either use MOVE optin in RESTORE DATABASE command in QA
or choose Device names and paths on Options tab in EM
How to restore a database with a new name (Transact-SQL)
kpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...>
kpc_6ng9.asp
Regards.
"Aron E. Tekulsky" wrote:

> I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh sa
me
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is tha
t
> on one of my servers i created the copy of est databse called test_new. O
n
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access
to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>
>|||One way to deal with it is to restore the db into a different directory than
the original. The other is to use the WITH MOVE option of RESTORE to get a
new physical name. You can rename the Logical names with ALTER DATABASE
after you restore it.
Andrew J. Kelly SQL MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uaQkgQiYFHA.2768@.tk2msftngp13.phx.gbl...
>I have a database called test. it has 2 logical files : test_data,
> test_log. they are in the following physical files: test_data.mdf,
> test_log.ldf.
> I make a backup of the database test.
> I wish to make a copy of the test database with a different name on teh
> same
> machine. I want to end up with a databse called test_new which has
> test_new_data and test_new_log as logical files. It ahsould then have
> test_new_data.mdf and test_new_log.ldf for physical files. I want to do
> this using a dump fro the test database.
> Does anyone know how to do this ? The reason for doing it this way is
> that
> on one of my servers i created the copy of est databse called test_new.
> On
> that test_new databse i had logical files of test_data and test_log. but
> the physical files were called test_new_data.mdf and test_new_log.ldf.
> since teh logical file names were the same there seems to be a problem in
> which in order for me to access the test_new database, i must give access
> to
> the test database as well as the test_new database to an id. otherwise i
> get an access violation.
> Any insight into this problem or my proprsed solution would be greatly
> appreciated.
> Aron
>|||Thanks for the information. But that does not give the desired result. I
will still have logical file names that are the same as teh original
database. I am looking for a way to have diffrent logical file names as
well.
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> Create a new database test_new, then
> when you will restore data from backup over this database
> either use MOVE optin in RESTORE DATABASE command in QA
> or choose Device names and paths on Options tab in EM
> How to restore a database with a new name (Transact-SQL)
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6ng9.asp[/ur
l][vbcol=seagreen]
>
> Regards.
> "Aron E. Tekulsky" wrote:
>
same[vbcol=seagreen]
that[vbcol=seagreen]
On[vbcol=seagreen]
but[vbcol=seagreen]
in[vbcol=seagreen]
access to[vbcol=seagreen]
i[vbcol=seagreen]|||As Andrew mentioned already you can rename logical name with ALTER DATABASE
after it will restored.
Regards.
"Aron E. Tekulsky" wrote:

> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>
>|||Hi,
For changing the Logical file name see the below command:-
ALTER DATABASE <DBNAME> modify file (NAME = 'old_MDF_NAME', NEWNAME =
'NEW_MDF_NAME')
do the same for LDF file as well.
Note:
We can not change the logical file name in SQL 7. The command will only work
in SQL 2000.
Thanks
Hari
SQL Server MVP
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:%23BXP2giYFHA.796@.TK2MSFTNGP10.phx.gbl...
> Thanks for the information. But that does not give the desired result. I
> will still have logical file names that are the same as teh original
> database. I am looking for a way to have diffrent logical file names as
> well.
> "SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
> news:B87ECFED-4075-4B81-87E8-84879F5C883A@.microsoft.com...
> ackpc_6ng9.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpc_6ng9.asp
> same
> that
> On
> but
> in
> access to
> i
>

Changing HOME folder per user

I am wondering how I could do the following -

Right now the default folder is Home, what if I have two users User1 and User2, my folder structure is the following -
Home/User1
Home/User2

I want each user to only have access to their folder. That is easy to do.

Once both users have proper access to their folders they still don't have access to Home folder. In Report Builder when I do 'open file' it defaults to Home folder so I get an error. How can I specify the home folder to be User1 for that user and User2 for the second user? I know I can give them both browsing permissions to Home but it's not very elegant.

There must be a way to programmatically change the folder as soon as proper user logs in. Anyone knows how to do that?

Thanks.
Anybody?

How about this, is there a way to do custom folder access, for example a user who has a proper flag set in a custom DB can only have access to one set of models, another user to another and so on. None of this is in RS DB except for one big organizational user who has access to everything. I want to be able to restrict access on the fly so once the user logs in using custom DB credentials they can only have access to some things and not the others.

Thanks
|||

There's no way to change the home folder programmatically. What you can do is given everyone browser permission on the home folder and then content manager or publisher permission on their home/user1 folder.

Not sure if you're aware of the My Reports feature that essentially lets you not have to manage the creation of User1..N folders your self. Everyone would see a folder called "My Reports".

-Lukasz

|||Thanks for your answer. The solution you proposed is what I'm doing right now.

As to My Reports, I am trying to get away from RS users/roles completely. We have all this defined in a custom DB and it's a real pain for me to make all that security work with RS.

Right now I'm trying to figure out how to get a folder name from CheckAccess so I can see in my DB if I should allow access to this folder from this user. Not sure if that's possible at all. Ideally my RS would only have one user (main RS admin) and the rest of the users/roles/permissions would be taken from a custom DB and inside my authentication extension I would give out permissions depending on that.
|||You can build your custom web that integrates your custom security (DB) with that you will be 100% positive that you are listing / giving the right access to the users.|||

Paul.G. wrote:

You can build your custom web that integrates your custom security (DB) with that you will be 100% positive that you are listing / giving the right access to the users.

I am not even using the web at this point. All the client is given is a link to Report Builder. There is a lot of underlying stuff other than the website that I have to deal with, that's the issue.
|||

RS does not expose the folder/path of the item being accessed to the security extension. We don't really support this scenario very well, but it is possible with enough determination :-).

The key thing is that the security descriptor stored with a given item in RS is entirely in the control of the authorization extension. The authorization extension builds the ACL, sets the group names, etc. You could try using it to store some relation information associated with the item. Then you'd use this information in your checkaccess method implementation to go look up the corresponding values in your own database.

The consequence of this approach is that assigning role based security in the RS UI/tools is likely to result in mismatches with your database. You'll want to write your own code to manage the security rights and not allow users to change it on the RS side.

Hope that helps,

-Lukasz

|||So you are saying that somehow I can relate acl info to something in my database to add more information to every entry while creating a descriptor?

That sounds interesting. Is there some kind of a unique key I can tie this extra info to?

Thanks very much for the idea.

Code Snippet

public byte[] CreateSecurityDescriptor(
AceCollection acl,
SecurityItemType itemType,
out string stringSecDesc)
{
BinaryFormatter bf = new BinaryFormatter();
using (MemoryStream result = new MemoryStream())
{
bf.Serialize(result, acl);
stringSecDesc = null;
return result.GetBuffer();
}
}

|||Why doesn't CreateSecurityDescriptor execute on folder creation? Shouldn't the descriptor be created during that time?

And once again, is there any unique key (I guess it would be in acl) that I can tie custom stuff to (like have it as a uuid in my db which corresponds to whatever extra security I create)?
|||

Not sure of the specific reason, but the default is to inherit from parent.

Unique IDs - you should assigne a unique ID in your own database of items, for example you might use the rowguid. This will make it *much* eaiser to relate items in SRS to your database. It will also mean that the first step in item creation in SRS is to create the item in your own database. Natural consequences are 1) you need to build custom publishing/management interfaces in your application and 2) you'll need to ensure your applications service account has sufficient permission in RS to at least craete all items and 3) you don't want users publishing reports directly against SRS - this would invalidate your security extension model.

Hope that helps,

-Lukasz

Changing datatype from char to datetime

I am trying to run the following query:

ALTER TABLE dnb_profile
ALTER COLUMN [family update date] datetime

and I keep getting the following error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

Can anyone tell me how I can do this successfully??

Thanks,

Connie Sawyer
Foley & Lardner
clsawyer@.foley.comOn 27 Sep 2004 09:15:29 -0700, Connie Sawyer wrote:

>I am trying to run the following query:
>ALTER TABLE dnb_profile
>ALTER COLUMN [family update date] datetime
>and I keep getting the following error:
>Server: Msg 242, Level 16, State 3, Line 1
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value.
>The statement has been terminated.

Hi Connie,

This indicates that at least one value currently in the [family update
date] column is of a format that won't convert to SQL Server properly.
There may be various explanations:

1. Someone managed to enter some gibbledygook in the column - possible,
since it's of the char data type. True rubbish would result in another
error message, but dates like february 30, december 53 or some date in
month number 17 would yield this message.

2. The contents of the column may look like normal dates to you, but not
to SQL Server. The error message you got is quite common if SQL Server
interprets day as month and month as day. Remember that there are manu
different notation styles for dates. The only unambiguous date formats are
yyyymmdd (for date only) or yyyy-mm-ddThh:mm:ss.mmm (for date and time,
where .mmm, denoting the milliseconds, is optional).

In each case, you'll have to inspect your data to find the cause and
either manually fix the offending rows (if there are just a few) or do
some string massaging to change from a misunderstood date format to one of
the standard formats before converting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Connie Sawyer wrote:

> I am trying to run the following query:
> ALTER TABLE dnb_profile
> ALTER COLUMN [family update date] datetime
> and I keep getting the following error:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> The statement has been terminated.
> Can anyone tell me how I can do this successfully??
> Thanks,
> Connie Sawyer
> Foley & Lardner
> clsawyer@.foley.com

What you should do is to run this:

SELECT * FROM dnb_profile
WHERE ISDATE([family update date])=0

This will return you all the records
where value of [family update date] can't be converted to date.
And you should fix those records before altering the column.

Here's the link to the isdate function:

http://msdn.microsoft.com/library/d..._ia-iz_8ov9.asp

WYGL,
Andrey

Changing Database Server Locale Date time settings ?

Hi There

We currently have the following scenario:

4 app servers with regional date and time settings of locale A.

1 database server with locale settings B.

What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.

It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?

In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?

Thanx

As long as date/time values are stored in datetime datatypes, changing the locale/regional settings 'should' not have any impact on the data.

If ServerA uses the form of 'dd/mm/yyyy' and attempts to pass that string value to serverB (and ServerB uses the form of 'mm/dd/yyyy', there is confusion and often failure. Is '06/12/2007' June 12th, or Dec 6th?

However, if you were to make sure that any time values passed to SQL procedures and functions was in the form of 'yyyymmdd' or 'yyyy/mm/dd' (standard ISO format), there would not be a problem for one server to interpret the date from a different server.

Changing Database Owner

When I try to connect to a database from my client/server application
I receive the following error: SQL SERVER ERROR 911 - Server rejected
the connection - access to the selected database has been denied.
When I run sp_helpdb I noticed that the db owner on this database is
different than the owner of the other databases I connect to. How do I
change the owner?
ThanksHi,
Please execute the below system stored procedure
use <dbname> go
sp_changedbowner 'username'
Note:
-- User name is the new owner name
-- dbname is the database name which you need to change the owner
Thanks
Hari
MCDBA
"Jim" <jromano@.phs-us.com> wrote in message
news:e1e1a687.0403261546.7c12e105@.posting.google.com...
> When I try to connect to a database from my client/server application
> I receive the following error: SQL SERVER ERROR 911 - Server rejected
> the connection - access to the selected database has been denied.
> When I run sp_helpdb I noticed that the db owner on this database is
> different than the owner of the other databases I connect to. How do I
> change the owner?
> Thanks