Repeating Cursor Queries - Fails
Oct 3, 2001
When I try to repeat a query using cursors the first run is succesful but the second returns nothing; I then have to disconnect and reconnect to get the query to work again. Here's sample code (which does nothing except print returned items)
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @login sysname , @password sysname
declare sourcelogins cursor for
select name , password
from master.dbo.syslogins
open sourcelogins
while ( @@fetch_status = 0)
begin
fetch sourcelogins into @login , @password
print @login
end
close sourcelogins
deallocate sourcelogins
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
First time through this prints all logins. Second time it returns "completed successfully" and prints nothing. Similarily, if I run two queries consecutively, the second using a differenct cursor name, the second still fails. Any ideas?
View 1 Replies
ADVERTISEMENT
Oct 24, 2000
I have been working on a loop that needs to run inside of a cursor statement.
It has to check for a difference in days and create transaction records
for the difference. The problem is that if I include this while statement
it will only process 1 record with the cursor and stop. If I remove the
while it will work for all the records the cursor should be reading but
doesn't give the multiple transactions I need if there is a day difference.
Is there a limitation to using a while inside of a cursor. Below is the
code. ANy hep is appreciated.
cursor stuff
declare dbcursor cursor for
select uniq_id,account_id,created_by,encounter_id,
start_date,date_stopped,sig_codes, ndc_id,modified_by
from patient_medication where convert(datetime,start_date) = '10/20/2000'
and date_stopped is not null and date_stopped <> start_date order by uniq_id
open dbcursor
fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid,
@sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS =0)
begin
--freq stuff
select @freq = SIG.sig_frequency
FROM SIG where SIG.SIG_KEY = @sig_code
--check for evey other day
set @freq = 1
set @nodays = datediff(day, @sdate - 1, @edate)
select @nodays
while @cnter < @nodays
begin
insert into PATIENT_MEDICATION_DISPERSAL_
(uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id)
values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid,
@sdate, @edate ,@sig_code, @ndcid, @modid
END
close dbcursor
deallocate dbcursor
View 3 Replies
View Related
Mar 28, 2006
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
View 13 Replies
View Related
Apr 21, 2008
Hi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !
Thanks: Peter
View 1 Replies
View Related
Feb 7, 2006
Hi
Having some issues with our apps.
We are trying to get our applications to work with sql2005.
Ive got the databases "setup", and all our apps run fine...
...except for when queries are made without the owner of the
table being specified in the query.
The connection is opened with the username that is associated with that owner.
And it fails in Manager as well. Is there something im missing, because you should
be able to do this.
eg:
select * from <table_name>
Gives the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '<table_name>'.
However if i were to query like this:
select * from <owner>.<table_name>
it works fine.
View 11 Replies
View Related
Oct 17, 2006
This one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View 2 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
           for select name, salary from ag
 open ll
            fetch from ll
Â
             while @@FETCH_STATUS=0
              fetch from ll
               update ag set salary=200 where 1=1
Â
  close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Oct 23, 2007
I have a list that is grouped by Department inside it I have a table with two columns: name and hours. For example:
Department A
Name Houres
Mike 1
Department B
Name Houres
Mike 1
Steve 1
Department C
Name Houres
Mike 1
Steve 1
Outside the list I have another table that has a column for total hours for all departments:
Which in this case is 5. Everything work fine, but I have problem with displaying the total hours for all departments. In other words this will be my output
5
5
5
5
5
5
5
5
5
5
5
5
It will be repeated multiple times. I believe I have to add group to the table that contains the column (total hours for all departments.) but I can€™t do that b/c It is the total for all departments. Also I tried to hide duplicate wich works, but the problem with that when I export it to pdf it will print empty pages.
Any thoughts!
Thanks
View 1 Replies
View Related
Jun 4, 2008
This is actually not asp.net, but I'm hoping someone can help me. I have a report that is pulling from a couple of different tables. I am trying to add a meal choice to the report. Let's say they have the option of choosing chicken, beef or fish. They check the checkbox next to the choice. My report is pulling the infor, but it is putting 3 rows for each person not taking into consideration what choice they chose. The checkbox's all write to the same column - ses. Here is the code for the stored procedure:
CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT dbo.[names].lname as LastName, dbo.[names].fname as FirstName,dbo.[evser].ses as MealChoice,dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id
INNER JOIN dbo.evregON dbo.[evldg].id = dbo.[evreg].id
Full OUTER JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[evser].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO
The items in bold are what I added.
View 3 Replies
View Related
Feb 11, 2005
When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...
Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair
Do I need to worry ?
View 5 Replies
View Related
Jun 7, 2004
How can I display same repeating record in a table?
Any help will be appreciated.
View 3 Replies
View Related
May 18, 2007
Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.
CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );
My statement
SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;
the result is
ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5
The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?
View 3 Replies
View Related
Feb 12, 2008
Hi everybody!
I've got this little problem.
I need to insert data from a table to another table.
The scenario looks as follows:
I've got 'Company' table (no duplicated records there) and 'Contacts' table (one to many relation: for one company there can be more than one contact).
The following statement retrieves the data but it shows me everything, including all contacts and therefore I get duplicating values, e.g. company name.
Is there any way of changing the following query so it works?
INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT Distinct
CI.CompanyID,
CC.ContactID,
--CC.FirstName + ' ' +CC.LastName,
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2
FROM
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID
Kind Regards
View 11 Replies
View Related
Mar 17, 2008
Hi.,
I dont know to eliminate the repeting record in the ID column how to do that.,
for ex., i have given an example.,
create table Example (ID int, Name varchar(30))
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Chirag')
INSERT INTO [Example] ([ID],[Name]) VALUES (1,'Shailesh')
INSERT INTO [Example] ([ID],[Name]) VALUES (2,'Dipak')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Mihir')
INSERT INTO [Example] ([ID],[Name]) VALUES (4,'Piyush')
select * from Example.,
i will get.,
ID Name
----------- ------------------------------
1 Chirag
1 Shailesh
2 Dipak
4 Mihir
4 Piyush
.....
but i need like
ID Name
----------- ------------------------------
1 Chirag
Shailesh
2 Dipak
4 Mihir
Piyush
I dont want repeated ID., How can i do that.,
View 14 Replies
View Related
Jul 23, 2005
Any help on how to get rid of repeating records
View 1 Replies
View Related
Dec 12, 2007
i have a query when i run it shows data rows repeating like 5 times. I am not sure what am i doing wrong. hope someone can help me out.
this is my query.
SELECT "INVOICEDETAILS"."SeviceAmount", "RptSalesUser"."SalesPersonId", "RptSalesUser"."SalesPersonName", "RptSalesUser"."TemplateStatus", "INVOICE"."InvoiceDate"
FROM ("imacstest"."dbo"."INVOICE" "INVOICE" INNER JOIN "imacstest"."dbo"."INVOICEDETAILS" "INVOICEDETAILS" ON "INVOICE"."InvoiceNo"="INVOICEDETAILS"."InvoiceNumber") INNER JOIN "imacstest"."dbo"."RptSalesUser" "RptSalesUser" ON ("INVOICE"."InvoiceNo"="RptSalesUser"."invoiceNumber") AND ("INVOICEDETAILS"."InvoiceNumber"="RptSalesUser"."invoiceNumber")
WHERE "RptSalesUser"."TemplateStatus"='A' AND ("INVOICE"."InvoiceDate">={ts '2007-01-01 00:00:00'} AND "INVOICE"."InvoiceDate"<{ts '2007-12-13 00:00:00'}) AND "RptSalesUser"."SalesPersonId"=5 and "Invoice"."InvoiceNo"='1004447' ORDER BY "RptSalesUser"."SalesPersonId","Invoice"."InvoiceDate"
when i execute this i get about 12 data rows which are fine but then the same 12 data lines repeats 4 more times. hope someone be able to help me quick.
View 17 Replies
View Related
Apr 15, 2008
I have a table called Customers which holds the customers details including CustID primary key a table called DVDs which holds all the dvd details including DVDID primary key and a table called WishList which holds the CustID and DVDID I want to take the CustID given my the user and display all the dvds in the wishlist with that CustID the code I have now is int CustID = Convert.ToInt16(Session["CustID"]); comm = new SqlCommand("SELECT DVDs.DVDID, dvds.Title, Director, Actor, SUBSTRING(Description,0,200) AS Description, Image FROM DVDs, Customers, WishList WHERE WishList.CustID = @CustID AND WishList.DVDID = DVDs.DVDID", conn); comm.Parameters.Add("@CustID", SqlDbType.Int).Value = CustID; but when i run the query it displays each dvd 5 times not sure why can any one help cheers
View 2 Replies
View Related
Apr 24, 2008
I have a store procedure that pulls info for a meeting coming up. They can choose their meal choice which in this case is fish, chicken, beef. What is happening on the report, it's listing each person 3 times with each choice no matter which is was. How do I correct this? Code below:
CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT dbo.[names].lname as LastName, dbo.[names].fname as FirstName, dbo.[evser].ses as MealChoice, dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id
INNER JOIN dbo.evregON dbo.[evldg].id = dbo.[evreg].id
INNER JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO
THANKS!!!
View 7 Replies
View Related
May 3, 2006
hello i'm using asp.net 2 with VB and i have a table in my db the problem is that table has a repeated records like this imagenow i want to update the absence record but cos it's repeated it will only update one record what i want is to apply the update on all other records ? hope u can help me .thanks
View 6 Replies
View Related
Apr 29, 2004
I would like to know how to produce the following in a query.
Parent Child
---------------
Dave Sarah
.......... Brad
.......... Alice
.......... Hanna
Rather than:
Parent Child
---------------
Dave Sarah
Dave Brad
Dave Alice
Dave Hanna
View 5 Replies
View Related
Jul 20, 2005
I am a developer who works with MS SQL Server a lot, but I'm far froman expert. I am revamping an appointment scheduling system thatallows for appointments to reoccur daily, weekly, monthly and yearly.Currently, I am saving the appointment date as the initial appointmentdate. Then when I want to check for appointments, my stored proc doesdoes a select on the appropriate records fitting certain critieria(like only appointments for this doctor, at this location, etc). OnceI have these records I cycle through them calling the DateAdd() andDateDiff() functions to see if the appointment is reoccuring duringthe dates I'm looking for.Here's is a mock up of what I'm doing. I know cursors are a huge hitperformance-wise (especially how they are used in this scenario) andwant to get a way from this, but I can't figure out how to getreoccuring appointments to work. Any help is appreciated. Thanks.sp_GetAppointments(@StartDate, @EndDate)set @DateToCheck = @StartApptDatewhile @DateToCheck <= @EndApptDatebegin--Start a cursorDECLARE RepeatCursor CURSORFORWARD_ONLY STATIC FORselect ApptDate from ApptTable where DoctorID = 1 and--Check if it repeats daily((repeat = 1 andDateAdd(d,DateDiff(d,ApptDate,@DateToCheck),ApptDa te) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)--Check if it repeats weeklyor (repeat = 2 andDateAdd(wk,DateDiff(wk,ApptDate,@DateToCheck),Appt Date) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)CLOSE RepeatCursorDEALLOCATE RepeatCursorset @DateToCheck = DateAdd(d,1,@DateToCheck)end
View 4 Replies
View Related
Nov 29, 2007
Ok, so I've been racking my brains on this one for a while now and figured it was time to ask for some help...
I have a table named tblAppInfo that looks something like this:
Code Block
user_name app_name app_ver
User1 MS Word 2000
User2 MS Word 2000
User3 MS Excel 2000
User4 MS Excel 2000
User5 MS Excel 2000
User6 MS Excel 2000
User7 MS Outlook 2000
User8 MS PowerPoint 2000
User9 Adobe Acrobat 5.0
User10 Adobe Acrobat 6.0
User11 Adobe Acrobat 7.0
What I want to do is remove any rows that repeat the app_name and app_ver more than twice. Basically in the example above, it would remove the lines that have MS Excel 2000 (as it's listed 4 times). MS Word 2000 would stay as it's only listed twice. As would Outlook and PowerPoint. All 3 Adobe's would also stay as their versions are different.
I've tried the following but it still repeats applications that occur more than twice:
Code BlockSELECT user_name, app_name, app_ver
FROM tblAppInfo
GROUP BY app_name, app_ver, user_name
having count(distinct app_name + app_ver + tblAppInfo.host_name) < 3
ORDER BY app_name, app_ver
I've also tried using nested select statements and everything else in my arsenal to no avail. I hope someone here will be able to help me out as I'm afraid I'll go bald if I try anymore!
Thanks in advance!
Tom
View 6 Replies
View Related
Sep 4, 2007
Hi
Pulled all my hair out now, can someone please offer some help before I go totally mad...
I have no page header, this is all in the body. I have a List with account number and job number in it at the top, called list1. In list1 I have list2, with the job details, basically a list of people who attended the job. The fields in list2 are textboxes, they're not in a table.
EG:
Acc No: 12345
Job No: 54321
Name
Fred Bloggs
Joe Bloggs
Etc
When my list2 of names goes over 1 page, I want to repeat the account number and job number on each subsequent page. In my real report I actually have 10 fields I want to repeat.
I then want to be able to export this report to a PDF.
I can't put the header in the page header and RepeatWith doesn't work when exporting to PDF, or at all depending on what mood it's in.
Does anyone have a workaround for this please?
Cheers
View 2 Replies
View Related
Dec 14, 2007
I have the option for repeating table headers for every page turn on as I would like the table headers repeated on every page. In the HTML report view, this works fine, however when the report is exported to pdf (a total of 80 some pages), when there is a table that contains a lot of data, the table headers are not displayed. I know this could be because the table includes a subreport and sometimes the subreport runs more than one page, but shouldn't the table header still be diplayed on the new page?
Is there something else that I need to check or should I move the table/column header information somplace else?
Thanks for any information.
View 1 Replies
View Related
Dec 3, 2007
Hi,
I have a matrix which displays like this
Title1 A Book
Another Book
Title2 Yet another book
My Book
How can I display it like this:
Title1 A Book
Title1 Another Book
Title2 Yet another book
Title2 My Book
So that when I export this to excel I can sort and filter data easier. Thanks very much.
View 4 Replies
View Related
Aug 3, 2007
I have run into a strange issue that I believe is a SQL Reporting Services issue.
I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.
The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.
When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.
What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.
I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.
If anyone has run into this and found a work around, let me know.
Thank you,
T.J.
View 1 Replies
View Related
Jan 31, 2008
Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null
Output:
ID Title Name Request Date Ship Date
29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008
Going crazy ugh...
View 3 Replies
View Related
Feb 28, 2005
I would like to avoid repeating 4 time the procedure if end and instead use someting like "switch case", but I can not find the equivalent.
Is there any command that could do that?
thanks
[code]
CREATE PROCEDURE sa_default
@etriduser int= null,
@locator int = null,
@choix int = null,
@login varchar(50) = null
AS
if @choix=0
begin
..........
end
if @choix=1
begin
............
end
if @choix=2
begin
...........
end
if @choix=3
begin
........
end
RETURN
GO
[/code]
View 8 Replies
View Related
Jan 17, 2003
How do l filter out funny characters in a field like (',','@','#','"'.'/')etc And Repeating digits Like 5555555,2222,111111,00,
000000000.
Tried using the patindex but my method is too long.How do l do that ?
View 3 Replies
View Related
May 9, 2008
Hi,
I've just recently learned that being an identity seed-column doesn't guarantee that you will always get unique values. It can double up and cause a violation of PK. If so, is there a work around this that doesn't involve a REINDEX? Cause if im home and my client suddenly experiences this in the middle of a busy day, that would be a total disaster. Any ideas on how i can avoid this or a workaround maybe? Thanks!
View 3 Replies
View Related
May 13, 2008
I am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
Thanks,
Walt
View 4 Replies
View Related
