Hi all,
I have a table - customer with a Column state_code varchar(5) and another valid( smallint)
select count(1)
from customer
where state_code != 'U' -- for unknown
and valid= 0 -- for valid
Returns different counts each time, even when the data in the table is NOT Changing.
something with != operator ?
Thanks for your help !
Sri
Hi All, we got a problem that I hope you guys can help us with.
We are trying to extract data from a SQL database in XML format to a variable (string) using an 'Execute SQL Task'
In the task we are using an ADO.NET connection to the database which is fine and via direct input we are using this query.
SELECT
title,
initials,
surname,
username,
gender,
emailaddress,
addressline1,
addressline2,
addressline3,
town,
postcode,
country,
emailcontact,
postalcontact,
telephone
FROM
CRMCustRecords
FOR XML path('customer'), root('CRMData'), ELEMENTS XSINIL
In SSMS this is fine and returns an ok (to my eyes anyway!) XML resultset.
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxxx</surname>
<username>xxxxxx</username>
<gender>x</gender>
<emailaddress>xxxx@xxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxx</town>
<postcode>xxxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>99999999999</telephone>
</customer>
<customer>
</CRMData>
However the SSIS task (running exactly the same query) is returning
<ROOT>
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxx</surname>
<username>xxxxxxxx</username>
<gender>x</gender>
<emailaddress>xxxxx@xxxxxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxxxxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxxx</town>
<postcode>xxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>9999999999</telephone>
</customer>
</CRMData>
</ROOT>
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
Thanks for any and all input
Pat
Hi Guys, I am experiencing weird results
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.
Thank you,
Trudye
Hi all,
I was trying to run this query
strSQL = "select '<input type=text name='& id &'size=4 maxlength=4 value=>',product,description,moreinfo,media,License,unitc ost from newdeal where category='" & session("category") & "'"
Idea is the value of id to be fetched from the DB and substituted in the input box.It works well with MS Access but in SqlServer it is showing an error saying that data of varchar type cannot be converted to *******.Id is ******* data type in the DB .can anyone help me please
Thanks
Shyam
I'm writing a report for a survey application. There is a subscriber table, a question table, and an answer table. The subscriber table holds info like the name, address, etc of the publication's subscribers. The Question table holds the questions on the survey (ie, How many widgets do you own? What is your age? How many do you think you will purchase in the coming year?). The Answer table holds answers to each question (18-21, 25-50%, 20+, etc). I am forced to work within a very non-standard database application running on MS SQL Server 2000 that also uses a separate Relationships table, so to determine which answers go with which questions the relationships table will have a record linking a question to an answer (and vice versa). To determine how each subscriber answered the question the subscibers have a record in the relationships table linking them with specific answers (and vice versa). Both types of entries are in the same table. There are several questions that will include a note to "check all that apply" (you may get several widgets from the company). The report runs through each question, shows how many subscribers answered the question, and how many selected each answer as a percentage of the total for that question. It is nearly finished, I can run it on the test data for any date, but there is one special case I'm having trouble with. For one question, they want to know additionally how many people chose one possible answer, but no others. I can do it using simple SQL to get the list of the subscribers that chose the answer in question, and then loop through each one in vb and run another query that will tell me how many answers related to that question they selected. This works okay for the test data, but in production there are more than 100,000 subscribers, which means running 100.000+ individual querys. I know it's possible to do this in one (all I want is the final count), but the SQL for this is horrid. Any help appreciated.
View 3 Replies View RelatedHi all,
We have an asp.net2 website pointing at sql2005 (not service packed yet - it may be the cause, not sure - would like opinions first!).The website will run without problems, but randomly we get the following scenario:We have a function that takes a SP name and a sqlcommand with parameter information. It opens a connection and uses a dataadapter to fill a datatable, then closes the connection and returns. When the problem occurs, we get a sqltimeout on the line where it tries to fill the datatable. However, when the function is called again, the results from the first query seem to get populated into the datatable. Then when it gets called again, the previous results are again returned. Its like ADO sees the timeout (but sql doesn't), but then picks up the next available resultset from sql (which is the wrong one!).Hope the above makes sense....Cheers,James.
hey ppl, if there is any1 that can help me here i would apreciateerror line(probably)Comando = New SqlCommand("INSERT INTO Table1(name) VALUES('a')", Conexao)on Table1 i have those fieldsid(int, identity) e name(text)but when i execute this code i get the following error:Error on the XML processing: no element found(ps. the error wasnt on english so i made a poor translation but i think u can have a general ideia)(the error message untranslated)Erro no processamento de XML: nenhum elemento encontradoPosição: http://localhost/teste.aspxNúmero da linha 1, Coluna 1:anyway, the wierd part is that on the sqlserver the data is insertted normally.. so.. what is that error??any1 pls help me! im going nuts (ps. im still noob on asp.net/sqlserver so please be gentle ^.^)(ps2. sorry for the crap english but it isnt my native language :) )
View 3 Replies View RelatedIll try describe this as best I can, because this isnt something that would be done usually, I think.
------------
SELECT Items
FROM tblFood
WHERE Type = "Fruit"
RETURN
-------------
This would return...
Items
-----------
Apple
Orange
Pear
Strawberry
etc...
Would there be a way to create a new Column for each Item of the Select? eg..
Col1------Col2---------Col3----------Col4
Apple--Orange------Pear-----Strawberry
The amount of columns needs to be dynamic. If i were to add pineapple there needs to be 5 columns etc.
Sorry if this has been covered before but I wasnt sure on search criteria. Thanks in advance.
Hi all,
I'm having a real wierd problem with sql 7.0 (sp3)update statement.
here is my table structure
sku varchar(18)
minimumquantity varchar(7)
maximumquantity varchar(7)
usa varchar(20)
when I issue an update like this...
update sapproductprices
set usa= "8.25"
from sapproductprices s
where (s.sku= "VSE-ASAP-BB-100-S1")
it works!, but, when I change my update to this it does'nt work...I get a message which says "the command completed successfully" and no update happens
update sapproductprices
set usa= "8.25"
from sapproductprices s
where (s.sku= "VSE-ASAP-BB-100-S1"
and s.maximumquantity ="10")
Can someone shed some light on this one please...
thanks
uday
Hi all,
I'm having a real wierd problem with sql 7.0 (sp3)update statement.
here is my table structure
sku varchar(18)
minimumquantity varchar(7)
maximumquantity varchar(7)
usa varchar(20)
when I issue an update like this...
update sapproductprices
set usa= "8.25"
from sapproductprices s
where (s.sku= "VSE-ASAP-BB-100-S1")
it works!, but, when I change my update to this it does'nt work...I get a message which says "the command completed successfully" and no update happens
update sapproductprices
set usa= "8.25"
from sapproductprices s
where (s.sku= "VSE-ASAP-BB-100-S1"
and s.maximumquantity ="10")
Can someone shed some light on this one please...
thanks
uday
Hi,I can not find any reference or support for the following issue fromMicrosoft or on Google:When I open a table either to return data or try to use the design tools orif I try to create a new database diagram, I get a new result pane, diagrampane etc... but no result and no functionality within that pane. Thedatabase is working and I can run querys using MS Query. The problem seemsto be with MMC or Enterprise Manager.I am using SQL Server 7.623 Service Patch Level 4 or 6. I am not sure onthat point. This has been a stable well functioning system for years withno recent changes. So I am sure it is not a configuration issue.Thanks in advance for your help.PS; I don't want to seem rude, but I am not interested in buying a new"upgrade" to fix this so any MS adbots out there can just not bother withresponding to this post :)
View 1 Replies View Relatedhi all!
i have encountered an inconsistent error while creating packages programatically. i am trying to create an ExecuteSQL Task like this:
ExecuteSQLTask sqlTask = (ExecuteSQLTask)sqlTaskHost.InnerObject;
and somtimes it failes on this command with this error:
<Message>Error in generating package C:PROJECTSPOSTALBuildSSIS packagesMappingsMappingsSBSCR_52_OUT.dtsx</Message>
<InnerException>Type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask' in Assembly 'Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' is not marked as serializable.</InnerException>
<Data />
<Source>mscorlib</Source>
<HelpLink />
<StackTrace> at System.Runtime.Serialization.FormatterServices.InternalGetSerializableMembers(RuntimeType type)
at System.Runtime.Serialization.FormatterServices.GetSerializableMembers(Type type, StreamingContext context)
at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitMemberInfo()
at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitSerialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter)
at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.Serialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter)
at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)
at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)
at System.Runtime.Remoting.RemotingServices.MarshalToBuffer(Object o)
at Microsoft.SqlServer.Dts.Runtime.ObjectHostImpl.get_InnerObject()
at Microsoft.SqlServer.Dts.Runtime.TaskHost.get_InnerObject()
at InterfaceEngine.OutputInterface.CreateExcel() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 842
at InterfaceEngine.OutputInterface.GenerateOutputPackage() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 322</StackTrace>
<TargetSite />
what is this about? why does it happen , and why not always? the package generation is generated by a web service that is called from an asp.net 2.0 page .
thanks a lot!!!
Hi
I'm modifying a report from MS CRM. Sales Pipeline. Not the easiest one but still. I just got to the drilldown report and need to change some things in it. I tested the SQL code as I could in SQL Server Management Studio so that I don't have any misspellings (the report I'm modifying is on a remote location). Anyway, now that I copy my code to the SQL Server Business Intelligence Development Studio and to the dataset I'm modyfing I see that a chunk of code from the bottom is missing ... ??? ... is there a limit on how much I can type?!
I counted in Word that I have 33.176 characters and 878 lines of code. Is that to much?!
The original report has a dataset of 27.529 characters and 724 lines of SQL code.
Hi Friends,
I am struggling with creating a simple stored procedure, I want to take max(timekey) out in one variable @Val in a dynamic SQL statement as below in SP. When I create this SP, it creates successfully but when I run it I get error
Select @Val=MAX(TimeKey) FROM ABC
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Val".
Can someone help me in understanding this wierd behavior? And how to get rid of this. Table and SP scripts are as below
CREATE TABLE ABC (TimeKey int, Data_Val int)
CREATE Procedure MAXVal(@Table_Name Varchar(30))
AS
BEGIN
DECLARE @Val INT,
@SQL Varchar(100)
SET @SQL='Select @Val=MAX(TimeKey) FROM '+''+@Table_Name+''
EXECUTE (@SQL)
END
Thanks,
Kul
Hi,
I used two symbols (”¬€¬ and ”´) in my reporting services page and they appear fine in Internet Explorer. However, when I try to export the page to a PDF file, they appear as question marks.
Can someone tell me why or how to work around this?
Thanks in advance,
Steven
I am not sure which of the forums this question should be posted to (Engine or TSQL) so I have posted to both. Please forgive the cross-post.
The problem we are having is with the performance of a stored procedure. Normally, this specific proc will only take seconds to run, but if we at all modify the stored proc in any way, the proc will take at least 20 minutes to run (we don't really know when it will finish because we cut it off at 20 minutes).
When this happens, we can run the profiler and see that a lock and unlock is happening on each record that the query is returning. We are at a loss as to why this is happening.
The only way to fix it (get the proc run time back down to seconds) is to run the engine tuning advisor. We don't even need to apply any of the recommended changes, we just need to run it and the proc performance goes back down to mere seconds.
If we run the profiler again when the proc performs normally, we dont' see any locking going on.
We can consistenly repeat these steps.
Just so we aren't jumping the gun, we have re-written and disected the proc 8 ways to Sunday and it still does the same thing.
Anyone else see this and can offer any suggestions?
Hi,
When I try to run the asp.net application (C#) it is throwing the following exception.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
The problem is I don't have SQL Server 2005 installed in my system. And I am trying to connect a remote SQL 2000 server.
Adding to my confusion, the same code is working fine in another system.
Help please?
Yes, I am pulling my hair out on this one...................
PROBLEM:When importing from my .sql file, I loose many lines of info in my SQL table. I am using the SQL Query Analyzer to execute the file. The file contains 655,000 rows and 20 columns of data on it.
When I run the full file, the system tells me it does not have enough system resources to execute the request. I broke it down just to see if it could handle a smaller file.
When I create a table and have it insert 10,000 rows of info, it drops 88 lines of data.
When I do the same with 120,000 rows I loose 300 rows of data.
When I run it with 56,000 lines of data on the file, I loose 260 rows of data.
When I do just 1,000 rows or below it is perfect.
I have taken the chunks into Excel and verified the number of lines I was trying to put into the table, and everytime, it showed I had the amount of lines I thought I was trying to import.
When I did a query once the table was created to see how many lines it created, I was always short.
This is what my query consisted of (Let me know if this may be my issue)
SELECT COUNT(*) Col_1
FROM nfo_tablename
GO
I even tried zero column names
Any suggestions??? Am I doing something wrong here? Should I be executing the process in a different way?
Here is the basic idea of what the top part of my "CREATE TABLE" file looks like (Color coded as I see it in my Query Analyzer):
Code:
CREATE TABLE nfo_tablename(
Col_1 Numeric,
Col_2 Character(4),
Col_3 Character(11),
Col_4 Character(25),
Col_5 Character(6),
Col_6 Character(5),
Col_7 Character(3),
Col_8 Character(6),
Col_9 Character(22),
Col_10 Character(2),
Col_11 Character(10),
Col_12 Numeric,
Col_13 Character(9),
Col_14 Character(2),
Col_15 Character(2),
Col_16 Character(2),
Col_17 Character(8),
Col_18 Character(1),
Col_19 Character(25),
Col_20 Character(39),
Col_21 varchar);
insert into nfo_tablename values(1,'DSW','UNIT','002-11-K','','57C','840','674209','PCD W/OVRLY DTI/DTM','P','ESPECIAL',674856,'676--9','CO','','1','','1','0201-11','Manufacturer_Name','T');
insert into nfo_tablename values(2,'DSW','SHELF','002-07','','35C','9','09','UNEQUAL','M','',0,'','CO','','','','1','002-07','Manufacturer_Name','T');
Thank you all for your insight. Any ideas, thoughts or suggestions, would be appreciated. I really need to get this table done so I can get a couple web pages created this weekend that are due Monday.
Vb stores an image (bitmap/jpg) in a SQL2000 image field. For somereason it doubles the size by adding 00 for each byte. But sometimes itdoesn't add 00 (0000.0000) but 01 or 20 or ?? and also the byte thatshould be transferd is changed...So SQLImageData = Imagebyte + 00and sometimesSQLImageData = CHANGEDImagebyte + xx.Some example data: (hex notation):A1 => A1 0003 => 03 00-----------91 => 18 2083 => 92 018C => 52 01Could anybody give me an explanation, because I need to know what ishappening, so I can remove te extra bytes added......I have already a image when I remove the extra bytes, but with somewrong data (on the place where things like 8C => 52 01 happen)....Thx,Geronimo
View 2 Replies View RelatedRight this has to be a Micro$oft mess-up surely...?I'm running SQL 2k standard with SP3. I have a table which I'm tryingto query using a LIKE operator on a varchar field as follows....WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%'....In actual fact the % is passed in by the application when the userselects "All managers" from the drop down list used to select theManager to filter by. If they select a manager's name from the listit becomes LIKE 'ajames' or whatever.BUT - the table currently contains 2972 records. If I take out theWHERE clause the SELECT returns all records - fine - but if I put thewhere clause in it returns only 1682!! I thought the % was meant tomatch, and I quote the SQL server Books Online files here; "Any stringof zero or more characters." Anyone explain to me whats going onhere?TIANiall
View 12 Replies View RelatedI get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
I can access the report server remotely. However, when I try to access via browser locally, no luck. I get access denied.
Some kind of browser diff maybe?
I noticed that on the browsers/machines that work, I have the option of putting the domain in seperately. The browser that doesnt work (on the same machine as the db server and web site) puts the domain in front of the username for me.
Any ideas?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedI have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies View RelatedJust a quick question that I really couldn't find using google or anything because it was too bland.
How do you return the whole database while still using the LIKE operator.
I have a textbox that you can use to search the database but I want all the data to be displayed underneath the box so you can "browse" the data if you don't know the name of what you are looking for.
What would I put as the default value? I used just defaultvalue="%" and that worked in 1 area, but not another. Is this the right way?
Thanks,
Chris
I have a query and for the life of me I cant figure it out.
I have a web form with a number of textboxes on it e.g. Title, subtitle, isbn. I also have a search button. This search page allows a user to enter any combination of fields in the form and then when they click the search button, a datagrid is diaplyed showing the results (gets the results from a SQL DB).
I have got the code working if the user selects say one item, but I dont know how to allow it so that the user can enter any combination of items in the form and the search button will search for it based on what the user says. I think im gettng confused with my logical operators in the sql statement.
If any one can take a look I would be greatful.public void BindData(){String @BookID = TxtBookID.Text;String @Title = TxtTitle.Text;String @Subtitle = TxtSubtitle.Text;String @ISBN = TxtISBN.Text;String @AuthorFName = TxtForename.Text;String @AuthorLName = TxtSurname.Text;
SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID_Internal, Title, Subtitle, ISBN, Edition, Publication_Date, Author.First_Name, Author.Last_Name FROM Book, Author WHERE Book.Author_ID = Author.Author_ID AND Book.Book_ID_Internal = '" + @BookID + "' AND Book.Title = '" + @Title + "' AND Book.Subtitle = '" + @Subtitle + "' AND Book.ISBN = '" + @ISBN + "' AND Author.First_Name = '" + @AuthorFName + "' AND Author.Last_Name = '" + @AuthorLName + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");GridView1.DataSource = ds;GridView1.DataBind();}
protected void BtnSearch_Click(object sender, ImageClickEventArgs e){BindData();}
Hi :)
I have a select statement that consists on something like this :
SELECT * FROM Products WHERE Ref LIKE '%" & Search & "%'
well this works ....but the problem is that i have some Ref in this format "MC-909" and if the user makes a search with the word "MC909" ...it dosent return any value ...
How can i solve this ??
Thanks in advance