Hi ,
if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.
Thanks
Shiva
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
Thanks
geri
Hi Folks:
This seems like a pretty straightforward question, but since I've been trying to find this in BOL and not having any luck, I thought I'd ask some folks more experienced than I.
How much room do I need on a SQL box to restore a 6.45 gig database? (it's
going from a huge machine to a backup tape, then from the backup tape to a
much smaller machine for testing an app, and we're trying to find out what we need to do to make it all work out.)
I'm assuming that I would need significantly more than the 6 gigs of the db for the work of actually doing the restore, but can't find out how much more...
Any ideas would be most appreciated.
Many thanks in advance,
Tom
hi all
i am under impression that indexes also stored seperately from data and need extra space . when we check in EM Table Info, size over there is just data size or sum of data and indexes. if it is just for data then is there is any space used to store indexes and that space is counted in space used by data base or else where.............. please clear my confusion.............. i am quite new in administartion of SQL Server
Quick question- how much disk space is required on the subscriber to initialize a subscription (transactional replication) – does it have to copy the snapshot files (e.g. the bcp files etc) from the distributor, and then initialize the subscriber from this, or does it do this over the network?
Does it differ depending on whether it is push or pull?
Say I have a 10 GB snapshot on the distributor with a push subscription to the subscriber. Do I need 20 GB free on the subscriber? E.g. 10 GB to receive the snapshot files + 10 GB for the subscriber DB? Or just 10 GB for the subscriber DB which is initialized from the snapshot files over the network?
Thanks in advance for any help with this.
Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records with the fields and table which I am using.
My table is beneficiary and fields are empid int, fname char(40), benefittype char(4) and benefitpercentage .
Following function is working but problem is not getting all results in one row, as described below.
CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary
Where Empid = @Empid
RETURN @ret
END
SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid
Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY
(
Records are in the table: (But I have many records like that so definitely I will need counter/function etc to get these data.
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33
Desired Result in one row:
Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33
I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?
View 9 Replies View RelatedMy web project (ASP.NET 2.0 / C#) runs against sql server 2000 and uses the System.Data.SqlClient.using System.Data.SqlClient;
I use System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand to make the connections to the database and do selects and updates. Is it correct to continue to use these against SQL Server 2005? I ask because I made a connection string (outside of .Net) for SqlServer 2005 using the SQL native provider and it had the following - Provider=SQLNCLI.1 and any connection strings I had made (also outside of ASP.NET) fro SQL Server all used Provider=SQLOLEDB.1. This is why I wondered if there is a different SqlClient in .Net 2.0 for SQL Server 2005?
Cheers
Al
Hi all,
I have Sqlserver2005 express edition installed on my pc. Now I wud like to install Sql sever2005 management studio express edition.
Can I do this or do I need delete the existing one to this?
Thanks in advance.
Hi, we have a SqlServer instance in production containing around 10databases.It has just been realised that all the db's are held on the small c:partition with only a gig or so of space left.On the server there is another partition and another hard disk bothwill ample space (few hundred gigs).What would be the best way of getting the data onto the otherpartitions with minimal impact on the applications. Can we move theprimary data files for each db? Should we just create secondary datafiles on the big partitions for all the db's? Is there a method ofmoving all the data at once?Any ideas on how we should approach this?(ps we dont have control of the sqlserver its outsourced, so simplerthe better).Thanks,Jim
View 2 Replies View RelatedI have recently purchased SQL server 2005 for a new install of bespoke software - I have however just been told that it only runs on SQL server 2000 at the moment - is there a way I can install sql 2000 server from the 2005 cd? any help would be greatly appreciated.
thanks
Hi There
In Sql Server 2005 sys.dm_db_task_space_usage is great for tracking down temdb space issues.
I have tempdb space issues in sql server 2000, how do i get the same info in SS2000 ? IE: What spid is using the most tempdb space ?
Thanx
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
One of my clients is upgrading to Sql 2000. I currently administer her server using a SQL 7 interface but I will need to upgrade that interface to sql 8 now to be able to give her the same level of support. How do I go about installing the required clients on my desktop in such a way as to not interfere with my own installation of MSDE?
I use Windows NT workstation 4. If I have a choice do I install developer's edition of SQL2000 or personal edition?
We also have pcanywhere access but this is too slow and unsure for my money.
Thank you,
Bo Graham
Hello,
This is a bit of a beginner questions... I need to start managing an SQL server 2000 using SQL server management studio (2005). I've heard there may be an issue with backwards compatibility, which might cause error when creating new tables or stored procedures... could someone please list the pros and cons on this?
Thank you all in advance
I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.
I tried with pivot but does not seems to be working.
Why am I getting message "A valid table name is required for in, out, or format options."
I used the syntax from a tutorial about bcp utility. I am trying to create a format file for flat file import and export.
My server instance is "stat-hpsqlexpress"
The database name is "STATRLO"
Owner is "dbo"
Table name is "PM-allactivity-emaillog_042315"
The bcp comand I am trying to run is:
bcp STATRLO.dbo.PM-allactivity-emaillog_042315 format nul -c -t, -f C:databaseActivity_c.fmt -S stat-hpsqlexpress - T
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Yes I know the instance says sqlexpress...it was upgraded.
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0
[Code] .....
This query runs forever and doesn't stop.
Hi,My webhost (1and1) is running SQL Server 2000 and their web tool supports the import of .bak files. However, when I try to import my .bak files created in SQL Server Management Studio Express I get the following error:"The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally."I have Googled this error and learnt that 2005 .bak files are not compatible with 2000 .bak files. I'm just wondering if there are any work arounds to this or alternative tools that I can create 2000 compatible .bak files from from 2000/2005 .mdf files.Thanks in advance.
Hello:
I have an old database created in SQL Server 2000,
now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams.
Please advise on how to make it work....
Thank you
Hi,
Pls let me know if SQL Server 2005 Management Studio (client tools only) is compatible with Windows Server 2000 or not?
Thanks in advance.
Regards,
Bhuvana
Hi,Can I use SQL 2005 Management studio to connect to SQL 2000 server?
View 1 Replies View RelatedHello,I had SQL2000 server running fine on the windows 2003 box. I then installedSQL 2005 Express. They both worked together fine. I then uninstalled SQL2005 Express and installed SQL 2005 Server. But when I open SQL ServerManagement Studio, I can only connect to the SQL 2000 engine. In the Objectexplorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I cansee the existing SQL 2000 databases).How can I get SQL Server Management Studio to 'see' the SQL 2005 databaseengine so I can create tables? I *think* its running because there is the'sqlservr.exe' process running, and during installation there is no issues.When I open SQL Server Management Studio, I choose 'Database Engine', thenmy local Servername with Windows Authentication. How do I connect to the SQL2005 instance?Thanks,Jack.
View 5 Replies View RelatedCan I use SQL 2005 Management Studio Express to manage my SQL Server 2000 dbs? Currently I have SQL Enterprise Manager Version 8.0 installed and was thinking I'd like to install and use SQL 2005 Management Studio Express instead. This doable?
View 4 Replies View RelatedHi
I have upgraded my local to sql server 2005, but still need to access the remote instance which is still at version 2008/8.0.
Enterprise Manager has been removed (by the SQL server 2005 installation)
Does anyone know how I can restart older version of SQL server instance (ie 2000/8.0) using SQL server Management Studio?
I don't have admin access on the server which the sql remote instance is hosted. Can get around it by asking the sys admin guy to do it for now but would prefer to continue to be able to do it myself.
SQL Server Configuration Manager can only manage SQL server 2005 versions - a nice little caveat!
Is there a way to stop/start remote services using 'net stop/start'? - without having to write code....
I'll continue to search for a solution, but if someon can point me in the right direction, I'd be most greatful.
hien
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
When I change any data on mentioned table above, Red exclamation sign appears left of the record .
How can I correct this problem?
Thanks.
hi .everyone !
i have ms visual studio 2005 with sql sever 2005 management tools
anyway .. i want to use and install sql server 2000 in this machine .. i tried to install it, but when i tried to connect to the server .. it gives me this error:
"you must use sql server 2005 management tools to connect to this server"
can you help me ? plz !?
I never noticed it before, but I am now using Management Studio to administer our sql databases and the issue became apparent. One of the databases located on a sql 2000 server, says "6.5 compatible", when viewed from Management Studio (but not from Enterprise Manager).
What are the step needed to upgrade the database? (I think I need to run the Sql 2005 Upgrade Advisor first, fix any errors found, then manually change compatibility mode (to 80) from the sql 2000 server where the database is located. )
TIA,
Barkingdog
I am trying to connect to another computer in my local network which has Windows XP installed. It has SQL server 2000. I get an error message 'Time out period elapsed' when i try to connect to SQL server 2000 thru SQL Server Management Studio Express' Please guide.
View 4 Replies View RelatedIs it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation the worst software documentation I have EVER SEEN?!
Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it.
Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.
I purchased SQL Server 2000 standard. Does the Management Studio a separate purhase? I saw a free download for SQL Server Management Studio Express but this is for SQL Express. Will this work on the SQL Server 2000?
Appreciate any input.
Thanks,
--dan
How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?