Sp_prepexec And Compatiblity Mode 7: Float Variables Comparing To String Field Fails
Jul 20, 2005
I have a statement that works in compatibility mode 8 of SQL Server
2000 while it fails in 7:
declare @P3 int
exec sp_prepexec @P3 output, N'@P1 float', N'select custname from
customer where custnr = @P1', 12600034
custnr is a varchar(15). As long as the float p1 is less than
1,000,000 the query returns correct results, but as soon as p1 is
equal or greater than 1,000,000 the resultset is empty. I could
figure, that above 1 million, sp_exec converts the float into
exponential format and compares this format to the string containing
fixed format values. As said above, in mode 8 the statement returns
correct results above 1,000,000.
Please don't ask me, why we use float variables - I don't have any
influence on the statement - we isolated the statement with the
profiler.
We are also currently dependent on the compatibility mode 7.
I would be very grateful if you have any ideas, help or other issues
for me.
Thank you,
Peter
Hi,we found some strange behavior when operating with floats and round().The following simplified statement reproduces the problem.select 6.56 - round(convert(float, 6.56), 2)from sysusers where name = 'public';===========-8.88178419In "real world", the floats are not literals, but float fields in tables.I could figure, that 6.56 - 6.56 will result in something like 0.00001,but -8.88178419 is beyond my imagination.The same statement without round results in 0.Has anyone any clue on this?Regards,Peter
Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure
are the variables i am populating Mapping them to result set in execute sql task.
This is the criteria for script task to Fail or success
I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.
I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script. End IF
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
I have a table with three columns: UniqID, Latitude, and Longitude.
I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.
I have deployed a project with multiple packages to SSIS 2012 db. I am able to configure the project parameters fine. But, I am not able to replace the package variable values with the 'Environment' variables.
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem? Try Dim i As Integer For i = 0 To arraySql.Count - 1 myCommand = New SqlCommand Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTrans With myCommand.Parameters
End With myCommand.ExecuteNonQuery() Next myTrans.Commit() myConnection.Close() Catch ex As Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close() End Try
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT )
ASSELECT * FROM ( SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
Hi, I have a varchar field named FinancialYTDCode containing data in the format 2007F or 2008F. I want to only select the rows with the FinancialYTDCode that is the same as the current year.
Could someone please show me how I write this in my script. Thanks
SELECT * FROM PABX INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE) AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3) LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE) WHERE pabx.COD_cliente = 224 and SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7') AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2) AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE AND PABX.CLASSIFICA IS NULL AND PABX.LOCALIDADE <> AUXILIAR.DESTINO AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)
But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it ?
PS: Both auxiliar.destino and pabx.localidade is varchar(255)
I have an excel spreadsheet that only has email addresses in a single columnar format on it (318 emails). I want to check and see if any of those emails are in the database. Is there a easier way than having to enter 300+ "OR" statements?
SELECT "Name"."FIRST_NAME", "Name"."LAST_NAME", "Name"."EMAIL", "Name"."ID", "Name"."MEMBER_TYPE" FROM "APSCU_PROD"."dbo"."Name" "Name" WHERE Name.EMAIL='marie@bahoo.com' OR Name.EMAIL='markg@ts.com' OR Name.EMAIL='mare@t.edu'
Hi, I run 2 steps viz with NORECOVERY and with RECOVERY in sequence as SQP jobs. The 2nd step fails some times. When it starts failing, I run full backup of the production server again and the 2nd step starts succeding. Unless I run the full backup the 2nd step keeps on failing.
What are the conditions which generate for [SQLSTATE 42000] (Error 3013) and how to tackle this problem.?
The 2 steps are :- 1.RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH Replace , MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , NORECOVERY GO
2.RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionSQL daily diff back up' WITH Recovery
The status of the 2 steps is : 1 Executed as user: BPOTESTREMEDY emedybackup. Processed 104480 pages for database 'ARSYSTEM', file 'ARSystem' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'ARSYSTEM', file 'ARSystem_log' on file 1. [SQLSTATE 01000] (Message 4035) RESTORE DATABASE successfully processed 104481 pages in 49.452 seconds (17.307 MB/sec). [SQLSTATE 01000] (Message 3014)
2. Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
SELECT DSNew, DTTM, RQDT FROM dbo.Feb INNER JOIN DMSEFL ON ACTR = DSNew where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20)) If I run the above query I get zero recs back.
If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')
Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.
I'm running a profiler trace and I'm finding that sP_prepexec is running over 6 seconds everytime.
I looked at the SQL profiler and found out that the ODBC driver generates the update statements using sp_prepexec, I tried to run the update(without sp_prepexec) in Query analyzer, it returns the results right away.
Is there any known problems with ODBC driver about this or sp_prepexec related problems, if yes, how can I work around this.
Using the "set values" tab, I am trying to set the values of package variables to strings that contain spaces and/or dashes. If I run the package using the integrations services server, it runs perfectly. If I schedule the package on SQL Server agent, I get the following error.
"Executed as user: GBPSQLServer. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "-restofstring" is not valid. The command line parameters are invalid. The step failed."
Looking at the command line generated by the SQL Agent interface, the value is quoted using double quotes like this:
/SQL "PackageName" /SERVER SQL1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "Package.Variables[LogFolder].Value";"\remoteservernameinetpub he-restofstring estofstring" /REPORTING E
It seems that the command line parser is not recognising the quotes, or maybe the slashes are throwing it off, or sql server agent is doing something funny to the command line before it executes it.
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
We are having problems with those two procedures, they are blocking other processes. I have seen sp_prepexec block another process for 5 hours, beforeI finally killed the process and the blocked process executed. Has anyone experienced behavior like this? What else can I do to troubleshoot this and determine the issue that is causing this? We could really use some assistance to determine the root of the problem.
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
When I installed SQL Server 7.0 on Windows 2000 Server, I got "File Protection Error" saying Windows 2000 system files are being updated by other version.... to restore the Windows 2000 files insert CD.
The same error I received when i tried to install SP3 of SQL Server 7.0, is there any compatibility issues? I canceled the above error and the Server works well after installation.
The following was captured from SQL Profiler during an interaction betweenAccess Xp and SQL Server 2000:declare @P1 intset @P1=1exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT"totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENID","FromFPW","prevdown","tempifpssysid","upsize_ts"FROM "dbo"."Family"WHERE "totalid" = @P1', N'-:1044463096'select @P1goI think I understand that sp_prepexec prepares a parameterized query with@P1 as a variable...but sp_prepexec is not in Books Online...could someone please explain the syntax of sp_prepexec?why is @P1 used for both the parameter within the select query and the nameof the prepared statement?the other seemingly strange thing, is that when I run the above in QueryAnalyzer and try to use the parameterized query like soexec sp_execute 1, N'-:1044463096'goI receive the error messageCould not find prepared statement with handle 1
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT, @BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125, @BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original], CAST(@SomeNumber AS BINARY(8)) AS [Binary], dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted], @SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float ( @BinaryFloat BINARY(8) ) RETURNS FLOAT AS BEGIN DECLARE@Part TINYINT, @PartValue TINYINT, @Mask TINYINT, @Mantissa FLOAT, @Exponent SMALLINT, @Bit TINYINT, @Ln2 FLOAT, @BigValue BIGINT
WHILE @Part <= 8 BEGIN SELECT@Part = @Part + 1, @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT), @Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0 BEGIN IF @PartValue & @Mask > 0 SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1, @Mask = @Mask / 2 END END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023) END Thanks again Jeff!
We have an existing project developed using VS 2003 with SQL server 2000.
Now planning to switch to SQL server 2005.
Are there any compatibility issues between VS 2003 and SQL server 2005, Plus sql server reporting services 2005.
We have been developing this project over a year and don't want to mess it. Now we are at the stage of developing reports and finidng lot of problems with sql server 2000 reporting services.
I'm relatively new to SQL Server. I have spent some time in Enterprise Manager for our SQL Server 2000 instances. We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.
