I have a table name in SQL Server 2000 that has a space in it
ex: aim international
I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************
And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************
and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************
Any idea what I have to do for it to work ??? Can I use table names
with spaces or it's just not a good idea???
Thanks for the help guys!!
JMT
I received the following error today while migrating some code from SQL 2000 standard to SQL 2005 standard:
The query processor ran out of stack space during query optimization. Please simplify the query.
From what I gather, this error is usually an indication that one of the SQL Server 2005 maximum capacity specifications has been exceeded. However, I'm not sure which one. The only one that seems suspect is the number of nested subqueries (32), however I believe the numerous subqueries in my query would be classified as correlated rather than nested.
An example of my code is below. It is necessarily messy, as the output needs to be denormalized somewhat. Note that this runs just fine on SQL 2000, and it retrieves the 1500 or so rows of data in about 30 seconds. Thanks in advance for any ideas as to what may be causing this error. (Sorry for not using code-block, but all the html tags it threw in were exceeding the 50,000 character limit for the message)
BEGIN
SELECT
(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 1) AS A5,
(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 2) AS A6,
(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 3) AS A7,
(SELECT Agent FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 4) AS A8,
dbo.f_formatpseudodate(Accrued_Interest_To) AS AI,
dbo.f_getmiscdescription('AM', c.Card_Number, Amended) AS AM,
Basis_Shareblock_1 AS B1,
Basis_Shareblock_2 AS B2,
Basis_Shareblock_3 AS B3,
Basis_Shareblock_4 AS B4,
Basis AS BS,
dbo.f_getmiscdescription('C1', c.Card_Number, Price_Code_1) AS C1,
dbo.f_getmiscdescription('C2', c.Card_Number, Price_Code_2) AS C2,
dbo.f_getmiscdescription('C3', c.Card_Number, Price_Code_3) AS C3,
dbo.f_getmiscdescription('C4', c.Card_Number, Price_Code_4) AS C4,
dbo.f_getmiscdescription('C5', c.Card_Number, Price_Code_5) AS C5,
dbo.f_getmiscdescription('C6', c.Card_Number, Price_Code_6) AS C6,
CAST(Card_Date AS smalldatetime) AS CD,
c.Card_Number AS CN,
dbo.f_getisocurrencycode(Currency_Code) AS CR,
Card_Status AS CS,
dbo.f_formatpseudodate(Record_Date) AS CT,
Currency_Description AS CU,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 1) AS D1,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 2) AS D2,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 3) AS D3,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 4) AS D4,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 5) AS D5,
(SELECT New_Issue_Description FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 6) AS D6,
dbo.f_formatboolean(Dutch_Auction) AS DA,
dbo.f_formatpseudodate(Dated_Date) AS DD,
dbo.f_getmiscdescription('DI', c.Card_Number, Distribution_In) AS DI,
Dealer_Manager_2 AS DL,
Dealer_Manager_1 AS DM,
dbo.f_getmiscdescription('DV', c.Card_Number, Dividend_In) AS DV,
Basis_Surrender_1 AS E1,
Basis_Receive_1_1 AS E2,
Basis_Amount_1_1 AS E3,
Basis_CUSIP_1_1 AS E4,
Basis_Receive_1_2 AS E5,
Basis_Amount_1_2 AS E6,
Basis_CUSIP_1_2 AS E7,
Basis_Receive_1_3 AS E8,
Basis_Amount_1_3 AS E9,
dbo.f_getmiscdescription('EC', c.Card_Number, Record_Date_Code) AS EC,
Equity_Debt AS ED,
dbo.f_getmiscdescription('EF', c.Card_Number, Effective_Date_Code) AS EF,
dbo.f_getmiscdescription('EX', c.Card_Number, Expiration_Date_Code) AS EX,
dbo.f_getmiscdescription('F1', c.Card_Number, Refer_Code_1) AS F1,
dbo.f_getmiscdescription('F2', c.Card_Number, Refer_Code_2) AS F2,
dbo.f_getmiscdescription('F3', c.Card_Number, Refer_Code_3) AS F3,
dbo.f_formatpseudodate(Effective_Date) AS FD,
File_Activity_Type AS FT,
dbo.f_getmiscdescription('G1', c.Card_Number, Rights_Ratio_1) AS G1,
dbo.f_getmiscdescription('G2', c.Card_Number, Rights_Ratio_2) AS G2,
dbo.f_getmiscdescription('G3', c.Card_Number, Rights_Ratio_3) AS G3,
RTRIM(LTRIM(Original_Card_Number)) AS GC,
dbo.f_getmiscdescription('GD', c.Card_Number, Ex_Rights_Date_Code) AS GD,
dbo.f_getmiscdescription('GT', c.Card_Number, Rights_To) AS GT,
Basis_Amount_2_3 AS H1,
Basis_CUSIP_2_3 AS H2,
Basis_Surrender_3 AS H3,
Basis_Receive_3_1 AS H4,
Basis_Amount_3_1 AS H5,
Basis_CUSIP_3_1 AS H6,
Basis_Receive_3_2 AS H7,
Basis_Amount_3_2 AS H8,
Basis_CUSIP_3_2 AS H9,
Information_Agent_1 AS I1,
Information_Agent_2 AS I2,
Interest_Rate AS [IN],
dbo.f_getmiscdescription('J1', c.Card_Number, Subscription_Ratio_Code_1) AS J1,
dbo.f_getmiscdescription('J2', c.Card_Number, Subscription_Ratio_Code_2) AS J2,
dbo.f_getmiscdescription('J3', c.Card_Number, Subscription_Ratio_Code_3) AS J3,
Basis_CUSIP_4_2 AS K1,
Basis_Receive_4_3 AS K2,
Basis_Amount_4_3 AS K3,
Basis_CUSIP_4_3 AS K4,
Basis_Receive_3_3 AS L1,
Basis_Amount_3_3 AS L2,
Basis_CUSIP_3_3 AS L3,
Basis_Surrender_4 AS L4,
Basis_Receive_4_1 AS L5,
Basis_Amount_4_1 AS L6,
Basis_CUSIP_4_1 AS L7,
Basis_Receive_4_2 AS L8,
Basis_Amount_4_2 AS L9,
dbo.f_formatpseudodate(Withdrawal_After) AS LA,
dbo.f_getmiscdescription('LC', c.Card_Number, Withdrawal_After_Code) AS LC,
dbo.f_getmiscdescription('LG', c.Card_Number, Eligibility) AS LG,
Withdrawal_After_Time AS LT,
dbo.f_getmiscdescription('LZ', c.Card_Number, Withdrawal_After_Zone) AS LZ,
Option_Expiration_Time AS M1,
Rights_Expire_Time AS M3,
Expiration_Date_Time AS M4,
dbo.f_formatpseudodate(Maturity_Date) AS MD,
Maximum_Eligibility AS ME,
dbo.f_validatecardnumber(Refer_Card_1) AS N1,
dbo.f_validatecardnumber(Refer_Card_2) AS N2,
dbo.f_validatecardnumber(Refer_Card_3) AS N3,
dbo.f_getmiscdescription('NT', c.Card_Number, Interest_Rate_Code) AS NT,
DTC_Match_Card_Number AS O3,
Offer_By AS OB,
Odd_Lot AS OD,
dbo.f_getmiscdescription('OV', c.Card_Number, OverSubscription) AS OV,
dbo.f_formatpseudodate(Option_Expiration_Date) AS OX,
(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 4) AS P1,
Information_Agent_Telephone_1 AS P2,
Information_Agent_Telephone_2 AS P3,
Dealer_Manager_Telephone_1 AS P4,
Dealer_Manager_Telephone_2 AS P5,
CUSIP_Number AS PN,
dbo.f_formatpseudodate(Pro_Ration_Date) AS PO,
dbo.f_getmiscdescription('PY', c.Card_Number, Payable_To) AS PY,
dbo.f_getmiscdescription('PZ', c.Card_Number, Protect_Period) AS PZ,
Price_1 AS R1,
Price_2 AS R2,
Price_3 AS R3,
Price_4 AS R4,
Price_5 AS R5,
Price_6 AS R6,
dbo.f_getmiscdescription('RB', c.Card_Number, Rights_Transferable) AS RB,
Rights_CUSIP AS RC,
Issuer_Description AS RD,
dbo.f_getmiscdescription('RI', c.Card_Number, Pro_Ration_Date_Code) AS RI,
Record_Type AS RT,
dbo.f_formatpseudodate(Rights_Expire_Date) AS RX,
CAST(Subscription_Ratio_1 AS varchar(12)) + Subscription_Ratio_1_Unit AS S1,
CAST(Subscription_Ratio_2 AS varchar(12)) + Subscription_Ratio_2_Unit AS S2,
CAST(Subscription_Ratio_3 AS varchar(12)) + Subscription_Ratio_3_Unit AS S3,
dbo.f_getmiscdescription('SC', c.Card_Number, Source_Name) AS SC,
Service_Type AS SE,
dbo.f_getmiscdescription('ST', c.Card_Number, Subscription_To) AS ST,
(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 1) AS T2,
(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 2) AS T3,
(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 3) AS T4,
(SELECT Redemption_Agent_Telephone FROM Redemption_Agent ra WHERE ra.Card_Number = c.Card_Number AND Redemption_Agent_Number = 4) AS T5,
(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 1) AS T7,
(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 2) AS T8,
(SELECT Agent_Telephone FROM Agent a WHERE a.Card_Number = c.Card_Number AND Agent_Number = 3) AS T9,
Tickler_Date AS TK,
dbo.f_getmiscdescription('TP', c.Card_Number, To_Purchase) AS TP,
dbo.f_getmiscdescription('TS', c.Card_Number, Transaction_Status) AS TS,
dbo.f_gettxtext(c.Card_Number) AS TX,
Basis_CUSIP_1_3 AS U1,
Basis_Surrender_2 AS U2,
Basis_Receive_2_1 AS U3,
Basis_Amount_2_1 AS U4,
Basis_CUSIP_2_1 AS U5,
Basis_Receive_2_2 AS U6,
Basis_Amount_2_2 AS U7,
Basis_CUSIP_2_2 AS U8,
Basis_Receive_2_3 AS U9,
RTRIM(Issue_Description) AS UD,
Voluntary_Mandatory AS VM,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 1) AS W1,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 2) AS W2,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 3) AS W3,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 4) AS W4,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 5) AS W5,
(SELECT New_Issue_CUSIP FROM New_Issue ni WHERE ni.Card_Number = c.Card_Number AND New_Issue_Number = 6) AS W6,
dbo.f_formatpseudodate(Withdrawal_Prior_To) AS WD,
Withdrawal_Prior_To_Time AS WH,
dbo.f_getmiscdescription('WO', c.Card_Number, Withdrawal_Prior_To_Code) AS WO,
dbo.f_getmiscdescription('WZ', c.Card_Number, Withdrawal_Prior_To_Zone) AS WZ,
(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =3) AS X1,
(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number = 3) AS X2,
dbo.f_formatpseudodate(Expiration_Date) AS XD,
dbo.f_formatpseudodate(Ex_Rights_Date) AS XR,
dbo.f_getmiscdescription('XT', c.Card_Number, Rights_Expire_Code) AS XT,
Payable_Rate_Shareblock AS Y1,
Payable_Rate_Surrender AS Y2,
(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y3,
(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y4,
(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =1) AS Y5,
(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y6,
(SELECT Payable_Rate_Amount FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y7,
(SELECT Payable_Rate_CUSIP FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =2) AS Y8,
(SELECT Payable_Rate_Receive FROM Payable_Rate pr WHERE pr.Card_Number = c.Card_Number AND Payable_Rate_Number =3) AS Y9,
dbo.f_formatpseudodate(Payment_Date) AS YD,
dbo.f_getmiscdescription('YR', c.Card_Number, Payable_Rate) AS YR,
dbo.f_getmiscdescription('Z1', c.Card_Number, Option_Expiration_Zone) AS Z1,
Rights_Expire_Zone AS Z3,
Expiration_Date_Zone AS Z4
FROM COMMON c
INNER JOIN DC_DailyCard dc ON c.Card_Number = dc.Card_Number
WHERE c.Card_Status = 'R' AND c.Card_Date BETWEEN @start_date AND @end_date
Hi: I did the following query on SQL Server A and got the following result. Input: select replace('10/10/00', '/', '') Result: 101000 (1 row(s) affected) However, I did the same query on SQL B and got a different result. Input: select replace('10/10/00', '/', '') Output: 10 10 00 (1 row(s) affected)
Both Servers are SQL 7.0 with SP2. I checked the setting, it seems to be identical. Does anyone know what could be the problem or have any hint what I need to check? I beleieve the correct result should be the first result.
I have a database that has grown larger than I had expected. I thinkthere is one table that is at the root of the problem. The table isdefined as follows:CREATE TABLE [dbo].[UserAudit] ([UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,[UserAuditAction_id] [int] NOT NULL ,[Dataset_id] [int] NOT NULL ,[UserName] [char] (64) NOT NULL ,[TableName] [char] (64) NOT NULL ,[Detail] [varchar] (4000) NOT NULL ,[DateRecorded] [smalldatetime] NOT NULL ,[Dsc] [char] (256) NULL)There are 14919 records in this table. When I do the calculations iapproximate that space used by this table should be in the region of10mb (4+4+4+64+64+290(average length of Detail column)+4+256) * 14919.When I execute "sp_spaceused 'UserAudit'" i see that 119MB are beingused by this table.name rows reserved data index_size unusedUserAudit 14919 119808 KB 119352 KB 400 KB 56 KBEven if I use 4000 for the Detail column in my calculations I stillcome up with about only 64mb.Any ideas on whats going on here?Thanks in advance.
Hey - I know that I can find space information about SQL Server. Allocated space, free space, used space, data space, etc... BUT is there a way that I can query how much total/available space is on the actual drive? For example, let's say that I have SQL installed on the D drive of a Server; i also have another application on that drive. I know that I can query how much room SQL Server is using, but can i query how much total/available space is on the drive? Any help appreciated.
I have recently upgraded to SQL Server 2005 (SP1) Developer Edition from SQL Server 2000 (SP4). Since doing so, i have been unable to delete any records from a specific table.
I have found this is due to having approximately 340 foreign key constraints referencing the UserID column of the table.
This functionality worked in SQL Server 2000. Do Microsoft plan to release any fixes for this issue?
I currently have 8,000 rows in the orders table and it is estimated that it will have in average 50 orders daily. The orders need to be kept for 6 months, before it is archived and deleted from the database. I calculated the amount of space that needs to be reserved for the table but unsure if I am on the right track in calculating the table size.
would there be anything wrong in my calcultation that i missed?
int datetime tinyint nvarchar(15) int int int int money money money money datetime
Got some problem... how come i lost some space (hardisk) when execute query... (the query is listed below)
I want to know is it create some virtual tables or view when we execute query ??? N where's the location in our hardisk ??
thx
Listed Query --------------------- SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Absen.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN HRAbsensiKaryawan Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID Union SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN ADMAbsensiKaryawan Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID UNION SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN ADMAbsensiBulananSite Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID Union SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN HRAbsensiKaryawanNonStaf Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID UNION SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN HRAbsensiKaryawanStaf Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID UNION SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN LCAbsensiBulananSite Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID UNION SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID, Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID, Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi, Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName, Data.GolonganID FROM AMBranchMaster Bran RIGHT OUTER JOIN HRDataKaryawan Data LEFT OUTER JOIN HRDistribusiKaryawan Dis LEFT OUTER JOIN TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN LCAbsensiIndirect Absen ON Data.KaryawanID = Absen.KaryawanID ON Bran.BranchID = Absen.BranchID LEFT OUTER JOIN HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID UNION ...................
I'm trying to determine how much space some tables use (SQL2000), and I found 2 suggestions posted earlier, but can't get them to work for me.
The first was "Right click on the DB in Enterprise Manager, select view then taskpad."
When I try that, I get some of the tables displayed with the info I want, but I can't see them all and can't scroll down beyond the first 22 tables in the database.
It says "command copleted successfully, but where does the output to this go ?? Is there something other than "print" I should use ? The grid pane is empty.
I have a stored proc that processes large amounts of data. I have used temp tables in this stored proc. After I drop these temp tables, it doesn't release the disc space and my hard drive runs out of space before the process ends. When I stop the sql service and restarts it, it releases the disc space. How can I force the release of disc space inside of my stored procedure?
I'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.
After doing something like this,
update tbl set col = ''
the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,
insert into tbl2 (cola, colb, colc) select * from tbl
the new table is smaller as expected (about half the size in my case). Any ideas?
The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.
However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.
Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?
Using lot intermediate table in SP, at end of the SP, those table are getting truncating.but those pages are still unallocated and unused , How to unallocated the space and add to OS drive.
Because of above scenario, we are wasting lot of space. i am seeing lot of unallocated space.
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
My group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database. What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns. Any suggestions? Thanks in advance.
In our SQL Server database we will have a table that will be populated with about 2000 recordsper day. That is 2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytesof available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,based solely on the number of records entered into this table per day. The problem is I don'tknow how to calculate how much hard drive space we need. I think I read that using varchar,sql server 2005 really optimizes a database. Here is a typical example of data in ourdatabase. I put dots on three lines between the first and last sample record to justillustrate that there are many records in between. Basically we only need 8 months of data at a time in the table and then we can purgerecords older than 8 months.Can someone help me approximate how much hard drive space I might need for 8 months of data,given the following sample record in the database? Sample: -->34.5 4.08 10.6 .0012 Sample Table in my DB just for illustration: (PPsquare inch) (Diameter) (Weight gm) (coeffOfSatFriction) 34.5 4.08 10.6 .0012...21.7 3.54 6.22 .019
The sp_spaceused proc seems to have been removed in 7.0? Dbcc sqlperf also seems to have been removed? Surely there must be a counterpart - which is? I like to have a script which shows the space usage especially on a table basis for a periodic report.
I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..
Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?
currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
Edit 2007-8-9: Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the -- output from the sp_spaceused stored procedure -- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select [FileSizeMB]= convert(numeric(10,2),sum(round(a.size/128.,2))), [UsedSpaceMB]= convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) , [UnusedSpaceMB]= convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) , [Type] = case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end, [DBFileName]= isnull(a.name,'*** Total for all files ***') from sysfiles a group by groupid, a.name with rollup having a.groupid is null or a.name is not null order by case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end, a.groupid, case when a.name is null then 99 else 0 end, a.name
create table #TABLE_SPACE_WORK ( TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE_USED ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME SYSNAME not null , TABLE_ROWS int not null , RESERVED int not null , DATA int not null , INDEX_SIZE int not null , UNUSED int not null , USED_MBnumeric(18,4)not null, USED_GBnumeric(18,4)not null, AVERAGE_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null, )
declare Cur_Cursor cursor local for select TABLE_NAME= rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE' order by 1
) select TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED, USED_MB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024),4), USED_GB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024*1024),4), AVERAGE_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),RESERVED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_DATA_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),DATA))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_INDEX_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_UNUSED_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),UNUSED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end from ( select TABLE_NAME, TABLE_ROWS, RESERVED= convert(int,rtrim(replace(RESERVED,'KB',''))), DATA= convert(int,rtrim(replace(DATA,'KB',''))), INDEX_SIZE= convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), UNUSED= convert(int,rtrim(replace(UNUSED,'KB',''))) from #TABLE_SPACE_USED aa ) a order by TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc go
drop table #TABLE_SPACE_WORK drop table #TABLE_SPACE_USED drop table #TABLE_SPACE
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb( unsprsid INT IDENTITY(1,1) NOT NULL, Firstname varchar(20) NOT NULL, Lastname varchar(20) NOT NULL, Tel int NOT NULL, adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL, Lastname varchar(20) NOT NULL, Tel int NOT NULL, address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress) SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb] SET IDENTITY_INSERT [dbo].[Unsprstb] OFF The Unssprstb need only  466,031MB !
That means the Table with SPARSE column need more storage, Why?Â
By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
We can know easily a database disk size, but can we konw a table in SQL 2005 database possess size? and more, how can I know the records in SQL 2005 Table possess size space?