Query With A Table Name That Has A Space...

Jul 23, 2005

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

View 6 Replies


ADVERTISEMENT

The Query Processor Ran Out Of Stack Space....Please Simplify The Query.

Oct 9, 2007



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

END

View 2 Replies View Related

SQL Query Blank Space

Oct 11, 2000

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.

Thanks for any help.

View 3 Replies View Related

Space Used By A Table?

Jul 20, 2005

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.

View 6 Replies View Related

Can I Find The Drive Space Using A SQL Query?

Jun 1, 2001

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.

View 1 Replies View Related

Query Optimisor Out Of Stack Space

Oct 4, 2006

Hi,

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?

Kind Regards,
Martyn...

View 4 Replies View Related

Calculating Table Space

Nov 16, 2007

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

4
4
2
15
4
4
4
4
4
4
4
4
4
4
= 65 bytes
+ rowoverhead = 7

total = 72

View 12 Replies View Related

Lost Some Space (hardisk) When Execute Query

Jul 7, 2004

Dear All,

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
...................

----------------

View 2 Replies View Related

How To Find Free Disk Space Available From SQL Query

Jul 14, 2004

Hi all,
I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same.

Also if i want to find hard disk space of a different machine on network, it is possible to get the data??

Also can i get the CPU/Memory usage data of our machine by some SQL/C++ commands??

Please let me know if u know any of the answer...

thanks
Alok

View 1 Replies View Related

Know The Hard Disk's SQL Server Space Available From SQL Query

Oct 5, 2007



I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.

How can I do something?

Thanks for your help.

View 4 Replies View Related

Table Size - Trying To Find Out Space Used

Jul 8, 2002

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.

The second suggestion:

exec sp_msforeachtable @command1="print '?'",@command2="sp_spaceused '?'"

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.

View 2 Replies View Related

BCP And Table Space Allocation Quandary

Jul 1, 2004

Hey all, first time poster here.
Cheers!

Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.

Using BCP to load ~4 million records.

Largest record width is ~350 bytes.

When finished i have over 6 million pages reserved and only about 800 thousand actually used.

Which puts me 'round about 3 rows per page and WAY too much empty space.

YIKES!

Any thoughts as to why this is happening?

Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?

Thanks!

View 14 Replies View Related

Temp Table & Disc Space

Dec 4, 2007

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?

View 6 Replies View Related

Reclaiming Unused Space In A Table

Mar 13, 2008

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?

Rob

View 3 Replies View Related

SHIFT + SPACE In Table Editor

Feb 6, 2007

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?

Simon

View 2 Replies View Related

How To Unallocate Truncated Table Space

Jul 21, 2015

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.

View 5 Replies View Related

How To Replace Empty Space Or White Space In A String In A Stored Procedure

Nov 14, 2007

Hi,
 I am trying to do this:
UPDATE Users SET  uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
 

View 1 Replies View Related

Mutilple Space Gets Converted To Single Space In Report Viewer Control

Feb 23, 2007

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 &lt;Pre&gt; instead of <pre>)

But in all the cases result is same. The Report Viewer control is showing €œ&nbsp€? instead of space and €œ<Pre>€? tag instead of preserving spaces.

Please provide me a solution so that spaces can be preserved in Report Viewer.

View 1 Replies View Related

Can I Find The Space Allocated For A Database W/ Query Analyzer Using 7.0?

May 15, 2001

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?

View 2 Replies View Related

Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage

Nov 26, 2015

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 % 

Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace

[Code] ....

View 3 Replies View Related

Estimate Log Space For Alter Table Statement

Jan 31, 2007

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.

View 4 Replies View Related

Need Help Approximating How Much Hard Drive Space I Need For My DB Table.

May 12, 2007

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

View 4 Replies View Related

How Find Space Used, Database And Table Level?

Mar 22, 2000

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.

View 3 Replies View Related

Release Unused Space Reserved For A Table

Sep 22, 2005

Hey guys,

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?

View 12 Replies View Related

Release Table Space After Column Is Dropped

Apr 12, 2008

I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.

thx

View 6 Replies View Related

SQL 2012 :: Reclaim The Unused Table Space?

Sep 15, 2014

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?

View 5 Replies View Related

Reclaim Unused Space / Rebuild Table

Dec 30, 2014

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.

View 5 Replies View Related

Script To Analyze Table Space Usage

Feb 14, 2006

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 @fetch_status int

declare @proc varchar(200)
select@proc= rtrim(db_name())+'.dbo.sp_spaceused'

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

open Cur_Cursor

declare @TABLE_NAME varchar(200)

select @fetch_status = 0

while @fetch_status = 0
begin

fetch next from Cur_Cursor
into
@TABLE_NAME

select @fetch_status = @@fetch_status

if @fetch_status <> 0
begin
continue
end

truncate table #TABLE_SPACE_WORK

insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'


-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME

insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK

end --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW

)
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




CODO ERGO SUM

View 12 Replies View Related

Transact SQL :: Table With SPARSE Column Need More Space?

Jul 24, 2015

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)

View 5 Replies View Related

Trans Log-&>space Allocated 27GB, Space Used 100MB

Mar 2, 2005

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?

View 1 Replies View Related

Double Space Replaced With Single Space By Dbms ??!

Jul 20, 2005

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

View 2 Replies View Related

Transact SQL :: Query To Return Free Space From Mounted Drive

Nov 25, 2015

Is it possible to find out available free space from a mounted / network drive using tsql query? I am using sql server 2008 R2.

View 5 Replies View Related

How Can I Know The Records In SQL 2005 Table Possess Size Space?

Sep 19, 2006

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?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved