I Couldn't Find Appropriate SQL Queries
Feb 6, 2007
Because forum HTML codes are OFF I had to put my question between code tags. Because to tell my question I had to show the database tables and wanted output. I succeeded retrieving the datas but combining these three tables together with SQL or ASP codes is too complex. Please help me determining the table. As I am subscribed to this forum you can ask me anything about my question. I can clarify my question if you need. Your answers will be replied immediately.
(To see my question pls save the codes below as htm file or see it with a wysiwyg editor.)
<p><b><font size="2">tbl_PRODUCTIONS</font></b></p>
<table border="1" width="971" id="table1">
<tr>
<td height="19" width="54"><b><font size="2">fld_DATE</font></b></td>
<td width="161" height="19"><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
<td height="19" width="124"><b><font size="2">fld_PRODUCT_CODE</font></b></td>
<td height="19" width="222"><b><font size="2">fld_DAILY_PRODUCTION_PROGRAM</font></b></td>
<td height="19" width="176"><b><font size="2">fld_PRODUCTION_QUANTITY</font></b></td>
<td height="19" width="136"><b><font size="2">fld_STOCK_QUANTITY</font></b></td>
<td height="19" width="52"><b><font size="2">fld_SELL</font></b></td>
</tr>
<tr>
<td width="54" >......</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">.....</td>
<td width="52">....</td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">115</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">190</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">350</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">150</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">100</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">135</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">80</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">99</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">210</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">309</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">155</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">100</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">60</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">190</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">350</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">156</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">120</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">140</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" >...</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">....</td>
<td width="52">....</td>
</tr>
</table>
<p>Â </p>
<p><b><font size="2">tbl_ANNUAL_PRG</font></b></p>
<table border="1" width="552" id="table2">
<tr>
<td width="62"><b><font size="2">fld_YEARS</font></b></td>
<td><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
<td><b><font size="2">fld_PRODUCT_CODE</font></b></td>
<td width="177"><b><font size="2">fld_PRODUCTION_PROGRAM</font></b></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="177"><font size="2">15000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="177"><font size="2">22000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="177"><font size="2">35000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="177"><font size="2">50000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="177"><font size="2">170000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="177"><font size="2">12000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="177"><font size="2">123000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="177"><font size="2">18000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="177"><font size="2">25000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="177"><font size="2">32000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="177"><font size="2">55000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="177"><font size="2">190000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="177"><font size="2">10000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="177"><font size="2">129000</font></td>
</tr>
</table>
<p>Â </p>
<p><b><font size="2">tbl_PRODUCT_GROUPS<br>
(This table gives the names to the product of the tbl_PRODUCTIONS table)</font></b></p>
<table border="1" width="366" id="table3">
<tr>
<td><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
<td width="175"><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
</tr>
<tr>
<td width="175"><font size="2">1.10</font></td>
<td width="175"><font size="2">Pencil</font></td>
</tr>
<tr>
<td width="175"><font size="2">1.20</font></td>
<td width="175"><font size="2">Rubber</font></td>
</tr>
<tr>
<td width="175"><font size="2">2.110.12</font></td>
<td width="175"><font size="2">Ruler</font></td>
</tr>
<tr>
<td width="175"><font size="2">12.128.43</font></td>
<td width="175"><font size="2">Pencil Box</font></td>
</tr>
<tr>
<td width="175"><font size="2">6.142.42</font></td>
<td width="175"><font size="2">School Bag</font></td>
</tr>
<tr>
<td width="175"><font size="2">9.86.50</font></td>
<td width="175"><font size="2">Pen</font></td>
</tr>
<tr>
<td width="175"><font size="2">12.43.250</font></td>
<td width="175"><font size="2">Calendar</font></td>
</tr>
</table>
<p>Â </p>
<p><font size="2">These 3 tables are in Oracle. I am trying to access the tables
with an asp page. I succeed to retrieve the values to a page, but processing the
values and joining the tables together is too complex for me. The table I want
is like this. Would you help me creating <b>ASP</b> page and <b>SQL</b> queries
to create this table:</font></p>
<p><font size="2">Parameters to send the asp page that process the SQL queries:<br>
startdate=18.01.2007<br>
finishdate=20.01.2007</font></p>
<p><b><font size="2">The Productions of -18.01.2007-20.01.2007</font></b></p>
<table border="1" width="1066" id="table4">
<tr>
<td><font size="2">Comes From tbl_PRODUCTIONS table</font></td>
<td><font size="2">Comes From tbl_PRODUCT_GROUPS table</font></td>
<td width="155"><font size="2">Comes from tbl_ANNUAL_PRG table</font></td>
<td width="208"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
<td width="179"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
<td width="108"><font size="2">Comes from tbl_PRODUCTIONS retrieving the
values for "finishdate". Not cumulative like other values!</font></td>
<td width="187"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
</tr>
<tr>
<td><b><font size="2">Producer Locations</font></b></td>
<td><b><font size="2">Products</font></b></td>
<td width="155"><font size="2"><b>Annual Production Program</b></font></td>
<td width="208"><font size="2"><b>Production Program for wanted period</b></font></td>
<td width="179"><font size="2"><b>Production Quantity for wanted period</b></font></td>
<td width="108"><font size="2"><b>Stock Quantity</b></font></td>
<td width="187"><font size="2"><b>Selling Quantity for wanted period</b></font></td>
</tr>
<tr>
<td width="119" ><font size="2">IST</font></td>
<td width="61"><font size="2">Pencil</font></td>
<td width="155"><font size="2">18000</font></td>
<td width="208"><font size="2">300</font></td>
<td width="179"><font size="2">304 </font></td>
<td width="108"><font size="2">100</font></td>
<td width="187"><font size="2">30</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IST</font></td>
<td width="61"><font size="2">Rubber</font></td>
<td width="155"><font size="2">25000</font></td>
<td width="208"><font size="2">600</font></td>
<td width="179"><font size="2">590</font></td>
<td width="108"><font size="2">200</font></td>
<td width="187"><font size="2">162</font></td>
</tr>
<tr>
<td width="119" ><font size="2">ANK</font></td>
<td width="61"><font size="2">Ruler</font></td>
<td width="155"><font size="2">32000</font></td>
<td width="208"><font size="2">900</font></td>
<td width="179"><font size="2">1009</font></td>
<td width="108"><font size="2">450</font></td>
<td width="187"><font size="2">2235</font></td>
</tr>
<tr>
<td width="119" ><font size="2">ANK</font></td>
<td width="61"><font size="2">Pencil Box</font></td>
<td width="155"><font size="2">55000</font></td>
<td width="208"><font size="2">450</font></td>
<td width="179"><font size="2">461</font></td>
<td width="108"><font size="2">4664</font></td>
<td width="187"><font size="2">165</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">School Bag</font></td>
<td width="155"><font size="2">190000</font></td>
<td width="208"><font size="2">300</font></td>
<td width="179"><font size="2">320</font></td>
<td width="108"><font size="2">234</font></td>
<td width="187"><font size="2">231</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">Pen</font></td>
<td width="155"><font size="2">10000</font></td>
<td width="208"><font size="2">375</font></td>
<td width="179"><font size="2">365</font></td>
<td width="108"><font size="2">654</font></td>
<td width="187"><font size="2">240</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">Calendar</font></td>
<td width="155"><font size="2">129000</font></td>
<td width="208"><font size="2">225</font></td>
<td width="179"><font size="2">230</font></td>
<td width="108">233</td>
<td width="187"><font size="2">303</font></td>
</tr>
</table>
OS: WinXP Pro
DB: Oracle (Version ?)
Script: ASP
View 4 Replies
ADVERTISEMENT
Feb 21, 2008
May i have a list of Which queries or codes couldn't use in a Transactions in Sql Server ?
View 6 Replies
View Related
Feb 2, 2006
Hi,
I have just installed sql server 2005 enterprise edition. I am totally new to sqlserver. I want to create a database in sql server and access that from my VB.net application. I couldn't find sql server management studio to create a database. can somebody help me with this...
thanks,
Leela
View 3 Replies
View Related
Oct 18, 2006
Hi,
I found rtrim and ltrim functions, but I didn't find trim, so should I use rtrim(ltrim(' a ' ))? Am I right?
cheers,
Alessandro Camargo
View 5 Replies
View Related
Sep 12, 2000
Is there a way to find out total number of queries ran i.e by day/hour or since the database was brought up in SQL Server 7?
Thanks in advance,
View 1 Replies
View Related
Apr 22, 2015
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies
View Related
Jul 20, 2005
Help, please. I am trying to update atable with this structre:CREATE TABLE Queue (PropID int, EffDate smalldatetime,TxnAmt int)INSERT Queue (PropID) SELECT 1INSERT Queue (PropID) SELECT 2INSERT Queue (PropID) SELECT 3....from this table...CREATE TABLE Txns (PropID int, TxnDate smalldatetime,TxnType char(1), TxnAmt int)INSERT Txns SELECT 1 '20000201', 'B', 100000INSERT Txns SELECT 1 '20020515', 'B', 110000INSERT Txns SELECT 1 '20020515', 'A', 120000INSERT Txns SELECT 1 '20020615', 'c', 130000....only certain txn types are okay, and they have an orderof preference...CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)INSERT GoodTxnTypes SELECT 'A', 1INSERT GoodTxnTypes SELECT 'B', 2The idea is to fill in the NULL fields in the Queue table,according to a rule -- the transaction must be the latesttransaction within a date window, it must be one of the goodtxn types, and if there are two txns on that date, choosethe txn by the preferred txn type (A is preferred over B,according to the field Pref).If the time window were 20020101 to 20030101, the txnselected to update the Queue table would be this one:INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there aretwo in the time window that are type A or B; they areboth on the same day, so the 'A' is preferred.If the time window were 20000101 to 20010101, this wouldbe selected because it is the only A or B type txn inthe interval:INSERT Txns SELECT 1 '20000201', 'B', 100000I'm looking for a statement that starts...UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,in this table, is the same as TxnDate in the Txn table).Assume we have @FirstDate and @LastDate available.Help, please. I'm getting stuck with (a) a sub-query tofind the relevant Txn records, and (b) another sub-querywithin that to find the MAX(TxnDate) within the timewindow. Filtering the Txn records on the basis of theGoodTxnTypes table is easy, as is ordering what is returned.But I'm having trouble joining the sub-queries back to theQueue table on the basis of PropId.
View 1 Replies
View Related
Jan 4, 2006
Hi,
I'm new to replication.
I configured Server named TESTER as publisher & distributer. Create new publication for database named TEST ( type : Transactional ), then Create a new push subcriptions for database TESTREPLICATION ( on the same Server : TESTER). Then I open a table in database TEST to input some data but I couldn't see any tables or data in database TESTREPLICATION ( I chose Continuously, not scheduled).
Yet, after the lunch, I could see the tables, no data. I tried to choose Start Synchronizing but not helped.
Please tell me if I configured incorrectly ?
Thanks,
View 7 Replies
View Related
Feb 6, 2007
Hi,
I'm not able to browse through http://localhost/reports.
When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver
please can anyone suggest me some solution to this problem..
Server Error in '/Reports' Application.
The trust relationship between the primary domain and the trusted domain failed.
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.
Exception Details: System.SystemException: The trust relationship between the primary domain and the trusted domain failed.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SystemException: The trust relationship between the primary domain and the trusted domain failed.
]
System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167
System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42
System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46
System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205
System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300
System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191
System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
View 11 Replies
View Related
Jul 20, 2015
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
View 2 Replies
View Related
Mar 30, 2015
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
View 3 Replies
View Related
Dec 30, 2014
How to find the list of queries currently running in the Database with User Login Information.
Since my database application is running slow, to find the slow queries.
View 8 Replies
View Related
May 8, 2006
Hi all,
I have created a package to copy 10 tables from a database to another one. I have created a datasource with SQL Authentication mode. And it couldn't run until I changed the datasource connection to Windows Authentication mode. How could it be like that, it's just the authentication mode?
Is there anyone have the right answer for this situation?
View 3 Replies
View Related
Apr 19, 2006
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently
View 1 Replies
View Related
Oct 21, 2007
Hi all,
I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"
1) The environment:
The Client: Windows XP SP2 (WORKGROUP)
Visual Studio 2008 (or VS2005)
The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN)
SQL Server 2005 Developer
2) User account and Permission login:
I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.
At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already
3) Connection and authentication:
I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command
SELECT SYSTEM_USER,
IS_SRVROLEMEMBER ('sysadmin')
4) Firewall:
I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)
5) Visual Studio Remote Debugger:
I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required."
so i didn't config Visual Studio Remote Debugger any thing.
Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???
If i execute store procedure on Server Explore, it works okey!
Please help me to find out what problem is???
Thanks,
Haiasc
View 2 Replies
View Related
Jul 26, 2005
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Aug 9, 2006
Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
View 1 Replies
View Related
Dec 7, 2007
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies
View Related
Dec 18, 2007
how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
View 3 Replies
View Related
Jun 18, 2004
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies
View Related
Oct 21, 2004
Hi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
View 2 Replies
View Related
Jan 23, 2006
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies
View Related
Jul 16, 2002
Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies
View Related
Aug 3, 2000
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
View 5 Replies
View Related
Feb 7, 2003
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
View 2 Replies
View Related
May 1, 2001
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
View 1 Replies
View Related
Oct 12, 2007
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
View 1 Replies
View Related
Feb 15, 2007
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
View 5 Replies
View Related
Jul 2, 2007
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks
View 14 Replies
View Related
Jun 26, 2012
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int
dateon DATETIME
dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
View 8 Replies
View Related
Mar 18, 2004
I'm confused.
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
PLEASE help, I have a deadline in a week : (
THANK YOU
View 1 Replies
View Related
