Dear all,
I have 3 column: date, time, stocks name, price
I have 2 questions:
1. what is the command (or query languange) to get the
the first and/or last observations for any given day (i know it can be done in
aggregate query, in Ms acces but can it be done in SQL server query as well?)?
e.g. I want to get the first and last price of the day for any particular stocks
2. how to calculate return with the following formula:
return=log P(t)-log P(t-1), where P(t) is price at
time t say 10 am and P(t-1) is price at one period
previous t say 9 am?
Regards
CharlyThe min and max functions will tell you the price ranges
eg select max(pricecolumn) from tablename where date='20040517'
select min(pricecolumn) from tablename where date='20040517'
If you want to be more selective look at the date/time setting you are using in your data and tailor the where command to select at that particular time
eg where date='2003-02-28 10:00:00.000'
Look at books online for the log function, and use selective where clauses for the times, ie where date='2004-05-17 10:00:00.000'|||select max(pricecolumn) from tablename where date='20040517'
broup by [stocks name]
Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Tuesday, March 20, 2012
Re Recorcer suspect database
Hi
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, tat 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tte de page
PFS_PAGE incorrecte. Le type est 0. Contrlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Herv RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
> Various Reasons for database going to suspect Status.
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
> How to set to Emergency Mode
> --X-
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> Thanks
> Hari
> SQL Server MVP
>
> "Herv RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, tat 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tte de page
PFS_PAGE incorrecte. Le type est 0. Contrlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Herv RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
> Various Reasons for database going to suspect Status.
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
> How to set to Emergency Mode
> --X-
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> Thanks
> Hari
> SQL Server MVP
>
> "Herv RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Re Recorcer suspect database
Hi
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, État 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tête de page
PFS_PAGE incorrecte. Le type est 0. Contrôlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Hervé RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
>
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
>
> Various Reasons for database going to suspect Status.
>
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
>
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
>
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
>
> How to set to Emergency Mode
> ---
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
>
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Hervé RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> What do you think about MSSQLRecovery 2.0, to recorver suspect database.
>> Do you know a better software to recover suspect database.
>>
>> Best regards
>>
>> Hervé RESCOURIO
>>
>>
>
>> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, État 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tête de page
PFS_PAGE incorrecte. Le type est 0. Contrôlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Hervé RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
>
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
>
> Various Reasons for database going to suspect Status.
>
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
>
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
>
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
>
> How to set to Emergency Mode
> ---
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
>
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Hervé RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> What do you think about MSSQLRecovery 2.0, to recorver suspect database.
>> Do you know a better software to recover suspect database.
>>
>> Best regards
>>
>> Hervé RESCOURIO
>>
>>
>
>> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Re Recorcer suspect database
Hi
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, tat 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tte de page
PFS_PAGE incorrecte. Le type est 0. Contrlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Herv RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
> Various Reasons for database going to suspect Status.
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
> How to set to Emergency Mode
> --_-
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> Thanks
> Hari
> SQL Server MVP
>
> "Herv RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
One of my client give me a suspect database, but he doesn't have any backup
of this base.
When i try to recorver the data with the command : DBCC CHECKDB with
REPAIR_REBUILD (or REPAIR_ALLOW_DATA_LOSS)
(http://65.214.43.26/articles/18/view.asp?id=7381), i got an error :
Serveur : Msg 8946, Niveau 16, tat 12, Ligne 2
Erreur de table : La page d'allocation (1:1) a des valeurs d'en-tte de page
PFS_PAGE incorrecte. Le type est 0. Contrlez le type, et les ID d'objet et
de page pour cette page.
What can i do?
The Microsoft support is too expensive, and i want to find an other solution
for the futur, to recorver my data.
When i try MSSQLRecorvery, i recorver all data, and it seems to be the best
solution. What do you think about this software?
Best regards
Herv RESCOURIO
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
eulsf9bNFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> First identify the cause for the Suspect. If it is due to some file access
> issues you do not want to use any tools instead use the SQL Server
> commands
> to recover the database.
> Various Reasons for database going to suspect Status.
> 1. The MDF or LDF file for the database is not available during startup
> 2. LDF file missing or Corrupted
> 3. MDF file page allocation problems.
>
> 1. The first one can be resolved by executing "sp_resetstatus" stored
> procedure and restart SQL server services
> 2. Start the database in Emergency mode , Update the Status column in
> master..sysdatabases table for that database to 32768.
> After this database will be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data.
> 3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD
> option. If the problem is not rectified
> wither restore from Backup or contact Microsoft support.
>
> How to set to Emergency Mode
> --_-
> Use emergency mode (also called bypass mode) to recover data when SQL
> Server marks a database suspect and the automatic recovery will fail.
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use
> this script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> Thanks
> Hari
> SQL Server MVP
>
> "Herv RESCOURIO" <hr@.segilog.com> wrote in message
> news:ufots5bNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> One of my client give me a suspect database, but he doesn't have any
backup
> of this base.
> The Microsoft support is too expensive, and i want to find an other
solution
> for the futur, to recorver my data.
> When i try MSSQLRecorvery, i recorver all data, and it seems to be the
best
> solution. What do you think about this software?
Just do the backups.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Wednesday, March 7, 2012
RDA Push Command Failing ...
Hi,
I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-
The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.
Cheers,
Jiggy!
Hi All,
I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?
Cheers very much,
Jiggy!
|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.
Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.
-Joshua
RDA Push Command Failing ...
Hi,
I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-
The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.
Cheers,
Jiggy!
Hi All,
I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?
Cheers very much,
Jiggy!
|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.
Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.
-Joshua
Subscribe to:
Posts (Atom)