SQL Enterprise manager is reporting my database device size to be a negative number.
Primarily, this is the case for the log files, but also occurs on a few of the .dat files.
Is there a correction? Is this a display error, or an actual size conflict/
I have several clients who are reporting a negative file size on their database devices in Enterprise Manager. The sp_helpdevice procedure reports the size correctly. Any suggestions?
View 1 Replies View RelatedOk, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?
View 2 Replies View RelatedI'm Using a CLR for creating a trigger on database tables,
This is my Exception :
A .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightTrgg': System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index System.ArgumentOutOfRangeException: at System.Collections.ArrayList.get_Item(Int32 index) at Triggers.AvailableFlightTrgg() . A .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightInsert': System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightTrgg': System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index System.ArgumentOutOfRangeException: at System.Collections.ArrayList.get_Item(Int32 index) at Triggers.AvailableFlightTrgg() . INSERT [SamaCRSHistory].[dbo].[AvailableFlight] VALUES ('283','50','23','4','2','6','15','1','5','4','5/25/2008 8:30:00 AM','5/25/2008 7:00:00 AM','6/26/2008 12:00:00 AM','5','AR2580','125','0','False','False','1','Flight created on 5/25/2008 00:00:00','1', 'I', GETDATE()); INSEA .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightTrgg': System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index System.ArgumentOutOfRangeException: at System.Collections.ArrayList.get_Item(Int32 index) at Triggers.AvailableFlightTrgg() . A .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightInsert': System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user defined routine or aggregate 'AvailableFlightTrgg': System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index System.ArgumentOutOfRangeException: at System.Collections.ArrayList.get_Item(Int32 index) at Triggers.AvailableFlightTrgg() . INSERT [SamaCRSHistory].[dbo].[AvailableFlight] VALUES ('283','50','23','4','2','6','15','1','5','4','5/25/2008 8:30:00 AM','5/25/2008 7:00:00 AM','6/26/2008 12:00:00 AM','5','AR2580','125','0','False','False','1','Flight created on 5/25/2008 00:00:00','1', 'I', GETDATE()); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','7','125','125','0','0','Flight created on 5/25/2008 00:00:00','1'); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','4','25','25','20','20','Flight created on 5/25/2008 00:00:00','1'); The statement has been terminated. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext) at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command) at StoredProcedures.AvailableFlightInsert(Decimal AvailableFlightTimeTableID, Decimal Availabl... INSERT [SamaCRSHistory].[dbo].[AvailableFlight] VALUES ('283','50','23','4','2','6','15','1','5','4','5/25/2008 8:30:00 AM','5/25/2008 7:00:00 AM','6/26/2008 12:00:00 AM','5','AR2580','125','0','False','False','1','Flight created on 5/25/2008 00:00:00','1', 'I', GETDATE()); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','7','125','125','0','0','Flight created on 5/25/2008 00:00:00','1'); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','4','25','25','20','20','Flight created on 5/25/2008 00:00:00','1');RT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','7','125','125','0','0','Flight created on 5/25/2008 00:00:00','1'); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','4','25','25','20','20','Flight created on 5/25/2008 00:00:00','1'); The statement has been terminated. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext) at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command) at StoredProcedures.AvailableFlightInsert(Decimal AvailableFlightTimeTableID, Decimal Availabl... INSERT [SamaCRSHistory].[dbo].[AvailableFlight] VALUES ('283','50','23','4','2','6','15','1','5','4','5/25/2008 8:30:00 AM','5/25/2008 7:00:00 AM','6/26/2008 12:00:00 AM','5','AR2580','125','0','False','False','1','Flight created on 5/25/2008 00:00:00','1', 'I', GETDATE()); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','7','125','125','0','0','Flight created on 5/25/2008 00:00:00','1'); INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('283','4','25','25','20','20','Flight created on 5/25/2008 00:00:00','1');
This is my Code :case TriggerAction.Insert:using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
dr = command.ExecuteReader();
dr.Read();AvailableFlightID = (Decimal)dr[0];
AvailableFlightTimeTableID = (Decimal)dr[1];AvailableFlightAirlineID = (Decimal)dr[2];
AvailableFlightRoutingID = (Decimal)dr[3];AvailableFlightAPTerminalIDOrg = (Decimal)dr[4];
AvailableFlightAPTerminalIDDest = (Decimal)dr[5];AvailableFlightAirCraftID = (Decimal)dr[6];
AvailableFlightFlightStatusID = (Decimal)dr[7];AvailableFlightCateringID = (Decimal)dr[8];
AvailableFlightPayLoadTableID = (Decimal)dr[9];AvailableFlightArrTime = (DateTime)dr[10];
AvailableFlightDeptTime = (DateTime)dr[11];AvailableFlightDate = (DateTime)dr[12];
AvailableFlightDayName = (int)dr[13];AvailableFlightFlightNo = (String)dr[14];
AvailableFlightCapacity = (int)dr[15];AvailableFlightFreeBaggage = (int)dr[16];
AvailableFlightHaveChild = (bool)dr[17];AvailableFlightHaveParrent = (bool)dr[18];
AvailableFlightCommissionPercent = (int)dr[19];AvailableFlightRemark = (String)dr[20];AvailableFlightUserID = (Decimal)dr[21];
dr.Close();f (AvailableFlightID != 0)
{command = new SqlCommand(@"INSERT [SamaCRSHistory].[dbo].[AvailableFlight] VALUES ('" + AvailableFlightID + @"','" + AvailableFlightTimeTableID + @"','" + AvailableFlightAirlineID + @"','" + AvailableFlightRoutingID + @"','" + AvailableFlightAPTerminalIDOrg + @"','" + AvailableFlightAPTerminalIDDest + @"','" + AvailableFlightAirCraftID + @"','" + AvailableFlightFlightStatusID + @"','" + AvailableFlightCateringID + @"','" + AvailableFlightPayLoadTableID + @"','" + AvailableFlightArrTime + @"','" + AvailableFlightDeptTime + @"','" + AvailableFlightDate + @"','" + AvailableFlightDayName + @"','" + AvailableFlightFlightNo + @"','" + AvailableFlightCapacity + @"','" + AvailableFlightFreeBaggage + @"','" + AvailableFlightHaveChild + @"','" + AvailableFlightHaveParrent + @"','" + AvailableFlightCommissionPercent + @"','" + AvailableFlightRemark + @"','" + AvailableFlightUserID + @"', '" + "I" + @"', " + "GETDATE()" + @");", connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();command = new SqlCommand(@"SELECT TimeTableSubClassSubClassID , TimeTableSubClassMaxCapacity, TimeTableSubClassWaitListCapacity FROM [SamaCRS].[dbo].[TimeTableSubClass] Where TimeTableSubClassTimeTableID = '" + AvailableFlightTimeTableID + "'", connection);
dr = command.ExecuteReader();
System.Collections.ArrayList SubClassIDList = new System.Collections.ArrayList();
System.Collections.ArrayList SubClassCapacityList = new System.Collections.ArrayList();System.Collections.ArrayList SubClassWaitListCapacityList = new System.Collections.ArrayList();while (dr.Read())
{
SubClassIDList.Add(dr["TimeTableSubClassSubClassID"]);SubClassCapacityList.Add(dr["TimeTableSubClassMaxCapacity"]);SubClassWaitListCapacityList.Add(dr["TimeTableSubClassWaitListCapacity"]);
}
dr.Close();
SubClassIDList.TrimToSize();
SubClassCapacityList.TrimToSize();
SubClassWaitListCapacityList.TrimToSize();int CountID = SubClassIDList.Count;for (int i = 0; i <= CountID; i++)
{
command = new SqlCommand(@"INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('" + AvailableFlightID + @"','" +SubClassIDList[i] + @"','" +
SubClassCapacityList[i] + @"','" + SubClassCapacityList[i] + @"','" +
SubClassWaitListCapacityList[i] + @"','" + SubClassWaitListCapacityList[i] + @"','" +
AvailableFlightRemark + @"','" + AvailableFlightUserID + @"');", connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
////----------------------------------------
}
}
break;
this Code doesnt Work , but before writing this one I used the Code Below and It was working perfectly,
Previous Working Code :
//Initialize all TimeTable Classes for Created Flightcommand = new SqlCommand(@"SELECT TimeTableSubClassSubClassID FROM [SamaCRS].[dbo].[TimeTableSubClass] Where TimeTableSubClassTimeTableID = '" + AvailableFlightTimeTableID + "'", connection);
dr = command.ExecuteReader();
System.Collections.ArrayList SubClassIDList = new System.Collections.ArrayList();while (dr.Read())
{
SubClassIDList.Add(dr.GetValue(0));
}
dr.Close();
SubClassIDList.TrimToSize();foreach (object AvailableFlightSubClassID in SubClassIDList)
{command = new SqlCommand(@"INSERT [SamaCRS].[dbo].[AvailableFlightSubClass] VALUES ('" + AvailableFlightID + @"','" + AvailableFlightSubClassID + @"','" + AvailableFlightCapacity + @"','" + AvailableFlightCapacity + @"','" + 0 + @"','" + 0 + @"','" + AvailableFlightRemark + @"','" + AvailableFlightUserID + @"');", connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
Can ANy one Help me with this Exception , I have Checked The Tables for Number of Columns , its not from Column numbers ,I Think iys from For() Loop????????
I am having trouble increasing the size of the log device on a SQL 6.5 database. When I use SQL Enterprise Manager I get an error saying that the device has 0 MBs available. When I use the ALTER DB statement I get an error saying that there is not enough space on the disk, but I know this not to be the case. Has anyone any suggestions?
Thanks in advance,
Michael lawlor
Hi,
What I need to do is restore a full backup of one database to a database where disk space is limited.
so my source device is 2gb for data and 1gb for tranlog
my dest is 1.5gb for data and .5gb for tranlog
I am thinking this is not possible, but thought I would post here anyway!
Thanks in advance!
Can anyone suggest a method to reduce the size of one our log devices. The DB was set up
initially at 500Mb with a log size of 1 Gb (typo by the client). We would like to reduce it to 100Mb.
if possible. Our environment is SQL Server 6.5 with service pack 3.
thanks,,,brad
I have created a backup device. THe file it wrote (.dat) is 1.2 gig when I tried to run another backup to it it failed (3sec) Is there a size limitation on the Device or .dat file size?
Just testing things out I tried to directly open this file and it gave me a memory problem(Try closing other applications not enough memory to open "filename").
I would appreciate your help, thanks
Jerry
I just upgraded our application from SSCE 2.0 to SQL Mobile. Our app is written in C++, and we use OLE DB for most of our queries, including the routine the downloads and inserts our lookup table data. This application is running on a Dell Axim X51.
Using SSCE 2.0, this routine takes 236 seconds, with most time spent inserting data into various tables (using OLE DB). The resultant database size is 15.1 MB.
Using SQL Mobile, this routine now takes 675 seconds, with a resultant database size of 27.9 MB!! There is a noticable increase in time when the downloaded data is being inserted into the database.
What would be the reason(s) for the slower performance and the increased size of the database? This appears to be a monumental step backwards in performance. Any suggestions regarding improving the perfomance and size?
Keep getting this error when positioning to the last page of a report.
Using Server 2003...SqlRpt Svcs 2000 sp2
Detail error msg:
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Anyone have any suggestions? Any way to find out what collection is blowing?...or where parameter name: index comes from?
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies View RelatedBasically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions
Basically in the stored procedure that creates the table the query includes:
Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....
but i dont know to write this....any ideas???
I have the following query, I would like to know if I can replace negative values with a zero and if a posiive value appears then leave the value alone?
SELECT TimeStamp, Tag1,Tag2,Tag3
FROM dbo.keller8
Hey guys,
I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.
The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.
Is there a known bug in QA that would manifest itself as this? What is the cause for this?
Thanks in advance.
Hi
I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?
TIA
JDJ
jdj@gvd.dk
Hello:
I have gotten the following information from sp_spaceused:
----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------
I unfortunately since this is for a test database that I have put the data and log on the same device.
My questions are:
1) Why do I have negative numbers?
2) Should I be concerned about this?
3) for a live database can I drop the log and put the log on another device? What do I need to do?
4) has anybody seen this before?
5)what can I use to get accurate numbers?
THanks. Any information will be greatly appreciated.
DAvid Spaisman
hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maarten
I wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed
DROP TABLE #SpaceUsed
and one of results looks strange to me:
TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB
Anyone know reason of such result (negative value of unused space)?
Hi
I have values in col_A (smallint) as follows.
1
-4
3
6
-7
I want the negative to be remove and result as follows.
1
4
3
6
7
Please advise how?
Thanks
Jawad
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob
View 4 Replies View RelatedI'm personally in favor of using the SQL CLR where appropriate, although I'm wondering what the negative consequences of enabling SQL CLR might be? Its disabled by default within SQL Server 2005 and most likely 2008, so what was the reason behind this ... beyond the fear of the DBA enabling something he might not himself fully understand.
Thanks,
Doug Holland
Hi,
I´m working with chart type bar (Simple Bar) and i received four values from stored procedure, all values are negative numbers. The Reporting Service shows the values in the chart, but, doesn't show the "bars".... If one of four values will be positive, the report shows the values and the "bars" but if all values will be negative, the report shows just de values.
What do I do for the bars appear with four negative values ?
Thanks.
what am I doing wrong here? I can multipley * 1.25, but not -1.25. Every google entry I find is how to convert positive to negative numbers... I want to use update to multiply and lower the price by 25% (I used the function a bit to liberally to multiply by 125% and now want to bring the values down to earth)
update production.product set listprice = (listprice * -1.25)
select max(listprice) from production.product
Hi,
I have table with a column which has both negative and positive values.
I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.
Plase help me.
Ex: My Input Values are
column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667
OutPut goes like This
Column1 Column2
-186.5499257 null
-141.8985222 null
-95.3846883 null
-71.1359768 null
-69.39071172 -564.36
30.44750844 null
40.33666032 null
53.81527872 null
98.2967252 null
112.948667 335.84
How can I remove the - after an import into a database. I want to be able to convert all negative numbers to positive, once the data has been imported into the table
Any ideas?
Does anyone know why would SQLDataSource.Update method return a negative value even though its updating records correctly?Its happening with .NET 2.0.
View 2 Replies View RelatedHelp
Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?
If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?
Thank you ahead of time
Please help!!!
I'm building a database that I have a form. This form allows user input the invoice amount field which is the currency field. My ultimate goal is allow user put in the postive numbers and it will store as negative amount. Please let me know if there is the way to do that.
Thanks in advance.
Linh
Hello everybody,
I have a DTS package that imports excel spreadsheets.
The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999).
Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel.
Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?
Thanks for any help.
I have a table with debits and credits columns
Credit Debit
-7600
0080
90-30
I am transferring the debits and the credits in to one column in another table
Type Amount
C-76
D80
C90
D-30
And I am facing two problems one is the negative numbers don't transfer and also when I have debit and credit on the same line they are not transferring.
Select Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then 'D' Else 'C' End,
Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then Round(A.[Debit],2) Else Round(A.[Credit],2) End
FROM Tble A