Sign (+ / -) Discrepancy W/ODBC - HELP!
Jan 10, 2002
I have a SQL 7 db that I use a DTS package to import Oracle data into. The package works fine and imports all the appropriate data. However, if I use an Access 2000 database to attach to the data via ODBC (using the MS SQL Server driver), the negative sign is dropped when displaying data in a table, query, or report.
Same problem in SQL Server - if I query the SQL 7 data via the Query Analyzer, the negative signs are dropped. However, if I query the SQL data using the Enterprise Manager (i.e., Open Table...Return All Rows via right click on the table), the data shows up properly with the negative signs there. Bottom line - the data is correct, but doesn't get displayed correctly in QA or via ODBC.
What gives?! Can anyone explain to me the "connections" that occur between EM and QA? Looks like QA uses a "temporary" ODBC connection to talk to the data, while the EM connects "directly" to the data. Also, what gives with the MS SQL Server ODBC driver - why wouldn't it display the negative signs? Is there a better SQL Server ODBC driver that I should/could use? I've tried configuring the ODBC connection differently, but to no avail.
Any help is greatly appreciated, as the data in question is being used in court and absolutely HAS to be accurately displayed.
Thanks!
Jeff Jones
Atlanta, GA
View 4 Replies
ADVERTISEMENT
Jul 25, 2007
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
View 3 Replies
View Related
Apr 11, 2007
I couldn't remember which computers I had already installed sp2 on so checked but one computer (running Windows Server 2003 and SQL 2005 Enterprise Edition) seems to have a discrepancy. SELECT @@VERSION returns (Build 3790: Service Pack 1) but the status bar shows "9.0 SP2". Thanks for any insight into this.
-Dave
View 5 Replies
View Related
Oct 25, 1999
During daily scheduled maintenance the following error occurs, which causes the maintenance job to fail. How can we fix?
Thanks!
Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=1085440 extent id=1085464 logical pg#=1085464 object id on extent=8 (object name = syslogs) indid on extent=0
View 2 Replies
View Related
Sep 28, 1999
I am getting the following error on a version 6.5 database
when I run the weekly database backup.
"Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=491520 extent id=491720 logical pg#=491720 object id on extent=8 (object name = syslogs) indid on extent=0"
The backup script I run is as follows..
SQLMAINT.EXE -D ECAP -CkDB -CkAl -UpdSts -BkUpDB F:MSSQLBACKUP -BkUpMedia DISK -DelBkUps 8 -Rpt F:MSSQLLOGECAP_DbBkUp.rpt
This database is a 7 x 24 database. What is the least intrusive and/or best way to correct this problem?
Jim
View 1 Replies
View Related
Jan 26, 2005
When I run the following query with a UNION:
SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCONLINETOOL.AMOUNT as PRICE, 15 AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCONLINETOOL.InYear, ITPSG.TBLCCONLINETOOL.InMonth, 'TRADITIONAL BOOKING' AS Reason FROM ITPSG.TBLCCONLINETOOL INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCONLINETOOL.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCONLINETOOL.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038' AND INYEAR = '2004'AND InMonth = 'OCTOBER' AND (ITPSG.TBLCCONLINETOOL.DESTINATION = 'DOMESTIC') AND (ITPSG.TBLCCONLINETOOL.TYPE = 'TRADITIONAL') UNION SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCEXCEPTIONS.PRICE as PRICE, ITPSG.TBLCCEXCEPTIONS.Lostsavings AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCEXCEPTIONS.InYear, ITPSG.TBLCCEXCEPTIONS.InMonth, ITPSG.TBLCCEXCEPTIONS.Reason FROM ITPSG.TBLCCEXCEPTIONS INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCEXCEPTIONS.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCEXCEPTIONS.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE (LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038') AND (ITPSG.TBLCCEXCEPTIONS.InYear = '2004') AND (ITPSG.TBLCCEXCEPTIONS.InMonth = 'OCTOBER') ORDER BY Reason
It returns these 16 records, 10 from the 1st table and 6 from the second. If I just remove the UNION operator and run them seperatly I get 11 from the 1st table and 6 from the second.
The record I am losing is the second of these two, but with the fields I am selecting they appear identical:
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING
Is there any reason why the UNION statement is making that second record vanish? Is there a way I can alter the statement so I can run the query with the UNION and not lose records?
Thanks,
View 2 Replies
View Related
Sep 18, 2006
Hi There,
Good Day :-)
How could I correct the erroneous value on the property window of an SQL Table.
My problem is that, if I am going to display the property window of Table1, the 'Rows' information displays 115. However, if I am going to execute - Select Count(*) from Table1 then it returns 117. How could I fix this glitch?
Please Help :-)
View 2 Replies
View Related
Dec 8, 2005
Guys
Here's a strange one - anyone else come across this :
In SQL2K
select cast(cast ('08-dec-2005' as datetime ) as int) returns 38692
In Excel2002 SP3
Put the same date in a cell and format it as a number and you get 38694
Presumably the products have a different start date
View 1 Replies
View Related
Jun 23, 2015
After performing copy_only backup of the Database using the below query, I restored the Database and now I checked and found out that there is a discrepancy in the table structure of the restored Database.Its a scheduled backup job.one of the column of the table varchar(300) has been changed to varchar(200)
BACKUP DATABASE DBname TO Â DISK = N'D:BACKUPdbname.bak' WITH Â COPY_ONLY, NOFORMAT, NOINIT, Â NAME = N'Database Backup', SKIP, NOREWIND, NOUNLOAD, Â STATS = 10
GO
View 7 Replies
View Related
Jul 20, 2005
I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?
View 3 Replies
View Related
Mar 28, 2006
Hi Guys,
I have a website with multiple pages and when a user signs in it puts a 1 in the database and their online status is shown to the rest of the members, if they use my signout button which I have on each page, it signs them out and updates the database with a 0 in the process, so shows them as then offline, my problem is some people forget to sign out and just close the browser window or begin surfing elsewhere.
Is there a way to have sql server 2000 automatically change the field in the database after 30 minutes of inactivity.
Or is there a way to automatically sign users out as they either click on the X in the browser window, or as they leave my site.
Any help would be appreciated.
Cheers
View 4 Replies
View Related
Jun 21, 2008
Hello,
Is there any built in function to find out the sign of a value.
For Ex: -225.89 Sign is -
566.987 Sign is +
Thanks
Ganesh Kumar
Solutions are easy. Understanding the problem, now, that's the hard part
View 2 Replies
View Related
Jan 6, 2006
I typed the following query
ALTER TABLE [konnik].[PORTAL_CONFIG]
ADD CONSTRAINT [DF_PORTAL_CONFIG_AUTOPM_ON]
DEFAULT (1) FOR [AUTOPM_ON]
and i received the following warning
Warning: The table 'PORTAL_CONFIG' has been created but its maximum row size (12068) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Bear in mind that this table come from Access through the Dts. Could anyone translate this for me? How can i overcome this???
View 10 Replies
View Related
Jan 19, 2007
i found one tutorial on self signing assemblies for use in sql server, but it appears to have errors. does anyone know of a better tutorial on this topic? the site im currently looking at is here: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
View 1 Replies
View Related
Jun 2, 2007
Hello:
I need to change minus brackets () with - sign in SSRS. How i can do this?
Thanks
Amit
View 11 Replies
View Related
Aug 17, 2007
I have a field in table A named tbl_dob.It is date of birth of member. I want to find zodic sign from this tbl_dob.How I get it.Please help me.
View 1 Replies
View Related
Sep 17, 2004
Hi
I have come across a bit of problem with my Web Form. I have standard textbox which inserts a value into a SQL 2000 database. However if I put a pound sign anywhere inside the text box and do the insert it disappears when I try and reshow the value.
I have checked the database field to see if it has been inputted but its no where to be seen.
So what has happened to my pound sign? any ideas?
I am using VS.Net 2003.
Thanks in advance
View 12 Replies
View Related
May 2, 2001
Does anyone know to include the Euro sign in SQL statements. I found an Ascii
code of ALt+0128, but then I use CHAR (0128) it actually prints the Char (128) which is Ç not €.
When I use the € directly in the code the application just shows a ?
Any help would be highly appreciated.
Markus
View 2 Replies
View Related
Nov 30, 2006
Code:
objrs.Open "Select * from [klcc_f$]", objConn, adOpenStatic
why IM getting dollar sign is not a valid name ?
View 2 Replies
View Related
Dec 24, 2013
I am trying to use this command to change the variable in where clause each time:
psexec servername -E cmd.EXE /c "sqlcmd -S servername /E -d dbname -v entertext="'%anything%'" -i c:myfoldermyscript.sql -o c:mypathoutput.sql"
myscript.sql is like:
select * from table_name
where summary like $(entertext);
This command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary.In fact, I wanted to write something which the user be able to enter variable each time when he/she run this batch file. because I put this sqlcmd command in batch file( like this in oracle: "select * from table_name where summary = &entertext; " I was looking the same command in sql server , but I did not find it). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.
View 3 Replies
View Related
Apr 14, 2015
I have a query where I have to use the convert function to return the unitprice as a decimal with 2 digits to the right of the decimal and a $ to the left of the number like 10 should be $10.0o. How do i insert the $. this is my code so far:
SELECT CONVERT(varchar, UnitPrice, 1) AS varchartotal
FROM dbo.[Order Details]
View 2 Replies
View Related
Sep 3, 2007
I'm trying to import a text file which contains dates and decimal values. My problem is that the date has the following format "YYYY-MM-DD" and the decimal sign for the values are "." (dot).
If I change the "Locale" for the file source so it will handle the "." signs in the values then the dates are invalid, and if I have it so the dates are handled then the "."s aren't seen as decimals.
Is it posible to handle this problem in SSIS
Thanks
View 1 Replies
View Related
Aug 13, 2007
In Crystal Reports, there was a "reverse sign for display" property that could be checked for a number box. This was used for debits and credits (and such) so a negative number would be displayed as a positive and a positive displayed as a negative.
How would this work, or is there something simple, in Reporting Services?
Thanks for the information.
View 5 Replies
View Related
Apr 26, 2006
Just lately I have noticed that I am periodically getting signed out of this forum. When I try and sign back in I get directed to an error page saying:
We apologize, but an unknown error has occured in the forums.
This error has been logged.
I would say this has been happening for a couple of weeks now at least.
Has anyone else expereienced this?
Thanks
Jamie
View 1 Replies
View Related
Dec 30, 2007
Hi,
I did all the step mentioned in this MSFT article http://msdn2.microsoft.com/en-us/library/ms137947.aspx Yet I can not sign a package using a certificate.
To sign a package
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want to work with.
In Solution Explorer, double-click the package to open it.
In SSIS Designer, on the SSIS menu, click Digital Signing.
In the Digital Signing dialog box, click Sign.
The Select Certificate dialog box, select a certificate.
Optionally, click View Certificate to view certificate information.
Click OK to close the Select a Certificate dialog box.
Click OK to close the Digital Signing dialog box.
To save the updated package, click Save Selected Items on the File menu.
I don't see a certificate to select after following the article's steps. Can some one please shed some light on this?
Thanks,
Saptagiri Tangudu
View 5 Replies
View Related
Nov 15, 2006
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?
View 2 Replies
View Related
May 24, 2004
Hi,
I have imported a table from Oracle to SQL server using the tool in enterprise manager.
Some of the columns were numeric and had negative values.
In Query analyzer these numbers show up as positive. But when i add a where clause and only ask for the values < 0 the same numbers show up bit still without a negative sign.
Does anyone have an explanation for this?
(could have found some help if the search function was working....)
Workaround?
Thanks,
YakoBay
View 2 Replies
View Related
Jun 29, 2015
I have a SSAS 2012 Tabular Cube and I am now stuck with a problem concerning something recursive in DAX.Basically I am looking for number of zones jump, provided the sign of the numbers are reverse.E.g. in the figure below. Number of zones jump for the figure -47,067 is 1, since there is a positive value in zone 2. If the Figure in Zone2 had been 0 or a negative number and Zone 3 had the same (i.e. 0 or a negative number) and Zone 4 had a positive number, the number of Zones jump would be 3. Is this something that could be done in DAX?Â
View 3 Replies
View Related
Jan 28, 2008
Hi
I€™m having trouble again with a matrix report.
I wish to make a matrix report with 5 columns. The right part of course will be filled depending on how many moths the user has chosen. The left part will be field with country, city and then the person names and some other info.
What I want to do is to have the first 2 columns, country and city, with drilldown. That is pushing the plus sign to open the corresponding group. How do I do this?
Thanks in advanced
Kind Regards
View 4 Replies
View Related
Jul 18, 2002
hi and thanks for your help. Is there such a code that return whether or not a field is either positive or negative. If I have a field that contain numeric value. lets say -500
can I run a code that check the sign ?
thanks for your help
Ali
View 2 Replies
View Related
Feb 24, 2014
How to implement the display of a dollar sign for an output in a query...
View 5 Replies
View Related
Jul 20, 2005
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
View 1 Replies
View Related
Jul 12, 2006
I have 2 xp machines one has the SQL EXPRESS Server with all the goodies on it and the other I just install the client stuff. I am using VB 6 to program with using the ADO. It works great on the server machine. The software pulls up great. I can't seem to get it to work on the client machine. I get "Login failed for user 'TIGGERLGuest'. I found the places to set the TCPIP setings on the server SQL Express to allow this even turned on the browser but nothing same error. I am using the same login on both machines and am pointed to the same workgroup. I used window authentication when I set them up. Not sure why it insists on using Guest.
This is a copy of the connection string I am using
"Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName="c:mpsdbmpswarehouse.mdf;Database=MPSDB;Data Source=tiggerlsqlexpress;Trusted_Connection=yes;"
Not sure if this is okay for connecting remote. Any help would be greatly appreciated
Rick
View 3 Replies
View Related
