PAGE HEADER: trouble

Showing posts with label trouble. Show all posts

Wednesday, March 28, 2012

Problem with SQL Server And Access 2003

Hi, everyone.
I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
in the past I have not had any trouble, but today seems to be a
different day.
Every time I go to try and open a dao.database object I get the error
"Run-Time error '3000'': Reserved error(-1038); there is no message
for this error."
I am not really sure what the issue is here. Below is the line of code
Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
gSQLServConnectionString)
Just so everything is clear gSQLServConnectionString =
ODBC;DSN=SQLServer;Trusted_Connection=ye
s;DATABASE=DicomSQLServer;
Now, I have tried a simlar connection string and it works just fine for
another server. When I test the connection in the ODBC manager it
connects to the server with a problem, but when I try to get access to
do it. I get the aforementioned error.
Thanks for all your help .
-ThanasYou may get an better response from:
microsoft.public.access.adp.sqlserver
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> Hi, everyone.
> I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> in the past I have not had any trouble, but today seems to be a
> different day.
> Every time I go to try and open a dao.database object I get the error
> "Run-Time error '3000'': Reserved error(-1038); there is no message
> for this error."
> I am not really sure what the issue is here. Below is the line of code
> Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> gSQLServConnectionString)
> Just so everything is clear gSQLServConnectionString =
> ODBC;DSN=SQLServer;Trusted_Connection=ye
s;DATABASE=DicomSQLServer;
> Now, I have tried a simlar connection string and it works just fine for
> another server. When I test the connection in the ODBC manager it
> connects to the server with a problem, but when I try to get access to
> do it. I get the aforementioned error.
> Thanks for all your help .
> -Thanas
>|||Thanks I'll give it a try.
Arnie Rowland wrote:[vbcol=seagreen]
> You may get an better response from:
> microsoft.public.access.adp.sqlserver
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Thanas" <atsioplakis@.gmail.com> wrote in message
> news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...

Problem with SQL Server And Access 2003

Hi, everyone.
I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
in the past I have not had any trouble, but today seems to be a
different day.
Every time I go to try and open a dao.database object I get the error
"Run-Time error '3000'': Reserved error(-1038); there is no message
for this error."
I am not really sure what the issue is here. Below is the line of code
Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
gSQLServConnectionString)
Just so everything is clear gSQLServConnectionString = ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
Now, I have tried a simlar connection string and it works just fine for
another server. When I test the connection in the ODBC manager it
connects to the server with a problem, but when I try to get access to
do it. I get the aforementioned error.
Thanks for all your help .
-ThanasYou may get an better response from:
microsoft.public.access.adp.sqlserver
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> Hi, everyone.
> I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> in the past I have not had any trouble, but today seems to be a
> different day.
> Every time I go to try and open a dao.database object I get the error
> "Run-Time error '3000'': Reserved error(-1038); there is no message
> for this error."
> I am not really sure what the issue is here. Below is the line of code
> Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> gSQLServConnectionString)
> Just so everything is clear gSQLServConnectionString => ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
> Now, I have tried a simlar connection string and it works just fine for
> another server. When I test the connection in the ODBC manager it
> connects to the server with a problem, but when I try to get access to
> do it. I get the aforementioned error.
> Thanks for all your help .
> -Thanas
>|||Thanks I'll give it a try.
Arnie Rowland wrote:
> You may get an better response from:
> microsoft.public.access.adp.sqlserver
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Thanas" <atsioplakis@.gmail.com> wrote in message
> news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> > Hi, everyone.
> > I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> > in the past I have not had any trouble, but today seems to be a
> > different day.
> >
> > Every time I go to try and open a dao.database object I get the error
> > "Run-Time error '3000'': Reserved error(-1038); there is no message
> > for this error."
> >
> > I am not really sure what the issue is here. Below is the line of code
> >
> > Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> > gSQLServConnectionString)
> >
> > Just so everything is clear gSQLServConnectionString => > ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
> >
> > Now, I have tried a simlar connection string and it works just fine for
> > another server. When I test the connection in the ODBC manager it
> > connects to the server with a problem, but when I try to get access to
> > do it. I get the aforementioned error.
> >
> > Thanks for all your help .
> >
> > -Thanas
> >

Monday, March 26, 2012

Problem with SQL Backup

Hello,
I having trouble restoring a full followed by a differential in SQL 2000.
I have a full file and one differential I am trying to restore.
I am using the full file to restore the full and then when I try to apply
the differential, I get an error saying Cannot apply the backup
My commands are
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
What I get is an error Msg3136 that says
Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
The question I have is why is this happening, and 2 how can I fix it. I was
able to get the full to restore properly, but not the diff.
Any help is appreciated.
--
Thanks,
DavidDavid
If these files are at the same device you need to specify a file option. If
they aren't ,make sure that you restore correct files.
"david" <david@.discussions.microsoft.com> wrote in message
news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with
> norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database
> dbname.
> The question I have is why is this happening, and 2 how can I fix it. I
> was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||Can you be a little more specific? An example will help. The data files are
on the same physical directory. how does this make a difference or is it the
way SQL needs to see it?
--
Thanks,
David
"Uri Dimant" wrote:
> David
> If these files are at the same device you need to specify a file option. If
> they aren't ,make sure that you restore correct files.
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
> > Hello,
> >
> > I having trouble restoring a full followed by a differential in SQL 2000.
> >
> > I have a full file and one differential I am trying to restore.
> >
> > I am using the full file to restore the full and then when I try to apply
> > the differential, I get an error saying Cannot apply the backup
> >
> > My commands are
> >
> >
> >
> > USE MASTER
> > RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> > norecovery,
> > move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> > move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> > RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with
> > norecovery
> >
> > What I get is an error Msg3136 that says
> >
> > Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database
> > dbname.
> >
> > The question I have is why is this happening, and 2 how can I fix it. I
> > was
> > able to get the full to restore properly, but not the diff.
> >
> > Any help is appreciated.
> > --
> > Thanks,
> >
> > David
>
>|||Additionally, what specifies the file number? Is this something I need to
specify when I backup the file as well? Is there any way to verify what the
file contains similar to getting the logical database name out of the file?
Also, do I need to specify move for each file I restore because of the
logical name?
I am assuming I need to.
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||Does anyone have input for me' Any help is appreciated.
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||My guess is that you did something like below:
(a) BACKUP DATABASE
(b) BACKUP DATABASE
(c) BACKUP DATABASE WITH DIFFERENTIAL
RESTORE (a)
RESTORE (c)
In order to restore (c), you need to:
RESTORE (b)
RESTORE (c)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
> Does anyone have input for me' Any help is appreciated.
> --
> Thanks,
> David
>
> "david" wrote:
>> Hello,
>> I having trouble restoring a full followed by a differential in SQL 2000.
>> I have a full file and one differential I am trying to restore.
>> I am using the full file to restore the full and then when I try to apply
>> the differential, I get an error saying Cannot apply the backup
>> My commands are
>>
>> USE MASTER
>> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> norecovery,
>> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> What I get is an error Msg3136 that says
>> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> The question I have is why is this happening, and 2 how can I fix it. I was
>> able to get the full to restore properly, but not the diff.
>> Any help is appreciated.
>> --
>> Thanks,
>> David|||Tibor:
There is a nightly full backup taken at 9:30 PM of the SQL database. We will
call this BACKUP (A)
Each morning, beginning at 8:00 AM, I am backing up the same database with
differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
If I have a failure, I want to take the full from the previous night (BACKUP
A)
and apply the appropriate differential (Backup 10AM) to bring the state of
the database to that point. There is no log backup.
I am copying these flat files to a file folder on another computer.
Through SQLQuery I am running the following restore script
This will allow me to move the full database to the new server and a new
location.
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
Then I try to restore the differential with the following command:
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
Lastly, I should be able to add the following line to bring the database to
the final usable state.
RESTORE database dbname with recovery.
What is happening is I am getting the error as follows
What I get is an error Msg3136 that says
Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
Prior people have stated I need to use the FILE flag, but I don't really
understand how to use it properly and I have asked for an example.
I am wondering if the path specified in the restore procedure
(F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
directory?
I am assuming yes, but I am unsure how SQL handles the media.
Can you help?
Thanks,
David
"Tibor Karaszi" wrote:
> My guess is that you did something like below:
> (a) BACKUP DATABASE
> (b) BACKUP DATABASE
> (c) BACKUP DATABASE WITH DIFFERENTIAL
> RESTORE (a)
> RESTORE (c)
> In order to restore (c), you need to:
> RESTORE (b)
> RESTORE (c)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
> > Does anyone have input for me' Any help is appreciated.
> > --
> > Thanks,
> >
> > David
> >
> >
> > "david" wrote:
> >
> >> Hello,
> >>
> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >>
> >> I have a full file and one differential I am trying to restore.
> >>
> >> I am using the full file to restore the full and then when I try to apply
> >> the differential, I get an error saying Cannot apply the backup
> >>
> >> My commands are
> >>
> >>
> >>
> >> USE MASTER
> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> norecovery,
> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >>
> >> What I get is an error Msg3136 that says
> >>
> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >>
> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> able to get the full to restore properly, but not the diff.
> >>
> >> Any help is appreciated.
> >> --
> >> Thanks,
> >>
> >> David
>
>|||david wrote:
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>
If you have no transaction logs, why are you specifying norecovery? Try using recovery.|||If you are absolutely certain that you do restore the most recent db backup before the diff backup
(check against the backup history table in msdb, for instance), and you still get the error and you
don't find anything on KB i suspect a bug in SQL Server and you should open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:9ED285B6-9549-49A5-BC0C-C01F15B2E4E6@.microsoft.com...
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> Lastly, I should be able to add the following line to bring the database to
> the final usable state.
> RESTORE database dbname with recovery.
> What is happening is I am getting the error as follows
> What I get is an error Msg3136 that says
> Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
> Prior people have stated I need to use the FILE flag, but I don't really
> understand how to use it properly and I have asked for an example.
> I am wondering if the path specified in the restore procedure
> (F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
> directory?
> I am assuming yes, but I am unsure how SQL handles the media.
> Can you help?
>
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
>> My guess is that you did something like below:
>> (a) BACKUP DATABASE
>> (b) BACKUP DATABASE
>> (c) BACKUP DATABASE WITH DIFFERENTIAL
>> RESTORE (a)
>> RESTORE (c)
>> In order to restore (c), you need to:
>> RESTORE (b)
>> RESTORE (c)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
>> > Does anyone have input for me' Any help is appreciated.
>> > --
>> > Thanks,
>> >
>> > David
>> >
>> >
>> > "david" wrote:
>> >
>> >> Hello,
>> >>
>> >> I having trouble restoring a full followed by a differential in SQL 2000.
>> >>
>> >> I have a full file and one differential I am trying to restore.
>> >>
>> >> I am using the full file to restore the full and then when I try to apply
>> >> the differential, I get an error saying Cannot apply the backup
>> >>
>> >> My commands are
>> >>
>> >>
>> >>
>> >> USE MASTER
>> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> >> norecovery,
>> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> >>
>> >> What I get is an error Msg3136 that says
>> >>
>> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> >>
>> >> The question I have is why is this happening, and 2 how can I fix it. I was
>> >> able to get the full to restore properly, but not the diff.
>> >>
>> >> Any help is appreciated.
>> >> --
>> >> Thanks,
>> >>
>> >> David
>>|||I have checked the files, but I am still not sure how to solve this issue. I
am using the data from the last full and the differential that I apply is the
one say for 2PM.
Why am I getting the error? And how do I correct it? Any help is appreciated?
Anyone?
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||The error message states that you did a database backup after the db backup you restore and before
the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
case with MS Support for them to have a look at (unless you find something in KB that applies). See
my scripts for how to reproduce the error message:
--Work
CREATE DATABASE test
CREATE TABLE test..t(c1 int identity)
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
--Doesn't work
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>I have checked the files, but I am still not sure how to solve this issue. I
> am using the data from the last full and the differential that I apply is the
> one say for 2PM.
> Why am I getting the error? And how do I correct it? Any help is appreciated?
> Anyone?
> --
> Thanks,
> David
>
> "david" wrote:
>> Hello,
>> I having trouble restoring a full followed by a differential in SQL 2000.
>> I have a full file and one differential I am trying to restore.
>> I am using the full file to restore the full and then when I try to apply
>> the differential, I get an error saying Cannot apply the backup
>> My commands are
>>
>> USE MASTER
>> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> norecovery,
>> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> What I get is an error Msg3136 that says
>> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> The question I have is why is this happening, and 2 how can I fix it. I was
>> able to get the full to restore properly, but not the diff.
>> Any help is appreciated.
>> --
>> Thanks,
>> David|||I understand. I have made sure the database files are OK. I am seeing the
same results. Can you give me additional information on how SQL applies its
backups? Are there specific criteria it is expecting when applying a
differential?
--
Thanks,
David
"Tibor Karaszi" wrote:
> The error message states that you did a database backup after the db backup you restore and before
> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
> case with MS Support for them to have a look at (unless you find something in KB that applies). See
> my scripts for how to reproduce the error message:
> --Work
> CREATE DATABASE test
> CREATE TABLE test..t(c1 int identity)
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> --Doesn't work
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >I have checked the files, but I am still not sure how to solve this issue. I
> > am using the data from the last full and the differential that I apply is the
> > one say for 2PM.
> >
> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> > Anyone?
> > --
> > Thanks,
> >
> > David
> >
> >
> > "david" wrote:
> >
> >> Hello,
> >>
> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >>
> >> I have a full file and one differential I am trying to restore.
> >>
> >> I am using the full file to restore the full and then when I try to apply
> >> the differential, I get an error saying Cannot apply the backup
> >>
> >> My commands are
> >>
> >>
> >>
> >> USE MASTER
> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> norecovery,
> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >>
> >> What I get is an error Msg3136 that says
> >>
> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >>
> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> able to get the full to restore properly, but not the diff.
> >>
> >> Any help is appreciated.
> >> --
> >> Thanks,
> >>
> >> David
>|||I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
between the db backup and the diff backup you try to restore?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
>I understand. I have made sure the database files are OK. I am seeing the
> same results. Can you give me additional information on how SQL applies its
> backups? Are there specific criteria it is expecting when applying a
> differential?
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
>> The error message states that you did a database backup after the db backup you restore and
>> before
>> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
>> open a
>> case with MS Support for them to have a look at (unless you find something in KB that applies).
>> See
>> my scripts for how to reproduce the error message:
>> --Work
>> CREATE DATABASE test
>> CREATE TABLE test..t(c1 int identity)
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>> --Doesn't work
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>> >I have checked the files, but I am still not sure how to solve this issue. I
>> > am using the data from the last full and the differential that I apply is the
>> > one say for 2PM.
>> >
>> > Why am I getting the error? And how do I correct it? Any help is appreciated?
>> > Anyone?
>> > --
>> > Thanks,
>> >
>> > David
>> >
>> >
>> > "david" wrote:
>> >
>> >> Hello,
>> >>
>> >> I having trouble restoring a full followed by a differential in SQL 2000.
>> >>
>> >> I have a full file and one differential I am trying to restore.
>> >>
>> >> I am using the full file to restore the full and then when I try to apply
>> >> the differential, I get an error saying Cannot apply the backup
>> >>
>> >> My commands are
>> >>
>> >>
>> >>
>> >> USE MASTER
>> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> >> norecovery,
>> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> >>
>> >> What I get is an error Msg3136 that says
>> >>
>> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> >>
>> >> The question I have is why is this happening, and 2 how can I fix it. I was
>> >> able to get the full to restore properly, but not the diff.
>> >>
>> >> Any help is appreciated.
>> >> --
>> >> Thanks,
>> >>
>> >> David
>>|||Good morning.
Had an interesting day yesterday that resulted in a better understanding of
the problem. I did a RESTORE HEADERONLY command on the two backup files and
noticed the date on the backup was two days earlier. I re-ran a full backup
and an another differential and was surprised the restore was successful.
I ensured the backup creation occured at the right time and now I have a
full backup that runs at 9PM each evening.
But I still have questions. This morning I took the full backup dated
11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
(latest). When I tried to restore again, I got the 3136 error again. A big
disappointment.
So my questions are:
1. Can someone explain how the LSN markers are used in SQL headeronly data?
and does the differential need to match one of the lsn to function properly
on the restore.
Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
Any help as always is appreciated.
--
Thanks,
David
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> between the db backup and the diff backup you try to restore?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> >I understand. I have made sure the database files are OK. I am seeing the
> > same results. Can you give me additional information on how SQL applies its
> > backups? Are there specific criteria it is expecting when applying a
> > differential?
> >
> > --
> > Thanks,
> >
> > David
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The error message states that you did a database backup after the db backup you restore and
> >> before
> >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> >> open a
> >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> >> See
> >> my scripts for how to reproduce the error message:
> >>
> >> --Work
> >> CREATE DATABASE test
> >> CREATE TABLE test..t(c1 int identity)
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >> --Doesn't work
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >>
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >> >I have checked the files, but I am still not sure how to solve this issue. I
> >> > am using the data from the last full and the differential that I apply is the
> >> > one say for 2PM.
> >> >
> >> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> >> > Anyone?
> >> > --
> >> > Thanks,
> >> >
> >> > David
> >> >
> >> >
> >> > "david" wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >> >>
> >> >> I have a full file and one differential I am trying to restore.
> >> >>
> >> >> I am using the full file to restore the full and then when I try to apply
> >> >> the differential, I get an error saying Cannot apply the backup
> >> >>
> >> >> My commands are
> >> >>
> >> >>
> >> >>
> >> >> USE MASTER
> >> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> >> norecovery,
> >> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >> >>
> >> >> What I get is an error Msg3136 that says
> >> >>
> >> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >> >>
> >> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> >> able to get the full to restore properly, but not the diff.
> >> >>
> >> >> Any help is appreciated.
> >> >> --
> >> >> Thanks,
> >> >>
> >> >> David
> >>
> >>
>|||Well, I learned some interesting things out of this process. Thanks Tibor for
the assistance with this. I did indeed seem to have a SQL backup in between.
After some diligent investigation, I realized there was an additional backup
occuring in a backup job (Veritas) that was a minute after my last full. I
removed the SQL Agent job and am relying solely on the flat file backup. I
was able to sucessfully restore both databases this morning using the full
and desired differential. I am going to continually test the process this
week to make sure I can restore a number of times to ensure the problem is
really solved.
I would like to understand the LSN numbers and how SQL uses these numbers in
the HEADER INFORMATION.
Can anyone elaborate?
--
Thanks,
David
"david" wrote:
> Good morning.
> Had an interesting day yesterday that resulted in a better understanding of
> the problem. I did a RESTORE HEADERONLY command on the two backup files and
> noticed the date on the backup was two days earlier. I re-ran a full backup
> and an another differential and was surprised the restore was successful.
> I ensured the backup creation occured at the right time and now I have a
> full backup that runs at 9PM each evening.
> But I still have questions. This morning I took the full backup dated
> 11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
> (latest). When I tried to restore again, I got the 3136 error again. A big
> disappointment.
> So my questions are:
> 1. Can someone explain how the LSN markers are used in SQL headeronly data?
> and does the differential need to match one of the lsn to function properly
> on the restore.
> Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
>
> Any help as always is appreciated.
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
> > I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> > between the db backup and the diff backup you try to restore?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> > >I understand. I have made sure the database files are OK. I am seeing the
> > > same results. Can you give me additional information on how SQL applies its
> > > backups? Are there specific criteria it is expecting when applying a
> > > differential?
> > >
> > > --
> > > Thanks,
> > >
> > > David
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> The error message states that you did a database backup after the db backup you restore and
> > >> before
> > >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> > >> open a
> > >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> > >> See
> > >> my scripts for how to reproduce the error message:
> > >>
> > >> --Work
> > >> CREATE DATABASE test
> > >> CREATE TABLE test..t(c1 int identity)
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >> --Doesn't work
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >>
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> > >> >I have checked the files, but I am still not sure how to solve this issue. I
> > >> > am using the data from the last full and the differential that I apply is the
> > >> > one say for 2PM.
> > >> >
> > >> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> > >> > Anyone?
> > >> > --
> > >> > Thanks,
> > >> >
> > >> > David
> > >> >
> > >> >
> > >> > "david" wrote:
> > >> >
> > >> >> Hello,
> > >> >>
> > >> >> I having trouble restoring a full followed by a differential in SQL 2000.
> > >> >>
> > >> >> I have a full file and one differential I am trying to restore.
> > >> >>
> > >> >> I am using the full file to restore the full and then when I try to apply
> > >> >> the differential, I get an error saying Cannot apply the backup
> > >> >>
> > >> >> My commands are
> > >> >>
> > >> >>
> > >> >>
> > >> >> USE MASTER
> > >> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> > >> >> norecovery,
> > >> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> > >> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> > >> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> > >> >>
> > >> >> What I get is an error Msg3136 that says
> > >> >>
> > >> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> > >> >>
> > >> >> The question I have is why is this happening, and 2 how can I fix it. I was
> > >> >> able to get the full to restore properly, but not the diff.
> > >> >>
> > >> >> Any help is appreciated.
> > >> >> --
> > >> >> Thanks,
> > >> >>
> > >> >> David
> > >>
> > >>
> >
> >

Wednesday, March 21, 2012

Problem with Service Broker and DBMail

Hi!
I try to use Service Broker and DBMail together, but have some trouble with that.
I need to create the queue with activation.
And the stored procedure activated on this queue must send e-mail using DBmail.
It's looks simple, but it doesn't work.
There is my script to create objects, but don't forget create dbmail profile before use it.
PS And replace my email by yours

Activated procedures are under the EXECUTE AS context, so they cannot dirrectly invoke procedures in another database (e.g. msdb DBmail procs). The issue is covered on this serios of articles:
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The easiest woraround is to mark the database where activation occurs as trustworthy:

ALTER DATABASE [<dbname>] SET TRUSTWORTHY ON;

HTH,
~ Remus

Tuesday, March 20, 2012

Problem with searching Chinese in ASP

Hi,
Currently I am having trouble searching Traditional Chinese but not
English and numbers. My host's OS is in English and the asp site is
coded in Big5. It seems to me that some how all the Chinese within the
.txt file are @.#$!@.#$%# and not Chinese. Maybe that's the reason the
search script didn't recognize it.
Here's the test page:
www.owco.ca/searchtest.asp
You can copy and paste only the chinese text into the text field, and
no result will be returned. If you copy and paste the English text,
search result will be returned.
Thank you,
-DavidIn ASP you need to set session.codepage= the code page you are using for
Chinese - it is likely to be Session.CodePage = 950
For ASP.Net you need to configure the culture settings. Please check out
this link for an example.
http://www.indexserverfaq.com/susankongrulez.zip
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<demidnight@.gmail.com> wrote in message
news:1135670597.170199.240460@.g47g2000cwa.googlegroups.com...
> Hi,
> Currently I am having trouble searching Traditional Chinese but not
> English and numbers. My host's OS is in English and the asp site is
> coded in Big5. It seems to me that some how all the Chinese within the
> .txt file are @.#$!@.#$%# and not Chinese. Maybe that's the reason the
> search script didn't recognize it.
> Here's the test page:
> www.owco.ca/searchtest.asp
> You can copy and paste only the chinese text into the text field, and
> no result will be returned. If you copy and paste the English text,
> search result will be returned.
> Thank you,
> -David
>|||Hi, Thank you for your reply.
I am on ASP 3.0
And the code page is set to:
<%
Response.Charset = "big5"
session.codepage = 950
%>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
And still won't work.

Monday, March 12, 2012

Problem with role-based security

Hi everyone

I have searched the forums for a solution, but i cannot find one that i can see apllies
thus I post.

I am having trouble restricting the access of one of my roles.
Here is the structure of my cube:

Cube: 'Sales'
Dimension : 'Product'
Hierarchy: 'Code - Company'
Member: '123'

I simply need to restrict the user to this member. I have set up the
security in Dimesion data on : Cube>>Dimension and Dimension...
I only selected
[Product].[Code - Company].&[123]
but when viewing a report using this user it doesn't seem to take effect,.
{All the company codes can still be seen in the company code parameter drop down}

When I try to implement this on the cell data tab, I can't see anything in the dropdown
when running the report in the browser

{Using 'Enable read permissions', 'Enable read
contingent Permissions' or a combonation of both}

I'm stuck as can be! Please help to shed some light on
this for me.

I thank you in advance

Gerhard Davids
(PS: If I have been unclear in anyway point it out to me plz
and I shall rephrase it)

Do you have any other roles that the user is a member of that would provide them access to the other company codes? Roles in AS2005 are cumulative, so if a user has access to a set of dimension members via one role but they are restricted to a subset of the dimension members via another role, they will still be able to see all of the dimension members...

HTH,

Dave Fackler

|||

Hi Dave

Thanks for the response.

No, th user is only part of one role so he cannot be overiden by
another. The user is also not part of the Domain, I dont know if that changes anything.
The user is only added on the local-mashine fo the reporting services.

Cube security work when I use the test cube security via the link in
the cell data tab.

|||

Two things to check:

1. Run Profiler for AS and make sure that the connection open from RS indeed authenticates as this user

2. Check whether this user is member of Administrators NT group on the machine - if so security doesn't apply to him by default

3. Check response from DISCOVER_CATALOGS while connected as this user and see the content of ROLES column. If it has * in it - it is a bad sign.

|||

Hi Mosha

Thank you for the responce.

I ran the profiler(first time using profiler..) and connected to my ssas
then connected to the reports server and opened the report etc...

The stack trace that was produced did show the correct user authenticated,
The user is also not part of the NT Admin group.

The trace did not show a DISCOVER_CATALOGS and no roles
column was present, however other DISCOVER_ were shown.
Did I do something incorect for this data to be missing?

The only strange thing that i saw was upon clicking the drop down
for the parameter the MDX querry to populate, the 'querry end' event's
error column contained a '1'.

Gerhard

Note: I am using SSAS 2005

|||Anyone have any ideas as to what may be causing this?|||

Hi Gerhard,

Are you using separate Analysis and Reporting servers? If so, the Report server doesn't connect to SSAS using the ID of the report end-user, unless you're using Kerberos - see this past post in the SQL Server OLAP newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/ad755b009d23f2e2

microsoft.public.sqlserver.olap > AS + RS

...

Sounds like the classic NT 2-hop authentication problem.
NT credentials can only be passed between two machines (i.e. client and then
RS server). If you attempt to transfer them again from the RS server to the
AS server, then you get a blank username (actually an error, depending on
the OS and its settings). This is a well-known limitation of NT -- it is
totally unrelated to RS or AS. If you really need to do this then you have a
few choices:
1) run RS and AS on the same machine
2) implement kerberos
You could also switch to saved connections on the RS machine, but that would
defeat the dynamic security that you have already established.
--
Dave Wickert [MSFT]
dwick...@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)

Problem with Returning a recordset from a stored procedure in ADO/VBScript

I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.

Here is my code:
<%
.....
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()

if not logRS.EOF then
.....
%>

My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.

Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.

Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.

CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter

Assume your parameter is an INT named @.my_param

cmm.Parameters.Append cmm.CreateParameter("@.my_param",3,1, 4,client_id)

[Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumnz_2.asp ]

Hope this helps...|||Ahh. Thank you so much. It was the NOCOUNT property.

Wednesday, March 7, 2012

problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.

Wednesday, March 28, 2012

Problem with SQL Server And Access 2003

Problem with SQL Server And Access 2003

Monday, March 26, 2012

Problem with SQL Backup

Problem with SQL Backup

Problem with SQL Backup

Wednesday, March 21, 2012

Problem with Service Broker and DBMail

Tuesday, March 20, 2012

Problem with searching Chinese in ASP

Monday, March 12, 2012

Problem with role-based security

Problem with Returning a recordset from a stored procedure in ADO/VBScript

Wednesday, March 7, 2012

problem with query where column name is the same as a keyword

PAGE HEADER

Blog Archive

About Me