Friday, March 30, 2012
Query Help
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql
Friday, March 23, 2012
Query for Table structure
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null
Query for SUM
Hi, i have a simple table with itemcode and quantity
Code Qty
--
A 2
B 3
C 5
A 3
How do i query the table to return me the total quantity by itemcode (as below)?
Code Qty
--
A 5
B 3
C 5
use aggregate function sum () and grup by
Select code,sum(qty) as TotalQty from YourTableName group by Code
Madhu
|||use the following code,
Code Snippet
Create Table #data (
[Code] Varchar(100) ,
[Qty] int
);
Insert Into #data Values('A','2');
Insert Into #data Values('B','3');
Insert Into #data Values('C','5');
Insert Into #data Values('A','3');
Select
Code
,Sum(Qty)
From
#Data
Group By
Code
Wednesday, March 21, 2012
Query for Backup Model
to backups but ran into a snag. Is there a way to query what backup
model a database is using? I can see it if I right click a database,
go to properties, select the options tab, and then can see the current
model (full, bulk-logged, and simple) but I would need to hard code
each database. In the spirit of automation, I'd rather query for the
type and then work around that instead. But I've been all over the
master database and can't seem to find any field that will indicate
the backup type.
Any guru's out there that can point me in the right direction?
Thanks,
UtahHi Utah,
Try DATABASEPROPERTYEX in Books Online: http://msdn2.microsoft.com/en-us/library/ms186823.aspx
You'll want the Recovery property.
Thanks,
KenJ
On Mar 15, 5:45 pm, Utahd...@.hotmail.com wrote:
Quote:
Originally Posted by
I'm trying to write some T-SQL code for adding some fills and thrills
to backups but ran into a snag. Is there a way to query what backup
model a database is using? I can see it if I right click a database,
go to properties, select the options tab, and then can see the current
model (full, bulk-logged, and simple) but I would need to hard code
each database. In the spirit of automation, I'd rather query for the
type and then work around that instead. But I've been all over the
master database and can't seem to find any field that will indicate
the backup type.
>
Any guru's out there that can point me in the right direction?
>
Thanks,
Utah
Quote:
Originally Posted by
Hi Utah,
>
Try DATABASEPROPERTYEX in Books Online:http://msdn2.microsoft.com/en-us/library/ms186823.aspx
>
You'll want the Recovery property.
>
Thanks,
>
KenJ
>
On Mar 15, 5:45 pm, Utahd...@.hotmail.com wrote:
>
>
>
Quote:
Originally Posted by
I'm trying to write some T-SQL code for adding some fills and thrills
to backups but ran into a snag. Is there a way to query what backup
model a database is using? I can see it if I right click a database,
go to properties, select the options tab, and then can see the current
model (full, bulk-logged, and simple) but I would need to hard code
each database. In the spirit of automation, I'd rather query for the
type and then work around that instead. But I've been all over the
master database and can't seem to find any field that will indicate
the backup type.
>
Quote:
Originally Posted by
Any guru's out there that can point me in the right direction?
>
Quote:
Originally Posted by
Thanks,
Utah- Hide quoted text -
>
- Show quoted text -
That could not have been more perfect. THANK YOU!
Friday, March 9, 2012
Query dataset locally from code
I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.
I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.
Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function
Thanks in advance, HenryHello Henry,
I have suggested the following article to you.
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
You do not need to configure the trust level if it not required.
I hope this will be some help.
The Assembly is more functional than the Embeded Code in Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.
I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function
The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?
Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function
Thanks in advance, Henry|||Hello Henry,
Unfortunately, you could not refer the dataset in the code.
I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Wei Lu,
it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.
Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications
So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()
With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!
Any ideas?
Thank you, Henry|||Hello Henry,
Yes, the render order is from the upper-left to bottom-right.
I would like to suggest you use a hidden textbox in the report to
initialize.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If I understood you correctly you are passing a predefined number of
parameters to this function. This can also be done dynamically using a
little hack described in this page:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
I took the code at this page and used to build a dynamic localization
function that reads the text id numbers, language codes and corresponging
texts from a dataset and stores them into a Hashtable object using text id +
language code as the key and text as the value.
Basic idea is that if an aggregate function like Sum is called for a certain
scope, it will go through all of the data in that scope and it can take a
function call that returns a number as a parameter.
Like this:
Sum(Code.LoadDictionary(Fields!TEXT_ID.Value , Fields!LANGUAGE_CODE.Value,
Fields!TEXT_TXT.Value), "DICTIONARY")
Where DICTIONARY is the dataset for dictionary. The LoadDictionary function
is called once for each row in the scope.
You can the have this 'loader' in a hidden field as Mr. Lu suggested.
Then for the localized field you just have for example
=Code.GetLocalizedText("TEXT_0001", Parameters!LanguagePar.Value)
I know that your solution works great. I just think tahat this way the
function calls are simpler. I hope this helps.
Juho Salo
Wednesday, March 7, 2012
Query Column with letters
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?|||if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?
Well the data is already in there and we would rather have it disabled with the option of cleaning it up later rather than simply deleting such data.|||ok. Use the ISNUMERIC function... it'll return a 1 if the field is a valid number... 0 if it's not. That should be the easiest way to determine if there are letters in the postal code.|||Good idea, but I must allow for hyphens.|||Ya mean like
USE Northwind
GO
SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 varchar(8000))
GO
INSERT INTO myTable99(Col1)
SELECT '12345' UNION ALL
SELECT 'Brett' UNION ALL
SELECT 'North Dallas 40' UNION ALL
SELECT '40-20' UNION ALL
SELECT '123.45' UNION ALL
SELECT '123456789012345678901234567890'
GO
SELECT * FROM myTable99
WHERE ISNUMERIC(REPLACE(Col1,'-','')) = 1
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO
Saturday, February 25, 2012
Query by database or code
I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks
I'm not exactly sure what you are asking. Are you asking whether or not to do your database access from the ASPX page or the code-behind? Or, are you asking if you should parameterize your queries?
|||Im using ASP.Net to create a website and heres and example of how i retrieve the data:
Dim strCommandText as String= "SELECT * FROM Player INNER JOIN Manufacturer ON Player.PlayerManufacturerID =
Manufacturer.ManufacturerID ORDER BY Player.PlayerName"
Dim myCommand As SqlCommand = new SqlCommand(strCommandText, myConnection)
Now above i write the SQL syntax into my application to retrieve data. If i have a query created within the DATABASE called ParameterName then i can write the code as:
MyCommand.Paramters.Add(ParameterName)
So my question is really asking which method is approved and why as theyre both doing the same thing?
Thanks
|||If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:
http://www.4guysfromrolla.com/webtech/092601-1.shtml
|||The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.
|||
If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:
http://www.4guysfromrolla.com/webtech/092601-1.shtml
Not quite. Im just trying to see if i should use a Select query in my code or should i call aparameter Stored Procedure from the database to retrieve the data. Sorry my fault i was using the wrong terminology, i should have said Stored Procedure but got my wires crossed as ASP .Net terms this as a parameter being passed in to the code your writing
jguadagno:
The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.
I think this may have answered the question. Where can i get started with Class libraries? also thanks for advising not to use SELECT * - i only used that in this question as an example.
Thanks guys
|||
EssCee:
I think this may have answered the question. Where can i get started with Class libraries?
http://www.15seconds.com/issue/050721.htm