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 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
****************************************************************************** * * BEGIN STACK DUMP: * 04/25/07 08:32:19 spid 22 * * Exception Address = 00412373 (CTableIsolation::FNeedLockClass + 14) * Exception Code = c0000005 E * Access Violation occurred reading address 00000014 * Input Buffer 132 bytes - * S E L E C T S u p F r o m , S u p T o F R O M S u p p o r t P r * o f i l e s w h e r e S u p P r o f i l e = 3 0 4 5 * * * MODULE BASE END SIZE * sqlservr 00400000 008d2fff 004d3000 * ntdll 77f60000 77fbefff 0005f000 * KERNEL32 77f00000 77f5efff 0005f000 * ADVAPI32 77dc0000 77dfefff 0003f000 * USER32 77e70000 77ec1fff 00052000 * GDI32 78140000 78174fff 00035000 * RPCRT4 77e10000 77e66fff 00057000 * ole32 77b20000 77bd0fff 000b1000 * OLEAUT32 65340000 653dafff 0009b000 * VERSION 77a90000 77a9afff 0000b000 * SHELL32 77c40000 77d7afff 0013b000 * COMCTL32 71710000 71793fff 00084000 * LZ32 779c0000 779c7fff 00008000 * opends60 41060000 41085fff 00026000 * ums 41090000 4109cfff 0000d000 * MSVCRT 78000000 78045fff 00046000 * sqlsort 04000000 0408efff 0008f000 * MSVCIRT 780a0000 780b1fff 00012000 * sqlevn70 410a0000 410a6fff 00007000 * rpcltc1 77bf0000 77bf6fff 00007000 * COMNEVNT 410b0000 410fefff 0004f000 * ODBC32 1f7d0000 1f803fff 00034000 * comdlg32 77d80000 77db1fff 00032000 * SQLWOA 41100000 4110bfff 0000c000 * odbcint 1f8c0000 1f8d5fff 00016000 * NDDEAPI 75a80000 75a86fff 00007000 * WINSPOOL 77c00000 77c17fff 00018000 * SQLTrace 41130000 4117dfff 0004e000 * NETAPI32 4ca00000 4ca40fff 00041000 * NETRAP 77840000 77848fff 00009000 * SAMLIB 777e0000 777ecfff 0000d000 * WSOCK32 776d0000 776d7fff 00008000 * WS2_32 776b0000 776c3fff 00014000 * WS2HELP 776a0000 776a6fff 00007000 * WLDAP32 77950000 77978fff 00029000 * SQLFTQRY 41020000 4103afff 0001b000 * SSNMPN70 41190000 41195fff 00006000 * SSMSSO70 411a0000 411aafff 0000b000 * SSMSRP70 411b0000 411b7fff 00008000 * ENUDTC 74e20000 74e36fff 00017000 * XOLEHLP 74e40000 74e48fff 00009000 * MTXCLU 74e50000 74e5cfff 0000d000 * ADME 74e60000 74e72fff 00013000 * DTCUtil 74e80000 74e89fff 0000a000 * DTCTRACE 74e90000 74e96fff 00007000 * CLUSAPI 7f230000 7f23cfff 0000d000 * RESUTILS 7f250000 7f259fff 0000a000 * MSDTCPRX 74ea0000 74ed0fff 00031000 * DTCCM 74ee0000 74efdfff 0001e000 * msafd 77660000 7766efff 0000f000 * wshtcpip 77690000 77698fff 00009000 * rpclts1 77e00000 77e05fff 00006000 * RpcLtScm 755b0000 755bafff 0000b000 * MSWSOCK 77670000 77684fff 00015000 * rnr20 75600000 7560dfff 0000e000 * RpcLtCcm 756d0000 756defff 0000f000 * security 76e70000 76e81fff 00012000 * msapsspc 756e0000 756f3fff 00014000 * MSVCRT40 779d0000 779e4fff 00015000 * schannel 77400000 7741dfff 0001e000 * MSOSS 75710000 75734fff 00025000 * CRYPT32 75740000 757b4fff 00075000 * MSASN1 757c0000 757cffff 00010000 * msnsspc 757d0000 757eefff 0001f000 * digest 757f0000 757fffff 00010000 * SQLRGSTR 01b60000 01b64fff 00005000 * xpsqlbot 01bf0000 01bf5fff 00006000 * sqlboot 01c00000 01c07fff 00008000 * EntApi 0a000000 0a011fff 00012000 * PSAPI 76bf0000 76bfafff 0000b000 * WININET 01db0000 01e45fff 00096000 * SHLWAPI 01e50000 01eb8fff 00069000 * xpstar 02440000 02470fff 00031000 * SQLWID 02480000 02485fff 00006000 * SQLSVC 02490000 024a8fff 00019000 * odbcbcp 024b0000 024b5fff 00006000 * SQLRESLD 024c0000 024c5fff 00006000 * W95SCM 024d0000 024d7fff 00008000 * SQLSVC 024e0000 024e5fff 00006000 * sqlimage 02590000 025bcfff 0002d000 * * Edi: 00100C70: 000a0100 00030008 00000000 00000000 00100e70 00100e50 * Esi: 00000000: * Eax: 00000000: * Ebx: 00000004: * Ecx: 00000000: * Edx: 00000004: * Eip: 00412373: 007ea6e2 05f61577 01f88318 468b0013 d49f850f 00147e83 * Ebp: 01C8F864: 00000001 00000000 2149a1e4 00000000 00412336 01c8f898 * SegCs: 0000001B: * EFlags: 00010246: 003d0052 00490044 005f004c 00410043 004f004c 005f0053 * Esp: 01C8F854: 00412336 01c8f898 00000000 00000004 2149a1e4 00000000 * SegSs: 00000023: *******************************************************************************
SqlDumpExceptionHandler: Process 22 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Using 'sqlimage.dll' version '4.0.5' Stack Dump being sent to C:MSSQL7logSQL00026.dmp
Sunrise406, creation date and time: 2001/11/16(14:34:00), first LSN: 647599:6555:1, last LSN: 647633:8427:1, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQL7BACKUPSunrise406_tlog_200704250000.TRN'}).
Using 'xpstar.dll' version '2000.28.09' to execute extended stored procedure 'xp_regread'.
Database backed up with following information: Database: Sunrise406, creation date and time: 2001/11/16(14:34:00), pages dumped: 47456, first LSN: 647633:8425:1, last LSN: 647633:8427:1, sort order: 52, striped: 0, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQL7ackupSunriseDailyBackup9pm'}).
public static IEnumerable clr_DecodeTime(int EncodedTime)
{
List<Airports> airport = new List<Airports>();
Airports a = new Airports();
a.ArrTime = "1000";
a.DepTime = "1100";
airport.Add(a);
return airport;
}
public static void ProcessesFillRowTime(Object o, out SqlChars arrTime, out SqlChars depTime)
{
Airports airport = o as Airports;
arrTime = new SqlChars(airport.ArrTime);
depTime = new SqlChars(airport.DepTime);
}
};
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
public class Airports
{
private string arrcode;
public string ArrCode
{
get
{
return arrcode;
}
set
{
arrcode = value;
}
}
private string depcode;
public string DepCode
{
get
{
return depcode;
}
set
{
depcode = value;
}
}
private string depdate;
public string DepDate
{
get
{
return DepDate;
}
set
{
DepDate = value;
}
}
private string arrdate;
public string ArrDate
{
get
{
return ArrDate;
}
set
{
ArrDate = value;
}
}
private string arrtime;
public string ArrTime
{
get
{
return ArrTime;
}
set
{
ArrTime = value;
}
}
private string deptime;
public string DepTime
{
get
{
return DepTime;
}
set
{
DepTime = value;
}
}
}
} The message I get back is :
Msg 6538, Level 16, State 49, Line 1
.NET Framework execution was aborted because of stack overflow. Every time I try and debug the function my pc hang. ps I'm using vista business edition and vs 2005.
I am getting the following stack error in my SQL Server logs. I understand that there is a Microsoft Hotfix available for this and would like to know where will I find the HOTFIX for this because I didn't find it on Microsoft Website. We are currently on SQL 2000 SP 4 (Windows Server 2003 SP1).
Thanks !
***************************************************************************** Computer type is AT/AT COMPATIBLE. Bios Version is IBM - 1000 Current time is 09:02:25 11/16/07. 4 Intel x86 level 6, 2992 Mhz processor(s). Windows NT 5.2 Build 3790 CSD Service Pack 1.
Memory MemoryLoad = 56% Total Physical = 4095 MB Available Physical = 1800 MB Total Page File = 8026 MB Available Page File = 6052 MB Total Virtual = 2047 MB Available Virtual = 203 MB
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike
We are using SQL Server 2005 SP2 (32 bit) Clustered, and our server crashed twice within the last week. Has anyone experienced problems with the scheduler crashing SQL Server?
Using 'dbghelp.dll' version '4.0.5' **Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000 ***Stack Dump being sent to Z:MSSQL.1MSSQLLOGSQLDump0002.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 10/23/07 12:59:25 spid 0 * * Deadlocked Schedulers * * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump Stack Signature for the dump is 0x000001A5
We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:
This file is generated by Microsoft SQL Server version 9.00.1399.06 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE. Bios Version is IBM - 1001 Current time is 16:48:36 12/05/06. 2 Intel x86 level 15, 3600 Mhz processor (s). Windows NT 5.2 Build 3790 CSD Service Pack 1.
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
I have developed a report in SSRS 2005. When I try to export the output to Excel, it shows an error saying:
Error occurred during local report processing.
Error occurred during rendering of the report. Stack Empty.
Do anyone have any idea what this means and how to fix it?
I can render the report in other formats (Adobe, csv etc) without any problem. My other reports, under same project and using same dataset, are working fine.
When a self-developed CLR stored procedure throws an Exception and the Exception is caught in the code and for example logged, there only are class and method names in the stack trace lines, but not the line numbers. Can the line numbers somehow be included in the stack trace?
I'm not familiar with .NET framework stack traces, but when I tried to find out more information I hit concepts like PDB files, are such things somehow needed to enable line numbers in stack traces?
Hi, I've been getting stack dumps and reports of corruption in sys.sysobjvalues in a series of different read_only databases. Each time the CheckDB runs over the set of databases it reports exactly the same errors (listed below) in a subset of databases, but only ever in the read_only databases, not the read_write ones on the drive.
I've got three 2TB drives allocated to the cluster and the errors are ONLY ever happening on one of the drives (lets call it N: ), however under the covers all three drives are hosted on a virtualised SAN (EVA8000) on the same spindles so I'm a bit confused about why we're getting the errors. Thinking that perhaps there was a problem with cabling/HBA, etc I failed the instance to another node at another site which completely eliminates all hardware except the disks, yet the problem still happens. I've yet to failover to the mirrored set of disks in another EVA cabinet to test there, but can't take the outage hit just yet.
I can run the checkdb multiple times and get exactly the same error, yet if I set the DB to read_write then the corruption and stack dumps magically go away - even after I set db back to read_only - after I cycle SQL server then the whole sequence will start again - corruption while read_only and then no corruption once altered to read_write and after. If I restore a backup of the database taken either before or after the corruption was first reported then the restored DB checks out fine. Of course I can restore from backups but I've got 2TBs worth of databases to restore and I don't want to do that until I *KNOW* exactly what the cause is.
Anyone got any good ideas what may be happening?
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Here's the error and stack dump:
dbcc checkdb (MyDB) with physical_only
DBCC results for 'MyDB'.
Msg 2515, Level 16, State 1, Line 1
The page (1:24), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
There are 0 rows in 0 pages for object "sys.sysobjvalues".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
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 table name in SQL Server 2000 that has a space in itex: aim internationalI had trouble just in the query analyzer with this..I had to place thename in brackets [] for it to work. But now I'm in Visual Studio .Net2003 and it gives me another problem. I get the table name from a dropdown list selection and send it to a query string. But is gives me thiserror:***************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 objectname ''AIIM international''.*******************Any idea what I have to do for it to work ??? Can I use table nameswith spaces or it's just not a good idea???Thanks for the help guys!!JMT
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.
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 ...................
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 %Β
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
Can anyone tell me where I can find information about why sql server might be executing a stack and/or symptom dump? That is, what are some of the conditions that might cause this. Every time I try to DBCC DBREINDEX on a certain table, stack and symptom dumps occur and SQL Server remains incommunicado (can't connect, even locally) until I restart it. I'm going to try to fix the table by using rename table, create new table (and dependent objects) , transfer data, and toast the original table. No problem, I've done that before with smashing success. But, if this solves my problem, I'll still be no wiser about what it was that caused the problem in the first place. Any suggestions, questions, comments, or even jeers would be appreciated. Thanks, Chris
I made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
I need to create a proc. which would send a warning via xp_sendmail when the database space gets within 10% of allocated space. I have written one which uses sp_spaceused however this proc requires DBCC udateusage to be run in order to get accurate results. This would work but i don't know how quickly the data returned by sp_spaceused becomes corrupt therefore how often DBCC should be run. I ran DBCC on our main production server and it took a considerable amount of time. Basically I am looking for another way to do this without using sp_spaceused. Any ideas
Does anyone have any knowledge on how to find out the log space left in a database? I've tried to use the DBCC sqlperf (logspace) and it gave me the numbers but I only need one of the numbers. How can I retrieve it? Someone please let me know if you have any info. Thanks a million.