check sql licence: error

Showing posts with label error. Show all posts

Tuesday, March 27, 2012

clear sql error

i am using sql 2000 and calling a sp from vb.net code.
I want when a error occur in sp i want to do some processing in sp and do
not want to throw that error to front end. how can i achieve that as after
checking @.@.error and doing processing, still error gets thrown to front endHave a look at
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||Hi
http://www.sommarskog.se/error-handling-II.html
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||You can configure this in your web.config file
regards
Thakkudu
Vikram wrote:
> i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front end[/color
]|||:)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4JapKTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
> Hi
> http://www.sommarskog.se/error-handling-II.html
>
>
> "Vikram" <aa@.aa> wrote in message
> news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||There's a setting in web.config that automagically adds error-handling to
stored procedures? :)
ML
http://milambda.blogspot.com/|||Also note that when you get a chance to upgrade to SQL Server 2005 this gets
much better because SQL Server 2005 introduces TRY...CATCH for Transact-SQL,
and also the ability to retrieve the error information (not just the error
number in @.@.ERROR) in Transact-SQL.
http://msdn2.microsoft.com/en-us/library/ms179465(en-US,SQL.90).aspx
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||> There's a setting in web.config that automagically adds error-handling to
> stored procedures? :)
yep:
[BestPractices]
SafeProgramming=True

Clear error in for each loop container

Hi,

I have a "Data Flow Task" inside" For Each container". Data flow task is processing file and updating the DB.

If one of the file is correpted i want to move to error folder and continue with the next file. i have given red arrow to a script Task which move the file to error folder. but its not continuing with next file. how can Ido that?

Any help

Set the Max Error count to 0 for the container. 0 = unlimited.|||thanks crispin.. its works.

Sunday, March 25, 2012

clean server cache

Hi,
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaksHi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while you
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>|||It's very rare, first time for us.
.net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> > Hi
> >
> > I am not sure how this could happen, if you restored the database nothing
> > should have been connected, and subsequent connections should have
> > connected
> > ok, providing permissions were in place. Does your .NET application have
> > connections to other databases? Did you check nothing was connected while
> > you
> > did the restore?
> >
> > John
> >
> > "mecn" wrote:
> >
> >> Hi,
> >> I am testing .net app. I restored sql server databases with overwrite the
> >> original database.
> >> I re-launch the .net app, I got this error says. Could not connect to the
> >> database DBID = 15...
> >>
> >> After I reboot the sql server, I was able to connect again,.
> >>
> >> My question is that how do I fix (clean the cache) without rebooting the
> >> server.
> >>
> >> thnaks
> >>
> >>
> >>
>
>

Clean Install Windows Auth Error

Hi,

I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.

I have not change anything since installation of this server.

This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.

Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.

True, this is something of Vista. http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

Sorry for not reading it first.

Thursday, March 22, 2012

Clause where in [Select... ?

Hi,
I would like that instrucion bellow bring me all solicitations which
quotation is in the select clause where in.
It gives me error at this part in [Select Cotacao from Cotacoes Where
Pedido = P1]
Thanks,
Vilmar
Select * From
Solicitations
Where
Sc_Cotacao in [Select Cotacao from Cotacoes Where Pedido = P1]
Solicitations
Code CodeQuotation
S1 C1
S2 C1
S3 C1
S4 C2
S5 C2
S6 C2
S7 C3
S8 C3
S9 C3
Quotation
Code CodeRequest
C1 P1
C2 P1
C3 P1
Pedidos
P1
P2
P3
««««««««»»»»»»»»»»»»»»
Vlmar Brazão de Oliveira
Desenvolvimento Web
HI-TECSounds like P1 should be a string literal
Select * From
> Solicitations
> Where
> Sc_Cotacao in [Select Cotacao from Cotacoes Where Pedido = 'P1']
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:OAXMBnxwDHA.1912@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like that instrucion bellow bring me all solicitations which
> quotation is in the select clause where in.
> It gives me error at this part in [Select Cotacao from Cotacoes Where
> Pedido = P1]
> Thanks,
> Vilmar
> Select * From
> Solicitations
> Where
> Sc_Cotacao in [Select Cotacao from Cotacoes Where Pedido = P1]
>
> Solicitations
> Code CodeQuotation
> S1 C1
> S2 C1
> S3 C1
> S4 C2
> S5 C2
> S6 C2
> S7 C3
> S8 C3
> S9 C3
> Quotation
> Code CodeRequest
> C1 P1
> C2 P1
> C3 P1
> Pedidos
> P1
> P2
> P3
> ««««««««»»»»»»»»»»»»»»
> Vlmar Brazão de Oliveira
> Desenvolvimento Web
> HI-TEC
>|||You will get better responses if you post the actual error message.
Assuming that your query is the one posted, the select statement used with
IN should be bounded with parentheses, not brackets
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:OAXMBnxwDHA.1912@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like that instrucion bellow bring me all solicitations which
> quotation is in the select clause where in.
> It gives me error at this part in [Select Cotacao from Cotacoes Where
> Pedido = P1]
> Thanks,
> Vilmar
> Select * From
> Solicitations
> Where
> Sc_Cotacao in [Select Cotacao from Cotacoes Where Pedido = P1]
>
> Solicitations
> Code CodeQuotation
> S1 C1
> S2 C1
> S3 C1
> S4 C2
> S5 C2
> S6 C2
> S7 C3
> S8 C3
> S9 C3
> Quotation
> Code CodeRequest
> C1 P1
> C2 P1
> C3 P1
> Pedidos
> P1
> P2
> P3
> ««««««««»»»»»»»»»»»»»»
> Vlmar Brazão de Oliveira
> Desenvolvimento Web
> HI-TEC
>|||Thank you everybody!
It was switch square brackets by parentheses.
Regards,
Vilmar
Brazil
"Scott Morris" <bogus@.bogus.com> escreveu na mensagem
news:uzakf7xwDHA.2520@.TK2MSFTNGP10.phx.gbl...
> You will get better responses if you post the actual error message.
> Assuming that your query is the one posted, the select statement used with
> IN should be bounded with parentheses, not brackets
> "Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
> news:OAXMBnxwDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > I would like that instrucion bellow bring me all solicitations which
> > quotation is in the select clause where in.
> > It gives me error at this part in [Select Cotacao from Cotacoes Where
> > Pedido = P1]
> > Thanks,
> > Vilmar
> >
> > Select * From
> > Solicitations
> > Where
> > Sc_Cotacao in [Select Cotacao from Cotacoes Where Pedido = P1]
> >
> >
> > Solicitations
> > Code CodeQuotation
> > S1 C1
> > S2 C1
> > S3 C1
> > S4 C2
> > S5 C2
> > S6 C2
> > S7 C3
> > S8 C3
> > S9 C3
> >
> > Quotation
> > Code CodeRequest
> > C1 P1
> > C2 P1
> > C3 P1
> >
> > Pedidos
> > P1
> > P2
> > P3
> >
> > ««««««««»»»»»»»»»»»»»»
> > Vlmar Brazão de Oliveira
> > Desenvolvimento Web
> > HI-TEC
> >
> >
>

Classpath

Hi everybody

I have been trying to compile a servlet. When i try
import javax.servlet.*

I get the compilation error " javax.servlet does not exist"

I have tomcat installed. I understand that i have to make some change in the classpath. I have servlet-api.jar and also jsp-api.jar. But i don't have classpath variable in my environment variables.

Any suggestiions!!!!!!!!!!!!!

thanksYou are totally in a wrong place vmiharia ... We only talk databases here ...

Originally posted by vmiharia
Hi everybody

I have been trying to compile a servlet. When i try
import javax.servlet.*

I get the compilation error " javax.servlet does not exist"

I have tomcat installed. I understand that i have to make some change in the classpath. I have servlet-api.jar and also jsp-api.jar. But i don't have classpath variable in my environment variables.

Any suggestiions!!!!!!!!!!!!!

thankssql sql

ClassNotFound

Dear all
I'm new on Java. Now I got a error in my program is
java.lang.ClassNotFoundException and the class is :
com.microsoft.jdbc.sqlserver.SQLServerDriver .
My classpath already contain that 3 JAR files
(msbase.jar,msutil.jar,mssqlserver.jar) and my servlet already included the
javax.sql.* and javax.naming.*. So now I don't know what i'm missing now.
anyone here can help me to solve this problem ? please give me some idea...
Thanks a lot !!
Ivan
This is definetly a classpath issue. Esle Open all jar and see if you can find the class you are looking for
|||Thx Neo,
I have found the problem now, the classpath I have been setting up before.
Actually the problem was I need to copy those JDBC JAR files to lib\
directory then it will be fine.
"neo" <anonymous@.discussions.microsoft.com> bl
news:538F8F46-32AA-4F42-A4EA-FC83131F384E@.microsoft.com g...
> This is definetly a classpath issue. Esle Open all jar and see if you can
find the class you are looking for

Classic error and question how do restore?

Hi all,
Well, Im here to make a classic question (I guess).
I did droped a table today, there was no backup, exists any possibility to
recover the table?
Before to come here I make a research about this and find few options.
1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
2 use some commands to recover the database and log to a new database at a
certain point of transaction log.
I tried first download the Log Explorer and it restrict me to open only an
example database.
So i look on google and find a lot of command.. all confuse.
So I pressed F1 and read about the RECOVER command and tried this:
I made an backup of my database
and executed the following string in Query Analizer
RESTORE DATABASE NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH RECOVERY, STOPAT = '21/12/2004'
GO
the restore occours successful, but the table is empty
after this I tried to restore the old database with the LumigentDemoDB name
to use the software, Nice, it is listed but it says tha there is no log for
the database.
And now? what to do?
If you think that u can help me, please, I beg to you.
Reguards,
Luiz
Hi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/de...ackpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Luiz
It sounds from your description that you made the backup of the database
AFTER you dropped the table, since you said you had no backup at the time
the error occurred. If that is true, you backed up a copy of the database
with the table already missing, so there is no way that restoring that
database will bring anything back. You must have a backup made BEFORE you
dropped the table.
The Lumigent product might have been able to help before you did the backup
and restore. However, the sample that you downloaded was just a sample. If
you want a product that will save your skin, you really should not expect to
get it for free.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Kalen,
Sorry, well, sorry everybody, my english is pretty bad.
Yes, I do not have any backup before the drop command
Looking all night long I discovered that I must make the transaction log
backup, so, I did it, trying to restore it dont let me to restore before
the date of
backup.
Opening the transaction log file (.ldf) in notepad I saw the data that has
been lost. So I still believe that I can recover it, but, how?
Now i'm going to try something, change back the date of computer, make the
backup and try to restore it.
Any other light?
Thank you Kalen and John
[]'s
Luiz
"Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Luiz
> It sounds from your description that you made the backup of the database
> AFTER you dropped the table, since you said you had no backup at the time
> the error occurred. If that is true, you backed up a copy of the database
> with the table already missing, so there is no way that restoring that
> database will bring anything back. You must have a backup made BEFORE you
> dropped the table.
> The Lumigent product might have been able to help before you did the
> backup and restore. However, the sample that you downloaded was just a
> sample. If you want a product that will save your skin, you really should
> not expect to get it for free.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>
|||Hi
You don't say how often the data changes in this table. If it is reasonably
static, you could restore the last full backup (before the problem) to a new
database and then just copy the table back into your live database.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
> Kalen,
> Sorry, well, sorry everybody, my english is pretty bad.
> Yes, I do not have any backup before the drop command
> Looking all night long I discovered that I must make the transaction log
> backup, so, I did it, trying to restore it dont let me to restore before
> the date of
> backup.
> Opening the transaction log file (.ldf) in notepad I saw the data that has
> been lost. So I still believe that I can recover it, but, how?
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
> Thank you Kalen and John
> []'s
> Luiz
>
> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||John,
Every day about 600 rows is inserted on this table, only insert, no delete.
I have no one backup before this happen (damn why?!)
I'll be more especific. I was trying to add a column in this table creating
a new temporary table,
inserting all the data, droping the old and renaming the new.
The error occour inserting all data in the new table.
The way that i commented (set back the date of computer) don't work
restoring the log it says that the STOPAT parameter is wrong (using the MMC
console)
Thank u one more time
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
> Hi
> You don't say how often the data changes in this table. If it is
> reasonably static, you could restore the last full backup (before the
> problem) to a new database and then just copy the table back into your
> live database.
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>
|||>
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
>
Seems good to me, but when you restore full data base backup use with
NORECOVERY option, this will give you the chance to restore log, but this
time with RECOVERY option
Regards,
Daniel
|||Hi
It is not a good idea to do ad-hoc SQL on a production system, it may cause
unnecessary locking and excessive resource usage, performing ad-hoc DDL may
cause problems like yours. Keeping your code in a source code control system
will enable you to audit and test changes. It will also allow you to
re-create any version of your database from scratch.
You should also implement a structured backup and maintainance process.
I assume that you have lost your temporary table?
You should not need to change the date of the computer to do the recovery.
Have you tried an earlier time to see if you get some data back?
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
> John,
> Every day about 600 rows is inserted on this table, only insert, no
> delete. I have no one backup before this happen (damn why?!)
> I'll be more especific. I was trying to add a column in this table
> creating a new temporary table,
> inserting all the data, droping the old and renaming the new.
> The error occour inserting all data in the new table.
> The way that i commented (set back the date of computer) don't work
> restoring the log it says that the STOPAT parameter is wrong (using the
> MMC console)
> Thank u one more time
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>
|||Hi John,
are you still in this case?
Well, yes, i lost the temp table (wel, both tables). My script has executed
something like this:
EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
DROP TABLE Original_Table /*Here is the shit*/
bla bla bla
..
..
..
DROP TABLE Temp_Table /*Shit was not complete without this*/
I tried to recover with a date before, but sql says that the date is less
than the minimun date.
I guess i think all possibilities.
Thankyou,
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to do ad-hoc SQL on a production system, it may
> cause unnecessary locking and excessive resource usage, performing ad-hoc
> DDL may cause problems like yours. Keeping your code in a source code
> control system will enable you to audit and test changes. It will also
> allow you to re-create any version of your database from scratch.
> You should also implement a structured backup and maintainance process.
> I assume that you have lost your temporary table?
> You should not need to change the date of the computer to do the recovery.
> Have you tried an earlier time to see if you get some data back?
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Luiz
I think the best thing you can do is call Microsoft PSS
http://support.microsoft.com/default.aspx, they will charge you for the
incident, but if it is recoverable they will be able to get you back up and
running quickly.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23MmR1hA7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> are you still in this case?
> Well, yes, i lost the temp table (wel, both tables). My script has
> executed something like this:
> EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
> DROP TABLE Original_Table /*Here is the shit*/
> bla bla bla
> .
> .
> .
> DROP TABLE Temp_Table /*Shit was not complete without this*/
>
> I tried to recover with a date before, but sql says that the date is less
> than the minimun date.
> I guess i think all possibilities.
> Thankyou,
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
>

Classic error and question how do restore?

Hi all,
Well, I´m here to make a classic question (I guess).
I did droped a table today, there was no backup, exists any possibility to
recover the table?
Before to come here I make a research about this and find few options.
1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
2 use some commands to recover the database and log to a new database at a
certain point of transaction log.
I tried first download the Log Explorer and it restrict me to open only an
example database.
So i look on google and find a lot of command.. all confuse.
So I pressed F1 and read about the RECOVER command and tried this:
I made an backup of my database
and executed the following string in Query Analizer
RESTORE DATABASE NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH RECOVERY, STOPAT = '21/12/2004'
GO
the restore occours successful, but the table is empty
after this I tried to restore the old database with the LumigentDemoDB name
to use the software, Nice, it is listed but it says tha there is no log for
the database.
And now? what to do?
If you think that u can help me, please, I beg to you.
Reguards,
LuizHi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>|||Luiz
It sounds from your description that you made the backup of the database
AFTER you dropped the table, since you said you had no backup at the time
the error occurred. If that is true, you backed up a copy of the database
with the table already missing, so there is no way that restoring that
database will bring anything back. You must have a backup made BEFORE you
dropped the table.
The Lumigent product might have been able to help before you did the backup
and restore. However, the sample that you downloaded was just a sample. If
you want a product that will save your skin, you really should not expect to
get it for free.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>|||Kalen,
Sorry, well, sorry everybody, my english is pretty bad.
Yes, I do not have any backup before the drop command
Looking all night long I discovered that I must make the transaction log
backup, so, I did it, trying to restore it don´t let me to restore before
the date of
backup.
Opening the transaction log file (.ldf) in notepad I saw the data that has
been lost. So I still believe that I can recover it, but, how?
Now i'm going to try something, change back the date of computer, make the
backup and try to restore it.
Any other light?
Thank you Kalen and John
[]'s
Luiz
"Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Luiz
> It sounds from your description that you made the backup of the database
> AFTER you dropped the table, since you said you had no backup at the time
> the error occurred. If that is true, you backed up a copy of the database
> with the table already missing, so there is no way that restoring that
> database will bring anything back. You must have a backup made BEFORE you
> dropped the table.
> The Lumigent product might have been able to help before you did the
> backup and restore. However, the sample that you downloaded was just a
> sample. If you want a product that will save your skin, you really should
> not expect to get it for free.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database at
>> a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is no
>> log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>|||Hi
You don't say how often the data changes in this table. If it is reasonably
static, you could restore the last full backup (before the problem) to a new
database and then just copy the table back into your live database.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
> Kalen,
> Sorry, well, sorry everybody, my english is pretty bad.
> Yes, I do not have any backup before the drop command
> Looking all night long I discovered that I must make the transaction log
> backup, so, I did it, trying to restore it don´t let me to restore before
> the date of
> backup.
> Opening the transaction log file (.ldf) in notepad I saw the data that has
> been lost. So I still believe that I can recover it, but, how?
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
> Thank you Kalen and John
> []'s
> Luiz
>
> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the database
>> AFTER you dropped the table, since you said you had no backup at the time
>> the error occurred. If that is true, you backed up a copy of the database
>> with the table already missing, so there is no way that restoring that
>> database will bring anything back. You must have a backup made BEFORE you
>> dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really should
>> not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database at
>> a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is no
>> log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>|||John,
Every day about 600 rows is inserted on this table, only insert, no delete.
I have no one backup before this happen (damn why?!)
I'll be more especific. I was trying to add a column in this table creating
a new temporary table,
inserting all the data, droping the old and renaming the new.
The error occour inserting all data in the new table.
The way that i commented (set back the date of computer) don't work
restoring the log it says that the STOPAT parameter is wrong (using the MMC
console)
Thank u one more time
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
> Hi
> You don't say how often the data changes in this table. If it is
> reasonably static, you could restore the last full backup (before the
> problem) to a new database and then just copy the table back into your
> live database.
> John
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction log
>> backup, so, I did it, trying to restore it don´t let me to restore before
>> the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the database
>> AFTER you dropped the table, since you said you had no backup at the
>> time the error occurred. If that is true, you backed up a copy of the
>> database with the table already missing, so there is no way that
>> restoring that database will bring anything back. You must have a backup
>> made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is
>> no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>|||>
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
>
Seems good to me, but when you restore full data base backup use with
NORECOVERY option, this will give you the chance to restore log, but this
time with RECOVERY option
Regards,
Daniel|||Hi
It is not a good idea to do ad-hoc SQL on a production system, it may cause
unnecessary locking and excessive resource usage, performing ad-hoc DDL may
cause problems like yours. Keeping your code in a source code control system
will enable you to audit and test changes. It will also allow you to
re-create any version of your database from scratch.
You should also implement a structured backup and maintainance process.
I assume that you have lost your temporary table?
You should not need to change the date of the computer to do the recovery.
Have you tried an earlier time to see if you get some data back?
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
> John,
> Every day about 600 rows is inserted on this table, only insert, no
> delete. I have no one backup before this happen (damn why?!)
> I'll be more especific. I was trying to add a column in this table
> creating a new temporary table,
> inserting all the data, droping the old and renaming the new.
> The error occour inserting all data in the new table.
> The way that i commented (set back the date of computer) don't work
> restoring the log it says that the STOPAT parameter is wrong (using the
> MMC console)
> Thank u one more time
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction log
>> backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no backup
>> at the time the error occurred. If that is true, you backed up a copy
>> of the database with the table already missing, so there is no way that
>> restoring that database will bring anything back. You must have a
>> backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any
>> possibility to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open
>> only an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is
>> no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>>
>|||Hi John,
are you still in this case?
Well, yes, i lost the temp table (wel, both tables). My script has executed
something like this:
EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
DROP TABLE Original_Table /*Here is the shit*/
bla bla bla
.
.
.
DROP TABLE Temp_Table /*Shit was not complete without this*/
I tried to recover with a date before, but sql says that the date is less
than the minimun date.
I guess i think all possibilities.
Thankyou,
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to do ad-hoc SQL on a production system, it may
> cause unnecessary locking and excessive resource usage, performing ad-hoc
> DDL may cause problems like yours. Keeping your code in a source code
> control system will enable you to audit and test changes. It will also
> allow you to re-create any version of your database from scratch.
> You should also implement a structured backup and maintainance process.
> I assume that you have lost your temporary table?
> You should not need to change the date of the computer to do the recovery.
> Have you tried an earlier time to see if you get some data back?
> John
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>> John,
>> Every day about 600 rows is inserted on this table, only insert, no
>> delete. I have no one backup before this happen (damn why?!)
>> I'll be more especific. I was trying to add a column in this table
>> creating a new temporary table,
>> inserting all the data, droping the old and renaming the new.
>> The error occour inserting all data in the new table.
>> The way that i commented (set back the date of computer) don't work
>> restoring the log it says that the STOPAT parameter is wrong (using the
>> MMC console)
>> Thank u one more time
>> Luiz
>> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
>> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction
>> log backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no backup
>> at the time the error occurred. If that is true, you backed up a copy
>> of the database with the table already missing, so there is no way
>> that restoring that database will bring anything back. You must have a
>> backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any
>> possibility to recover the table?
>> Before to come here I make a research about this and find few
>> options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open
>> only an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the
>> LumigentDemoDB name to use the software, Nice, it is listed but it
>> says tha there is no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>>
>>
>|||Hi Luiz
I think the best thing you can do is call Microsoft PSS
http://support.microsoft.com/default.aspx, they will charge you for the
incident, but if it is recoverable they will be able to get you back up and
running quickly.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23MmR1hA7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> are you still in this case?
> Well, yes, i lost the temp table (wel, both tables). My script has
> executed something like this:
> EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
> DROP TABLE Original_Table /*Here is the shit*/
> bla bla bla
> .
> .
> .
> DROP TABLE Temp_Table /*Shit was not complete without this*/
>
> I tried to recover with a date before, but sql says that the date is less
> than the minimun date.
> I guess i think all possibilities.
> Thankyou,
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> It is not a good idea to do ad-hoc SQL on a production system, it may
>> cause unnecessary locking and excessive resource usage, performing ad-hoc
>> DDL may cause problems like yours. Keeping your code in a source code
>> control system will enable you to audit and test changes. It will also
>> allow you to re-create any version of your database from scratch.
>> You should also implement a structured backup and maintainance process.
>> I assume that you have lost your temporary table?
>> You should not need to change the date of the computer to do the
>> recovery.
>> Have you tried an earlier time to see if you get some data back?
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>> John,
>> Every day about 600 rows is inserted on this table, only insert, no
>> delete. I have no one backup before this happen (damn why?!)
>> I'll be more especific. I was trying to add a column in this table
>> creating a new temporary table,
>> inserting all the data, droping the old and renaming the new.
>> The error occour inserting all data in the new table.
>> The way that i commented (set back the date of computer) don't work
>> restoring the log it says that the STOPAT parameter is wrong (using the
>> MMC console)
>> Thank u one more time
>> Luiz
>> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
>> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction
>> log backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no
>> backup at the time the error occurred. If that is true, you backed up
>> a copy of the database with the table already missing, so there is no
>> way that restoring that database will bring anything back. You must
>> have a backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just
>> a sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>>> Hi all,
>>>
>>> Well, I´m here to make a classic question (I guess).
>>> I did droped a table today, there was no backup, exists any
>>> possibility to recover the table?
>>>
>>> Before to come here I make a research about this and find few
>>> options.
>>> 1 use the "Log Explorer" from Lumigent to "rollback" the
>>> transaction.
>>> 2 use some commands to recover the database and log to a new
>>> database at a certain point of transaction log.
>>>
>>> I tried first download the Log Explorer and it restrict me to open
>>> only an example database.
>>> So i look on google and find a lot of command.. all confuse.
>>>
>>> So I pressed F1 and read about the RECOVER command and tried this:
>>> I made an backup of my database
>>> and executed the following string in Query Analizer
>>>
>>> RESTORE DATABASE NewDB
>>> FROM disk='c:\Program Files\Microsoft SQL
>>> Server\MSSQL\Backup\BrasValor.bak'
>>> WITH NORECOVERY, REPLACE
>>> GO
>>> RESTORE LOG NewDB
>>> FROM disk='c:\Program Files\Microsoft SQL
>>> Server\MSSQL\Backup\BrasValor.bak'
>>> WITH RECOVERY, STOPAT = '21/12/2004'
>>> GO
>>>
>>> the restore occours successful, but the table is empty
>>> after this I tried to restore the old database with the
>>> LumigentDemoDB name to use the software, Nice, it is listed but it
>>> says tha there is no log for the database.
>>>
>>> And now? what to do?
>>>
>>> If you think that u can help me, please, I beg to you.
>>>
>>> Reguards,
>>> Luiz
>>>
>>
>>
>>
>>
>>
>

Class not registered error while using Stored procedure

Hi,

I had registered a COM DLL (CogUdf32.dll) as Assembly under the Adventure Works DW.

When i tried executing a method from it through MDX query:

SELECT

{ FILTER([Customer].[Customer].AllMembers, CogUdf32.CogInStr([Customer].[Customer].CurrentMember.Name,"USA") > 0) }

ON AXIS(0)

FROM [Adventure Works];

I am getting following error:

The following system error occurred: Class not registered .

Any pointers are welcome.

Thanks and Regards,
Santosh.

COM UDF's are turned off by default due to the security concerns.

It is better practice and it is safer to write your UDF's in .NET language and compile them as assemblies. But if you still need your COM UDF go to the "SQL Surface Area Configuration" tool avaliable through the start menu shortcut and enable COM UDF for Analysis Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

MY UDF's are written in .NET language. I am able to add them as COM DLL under Assemblies. But when i try to use the functions, i am getting these errors:

MDX:
SELECT
{ FILTER([Measures].[Sales Amount], CogUdf32.CogInStr([Measures].[Sales Amount],"*", 0) > 0) }
ON AXIS(0)
FROM [Adventure Works]

Error: The following system error occurred: Class not registered .

MDX:
SELECT
{ FILTER([Measures].[Sales Amount], CogUdf32.CCogRExp.CogInStr([Measures].[Sales Amount],"*", 0) > 0) }
ON AXIS(0)
FROM [Adventure Works]

Error: Query (2, 37) The '[CogUdf32].[CCogRExp].[CogInStr]' function does not exist.

Is there any method to find the registered classes/functions for the Assemblies added in Analysis Service.

Class not registered error

When I try to run DTS Package in SQL 2000, it reports a error - Class not registered?

What is the solution to this problem?

You might try the DTS forum - http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

This forum is for SSIS.

Class does not support aggregation

In Microsoft SQL Server Management Studio 2005 whenever I open a table I get
the error
--
Class does not support aggregation (or class object is remote) (Exception
from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
(Microsoft.SqlServer.SqlTools.VSIntegration)
I previously had SQL Server 2000 installed, but the upgrade worked
successfully.
Could someone please advise?
Many thanks
Richard.Hello Richard,
This is a COM interop related issue:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/html/3b
414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
You may want to remove Workstation componenents, then do a repair on Net
Framework 2.0 then Re-Install workstation components to test:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178936&SiteID=1
If the issue persists, please make sure you have removed all SQL 2000
components including SQL client tool to test the situation again.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Class does not support aggregation
>thread-index: AcYdzJqnv7Gvd6SmT+qRbL0nvajMoA==>X-WBNR-Posting-Host: 194.131.103.210
>From: "=?Utf-8?B?UmljaA==?=" <richvista@.nospam.nospam>
>Subject: Class does not support aggregation
>Date: Fri, 20 Jan 2006 06:20:03 -0800
>Lines: 13
>Message-ID: <801EF9EE-69E9-4B04-902C-E7BC6C98D685@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418324
>X-Tomcat-NG: microsoft.public.sqlserver.server
>In Microsoft SQL Server Management Studio 2005 whenever I open a table I
get
>the error
>--
>Class does not support aggregation (or class object is remote) (Exception
>from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
>(Microsoft.SqlServer.SqlTools.VSIntegration)
>I previously had SQL Server 2000 installed, but the upgrade worked
>successfully.
>Could someone please advise?
>Many thanks
>Richard.
>|||Thank you Peter, I did as you suggested and it now works ok.
"Peter Yang [MSFT]" wrote:
> Hello Richard,
> This is a COM interop related issue:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/html/3b
> 414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
> You may want to remove Workstation componenents, then do a repair on Net
> Framework 2.0 then Re-Install workstation components to test:
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178936&SiteID=1
> If the issue persists, please make sure you have removed all SQL 2000
> components including SQL client tool to test the situation again.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Class does not support aggregation
> >thread-index: AcYdzJqnv7Gvd6SmT+qRbL0nvajMoA==> >X-WBNR-Posting-Host: 194.131.103.210
> >From: "=?Utf-8?B?UmljaA==?=" <richvista@.nospam.nospam>
> >Subject: Class does not support aggregation
> >Date: Fri, 20 Jan 2006 06:20:03 -0800
> >Lines: 13
> >Message-ID: <801EF9EE-69E9-4B04-902C-E7BC6C98D685@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418324
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >In Microsoft SQL Server Management Studio 2005 whenever I open a table I
> get
> >the error
> >--
> >
> >Class does not support aggregation (or class object is remote) (Exception
> >from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
> >(Microsoft.SqlServer.SqlTools.VSIntegration)
> >
> >I previously had SQL Server 2000 installed, but the upgrade worked
> >successfully.
> >Could someone please advise?
> >Many thanks
> >Richard.
> >
>sql sql

Tuesday, March 20, 2012

Circular Reference?

I get this error when I ran the below statement what did I do wrong?

"The definition of MonthRange set contains a circular reference"

WITH SET [MonthRange] AS

{

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

}

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([MonthRange], [Industry].&[Subprime])

[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.

What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )

Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?

(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)

For example,

given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102

and

given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104

Try this version:

WITH

SET [MonthRange] AS

LastPeriods(3,

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as

Aggregate([MonthRange])

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],

[Industry].&[Subprime])

|||

Hi Deepark,

This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.

Is it because of some SCOPING issue in the calculation?

--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.

WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])

SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO

Calculations:

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */

CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;

|||

Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

by the way, the code that works look like this:

--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.

--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?

WITH SET [MonthRange] AS

LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

SELECT {

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1

FROM [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

--this one also works, so if I want another rolling period, I just replace the lag #

WITH SET [MonthRange] AS

{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

select

{

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1

from [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

Thursday, March 8, 2012

Child packages: Execute them all out of process?

HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?

Thank you,
Ccote

ccote wrote:

HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
Thank you,
Ccote

I don't think there is a any best practice guidance around this. Personally I tend to think if you need to execute them out of process, then do so. otherwise, in proc is fine. I can't think of another rationale for one or the other.

-Jamie

|||

Out of process is slower, but gives you a new process (Obviously!) and this allows a new set of memory. For 32-bit this can be benefical as you get another 2Gb (/3Gb), just for the out of proc package execution host, rather than sharing the memory of the parent. So if you have a high memory requirement and the machine has enough memory to support the two processes taking their own share, then out of proc makes sense, but at the cost of speed.

So what you see is expected. It takes time to setup a new process and allocate it all that memory.

Chewing on RAISERROR(N'[Text in brackets]ouch!', 11, 1)

Is this supposed to happen' (MSSQL2K Service pack 3/3a)
/*--
RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
--*/
[Presto!][Ho ho!]A minor error occurred.
Server: Msg 50000, Level 11, State 1, Line 2
A major error occurred.> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
IMHO, no. I would expect the same message text from both statements. The
behavior you observed occurs on my SP4 SQL Server too.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...
> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
> /*--
> RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
> RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
> --*/
> [Presto!][Ho ho!]A minor error occurred.
> Server: Msg 50000, Level 11, State 1, Line 2
> A major error occurred.
>|||I discussed this with some of the other MVPs and it looks like the culprit
is the Query Analyzer 'parse ODBC message prefixes' option. It looks like
it's too aggressive in removing the ODBC noise from messages.
You'll get the expected results if you turn off the option under
Tools-->Options-->Connections. However, the messages will also be prefixed
with the '[Microsoft][ODBC SQL Server Driver][SQL Server]' stuff.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...
> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
> /*--
> RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
> RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
> --*/
> [Presto!][Ho ho!]A minor error occurred.
> Server: Msg 50000, Level 11, State 1, Line 2
> A major error occurred.
>|||So long as the missing text isn't piling up somewhere and threatening a
huge problem some time in the future somehow :-)
Thanks to all you MVPs for efforts. So it's Query Analyzer itself
doing it... okay. I noticed it when I did something like,
SET @.workstring =
REPLACE(
N'RAISERROR(''@.{tbl} had some kind of error.'', 16,
1)
, N'@.{tbl}', QUOTENAME(@.tablename))
EXEc sp_executesql @.workstring
(Of course QUOTENAME('TableName') is '[TableName]'.)
Incidentally Google Groups (through which I'm sending this) in its v2
beta version, has a similar issue with article headers - and one group
that I use, alt.fan.pratchett, likes to self-classify articles as [R]
(relevant, on-topic), [I] (off-topic), various others (meta-topical).
These go - even if protected by "Re: ", apparently - but some
experiments with ".[I]", etc, make 'em stay. The issue there isn't
just the Google user's experience but the view that other users get
when a Google user participates.
It seems reasonable that leading text, even a space, will continue to
save my leading parenthetical text here, too.
(And I'll be surprised if Google Groups is held on Microsoft SQL
servers, but I guess why not?)
Does this Query Analyzer issue rate making some kind of official bug
report, I wonder? If telling you doesn't already count... I'd rather
work around it than spend money. I was only being cute with quoted
table names anyway - I don't need to quote my names, but better safe
than sorry and it also helps them to stand out. But in this case, not
;-)
Maybe Microsoft knew already; I find it difficult to pick keywords for
a Knowledge Base search on this issue.
Dan Guzman wrote:
> I discussed this with some of the other MVPs and it looks like the culprit
> is the Query Analyzer 'parse ODBC message prefixes' option. It looks like
> it's too aggressive in removing the ODBC noise from messages.
> You'll get the expected results if you turn off the option under
> Tools-->Options-->Connections. However, the messages will also be prefixe
d
> with the '[Microsoft][ODBC SQL Server Driver][SQL Server]' stuff.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <rja.carnegie@.excite.com> wrote in message
> news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...|||> Does this Query Analyzer issue rate making some kind of official bug
> report, I wonder? If telling you doesn't already count... I'd rather
> work around it than spend money.
I agree with your triage assessment. I'd rather see the devs spend their
time tidying up SQL 2005. In any case, I believe the QA behavior should be
documented in a KB article. BTW, it's a non-issue with SQL 2005 since Query
Analyzer is superseded by SQL Server Management Studio.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126700741.448066.200040@.o13g2000cwo.googlegroups.com...
> So long as the missing text isn't piling up somewhere and threatening a
> huge problem some time in the future somehow :-)
> Thanks to all you MVPs for efforts. So it's Query Analyzer itself
> doing it... okay. I noticed it when I did something like,
> SET @.workstring =
> REPLACE(
> N'RAISERROR(''@.{tbl} had some kind of error.'', 16,
> 1)
> , N'@.{tbl}', QUOTENAME(@.tablename))
> EXEc sp_executesql @.workstring
> (Of course QUOTENAME('TableName') is '[TableName]'.)
> Incidentally Google Groups (through which I'm sending this) in its v2
> beta version, has a similar issue with article headers - and one group
> that I use, alt.fan.pratchett, likes to self-classify articles as [R]
> (relevant, on-topic), [I] (off-topic), various others (meta-topical).
> These go - even if protected by "Re: ", apparently - but some
> experiments with ".[I]", etc, make 'em stay. The issue there isn't
> just the Google user's experience but the view that other users get
> when a Google user participates.
> It seems reasonable that leading text, even a space, will continue to
> save my leading parenthetical text here, too.
> (And I'll be surprised if Google Groups is held on Microsoft SQL
> servers, but I guess why not?)
> Does this Query Analyzer issue rate making some kind of official bug
> report, I wonder? If telling you doesn't already count... I'd rather
> work around it than spend money. I was only being cute with quoted
> table names anyway - I don't need to quote my names, but better safe
> than sorry and it also helps them to stand out. But in this case, not
> ;-)
> Maybe Microsoft knew already; I find it difficult to pick keywords for
> a Knowledge Base search on this issue.
> Dan Guzman wrote:
>|||Dan Guzman wrote:
> I agree with your triage assessment. I'd rather see the devs spend their
> time tidying up SQL 2005.
I don't mind if Bill Gates's money is spent on my bug. Just not my
money, please. ;-)

> In any case, I believe the QA behavior should be
> documented in a KB article.
You think there is one, or someone should make one?

> BTW, it's a non-issue with SQL 2005 since Query
> Analyzer is superseded by SQL Server Management Studio.
A whole new set of quirks, no doubt! But how soon do you think you'll
get us off of 2000'
Thanks again, meanwhile.

Tuesday, March 27, 2012

clear sql error

Clear error in for each loop container

Sunday, March 25, 2012

clean server cache

clean server cache

clean server cache

Clean Install Windows Auth Error

Thursday, March 22, 2012

Clause where in [Select... ?

Classpath

ClassNotFound

Classic error and question how do restore?

Classic error and question how do restore?

Class not registered error while using Stored procedure

Class not registered error

Class does not support aggregation

Tuesday, March 20, 2012

Circular Reference?

Thursday, March 8, 2012

Child packages: Execute them all out of process?

Chewing on RAISERROR(N'[Text in brackets]ouch!', 11, 1)

check sql licence

Blog Archive

About Me