Hi, everyone.
I have read a lot of topics about execution plan for query, but I got little.
Please give me some help with examples for comparing different select statements to find the best efficient select statement.
Thank you very much.
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies View RelatedMy group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
View 2 Replies View RelatedI downloaded the trial version of sql server 2005. but after installation, I couldnt find the Enterprise Manager and Query Analyzer. Any idea about that? Thanks.
View 3 Replies View RelatedHi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer:
When i try as Select * from Address it said invalid object.
But when i explored AdventureWorks database in the Management studio i see it as Person.Address!!! i don't understand what is a Person in Person.Address??? any clues on this?
So as a test when i ran select * from Person.Address it did work and i saw the results in query analyzer.
Can any one help me understand about this confusion?
Thanks-L
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
Case 1
execute an SQL Update Command
Case 2
execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
I have a table that has the following...
ID Status Type Check_Num Issued IssueTime Paid PaidTime
-----------------------------------------------------------------
1 I <null> 10 10.00 2/1/02
2 E IDA 10 <null> <null> 10.01 2/3/02
3 E CAP 10 <null> <null> 10.00 2/4/02
4 E PNI 11 <null> <null> 15.00 2/6/02
I want to return the Check_Num,Type, Paid, and Max(PaidTime) from this...
Example:
Check_Num Type Paid Time
---------------------------
10 CAP 10.00 2/4/02
11 PNI 15.00 2/6/02
Any assistance will be greatly appreciated.
Thanks,
Brian
It seems I should be able to do 1 select and both return thatrecordset and be able to set a variable from that recordset.eg.Declare @refid intSelect t.* from mytable as t --return the recordsetSet @refid = t.refidthe above doesn't work-how can I do it without making a second trip tothe database?Thanks,Rick
View 3 Replies View RelatedWhich way of retrieving a record is more effecient?:Select tbl1.field1, tbl2.field1from table1 tbl1 inner join table2 tbl2on tbl1.id = tbl2.idwhere someid = somevalueand someid = somevalueorSelectfield1 = (Select field1 from tabl1 where someid = somevalue),field2 = (Select field2 from table2 where someid = somevalue)
View 3 Replies View Relatedwhat's the difference, if I use SQLDataReader at code level, making a query of that retrieves 500 rows and 2 columns, and making a query that retrieves 2 rows and 500 columns?
View 6 Replies View RelatedThis query is giving me very slow search .What could be the efficient way
SELECT (
SELECT COUNT(applicationID) FROM Vw_rptBranchOffice
WHERE ( statusDate between '2008-03-13 16:12:11.513' AND '2008-05-30 00:00:00.000'
AND SearchString like '%del%')) AS
totalNO,ApplicationID,SearchString,StudentName,IntakeID,CounslrStatusDate
FROM Vw_rptBranchOffice
WHERE statusDate between '2008-03-13 16:12:11.513' AND '2008-05-30 00:00:00.000' AND
SearchString like '%del%'
I have a table that has a date and time column. I need to do a search on the table by days and will eventually need to do it by hours as well.
I wanted to ask the question that will the performance get better if I create two additional columns one stateing the "Day of Week" and the other stating " Hour of Week". These will have numerical values prepopulated i.e. for Saturday 7, sunday 1, Monday 2 etc etc etc. And for the time , I will have 1 for 1pm-159pm 2 for 2-2:59pm pm 3 for 3-3:59pm etc etc etc
The total number of rows in the table could total half a million, with filtered to by weekf of day may be reduce to being 80,000 or so.
Is the above criteria to add two numeric columns to the table and putting indexes on those two numeric fields is a good solution? and efficinet or just using the datepart functionality with the actual date column and using the week of day and time parameters as the case may be.
Thanks fro your help.
Thanks
Please help me with the efficient JOIN query to bring the below result :
create table pk1(col1 int)
create table pk2(col1 int)
create table pk3(col1 int)
create table fk(col1 int, col2 int NOT NULL, col3 int, col4 int)
insert into pk1 values(1)
insert into pk1 values(2)
insert into pk1 values(3)
insert into pk2 values(1)
insert into pk2 values(2)
insert into pk2 values(3)
insert into pk3 values(1)
insert into pk3 values(2)
insert into pk3 values(3)
insert into fk values(1, 1, null, 10)
insert into fk values(null, 1, 1, 20)
insert into fk values(1, 1,null, 30)
insert into fk values(1, 1, null, 40)
insert into fk values(1, 1, 1, 70)
insert into fk values(2, 3, 1, 60)
insert into fk values(1, 1, 1, 100)
insert into fk values(2, 2, 3, 80)
insert into fk values(null, 1, 2, 50)
insert into fk values(null, 1, 4, 150)
insert into fk values(5, 1, 2, 250)
insert into fk values(6, 7, 8, 350)
insert into fk values(10, 1, null, 450)
Below query will give the result :
select fk.* from fk inner join pk1 on pk1.col1 = fk.col1 inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3
Result :
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| 1 | 1 | 1 | 70 |
| 2 | 3 | 1 | 60 |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 3 | 80 |
+------+------+------+------+
But I require also the NULL values in col1 and col3
Hence doing the below :
select distinct fk.* from fk inner join pk1 on pk1.col1 = fk.col1 or fk.col1 is null inner join pk2 on pk2.col1 = fk.col2 inner join pk3 on pk3.col1 = fk.col3 or fk.col3 is null
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| null | 1 | 1 | 20 |
| null | 1 | 2 | 50 |
| 1 | 1 | null | 10 |
| 1 | 1 | null | 30 |
| 1 | 1 | null | 40 |
| 1 | 1 | 1 | 70 |
| 1 | 1 | 1 | 100 |
| 2 | 2 | 3 | 80 |
| 2 | 3 | 1 | 60 |
+------+------+------+------+
The above is the reqd output, but the query will be very slow if there are more NULL valued rows in col1 and col3, since I need to also use distinct if I use 'IS NULL' check in JOIN.
Please let me know if there is an aliternative to this query which can return the same result set in an efficient manner.
I am using a Table Many Times in Left Outer Joins and Inner Joins for various Conditions,
is there anyway of writing a query using minimal Table usage, instead of Recurring all the time.
**********************************
SELECT
blog.blogid,
BM.TITLE,
U.USER_FIRSTNAME+ ' ' + U.USER_LASTNAME AS AUTHORNAME,
Blog_Entries = (cASE WHEN Blog_Entries is NULL or Blog_Entries = ' ' then 0 else Blog_Entries END),
Blog_NewEntries = (cASE WHEN Blog_NewEntries is NULL or Blog_NewEntries = ' ' then 0 else Blog_NewEntries END),
Blog_comments = (cASE WHEN Blog_comments is NULL or Blog_comments = ' ' then 0 else Blog_comments END),
dbo.DateFloor(VCOM.objCreationDate) AS CreationDate,
dbo.DateFloor(BLE.entryDate) AS Date_LastEntry
FROM vportal4VSEARCHCOMM.dbo.blog_metaData BM
INNER JOIN vportal4VSEARCHCOMM.dbo.blog BLOG
ON BM.BLOGID = BLOG.BLOGID
INNER JOIN vportal4VSEARCH.dbo.[USER] U
ON U.USER_ID = BLOG.OWNERID
INNER JOIN vportal4VSEARCHCOMM.dbo.vComm_obj VCOM
ON BLOG.vCommObjID = VCOM.vCommObjId
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BLE
ON BLOG.BLOGID = BLE.BLOGID
LEFT OUTER JOIN
(SELECT BlogID, Blog_Entries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
GROUP BY BlogID )B on B.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BlogID, Blog_NewEntries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
WHERE ENTRYDATE > '01/01/2008'
GROUP BY BlogID )C on C.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BEN.BLOGID, Blog_comments = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.blog_comment BC
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BEN
ON BEN.blog_entryId = BC.blogEntryId
GROUP BY BEN.BLOGID )D on D.BLOGID = BM.BLOGID
WHERE VCOM.objName like '%blog%'
thanks
Hi all,Any thoughts on the best way to run an update query to update a specificlist of records where all records get updated to same thing. I would thinka temp table to hold the list would be best but am also looking at theeasiest for an end user to run. The list of items is over 7000Example:update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '001-LBK'update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '001-LYE'update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '001-XLBK'update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '001-XLYE'update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '002-LGR'update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS' where item_no = '002-LRE'All records get set to same. I tried using an IN list but this wassignificantly slower:update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',prod_cat = 'OBS'where item_no in('001-LBK','001-LYE','001-XLBK','001-XLYE','002-LGR','002-LRE')Thanks
View 3 Replies View RelatedHi,Where does Sql server store the most current active T-SQLstatements? Is there a system table or stored procedure(documented orundocumented)that will show you the active sql statements beingexecuted. For eg: in oracle you can query v$sql to see all the sqlstatements executed by each session.I know that you can use Enterprise manager, but was wondering if youcan run a query via query analyzer to look at it.Any help is appreciated.ThanksGeetha
View 3 Replies View Related
How can I make a statement that will return the 10 most frequently occuring values in a column?
I have no idea if that is even possible, if you have an idea on how I could do that I would really appreciate it.
Im trying to make a page that would show some statistics on a table I have.
Im also trying to make something that would show the count of the number of records inserted in the last 24 hours, week, month, year etc. The table has a column called "DateInserted" as SmallDate, right now i can use a Where DateInserted > '20080310' to get the count, but its not dynamic, is there anyone to merge all these results into one row with each column being a diffrent time period?
I know this a lot of questions, but I would really appreciate any pointers.
STEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO Trace
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted from History'),
('Insert into Account Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies View RelatedI created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
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)
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedHello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
Both of these work fine separately; How do I join these two Select Statements?
SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX
FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON
WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT
SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
Thanks!
Lisa
I have a table that list Canadian provinces and American States it looks something like this:
ID | ProvState
Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.
I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.
SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState
UNION
SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState
Anyone have any suggestions to this problem?
Arnie and All others. Thanks for your help.
The previous case became lenghty and then just mixed up a lot.
To make it easy I have created two temp tables and wrote to test select statement .
You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.
--Selected record must RaType='b'
-- PlanID='H321'
-- Gender='0'
--
--And not to include in select if any one of these meets:
-- Hosp='1' in other words it has to be 0
-- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i')
-- Dod is not null in other words Dod has to be null
--
--copy from here
GO
Create table #MyTable
( RowID int IDENTITY,
RD varchar(10),
RAType varchar(5),
History varchar(15)
)
INSERT INTO #MyTable VALUES ( '1', 'A', '1111' )
INSERT INTO #MyTable VALUES ( '2', 'S','2222' )
INSERT INTO #MyTable VALUES ( '3', 'D', '2345')
INSERT INTO #MyTable VALUES ( '4', 'I2','1234' )
INSERT INTO #MyTable VALUES ( '5', 'C','3333' )
INSERT INTO #MyTable VALUES ( '1', 'B','4444' )
INSERT INTO #MyTable VALUES ( '2', 'X','5555' )
INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666')
GO
Go
Create Table #MYTable2
(
RowID int IDentity,
RD varchar(10),
RaType varchar(5),
History varchar(15),
PlanID varchar(6),
Hosp varchar(2),
ESRD varchar(2),
RafctorType varchar(3),
gender varchar(5),
dod varchar (5) NULL
)
INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL)
INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL )
Go
Select #MYtable2.History from #MYTable2
INNER JOIN #mytable on #myTable2.History=#mytable.history
Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL
Select #Mytable2.History from #MyTable2
INNER JOIN #mytable on #mytable2.History=#mytable.history
where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)
Go
drop table #Mytable2
Drop table #MYtable
Hi Everyone,
Can you please let me know what is wrong with the following code? I run the following code using path: http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
public void searchResultsWithClinic() { SqlConnection myConnection; string conString; conString = ConfigurationManager.AppSettings["calendarString"]; myConnection = new SqlConnection(conString);
String cmdStr1, cmdStr2, cmdStr3; cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'";
cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'"; SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); DataSet DS = new DataSet(); myCommand.Fill(DS, "SearchPaymentResults");
repeaterSearchPaymentResults.DataSource = DS; repeaterSearchPaymentResults.DataBind(); myConnection.Close();
}
Incorrect syntax near 'admin'.
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.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error:
Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection);
Line 91: DataSet DS = new DataSet();
Line 92: myCommand.Fill(DS, "SearchPaymentResults");
Line 93:
Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs Line: 92 ---------------------------------------------------------------------------------
Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
thanks,May
I have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount
FROM dbo.Test2 INNER JOIN
dbo.test3 INNER JOIN
dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN
dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN
dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId)
GROUP BY dbo.TEST1.[OFFICE NAME]
ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks