SQL Server 2K Problem With Fractional Real Values
Jul 23, 2005
I have a field of type Real in my SQL 2K database. I stored a value of
..35 in the field.
When I "Open Table"->"Return All Rows" in the Enterprise Manager I get
back .35 for the field value.
I went to The SQL Query Analyzer and executed the following T-SQL:
SELECT field
FROM table
I got back 0.34999999 for the field.
When running stored procs against the field I also get back 0.34999999.
This is causing problems in my app. I can use the Round T-SQL
statement to get back the value I expect, but this causes app
development problems. For a goof I put 1.35 in the field and T-SQL did
return 1.35. This problem only seems to occur with 0.nnn values. I
also tried a float data type for the field but I had the same problems
I had with real.
Why is T-SQL returning 0.34999999 for my field?
View 1 Replies
ADVERTISEMENT
Jan 21, 2008
I have some Sp that return real values, I''m using function like SELECT CONVERT(REAL,field) FROM .... to ensure that conversion and rounding is done.
When I got the values in my application (Datagrid) I receive values having even 9 decimals
How can efectively return only 2 decimals from my query?
Thanks
View 3 Replies
View Related
Apr 29, 2004
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle
View 2 Replies
View Related
Dec 18, 2007
Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.Have a DateModified field which previously was smalldatetime. Changed overthe weekend to datetime. Field is updated with a trigger that sets value toGetDate().One record in the table now cannot be updated. In Access, get message:[microsoft][odbc sql server driver]fractional truncation (#0)[microsoft][odbc sql server driver]timeout expired (#0)In QA (using Update), no message; just hangs.Note that many records in the table have been modified since the change, andthere doesn't seem to be a problem. It just seems to be this one record(possible others that we haven't come across yet) that will not allowupdates.Any ideas how to resolve this?Thanks!Neil
View 4 Replies
View Related
Nov 17, 2005
/*
Format strings for fractional Numbers and Currency values
Return Values: VARCHAR
Parameters: @n Specifies numeric expression to format.
@sFormat: Specifies one or more format codes that determine how the expression is formatted.
The following table lists the available format codes.
9.000000|00 |09
12.100000|### |12
12345.120000|### ### ###.000|12 345.120
12345.120000|### ### ###.###|12 345.12
12345.120000|$ ### ### ###.000|$ 12 345.120
12345.120000|### ### ###.### $|12 345.12 $
12345.120000|$ ###,###,###.000|$ 12,345.120
12345.120000|### ### ###.000|12 345.120
12345.120000| |12345
1.120000|### ### ###.000|1.120
12.120000|### ### ###.000|12.120
123.120000|### ### ###.000|123.120
1234.120000|### ### ###.000|1 234.120
12345.120000|### ### ###.000|12 345.120
123456.120000|### ### ###.000|123 456.120
1234567.120000|### ### ###.000|1 234 567.120
12345678.120000|### ### ###.000|12 345 678.120
123456789.120000|### ### ###.000|123 456 789.120
1234567890.120000|### ### ###.000|1234 567 890.120
12345678901.120000|### ### ###.000|12345 678 901.120
123456789012.120000|### ### ###.000|123456 789 012.120
*/
CREATE FUNCTION xNumberFormat(@n NUMERIC(38, 4), @sFormat VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN
DECLARE @sRet VARCHAR(255), @i TINYINT, @j INT, @nDec TINYINT, @sNumber VARCHAR(255), @cF CHAR(1), @cR CHAR(1), @sE VARCHAR(255), @sX VARCHAR(255)
SELECT @sE = '', @i = LEN(@sFormat)
WHILE @i > 0 AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sE = SUBSTRING(@sFormat, @i, 1) + @sE, @i = @i -1
SELECT @sFormat = LEFT(@sFormat, @i), @sX = '', @i = 1
WHILE @i < LEN(@sFormat) AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sX = @sX + SUBSTRING(@sFormat, @i, 1), @i = @i +1
SELECT @sFormat = RIGHT(@sFormat, LEN(@sFormat) - @i + 1)
IF @n = 0 AND CHARINDEX('0', @sFormat) = 0 AND @sE = '' AND @sX = '' RETURN ''
SET @nDec = CHARINDEX('.', @sFormat)
IF @nDec > 0 SET @nDec = LEN(@sFormat) - @nDec
SET @sNumber = RTRIM(LTRIM(STR(@n, 255, @nDec)))
IF @nDec > 0 SET @nDec = @nDec + 1
SET @sRet = RIGHT(@sNumber, @nDec)
IF @nDec > 0
BEGIN
SET @i = 1
WHILE RIGHT(@sRet, 1) = '0' AND SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1) = '#' SELECT @sRet = LEFT(@sRet, LEN(@sRet) - 1), @i = @i + 1
IF @sRet = '.' SET @sRet = ''
END
SELECT @i = @nDec + 1, @j = @nDec + 1
WHILE @i <= LEN(@sFormat) AND @j <= LEN(@sNumber)
BEGIN
SELECT @cF = SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1), @cR = SUBSTRING(@sNumber, LEN(@sNumber) - @j + 1, 1)
IF @cF NOT IN ('#', '0')
IF @j = LEN(@sNumber) AND @n < 0 SET @i = @i + 1 ELSE SELECT @sRet = @cF + @sRet, @i = @i + 1
ELSE
SELECT @sRet = @cR + @sRet, @i = @i + 1, @j = @j +1
END
IF @j <= LEN(@sNumber) SET @sRet = LEFT(@sNumber, LEN(@sNumber) - @j + 1) + @sRet
WHILE @i <= LEN(@sFormat) AND SUBSTRING(@sFormat, @i - @j + 1 , 1) = '0' SELECT @sRet = '0' + @sRet, @i = @i + 1
RETURN @sX + @sRet + @sE
END
View 4 Replies
View Related
Jul 20, 2005
I am about to buy some database hardware, and am considering buying adual 64-bit opteron server. In the past, we have run been running the32-bit version of SQL Server 2000.Has anybody tried the new 64-bit version of Sql Server 2000? Can weassume that something that works in the 32-bit world will work in the64 bit, or are we going to spend time debugging Microsoft code? Also,which flavor of 64-bit windows operating system did you use?Any experiences you want to share are much appreciated...-Cheers,Richard
View 2 Replies
View Related
Apr 12, 2007
Hi,
We currently have a propriety in memory DB that is used to store the latest transactions in the system and we have a service that copies the data to a SQL Server every couple of seconds - For historical reporting purpose.
We would like to move into a more standard DB as our real time DB, since we have scalability and availability issues. We taught about using SQL Server since this is the DB we know, but I'm not sure it's built to handle real time data.
Does someone has any experience in using SQL Server for "Real Time" applications?
Does someone has any experience in storing the data files on RAM?
Does MS has a solution similar to Oracle's TimesTen, which is their real time DB?
Thanks,
Avi G..
View 3 Replies
View Related
Oct 28, 2005
This Q is so easy I can't find the answer anywhere!
Why is SQL Server called 'Server' ? I understand it is a lot more capable and robust than Access but where does 'server' come into it.
I currently use ASP and Access for dynamic websites but it is time to move up a notch.
Do I just buy SQL Server, make a database, upload it to the same place as before and hey presto?
can I run lots of websites (on different domains and servers) from databases created with my single license standard edition?
Thanks
M
View 5 Replies
View Related
Jan 22, 2008
I am having a problem connecting to my SQL Server 2005 database at the same time with SQL Management Studio Express and from the website at the same time.
Everytime I want to view any pages in my site that access the database, I have to close management studio and restart the server for some reason or i get a failed login error message.
Is it NOT possible to work on the database at the same time as viewing pages in the website that access the database?
This freaking error is realy starting to bug me.
I sure hope that there is a work around or something for this.
View 7 Replies
View Related
Sep 19, 2006
I saw a presentation last week where the speaker created some sorta sql server "watch window" (in Sql Server Management Studio I think) where he could watch all the commands being executed on his sql server database in real-time. For example he could navigate to web pages (that hit the database) and as he pressed buttons you could see the sql commands execute in this "watch window." If other users hit the database at the same time you could see those sql queries execute as well. I didn't think at the time to ask how he did it - does anyone know how to set this up? I have a problem with my sql server right now and it would be useful to see which sql queries (etc) are being executed when. Thanks in advance,J. Shane Kunklejkunkle@vt.edu
View 2 Replies
View Related
Apr 11, 2006
Hell, I host web pages on a server(s) that I own, located in a datacenter.I am trying to figure out how to properly liscnese sql server 2005.I have called microsoft, and the people i talk to dont seem to understand my situation. They keep asking me about the number of employess i have, which is totally irrelevant. Let me give you a basic example.Example 1.My personal web page has a blog on it. The blog data is stored in a SQL database. Its a popular blog, thousands of anonymous people reading my blog every month via my webpage. The only "thing" accessing the SQL databse is an asp.net script i write, which then turns around and presents the data via html over the www. just like every other blog in the universe. Example 2.I sell artwork over the internet via my web page. My web page uses a shopping cart system which makes use of an SQL database to keep track of inventory and orders etc. No one ever tries to connect to my database, they just use the shopping cart on my web page which of course connects to the database.Now according to the MS liscensing documentation it seems that i can buy sql server w/ 5 device CAL's. Since I only have 1 or 2 webservers accessing the database server, that should be no problem right?So my basic question is this: is a single Device CAL enough to allow one webserver to connect to one sql server and then show dynamic content to thousands of anonymous users (whoever happens to visit my sites?).Thank you very much for any help you can provide.
View 1 Replies
View Related
Oct 7, 2006
When I first installed MS SQL Server 2005 Express Editon, I didn't I selected Windows Auth instead of mixed mode, so I uninstalled sql server and then attempted to reinstall. On the earlier installtion I did setup to instances which show in the "Control Panel/admin tool/services". Anyway, I used the unistall through the control panel, but on installing again it told me that I had instances aready running and needed to stop them. Couldn't work out how to do that. So after many different things, including using the MS's removal tool and windows installer clean up, I still failed. Now however I get the following error message:
Setup failed because Service MSSQL$SQLExpress is disabled for the current hardware profile. Services must be set with the current Hardware Profile logon property profile enabled. For more information on how to enable the hardware profile logon property refer to the product documentation.
Can someone help?
Cheers
Dave
View 3 Replies
View Related
Nov 22, 2007
So for the last week of my life, literally, I have been trying to import excel files, text files, etc through the bcp utility, link server and also trying bulk insert....I am really, really tired of this not working and I am starting to think there is a bug in express. I first thought these issues might have to do with Vista but I have even tried and failed with XP SP2. I am not new to SQL Server, nor am I a new developer - I have over five years experience so I am really pissed about it at this point! I know most professional developers use SQL Server 2005 Enterprise or something other than Express which is why I am probably not getting answers. btw, this is not the first time I have posted about these issues either. I have referenced multiple sources including forums, books on-line and I even resorted to getting a SQL Server 2005 EXPRESS book for dummies! I have enabled remote connections for both named pipes and tcp/ip. I have started the sql browser service. I have given the correct permissions to the file I want to import and to the table I want to import the data to. The file contains the int values 1,2,3,4,5,6,7,8,9 --> I have even tried saving this file as a .csv -- thanks a whole bunch sql server 2005 dev team for getting rid of the import/export wizard found in 2000, arrrrrgh!
BULK INSERT TestDB.dbo.tblTestData
FROM 'C:datamyData.txt' WITH (DATAFILETYPE = 'char', FIELDTERMINATOR =',');
GO
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (columnValue).
Can ANYONE give me a real answer on h ow I can get this to actually work?
-Brian
View 3 Replies
View Related
May 11, 2006
Hello all,my question might be trivial but since my background isn't DB i'll dareto ask it any way:how to create a real (not virtual) table from view1 & view2 ni sqlserver?what should i do specificly in the sql server application & what is thesyntax for that?i thank all of you,groupy.
View 2 Replies
View Related
Jul 27, 2007
Hello Everyone,
Does SQL Server 2005 has a counterpart to Oracle 10G's Real Application Clusters (RAC)?
http://www.oracle.com/technology/products/database/clustering/index.html
Regards,
Joseph
View 3 Replies
View Related
Dec 13, 2007
I am not very good in queries. Could you please suggest me some web site/Tutorial/Artical where i can get Study Material for complex and real life queries. I know the syntexes, I just need to practice queries to enhance my skills
View 1 Replies
View Related
Sep 26, 2006
Hi ,
I am running SQL Server Desktop Engine on Windows XP. I am developing in .NET framework v1.1.
I intend to u/g to the express edition 2005 however should this old version of the engine be OK (particularly considering that Windows updates are being often run)?
Main question.
For ages the database has worked well then one day recently (just after running a Windows update) the following error occurred SQL Server does not exist or access denied .
I followed my code and this is the result of an exception being thrown in my code due to one of two obvious possibilities.
Everything looks fine as far as SQL Server DEsktop Engine (including services and TCP/IP protocols. Yes I have check cliconf.exe and svrnetcn.exe !!)
I specified mixed mode access (at setup) and In .NET my connection string is :
ConnectionString = "Persist Security Info=false;IntegratedSecurity=sspi;Database=ShedDb;server=warehouse1";
I notice that when I try to login to the database engine with OSQL it does not recognise the original sa password??
Somehow this has been changed (I definitely have not)
Q1. Is it possible that a virus (Worm) could have done this?
Q2. I think that I am stuffed(sorry) because now the sa has changed I cannot perform any admin functions (like resetting the sa pw) Is there any way to change the sa password in this sitatuon?
Q3. The database is locally setup on my development system. When I use the above mentioned connection string I assume that because I do not mention a userid and password that database access is being achieved via Windows authentication (I did specify mixed mode at setup) Is this correct?
Q4. I can appreciate the severity of the sa password being altered. However if Windows authentication is being used by my application, why should this matter? How does Windows authentication work?
Q5. I do have a backup of all the databases including master, model, temp... (I think that the sa password is stored in master). Would the crude approach of simply copying the backup of these backup database files (like master.mdf and master.ldf) suffice in fixing this problem?
Any help would be appreciated.
Thanks
Andrew.
View 8 Replies
View Related
Mar 25, 2008
Hi List
Im trying to set up an implementation of Project Real --it works like this-
Create two system environment variables called REAL_Root_Dir and
REAL_Configuration with the values given below. Click on
Start -> Control Panel -> System. Go to the Advanced Panel, click Environment Variables button, then New in the System variables box.
If the Project REAL files were installed at C:Microsoft Project REAL, then the variable values will be:
Variable Name: REAL_Root_Dir
Variable Value: C:Microsoft Project REALETL
Variable Name: REAL_Configuration
Variable Value: %REAL_Root_Dir%REAL_Config.dtsconfig
The package OLEDB connections work like this
First read enviroment variable to get location of config file
Next read Config File to get connection string for Config Database
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[SQL - Configuration].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=(local);Initial Catalog=DataWarehouseABC;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Next read Config database to get connection strings for Source and Destination databases
Destination database is called "DataWarehouseABC"
Source database is called "SnapshotABC"
the Source database OLEDB connection works 100%
however the destination OLDB connection we get this error below
PS--Both source and destination databases are on the same development machine , however both databases are restored bak files from another production machine
Error 1 Error loading LoadGroup_Daily.dtsx: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'xxxxxx'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "DataWarehouseABC" requested by the login. The login failed.".
Any ideas on how one OLEDB Connection in this package can get this corruption
thanks in advance
Dave
View 5 Replies
View Related
Feb 13, 2015
I have a SSAS stored procedure with a signature:
public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}
with the measures being real or query-scoped calculated members.
To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.
My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.
However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.
If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?
View 0 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Jul 27, 2007
Hello Everyone,
Does SQL Server 2005 has a counterpart to Oracle 10G's Real Application Clusters (RAC)?
http://www.oracle.com/technology/products/database/clustering/index.html
Regards,
Joseph
View 1 Replies
View Related
Apr 18, 2007
Hi,when i create a new user in a .MDF file, the hour of creation in the field "createdate" of table "membership" is exactly 2 hours less than the time of my computer. Probably something to do with different hours between Europe and UK or ...How can i set the creation hour to exact the same hour of my computer?ThanksTartuffe
View 4 Replies
View Related
Nov 11, 2007
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
(
@StudioINT = NULL,
@Br1INT = NULL,
@Br2INT = NULL,
@Br3INT = NULL,
@Br4INT = NULL,
@OverBr4INT = NULL,
@CondoINT = NULL,
@ListingTypevarchar(10) = NULL,
@WindowAirINT = NULL,
@CentralACINT = NULL,
@BalconyDeckPatioINT = NULL,
@UseOfYardINT = NULL,
@DishwasherINT = NULL,
@WasherDryerINT = NULL,
@FireplaceINT = NULL,
@EIKINT = NULL,
@HardwoodFloorsINT = NULL,
@BroadbandNetINT = NULL,
@TVINT = NULL,
@ThermostatINT = NULL,
@LandlordNotPresentINT = NULL,
@SmokingINT = NULL,
@NoPetsAllowedINT = NULL,
@CatINT = NULL,
@MoreCatsINT = NULL,
@SmallDogINT = NULL,
@LargeDogsINT = NULL,
@DoorpersonINT = NULL,
@IngroundPoolINT = NULL,
@AboveGroundPoolINT = NULL,
@ElevatorINT = NULL,
@UseOfGarageINT = NULL,
@LaundryFacilitiesINT = NULL,
@HealthCenterINT = NULL,
@StorageAreasINT = NULL,
@WheelchairAccessINT = NULL,
@BusinessCentersINT = NULL,
@RentChargeMinINT = NULL,
@RentChargeMaxINT = NULL,
@DebugBIT = 1
)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
SELECT
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROMdb_REListings as r
INNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE1 = 1
'
IF @Studio IS NOT NULL
SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio)
IF @Br1 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1)
IF @Br2 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2)
IF @Br3 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3)
IF @Br4 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4)
IF @OverBr4 IS NOT NULL
SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4)
IF @Condo IS NOT NULL
SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo)
IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType)
IF @WindowAir IS NOT NULL
SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir)
IF @CentralAC IS NOT NULL
SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC)
IF @BalconyDeckPatio IS NOT NULL
SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio)
IF @UseOfYard IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard)
IF @Dishwasher IS NOT NULL
SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher)
IF @WasherDryer IS NOT NULL
SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer)
IF @Fireplace IS NOT NULL
SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace)
IF @EIK IS NOT NULL
SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK)
IF @HardwoodFloors IS NOT NULL
SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors)
IF @BroadBandNet IS NOT NULL
SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet)
IF @TV IS NOT NULL
SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV)
IF @Thermostat IS NOT NULL
SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat)
IF @LandlordNotPresent IS NOT NULL
SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent)
IF @Smoking IS NOT NULL
SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking)
IF @NoPetsAllowed IS NOT NULL
SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed)
IF @Cat IS NOT NULL
SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat)
IF @MoreCats IS NOT NULL
SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats)
IF @SmallDog IS NOT NULL
SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog)
IF @LargeDogs IS NOT NULL
SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs)
IF @Doorperson IS NOT NULL
SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson)
IF @IngroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool)
IF @AboveGroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool)
IF @Elevator IS NOT NULL
SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator)
IF @UseOfGarage IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage)
IF @LaundryFacilities IS NOT NULL
SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities)
IF @HealthCenter IS NOT NULL
SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter)
IF @StorageAreas IS NOT NULL
SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas)
IF @WheelchairAccess IS NOT NULL
SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess)
IF @BusinessCenters IS NOT NULL
SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL
SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin)
IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @Debug = 1
PRINT @SQL
EXEC (@SQL)
View 5 Replies
View Related
Feb 14, 2001
Hi. I am trying to setup php4 to talk to MSSQL 7, but am having no luck. Both are on a Win2k server. I get the error below. Please can someone tell me what to do, or even a step by step guide to setting up php to connect to a MSSQL7 database. I originally created the database in access 2000 and now want to use MSSQL to use it. This is the error i get:
Warning: MS SQL message: Could not locate entry in sysdatabases for database 'customers'. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:Websiteewsiteestdb.php on line 13
Warning: MS SQL: Unable to select database: customers in C:Websiteewsiteestdb.php on line 13
Database unavailable
View 2 Replies
View Related
Oct 3, 2000
I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.
--Buddy
View 4 Replies
View Related
May 18, 2004
Hi,
I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.
The database forms the basis for a diary/calendar system for part-time employees and has two tables:
- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.
- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.
I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.
Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.
Any ideas?
View 8 Replies
View Related
Mar 5, 2007
Hi,I would like to convert real data type to datetime type. Example:I have a real data type which is: 23,613456 (23 hours and 0,613456). Iwould like to have it in hh:mm:ss format. How to do this? Can I useconvert/cas function?Thanks for helpRgdsMario
View 1 Replies
View Related
Jul 20, 2005
Timings... sometimes there are almost too many ways to do the same thing.The only significant findings I see from all the below timings is:1) Integer math is generally fastest, naturally. Bigint math isn't muchslower, for integers that all fit within an integer.2) Converting float to varchar is relatively slow, and should be avoided ifpossible. Converting from integer to varchar or varchar to int is severaltimes faster.3) Most significantly, and less obvious, CASE expr WHEN .... recomputes exprfor each WHEN condition, unfortunately, and is the same speed (or perhapsslightly slower) as listing WHEN expr = value for each condition. Perhaps anindexed computed column (somehow materialized) would be advisable whenpossible to avoid repeated computations in CASE..WHEN expressions (if thathelps..).Note that if you divide by COUNT(*), most timings below are below onemicrosecond per row, so this all may not be very significant in mostapplications, unless you do frequent aggregations of some sort.COUNT(*) FROM [my_sf_table] = 477446 rowsThe result from each query = either 47527 or 47527.0Platform: Athlon 2000 XP w/512MB RAM, table seems to be cached in RAM, SQL2000, all queries run at least 3 times and minimum timings shown (msec).SRP is a REAL (4 bytes)Fastest ones are near the end.CPU SQL(ms)-- Convert to varchar (implicitly) and compare right two digits-- (original version -- no I didn't write it)4546 select sum(case right(srp,2)when '99' then 1 when '49' then 1 else 0 end)from sf-- Use LIKE for a single comparison instead of two, much faster-- Note that the big speedup indicates that-- CASE expr WHEN y then a WHEN z then b .-- recalculates expr for each WHEN clause2023 select sum(case when srp like '%[49]9' then 1 else 0 end)from sf-- Floating point method of taking a modulus (lacking fmod/modf)2291 select sum(case round(srp - 100e*floor(srp*.01e),0)when 99 then 1 when 49 then 1 else 0 end)from sf-- Round to nearest 50 and compare with 491322 select sum(case round(srp-50e*floor(srp*.02e),0)when 49 then 1 else 0 end)from sf-- Divide by 49 by multiplying by (slightly larger than) 1e/49e811 select sum(floor((cast(srp as integer)%50)*2.04082E-2))from sf-- Integer approach without using CASE731 select sum(coalesce(nullif(sign(cast(srp asinteger)%50-48),-1),0))from sf-- My original integer approach651 select sum(case cast(srp as integer)%100when 99 then 1 when 49 then 1 else 0 end)from sf-- Modulus 50 integer approach without CASE481 select sum((cast(srp as integer)%50)/49)from sf-- Modulus 50 integer approach460 select sum(case cast(srp as integer)%50when 49 then 1 else 0 end)from sf-- bigint without CASE531 select sum((cast(srp as bigint)%50)/49)from sf-- bigint with CASE521 select sum(case cast(srp as bigint)%50when 49 then 1 else 0 end)from sf-- get SIGN to return -1 or 0, then add 1-- much better than the coalesce+nullif approach500 select sum(sign(cast(srp as integer)%50-49)+1)from sf-- SIGN with BIGINT551 select sum(sign(cast(srp as bigint)%50-49)+1)from sfBTW, I know srp should be int to begin with for this to be faster... Okay,so...select cast(srp as int) srp into sf from [my_real_sf_table]720 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf339 select sum(1+sign(srp%50-49)) from sf310 select sum(srp%50/49) from sf300 select sum(case srp%50 when 49 then 1 else 0 end) from sfWhat if it were a char(7)?select cast(cast(srp as integer) as char(7)) srp into sf2 from[my_sf_table]801 select sum(case right(rtrim(srp),2) when '49' then 1when '99' then 1 else 0 end) from sf2717 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2405 select sum(srp%50/49) from sf2391 select sum(case srp%50 when 49 then 1 else 0 end) from sf2How about varchar(7)?drop table sf2select cast(cast(srp as integer) as varchar(7)) srp into sf2 from[my_sf_table]581 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2569 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2LIKE is faster on VARCHAR than on CHAR columns...Apparently it has to effectively RTRIM the trailing spaces during the LIKEoperation.Is binary collation any faster?drop table sf2select cast(cast(srp as integer) as varchar(7))COLLATE Latin1_General_BIN srpinto sf2 from tbl_superfile561 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2530 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2Binary collation comparisons are slightly faster, though it's not a bigdifference (with just two characters being compared).662 select sum(case convert(binary(2),right(srp,2))when 0x3439 then 1 when 0x3939 then 1 else 0 end) from sf2-----------5037 select right(srp,2) srp,count(*) from my_sf_tablegroup by right(srp,2)order by right(srp,2)920 select cast(srp as int)%100 srp,count(*) from my_sf_tablegroup by cast(srp as int)%100order by cast(srp as int)%100---On the one hand, premature optimization can be a waste of time and energy.On the other hand, understanding performance implications of variousoperations can help write more efficient systems.In any case, an indexed computed column or one updated on a trigger couldvirtually eliminate the need for any of these calculations to be performed,except upon insertion or update, so maybe my comparisons aren't verymeaningful for most applications, considering we're talking about less than3 microseconds per row here worst-case.But the results remind me, some recommend avoiding Identity when it's notnecessary. I find Identity(int,1,1) to be a nice, compact surrogate key thatis useful for quick comparisons, grouping, etc, and so on. Also, it seemsmost appropriate as the primary key to all lookup tables in a star schema inOLAP data warehousing. (?) Of course, in some situations, it's notappropriate, particularly when having a surrogate key violates dataintegrity by allowing duplicates that would not be allowed with a properprimary key constraint, or when the surrogate key is completely redundantwith (especially a short) single-column unique key value that would be abetter selection as the primary key. With multi-column primary keys, I thinkit's sometimes convenient to have a surrogate Identity if only for INclauses that reference that identity column (though EXISTS can usuallyreplace those, so maybe that's a weak excuse for an extra column.)
View 4 Replies
View Related
Aug 22, 2007
Hello,
I define the sql-query:
SELECT myColumn FROM myPersonsTable WHERE myColumn=@FirstName
then I declare some SqlParameters like:
SqlParameter par=new SqlParameter();
par.ParameterName="@FirstName";
par.Value="John";
mySqlCommand.Parameters.Add(par);
Showing the mySqlCommand.CommandString it will look like:
(A) SELECT myColumn FROM myPersonsTable WHERE myColumn=@FirstName
Is this the real string that is send to the Database (among with some hidden string "John")?...
...or does the real string which is send to the Database look like:
(B) SELECT myColumn FROM myPersonsTable WHERE myColumn='John'
???
If so, where can I retrieve that last string (B)?
Or is (A) the way 'transact sql' operates?
Henk
View 3 Replies
View Related
Nov 8, 2006
Greetings,
I have a SQL Server 2000 database using Integrated Security. Users are added directly to the database from their domain name. In the Login section of the Security node in Enterprise Manager they show up as DomainNameUserName. I have four users that have the System Administrator database role. When I use the user function to get their user name they show up as dbo. Is there a way to get their actual domain user name?
Thanks.
View 3 Replies
View Related
Oct 23, 2006
A problem so simple I'm a little embarrassed to post it... I am setting up a trigger in which I want to, among other things, record the network ID of the user who performs the action that triggers the trigger. I know there is a variable or function out there to pull that information, but I can't find it, and have tried every variation of "login logname usr_id network_id....." I can find.
So first.... could somebody please tell me the variable or function that will return that piece of information. And second, if you also could point me to a good place, either in print or on line, to look up that kind of information, I would be most grateful.
Thanks.
View 2 Replies
View Related
Sep 1, 2006
I was hoping there would be a newbie section in here... But I'll post anyway (And sorry in advance if its a really dumb question)I am using VWDE and have a SQL DB which has been imported from one of my existing forums, when it shows in the database explorer its show like belowtblArcade (jdName)tblArcadePB (jdName)etc..etc...What I want to know is why this DB has (jdName) after the Table name and my other DB's shown don't?? They are just shown as tblArcade
tblArcadePB etc..etc.. My main questions are "How do I remove the brackets from the forum DB" as when I try to preview data in VWDE I get a message Invalid object name 'tblArcade'.I know its to do with this extra bit as it only happens to the tables that have this on the end... Hope that makes sense and someone can help me, if you could reply as if I'm really dumb it would be appreciated
View 4 Replies
View Related
