What Is Wrong With This Query..It's Taking Forever...
May 16, 2008
USE GLPDEMO
GO
select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'
DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)
DECLARE TCursor CURSOR for
SELECT TriggerName, TableName from Temp_TablesAndTriggers
OPEN TCursor
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
WHILE @@FETCH_STATUS = 0
select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable
EXECUTE sp_executesql @exestr;
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
CLOSE TCursor
DEALLOCATE TCursor
--DROP TABLE #Temp_TablesAndTriggers
View 4 Replies
ADVERTISEMENT
Mar 1, 2006
I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage, dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM dbo.tblErrorLevel INNER JOIN dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.
View 2 Replies
View Related
May 14, 2008
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..
; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update
thanks
View 8 Replies
View Related
Mar 17, 2008
Hi all,
2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.
So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...
The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??
Anyone has experience running shrink on large DBs?
thanks!
View 14 Replies
View Related
Aug 23, 2007
what can I do?
all queries that used to work are taking forever now???
what can I do?
is there a max size for the db that I may have reached
please advise asap
View 20 Replies
View Related
Dec 14, 2007
I am using RS 2005 and SQL Server 2005. I am having a table with about 6 million rows. I am extracting about 2 milliion rows for a report. When i run the report as a single user the report comes up in 6-7 minutes but when i run the report with 2 users the report takes forever to come up.
The statistics are different each time sometimes 19 minutes sometimes 30 minutes. The report connects to the db with the same dbuser id for both the people running the report. The stored procedure being invoked uses temp tables and also indexes are created on the fly for these temp tables.
The moment 2 people are running the report and when i run an SP_WHO2 i see that one process id that is being started by reportserver blocks another process being run by reportserver.
Timeouts are not happening the report justs goes on forever to come up. Any help? Also if you need any more information please do let me know I will be glad to give them.
The report is a matrix report and there are 4 levels of grouping on the report.
Thanks in advance
View 1 Replies
View Related
May 3, 2000
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
View 3 Replies
View Related
Apr 9, 2008
Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.
Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.
I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??
has anyone got any ideas ?
View 6 Replies
View Related
Jan 15, 2008
Hi All,
I have 2 reports that report on baiscally the same thing, just group differently.
Report 1 groups summary phone call stats by Department, Day, and Hour - which are all drop down options.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see daily stats ... the daily stats can then be expanded to see the hourly stats.
Kinda Like:
-------------------------
-Department 1
-10/1/2007
12:00 AM
1:00 AM
+10/2/2007
+Department 2
-------------------------
Report 2 shows the same summary stats by department and extension - which is also a drop down option.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see summary stats for each extension.
Kinda Like:
-------------------------
-Department 1
Ext 2005
Ext 2008
+Department 2
-------------------------
The queries for these reports run from the Management Studio in about 10 seconds each with the Report 1 query returning about 800 rows for the month of October 2007 and the Report 2 query returning about 30 rows for the same date range.
When the reports are rendered, Report 1 (with 800 rows) is rendered in about 20 seconds, while Report 2 (with only 30 rows) takes about 5 minutes to render.
The reports themselves are very similar, with the only difference being the grouping. It is weird that the report that returns the samller Dataset is actually taking longer to render.
One thing I did try was running the queries from the Data tab of the .rdl files (in visual studio) and the query for Report 2 took about 4 minutes to return data, while (as I mentioned above) it ran in about 10 seconds in Management Studio.
Has anyone else run into this?
Any suggestions?
Thanks in advance!
-Matt
View 1 Replies
View Related
Aug 29, 2001
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
View 3 Replies
View Related
Apr 22, 2008
Back in the days of SQL 7.0 I used a lot of ODBC SELECT querying form VB applications, in which I implemented NOLOCK in order to prevent the primary business applications from being locked out of tables once the queries were run.
Now, quite a few years later, I'm busying myself converting a lot of old Access based forms and queries to TSQL on SQL-Server 2000, and wonder aimlessly why NOLOCK queries (simple select ones) are imensely slower than a standars select clause.
SELECT * FROM employees
This would be much much faster than the code below, but users would get "The current record could not be accessed, as it is being used by another user", evidently because I'm locking the record while producing the output.
SELECT * FROM employees (nolock)
So this could should - as I remember it - do a dirty read on table, not obstructing other users and give me a snapshot of date as they are, although they might be locked for edit.
Could anyone explain to me why the nOLOCK query fials to give me any output? It is as if the nolock request is waiting for the table/records to free? In which case I'll never be able to run a query.
Cheers in advance, Trin
View 14 Replies
View Related
Jan 16, 2007
Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.
View 2 Replies
View Related
Dec 14, 2007
Hi all,
I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.
If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.
I am doing something like this:
###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";
SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();
############################################################################################
StudentTable contains roughly 1000 rows.
Somebody please help me out of this. Thanks in advance.
Surya
View 11 Replies
View Related
May 27, 2015
I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).
View 3 Replies
View Related
Sep 13, 2001
Hi,
I am running this query and it is taking over 3 minutes.
"select * from table1 where CONVERT(varchar(10),dated,5) = '13-09-01' "
Table1 has a column called dated which is datetime datatype.
Any suggestions how can i optimize this query?I tried Non-clustered index on Dated column and time came down to less than 3 but still more than 2min.
TIA.
View 4 Replies
View Related
Nov 2, 2007
Dear All,
here i'm posting my query which is taking 3 minutes
please suggest me the best query
SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val
thank you very much
View 7 Replies
View Related
Apr 3, 2007
Below is my query which is taking a long time to execute, DB is SQL Server 2005 through a web Application
I have downloaded the latest MS SQL 2005 driver 1.xxx and still the query takes long to execute
The Description field is a Full_text indexed catalog column
the p.vendornumber is a primary key same with c.ID
Any one have an idea why it is taking this long to run
The Execution Time is: 13640 ms Which I think is very long
SELECT Upper(p.Type) Type,p.Modelname,p.partno,Upper(p.description) description,
Upper(p.classification)classification,p.vendornumber,p.mfg,
p.price,c.CompanyName,c.City,c.State,p.thumbnail
FROM P_all p, Acts c
WHERE p.vendornumber = c.ID
AND CONTAINS(p.Description, '"helmet*"')
Order by p.VendorNumber
Thanks
View 5 Replies
View Related
Jun 19, 2007
dear guys. i have this one problem, where the sql statements really took very long time to be processed. It took more than 1 minute, depending on the total data in the table. I guest this have to do with the 'count' statements. here is the code:
------------------------------------------------------------
$sql = "SELECT company,theID,abbs,A as Active,N as Nonactive,(A+N) as Total
FROM(
select distinct D.nama As company, C.domID As theID, D.abbrew As abbs,
count(distinct case when B.ids is NOT NULL THEN A.dauserid END) As A,
count(distinct case when B.ids is NULL THEN A.dauserid END) As N
FROM
tableuser A LEFT OUTER JOIN tabletranscript B on (A.dauserid=B.dauserid)
INNER JOIN thedommember C ON(C.entitybuktiID=1 AND C.mypriority=1 AND
C.entitybuktiID=A.dauserid)
INNER JOIN mydomain D ON (C.domID=".$getID.")
GROUP BY D.nama, C.domID, D.abbrew
ORDER BY company
)";
Hope any of you can simplify this statements into a query that doesnt take ages to be processed.
Thanks in advance....
View 1 Replies
View Related
Jan 26, 2007
I am not sure if I am in the correct location or if I should be in the SQL forum but here is my question:
I have an update statement that goes out through SQL 2000 through a local linked server to another SQL 2000 server on my machine. When I run the update in Query Analyzer it takes less than a second. When I run it in my C# code using the SqlCommand object and parameters it takes me ~23 seconds. If I remove one of the parameters it goes down to ~15 milliseconds. Has anyone heard of this happening?
The parameter that I remove is a simple char(10) column that isn't the primary key and is used in the WHERE statement. There isn't an index on the field.
23 Seconds
Update table Set column = @val WHERE field = @field AND other = @other
15 milliseconds
Update table Set column = @val WHERE field = 'values' AND other = @other
View 11 Replies
View Related
May 16, 2007
I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.
use MyDatabase
GO
select db_name(database_id) as 'Database', o.name as 'Table',
s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,
schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s
join sys.objects o on s.object_id = o.object_id
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(database_id) = 'MyDatabase'
order by avg_fragmentation_in_percent desc
--order by avg_fragment_size_in_pages desc
--order by page_count desc
--order by record_count desc
--order by avg_record_size_in_bytes desc
View 4 Replies
View Related
Oct 6, 2005
Hi
I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.
Thanks.
View 1 Replies
View Related
Jan 19, 2004
Hope someone can help me with this because its driving me potty!
I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).
This happens randomly, the scripts will be working fine and then fail a few times before magically working again!
Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!
Any help or tips would really be appreciated.
Thanks.
View 5 Replies
View Related
Aug 29, 2007
Hey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?)
View 1 Replies
View Related
Nov 26, 2007
Can anyone please tell me what is wrong with this query:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
In DreamWeaver the bit in bold is greyed out, why?
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
Thanks Joe
View 2 Replies
View Related
Jan 4, 2005
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT
DISTINCT MtgeBroker,
COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE
(
RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness')
AND
RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974')
)
AND
(
MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE()))
AND
YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE()))
)
GROUP BY MtgeBroker
View 1 Replies
View Related
Mar 15, 2006
Hi all!
I am trying to set up a poll in ASP.NET 2.0, using SQL database. My
problem is that it won't update the database. This is what it looks
like:
(master)
<asp:radiobuttonlist id="vmpoll"
runat="server"
OnSelectedIndexChanged="poll_SelectedIndexChanged">
<asp:listitem>Object1</asp:listitem>
<asp:listitem>Object2</asp:listitem>
<asp:listitem>Object3</asp:listitem>
<asp:listitem>Object4</asp:listitem>
<asp:listitem>Object5</asp:listitem>
</asp:radiobuttonlist>
<br />
<asp:Button ID="btnPoll" runat="server" CommandName="update"
Text="Rösta" />
(master.cs)
protected void poll_SelectedIndexChanged(object sender, EventArgs e)
{
if (vmpoll.SelectedValue != "")
{
string ConnectionString =
System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString;
SqlConnection con = new SqlConnection(ConnectionString);
string sql_update = "UPDATE poll SET votes = votes + 1 WHERE lagnamn =
'" + vmpoll.SelectedValue + "' LIMIT 1;";
Label1.Text = "Du har röstat på " + vmpoll.SelectedValue;
}
else
{
Label1.Text = "Inget värde valt";
}
}
Finally, the database has a table called poll, including the fields
int lagID Pk
string lagnamn
int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but
it won't update the database. I hope someone will see where I am going wrong here.
Thanks in advance!!
View 5 Replies
View Related
Apr 2, 2006
What is wrong with this query? How can may it work?
UPDATE T1 INNER JOIN T2 ON T1.cID = T2.cID SET T1.PSVP = T2.PSVP, T1.PSVR = T2.PSVR
WHERE (((T1.cID)=123))
View 1 Replies
View Related
Sep 2, 2006
I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
rs.MoveNext
Loop
Set rs = Nothing
could show the following:
2441-John Doe (10)
2441-John Doe (11)
if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
I appreciate any help you can give me!
Thanks~
View 1 Replies
View Related
Feb 5, 2004
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
, SUM(inv_numb, freight_val)
FROM OPCSAHH
WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
LESS THEN = 2003
AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
anyone know?
View 7 Replies
View Related
May 19, 2008
THe following query generates, Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable '
select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user)
from ? as o
where
not exists(select * from users u
where (u.users_id = o.rn_create_user)
)';
View 3 Replies
View Related
Mar 27, 2007
insert into o_country(cname) values(select cname from countries where cid=5)
o_country
----------
Cid int(primary key)
Cname varchar(50)
countries
----------
Cid int(primary key)
Cname varchar(50)
Malathi Rao
View 1 Replies
View Related
Jun 22, 2007
Dear experts,
please let me know where is the mistake in my query
select distinct (select COLUMN003 from TABLE021 where COLUMN001 = t.COLUMN001)
+ '(' +(select COLUMN002 from TABLE021 where COLUMN001 = t.COLUMN001)+ ')' as tablename
(select COLUMN003 from CATABLE009 where COLUMN001 = t.COLUMN003) modulename from TABLE022 t order by tablename
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 4 Replies
View Related