After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help
View 1 RepliesHi all,
How can i generate auto Number in SQL Server 2000,like AutoNumber Datatype in MsAccess....
Thanx in advance
Sajjad
this might be a stupid question.
I have a parameter which has a default value set to it. when a second value is selexcted is it necessary to click the view report button.
Could it be set to auto reload on dropdown selection.
I am using reporting Services 2000
Thanks.
Hi,I just deleted about 1 million records from a SQL Server 2000 database. Iaccess it using Access 2000.I have Compact and Repair available in the adp under Tools. Should I do thatfrom Access? Will it not mess anything up?Thanks!J~
View 2 Replies View RelatedI'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...
Thanks in advance.
dog
I want to use function "Compact and repair database" in SQL Server2000 which like function Access 2003.
How can i do ?
Thank you very much.
Please, help
Since a few days ago we are viewing these messages, several time at a day:
3/14/2008 2:18:30 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) created.
3/14/2008 2:18:44 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) is marked for unload due to memory pressure.
3/14/2008 2:18:44 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) unloaded.
In this database only have one assemblie installed created using Vb.Net 2005, please help me how to diagnose where are the problem.
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
I'm building a web site. there is a database.
I've set a primary key and unique of a field.also I set it to auto numbering.
Everytime I insert a record , that field will increase 1 (type bigint , start from 1).
After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?
Is there any faster and efficient way for exporting or unloading the data from a table (Contains about 400 to 500 million rows) into a flat or raw file. I am using SQL server 2005 and the Export Utility in SQL Server is very slow.
Any help is greatly appreciated...
How to Back up table in SQL 2000
View 3 Replies View Related
Hi there.
I have a corrupt .sdf database sat on a SD Card but i am unable to find a way to repair it.
So far I have tried a few different methods, most of which just tell me what i already know i.e. "the database is corrupt and needs repairing!!"
I am unable to open the file in SSMS object browser (see above message) which means i cant run repair / shrink utilites.
I am also unable to add the database to a vs2005 project (again see above message) meaning again i can't run any repair utilities.
I have also tried using the compact / Repair utility in Query analyser 3.0 on my Windows Mobile 5.0 PDA but once again i am greeted with an "error reading file - Run the Repair Utility" Message.
Lastly i've tried using the SQLCEengine.repair method and, depending on whether i do it on the PDA or on a desktop pc it throws the exceptions "Error occurred reading database file - run the repair utility"(PDA) or "invalid database type" (PC)
Can anyone shed any light as to whether there is another (easier???) way of doing this or if i just have to face it that this file is lost?
Thanks
ps. the code i used for the .repair is as below:
Dim conn As SqlCeEngine
Dim strdb As String
strdb = "SD CardPathnameDatabase.sdf"
conn = New SqlCeEngine(String.Format("Data Source={0}", strdb))
If conn.Verify = False Then
conn.Repair(Nothing, RepairOption.RecoverCorruptedRows)
MsgBox("Successfully Repaired")
End If
Hello
I have a table with two columns. First column is type and second column is ID. ID column must be unique and I want to give values in ascending order to the new records. For example first record's ID is 1 ,second one's is 2 ... How can I do this in sql server 2005?
I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
[Code] ....
My Attempt:
WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER(
PARTITION BYTestId
[Code] ....
Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
[Code] ....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.
any assistance would be greatly appreciated.
thanks
Hi to everybody,
can Sql Server Compact Edition run on Windows 2000 SP3?
Thanks,
Emanuele
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View RelatedGreetings to all!
Can MS Access 2000 databases be imported into SQL Server Compact 3.5 Beta?
Does Sql Server Compact Edition 3.5 support RDA synchronization with Sql Server 2000 database?
We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)
We are looking at our options regarding rolling back to Service Pack 3.
Can this be done ?
&
If so, is there a method documented on how to do this ?
Any advice would be appreciated.
I have schedule DTS to import every day few tables. It had been workinggreat and had no problem. All of a sudden I started getting this error.ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THETASK. 1 UNIDENTIFIED ERROR.Thanks.
View 2 Replies View RelatedHi there,
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
Thanks in advance
Hi,
Can we able to auto generate columns in sql server 2000?
I have a tabe with two fields No and Name. I need to auto generate no for each name i am going to enter. Can any give me a solution for this? If so i will be thankful to u.
This is my mail id suresh@tracy.in
Do reply me as soon as possible.
Hi All,Would someone give me an explanation on how SQL 2000 server backs up auser database? Is SQL server make a copy of user database first, andthen backup it up to a defined backup device? Or is it going straightto the defined backup device.Thanks,Bob.
View 1 Replies View RelatedI am maintaining a .net 1.0 asp.net web application that accesses a SQL server 2000 database. I need to backup the database and then restore it on a laptop. Currently it resides on a server. The problem is that on the server they are using sql server 2005. If I backup using 2005 and then try and restore this database using SQL server 2000 or 2005 on the laptop, I get an error message. "Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.
What can I do to restore the database? Must it be backed up with SQL server 2000 and then restored with SQL Server 2005?
Please help!
I am having a bit of a mare on the database backup front - i.e. monster
DB, not a great deal of space to back it up into.
As I do the backup, the file on the disk seems to stay at 2KB
How does the file get written? Is there a temp somewhere first?
Cheers in advance - Dave
Hey all,
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
Thanks!
In our application we use the ADODB.Recordset.
In SQL server 2000
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
Karikalan writes "We need alpha numeric auto increment code in sql server 2000.
(for eg.: ico1001, ico1002, ico1003,......)
Can any one send the code in MS sql server 2000? plz ..................
bcoz i am beginner in sqlserver 2000"
Hi,
I am monitoring my replication process and I have a problem with the loop detection, I can see origined transacctions in the server, executed in the same for the replication.
The subscriptions configuration is sp_helpsubscription
exec sp_addsubscription @publication = N'PUBLICATION',
@article = N'all',
@subscriber = N'192.168.0.23',
@destination_db = N'PRUEBABD',
@sync_type = N'none',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor',
@loopback_detection = 'true'
GO
I need for help.
Hi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
can anyone give suggestions how to generating a number starting with certain numbers, example 33###, because when i insert new record into datatabase
i want the number start 33111, or something and next record is 33112
thanks
Hi,
How to create an Auto Numbering column/field in MSSQL?
Thanks in advance
Xtine