|
Hi
How can i combine the follow SQL statements? Without compromising on performance.
<br />
select ppar_engparameters.parmval, ppar_engparameters.parmname<br />
from ppar_engparameters<br />
where ppar_engparameters.prcdversion = (select max(prcd.prcdversion) from prcd where<br />
prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A') and <br />
ppar_engparameters.parmval='myValue' <br />
<br />
<code>select category from catg_pll<br />
where catgnumber = 01 and partprcdname||' '||partprcdversion = 'myValue2'
select catg_pll.category, prcd.activeflag, prcd.prodstatus from catg_pll, prcd<br />
where prcd.prcdname||' '||prcd.prcdversion = 'myValue2'<br />
and catgnumber = '05' and <br />
catg_pll.partprcdname||' '||catg_pll.partprcdversion = 'myValue2'<br />
|
|
|
|
|
Hi,
I'm facing this problem which i don't know how to solve it.
Can anyone guide me?
I have a list of data in table @tmp1. Format like below. This table consists > 100 rows of data.
Format:
PartID PartNumber Yield
866 A100 0.98154735437972429
866 A100 0.9997734994337486
866 A100 0.99168797953964194
868 A200 0.70783730158730163
868 A200 0.99929971988795518
868 A200 0.99889908256880733
When i run this query,
select partid,partnumber,convert(float,exp(sum(log(yield))))
from @tmp1
group by partid,partnumber
Error encountered.Error message is "A domain error occurred".
I'm trying to multiply all data from 'Yield' column. That's why i query with
convert(float,exp(sum(log(yield))))
But I suspect convert(float,exp(sum(log(yield)))) is giving the error.
So I tried to change datatype to int or varchar or decimal.
But still cannot be solved.
Pls help me.
Thanks in advance.
|
|
|
|
|
Check whether column yield contains any -ve value
this might result in domain error
Regards
KP
|
|
|
|
|
It contains zero value. But don't have -ve value.
How can i multiply all data when there is zero value?
still convert(float,exp(sum(log(yield)))) ??
Can you please help to guide me?
Thanks.
|
|
|
|
|
in one of mydatabase,i want date store in ddmmyy format in sqlserver 2005,
currently it stores in mmddyy format.
i know i can display in any format using conver function,but if it can store in format which i need,then i dont want to use extra function.
is there any wany to do that??
thank you
|
|
|
|
|
I don't think that you can change the storage format. Using Convert function would be better. If your application uses common date format in all areas, consider putting the convert inside a custom function. Use this custom function everywhere.
|
|
|
|
|
SQL Server stores the data in a datetime datattype (a number underneath it all), the format is only a concern of your presentation layer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
Is there any way to get current weeks detail frm the database using SQL 2000??
For e.g. today is wednesday, i want all data from monday to sunday.
Plz can anyone help in this
--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work.
|
|
|
|
|
DATEPART() function should solve your problem
Ex.
<br />
WHERE DatePart(wk, Convert(DateTime, ColA)) = DatePart(wk, Convert(DateTime, getdate()))<br />
AND Year(ColA) = Year(GetDate())<br />
Regards
KP
|
|
|
|
|
Thanks Krish..
It worked....
--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work.
|
|
|
|
|
Does anyone know how to (or if it is possible) to change SQL 2005 editor to select text inside square brackets without the brackets?
eg USE [MyDatabase]
If you doubleclick on 'MyDatabase' it selects the brackets as well. Good old QA in SQL 2000 just selected the text.
|
|
|
|
|
Hi All,
I have a table report which has 10 fields as cols.The last 2 fields come from 2 diffrent subreport, having a single field each. There are also 2 groups in this table. I have to limit the rows by checking each cell in a group is 0. If all the cells have got a 0 then hide the row. I can check for all the cells except the last 2(Subreport).
Since the last 2 cells has only got 2 subreports instead of a textbox, as it is the case for the remaining 8 cells, i don't know how to check for data returning from a subreport. For all the other textboxes i have checked it as ReportItems!textbox1.value=0
Can anyone help me with this Please?
Thanks & Regards,
Payal
|
|
|
|
|
Subreport report items have a NoRows property. You can check its value to determine whether any data has been returned for the subreport.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks Paul .
Can this property be checked from the main report?
I don't know how to refer the subreport from the main report...Like ReportItems!txtbox1 is there options for referring to a subreport also?
It will be really helpful if u could help me out with some examples as i am coming across this for the first time.
Cheers
Payal
|
|
|
|
|
Ok, I'm tried and I'm making mistakes, but for the life of me I don't see what's wrong with the following sproc. I even ran it and then tried an external Select @@Identity which did return a value, but not the sproc.
SPROC:
@CourseNumber int OUTPUT,
@CourseName nvarchar(100),
@College int,
@Department int,
@Description nvarchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO Course
(CourseName, College, Department,Description)
VALUES
(@CourseName, @College, @Department,@Description)
SELECT @CourseNumber = @@IDENTITY
"Every time Lotus Notes starts up, somewhere a puppy, a kitten, a lamb, and a baby seal are killed. Lotus Notes is a conspiracy by the forces of Satan to drive us over the brink into madness. The CRC-32 for each file in the installation includes the numbers 666." Gary Wheeler
"The secret to a long and healthy life is simple. Don't get ill and don't die." Pete O'Hanlon, courtesy of Rama
"I realised that all of my best anecdotes started with "So there we were, pissed". Pete O'Hanlon
|
|
|
|
|
Does table Course contain any columns where the IDENTITY property is true?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Yes it does, the CourseNumber column.
Anyways, it works now. I restarted the DB and now it works. Go figure. Thanks though
"Every time Lotus Notes starts up, somewhere a puppy, a kitten, a lamb, and a baby seal are killed. Lotus Notes is a conspiracy by the forces of Satan to drive us over the brink into madness. The CRC-32 for each file in the installation includes the numbers 666." Gary Wheeler
"The secret to a long and healthy life is simple. Don't get ill and don't die." Pete O'Hanlon, courtesy of Rama
"I realised that all of my best anecdotes started with "So there we were, pissed". Pete O'Hanlon
|
|
|
|
|
Hi, I have a table that contains a column of type XML, and an XSL style sheet is stored within these. Inside each XSL, there is a value called TargetURL - which I would like to update to URL. The text replace function does not work with XML, and I did not find anything in google. Does anyone know how this can be done?
|
|
|
|
|
"An Error has occured whilst establishing a connection to the server.When connecting to sqlserver 2005, this failure may be caused by the fact under default settings SQL Server does not allow remote connections.(Provider: named pipes; error 40-Could not open connection to sql server"
Hi All
i am getting the above error when i am trying to retrieve data from sql 2005.
i have created my project in vs2005.
my connection string = "Data Source=.;Initial Catalog=MYDB;Persist Security Info=True;User ID=sa;Password=sa"
i have already enabled all my Sqlexpress protocols and enabled remote connection in the management express.
do you have any ideas on how to solve this.
p.s. the program works fine if i create an odbc to the db but i dont want to use an odbc.
thanks
Anoop Brijmohun
|
|
|
|
|
Hi Anoop,
Please follow the below mentioned steps:
Step 1) Make sure SQL SERVER is up and the instance you try to connect is running.
Step 2) Your system Firewall should not block SQL Server port.
Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
Step 4) Now follow this KB Article of MSDN depending on your server : http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
You may also take a look at the following link of Microsoft Forum.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=147346&SiteID=1[^]
I hope this helps .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Hi John
Thanks for you suggestions...
i tried those but still didn't work..
after some investigation i discovered that the connectionstring was not correct.
it should have read...
"Data Source=ANOOPNB\SQLEXPRESS;Initial Catalog=MYDB;Persist Security Info=True;User ID=sa;Password=sa"i used the vs2005 wizard under the settings menu to get this.
this solved the problem for me...
thank you so much though!
Anoop
|
|
|
|
|
I am working on a project that generates reports via SSRS, and i am having the following problem. for the LoadReport method, i am using "/temp" for the report path. i have placed a "temp" folder every place on the server i can imagine to help the service "find" it, but I keep getting this error:
The item '/temp' cannot be found. ---> The item '/temp' cannot be found.
here is the stack trace:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. <br />
<br />
Exception Details: System.Web.Services.Protocols.SoapException: The item '/temp' cannot be found. ---> The item '/temp' cannot be found.<br />
<br />
Source Error: <br />
<br />
<br />
Line 227: [return: System.Xml.Serialization.XmlElementAttribute("executionInfo")]<br />
Line 228: public ExecutionInfo LoadReport(string Report, string HistoryID) {<br />
Line 229: object[] results = this.Invoke("LoadReport", new object[] {<br />
Line 230: Report,<br />
Line 231: HistoryID});<br />
<br />
<br />
<br />
<br />
Stack Trace: <br />
<br />
<br />
[SoapException: The item '/temp' cannot be found. ---> The item '/temp' cannot be found.]<br />
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +551452<br />
System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +204<br />
<br />
<br />
ANy idea? This is my SSRS project, and outside of this little roadblock, it is moving along beautifully.
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
The report path should be the full path to the report relative to the root folder, including the name of the report itself. You appear to have just specified the name of the folder the report resides in and not the name of the report itself. If you don't specify the name of the report, how can the report server know what report you want to load?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
I'm exporting my report to Excel and there are colums missing ! Specifically columns where visibility is conditional. I can see them on the server, where they appear and disappear corrdctly. Is excel just copying each page or is it trying to be cleverer than that?
I just want what I can see exporting to Excel, that's not what is happening.
Thanks in Advance.
Harvey
|
|
|
|
|
how to insert null to an EMPTY cell in SQL SERVER...
i.e, in a field, i enter some values and deleted it. so, it is considered as EMPTY field and not null. i need a query to make that field <NULL>
can anyone help me? - KARAN
|
|
|
|